Java MySQL检查数据库中是否存在值。

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).

如果数据库中已经存在某个特定值，我将尝试检查。我正在使用JDBC访问java独立应用程序的数据库(查询将记录插入到db工作中，这样我的设置和连接就可以了)。

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):

我得到这个错误(显然我的SQL语法有问题):

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!

但是，如果我尝试在我的MySQL命令行中执行这个命令，它就会生效!你能告诉我，我的陈述有什么问题吗?谢谢你的任何建议!

7 个解决方案

#1

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

您需要在MySQL中引用一个字符串，因此查询需要是。

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:

我建议使用PreparedStatement来避免这类问题和SQL注入的任何风险:

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.

此外，我建议使用select count(*)而不是full select *，因为它返回的数据要少得多(想想ResultSet的大小)，而MySQL也可以优化它。

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);
}

#2

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.

除了防止SQL注入之外，如果您重复运行相同的查询，准备语句还应该提高性能。

#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. )

你漏掉了msgid周围的引号。(我假设msgid是字符串，而不是整数值。)

#4

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

因为msgid是一个varchar，您需要用单引号包围where子句中的值。

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.

但是，动态生成SQL字符串并不可取，因为它可以将您的应用程序暴露给SQL注入。

Instead use a PreparedStatement:

而不是使用PreparedStatement:

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

#5

Use single quotes arount the parameter:

使用单引号arount参数:

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

Or better you use prepared statements.

或者更好地使用准备好的语句。

#6

You need to use single quotes

你需要使用单引号。

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

#7

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

只是一个简单的重复输入算法。

#1