小编典典

JDBC:抱怨无效符号,但看起来还不错

java

我必须使用JDBC写入数据库(不选择休眠/ ibatis),而我的数据库是Oracle 11g。

我创建以下查询: insert into user(user_id, username, age, creation_ts) values(seq_userid.NEXTVAL, 'Jack', 19,TO_TIMESTAMP('14/12/2010 15/09/46', 'DD/MM/RR HH24/MI/SS'));

但是我的statetement.execeuteUpdate(above sql)。生成无效的符号异常。但是当我在松鼠中执行查询时,它就被提交了。有人知道为什么会这样吗?


Edit:
user table:
id: number : not null
username varchar2(30) not null
age number(10) not null
creation_ts timestamp not null

Error:
ORA-00911: invalid character

Java snippet:
try
        {       
            DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
            String url = "privatized";
            Connection conn = DriverManager.getConnection(url, "username", "password");

            Statement st = conn.createStatement();

            Format formatter = new SimpleDateFormat(dateTimeFormatString);
            String formattedDate = formatter.format(Calendar.getInstance(TimeZone.getDefault()).getTime());

            StringBuilder insertQuery = new StringBuilder("insert into user(user_id, username, age, creation_ts) values(seq_userid.NEXTVAL,");
                insertQuery.append(username);
                insertQuery.append(",");
            insertQuery.append(age);
            insertQuery.append(",TO_TIMESTAMP('");
            insertQuery.append(formattedDate);
            insertQuery.append("', 'DD/MM/RR HH24/MI/SS'));");
            System.err.println(insertQuery.toString());
            st.executeUpdate(insertQuery.toString());

            conn.close();
        } catch (SQLException ex){
            System.err.println(ex.getMessage());
            System.err.println(ex.getCause().toString());
            ex.printStackTrace();
            System.out.println("=========================================");
        } catch(Exception ex) {
            System.err.println(ex.getMessage());
        }

阅读 165

收藏
2020-11-30

共1个答案

小编典典

正如我在上面的评论中所述,问题可能是由于SQL语句末尾的多余分号引起的。看这篇文章

您可能还需要查看PreparedStatments,以使您的生活更轻松。这是上述代码的粗略翻译。我已经离开了一些部分,并且最有可能出现错误。

String query = "insert into user(user_id, username, age, creation_ts) values(?,?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(query);
... //fill in all your parameters
pstmt.setTimestamp(4, new Timestamp(System.currentTimeMillis()) );
... //execute here
2020-11-30