java - 它不会给我代码错误，MySQLSyntaxErrorException

Question

这是错误:

run: Mon Mar 25 05:22:00 SGT 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification. Mar 25, 2019 5:22:15 AM mypackage.profile jButton6ActionPerformed SEVERE: null 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 'com.mysql.jdbc.JDBC42PreparedStatement@41399cae: SELECT * FROM persons WHERE id ' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

然后是代码:

private void jButton6ActionPerformed(java.awt.event.ActionEvent evt) {                                         
    // TODO add your handling code here:
    PreparedStatement ps;
    ResultSet rs;
    String id = txtid.getText();
    if (id.isEmpty()) {
        JOptionPane.showMessageDialog(null, "ID field is empty");
        return;
    }
    String sql = "SELECT * FROM persons WHERE id = "+ Integer.parseInt(id) + ";";
    JOptionPane.showConfirmDialog(null,"Is this right?\n" + sql);
    try {
        ps = con.prepareStatement(sql);
        rs = ps.executeQuery(String.valueOf(ps));
        if(rs.next()){
        txtln.setText(rs.getString("lastName"));
        txtfn.setText(rs.getString("firstName"));
        txtadrs.setText(rs.getString("address"));
        txtcity.setText(rs.getString("city"));
        }
    } catch (SQLException ex) {
        Logger.getLogger(profile.class.getName()).log(Level.SEVERE, null, ex);
    }
}                                        

private void jButton5ActionPerformed(java.awt.event.ActionEvent evt) {                                         
    // TODO add your handling code here:
    dispose();
    new menu().setVisible(true);
}

Answer 1

rs = ps.executeQuery(String.valueOf(ps)); 在 PreparedStatement 的上下文中不正确.由于您在创建 ps 时已经提供了 SQL，因此您想执行 rs = ps.executeQuery();。

您的代码几乎没有大问题:

1. 切勿从 Swing EDT 运行 JDBC，因为您将阻止 Swing UI 刷新事件并且您的应用程序将卡住。您必须在后台任务中安排长时间运行的任务，如 SQL 查询，请参阅 Worker Threads and SwingWorker文档。

2. 切勿使用 String 连接来构建 SQL。在你的例子中还没有问题，但是如果 id 是 String 而不是 int 你会有一个 SQL Injection vulnerability .由于您已经创建了 PreparedStatement 使用:

   ps = con.prepareStatement("SELECT * FROM persons WHERE id = ?");
   ps.setInt(1, Integer.parseInt(id));
   

3. 处理完PreparedStatement后关闭，如果您不打算重用它，请使用try-with-resources syntax .

4. MySQL 不需要在语句末尾添加 ;。仅当您启用多语句执行时才需要这样做。