Tag Archives: JAVA Operate Database Error

[Solved] JAVA Operate Database Error: You have an error in your SQL syntax; Dao layer SQL statement error

JAVA Operate Database Error: You have an error in your SQL syntax; Dao layer SQL statement error

Specific error reports are as follows:

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
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2491)
	at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1552)
	at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2607)
	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1480)
	at cn.edu.wut.jwms.dao.TeacherDao.insertTeacher(TeacherDao.java:39)
	at cn.edu.wut.jwms.testDao.testTeacherInsert(testDao.java:14)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
	at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
	at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
	at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
	at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
	at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)

Error reporting location code:

public boolean insertTeacher(Teacher teacher)  {
        boolean b = false;

        try {
            //get the connection
            Connection connection = JDBCUtil.getConn();

            String sql = "INSERT INTO tb_teacher(teacher_num,teacher_pwd,teacher_name,teacher_tel,teacher_col_id) VALUES(?,?,?,?,?)";
            //compile
            PreparedStatement ps = connection.prepareStatement(sql);

            //Assigning values to placeholders
            ps.setString(1,teacher.getTeacherNum());
            ps.setString(2,teacher.getTeacherPwd());
            ps.setString(3,teacher.getTeacherName());
            ps.setString(4,teacher.getTeacherTel());
            ps.setInt(5,teacher.getTeacherColId());

            int i = ps.executeUpdate(sql);//Number of rows affected by the update operation on the data

            b = i>0?true:false;

            //close the connection
            connection.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        return b;
    }

Process: check the SQL statements for many times and no errors are found. The test data is also checked for many times to confirm that the rules formulated during table creation are met;

Result.

int i = ps.executeUpdate(sql); because in this code, pass in the sql statement again to report an error, modified to
int i = ps.executeUpdate();; after the error is resolved.
Reason: The sql statement has already been passed in when the compile operation is executed, so it does not need to be passed in again when it is executed.