mardi 4 août 2015

c3po Pool With Multithread in Java

I have this app in tomcat which receives many requests in a servlet and process them creating a new thread for each request, so to answer with status 200 quickly.

But in my tomcat log im getting a lot of this error

ago 04, 2015 4:14:37 AM com.mchange.v2.c3p0.stmt.GooGooStatementCache 
INFORMACIÓN: Problem with checked-in Statement, discarding.
java.sql.SQLException: No operations allowed after statement closed.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:937)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:872)
    at com.mysql.jdbc.StatementImpl.checkClosed(StatementImpl.java:445)
    at com.mysql.jdbc.PreparedStatement.clearParameters(PreparedStatement.java:1026)
    at com.mchange.v2.c3p0.stmt.GooGooStatementCache.refreshStatement(GooGooStatementCache.java:622)
    at com.mchange.v2.c3p0.stmt.GooGooStatementCache.checkinStatement(GooGooStatementCache.java:272)
    at com.mchange.v2.c3p0.stmt.GooGooStatementCache.checkinAll(GooGooStatementCache.java:324)
    at com.mchange.v2.c3p0.impl.NewPooledConnection.checkinAllCachedStatements(NewPooledConnection.java:772)
    at com.mchange.v2.c3p0.impl.NewPooledConnection.markClosedProxyConnection(NewPooledConnection.java:402)
    at com.mchange.v2.c3p0.impl.NewProxyConnection.close(NewProxyConnection.java:87)
    at Util.PoolLocal.realeaseConnection(PoolLocal.java:53)
    at app.ModelML.getToken(ModelML.java:148)
    at app.ModelML.getML(ModelML.java:268)
    at Util.AsyncRequestProcessor.run(AsyncRequestProcessor.java:24)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)

I believe its because there are sometimes many threads at the same time and this couses some unsync access to the pool

Here is the pool

package Util;


import java.beans.PropertyVetoException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;

import app.ModelML;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class PoolLocal {

    private static PoolLocal datasource;
    private ComboPooledDataSource cpds;

    private PoolLocal() throws IOException, SQLException, PropertyVetoException {
        cpds = new ComboPooledDataSource();
        cpds.setDriverClass("com.mysql.jdbc.Driver"); //loads the jdbc driver
        cpds.setMaxIdleTime(3600);
        setPredeterminado(cpds);
        cpds.setMinPoolSize(5);
        cpds.setAcquireIncrement(5);
        cpds.setMaxPoolSize(30);
        cpds.setMaxStatements(360);

    }
    public void setPredeterminado( ComboPooledDataSource cpds){
        cpds.setJdbcUrl("jdbc:mysql://"+ModelML.server+"/"+ModelML.db_name);
        cpds.setUser(ModelML.db_user);
        cpds.setPassword(ModelML.db_pass);


    }

    public static PoolLocal getPoolConnection() throws IOException, SQLException, PropertyVetoException {
        if (datasource == null) {
            datasource = new PoolLocal();
            return datasource;
        } else {
            return datasource;
        }
    }

    public Connection getConnection() throws SQLException {
        return this.cpds.getConnection();
    }
    public void realeaseConnection(Connection c){
        try {
            if(c!=null)
                if(!c.isClosed())
                    c.close();
        } catch (SQLException e) {
            Log.log("RELEASE");

            e.printStackTrace();
        }
    }
}

The servlet

package Servlets;



import java.io.BufferedReader;
import java.io.IOException;
import java.util.concurrent.ThreadPoolExecutor;

import javax.servlet.AsyncContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import Util.AppAsyncListener;
import Util.AsyncRequestProcessor;
import Util.Log;

import com.google.gson.JsonObject;
import com.google.gson.JsonParser;

@WebServlet(urlPatterns="/ml", asyncSupported = true)
public class ML extends HttpServlet {
    private static final long serialVersionUID = 1L;

    protected synchronized void  doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }

    protected synchronized void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String str = null;

        try {
            StringBuilder sb = new StringBuilder();
            BufferedReader br = request.getReader();
            while((str = br.readLine()) != null )sb.append(str);

            Log.log("Nuevo :"+sb.toString());
            JsonParser parser = new JsonParser();
            JsonObject o = (JsonObject)parser.parse(sb.toString());
            String resource=o.get("resource").getAsString();
            String cliente=o.get("user_id").getAsString();

            AsyncContext asyncCtx = request.startAsync();
            asyncCtx.addListener(new AppAsyncListener());
            asyncCtx.setTimeout(9000);

            ThreadPoolExecutor executor = (ThreadPoolExecutor) request.getServletContext().getAttribute("executor");

            executor.execute(new AsyncRequestProcessor(asyncCtx, resource,cliente));


        }catch(Exception e){
            Log.log("ERROR en servlet ml: "+str);
            e.printStackTrace();
        }
        return; 
    }

}

And this are the only 3 methods which use the pool

public String getToken(String cliente) {//Solo para inicializar la aplicacion
        Connection conn=null;
        try {

            conn= PoolLocal.getPoolConnection().getConnection();
            PreparedStatement ps;
            Log.log(cliente);
            ps = conn.prepareStatement("SELECT * FROM mlauth where id="+cliente);
            ResultSet resultSet = ps.executeQuery();
            if(resultSet.next())
                return resultSet.getString("token");

        } catch (SQLException | IOException | PropertyVetoException e) {
            Log.log("ERROR al traer token");
            e.printStackTrace();
        }finally{

              try {
                  PoolLocal.getPoolConnection().realeaseConnection(conn);
            } catch (IOException | SQLException | PropertyVetoException e) {
                e.printStackTrace();
            }
        }
        return "";

    }
    public String getRefreshToken(String cliente) {
        Connection conn=null;
        try {

            conn= PoolLocal.getPoolConnection().getConnection();
            PreparedStatement ps;
            ps = conn.prepareStatement("SELECT * FROM mlauth where id="+cliente);
            ResultSet resultSet = ps.executeQuery();
            if(resultSet.next())
                return resultSet.getString("refresh_token");

        } catch (SQLException | IOException | PropertyVetoException e) {
            Log.log("ERROR al traer refresh_token");
            e.printStackTrace();
        }finally{

              try {
                  PoolLocal.getPoolConnection().realeaseConnection(conn);
            } catch (IOException | SQLException | PropertyVetoException e) {
                e.printStackTrace();
            }
        }
        return "";

    }
    public String setToken(String token,String refresh_token,String cliente) {
        Connection conn=null;
        try {

            conn= PoolLocal.getPoolConnection().getConnection();
            PreparedStatement ps;
            ps = conn.prepareStatement("INSERT INTO mlauth (id,token, refresh_token, last_modif) VALUES (?,?,?,NOW()) ON DUPLICATE KEY UPDATE token = VALUES(token), refresh_token = VALUES(refresh_token), last_modif = NOW()");
            ps.setString(1, cliente);
            ps.setString(2, token);
            ps.setString(3, refresh_token);
            ps.executeUpdate();
        } catch (SQLException | IOException | PropertyVetoException e) {
            Log.log("ERROR al setear token");
            e.printStackTrace();
        }finally{

            try {
                PoolLocal.getPoolConnection().realeaseConnection(conn);
            } catch (IOException | SQLException | PropertyVetoException e) {
                e.printStackTrace();
            }
        }
        return null;

    }



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire