Java Threads and MySQL

Question

Hello all,

I have a threaded chat server application which requires MySQL authencation.

Is the best way to have 1 class create the MySQL connection, keep that connection open and let every thread use that connection but use own Query handler?

Or is it better to have all threads make a seperate connection to MySQL to authencate?

Or is it better to let 1 class handle the queries AND connections?

We are looking at a chatserver that should be able to handle upto 10.000 connections/users.

Thanks!

Answer 1

Have you looked at connection pooling ? Check out (for example) Apache DBCP or C3P0.

Briefly, connection pooling means that a pool of authenticated connections are used, and free connections are passed to you on request. You can configure the number of connections as appropriate. When you close a connection, it's actually returned to the pool and made available for another client. It makes life relatively easy in your scenario, since the pool looks after the authentication and connection management.

Answer 2

c3p0 connection pool is a robust solution. You can also check dbcp but c3p0 shows better performance, supports auto-reconnection and some other features.

Answer 3

You should not have just one connection. It's not a thread-safe class. The idea is to get a connection, use it, and close it in the narrowest scope possible.

Yes, you'll need a pool of them. Every Java EE app server will have a JNDI pooling mechanism for you. I wouldn't recommend one class for all queries, either. Your chat ap

Your chat app ought to have a few sensible objects in its domain model. I'd create data access objects for them as appropriate. Keep the queries related to a particular domain model object in its DAO.

Answer 4

Thanks all for commeting.

I am now using c3p0, and I created this:

public static void main(String[] args) throws PropertyVetoException
{
    ComboPooledDataSource pool = new ComboPooledDataSource();
    pool.setDriverClass("com.mysql.jdbc.Driver");
    pool.setJdbcUrl("jdbc:mysql://localhost:3306/db");
    pool.setUser("root");
    pool.setPassword("pw");
    pool.setMaxPoolSize(100);
    pool.setMinPoolSize(10);

    Database database = new Database(pool);
    try
    {

        ResultSet rs = database.query("SELECT * FROM `users`");

        while (rs.next()) {
            System.out.println(rs.getString("userid"));
            System.out.println(rs.getString("username"));
        }
    }
    catch(Exception ex)
    {
        System.out.println(ex.getMessage());
    }
    finally
    {
        database.close();
    }

}

public class Database {

ComboPooledDataSource pool;
Connection conn;
ResultSet rs = null;
Statement st = null;

public Database (ComboPooledDataSource p_pool)
{
    pool = p_pool;
}

public ResultSet query (String _query)
{
    try {
        conn = pool.getConnection();
        st = conn.createStatement();
        rs = st.executeQuery(_query);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {

    }
    return rs;
}

public void close ()
{
    try {
        st.close();
        conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

}

Would this be thread safe?