JDBC

JDBC is a set of Java classes and interfaces for executing statements SQL

It is the CLI part of Java (Call-Level Interface)

It was jointly developed by JavaSoft, Sybase, Informix and IBM among others

JDBC allows the manipulation of any SQL database

You don't need to do a specific program to manipulate Oracle, Sybase, etc...

Our program can handle any database

Bringing together Java with JDBC, we get programs that can run on any platform, and they can manipulate any database

The classes and interfaces JDBC is located within the package java.sql

Process of working with JDBC

The JDBC worker process consists of the following steps:

1. Connect to the database (using DriverManager)

2. Issue SQL statements (using Statement, PreparedStatement, CallableStatement)

3. Processing the results (ResultSet)

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.SQLException;

class example {
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;

    public example() {
        int x = 0;
        String s = "";
        float f = 0.0f;
        String url = "jdbc: odbc: data";
        String user = "login";
        String pass = "password";

        try {
            con = DriverManager.getConnection(url, user, pass);
            stmt = con.createStatement();
            rs = stmt.executeQuery("SELECT d1, d2, d3 FROM Table1");

            while(rs.next()) {
                x = rs.getInt("d1");
                s = rs.getString("d2");
                f = rs.getFloat("d3");
            }
            rs.close();
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finaly {
            try {
                if (with! = null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

JDBC Driver

Each particular database implements the JDBC interface in a particular way, as do the helper and utility classes that are required for that database

That is why they are necessary drivers of databases

The same application will be able to connect to different databases simply by changing that driver (we should not confuse the database driver with the class Driver, because they are different things)

The interface Driver specifies the methods that any JDBC driver must implement, so drivers can be loaded in two ways:

1. When you start the class Driver, the DriverManager consultation the property jdbc.drivers

   This property contains a list of drivers (classes) that must be loaded

   To do this, you must run a command like this (to use ODBC) on the command line:

   java -Djdbc.drivers=sun.jdbc.odbc.JdbcOdbcDriver application

2. If you want to enter a new driver after the DriverManager is initialized we should use the method forname of the class Class

   To do this we must include in our code the following statement (to use ODBC):

   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

   It is advisable to use of static in our application (because the driver will only load once, when loading the class)

Tipos de driver:

• Bridge JDBC-ODBC

  Translates JDBC to ODBC and then relays it to the ODBC driver of the machine

  Is the ODBC driver actually communicates with the database

  Is included in the JDK but does not include JDBC2

  Inconvenience:

  • It's useful for testing, but it's slow in production

  • ODBC driver required on client (lower portability)

• Driver JDBC on a native driver of the database

  Relays JDBC the native driver installed on the machine

  The native driver is the one that actually communicates with the database

  Drawback:

  • need for native driver (lower portability)
• Driver Java on network

  Translates JDBC calls into a network protocol independent of the platform that contact with the server

  The server translates these requests to the specific protocol of each database

  It uses a middleware on the network server that is capable of connecting customers with pure Java to many different databases

  Advantage:

  • is fast, platform-independent and requires no installation on the client

• Driver pure Java and native protocol

  Translates the JDBC calls to the specific protocol of the database by directly contacting it

Get connections

Using the specific driver we will make the connections, but connection requests must be made using the DriverManager

Once the class Driver has been uploaded and registered, the DriverManager you can establish connections to the database by using these two steps:

1. The method is called DriverManager.getConnection(url, user, pass); and you get an object of type Connection

2. The DriverManager tests the registered drivers to see if you can establish the connection and if it wasn't possible, launch a SQLException

A single application can have multiple connections to the same database or multiple connections to other databases (up to the maximum allowed by the database)

The parameters supported by getConnection are the url (which is a subprotocol that uses the database to make the connection), the user (the username to be connected) and the pass (the password that the user uses to connect)

The parameter url has the following format:

jdbc:<subprotocol>:<subname>

The subprotocol is particular to each database, and uses the DriverManager to find the proper driver to handle it

The subname depends on the subprotocol specific

The driver will be responsible to interpret and will help you to locate the database

Our app won't work with the specific driver, but will work on the DriverManager

In this way applications can work with the object Connection without worrying about the type of database we're working with (no code modifications need to be made, just change the parameter url)

The connection should always be closed at the end, because otherwise resources are consumed unnecessarily, although connections can be reused, it is not advisable, it is better to always use a new connection

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

class connection{
    static{
        try{
            Class.forName("oracle.jdbc.driver.OracleDriver");
        }catch(ClassNotFoundException e){
            System.out.println("Could not load the BD driver");
        }
     }

     Connection con = null;

     public conexion(){
         try{
             String url = "jdbc:oracle:thin@localhost:1521:balance";
             String user = "countable";
             String pass = "#cap1128";

             con = DriverManager.getConnection(url, user, pass);

             //from this line we would include the SQL queries that we need
         }catch(SQLException e){
             e.printStackTrace();
         }finaly{
             try{
                 if(con != null){
                     con.close();
                 }
             }catch(SQLException e){
                 e.printStackTrace();
             }
         }
     }
}

In the example we have used a skeleton of connection to Oracle

To use the connection we need to create an object of type Connection, which represents an open session with the database

The object provides a context with which to issue SQL statements and get results

The object Connection must be initialized to an initial null, so we can check if there was a connection because we'll include a block try to handle bugs, which will launch SQLException

To close the connection must be made using the method close(), within a finaly (which will also have its block try that spear SQLException)

The interface Statement

The connection allows us to create objects Statement by using the method createStatemen()

Connection con = DriverManager.getConnection(url, user, pass);
Statement stmt = con.createStatement()

The objects Statement allow sql statements to be executed and results obtained (using the objects ResultSet)

The methods to execute SQL accept String (JDBC is CLI), which can be composed dynamically based on values contained in variables

To compose the String SQL, concatenate the different SQL fragments (static and variable)

String idaccount = "572001";
String sql = "SELECT id, balance FROM accounting WHERE id = '" + idaccount + "'";

There are three ways to execute SQL statements:

1. By using the method executeQuery(<sql>) for queries using the statement SELECT that produces tuples as a result (returns an object of type ResultSet)

   rs = stmt.executeQuery("SELECT a, b, c FROM Table");

   • It is like a table that stores the result of the query

   • You have a number of query methods

   • It's like a cursor (in PL/SQL terminology)

2. By using the method executeUpdate(<sql>) for updates by using the sentences INSERT, DELETE, UPDATE, as well as DDL commands (CREATE, DROP, ALTER TABLE, ADD) and PL/SQL blocks (between begin and end blocks)

   stmt.executeUpdate("DROP TABLE Table");
   int n = stmt.executeUpdate("UPDATE Table SET a = a + 1");

   • Returns an integer that indicates the number of rows modified by the command

   • With DDL commands it returns 0

3. By using the method execute(<sql>) that executes any SQL statement

   • If we used a QUERYReturn True

     • The ResultSet can be obtained by the method getResultSet()

   • If we used a UPDATEReturn False

     • The total number of modified rows can be obtained by using the method getUpdateCount()

After processing the result, the Statement by using the method close()

This method will also close the ResultSet associate, however, Sun recommended closing the ResultSet explicitly to avoid errors unwanted

The same Statement can be reused in the same connection to execute different statements

The interface ResultSet

The object ResultSet acts as a cursor within the results

The first time you read it points to the first result, but does not read it, so you have to read the first row moving forward using the method next()

The method next() returns True if you were able to move forward or False if you could not

ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table");

while(rs.next() ){
    //Processing of each row
}

To obtain the values of the ResultSet methods are used get that have the following format get<type>(<column>)

To name the columns we can either do it by name or by an index, which starts at 1, their numbering is given by the order in which it was entered in the SELECT

You have to pay attention to the dates, because they are kept as java.sql.Date, not as java.util.Date as you might expect

int a = 0;
String b ="";
java.sql.Date c = null;
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table");

while(rs.next() ){
    a = rs.getInt("a");    //rs.getInt(1);
    b = rs.getString("b");    //rs.getString(2);
    c = rs.getDate("c");    //rs.getDate(3);
    System.out.println("ROW = " + a + " " + b + " " + c);
}

It should be close ResultSet although it is implicitly closes when you close or reuse the Statement that created it

When you have read a null SQL using one of the methods get<tipo>, it returns:

• A value null Java for those methods that return Java objects (getString(), getBigDecimal(), getDate(), getTime(), getTimestamp(), getObject(), etc)

• A value of 0 for those methods that return numeric types (getByte(), getShort(), getInt(), getLong(), getFloat(), getDouble() )

• A value False for the method getBoolean()

To determine if a given value was null, you must first try to read the column and use the ResulSet wasNull() to know if it was null

Return True yes it was, False otherwise

int a = 0;
String b ="";
java.sql.Date c = null;
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table");

while(rs.next() ){
    a = rs.getInt("a");    //rs.getInt(1);
    if(a == 0 && rs.wasNull() ){
        //The null is processed
    }
    b = rs.getString("b");    //rs.getString(2);
    if(b == null){
        //The null is processed
    }
    c = rs.getDate("c");    //rs.getDate(3);
    if(c == null){
        //The null is processed
    }
    System.out.println("ROW = " + a + " " + b + " " + c);
}

The interface PreparedStatement

Every time you launch a Statement the database must interpret it and calculate a query plan

But when using PreparedStatement, can be executed multiple times, getting an increase in performance by having the query already analyzed and optimized

Not all databases support PreparedStatement, you have to read the documentation of it to know if they can be used

The objects PreparedStatement derived from the Statement obtained from the connection, using the prepareStatement(<sql>);

A PreparedStatement to launch precompiled SQL statements, you can parameterize one or more entries using the ?, whose values may be changed in different executions of the statement

Parameterize entries is useful when we do not know the values of certain SQL data types in the target database,

The parameterized value gets the correct value from the database driver, so we won't have to worry about the types

Before the SQL statement can be executed we will need to assign a value to the input parameters

To assign values use the methods set<type>(column, value); being the type compatible with the parameter

To run methods are used execute of Statement

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

class connection{
    static{
        try{
            Class.forName("oracle.jdbc.driver.OracleDriver");
        }catch(ClassNotFoundException e){
            System.out.println("Could not load the BD driver");
        }
     }

     Connection con = null;
     PreparedStatement ps = null;

     public connection(){
         try{
             String url = "jdbc:oracle:thin@localhost:1521:balance";
             String user = "accountant";
             String pass = "#cap1128";
             float salary[] = {1500, 2000, 1205.75f};
             String name[] = {"Juan", "Ana", "María"};
             String sql = "UPDATE table SET salary=? WHERE name=?";

             con = DriverManager.getConnection(url, user, pass);
             ps = con.prepareStatement(sql);

            for(int i=0; i<salarys.length; i++){
                ps.setFloat(1, salary[i]);
                ps.setString(2, name[i]);
                ps.executeUpdate();
            }
            ps.close();
         }catch(SQLException e){
             e.printStackTrace();
         }finaly{
             try{
                 if(con != null){
                     con.close();
                 }
             }catch(SQLException e){
                 e.printStackTrace();
             }
         }
     }
}

In the example we have used a PreparedStatement for Oracle