Menu
I am trying to check, if a specific value already exists in my database. I am accessing database from java standalone app using JDBC (queries for inserting records into db work so my setup and connection are ok).
String queryCheck = "SELECT * from messages WHERE msgid = " + msgid;
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(queryCheck); // execute the query, and get a java resultset
// if this ID already exists, we quit
if(rs.absolute(1)) {
conn.close();
return;
}
I am getting this error (there is apparently something wrong with my SQL syntax):
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'd-f05708071f8f' at line 1
However, if I try to execute this command in my MySQL command line, it works! Can you tell me, whats wrong with my statement? Thanks for any tips!
827
We can check if database exists using java. sql. DatabaseMetaData interface. using DatabaseMetaData interface we can obtain the meta data about the database catalog, for example we can obtain the database name, tables available in that database, columns in that table etc.
To test whether a row exists in a MySQL table or not, use exists condition. The exists condition can be used with subquery. It returns true when row exists in the table, otherwise false is returned. True is represented in the form of 1 and false is represented as 0.
The SQL EXISTS Operator The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records.
SELECT ID FROM TABLE WHERE ID = 'number'; SELECT count(*) FROM TABLE WHERE ID = 'number'; 1 - exists, In your PHP or other code, you must check if one of these queries return value.
You need to use bind variables.
PreparedStatement st = conn.prepareStatement(
"SELECT * from messages WHERE msgid = ?");
st.setString(1, msgid);
ResultSet rs = st.executeQuery(queryCheck);
Or get into manual quoting, but that is risky.
In addition to preventing SQL injection, prepared statements should also improve performance if you run the same query repeatedly.
196
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
