Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
1

Is this Good Code to implement pooling? I want to implement this in my project which has 30 threads operating concurrently and each thread requires more than four connection for each request? Does this code work? 

    import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbcp.BasicDataSource;

/**
 *
 * @author
 * taher_JAVAHUNTER
 */

    public class JDBCHelper {

        private final static String username = "root";
        private final static String password = "";
        private final static String url = "jdbc:mysql://localhost:3306/treamisdemo";
        public static Connection connection = null;
        public static int connectionCount = 0;
    //    public JDBCHelper(boolean setCon) {
    //        try {
    //            setConnectionTest();
    //        } catch (Exception e) {
    //            System.out.println("Error in Connection:" + e.toString());
    //        }
    //    }
        public static BasicDataSource dataSource;

        public static Connection getConnection() throws SQLException {
            try {
                if (dataSource == null) {
                    dataSource = new BasicDataSource();
                    String driver = "com.mysql.jdbc.Driver";
                    try {
                        dataSource.setDriverClassName(driver);
                        dataSource.setUrl(url);
                        dataSource.setUsername(username);
                        dataSource.setPassword(password);
                        dataSource.setMaxActive(100);
                        dataSource.setMaxWait(10000);
                        dataSource.setMaxIdle(10);
                        if (connection == null || connection.isClosed()) {
                            System.out.println(" requeition CONNECTION WITH FIRST SERVER.");
                            connection = dataSource.getConnection();
                            connectionCount++;
                        }
                    } catch (SQLException e) {
                        System.out.println("***Connection Requisition*** Could not connect to the database msg :" + e.getMessage());
                    }
                } else {
                    connection = dataSource.getConnection();
                    connectionCount++;
                }
            } catch (Exception e) {
                System.out.println("open connection exception" + e);
            }
            return connection;
        }

        public static void close(ResultSet c) {
            try {
                if (c != null) {
                    c.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        public static void close(Statement c) {
            try {
                if (c != null) {
                    c.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        public static void close(Connection c) {
            try {
                if (c != null) {
                    c.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

}
share|improve this question
2  
I suggest you read stackoverflow.com/questions/520585/… –  Elliott Frisch yesterday
1  
DBCP is largely abandoned, I recommend HikariCP, but then again I am biased as I helped write it. –  brettw yesterday

1 Answer

up vote 0 down vote

I would not recommend this approach. You are basically creating a connection and hanging on it it. I'm not in love with your pattern, but something like this would be better:

public class DataTransaction {
   private final static BasicDataSource dataSource;

   static {
      dataSource = new BasicDataSource();
      dataSource.setDriverClassName("com.mysql.jdbc.Driver");
      dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/registrationtest");
      dataSource.setUsername("root");
      dataSource.setPassword("root");
      dataSource.setMaxActive(100);
      dataSource.setMaxWait(10000);
      dataSource.setMaxIdle(10);
   }

   private DataTransaction() {
   }

   public static DataSource getDataSource() {
      return dataSource;
   }
}

Further, I would not hardcode any of the DataSource parameters, but rather initialize the DataSource from a properties file.

share|improve this answer
 
hi could see my updated code above plz whether it work or not? –  user2998826 yesterday
 
Looks ok enough. Because getConnection() is un-synchronized two threads hitting at the same time (the first time) could initialize the DataSource twice (because both think it is null). If what you've shown is all you're trying to do, I recommend just using the SQLClosure class from the SansORM library and not bothering writing your own helper. –  brettw yesterday

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.