CODEWITHSUNDEEP
sql-serverjdbcc3p0
calvinkrishy
calvinkrishy

Reputation: 3928

Resetting a JDBC connection state (c3p0)

What is the recommended way to reset a C3P0 managed connection to its initial state?

I am using the Microsoft JDBC driver and setting a SET ROWCOUNT 1 on one connection. This results in all queries executed by that connection to return only one row even after the connection has been returned to the pool and later fetched again. Should I be resetting the values explicitly onCheckin or onCheckout?

Main class

    import com.mchange.v2.c3p0.ComboPooledDataSource;
    import java.beans.PropertyVetoException;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    import java.sql.Connection; 
    import java.sql.ResultSet;
    
    public class Main {
    
        final ComboPooledDataSource cpds;
        
        Main() throws PropertyVetoException {
            cpds = new ComboPooledDataSource();
            cpds.setDriverClass("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            cpds.setJdbcUrl("jdbc:sqlserver://10.42.62.41:1433;databaseName=mach;integratedSecurity=false;SendStringParametersAsUnicode=true;applicationName=panda;");
            cpds.setUser("testuser");
            cpds.setPassword("welcome123");
            cpds.setInitialPoolSize(0);
            cpds.setMinPoolSize(1);
            cpds.setMaxPoolSize(1);
            cpds.setConnectionCustomizerClassName("C3p0ConnectionCustomizer");
            cpds.setDescription("Netbeans test project");
        }
        
        Connection getConnection() throws SQLException{
            return cpds.getConnection();
        }
        
        public static void main(String[] args) throws PropertyVetoException, SQLException {
            Main m = new Main();
            
            try(Connection connection = m.getConnection()){
                Statement stmt = connection.createStatement();
                stmt.execute("SET ROWCOUNT 1");
            }
            
            try(Connection connection = m.getConnection()){
                try(Statement stmt = connection.createStatement()) {
                    int cnt = 0, rsCnt = 0;
                    boolean results = stmt.execute("select * from Foo; select * from Bar");
                    if(results) {
                        do {
                            rsCnt++;
                            ResultSet rs = stmt.getResultSet();
                            while(rs.next()) {
                                cnt++;
                            }
                            System.out.println(rsCnt + " -> " + cnt);
                            rs.close();
                            results = stmt.getMoreResults();
                            cnt = 0;
                        } while (results);
                    }
                }
            }
            
        }
    
    }

Customizer - Mainly to see the connection being used.

    import com.mchange.v2.c3p0.AbstractConnectionCustomizer;
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    public class C3p0ConnectionCustomizer extends AbstractConnectionCustomizer {
    
        @Override
        public void onAcquire(Connection c, String pdsIdt) {
            try (Statement stmt = c.createStatement()) {
                stmt.execute("SET ROWCOUNT 0");
            } catch(SQLException sqle) {
                sqle.printStackTrace();
            }
        }
    
        @Override
        public void onCheckOut(Connection c, String pdsIdt) {
            System.out.println("Checked out " + c + " [" + pdsIdt + "]");
        }
    
        @Override
        public void onCheckIn(Connection c, String pdsIdt) throws SQLException {
            System.out.println("Checking in " + c + " [" + pdsIdt + "]");
    
        }
    
    }

Without the SET ROWCOUNT 1 line, more rows are returned by the above queries. The customizer logging shows that the same connection is being used.

Upvotes: 1

Views: 1098

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

One could argue that, ideally, code that calls SET ROWCOUNT 1 really should "clean up after itself" by making sure that it calls SET ROWCOUNT 0 before releasing the connection back to the pool.

However, if we cannot absolutely guarantee such behaviour then it seems quite reasonable to simply move your SET ROWCOUNT 0 call from the onAcquire method to the onCheckIn method. It would result in an extra round-trip for each check-in, even if resetting ROWCOUNT was not really necessary, but a SET ROWCOUNT 0 would be a relatively inexpensive operation.

(I just tried it with c3p0-0.9.5.2 and mssql-jdbc, and moving SET ROWCOUNT 0 to the onCheckIn method had the desired effect.)

Upvotes: 1

Related Questions