添加的ArrayList＆LT;串GT;对于SQLite数据库 [英] Add ArrayList<String> to SQLite DB

问题描述

i'm写我的第一个程序，并试图将数据与一个ArrayList转移（从CSV数据库）来使用JDBC驱动程序的sqlite-JDBC-3.7.2.jar SQLite数据库。
我已阅读教程献给 SQLite和Java的初学者，也不同的答案在计算器的问题（ <一href=\"http://stackoverflow.com/questions/3142285/saving-arraylist-in-sqlite-database-in-android\">Link 1 ，链接2 ），我终于可以创建表的分贝和可变列（数据类型：文本）。但是，当我尝试从一个ArrayList添加记录我的TestTable的这个方法：

i´m writing my first program and trying to transfer data with an ArrayList (from a CSV database) to an SQLite database using JDBC Driver sqlite-jdbc-3.7.2.jar. I have already read tutorials für SQLite and java beginners and also different answers to questions on stackoverflow (Link 1,Link 2 ) and i can finally create a db with tables and variable columns (datatype: TEXT). But when i try to add record from an ArrayList to my testtable with this method:

/**
 * Add record from ArrayList<string> to table
 * @param selectTable (name of table)
 * @param line (List with record for table)
 */
public void addRecord (String selectTable, ArrayList<String> line){
    try{
        //connect to database
        connection = new DBconnect(pathDataBase);
        database = connection.getConnection();

        //create SQL Statement
        ArrayList<String> columnNames = new ArrayList<String>();
        columnNames = getColumnList(selectTable); //call method to get all columnNames from selected table

        String sSQL = "INSERT INTO " + selectTable + "("; //update statement (string sSQL)

        //disperse ArrayList<string> columnNames in parts to add to the statement(string sSQL)
        int i = 0;
        for (i=0; i<columnNames.size(); i++){
            sSQL = sSQL + columnNames.get(i);
            if (columnNames.size() + 1 > i+2){
                sSQL = sSQL + ", "; //update statement(string sSQL)
            }//end if
        }// end for
        sSQL = sSQL + ") VALUES("; //update statement(string sSQL)

        //disperse ArrayList<string> line in parts to add to the statement(string sSQL)
        i=0;
        for (i=0; i<columnNames.size(); i++){
            sSQL = sSQL + line.get(i); //add record per line in columns
            if (columnNames.size() + 1 > i+2){
                sSQL = sSQL + ", ";
            }//end if
        }//end for
        sSQL = sSQL + ");";
        System.out.println(sSQL);

        Statement statement = database.createStatement();
        System.out.println("created statement");
        statement.executeUpdate(sSQL);
        System.out.println("executed Update");

        statement.close();
        database.close();
        //catch exception
    } catch ( Exception e ) {
        System.err.println( e.getClass().getName() + ": " + e.getMessage() );
        System.exit(0);
    }//end try/catch
}//end method

我得到以下异常说明：

i get the following exception description:

Opened database successfully
INSERT INTO Testtable(Test1, Test2, Test3) VALUES(Hallo1, Hallo2, Hallo3); //(ArrayList<string> columnNames) (ArrayList<string> line)
created statement
java.lang.NullPointerException: null

我觉得东西在我的字符串sSQL已经是错误的。

I think something in my String "sSQL" has to be wrong.

是否有人知道我必须以使其工作改变？
我无法写入数据类型串在文本列？

Does someone know what i have to change in order to make it work? Can i not write the datatype "string" in a "Text" column?

我希望我描述了我的问题可以理解，问正确的问题。

I hope i described my problem understandable and asked the right questions.

提前非常感谢。 ：）

@all：哦，我绊了我自己的愚蠢。我打开数据库的连接，调用一个方法，这也打开一个连接，并关闭它。当然，现在我不能执行语句，因为我的数据库是封闭的。那仁感谢您的时间！

@all: Oh man, i stumbled over my own stupidity. I opened a connection to database and call a method, which opens also a connection and closes it. of course now i can´t execute a statement, because my database is closed. Thanks Naren for your time!!!

推荐答案

使用for循环这样的......如果你通过输入如 TEST0的Test1的Test2 Test3的产生输出像这样

use for loop like this... if you pass input like Test0 Test1 Test2 Test3 produces the output like this

 Test0,Test1,Test2,Test3

所以

for(int i=0;i<columnNames.size();i++)
    {
        str=str+columnNames.get(i);
        if(!(i+1==columnNames.size()))
        {
            str=str+",";
        }

    }

和第二循环

 for (i=0; i<line.size(); i++){
                sSQL = sSQL + line.get(i); //add record per line in columns
                if(!(i+1==line.size()))
                {
                    str=str+",";
                }
            }

和您的查询中插入？（占位符）而构建。如果这样你应该使用preparaedStatement PS，然后ps.setInt（）方法一样，填补了它的正确值，那么你应该使用executeUpdate（ ）...如果你想用prepareStatementü可以检查的 http://www.mkyong.com/jdbc/jdbc-$p$pparestatement-example-insert-a-record/

And your inserting the "?"(placeHolders) in query while building.. If so you should use PreparaedStatement ps and then ps.setInt() like methods to fill the proper values in it then you should executeUpdate()... If you want to use prepareStatement u can check http://www.mkyong.com/jdbc/jdbc-preparestatement-example-insert-a-record/

这篇关于添加的ArrayList＆LT;串GT;对于SQLite数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！