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

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!

Answers


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:

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


Need Your Help

How to create scheduler to run my script every night at 12.00- Selenium WebDriver

java testng

Currently working on Selenium WebDriver and using Java. I have a project called*Test*.

Nagios: creating custom menu for an authenticated user

nagios

Is there any 'easy' way to create customized web gui (for example, menu, default home page etc.) for a Nagios authenticated user? I have created a user for a customer, who has access to certain