使用java将csv文件导入oracle数据库

I am getting the following error

我收到以下错误

  Java.lang.ArrayIndexOutOfBoundsException: 0
  at oracle.jdbc.driver.OracleSql.main(OracleSql.java:1614)

I am using eclipse indigo and oracle 10g.how can i fix this problem.can anyone help me ?

我正在使用eclipse indigo和oracle 10g。我可以解决这个问题。任何人都可以帮助我吗?

i want to load a csv file into oracle database.. i've created a table named zt with three columns (id, s_date, data) CSV data contains as follows:

我想将一个csv文件加载到oracle数据库中..我创建了一个名为zt的表,其中包含三列(id,s_date,data),CSV数据包含如下:

   a1,2015-04-15 17:40:20.0,18.5786    
   a2,2015-04-15 16:20:59.0,16.7868   
   a3,2015-03-15 16:20:59.0,16.51689    
   a4,2015-04-16 16:20:55.0,24.789028  
   a5,2015-02-15 17:55:59.0,28.784145

code

import java.io.FileReader;    
import java.sql.Connection;    
import java.sql.DriverManager;    
import java.sql.PreparedStatement;
import java.sql.Statement;
import au.com.bytecode.opencsv.CSVReader;

public class ImportTest {
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        readCsv();
    }

    public static void readCsv() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection conn     =DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/xe","system","arry");
            PreparedStatement pstmt =conn.prepareStatement("Insert into zt values(?,?,?)");

            CSVReader reader = new CSVReader(new FileReader("D:\\zzzzzzzz.csv"), ','); 
            String[] nextLine;
            int i = 0;
            while((nextLine = reader.readNext()) != null) {
                i++;
                pstmt.setString(1,nextLine[0]);
                pstmt.setString(2,nextLine[1]);
                pstmt.setDouble(3,Double.parseDouble(nextLine[2]));
            }

            pstmt.close();
            conn.commit();
            conn.close();
        } catch(Exception e) {
            e.printStackTrace();       
        }
    }
}

3 个解决方案

#1

For starters you have

对于初学者,你有

pstmt.setString(1,nextLine[0]);
pstmt.setString(1,nextLine[1]);
pstmt.setDouble(2,Double.parseDouble(nextLine[2]))

Note you have repeated parameter one twice and if exception is coming from database then this is very likely to be the cause.

请注意,您重复参数一次两次,如果异常来自数据库,则很可能是原因。

more over

IndexOutOfBoundsException in Java is a runtime exception .As stated in the doc

Java中的IndexOutOfBoundsException是一个运行时异常。如文档中所述

Thrown to indicate that an index of some sort (such as to an array, to a string, or to a vector) is out of range. Applications can subclass this class to indicate similar exceptions.

抛出以指示某种索引(例如数组,字符串或向量)超出范围。应用程序可以子类化此类以指示类似的异常。

It is obvious that you are accessing the index of the arrays without checking first. Always a dangerous thing to do.

很明显,您在不先检查的情况下访问数组的索引。总是一件危险的事情。

I also agree it does seem the you CSV file is not correct but either way you problem is not limited to that and you should safeguard against it. I have added code to show a basic safeguard

我也同意你的CSV文件似乎不正确,但无论哪种方式你的问题不仅限于此,你应该防范它。我添加了代码以显示基本保护措施

Just put a check for length in your while loop like below

只需在下面的while循环中检查长度

 while((nextLine = reader.readNext()) != null){
             i++;
                // Remember length 3 = index 2
                if (nextLine.length == 3){
                    pstmt.setString(1,nextLine[0]);
                    //I have changed it to 2 from 1 
                    pstmt.setString(2,nextLine[1]);
                    pstmt.setDouble(2,Double.parseDouble(nextLine[2]));
             }
    }

#2

That is quite simple. You are using CSVReader and giving it comma (',') as a field separator, however the 4 sample lines that you have showed us do not contain any commas.

这很简单。您正在使用CSVReader并将其逗号(',')作为字段分隔符,但是您向我们显示的4个示例行不包含任何逗号。

Such a line 'a1 2015-04-15 17:40:20.0 18.5786' splitted on ',' will be just 1 string containing the entire line.

这样一条'a1 2015-04-15 17:40:20.0 18.5786'分裂','将只包含整行的1个字符串。

Your file is not a CSV strictly speaking.

严格来说,您的文件不是CSV。

#3

It seems you're trying to run a class within the Oracle JDBC driver JAR rather than your code.

您似乎正在尝试在Oracle JDBC驱动程序JAR中运行一个类而不是您的代码。

The Oracle JDBC JAR does contain a class oracle.jdbc.driver.OracleSql, and this class has a main method, so it's possible to run this class, but I don't think that's what you want to do.

Oracle JDBC JAR确实包含一个类oracle.jdbc.driver.OracleSql,并且这个类有一个main方法,因此可以运行这个类,但我不认为这是你想要做的。

Here's how to reproduce this error, give or take a different line number:

以下是如何重现此错误,给出或采用不同的行号:

C:\>java -cp ojdbc14.jar oracle.jdbc.driver.OracleSql
java.lang.ArrayIndexOutOfBoundsException: 0
    at oracle.jdbc.driver.OracleSql.main(OracleSql.java:1717)

This is the full output: I haven't truncated the stacktrace.

这是完整输出:我没有截断堆栈跟踪。

I couldn't reproduce this behaviour with a version of the JDBC JAR newer than ojdbc14.jar: I got a usage message instead attempting the same thing with ojdbc5.jar, ojdbc6.jar and ojdbc7.jar. The exception stacktrace above suggests that ojdbc14.jar is failing to check the number of command-line arguments before attempting to use them, whereas later versions fix this defect.

我无法使用比ojdbc14.jar更新的JDBC JAR版本重现此行为:我得到了一条用法消息,而不是尝试使用ojdbc5.jar,ojdbc6.jar和ojdbc7.jar执行相同的操作。上面的异常堆栈跟踪表明ojdbc14.jar在尝试使用它们之前未能检查命令行参数的数量,而更高版本修复了此缺陷。

#1