Java thread-safe Sqlite?

Question

Im using sqlite to store chat history i am now concerned weather my approach is thread-safe.

The method below is the one i use to add my messages to the database.

Is my approach thread-safe?

public class dbHistory {
    public synchronized void addMessage(String from, String agentName, String msg, String time, String channel) {
        try {
            String databaseFileLocation = "jdbc:sqlite:history_" + agentID + ".db";

            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection(databaseFileLocation);
            PreparedStatement prep = conn.prepareStatement("insert into history values (?, ?, ?, ?, ?);");

            DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            Calendar cal = Calendar.getInstance();

            prep.setString(1, channel);
            prep.setString(2, from);
            prep.setString(3, msg);
            prep.setString(4, agentName);
            prep.setString(5, dateFormat.format(cal.getTime()));
            prep.addBatch();

            conn.setAutoCommit(false);
            prep.executeBatch();
            conn.setAutoCommit(true);

            conn.close();
        } catch (Exception ex) {
            System.out.println(ex);
        }
    }
}

Mikhail Kopylov · Accepted Answer · 2013-10-18 08:06:54Z

3

Yes, it's thread-safe, but too slow.

Connection creation is a very slow operation in any language, so you should use any connection pool to save time. Also you should remember that SimpleDateFormat.format is not thread-safe so you should use it only in one thread at one time.

Also you should not manage autocommit property around 'execute' method. Autocommit is a property of a connection, you should set it only once. If you set it to false, execute 'commit' method after every sql operation (or not, if you need) - you should manage commits manually. If you set it to true, your connection will generate commit execution automatically after every sql statement execution

answered Oct 18, 2013 at 8:06

Mikhail Kopylov

2,0985 gold badges32 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alosyius Over a year ago

How could i make a connection that i could reuse?

Mikhail Kopylov Over a year ago

@Alosyius, for example, you can use connection pools. You can write them manually or you can use known tooks like c3p0 sourceforge.net/projects/c3p0

Tomasz Waszczyk · Accepted Answer · 2013-10-18 08:06:58Z

2

You should remember that SQLite can work in 3 modes:

1.Single-thread. In this mode, all mutexes are disabled and SQLite is unsafe to use in more than a single thread at once.

2.Multi-thread. In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads.

3.Serialized. In serialized mode, SQLite can be safely used by multiple threads with no restriction.

http://www.sqlite.org/threadsafe.html

answered Oct 18, 2013 at 8:06

Tomasz Waszczyk

3,2196 gold badges46 silver badges81 bronze badges

1 Comment

Sam Ginrich Over a year ago

This seems to be the actual answer. Though, not clear how the description maps to the java interface.

Collectives™ on Stack Overflow

Java thread-safe Sqlite?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related