Java 加载 .csv 文件时如何将当前系统时间戳插入到 db2 数据库基本列中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18225333/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert the current system timestamp into db2 database base column when .csv file is loaded
提问by 123HIS
The below class will import the .csv into database table.it is working fine and Now i need to update another column in same table where current system timestamp needs to get get updated when this program is executed in the respective column of the database table.
下面的类将 .csv 导入数据库表。它工作正常，现在我需要更新同一表中的另一列，当在数据库表的相应列中执行该程序时，需要更新当前系统时间戳。
Example: In Db2 table Subjects columns are: Eng Social Maths TimeStamp
示例：在 Db2 表中，Subjects 列是：Eng Social Maths TimeStamp
In .CSV file has only 3 columns Eng Social Maths .
在 .CSV 文件中只有 3 列 Eng Social Maths 。
When .csv file is imported (using above program) to db2 all the columns are updated except TimeStamp. Timestamp is inculded to tack the when .csv file is uploaded to table. So, how to Update the TimeStamp column with Current System timestamp simultaneously .? Please help
当 .csv 文件被导入（使用上面的程序）到 db2 时，除了时间戳之外的所有列都会更新。时间戳被包含在 .csv 文件上传到表的时间。那么，如何同时使用当前系统时间戳更新 TimeStamp 列。？请帮忙
public class CSVLoader {
公共类 CSVLoader {
private static final
String SQL_INSERT = "INSERT INTO OPPTYMGMT.${table}
(${keys}) VALUES(${values})";
private static final String TABLE_REGEX = "\$\{table\}";
private static final String KEYS_REGEX = "\$\{keys\}";
private static final String VALUES_REGEX = "\$\{values\}";
private Connection connection;
private char seprator;
public CSVLoader(Connection connection) {
this.connection = connection;
//Set default separator
this.seprator = ',';
}
public void loadCSV(String csvFile, String tableName) throws Exception {
CSVReader csvReader = null;
if(null == this.connection) {
throw new Exception("Not a valid connection.");
}
try {
csvReader = new CSVReader(new FileReader(csvFile), this.seprator);
} catch (Exception e) {
e.printStackTrace();
throw new Exception("Error occured while executing file. "
+ e.getMessage());
}
String[] headerRow = csvReader.readNext();
if (null == headerRow) {
throw new FileNotFoundException(
"No columns defined in given CSV file." +
"Please check the CSV file format.");
}
String questionmarks = StringUtils.repeat("?,", headerRow.length);
questionmarks = (String) questionmarks.subSequence(0, questionmarks
.length() - 1);
String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName);
query = query
.replaceFirst(KEYS_REGEX, StringUtils.join
(headerRow, ","));
query = query.replaceFirst(VALUES_REGEX, questionmarks);
System.out.println("Query: " + query);
String[] nextLine;
Connection con = null;
PreparedStatement ps = null;
try {
con = this.connection;
con.setAutoCommit(false);
ps = con.prepareStatement(query);
final int batchSize = 1000;
int count = 0;
Date date = null;
while ((nextLine = csvReader.readNext()) != null) {
System.out.println( "inside while" );
if (null != nextLine) {
int index = 1;
for (String string : nextLine) {
date = DateUtil.convertToDate(string);
if (null != date) {
ps.setDate(index++, new java.sql.Date(date
.getTime()));
} else {
ps.setString(index++, string);
System.out.println( "string" +string);
}
}
ps.addBatch();
}
if (++count % batchSize == 0) {
ps.executeBatch();
}
}
ps.executeBatch(); // insert remaining records
con.commit();
} catch (Exception e) {
con.rollback();
e.printStackTrace();
throw new Exception(
"Error occured while loading data
from file to database."
+ e.getMessage());
} finally {
if (null != ps)
ps.close();
if (null != con)
con.close();
System.out.println("csvReader will be closed");
csvReader.close();
}
}
public char getSeprator() {
return seprator;
}
public void setSeprator(char seprator) {
this.seprator = seprator;
}
}
采纳答案by mustaccio
private static final
String SQL_INSERT = "INSERT INTO OPPTYMGMT.${table}
(${keys}, my_timestamp_column) VALUES(${values}, current_timestamp)";
回答by Rakesh KR
回答by AngocA
When using the expression current system, it could mean the system where Java is executed OR the system where DB2 is executed.
当使用表达当前系统时，它可以表示执行 Java 的系统或执行 DB2 的系统。
You can get the Java date with the previous answer.
您可以使用上一个答案获取 Java 日期。
For the DB2 date you should provide this in the insert statement
对于 DB2 日期，您应该在插入语句中提供它
current date
For example, for a table
例如，对于一个表
create table t1 (date date)
insert into t1 values (current date)
- Current date special register http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0005870.html
- 当前日期专用寄存器http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0005870.html
Finally, if you really want to import data into DB2, it is better to use the IMPORT or LOAD commands, instead of recreate your own tool.
最后，如果您真的想将数据导入 DB2，最好使用 IMPORT 或 LOAD 命令，而不是重新创建您自己的工具。
