java - ORA-00917 : missing comma - Insert query failed

Question

我正在尝试在 JSP 页面中创建插入查询，如下所示

    try 
    {
        Class.forName("oracle.jdbc.driver.OracleDriver");
    } 
    catch (ClassNotFoundException e) 
    {
        e.printStackTrace();
    }

    try 
    {
        connection = DriverManager.getConnection(
        "jdbc:oracle:thin:@localhost:1521:" + "XE", "hr","hr");

        if (connection != null) 
        {
            statement = connection.createStatement();

            String q2 = "INSERT INTO HR.tweets (";
            q2 = q2 + "DATE_TIME,USER_NAME,TWEET_BEFORE,TWEET_AFTER)";
            q2 = q2 + "VALUES (";
            q2 = q2 + "(select SYSDATE from dual),";
            q2 = q2 + "'" + tweet.getUser().getScreenName() + "'" + ",";
            q2 = q2 + "'" + tweet.getText() + "'" +",";
            q2 = q2 + "'" + finalstring + "')";

            statement.execute(q2);   
            statement.close();
            connection.close();
        }
    } 
    catch (SQLException e) 
    {
    e.printStackTrace();
    } 

在statement.execute(q2)处，我收到ORA-00917:缺少逗号错误。

以下查询是在代码中创建的:

INSERT INTO HR.tweets (DATE_TIME,USER_NAME,TWEET_BEFORE,TWEET_AFTER)VALUES ((select SYSDATE from dual),'Dannazxcv','RT @HugotInhinyero: Wish we could turn back time to the good old days. When our mama sings us to sleep but now we're stressed out.🎶🎶
#engin…','hugotinhinyero turn back time good days. mama sing sleep we're stress out.   engin'  )

请帮助我。

Answer 1

您的 SQL 插入存在语法错误，因为您的参数之一包含 ':

'hugotinhinyero turn back time good days. mama sing sleep we're stress out. engin'

要避免此类错误，请不要手动构建 SQL 字符串，而是使用 PreparedStatement 和参数:

String insert = "INSERT INTO HR.tweets (DATE_TIME,USER_NAME,TWEET_BEFORE,TWEET_AFTER) " + 
" VALUES ((select SYSDATE from dual),?,?,?)";
PreparedStatement stmt = connection.prepareStatement(insert);
stmt.setParameter(1, tweet.getUser().getScreenName());
stmt.setParameter(2, tweet.getText());
stmt.setParameter(3, finalstring);
stmt.executUpdate();