JDBC supports two ways to manage the Connections.
DriverManager Connections.
DataSource Connections.
If you want to get the DriverManager Connections then you need to write the following code.
Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/myjdbcdb","root","india");
When you call getConnection(url,un,pw) method on the DriverManager then newConnection will be created and returned. When you call close() method on the Connection which is taken from DriverManager then that Connection will be closed
When you use DriverManager Connections, You will get 2 problems.
With DriverManager connections, you need to Hardcode the Driver
problems.
Note : We have alreday solved this using JDBCUtil
When you call getConnection(url,un,pw) method on the DriverManager then new Connection will be created and returned. When you call close() method on the Connection which is taken from DriverManager then that Connection will be closed permenently. Creating and destroying connections every time for every user is expensive and may damage your application performance.
You can solve these problems with DataSource Connections.
DataSource Connections are Pooled Connections.
Connection pool is special area which is containing set of reusable database conections i.e multiple database connections will be created and will be placed in pool.
Whenever you want to use the connection you can just pick a connection from the pool.
Use the connection for database operation.
Return the connection to pool.
Following are Third Party DataSources / Connection Pooling Algorithms
Hikari DataSource
Tomcat DataSource
DBCP2
DBCP
C3P0
package com.jtcindia.jdbc;
import java.sql.*;
import org.apache.jtcindia.dbcp.BasicDataSource;
import com.jtcindia.jdbc.util.JDBCUtil;
public class Lab24 {
public static void main(String[] args) {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
BasicDataSource myds = new BasicDataSource();
myds.setDriverClassName("com.mysql.jdbc.Driver");
myds.setUrl("jdbc:mysql://localhost:3306/myjdbcdb");
myds.setUsername("root");
myds.setPassword("jtcsom");
myds.setInitialSize(10);
myds.setMaxActive(100);
con = myds.getConnection();
String SQL = "select * from mycustomers";
ps = con.prepareStatement(SQL);
rs = ps.executeQuery();
while (rs.next()) {
int cid = rs.getInt(1);
String cn = rs.getString(2);
String em = rs.getString(3);
int ph = rs.getInt(4);
String ci = rs.getString(5);
System.out.println(cid + "\t" + cn + "\t" + em + "\t" + ph + "\t" + ci);
}
System.out.println("----Done-----");
} catch (Exception ex) {
ex.printStackTrace();
} finally {
JDBCUtil.cleanup(rs, ps, con);
}
}
}
Q) How to Use DBCP Connection Pooling?
DataSourceUtil.java
package com.jtcindia.jdbc;
import java.sql.*;
import org.apache.commons.dbcp.BasicDataSource;
public class DataSourceUtil {
public static Connection getConnection() {
Connection con = null;
try {
BasicDataSource myds = new BasicDataSource();
//myds.setDriverClassName("com.mysql.jdbc.Driver");
myds.setUrl("jdbc:mysql://localhost:3306/myjdbcdb");
myds.setUsername("root");
myds.setPassword("india");
myds.setInitialSize(10);
myds.setMaxActive(100);
con = myds.getConnection();
} catch (Exception ex) {
ex.printStackTrace();
}
return con;
}
public static void cleanUp(Statement st, Connection con) {
try {
if (st != null)
st.close();
if (con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void cleanUp(ResultSet rs, Statement st, Connection con) {
try {
if (rs != null)
rs.close();
if (st != null)
st.close();
if (con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}