在通用prepareStatement中处理日期 [英] Handle Dates in generic preparedStatement

查看:312
本文介绍了在通用prepareStatement中处理日期的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

构建通用的preparedStatement时遇到麻烦:我有8个SQL表,它们都以相同的方式进行操作,因此我想构建一个唯一的管理器,该管理器可以插入/从8个表中选择.

为此,每个表都有一个描述符,该描述符可以在插入时提供表的字段,名称和值的数组.

在管理器中,要插入的准备好的语句具有以下形式:

"INSERT INTO " + table_name + " VALUES (?)"

然后,我用类似的东西填补空白

myPreparedStatement.setString(1, values.getAllValues());

getAllValues()方法必须返回一个包含每个字段的字符串,例如'This','Is',3,'example'. 我对字符串和数字没有问题,但是我不能在这些值中添加任何日期...

以2008年9月3日为例,我使用了以下格式: 2008-09-03, 03/08/09, 080903, 03092018,但全部失败.从我这里到那里看到的,"yyMMdd"格式似乎是最好的选择,但是我有错误:

"java.sql.SQLDataException: ORA-01843: not a valid month"

我不知道为什么...以前有人遇到过这个问题吗?

我知道这里有很多关于在数据库中插入日期的文章,但是他们都使用

preparedStatement.setDate(pos, Date);

声明,由于日期不在我所有表中的同一位置,所以我不能这样做.

按照评论中的要求,这是一个最小的示例,可以重现我正在尝试做的事情.如果您也想复制,我让您处理连接和数据库设置:

public class Sample {

public void saveAll() throws ServiceException {

    Connection c = null;
    PreparedStatement ps = null;
    String sql = "INSERT INTO " + getTableName() +" VALUES (?)";

    try {
        c = getConnection();
        c.setAutoCommit(false);

        batch = c.prepareStatement(sql);
        batch.setString(getAllFieldValues());

        int res = batch.executeUpdate();
        c.commit();

    } catch (BatchUpdateException b) {
        throw new ServiceException("Erreur lors de l'exécution du batch", b);
    } catch (SQLException s) {
        throw new ServiceException("Impossible de sauvegarder les beans en base.", s);
    } finally {
        getManager().close(batch);
        freeConnection(c);
    }
}

public String getAllFieldValues() {
        return "'Hello', 'World', 42, '171228'"; 
}

public String getTableName() {
    return "myTableName";
}

}

解决方案

在JDBC中没有generic preparedStatement这样的东西.要在表T中插入四列,您必须使用

 INSERT into T (col1,col2,col3,col4) values (?,?,?,?)

可能会省略第一个带有列名的列表,但这是一个不好的做法,因为您相信可能会更改的表的实际列.

仅使用

 INSERT into T  values (?,?,?,?)

工作正常,直到有人通过添加或删除列来修改表,此后将失败.

必须使用setXXX方法对所有绑定变量进行额外设置,并使用适当的类型和索引以1开始的列.

stmt.setInt(1,100)
stmt.setString(2,'xxx') 

I am facing troubles when building a generic preparedStatement : I have 8 SQL Tables, which are all manipulated the same way, so I'd like to build a unique manager which could insert into / select from any of the 8 tables.

To do so, each table has a descriptor, which can provide the fields of a table, its name and the array of values when inserting.

In the manager, the prepared statement to insert is of the following form :

"INSERT INTO " + table_name + " VALUES (?)"

Then, I fill the gap with something like

myPreparedStatement.setString(1, values.getAllValues());

the getAllValues() method must return a string which holds every fields, like " 'This', 'Is', 3, 'example' ". I have no problem with strings and numbers, but I can't add any date in those values...

Using September 3rd, 2008 as example, I used the following formats : 2008-09-03, 08-09-03, 080903, 03092018, but all fail. "yyMMdd" format seemed like the best option from what I saw here and there, but I have the error :

"java.sql.SQLDataException: ORA-01843: not a valid month"

And I have no idea why... has anyone faced this issue before ?

I know there are lots of posts here that talks about inserting dates in database, but they all use the

preparedStatement.setDate(pos, Date);

Statement, and I can't do that since the dates aren't in the same position in all of my tables.

EDIT :

As asked in the comment, here is a minimal sample that reproduce what I'm trying to do. If you want to reproduce as well, I let you handle the connection and database setup :

public class Sample {

public void saveAll() throws ServiceException {

    Connection c = null;
    PreparedStatement ps = null;
    String sql = "INSERT INTO " + getTableName() +" VALUES (?)";

    try {
        c = getConnection();
        c.setAutoCommit(false);

        batch = c.prepareStatement(sql);
        batch.setString(getAllFieldValues());

        int res = batch.executeUpdate();
        c.commit();

    } catch (BatchUpdateException b) {
        throw new ServiceException("Erreur lors de l'exécution du batch", b);
    } catch (SQLException s) {
        throw new ServiceException("Impossible de sauvegarder les beans en base.", s);
    } finally {
        getManager().close(batch);
        freeConnection(c);
    }
}

public String getAllFieldValues() {
        return "'Hello', 'World', 42, '171228'"; 
}

public String getTableName() {
    return "myTableName";
}

}

解决方案

There is no such thing as generic preparedStatement in JDBC. To insert four columns in table T you must use

 INSERT into T (col1,col2,col3,col4) values (?,?,?,?)

You may ommit the first list with the column names, but this is a bad practice as you trust on the actual columns of the table that may change.

Using only

 INSERT into T  values (?,?,?,?)

work fine until somebody modifies the table by adding or dropping a column and will fail afterwards.

All bind variables must be set extra with the setXXX method with appropriate type and index of the column starting with 1.

stmt.setInt(1,100)
stmt.setString(2,'xxx') 

这篇关于在通用prepareStatement中处理日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆