問題描述
下面的類會(huì)將 .csv 導(dǎo)入數(shù)據(jù)庫表.它工作正常，現(xiàn)在我需要更新同一個(gè)表中需要獲取當(dāng)前系統(tǒng)時(shí)間戳的另一列當(dāng)該程序在數(shù)據(jù)庫表的相應(yīng)列中執(zhí)行時(shí)得到更新.
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 表中，主題列是:英語社會(huì)數(shù)學(xué)時(shí)間戳
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 .
當(dāng) .csv 文件被導(dǎo)入(使用上述程序)到 db2 時(shí)，所有列都會(huì)更新，除了 TimeStamp.時(shí)間戳用于記錄 .csv 文件何時(shí)上傳到表格.那么，如何同時(shí)使用當(dāng)前系統(tǒng)時(shí)間戳更新 TimeStamp 列.?請(qǐng)幫忙
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)";
這篇關(guān)于加載.csv文件時(shí)如何將當(dāng)前系統(tǒng)時(shí)間戳插入db2數(shù)據(jù)庫基列的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！