JDBC:抱怨无效符号但似乎没问题 [英] JDBC: complains about invalid sign but seems fine

问题描述

我必须使用 JDBC 来写入数据库(hibernate/ibatis 不是一个选项)并且我的数据库是 Oracle 11g.

I have to use JDBC to write to a database (hibernate/ibatis is not an option) and my database is 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).生成无效符号异常.但是当我在 squirrel 中执行查询时，它被提交得很好.有谁知道为什么会这样?

However my statetement.execeuteUpdate(above sql). generates an invalid sign exception. But when I perform the query in squirrel it gets commited just fine. Does anyone know why this is happening?

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());
        }

推荐答案

正如我在上面的评论中所说，问题可能是由于 SQL 语句末尾的额外分号造成的.请参阅此文章

As I put in a comment above, the issue could be due to the extra Semicolon at the end of your SQL statement. see this article

您可能还想查看 PreparedStatments 让您的生活更轻松.这是您上述代码的粗略翻译.我留下了一些部分，很可能有错误.

You may also want to look at PreparedStatments to make your life easier. Here would be a rough translation of your above code. I have left some parts, and there are most likely errors.

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

这篇关于JDBC:抱怨无效符号但似乎没问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！