CODEWITHSUNDEEP
javaembedded-databasejavadbderby
Clark Minor
Clark Minor

Reputation: 291

Setting up an embedded Derby database in a standalone Java application

I'm trying to setup an embedded Derby database for a standalone Java application, but after pouring through all sorts of documentation, I just can't seem to find any simple explanations or examples. I'm using Eclipse with the Derby plugin and have enabled Derby nature for my project.

I found an example of using an embedded Derby database in a standalone address book as well as an overview of using Derby in Eclipse (that doesn't seem to cover the embedded deployment), but I still feel like I'm missing something fundamental.

This is my first time attempting to use a database with Java, and I'm a little confused, so here are my basic questions:

Snippets of code would be very helpful!

Upvotes: 29

Views: 62802

Answers (5)

Mario
Mario

Reputation: 51

I suggest that you use a class named ConnectionDerby, where put all the logic and the parameters to Select, insert, update, Delete, and as a embedded database i comprobate if a database already exists, if not exists i created then, i hope this code help you, sorry or my english and i am newbie using this database in java, but i hope this help you to understand....

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\\nombrebasededatos.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\\nombrebasededatos.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("Sistema Creado por Mario José Echeverría");
        System.out.println("NO SE ENCONTRO LA BASE DE DATOS");
        System.out.println("CREANDO BASE DE DATOS EN DERBY DATABASE");
        String createTableProyecto = "Sentence to create first table";
        String createTablePrimer = "Sentence to create second table";
        String createTableTopCoat = "Sentence to create third table";
        String createTableCotizacion = "Sentence to create fourth table";
        CrearBD(createTableProyecto);
        CrearBD(createTablePrimer);
        CrearBD(createTableTopCoat);
        CrearBD(createTableCotizacion);
        //*************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\\nombrebasededatos.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\\nombrebasededatos.db");
        sttm = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        //String sqlcad = "Select nombre, m2xgal, pregal, precub, descripcion from primer";
        rs = sttm.executeQuery(sqlcad);
        return rs;
    } catch (Exception e) {
        System.out.println("Error= " + e.getMessage());
        return rs;
    }
}
}

Upvotes: 3

simgineer
simgineer

Reputation: 1898

If you're ok with switching to the netbeans IDE here are two useful tutorials which I was able to get working in the ide (i'm having some minor issues with the installer). It uses JPA which is an abstraction that simplifies a lot of database interaction.

https://blogs.oracle.com/geertjan/entry/embedded_database_for_netbeans_platform

http://platform.netbeans.org/tutorials/nbm-crud.html

To address some of your inquiries:

  1. If you're using java and relation dbs i would highly recommend JPA. Otherwise you are using JDBC to interact with your database and using SQL.
  2. Traditionally you use a utility or run a script to create the table schema however since you are going for embedded you might be interested (as i am) in having the db and schema create it self dynamically so you don't have to run this script every time you install your application. This is doable with derby's embedded JPA configuration which the tutorial covers.
  3. if you are running an embedded derby database there is no separate thread or socket that you start up. you app will use the jpa or derby api which will use file locking to access the derby files. In my definition an embedded database does not have a separate thread or process listening on a socket handling multiple request.

Hope this helps and Good luck!

Upvotes: 1

Jan Bodnar
Jan Bodnar

Reputation: 11657

To use Derby in Java in embedded mode, we need to do the following steps:

  • Use the org.apache.derby.jdbc.EmbeddedDriver driver, located in the derbyclient Maven dependency
  • Use the connection string for embedded mode: jdbc:derby:dbname
  • Set up the Derby system home: System.setProperty("derby.system.home", "/home/janbodnar/.derby");
  • Shut down Derby programatically at the end: DriverManager.getConnection("jdbc:derby:;shutdown=true");
  • Handle XJ015 error, which is triggered at successfull shutdown

Full working examples can be found at my Java JDBC Derby programming tutorial.

Upvotes: 4

Vit Bernatik
Vit Bernatik

Reputation: 3802

I never did derby (although did once mysql) and got all going from this simple example. Actually I did not even read the talk - I just did scroll to the middle where self-explanatory example is.

Upvotes: 0

danes
danes

Reputation: 1

Those blogs n url are very wonderful but I will suggest the OP switch over to NetBeans even though I used d ClientDriver version of Java Derby drivers and I create a class or method to start up the database automatically at start up time so that I don't encounter any SQLException at run time and it has been working. Though I do use NetworkServerControl class to start up my database at run time going like diz 

NetworkServerControl server=new NetworkServerControl(InetAddress.getLocalHost(),1527);
server.start (null);
//Class.forName n DriverManager.getConnection() declarations goes here.

Upvotes: -1

Related Questions