How do I use WHERE EXISTS function in JDBC

Question

For the last 6-7 hours I've been searching/googling/experimenting for a solution to this but no results so far.

I have an SQL string(below) which works PERFECTLY in PHPMYADMIN but doesn't work within my java execution. When attempting to execute the query I get "Column rid Not Found."

SQLException:

java.sql.SQLException: Column 'rid' not found.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1167)
at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2851)
at net.matthewauld.racetrack.server.WrSQL.getJSONClassSpecificRiders(WrSQL.java:41)
at net.matthewauld.racetrack.server.ClientListener.parseCommand(ClientListener.java:110)
at net.matthewauld.racetrack.server.ClientListener.run(ClientListener.java:42)

--

SELECT * FROM `riders` WHERE EXISTS(SELECT * FROM `ridersclasses` WHERE ridersclasses.rid = riders.id AND `cid` = '6') ORDER BY `first_name` ASC

Here is my java code to execute the SQL Query.

url = "jdbc:mysql://127.0.0.1:3306/racetrack2013?allowMultiQueries=true";
con = DriverManager.getConnection(url, user, password);
st = con.createStatement();
rs = st.executeQuery(query);

Below I will post the structure of the two tables and some data examples:

RidersClasses

+---------------------------------------------------+
|                   ridersclasses                   |
+---------------------------------------------------+
|  ID  |  RID  |  CID  |  BIKENUMBER  |  BIKEMODEL  |
+---------------------------------------------------+

Riders

+----------------------------------------------------------------------------------+
|                                         Riders                                   |
+----------------------------------------------------------------------------------+
|  ID  |  first_name  | last_name  |  address  |  city_id  |  h_phone  |  c_phone  |
+----------------------------------------------------------------------------------+

IMO, it would be more helpful if you could post the (whole) stacktrace. — Bhesh Gurung, Jun 14 '13 at 06:37
Did you try naming your tables using the AS keyword? Eg: ....from ridersclasses as rideClass .... Then referennce them like rideClass.rid = ... — ug_, Jun 14 '13 at 06:41
@MatthewAuld I think you have to try with the exact name of table name and column name as case sensitive. — Yogesh Suthar, Jun 14 '13 at 06:42
@ns47731 I've attempted adding the AS keywords for each table but I still get the same error. — Matthew Auld, Jun 14 '13 at 06:43
@MatthewAuld maybe the problem is in jdbc .. have you tried `rs.getInt("RID")` ? where rs is the result set — Stephan, Jun 14 '13 at 06:44
@YogeshSuthar if you look at the information I've provided you will see that it is already properly cased. — Matthew Auld, Jun 14 '13 at 06:45
Does rewriting the query help? Did you try `SELECT * FROM riders WHERE ID IN (SELECT ID FROM ridersclasses WHERE CID = '6') ORDER BY first_name ASC` — devnull, Jun 14 '13 at 06:45
I think the error happens after the execution when you trying to read a column from the row, by looking at your exception. — DevZer0, Jun 14 '13 at 06:45
@Stephan I can't get the ResultSet if the query isn't executing correctly. — Matthew Auld, Jun 14 '13 at 06:46

score 6 · Accepted Answer · answered Jun 14 '13 at 06:47

6

The problem is simple : there's no rid column in riders table.

As its shown in the strack trace the problem starts at getInt

So instead of rs.getInt("rid") use rs.getInt("id")

answered Jun 14 '13 at 06:47

Stephan

8,000
3
36
42

Good call you might add that to get the rid you could use a left join instead of the EXISTS – ug_ Jun 14 '13 at 06:52
@ns47731 Thanks, i thought of that but rid = id so its not necessary – Stephan Jun 14 '13 at 07:07

How do I use WHERE EXISTS function in JDBC

1 Answers1