is it necessary to call getConnection() in every class whenever I need to get the connection using c3Po pooling?

I have one jsp page LoginCheck.jsp, where i want to call database to verify login details and one java class CmDatabaseConnection where i create connection pooling.

LoginCheck.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<%@ page import ="java.sql.*" %>
<%@ page import ="java.util.Date" %>
<%@ page import ="com.wipro.clk.CmDatabaseConnection" %>
<%@ page import ="com.mchange.v2.c3p0.ComboPooledDataSource" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<%
String userid = request.getParameter("username");
System.out.println("entered username is " +userid);
String pwd=request.getParameter("password"); 
System.out.println("entered password is " +pwd);

ComboPooledDataSource dataSource = CmDatabaseConnection.makeConnectionPool();

Connection con=dataSource.getConnection();


/* Class.forName ("oracle.jdbc.driver.OracleDriver");
Connection con= DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/CCB25", "CISADM", "CISADM"); */

//
// USE BINDS ! to avoid sql injection
// push the userid and passwd comparison to the db 
// no need to get the password and compare locally
// 
String query = "select password from cmlogin where userid=? and password=?";

PreparedStatement st = con.prepareStatement(query);

st.setString(1,userid);
st.setString(2,pwd);

ResultSet rs  = st.executeQuery(); 
System.out.println("Size is  "+st.getFetchSize());
String pass1;

//  .next will advance if the query has any results
// 

if (rs.next()) {
pass1 = rs.getString("password");
System.out.println("value of result pass1" +pass1);

String name =request.getParameter("username");
System.out.println(name);
session.setAttribute("nam",name);
%> 
<jsp:forward page="admin.jsp"></jsp:forward>
<%
} else {
String msg="Username or password failed";
%>
<center> <p style="font-family:verdana;color:red;"> <%=msg %>
<jsp:include page="Login.jsp"></jsp:include>
<%  } 
con.close();
%>
</body>

CmDatabaseConnection.java

package com.wipro.clk;

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

import com.mchange.v2.c3p0.ComboPooledDataSource;


public class CmDatabaseConnection {



public static ComboPooledDataSource  makeConnectionPool()
{
ComboPooledDataSource cpds = new ComboPooledDataSource();
try
{

cpds.setDriverClass("oracle.jdbc.driver.OracleDriver");
cpds.setJdbcUrl("jdbc:oracle:thin:@localhost:1521/CCB25");
cpds.setUser("CISADM");
cpds.setPassword("CISADM");
cpds.setMaxPoolSize(100);
cpds.setMinPoolSize(5);
cpds.setMaxStatements(180);  
cpds.setAcquireIncrement(20);


} 
catch (Exception ex) 
{

ex.printStackTrace();
}
return cpds;

}


}

Now my doubt is, is it necessary to call below lines whenever i want to call database?

ComboPooledDataSource dataSource = CmDatabaseConnection.makeConnectionPool();
Connection con=dataSource.getConnection();

or is it possible to acquire db connection once and use the same connection in every java/jsp file? and if we use the same in every file then is it not needed to close the connection?

Solution

You still need to get and close connections. However, the connection pool solution (e.g. C3P0) is taking care about connections reusing and other optimizations.

You definitely should not create a datasource on every call, it should be created once. Most probably it is better to consider abilities of your application server (e.g. Tomcat) for acquiring the datasource (e.g. via JNDI).