SQL Query works in SQL Developer, but not on the Java side

Question

I'm trying to figure this but without success. I'm trying to find every row in a specific table that contains a value.

For example, let's consider this Employee table:

|     Id           | First_name | Last_name  | Date_of_birth | Car_number |
|------------------|------------|------------|---------------|------------|
| 10001            | John       | Washington | 28-Aug-43     | 5          |
| 10083            | Arvid      | Sharma     | 24-Nov-54     | null       |
| 10034            | David      | Johnson    | 12-May-76     |            |

I'm building this query on the Java side (this is what I print in the log):

Select * From Employee WHERE Id LIKE ? OR First_name LIKE ? OR Last_name LIKE ? OR Date_of_birth LIKE ? OR Car_number LIKE ?

then I use a prepared statement so that, if I search for the string 'oh' it becomes:

Select * From Employee WHERE Id LIKE '%oh%' OR First_name LIKE '%oh%' OR Last_name LIKE '%oh%' OR Date_of_birth LIKE '%oh%' OR Car_number LIKE '%oh%'

Here's the corresponding code:

String wantedQuery = "Select * From " + tableName + " WHERE";

PreparedStatement preparedStatement;
try(ResultSet rsColumns = columnsForTable(tableName)) {
    String keyword = keywordField.getText();

    int limit = 0;
    while (rsColumns.next()) {
        wantedQuery += " " + rsColumns.getString(1) + " LIKE ? OR";
        limit++;
    }
    wantedQuery = wantedQuery.substring(0, wantedQuery.length()-3);

    preparedStatement = con.prepareStatement(wantedQuery);

    System.out.println(wantedQuery);
    System.out.println("\'%"+keyword+"%\'");

    for(int i = 1; i <= limit; i++) {;
        preparedStatement.setString(i, "\'%"+keyword+"%\'");
    }
}

try(ResultSet rs = preparedStatement.executeQuery()) {
 //now get the results from here
 ResultSetMetaData metaData = rs.getMetaData();
 while (rs.next()) {
     System.out.println("Fetching row");
     ...
 }

Now the problem is that when I try to get the results, it gives me 0 rows (I never see "Fetching row" printed) but the query works in SQL Developer. So I guess the error is on the Java side but I have any clue what it can be. Any ideas?

As a quick guess, you don't need the single quite signs here: preparedStatement.setString(i, "\'%"+keyword+"%\'"); — meskobalazs
– meskobalazs, Commented Apr 26, 2015 at 7:21
@meskobalazs Hmm that was it! But it doesn't fetch all the rows. I get like 8 rows but when running the query on the database directly, there are more than one hundred.... — user2336315
– user2336315, Commented Apr 26, 2015 at 7:25

meskobalazs · Accepted Answer · 2015-04-26 07:29:49Z

2

When you are using setString(), JDBC knows that it must be a String, so it puts the single quotes around the String as needed, you don't have to do it yourself. But because you do so, it actually a different one, than what you expect.

edited Apr 26, 2015 at 7:29

answered Apr 26, 2015 at 7:26

meskobalazs

16.1k2 gold badges44 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2336315 Over a year ago

Nevermind for my previous comment, I forget to run the query with the same keyword, thanks for the help. rsColumns.getString(1) is correct since this is from where I get the columns name from the table ("Select column_name From USER_TAB_COLUMNS WHERE table_name = ?")

Yoshita Mahajan · Accepted Answer · 2022-03-28 17:02:57Z

0

Try below mentioned methods

Add spaces at the start and at the end in the query in java code
Remove ; from the end of the sql in java code as whenever java code executes sql it appends ; to it.

answered Mar 28, 2022 at 17:02

Yoshita Mahajan

4634 silver badges6 bronze badges

Collectives™ on Stack Overflow

SQL Query works in SQL Developer, but not on the Java side

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related