Java MySQL check if value exists in database

Question

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!

Please, please, please, ALWAYS use query binding... ALWAYS not only you prevent SQL injection attacks, you also help the database as it will cache the execution plan making the next query faster. — Juan Antonio Gomez Moriano
– Juan Antonio Gomez Moriano, Commented Apr 19, 2013 at 7:27

Boris the Spider · Accepted Answer · 2013-04-19 07:32:17Z

27

You need to wrap a String in quotes in MySQL, so the query needs to be

SELECT * from messages WHERE msgid = 'd-f05708071f8f';

Not

SELECT * from messages WHERE msgid = d-f05708071f8f;

So the code should read

String queryCheck = "SELECT * from messages WHERE msgid = '" + msgid + "'";

I would suggest using a PreparedStatement to avoid these sorts of issues and any risk of SQL injection:

final String queryCheck = "SELECT * from messages WHERE msgid = ?";
final PreparedStatement ps = conn.prepareStatement(queryCheck);
ps.setString(1, msgid);
final ResultSet resultSet = ps.executeQuery();

Using string concatenation for query building is considered very bad practice. Has been for a long time now.

Further I would suggest using select count(*) rather than the full select * as this returns much less data (think of the size of the ResultSet) and MySQL can optimise it too.

final String queryCheck = "SELECT count(*) from messages WHERE msgid = ?";
final PreparedStatement ps = conn.prepareStatement(queryCheck);
ps.setString(1, msgid);
final ResultSet resultSet = ps.executeQuery();
if(resultSet.next()) {
    final int count = resultSet.getInt(1);
}

edited Apr 19, 2013 at 7:32

answered Apr 19, 2013 at 7:26

Boris the Spider

61.4k6 gold badges111 silver badges177 bronze badges

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

1 Comment

Smajl Over a year ago

yep, forgot hyphens and I was testing it only with integers... my bad.. thanks!

Thilo · Accepted Answer · 2013-04-19 07:26:15Z

6

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.

answered Apr 19, 2013 at 7:26

Thilo

264k107 gold badges527 silver badges674 bronze badges

Comments

RPB · Accepted Answer · 2013-04-19 07:31:22Z

3

You can try this:

String queryCheck = "SELECT * from messages WHERE msgid = '" + msgid + "'";

You have missed quotes around msgid. (I'm assuming that msgid is String and not Integer value. )

edited Apr 19, 2013 at 7:31

RPB

16.4k16 gold badges59 silver badges80 bronze badges

answered Apr 19, 2013 at 7:27

PC.

7,0425 gold badges38 silver badges73 bronze badges

1 Comment

Kevin Bowersox Over a year ago

Just a suggestion when answering on here, if a user is concatenating Strings to be used for sql queries, always recommend a PreparedStatement many users will downvote you instantly if you don't, on the same note you will get some upvotes for mentioning sql injection/preparedstatements.

Kevin Bowersox · Accepted Answer · 2013-04-19 07:31:57Z

3

Since msgid is a varchar you need to surround the value in the where clause with single quotes.

String queryCheck = "SELECT * from messages WHERE msgid = '" + msgid + "'";

Dynamically generating SQL strings is not recommend however since it can expose your application to sql injection.

Instead use a PreparedStatement:

            String queryCheck = "SELECT * from messages WHERE msgid = ?";
            PreparedStatement st = conn.prepareStatement(queryCheck);
            st.setString(1, msgid);
            ResultSet rs = st.executeQuery();

edited Apr 19, 2013 at 7:31

answered Apr 19, 2013 at 7:26

Kevin Bowersox

94.6k19 gold badges164 silver badges198 bronze badges

Comments

CloudyMarble · Accepted Answer · 2013-04-19 07:37:26Z

3

Use single quotes arount the parameter:

"SELECT * FROM messages WHERE msgid = '" + msgid + "'";

Or better you use prepared statements.

edited Apr 19, 2013 at 7:37

answered Apr 19, 2013 at 7:26

CloudyMarble

37.7k70 gold badges103 silver badges133 bronze badges

Comments

PSR · Accepted Answer · 2013-04-19 07:27:02Z

1

You need to use single quotes

SELECT * from messages WHERE msgid = 'd-f05708071f8f';

answered Apr 19, 2013 at 7:27

PSR

40.4k43 gold badges115 silver badges154 bronze badges

Comments

abagshaw · Accepted Answer · 2017-07-29 17:35:06Z

String sql1 ="SELECT Time FROM monday_wednesday WHERE Time ='"+time.getSelectedItem()+"'";
pst=con.prepareStatement(sql1);
rs=pst.executeQuery();
if(rs.next()) {
    if(rs.getString("Time").equals(time.getSelectedItem())) {
        JOptionPane.showMessageDialog(null,"Time is already taken","",JOptionPane.INFORMATION_MESSAGE); 
    }
} else {
    String sql="INSERT INTO monday_wednesday(pfname,pmname,plname,Birthdate,Gender,Address,City,Contact,Contactperson,Time,Date)\n" + "VALUES ('"+txtFirstName1.getText()+"','"+txtMiddleName1.getText()+"','"+txtLastName1.getText()+"','"+d+"','"+gender.getSelectedItem()+"','"+ txtAddress.getText()+"','"+txtCity.getText()+"','"+txtContact.getText()+"','"+txtContactPerson1.getText()+"','"+time.getSelectedItem()+"','"+dateFormat.format(date)+"')";
}

Just a simple duplicate entry algorithm

Collectives™ on Stack Overflow

Java MySQL check if value exists in database

7 Answers 7

1 Comment

Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related