java - finally block 末尾的参数索引超出 mysql 范围

Question

我的大部分代码似乎都能正常工作，但我一直在线程“主”java.sql.SQLException 中收到异常:参数索引超出范围(1 > 参数数量，即 0)。它发生在 readDatabase() 的 finally block 之后。它没有到达打印语句 System.out.println("DOESN'T GET HERE");我不知道为什么。这是处理所有内容的类。在主类中，它只是创建一个对象并调用 readDatabase();

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

//static because when creating an object of it in main, you won't have to make an object of the outer class (SQLProject) first
public class MySQLAccess{
    private Connection connect = null;
    private Statement statement = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;

    public void readDatabase() throws Exception
    {
        try{
            Class.forName("com.mysql.jdbc.Driver");
            connect = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "");

            statement = connect.createStatement();
            System.out.println("here1");
            resultSet = statement.executeQuery("select * from test.comments");
            writeResultSet(resultSet);

            preparedStatement = connect.prepareStatement("INSERT INTO test.comments values(default, ?, ?, ?, ?, ?, ?)");
            //columsn in test.comments
            // myuser, email, webpage, datum, summary, COMMENTS
            preparedStatement.setString(1, "Test");
            preparedStatement.setString(2, "TestEmail");
            preparedStatement.setString(3, "TestWebpage");
            preparedStatement.setDate(4, new java.sql.Date(2009, 12, 11));
            preparedStatement.setString(5, "Test Summary");
            preparedStatement.setString(6, "Test Comment");
            System.out.println("here2");
            preparedStatement.executeUpdate();

            preparedStatement = connect.prepareStatement("SELECT myuser, webpage, datum, summary, comments FROM test.comments");
            System.out.println("here3");
            resultSet = preparedStatement.executeQuery();
            writeResultSet(resultSet);

            preparedStatement = connect.prepareStatement("DELETE FROM test.comments WHERE myuser='?';");
            preparedStatement.setString(1, "Test");
            preparedStatement.executeUpdate();

            resultSet = statement.executeQuery("SELECT * FROM test.comments;");
            System.out.println("Writing meta data");
            writeMetaData(resultSet);
        }
        catch (Exception e){
            throw e;
        }
        finally{ 
            close();
            System.out.println("ALMOST");
            }
        System.out.println("DOESN'T GET HERE");
    }

    private void writeMetaData(ResultSet resultSet) throws SQLException
    {
        System.out.println("The columns in the table are: ");
        System.out.println("Table: " + resultSet.getMetaData().getTableName(1));

        for(int i=1;i<=resultSet.getMetaData().getColumnCount(); i++)
        {
            System.out.println("Column " + i + " " + resultSet.getMetaData().getColumnName(i));
        }
    }

    private void writeResultSet(ResultSet resultSet) throws SQLException
    {
        while(resultSet.next())
        {
            String user = resultSet.getString("myuser");
            String website = resultSet.getString("webpage");
            String summary = resultSet.getString("summary");
            Date date = resultSet.getDate("datum");
            String comment = resultSet.getString("comments");

            System.out.println("User: " + user);
            System.out.println("website: " + website);
            System.out.println("summary: " + summary);
            System.out.println("date: " + date);
            System.out.println("comment: " + comment);
        }
    }

    private void close()
    {
        try{
            if(resultSet != null)
                resultSet.close();
            if(statement != null)
                statement.close();
            if(connect != null)
                connect.close();

        }
        catch(Exception e){
            e.printStackTrace();
            System.out.println("hello");
            System.out.println(e);
        }
    }
}//private inner class

Answer 1

preparedStatement = 
    connect.prepareStatement("DELETE FROM test.comments WHERE myuser='?';");
preparedStatement.setString(1, "Test");

这是有问题的陈述。问号包含在引号中，因此语句解析器无法找到它，因此下一条语句会抛出错误。

虽然参数类型是String，但是对应的占位符不应该包含在引号中。准备好的语句处理器将负责根据参数的数据类型生成适当的 SQL。因此，它始终是一个普通的 ? 应该用作任何数据类型的参数的占位符。

因此，这两个语句应该简单如下:

preparedStatement = 
    connect.prepareStatement("DELETE FROM test.comments WHERE myuser=?");
preparedStatement.setString(1, "Test");