加载.csv文件时如何将当前系统时间戳插入db2数据库基列
下面的类会将 .csv 导入数据库表.它工作正常，现在我需要更新同一个表中需要获取当前系统时间戳的另一列当该程序在数据库表的相应列中执行时得到更新.
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.
示例:在 Db2 表中，主题列是:英语社会数学时间戳
Example: In Db2 table Subjects columns are: Eng Social Maths TimeStamp
在 .CSV 文件中只有 3 列 Eng Social Maths .
In .CSV file has only 3 columns Eng Social Maths .
当 .csv 文件被导入(使用上述程序)到 db2 时，所有列都会更新，除了 TimeStamp.时间戳用于记录 .csv 文件何时上传到表格.那么，如何同时使用当前系统时间戳更新 TimeStamp 列.?请帮忙
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
公共类 CSVLoader {
public class 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;
}
}
推荐答案
private static final
String SQL_INSERT = "INSERT INTO OPPTYMGMT.${table}
(${keys}, my_timestamp_column) VALUES(${values}, current_timestamp)";
相关文章