reading strings into a database using sqlite

Question

So I need to read text files into a sqlite database. I already read the files into an ArrayList called weFeelFine, and now I'm trying to create the database.

creating the database:

        try{
        Connection conn = DriverManager.getConnection(CONNECTION_STRING);
        Statement statement = conn.createStatement();

        statement.execute("DROP TABLE IF EXISTS " + TABLE_CONTACTS);   

        statement.execute("CREATE TABLE IF NOT EXISTS " + TABLE_CONTACTS +
                " ( "  + COLUMN_LINE + " text " + ")" );    //create a table

        for(int i = 0; i< weFeelFine.size(); i++){
            String line = weFeelFine.get(i);
            insertContacts(statement,line);
        }
        statement.close();
        conn.close();
    }
    catch (SQLException ex){
        System.out.println("something went wrong! " + ex.getMessage());
        ex.printStackTrace();
    }

the INSERT method I use:

    private static void insertContacts(Statement statement, String lineNow) throws SQLException{
    statement.execute("INSERT INTO " + TABLE_CONTACTS + " (" + COLUMN_LINE + " )" +
            "VALUES('" + lineNow + "')" );
}

Some sentences contain the ' symbol. Which causes the a program to fail.

One of the sentences I'm have trouble to read (with words like - i'm ):

i'm feeling really grouchy about the weather have an eerie correspondence to the times i've gotten slack around my inner work

The error I get:

SQL error or missing database (near "m": syntax error)

How can I tell the program to ignore that symbol, and take the whole sentence?

score 0 · Answer 1 · edited Dec 19 '19 at 10:07

Since i can't mark the question as a duplicate i'll just leave this link here with a summary. How to escape single quotes for SQL insert...when string to insert is in a user generated variable

You can either use a PreparedStatement:

String statement ="INSERT INTO " + TABLE_CONTACTS + " (" + COLUMN_LINE + " ) VALUES(?)"; PreparedStatement statement= con.prepareStatement(statement); statement.setString(1,lineNow); statement.executeUpdate();
Escape the Single Quotes usong replace() and then call the insert statement:

lineNow = lineNow.replace("'","''");

reading strings into a database using sqlite

1 Answers1