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).生成无效的符号异常. 但是当我在松鼠中执行查询时，它就被提交了. 有人知道为什么会这样吗?

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屋！