1

Is it industry standard to create a JDBC connection, and close, with every Java class that utilizes a prepared statement, or a call on a stored procedure? It just seems that there would be a much more efficient way to reuse a connection when executing inserts, updates, and deletes, rather than making a connection every time a class is instantiated.

I am not asking in reference to Java Servlets.

Thank you for any feedback, and resources to review.

dangerouslyCoding
  • 23
  • 9
  • 3
    It is industry standard to use a Connection Pool, e.g. by getting the connection from the Servlet Container, which will manage the Connection Pool for you. If not Servlet, you should use a third-party library for the Connection Pool, e.g. see [How to establish a connection pool in JDBC?](http://stackoverflow.com/q/2835090/5221149) – Andreas Feb 08 '17 at 03:18
  • but if not using servlets you can still easily use a connection pool. depending on what your application does and how many connections are involved and how often, you would need to decide if that's even worth doing. (but it usually is) – slipperyseal Feb 08 '17 at 03:20

2 Answers2

3

Usually the applications use connection polling provided by DataSource. You don't have to run application server for this, not even servlet container. Just instantiate the implementation and use it from application. Commonly used implementation is Apache Dbcp:

    ds = new BasicDataSource();
    ds.setDriverClassName("com.mysql.jdbc.Driver");
    ds.setUsername("root");
    ds.setPassword("root");
    ds.setUrl("jdbc:mysql://localhost/test");
    ...
    // then later in the application:
    Connection conn = ds.getConnection();
    // do the stuff
    conn.close(); // only releases conn back to pool
    ...
    // or better in Java 7:
    try (Connection conn = ds.getConnection()) {
          // do the stuff
    }
Zbynek Vyskovsky - kvr000
  • 18,186
  • 3
  • 35
  • 43
  • Thank you all for the feedback. Created a Connection Manager class, and went with Connection.getConnection() worked perfect; and like that I can simply make a connection with a method. Please let me know if I need to post my updated code. – dangerouslyCoding Feb 09 '17 at 01:27
0

You can use a connection pool from which your code can grab/release connections as required. Some popular ones are C3P0 and DBCP. See this answer to get started.

Community
  • 1
  • 1
Catchwa
  • 5,845
  • 4
  • 31
  • 57