MySQL异常处理记录

MySQLSyntaxErrorException异常

遇到这个异常第一感觉就是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 '?,?,?,?)' at line 1

随后我仔细检查了SQL语句，发现并没有错，又将SQL语句使用System.out.println(ps.toString());打印出来的结果放到MySQL客户端中执行也是正常的。最后发现是调用executeUpdate(sql);将sql作为参数传入了。

Connection conn = null;
PreparedStatement ps = null;

try {
    String sql = "insert into t_student(id,name,sex,age) values(?,?,?,?)";
    conn = DbUtil.getConnection();
    ps = conn.prepareStatement(sql);
    ps.setInt(1, student.getId());
    ps.setString(2, student.getName());
    ps.setString(3, student.getSex());
    ps.setInt(4, student.getAge());
    System.out.println(ps.toString());
    int i = ps.executeUpdate(sql); // error
    return i;
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    if (conn != null) {
        try {
            DbUtil.closeConnection(conn, ps);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

在executeUpdate(String sql)的注释中有指出此方法不能被PreparedStatement或者CallableStatement调用。在创建PreparedStatement对象时就已经将SQL语句传入，调用executeUpdate方法时不能再传入了，否则就会出现MySQLSyntaxErrorException异常。

executeUpdate注释信息

    /**
     * Executes the given SQL statement, which may be an <code>INSERT</code>,
     * <code>UPDATE</code>, or <code>DELETE</code> statement or an
     * SQL statement that returns nothing, such as an SQL DDL statement.
     *<p>
     * <strong>Note:</strong>**This method cannot be called on a
     * <code>PreparedStatement</code> or <code>CallableStatement</code>.**
     * @param sql an SQL Data Manipulation Language (DML) statement, such as <code>INSERT</code>, <code>UPDATE</code> or
     * <code>DELETE</code>; or an SQL statement that returns nothing,
     * such as a DDL statement.
     *
     * @return either (1) the row count for SQL Data Manipulation Language (DML) statements
     *         or (2) 0 for SQL statements that return nothing
     *
     * @exception SQLException if a database access error occurs,
     * this method is called on a closed <code>Statement</code>, the given
     * SQL statement produces a <code>ResultSet</code> object, the method is called on a
     * <code>PreparedStatement</code> or <code>CallableStatement</code>
     * @throws SQLTimeoutException when the driver has determined that the
     * timeout value that was specified by the {@code setQueryTimeout}
     * method has been exceeded and has at least attempted to cancel
     * the currently running {@code Statement}
     */
    int executeUpdate(String sql) throws SQLException;