在通用prepareStatement中处理日期 [英] Handle Dates in generic preparedStatement
问题描述
构建通用的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屋！
