title: Java Threads and MySQL
body: <p>I have a threaded chat server application which requires MySQL authencation. </p>
  
  <p>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?</p>
  
  <p>Or is it better to have all threads make a seperate connection to MySQL to authencate?</p>
  
  <p>Or is it better to let 1 class handle the queries AND connections?</p>
  
  <p>We are looking at a chatserver that should be able to handle upto 10.000 connections/users.</p>
  
  <hr>
  
  <p>I am now using c3p0, and I created this:</p>
  
  <pre><code>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();
      }
  </code></pre>
  
  <p>}</p>
  
  <p>public class Database {</p>
  
  <pre><code>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();
      }
  }
  </code></pre>
  
  <p>}</p>
  
  <p>Would this be thread safe?</p>