JDCBC connection error with Jmeter test

I try to stress my application with Jmeter. When i don't synchronize my two methods I have multiple errors like the following:

The statement is closed. or

22:43:40,669 ERROR [stderr] (http-localhost-127.0.0.1-8080-106) java.sql.SQLException: Error
22:43:40,669 ERROR [stderr] (http-localhost-127.0.0.1-8080-106)     at org.jboss.jca.adapters.jdbc.WrappedConnection.checkException(WrappedConnection.java:1643)
22:43:40,670 ERROR [stderr] (http-localhost-127.0.0.1-8080-106)     at org.jboss.jca.adapters.jdbc.WrappedStatement.checkException(WrappedStatement.java:1262)
22:43:40,670 ERROR [stderr] (http-localhost-127.0.0.1-8080-106)     at org.jboss.jca.adapters.jdbc.WrappedResultSet.checkException(WrappedResultSet.java:4063)
22:43:40,670 ERROR [stderr] (http-localhost-127.0.0.1-8080-106)     at org.jboss.jca.adapters.jdbc.WrappedResultSet.next(WrappedResultSet.java:1866)
22:43:40,671 ERROR [stderr] (http-localhost-127.0.0.1-8080-106)     at fr.formation.dao.DaoImpl.getAllPersonnes(DaoImpl.java:275)

 java.sql.SQLException: The result set is closed.

 Caused by: java.lang.NullPointerException
22:43:40,992 ERROR [stderr] (http-localhost-127.0.0.1-8080-73)  at com.mysql.jdbc.ResultSetImpl.checkColumnBounds(ResultSetImpl.java:825)
22:43:40,993 ERROR [stderr] (http-localhost-127.0.0.1-8080-73)  at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2710)
22:43:40,993 ERROR [stderr] (http-localhost-127.0.0.1-8080-73)  at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2846)
22:43:40,993 ERROR [stderr] (http-localhost-127.0.0.1-8080-73)  at org.jboss.jca.adapters.jdbc.WrappedResultSet.getInt(WrappedResultSet.java:1073)
22:43:40,993 ERROR [stderr] (http-localhost-127.0.0.1-8080-73)  ... 23 more
22:43:40,993 ERROR [stderr] (http-localhost-127.0.0.1-8080-184) java.sql.SQLException: The result set is closed.
22:43:40,994 ERROR [stderr] (http-localhost-127.0.0.1-8080-184)     at org.jboss.jca.adapters.jdbc.WrappedResultSet.checkState(WrappedResultSet.java:4081)
22:43:40,994 ERROR [stderr] (http-localhost-127.0.0.1-8080-184)     at org.jboss.jca.adapters.jdbc.WrappedResultSet.getInt(WrappedResultSet.java:1065)

my code :

public  List<PersonneDao> getAllPersonnes() throws SQLException{
        List<PersonneDao> liste = new ArrayList<PersonneDao>();
          ResultSet rs = null ;
          PreparedStatement preparedStatement = null;
          try {

                connection =  ConnectionUtil.getInstance().getConnection();
                preparedStatement = (PreparedStatement) connection.prepareStatement("select * from personne");
                 rs = preparedStatement.executeQuery();



                while (rs.next()) {
                PersonneDao user = new PersonneDao();
                    user.setId (rs.getInt("id"));
                    user.setNom (rs.getString("nom"));
                    user.setPrenom(rs.getString("prenom"));
                    user.setEmail(rs.getString("email"));

                    liste.add(user);

                }

                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{

                if (rs != null) {
                    try {
                        rs.close();
                    } catch (SQLException sqlex) {
                        // ignore, as we can't do anything about it here
                    }

                    rs = null;
                }

                if (preparedStatement != null) {
                    try {
                        preparedStatement.close();
                    } catch (SQLException sqlex) {
                        // ignore, as we can't do anything about it here
                    }

                    preparedStatement = null;
                }

                if (connection != null) {
                    try {
                        ConnectionUtil.getInstance().close(connection);
                    } catch (SQLException sqlex) {
                        // ignore, as we can't do anything about it here
                    }

                    connection = null;


                }
            }
        return liste;

    }

}

and

public class ConnectionUtil {

    private DataSource dataSource;

    private static ConnectionUtil instance = new ConnectionUtil();

    private ConnectionUtil() {
        try {
            Context initContext = new InitialContext();
            dataSource = (DataSource) initContext.lookup("java:/dsFormationJSP");
        } catch (NamingException e) {
            e.printStackTrace();
        }
    }

    public static ConnectionUtil getInstance() {
        return instance;
    }

    public Connection getConnection() throws SQLException {
        Connection connection = dataSource.getConnection();
        return connection;
    }

    public void close(Connection connection) throws SQLException {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
        connection = null;
    }

}

and i use Jboss AS 7.1 with this conf

  <datasource jta="false" jndi-name="java:/dsFormationJSP" pool-name="dsFormationJSP" enabled="true" use-ccm="false">
                    <connection-url>jdbc:mysql://localhost:3306/base_personne</connection-url>
                    <driver-class>com.mysql.jdbc.Driver</driver-class>
                    <driver>mysql-connector-java-5.1.30-bin.jarcom.mysql.jdbc.Driver_5_1</driver>
                    <pool>
                        <min-pool-size>50</min-pool-size>
                        <max-pool-size>70</max-pool-size>
                        <prefill>true</prefill>
                    </pool>
                    <security>
                        <user-name>root</user-name>
                        <password>root</password>
                    </security>
                    <validation>
                        <validate-on-match>false</validate-on-match>
                        <background-validation>false</background-validation>
                    </validation>
                    <timeout>
                        <blocking-timeout-millis>50000</blocking-timeout-millis>
                        <idle-timeout-minutes>5</idle-timeout-minutes>
                    </timeout>
                    <statement>
                        <share-prepared-statements>false</share-prepared-statements>
                    </statement>
                </datasource>

When i synchronized the two methods it's OK. I normally do not need to to that ( its the native mechanism of database and jboss).Thanks for your help.

It looks like connection in getAllPersonnes() is a field, and not a local variable. This means that when run concurrently one execution could overwrite the connection of another execution, this can lead to two executions using the same connection.

For example:

Thread1 sets connection,
Thread2 sets connection,
Thread1 prepares statement (with connection set by Thread2!),
Thread2 prepares statement,
etc

Whichever method finishes first closes the connection before the other is done with it. Closing a connection closes the dependent statement and result set, which leads to the errors.

You need to change connection to a local variable inside the method, so it can't be overwritten by other threads.