JavaWeb错误:You have an error in your SQL syntax； check the manual that corresponds to your MySQL

原创已于 2024-01-25 16:12:40 修改 · 818 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #sql #数据库 #java

于 2022-10-11 18:38:54 首次发布

物联网同时被 2 个专栏收录

11 篇文章

订阅专栏

Java

2 篇文章

订阅专栏

本文通过一个具体的例子，展示了在Java中如何正确地构造SQL插入语句，并对比了错误的做法。作者强调了使用引号来包裹字符串的重要性，这对于避免SQL语法错误至关重要。

在这里插入图片描述

正确代码

// 添加用户的操作
    public boolean insert(User user) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 获得数据的连接
            conn = JDBCUtils.getConnection();
            // 获得Statement对象
            stmt = conn.createStatement();
            // 初始化时间
            String create_time=accesstime();
//            String create_time="NULL NULL ";
            System.out.println("CREATE_TIME0:"+create_time);
            //初始化参数
            String CREATE_TIME_real=create_time;
            System.out.println("CREATE_TIME1:"+CREATE_TIME_real);
            String STARTTIME_real=create_time;
            String ENDTIME_real=create_time;
            String SITE_real="0";
            String PAREA_real="0";
            String LINE_real="0";
            String OP_real="0";
            String EQP_ID_real="0";
            String UNIT_ID_real="0";
            String PORT_ID_real="0";
            String LOT_NO_real="0";
            String MODEL_NO_real="0";
            String PART_NO_real="0";
            String PARAMETER_real="0";
            String PARAMETER_DESC_real="0";
            String PARAMVALUE_real="0";
            String IS_CONFIG_DATA_real="N";
            String IS_REAL_DATA_real="Y";
            // 发送SQL语句
//            String sql = "INSERT INTO s02 (CREATE_TIME,STARTTIME,ENDTIME,SITE,PAREA,LINE,OP,EQP_ID,UNIT_ID,PORT_ID, LOT_NO, MODEL_NO,PART_NO,PARAMETER,PARAMETER_DESC,PARAMVALUE,IS_CONFIG_DATA,IS_REAL_DATA)"+ " VALUES(" + CREATE_TIME_real+"," +STARTTIME_real,ENDTIME_real,SITE_real+ "," + PAREA_real,LINE_real,OP_real,EQP_ID_real,UNIT_ID_real,PORT_ID_real, LOT_NO_real,MODEL_NO_real,PART_NO_real,PARAMETER_real + "," +PARAMETER_DESC_real,PARAMVALUE_real + ","+ IS_CONFIG_DATA_real,IS_REAL_DATA_real + ")";
            String sql = "insert into s02 values ("+"\'"+CREATE_TIME_real+"\'"+","+"\'"+STARTTIME_real+"\'"+","+"\'"+ ENDTIME_real+"\'"+","+SITE_real+","+PAREA_real+","+LINE_real+","+OP_real+","+EQP_ID_real+","+UNIT_ID_real+","+PORT_ID_real+","+LOT_NO_real+","+MODEL_NO_real+","+PART_NO_real+","+PARAMETER_real+","+PARAMETER_DESC_real+","+PARAMVALUE_real+","+"\'"+IS_CONFIG_DATA_real+"\'"+","+"\'"+IS_REAL_DATA_real+"\'"+")";
            System.out.println("sql:"+sql);
            boolean num=stmt.execute(sql);
            System.out.println("num:"+num);

//            stmt.executeUpdate(sql);
//            int num = stmt.executeUpdate(sql);
//            if (num > 0) {
//                return true;
//            }
//            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, stmt, conn);
        }
        return false;
    }

错误代码

          // 发送SQL语句
//            String sql = "INSERT INTO s02 (CREATE_TIME,STARTTIME,ENDTIME,SITE,PAREA,LINE,OP,EQP_ID,UNIT_ID,PORT_ID, LOT_NO, MODEL_NO,PART_NO,PARAMETER,PARAMETER_DESC,PARAMVALUE,IS_CONFIG_DATA,IS_REAL_DATA)"+ " VALUES(" + CREATE_TIME_real+"," +STARTTIME_real,ENDTIME_real,SITE_real+ "," + PAREA_real,LINE_real,OP_real,EQP_ID_real,UNIT_ID_real,PORT_ID_real, LOT_NO_real,MODEL_NO_real,PART_NO_real,PARAMETER_real + "," +PARAMETER_DESC_real,PARAMVALUE_real + ","+ IS_CONFIG_DATA_real,IS_REAL_DATA_real + ")";