Many Apache projects support interaction with a relational database. In Java Development Creating a new connection for each user can be time consuming (often requiring multiple seconds of clock time), in order to perform a database transaction that might take milliseconds.
Opening a connection per user can be unfeasible in a publicly-hosted Internet application where the number of simultaneous users can be very large.
Accordingly, developers often wish to share a “pool” of open connections between all of the application’s current users. The number of users actually performing a request at any given time is usually a very small percentage of the total number of active users, and during request processing is the only time that a database connection is required.
The application itself logs into the DBMS, and handles any user account issues internally.
Other must read: Simple Oracle Database JDBC Connect and ExecuteQuery Example in Java
In Java There are several Database Connection Pools already available, both within Apache products and elsewhere. This Commons package provides an opportunity to coordinate the efforts required to create and maintain an efficient, feature-rich package under the ASF license.
The configuration of the data source can be defined using some properties method provided by this class. The basic properties is the driver classname, connection url, username and password.
This example demonstrate how to use the BasicDataSource class of Apache Commons DBCP to create a basic requirements for database connection.
package com.crunchify.tutorials;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.dbcp.BasicDataSource;
/**
* @author Crunchify.com
*
*/
public class CrunchifyDataSourceExample {
public static void main(String[] args) throws Exception {
// Creates a BasicDataSource
BasicDataSource crunchifyDS = new BasicDataSource();
// Define Driver Class
crunchifyDS.setDriverClassName("com.mysql.jdbc.Driver");
// Define Server URL
crunchifyDS.setUrl("jdbc:mysql://localhost/crunchifydb");
// Define Username
crunchifyDS.setUsername("admin");
// Define Your Password
crunchifyDS.setPassword("password");
// onnection (session) with a specific database. SQL statements are
// executed and results are returned within the context of a connection.
Connection conn = null;
// An object that represents a precompiled SQL statement.
PreparedStatement stmt = null;
try {
conn = crunchifyDS.getConnection();
stmt = conn.prepareStatement("select * from company");
// A table of data representing a database result set, which is
// usually generated by executing a statement that queries the
// database.
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
System.out.println("Comapny: " + rs.getString("company"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// Close Statement and Connection
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
}
}