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);
 }
 }
}

Answer 1

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

• Alosyius: How could i make a connection that i could reuse?
• Mikhail Kopylov: @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

Answer 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

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