Good day to all of the Java community. I have this problem. Sometimes I last
connection with my database Oracle. I'm using JSP, SERVELT, jQuery, Netbeans
6.9 and Glassfish as WebServer. I'm building an web application with Pool
Connections. This is my code java class SQL. import
com.sun.rowset.CachedRowSetImpl; import java.sql.CallableStatement; import
java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException;
import java.sql.Statement; import java.sql.Types; import
javax.naming.Context; import javax.naming.InitialContext; import
javax.sql.DataSource; /** * * @author Danny Campoverde */ public class
oracleSQL { private Statement st=null; private CachedRowSetImpl cache = null;
private DataSource ds; private Connection conexion=null; public oracleSQL()
throws Exception{ Context initContext = new InitialContext(); ds =
(DataSource)initContext.lookup("jdbc/_Oracle"); } protected synchronized
Connection getConnection() throws SQLException, Exception { return
ds.getConnection(); } //funcion que permite ejecutar una sentencia Insert,
Delete o Update public void ejecutar(Connection conn, String sql) throws
SQLException, Exception{ conn.setAutoCommit(false); st =
conn.createStatement(); st.executeUpdate(sql); st.close(); //if (conexion !=
null) try { conexion.close(); } catch (SQLException logOrIgnore) {} }
//funcion que permite verificar si el query solicitado devuelve al menos mas
de 1 registro public boolean existe(String sql) throws SQLException,
Exception { conexion = this.getConnection(); boolean ok=false; st=
conexion.createStatement(); ResultSet res = st.executeQuery(sql); cache = new
CachedRowSetImpl(); cache.populate(res); ok = cache.next(); //st.close();
close(conexion, st, res); cache.close(); return ok; } //funcion que permite
obtener un conjunto de datos public CachedRowSetImpl consultarDATA(String
sql) throws SQLException, Exception { conexion = this.getConnection(); st =
conexion.createStatement(); ResultSet data = st.executeQuery(sql); cache =
new CachedRowSetImpl(); //Con el metodo [populate], capturo la data del
resulset. cache.populate(data); //st.close(); close(conexion, st, data);
return cache; } public ResultSet queryDATA(Connection conn, String sql)
throws Exception{ //conexion = this.getConnection(); st =
conn.createStatement(); ResultSet data = st.executeQuery(sql); return data; }
//funcion que permite obtener un registro public String
consultarRegistro(String sql) throws SQLException, Exception { conexion =
this.getConnection(); st = conexion.createStatement(); ResultSet res =
st.executeQuery(sql); cache = new CachedRowSetImpl(); cache.populate(res);
cache.next(); String txt; if(cache.wasNull()){ txt=""; }else{ txt =
cache.getString(1); //st.close(); cache.close(); } close(conexion, st, res);
return txt; } public String ejecutarStoredProcedure(int medio, int dia_desde,
int dia_hasta)throws SQLException, Exception{ conexion= this.getConnection();
CallableStatement call; call = conexion.prepareCall("{call
PR_PROMEDIOS_CIR(?,?,?,?,?,?,?,?)}"); call.setInt(1, medio); call.setInt(2,
dia_desde); call.setInt(3, dia_hasta); call.setInt(4, 5);
call.registerOutParameter(5, Types.DATE); call.registerOutParameter(6,
Types.VARCHAR); call.registerOutParameter(7, Types.INTEGER);
call.registerOutParameter(8, Types.VARCHAR); call.execute(); return
call.getString(7); } public void close(Connection conn, Statement statement,
ResultSet resultSet) throws Exception{ if (conn != null) try { conn.close();
} catch (SQLException logOrIgnore) {} if (statement != null) try {
statement.close(); } catch (SQLException logOrIgnore) {} if (resultSet !=
null) try { resultSet.close(); } catch (SQLException logOrIgnore) {} } }
always close connections to the database but do not understand why the
WebServer glassfish sends me this error.: *Error in allocating a connection.
Cause: In-use connections equal max-pool-size and expired max-wait-time.
Cannot allocate more connections..* This is my part Servlet, not is all!!
public class servlet_proceso extends HttpServlet { class_cuenta CUENTA = new
class_cuenta(); class_modulo MODULO = new class_modulo(); class_general
GENERAL = new class_general(); class_costos COSTOS = new class_costos();
oracleSQL SQL = null; private Connection conn= null; @Override public void
init(ServletConfig config) throws ServletException{ try{ super.init(config);
SQL = new oracleSQL(); }catch(Exception e){ e.printStackTrace(); } }
protected void processRequest(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8"); PrintWriter out =
response.getWriter(); try { //Esta conexion es usada para el metodo
SQL.ejecutar. conn = SQL.getConnection(); String option =
request.getParameter("option").toString().trim(); //#CONDICION: verifico si
los datos ingresados en el login, existen en la DB
if(option.equals("validarLogin")){ if(SQL.existe("select * from account where
acc_email='"+ request.getParameter("txtEmail").toString().trim() +"' and
acc_password='"+ request.getParameter("txtPwd").toString().trim() +"'")){
SQL.ejecutar(conn, "INSERT INTO logs VALUES(log_seq_id.nextval, '"+
request.getParameter("txtEmail").toString().trim() +"','Inicio sesion al
sistema',current_timestamp)"); SQL.ejecutar(conn, "COMMIT"); String perfil =
SQL.consultarRegistro("SELECT profile FROM account where acc_email='"+
request.getParameter("txtEmail").toString().trim() +"' and acc_password='"+
request.getParameter("txtPwd").toString().trim() +"'"); String nombre =
SQL.consultarRegistro("SELECT acc_name FROM account where acc_email='"+
request.getParameter("txtEmail").toString().trim() +"' and acc_password='"+
request.getParameter("txtPwd").toString().trim() +"'"); session =
request.getSession(true); session.setAttribute("user_cotizador",
request.getParameter("txtEmail").toString().trim());
session.setAttribute("user_perfil", perfil);
session.setAttribute("user_nombre", nombre);
response.sendRedirect("principal.jsp"); }else{ out.println("");
out.println(""); out.println(""); out.println("function gotoPage()");
out.println("{"); out.println("alert('ERROR: NO EXISTE ESTE USUARIO O NO
TIENE AUTORIZACIÓN PARA INGRESAR');");
out.println("window.location.href='index.jsp';"); out.println("}");
out.println(""); out.println(""); out.println(""); out.println("
"); out.println(""); } } else if(option.equals("agregarCliente")){ //valido
si existe o no el cliente if(SQL.existe("select * from customer where
cus_ruc_ci='"+ request.getParameter("ci") +"' and cus_status='A'")){
out.println("Este cliente ya existe"); }else{ String sql = "INSERT INTO
customer VALUES(customer_seq_id.nextval, '"+ request.getParameter("ci")
+"',"; sql +="'"+ request.getParameter("nombres").toString() +"', '"+
request.getParameter("direccion").toString() +"',"; sql +="'"+
request.getParameter("fono").toString() +"','"+
request.getParameter("email").toString() +"','A','"+
request.getParameter("vendedor") +"')"; SQL.ejecutar(conn,sql);
SQL.ejecutar(conn,"INSERT INTO logs VALUES(log_seq_id.nextval, '"+
(String)request.getSession().getAttribute("user_cotizador") +"','Se ha
ingresado un nuevo cliente con CI o RUC "+ request.getParameter("ci") +" al
Sistema',current_timestamp)"); SQL.ejecutar(conn,"COMMIT"); out.println("");
out.println("Cliente agregado satisfactoriamente."); out.println(""); } }
conn.close() } I need help or guide to understand why reason is giving me
this problem. Help me please... Thank you all.
--
[Message sent by forum member 'dcampoverde']
View Post: http://forums.java.net/node/884990