oracle ora00957,ORA-00957：重复列名，同时将数据从Excel到Oracle DB

号码，简短说明，优先级，状态，类别，子类别，分辨率错误，分辨率类型，待处理原因，事件结束时间，事件开始时间，活动，已关闭，评论和工作记录，已创建，创建者，影响，事件的配置项目，分配组，打开，更新，打开开始时间，事件的状态，由开业，分配组，解决时间，解决，解决的日期，时间分辨，在级别，严重性，LOB，接触式解决

private static SimpleDateFormat inputSdf=new SimpleDateFormat("dd-MMM-yyyy");//MM/dd/yyyy hh:mm:ss aaa

private static SimpleDateFormat outputSdf=new SimpleDateFormat("yyyy-MM-dd");

private static String query=null;

private static String checkQuery=null;

private static String delQuery=null;

private static String maxIdQuery=null;

static{

String tableName="SUPPORT_NEW";

StringBuffer buffer=new StringBuffer();

buffer.append("INSERT INTO "+tableName+" (");

buffer.append(" SPRT_ID, SPRT_NBR, SPRT_DESC, PRTY_TXT, ST_TXT, ");

buffer.append(" CTGY_TXT, SCTGY_TXT, CONF_ITEM_TXT, ASGMT_GRP_TXT, OPENED_TMSTP, ");

buffer.append(" UPDATED_TMSTP, OPENED_BY_TXT, RES_NOTE_TXT, RES_ERROR_TXT, RES_TYPE_TXT, ");

buffer.append(" PEND_RESN_TXT, EVENT_END_TM, EVENT_START_TM, ACT_TXT, ");

buffer.append(" CLOSED_TMSTP, COM_AND_WORK_NOTE_TXT, CREATED_TM, CREATED_BY_TXT, IMPACT_TXT, ");

buffer.append(" INCDT_START_TM, INCDT_ST_TXT, OPEN_BY_GRP_TXT, RESLV_TM, RESLV_DT, "); //RESOLVED=RESLV_DT

buffer.append(" RESLV_TM, RESLV_AT_LEVEL_TXT, SVRTY_TXT, LOB_TXT, "); //Resolved_Date=null

buffer.append(" SLA_TXT, MONIT_TYPE_TXT ");

buffer.append(") VALUES (");

buffer.append(" ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?, ?,?");

buffer.append(") ");

query=buffer.toString();

checkQuery = " SELECT SPRT_ID FROM "+tableName+" WHERE SPRT_NBR = ? ";

delQuery = " DELETE FROM "+tableName+" WHERE SPRT_NBR = ? ";

maxIdQuery = " SELECT MAX(SPRT_ID) FROM "+tableName;

}

public static void insertToDB(String[][] data) throws Exception{

Connection dbConnection=null;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

dbConnection = DriverManager.getConnection( "jdbc:oracle:thin:@192.168.7.39:1521:ORCL","DASHBOARD","DASHBOARD");

PreparedStatement preparedStatement = dbConnection.prepareStatement(query);

PreparedStatement checkStatement = dbConnection.prepareStatement(checkQuery);

PreparedStatement delStatement = dbConnection.prepareStatement(delQuery);

PreparedStatement maxIdStatement = dbConnection.prepareStatement(maxIdQuery);

ResultSet rs1;

ResultSet rs2;

long id=0;

for (int i=1;i

String col[]=data[i];

System.out.print("\n Preparing Record "+i+"@= PDMS_ID="+col[0]+";id="+id);

checkStatement.setString(1, col[0]);

rs1=checkStatement.executeQuery();

if(rs1.next()){

delStatement.setString(1, col[0]);

delStatement.executeUpdate();

System.out.print("=>ID Exists hence updating ");

}else{

System.out.print("=>Fresh record ");

}

System.out.println();

rs2=maxIdStatement.executeQuery();

while(rs2.next()){

id=rs2.getLong(1);

}

id++;

preparedStatement.setLong(1, id); //id

preparedStatement.setString(2, col[0]); //pdsm_number

((OraclePreparedStatement) preparedStatement).setStringForClob(3, col[1]);//Description

preparedStatement.setString(4, col[2]); //Priority

preparedStatement.setString(5, col[3]); //State

preparedStatement.setString(6, col[4]); //Category

preparedStatement.setString(7, col[5]); //Subcategory

preparedStatement.setString(8, col[6]); //Configuration_item

preparedStatement.setString(9, col[7]); //Assignment_group

//preparedStatement.setDate(10, (java.sql.Date)sdf.parse(col[9])); //Opened

((OraclePreparedStatement) preparedStatement).setDate(10, formateDate(col[8]));

((OraclePreparedStatement) preparedStatement).setDate(11, formateDate(col[9])); //Updated

preparedStatement.setString(12, col[10]); //Opened_by

((OraclePreparedStatement)preparedStatement).setStringForClob(13, col[11]);//Resolution_notes

preparedStatement.setString(14, col[12]); //Resolution_Error

preparedStatement.setString(15, col[13]); //Resolution_Type

preparedStatement.setString(16, col[14]); //Pending_Reason

((OraclePreparedStatement) preparedStatement).setDate(17, formateDate(col[15]));; //Event_End_Time

((OraclePreparedStatement) preparedStatement).setDate(18, formateDate(col[16]));; //Event_Start_Time

//preparedStatement.setString(19, col[18]); //Action

preparedStatement.setString(19, col[17]); //Active

((OraclePreparedStatement) preparedStatement).setDate(20, formateDate(col[18]));; //Closed

((OraclePreparedStatement)preparedStatement).setStringForClob(21, col[19]);//Comments_and_Work_notes

((OraclePreparedStatement) preparedStatement).setDate(22, formateDate(col[20]));; //Created

preparedStatement.setString(23, col[21]); //Created_by

preparedStatement.setString(24, col[22]); //Impact

((OraclePreparedStatement) preparedStatement).setDate(25, formateDate(col[23])); //Incident_Start_Time

preparedStatement.setString(26, col[24]); //Incident_state

preparedStatement.setString(27, col[25]); //Opened_by_Assignment_Group

preparedStatement.setString(28, col[26]); //Resolve_time

((OraclePreparedStatement) preparedStatement).setDate(29, formateDate(col[27])); //Resolved

//preparedStatement.setString(30, null); //Resolved_Date

preparedStatement.setString(30, col[29]); //Resolved_Time

preparedStatement.setString(31, col[30]); //Resolved_at_Level

preparedStatement.setString(32, col[31]); //Severity

if("APAC Supply Chain".equalsIgnoreCase(col[32].trim())){ //LOB

preparedStatement.setString(33, "APAC");

}else{

preparedStatement.setString(33, "US");

}

//SLA

if(col[15]!=null && !col[15].trim().equals("") && col[23]!=null && !col[23].trim().equals("")){

long interval=formateDate(col[15]).getTime()- formateDate(col[23]).getTime();

long p1 = 60*60*1000;//P1 – 1 Hour

long p2 = 2*60*10*1000;//P2 – 2 Hours

long p3 = 24*60*10*1000;//P3 – 24 Hours

long p4 = 21*24*60*10*1000;//P3 – 21 days

if("1-Critical".equalsIgnoreCase(col[2])){//P1

preparedStatement.setString(34, (interval>p1)?"BREACHED":"ADHERED");

}else if("2-High".equalsIgnoreCase(col[2])){//P2

preparedStatement.setString(34, (interval>p2)?"BREACHED":"ADHERED");

}else if("3-Moderate".equalsIgnoreCase(col[2])){//P3

preparedStatement.setString(34, (interval>p3)?"BREACHED":"ADHERED");

}else if("4-Low".equalsIgnoreCase(col[2])){//P4

preparedStatement.setString(34, (interval>p4)?"BREACHED":"ADHERED");

}

}else{

preparedStatement.setString(34, "ADHERED");

}

if(col[33].equalsIgnoreCase("Monitoring")){//MONITOR_TYPE

preparedStatement.setString(35, "System Generated");

}else{

preparedStatement.setString(35, "User Generated");

}

preparedStatement.execute();

/*preparedStatement.addBatch();

if (i % 100 == 0 || (i+1) == data.length) {

System.out.println("Batch update @"+i);

preparedStatement.executeBatch(); // Execute every 100 items.

}*/

}

} catch (Exception ex) {

throw ex;

} finally{

try{

if(dbConnection!=null && !dbConnection.isClosed()){

dbConnection.close();

}

}catch(Exception ex){

ex.printStackTrace();

}

}

}

public static void main(String[] args) {

try{

System.out.println("Reading Data from XLS");

List xlsData=ReadXLGeneric.readExcelData("D:/DASHBOARD_WORKSPACE/data/latest-pdsm.xlsx");

//"D:/Duke/0/projects/vmo/dbDump/PDSMDumps/PDSMData/Feb.xlsx

System.out.println("Inserting into DB");

insertToDB(xlsData.get(0));

}catch(Exception e){

e.printStackTrace();

}

}

public static java.sql.Date formateDate(String input)throws Exception{

try {

if(input!=null && !input.equals("") && !input.equals("UNDEFINED")){

return java.sql.Date.valueOf(outputSdf.format(inputSdf.parse(input)));

}

return null;

} catch (ParseException e) {

throw e;

}

}

public static List readExcelData(String fileName) {

List>> sheetList=new ArrayList>>();

List> strTable=new ArrayList>();

List strList=new ArrayList();

Cell cell=null;

try {

FileInputStream fis = new FileInputStream(fileName);

Workbook workbook = null;

if(fileName.toLowerCase().endsWith("xlsx")){

workbook = new XSSFWorkbook(fis);

}else if(fileName.toLowerCase().endsWith("xls")){

workbook = new HSSFWorkbook(fis);

}else{

throw new IllegalArgumentException("Invalid formate should be xls or xlsx");

}

//loop through each of the sheets

for(int i=0; i < workbook.getNumberOfSheets(); i++){

Sheet sheet = workbook.getSheetAt(i);

Iterator rowIterator = sheet.iterator();

strTable=new ArrayList>();

while (rowIterator.hasNext())

{

Row row = rowIterator.next();

Iterator cellIterator = row.cellIterator();

strList=new ArrayList();

while (cellIterator.hasNext())

{

cell=cellIterator.next();

switch (cell.getCellType()) {

case Cell.CELL_TYPE_BLANK:

strList.add("");

break;

case Cell.CELL_TYPE_STRING:

strList.add(cell.getStringCellValue());

break;

case Cell.CELL_TYPE_NUMERIC:

if(cell.toString().contains("-")){

strList.add(cell.toString());

}else{

strList.add(Double.toString(cell.getNumericCellValue()));

}

break;

case Cell.CELL_TYPE_FORMULA:

//System.out.println("Formula is " + cell.getCellFormula());

switch(cell.getCachedFormulaResultType()) {

case Cell.CELL_TYPE_NUMERIC:

//System.out.println("Last evaluated as: " + cell.getNumericCellValue());

strList.add(Double.toString(cell.getNumericCellValue()));

break;

case Cell.CELL_TYPE_STRING:

// System.out.println("Last evaluated as \"" + cell.getRichStringCellValue() + "\"");

strList.add(cell.getRichStringCellValue().toString());

break;

}

break;

default:

strList.add("UNDEFINED");

}

}

strTable.add(strList);

}

sheetList.add(strTable);

}

fis.close();

} catch (IOException e) {

e.printStackTrace();

}

return getAsSheetList(sheetList);

}

在运行这个程序，我得到以下异常

下面是数据要传送的数据库模型。

如何解决这个问题？

2017-09-26

Akhilesh