問題描述
下面的類會將 .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)";
這篇關于加載.csv文件時如何將當前系統時間戳插入db2數據庫基列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！