Geek Answers Handbook

How to use MySQL subquery in JDBC?

Request example:

SELECT country FROM data WHERE city LIKE (SELECT LEFT ('jakartada',7));

Example in JDBC:

 String sql = " SELECT country FROM data WHERE city LIKE (SELECT LEFT ('?',7)) "; PreparedStatement ps = koneksi.prepareStatement(sql); ps.setString(1, city ); ResultSet rs = ps.executeQuery();

Why is this not working properly?

+6
source share
4 answers

There is no parameter in the prepared statement, but the code is trying to set the parameter. Try adding a parameter to the statement.

 String sql = " SELECT country FROM data WHERE city LIKE (SELECT LEFT (?,7)) "; PreparedStatement ps = koneksi.prepareStatement(sql); ps.setString(1, city ); ResultSet rs = ps.executeQuery();

Or try deleting the statement specifying the parameter:

 String sql = " SELECT country FROM data WHERE city LIKE (SELECT LEFT ('jakartada',7)) "; PreparedStatement ps = koneksi.prepareStatement(sql); ResultSet rs = ps.executeQuery();
+3
source

I believe that you are doing it harder than it should be, and at the same time you are missing something. Is this what you are trying to do?

  SELECT country FROM data WHERE city LIKE 'jakarta%'

That is, you are looking for a country column from each line, where the name of the city begins with "jakarta"? If yes, do not forget the % sign. If you did not specify the % sign, then

  SELECT country FROM data WHERE city LIKE 'jakarta'

and

  SELECT country FROM data WHERE city = 'jakarta'

mean the same thing as each other, and the LIKE operator is useless; you can also use the = operator.

So it seems to me that you need a MySQL query

  SELECT country FROM data WHERE city LIKE CONCAT(LEFT('jakartada',7),'%')

to add a% sign. In this case, you do not need a subquery.

As you pointed out, the required Java code:

  String sql = "SELECT country FROM data " . "WHERE city LIKE CONCAT(LEFT(?,7),'%')"; PreparedStatement ps = koneksi.prepareStatement(sql); ps.setString(1, city ); ResultSet rs = ps.executeQuery(); ... process the rs records ... rs.close(); /* please don't forget to close your result sets */
+1
source

Do not specify in the prepared expression when setting values ​​at runtime ... Otherwise, it will be interpreted as an input, but not for the ps position ... Remove single quotes from the question mark ...

0
source

use this link for your solution and this request

http://sqlfiddle.com/#!2/c79ab/10

 SELECT country FROM data WHERE city LIKE CONCAT(LEFT('jakartada',7),'%')
0
source

More articles:


All Articles