Connection Pooling Class

Discussion in 'Resources' started by ResultStatic, Oct 25, 2014.

Thread Status:
Not open for further replies.
  1. Offline

    ResultStatic

    I could not find any good tutorials telling you exactly how to use connection pooling. all the ways wanted you to download libs. here is how i do it. tell me if i did anything wrong, but this should be much more efficient than opening new connections each time or having a static instance of the connection. this way allows the server to use multiple connections to concurrently send data then it puts the connection back in the pool of usable connections.

    Code:java
    1.  
    2. import java.rmi.registry.LocateRegistry;
    3. import java.rmi.registry.Registry;
    4. import java.rmi.server.ExportException;
    5. import java.util.Properties;
    6.  
    7. import javax.naming.Context;
    8. import javax.naming.InitialContext;
    9. import javax.naming.NamingException;
    10.  
    11. import java.rmi.*;
    12. import java.sql.Connection;
    13. import java.sql.PreparedStatement;
    14. import java.sql.ResultSet;
    15. import java.sql.SQLException;
    16.  
    17. import com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource;
    18.  
    19. public class SQL {
    20. private static final SQL instance = new SQL();
    21. private MysqlConnectionPoolDataSource dataSource;
    22. public static SQL getInstance(){
    23. return instance;
    24. }
    25.  
    26. public SQL(){
    27. dataSource = new MysqlConnectionPoolDataSource();
    28. try {
    29. startRegistry();
    30. dataSource.setUser(username);
    31. dataSource.setPassword(password);
    32. dataSource.setServerName(anything i put my db ip);
    33. dataSource.setPort(port usually 3306);
    34. dataSource.setDatabaseName(must be the real name to ur db that contains the tables);
    35. InitialContext context = createContext();
    36. context.rebind("HrDS", dataSource);
    37.  
    38. // TODO Auto-generated catch block
    39. e.printStackTrace();
    40. }
    41. }
    42.  
    43. private static void startRegistry() throws RemoteException {
    44. try{
    45. Registry reg = LocateRegistry.createRegistry(1099);
    46. }catch (ExportException e){
    47.  
    48. }
    49. }
    50.  
    51. private static InitialContext createContext() throws NamingException {
    52. Properties env = new Properties();
    53. env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.rmi.registry.RegistryContextFactory");
    54. env.put(Context.PROVIDER_URL, "rmi://localhost:1099"); //listening port
    55. context = new InitialContext(env);
    56. return context;
    57. }
    58.  
    59. public void createSatement(String data){
    60. try {
    61. Connection connection = dataSource.getConnection();
    62. if (connection != null && !connection.isClosed()) {
    63. PreparedStatement statement = connection.prepareStatement(data);
    64. statement.execute();
    65. statement.close();
    66. connection.close();
    67. }
    68. } catch (SQLException e) {
    69. e.printStackTrace();
    70. }
    71. }
    72.  
    73.  
    74. public void addRow(String values, String table){
    75. this.createSatement("INSERT INTO `" + table + "` values(" + values + ");");
    76. }
    77.  
    78. public void deleteTable(String name){
    79. this.createSatement("DROP TABLE IF EXISTS " + name);
    80. }
    81.  
    82. public void deleteRow(String table, String column, String condition){
    83. this.createSatement("DELETE FROM " + "`" + table + "`" + " WHERE " + column + " = " + condition);
    84. }
    85.  
    86. public void createTable(String table, String columns){
    87. this.createSatement("CREATE TABLE IF NOT EXISTS " + table + "(" + columns + ")");
    88. }
    89. }
    90.  
     
  2. Offline

    RingOfStorms

    I'm not very experienced in SQL and I'm somewhat curious of what the benefit is of pooling connections? I currently have mine set up to use one connection per server, no matter how many plugins use it. So in other words, the polar opposite of what you've done here. Wouldn't it be easier and better to just have one active connection and use just that one connection across all of your plugins/uses. I'm just curious what is better, this pooling stuff or using one connection for each server.
     
  3. Offline

    ResultStatic

    RingOfStorms well the problem is with one connection the server has to wait for each query to finish before starting a new one. so lets say u schedule 30 queries to the data base. you can run those queries on separate threads where each thread gets a connection from the pool of connections then when its done it puts the connection back in the pool to be used again. its hard to explain but every forum i see they say its better.
     
  4. Offline

    RingOfStorms

    Hmm yea I may need to take a look at that.

    How does this method translate to async vs sync. Can you still choose or is everything going to be asyc then?
     
  5. Offline

    ResultStatic

    RingOfStorms sql is always async or the main thread freezes until the query is done
     
Thread Status:
Not open for further replies.

Share This Page