Connection Java Class to a Derby Database increase performance of the pool connections

Question

i have a class named ConnectionDerby, and in that class i set the parameters to connect to an embedded database (Derby) and i want to reduce so much redundancy of connections, the actual methods and connection works, but i want to increase the performance, so the code of the class is this:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.swing.JOptionPane;

public class ConnectionDerby {

    private Connection conn = null;
    private Statement sttm = null;

    public Connection CrearBD(String query) {
        try {
        //Obtenemos el Driver de Derby
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        conn = DriverManager.getConnection("jdbc:derby:.\\BD\\inventario.db;create=true");
        if (conn != null) {
            //JOptionPane.showMessageDialog(null, "Base de Datos Lista");
            try {
                PreparedStatement pstm = conn.prepareStatement(query);
                pstm.execute();
                pstm.close();
                //JOptionPane.showMessageDialog(null, "Base de Datos Creada Correctamente");
                System.out.println("SENTENCIA SQL EFECTUADA CORRECTAMENTE");
            } catch (SQLException ex) {
                //JOptionPane.showMessageDialog(null, ex.getLocalizedMessage());
                System.out.println(ex.getMessage());
                JOptionPane.showMessageDialog(null, "NO SE PUDO EFECTUAR LA SENTENCIA SQL", "Error", JOptionPane.ERROR_MESSAGE);
                //JOptionPane.showMessageDialog(null, "NO SE PUDO EFECTUAR LA SENTENCIA SQL");
            }
        }

        } catch (SQLException e) {
        System.out.println(e.getMessage());
        JOptionPane.showMessageDialog(null, "NO SE PUDO EFECTUAR LA SENTENCIA SQL", "Error", JOptionPane.ERROR_MESSAGE);
        //JOptionPane.showMessageDialog(null, "TRONO LA APLICACION EN EJECUTAR LAS SENTENCIAS SQL parte 2");
    } catch (ClassNotFoundException e) {
        System.out.println(e.getMessage());
        JOptionPane.showMessageDialog(null, "NO SE PUDO EFECTUAR LA SENTENCIA SQL", "Error", JOptionPane.ERROR_MESSAGE);
        //JOptionPane.showMessageDialog(null, "TRONO LA APLICACION EN EJECUTAR LAS SENTENCIAS SQL parte 3");
    }
        return conn;
    }

    public Connection AccederBD() {
        try {
            //Obtenemos el Driver de Derby
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
            //Obtenemos la Conexión
            conn = DriverManager.getConnection("jdbc:derby:.\\BD\\inventario.db");
            if (conn != null) {
                System.out.println("Base de Datos Ya Leida Correctamente");
                //JOptionPane.showMessageDialog(null, "Base de Datos Ya Leida Correctamente");
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            System.out.println("NO SE ENCONTRO LA BASE DE DATOS");
            System.out.println("CREANDO BASE DE DATOS EN DERBY DATABASE");
            String createTableProyecto = "CREATE TABLE proyecto\n"
                + "(\n"
                + "idproyecto INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),\n"
                + "nombre VARCHAR(50) NOT NULL,\n"
                + "descripcion VARCHAR(1000),\n"
                + "CONSTRAINT proyecto_pk PRIMARY KEY (idproyecto)\n"
                + ")";
            String createTablePrimera = "CREATE TABLE primera\n"
                + "(\n"
                + "idprimera INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),\n"
                + "nombre VARCHAR(75) NOT NULL,\n"
                + "descripcion VARCHAR(1000),\n"
                + "CONSTRAINT primera_pk PRIMARY KEY (idprimera)\n"
                + ") ";
            String createTableSegunda = "CREATE TABLE segunda\n"
                + "(\n"
                + "idsegunda INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),\n"
                + "nombre VARCHAR(75) NOT NULL,\n"
                + "descripcion VARCHAR(1000),\n"
                + "CONSTRAINT segunda_pk PRIMARY KEY (idsegunda)\n"
                + ")";
            String createTableEncabezado = "CREATE TABLE encabezado\n"
                + "(\n"
                + "idencabezado INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),\n"
                + "idproyecto INTEGER NOT NULL,\n"
                + "idprimera INTEGER NOT NULL,\n"
                + "idsegunda INTEGER NOT NULL,\n"
                + "fecha DATE NOT NULL,\n"
                + "CONSTRAINT encabezado_pk PRIMARY KEY (idencabezado)\n"
                + ")";
            String createRelationCotizacionIdTopCoat = "ALTER TABLE ENCABEZADO ADD FOREIGN KEY (IDSEGUNDA) REFERENCES SEGUNDA(IDSEGUNDA)";
            String createRelationCotizacionIdPrimer = "ALTER TABLE ENCABEZADO ADD FOREIGN KEY (IDPRIMERA) REFERENCES PRIMERA(IDPRIMERA)";
            String createRelationCotizacionIdProyecto = "ALTER TABLE ENCABEZADO ADD FOREIGN KEY (IDPROYECTO) REFERENCES PROYECTO(IDPROYECTO)";
            CrearBD(createTableProyecto);
            CrearBD(createTablePrimera);
            CrearBD(createTableSegunda);
            CrearBD(createTableEncabezado);
            CrearBD(createRelationCotizacionIdTopCoat);
            CrearBD(createRelationCotizacionIdPrimer);
            CrearBD(createRelationCotizacionIdProyecto);
            //*************PRUEBAS*****************
        } catch (ClassNotFoundException e) {
            System.out.println(e.getMessage());
            System.out.println("ERROR DE TIPO ClassNotFoundException");
            //JOptionPane.showMessageDialog(null, "TRONO LA APLICACION EN ACCEDER A LA BASE DE DATOS parte 2");
        }
        return conn;
    }

    public void UID(String sqlcad) {
        try {
            //Obtenemos el Driver de Derby
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
            conn = DriverManager.getConnection("jdbc:derby:.\\BD\\inventario.db");
            sttm = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            sttm.executeUpdate(sqlcad);
            System.out.println("Conexión Exitosa a la Base de Datos");
            //JOptionPane.showMessageDialog(null, "Conexión exitosa");
            sttm.close();
            conn.close();
            if (conn != null) {
            System.out.println("Consulta Realizada Correctamente");
            //JOptionPane.showMessageDialog(null, "Base de Datos Ya Leida Correctamente");
            }
        } catch (SQLException e) {
            System.out.println("Error= " + e.getMessage());
        } catch (ClassNotFoundException e) {
            System.out.println("Error= " + e.getMessage());
        }
    }

    public ResultSet getvalores(String sqlcad) {
        ResultSet rs = null;
        try {
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        conn = DriverManager.getConnection("jdbc:derby:.\\BD\\inventario.db");
        sttm = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        //String sqlcad = "Select nombre, descripcion from primera";
        rs = sttm.executeQuery(sqlcad);
        return rs;
        } catch (Exception e) {
            System.out.println("Error= " + e.getMessage());
            return rs;
        }
    }
}

and i use another class to set the string of the query of the query, like this:

import java.sql.ResultSet;

public class Primer {

    ConnectionDerby c = new ConnectionDerby();

    public void insertMaestroPrimera(String nombre, String descripcion) {
        c.UID("INSERT INTO primera(nombre, descripcion) values('" + nombre + "','" + descripcion + "')");
    }

    public void updateMaestroPrimera(int id, String nombre, String descripcion) {
        c.UID("UPDATE primera set nombre='" + nombre + "',descripcion='" + descripcion + "' where idprimera=" + id);
    }

    public void deleteMaestroPrimer(int id) {
        c.UID("DELETE FROM primera where idprimera=" + id);
    }

    public ResultSet llenarTable() {
        ResultSet rs = null;
        String sql = "SELECT nombre, descripcion from primera ORDER BY nombre";
        rs = c.getvalores(sql);
        return rs;
    }

    public ResultSet obtenerDatos(String value) {
        ResultSet rs = null;
        String sql = "SELECT idprimera, nombre, descripcion from primera WHERE nombre = '" + value + "'";
        rs = c.getvalores(sql);
        return rs;
    }
}

so guys i want some advices, cause when i do an update in the table encabezado i got the followings errors: "Error= Statement.executeUpdate() cannot be called with a statement that returns a ResultSet." and "Error= A lock could not be obtained within the time requested". I think it is because too much pool connections, so please anybody can help me increasing the performance or maybe the sql is wrong i dont know, but the CRUD in the other tables (primera, segunda, proyecto) works very well! Thanks for your time by the way!

Answer 1

There are two big problems in your current design:

1. Connection variables must be declared locally per method. You're declaring the Connection as a field, so if two threads accessing the same ConnectionDerby object instance performing different operations at the same time will fall into a race condition, thus having strange problems like posted in this error message: Error= Statement.executeUpdate() cannot be called with a statement that returns a ResultSet, which means a thread A tried to perform an update and thread B tried to perform a read operation, both at the same time, on the same instance, and the race condition mixed both the Connection and Statement instances.

2. You're opening the connections manually. This is a bad idea since opening a connection has a high cost in term of performance. You should use instead a database connection pool.

Having said this, you can raise the performance and fix the problems in your project by:

• Using a database connection pool. There are several options, I recommend using BoneCP.
• Remove your Connection and Statement field from ConnectionDerby class, instead add new Connection and Statement variables inside each method. Remember to always close the resources after using them.

More info:

• How to properly keep a DB connection from a Connection Pool opened in JBoss
• Should a database connection stay open all the time or only be opened when needed?

Probably you're not using JBoss, but the concepts in the answer applies for this case as well.