Add ArrayList to SQLite DB

Question

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

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.

Many thanks in advance. :)

@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!!!

Naren · Accepted Answer · 2014-02-14T13:22:57.830

0

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

 Test0,Test1,Test2,Test3

so

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

    }

and second for loop

 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+",";
                }
            }

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/

edited Feb 14 '14 at 13:22

answered Feb 14 '14 at 13:10

Naren

1,447
1
10
11

Thank you for your answer. I changed the loops and tried again. Unfortunately i get the same exception. When i change the Statement to a PreparedStatement the method does not print my Syso "created Statement"and still get the "null". – Xyvel Feb 14 '14 at 14:07
you do one thing ...put e.printStackTrace() in catch block before System.exit(0); and run and post that Exception trace .. so that we can proceed – Naren Feb 14 '14 at 14:13
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!!! – Xyvel Feb 14 '14 at 14:33
i thought of same thing about database closing... any ways it is working now – Naren Feb 14 '14 at 14:35

Add ArrayList to SQLite DB

1 Answers1