Java Database Access

Content:

  1. Introduction
  2. Plain JDBC
  3. JDBC RowSet
  4. Hibernate
  5. JPA
  6. Special JDBC features
  7. Java SE vs Java EE
  8. SQLJ (embedded SQL)
  9. Other
  10. Calling stored procedures
  11. Java SP and UDF
    1. IBM DB2
    2. Apache Derby

Introduction:

Java comes with several database access technologies. And additional options are available as third party libraries.

All the examples below will use the following database table:

CREATE TABLE T1 (
    F1 INTEGER NOT NULL,
    F2 VARCHAR(50),
    PRIMARY KEY(F1)
);

Most of the examples will be implementing this super simple DAL:

Data class:

package javadb;

public class T1 {
    private int f1;
    private String f2;
    public T1() {
        this(0, "");
    }
    public T1(int f1, String f2) {
        this.f1 = f1;
        this.f2 = f2;
    }
    public int getF1() {
        return f1;
    }
    public void setF1(int f1) {
        this.f1 = f1;
    }
    public String getF2() {
        return f2;
    }
    public void setF2(String f2) {
        this.f2 = f2;
    }
    @Override
    public String toString() {
        return String.format("[%d,%s]", f1, f2);
    }
}

Interface:

package javadb;

import java.sql.SQLException;
import java.util.List;

public interface DAL {
    public T1 getOneByPK(int f1) throws SQLException;
    public List<T1> getAll() throws SQLException;
    public List<T1> getRangeOfPK(int f1_start, int f1_end) throws SQLException;
    public List<T1> getContainingF2(String f2) throws SQLException;
    public void saveChanges(T1 o) throws SQLException;
    public void saveNew(T1 o) throws SQLException;
    public void remove(int f1) throws SQLException;
}

Demo code:

package javadb;

import java.sql.SQLException;
import java.util.List;

public class Demo {
    public static void updateF2(DAL dal, int f1, String f2) throws SQLException {
        T1 o = dal.getOneByPK(f1);
        o.setF2(f2);
        dal.saveChanges(o);
    }
    public static void print(List<T1> lst) {
        System.out.print("{");
        for(int i = 0; i < lst.size(); i++) {
            if(i > 0) System.out.print(",");
            System.out.print(lst.get(i));
        }
        System.out.println("}");
    }
    public static void test(String lbl, DAL dal) throws SQLException
    {
        System.out.println(lbl + ":");
        System.out.println(dal.getOneByPK(2));
        updateF2(dal, 2, "BBB");
        System.out.println(dal.getOneByPK(2));
        updateF2(dal, 2, "BB");
        System.out.println(dal.getOneByPK(2));
        print(dal.getAll());
        print(dal.getRangeOfPK(2, 4));
        print(dal.getContainingF2("B"));
        dal.saveNew(new T1(999, "XXX"));
        print(dal.getAll());
        dal.remove(999);
        print(dal.getAll());
    }
}

The discussion about what a DAL is and why it is good is beyond the scope for this article. Read about DAL here.

Plain JDBC:

JDBC is the foundation for all database access in Java. All the higher level interfaces (including ORM) are build on top of JDBC.

JDBCis also the the only database access technology suitable for bulk updates.

All Java developers should know JDBC.

Unlike most other programming languages then the use of database specific API's is practically never seen in Java.

Java JDBC code use interfaces and dynamically load a driver with a JDBC implementation.

Traditional JDBC style:

package javadb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class JDBCDALOld extends Demo implements DAL {
    private String constr;
    private String usr;
    private String pwd;
    public JDBCDALOld(String constr, String usr, String pwd) {
        this.constr = constr;
        this.usr = usr;
        this.pwd = pwd;
    }
    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(constr, usr, pwd);
    }
    @Override
    public T1 getOneByPK(int f1) throws SQLException {
        T1 res;
        Connection con = getConnection();
        PreparedStatement pstmt = con.prepareStatement("SELECT f1,f2 FROM t1 WHERE f1 = ?");
        pstmt.setInt(1, f1);
        ResultSet rs = pstmt.executeQuery();
        if(rs.next()) {
            res = new T1(rs.getInt(1), rs.getString(2));
        } else {
            res = null;
        }
        rs.close();
        pstmt.close();
        con.close();
        return res;
    }
    @Override
    public List<T1> getAll() throws SQLException {
        List<T1> res = new ArrayList<T1>();
        Connection con = getConnection();
        PreparedStatement pstmt = con.prepareStatement("SELECT f1,f2 FROM t1");
        ResultSet rs = pstmt.executeQuery();
        while(rs.next()) {
            res.add(new T1(rs.getInt(1), rs.getString(2)));
        }
        rs.close();
        pstmt.close();
        con.close();
        return res;
    }
    @Override
    public List<T1> getRangeOfPK(int f1_start, int f1_end) throws SQLException {
        List<T1> res = new ArrayList<T1>();
        Connection con = getConnection();
        PreparedStatement pstmt = con.prepareStatement("SELECT f1,f2 FROM t1 WHERE f1 BETWEEN ? AND ?");
        pstmt.setInt(1, f1_start);
        pstmt.setInt(2, f1_end);
        ResultSet rs = pstmt.executeQuery();
        while(rs.next()) {
            res.add(new T1(rs.getInt(1), rs.getString(2)));
        }
        rs.close();
        pstmt.close();
        con.close();
        return res;
    }
    @Override
    public List<T1> getContainingF2(String f2) throws SQLException {
        List<T1> res = new ArrayList<T1>();
        Connection con = getConnection();
        PreparedStatement pstmt = con.prepareStatement("SELECT f1,f2 FROM t1 WHERE f2 LIKE ?");
        pstmt.setString(1, "%" + f2 + "%");
        ResultSet rs = pstmt.executeQuery();
        while(rs.next()) {
            res.add(new T1(rs.getInt(1), rs.getString(2)));
        }
        rs.close();
        pstmt.close();
        con.close();
        return res;
    }
    @Override
    public void saveChanges(T1 o) throws SQLException {
        Connection con = getConnection();
        PreparedStatement pstmt = con.prepareStatement("UPDATE t1 SET f2 = ? WHERE f1 = ?");
        pstmt.setString(1, o.getF2());
        pstmt.setInt(2,  o.getF1());
        pstmt.executeUpdate();
        pstmt.close();
        con.close();
    }
    @Override
    public void saveNew(T1 o) throws SQLException {
        Connection con = getConnection();
        PreparedStatement pstmt = con.prepareStatement("INSERT INTO t1 VALUES(?,?)");
        pstmt.setInt(1,  o.getF1());
        pstmt.setString(2, o.getF2());
        pstmt.executeUpdate();
        pstmt.close();
        con.close();
    }
    @Override
    public void remove(int f1) throws SQLException {
        Connection con = getConnection();
        PreparedStatement pstmt = con.prepareStatement("DELETE FROM t1 WHERE f1 = ?");
        pstmt.setInt(1, f1);
        pstmt.executeUpdate();
        pstmt.close();
        con.close();
    }
    public static void main(String[] args) throws SQLException {
        test("Plain JDBC - MySQL", new JDBCDALOld("jdbc:mysql://localhost/Test", "arne", "xxxxxx"));
        test("Plain JDBC - Oracle", new JDBCDALOld("jdbc:oracle:thin:@localhost:1521:xe", "arne", "xxxxxx"));
        test("Plain JDBC - DB2", new JDBCDALOld("jdbc:db2://localhost:50000/Test", "arne", "xxxxxx"));
    }
}

Note that the code above does not do any exception handling, which would not be good for production code.

Java 1.7 introduced the try with resource construct that made it so much easier to ensure that connection etc. get properly closed.

package javadb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class JDBCDALModern extends Demo implements DAL {
    private String constr;
    private String usr;
    private String pwd;
    public JDBCDALModern(String constr, String usr, String pwd) {
        this.constr = constr;
        this.usr = usr;
        this.pwd = pwd;
    }
    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(constr, usr, pwd);
    }
    @Override
    public T1 getOneByPK(int f1) throws SQLException {
        T1 res;
        try(Connection con = getConnection()) {
            try(PreparedStatement pstmt = con.prepareStatement("SELECT f1,f2 FROM t1 WHERE f1 = ?")) {
                pstmt.setInt(1, f1);
                try(ResultSet rs = pstmt.executeQuery()) {
                    if(rs.next()) {
                        res = new T1(rs.getInt("f1"), rs.getString("f2"));
                    } else {
                        res = null;
                    }
                }
            }
        }
        return res;
    }
    @Override
    public List<T1> getAll() throws SQLException {
        List<T1> res = new ArrayList<T1>();
        try(Connection con = getConnection()) {
            try(PreparedStatement pstmt = con.prepareStatement("SELECT f1,f2 FROM t1")) {
                try(ResultSet rs = pstmt.executeQuery()) {
                    while(rs.next()) {
                        res.add(new T1(rs.getInt("f1"), rs.getString("f2")));
                    }
                }
            }
        }
        return res;
    }
    @Override
    public List<T1> getRangeOfPK(int f1_start, int f1_end) throws SQLException {
        List<T1> res = new ArrayList<T1>();
        try(Connection con = getConnection()) {
            try(PreparedStatement pstmt = con.prepareStatement("SELECT f1,f2 FROM t1 WHERE f1 BETWEEN ? AND ?")) {
                pstmt.setInt(1, f1_start);
                pstmt.setInt(2, f1_end);
                try(ResultSet rs = pstmt.executeQuery()) {
                    while(rs.next()) {
                        res.add(new T1(rs.getInt("f1"), rs.getString("f2")));
                    }
                }
            }
        }
        return res;
    }
    @Override
    public List<T1> getContainingF2(String f2) throws SQLException {
        List<T1> res = new ArrayList<T1>();
        try(Connection con = getConnection()) {
            try(PreparedStatement pstmt = con.prepareStatement("SELECT f1,f2 FROM t1 WHERE f2 LIKE ?")) {
                pstmt.setString(1, "%" + f2 + "%");
                try(ResultSet rs = pstmt.executeQuery()) {
                    while(rs.next()) {
                        res.add(new T1(rs.getInt("f1"), rs.getString("f2")));
                    }
                }
            }
        }
        return res;
    }
    @Override
    public void saveChanges(T1 o) throws SQLException {
        try(Connection con = getConnection()) {
            try(PreparedStatement pstmt = con.prepareStatement("UPDATE t1 SET f2 = ? WHERE f1 = ?")) {
                pstmt.setString(1, o.getF2());
                pstmt.setInt(2,  o.getF1());
                pstmt.executeUpdate();
            }
        }
    }
    @Override
    public void saveNew(T1 o) throws SQLException {
        try(Connection con = getConnection()) {
            try(PreparedStatement pstmt = con.prepareStatement("INSERT INTO t1 VALUES(?,?)")) {
                pstmt.setInt(1,  o.getF1());
                pstmt.setString(2, o.getF2());
                pstmt.executeUpdate();
            }
        }
    }
    @Override
    public void remove(int f1) throws SQLException {
        try(Connection con = getConnection()) {
            try(PreparedStatement pstmt = con.prepareStatement("DELETE FROM t1 WHERE f1 = ?")) {
                pstmt.setInt(1, f1);
                pstmt.executeUpdate();
            }
        }
    }
    public static void main(String[] args) throws SQLException {
        test("Plain JDBC - MySQL", new JDBCDALModern("jdbc:mysql://localhost/Test", "arne", "xxxxxx"));
        test("Plain JDBC - Oracle", new JDBCDALOld("jdbc:oracle:thin:@localhost:1521:xe", "arne", "xxxxxx"));
        test("Plain JDBC - DB2", new JDBCDALOld("jdbc:db2://localhost:50000/Test", "arne", "xxxxxx"));
    }
}

JDBC RowSet:

Java 1.4 added RowSet to JDBC.

RowSet is build on top of ResultSet.

Both ResultSet and RowSet are interfaces with very few restrictions on actual implementation.

But even though implementations may vary then it may be beneficial conceptually to think about them as:

ResultSet
Data being streamed from database as you read through the ResultSet
RowSet
A copy of all data kept in memory

Let me emphasize again that there is absolutely no guarantee that they are implemented like this. It is just a way of thinking.

Example:

package javadb;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.RowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;

public class RowSetDAL extends Demo implements DAL {
    private String constr;
    private String usr;
    private String pwd;
    public RowSetDAL(String constr, String usr, String pwd) {
        this.constr = constr;
        this.usr = usr;
        this.pwd = pwd;
    }
    private RowSet getRowSet(String sql) throws SQLException {
        RowSetFactory rsf = RowSetProvider.newFactory();
        RowSet rs = rsf.createJdbcRowSet();
        rs.setUrl(constr);
        rs.setUsername(usr);
        rs.setPassword(pwd);
        rs.setCommand(sql);
        return rs;
    }
    @Override
    public T1 getOneByPK(int f1) throws SQLException {
        T1 res;
        try(RowSet rs = getRowSet("SELECT f1,f2 FROM t1 WHERE f1 = ?")) {
            rs.setInt(1, f1);
            rs.execute();
            if(rs.next()) {
                res = new T1(rs.getInt("f1"), rs.getString("f2"));
            } else {
                res = null;
            }
        }
        return res;
    }
    @Override
    public List<T1> getAll() throws SQLException {
        List<T1> res = new ArrayList<T1>();
        try(RowSet rs = getRowSet("SELECT f1,f2 FROM t1")) {
            rs.execute();
            while(rs.next()) {
                res.add(new T1(rs.getInt("f1"), rs.getString("f2")));
            }
        }
        return res;
    }
    @Override
    public List<T1> getRangeOfPK(int f1_start, int f1_end) throws SQLException {
        List<T1> res = new ArrayList<T1>();
        try(RowSet rs = getRowSet("SELECT f1,f2 FROM t1 WHERE f1 BETWEEN ? AND ?")) {
            rs.setInt(1, f1_start);
            rs.setInt(2, f1_end);
            rs.execute();
            while(rs.next()) {
                res.add(new T1(rs.getInt(1), rs.getString(2)));
            }
        }
        return res;
    }
    @Override
    public List<T1> getContainingF2(String f2) throws SQLException {
        List<T1> res = new ArrayList<T1>();
        try(RowSet rs = getRowSet("SELECT f1,f2 FROM t1 WHERE f2 LIKE ?")) {
            rs.setString(1, "%" + f2 + "%");
            rs.execute();
            while(rs.next()) {
                res.add(new T1(rs.getInt(1), rs.getString(2)));
            }
        }
        return res;
    }
    @Override
    public void saveChanges(T1 o) throws SQLException {
        try(RowSet rs = getRowSet("SELECT f1,f2 FROM t1 WHERE f1 = ?")) {
            rs.setInt(1, o.getF1());
            rs.execute();
            rs.last();
            rs.updateInt("f1", o.getF1());
            rs.updateString("f2", o.getF2());
            rs.updateRow();
        }
    }
    @Override
    public void saveNew(T1 o) throws SQLException {
        try(RowSet rs = getRowSet("SELECT f1,f2 FROM t1")) {
            rs.execute();
            rs.moveToInsertRow();
            rs.updateInt("f1", o.getF1());
            rs.updateString("f2", o.getF2());
            rs.insertRow();;
        }
    }
    @Override
    public void remove(int f1) throws SQLException {
        try(RowSet rs = getRowSet("SELECT f1,f2 FROM t1 WHERE f1 = ?")) {
            rs.setInt(1, f1);
            rs.execute();
            rs.last();
            rs.deleteRow();
        }
    }
    public static void main(String[] args) throws SQLException {
        test("JDBC RowSet - MySQL", new RowSetDAL("jdbc:mysql://localhost/Test", "arne", "xxxxxx"));
        test("JDBC RowSet - Oracle", new RowSetDAL("jdbc:oracle:thin:@localhost:1521:xe", "arne", "xxxxxx"));
        test("JDBC RowSet - DB2", new RowSetDAL("jdbc:db2://localhost:50000/Test", "arne", "xxxxxx"));
    }
}

Besides JdbcRowSet that is a connected RowSet there is also CachedRowSet that is a disconnected RowSet.

I will not go more in RowSet as it is something that practically no Java developers use in practice.

Hibernate:

Hibernate is a widely used ORM in Java.

ORM or O/R-Mapper or Object Relational Mapper frameworks are frameworks that maps between an object model and a relational data model. They enable the application code to deal only with the object model and then the ORM translate to relational database and SQL. Consider it "SQL free" database programming.

Hibernate was not the first major ORM for Java. That honor goes to entity EJB's with CMP (Container Managed Persistence) which is from 1999. That was actually the first mainstream ORM framework.

But EJB CMP was never super popular by developers. And in 2001 the Hibernate ORM framework showed up and quickly gained popularity.

Hibernate was simpler and more practical oriented. And it worked in both Java EE and Java SE context.

Hibernate's approach has been copied many times. Including:

Hibernate use two types of configuration files:

Hibernate config file hibconf.xml for demo with MySQL:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <!--<property name="show_sql">true</property>-->
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost/Test</property>
        <property name="hibernate.connection.username">arne</property>
        <property name="hibernate.connection.password">xxxxxx</property>
        <property name="hibernate.connection.pool_size">5</property>
        <mapping file="/work/hibmap.xml"/>
    </session-factory>
</hibernate-configuration>

Mapping config file hibmap.xml for demo:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping default-lazy="false">
    <class name="javadb.T1" table="t1">
        <id name="f1" column="f1">
            <generator class="assigned"/>
        </id>
        <property name="f2">
            <column name="f2"/>
        </property>
    </class>
</hibernate-mapping>

Note this:

<!--<property name="show_sql">true</property>-->

If set to true then Hibernate will dump all executed SQL statements. That is great for troubleshooting performance problems.

Example:

package javadb;

import java.io.File;
import java.sql.SQLException;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

public class HibernateDALHQL extends Demo implements DAL {
    private SessionFactory sf;
    public HibernateDALHQL(String conffnm) {
        sf = new Configuration().configure(new File(conffnm)).buildSessionFactory();
    }
    @Override
    public T1 getOneByPK(int f1) {
        try(Session s = sf.openSession()) {
            return s.load(T1.class, f1);
        }
    }
    
    @Override
    public List<T1> getAll() {
        try(Session s = sf.openSession()) {
            Query q = s.createQuery("SELECT o FROM T1 AS o");
            @SuppressWarnings("unchecked")
            List<T1> res = q.list();
            return res;
        }
    }
    @Override
    public List<T1> getRangeOfPK(int f1_start, int f1_end) {
        try(Session s = sf.openSession()) {
            Query q = s.createQuery("SELECT o FROM T1 AS o WHERE f1 BETWEEN :f1_start AND :f1_end");
            q.setParameter("f1_start", f1_start);
            q.setParameter("f1_end", f1_end);
            @SuppressWarnings("unchecked")
            List<T1> res = q.list();
            return res;
        }
    }
    @Override
    public List<T1> getContainingF2(String f2) {
        try(Session s = sf.openSession()) {
            Query q = s.createQuery("SELECT o FROM T1 AS o WHERE f2 LIKE :f2");
            q.setParameter("f2", "%" + f2 + "%");
            @SuppressWarnings("unchecked")
            List<T1> res = q.list();
            return res;
        }
    }
    @Override
    public void saveChanges(T1 o) {
        try(Session s = sf.openSession()) {
            Transaction tx = s.beginTransaction();
            s.update(o);
            tx.commit();
        }
    }
    @Override
    public void saveNew(T1 o) {
        try(Session s = sf.openSession()) {
            Transaction tx = s.beginTransaction();
            s.save(o);
            tx.commit();
        }
    }
    @Override
    public void remove(int f1) {
        try(Session s = sf.openSession()) {
            Transaction tx = s.beginTransaction();
            s.delete(new T1(f1, ""));
            tx.commit();
        }
    }
    public static void main(String[] args) throws SQLException {
        test("Hibernate", new HibernateDALHQL("/work/hibconf.xml"));
    }
}

The example uses HQL (Hibernate Query Languuage) for the queries:

    @Override
    public List<T1> getAll() {
        try(Session s = sf.openSession()) {
            Query q = s.createQuery("SELECT o FROM T1 AS o");
            @SuppressWarnings("unchecked")
            List<T1> res = q.list();
            return res;
        }
    }
    @Override
    public List<T1> getRangeOfPK(int f1_start, int f1_end) {
        try(Session s = sf.openSession()) {
            Query q = s.createQuery("SELECT o FROM T1 AS o WHERE f1 BETWEEN :f1_start AND :f1_end");
            q.setParameter("f1_start", f1_start);
            q.setParameter("f1_end", f1_end);
            @SuppressWarnings("unchecked")
            List<T1> res = q.list();
            return res;
        }
    }
    @Override
    public List<T1> getContainingF2(String f2) {
        try(Session s = sf.openSession()) {
            Query q = s.createQuery("SELECT o FROM T1 AS o WHERE f2 LIKE :f2");
            q.setParameter("f2", "%" + f2 + "%");
            @SuppressWarnings("unchecked")
            List<T1> res = q.list();
            return res;
        }
    }

It is also possible to use SQL:

    @Override
    public List<T1> getAll() {
        try(Session s = sf.openSession()) {
            Query q = s.createSQLQuery("SELECT f1,f2 FROM t1");
            @SuppressWarnings("unchecked")
            List<Object[]> tmp = q.list();
            List<T1> res = tmp.stream().map(oa -> new T1((Integer)oa[0], (String)oa[1])).collect(Collectors.toList());
            return res;
        }
    }
    @Override
    public List<T1> getRangeOfPK(int f1_start, int f1_end) {
        try(Session s = sf.openSession()) {
            Query q = s.createSQLQuery("SELECT f1,f2 FROM t1 WHERE f1 BETWEEN :f1_start AND :f1_end");
            q.setParameter("f1_start", f1_start);
            q.setParameter("f1_end", f1_end);
            @SuppressWarnings("unchecked")
            List<Object[]> tmp = q.list();
            List<T1> res = tmp.stream().map(oa -> new T1((Integer)oa[0], (String)oa[1])).collect(Collectors.toList());
            return res;
        }
    }
    @Override
    public List<T1> getContainingF2(String f2) {
        try(Session s = sf.openSession()) {
            Query q = s.createSQLQuery("SELECT f1,f2 FROM t1 WHERE f2 LIKE :f2");
            q.setParameter("f2", "%" + f2 + "%");
            @SuppressWarnings("unchecked")
            List<Object[]> tmp = q.list();
            List<T1> res = tmp.stream().map(oa -> new T1((Integer)oa[0], (String)oa[1])).collect(Collectors.toList());
            return res;
        }
    }

and Criteria:

    @Override
    public List<T1> getAll() {
        try(Session s = sf.openSession()) {
            Criteria crit = s.createCriteria(T1.class);
            @SuppressWarnings("unchecked")
            List<T1> res = crit.list();
            return res;
        }
    }
    @Override
    public List<T1> getRangeOfPK(int f1_start, int f1_end) {
        try(Session s = sf.openSession()) {
            Criteria crit = s.createCriteria(T1.class);
            crit.add(Restrictions.between("f1",  f1_start,  f1_end));
            @SuppressWarnings("unchecked")
            List<T1> res = crit.list();
            return res;
        }
    }
    @Override
    public List<T1> getContainingF2(String f2) {
        try(Session s = sf.openSession()) {
            Criteria crit = s.createCriteria(T1.class);
            crit.add(Restrictions.like("f2", "%" + f2 + "%"));
            @SuppressWarnings("unchecked")
            List<T1> res = crit.list();
            return res;
        }
    }

It is recommended to use HQL or Criteria not SQL.

JPA:

Given the success of Hibernate the Java community started looking at standardizing an ORM API.

First attempt was JDO (Java Data Objects) that came out in 2003. It never took off.

But in 2006 came JPA (Java Persistence API) that was very closely modelled after Hibernate. And it became a success.

JPA is just an API. Different vendors provide different implementations of that API.

Well known JPA implementations are:

JPA can use both annotations and XML for configuration. Annotation is the most common.

So to use JPA the data class need to be annotated like:

package javadb;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="t1")
public class T1 {
    private int f1;
    private String f2;
    public T1() {
        this(0, "");
    }
    public T1(int f1, String f2) {
        this.f1 = f1;
        this.f2 = f2;
    }
    @Id
    @Column(name="f1")
    public int getF1() {
        return f1;
    }
    public void setF1(int f1) {
        this.f1 = f1;
    }
    @Column(name="f2")
    public String getF2() {
        return f2;
    }
    public void setF2(String f2) {
        this.f2 = f2;
    }
    @Override
    public String toString() {
        return String.format("[%d,%s]", f1, f2);
    }
}

JPA is configured in a META-INF/persistence.xml in classpath.

Example for Hibernate provider and MySQL:

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
             version="2.0">
   <persistence-unit name="javadb">
      <provider>org.hibernate.ejb.HibernatePersistence</provider>
      <class>javadb.T1</class>
      <exclude-unlisted-classes/>
      <properties>
          <!--<property name="show_sql">true</property>-->
          <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
          <property name="hibernate.connection.url" value="jdbc:mysql://localhost/Test"/>
          <property name="hibernate.connection.username" value="root"/>
          <property name="hibernate.connection.password" value=""/>
          <property name="hibernate.connection.pool_size" value="5"/>
      </properties>
   </persistence-unit>
</persistence>

Example:

package javadb;

import java.sql.SQLException;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;

public class JPADALJPQL extends Demo implements DAL {
    private EntityManagerFactory emf;
    public JPADALJPQL(String conffnm) {
        emf = Persistence.createEntityManagerFactory("javadb");
    }
    @Override
    public T1 getOneByPK(int f1) {
        EntityManager em = emf.createEntityManager();
        T1 res = em.find(T1.class, f1);
        em.close();
        return res;
    }
    @Override
    public List<T1> getAll() {
        EntityManager em = emf.createEntityManager();
        TypedQuery<T1> q = em.createQuery("SELECT o FROM T1 AS o", T1.class);
        List<T1> res = q.getResultList();
        em.close();
        return res;
    }
    @Override
    public List<T1> getRangeOfPK(int f1_start, int f1_end) {
        EntityManager em = emf.createEntityManager();
        TypedQuery<T1> q = em.createQuery("SELECT o FROM T1 AS o WHERE f1 BETWEEN :f1_start AND :f1_end", T1.class);
        q.setParameter("f1_start", f1_start);
        q.setParameter("f1_end", f1_end);
        List<T1> res = q.getResultList();
        em.close();
        return res;
    }
    @Override
    public List<T1> getContainingF2(String f2) {
        EntityManager em = emf.createEntityManager();
        TypedQuery<T1> q = em.createQuery("SELECT o FROM T1 AS o WHERE f2 LIKE :f2", T1.class);
        q.setParameter("f2", "%" + f2 + "%");
        List<T1> res = q.getResultList();
        em.close();
        return res;
    }
    @Override
    public void saveChanges(T1 o) {
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        // no nice update so find and set
        T1 o2 = em.find(T1.class, o.getF1());
        o2.setF2(o.getF2());
        em.getTransaction().commit();
        em.close();
    }
    @Override
    public void saveNew(T1 o) {
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        em.persist(o);
        em.getTransaction().commit();
        em.close();
    }
    @Override
    public void remove(int f1) {
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        em.remove(em.merge(new T1(f1, ""))); // need to merge to make the object managed
        em.getTransaction().commit();
        em.close();
    }
    public static void main(String[] args) throws SQLException {
        test("Hibernate", new JPADALJPQL("/work/hibconf.xml"));
    }
}

The example uses JPQL (Java Persistence Query Languuage) for the queries:

    @Override
    public List<T1> getAll() {
        EntityManager em = emf.createEntityManager();
        TypedQuery<T1> q = em.createQuery("SELECT o FROM T1 AS o", T1.class);
        List<T1> res = q.getResultList();
        em.close();
        return res;
    }
    @Override
    public List<T1> getRangeOfPK(int f1_start, int f1_end) {
        EntityManager em = emf.createEntityManager();
        TypedQuery<T1> q = em.createQuery("SELECT o FROM T1 AS o WHERE f1 BETWEEN :f1_start AND :f1_end", T1.class);
        q.setParameter("f1_start", f1_start);
        q.setParameter("f1_end", f1_end);
        List<T1> res = q.getResultList();
        em.close();
        return res;
    }
    @Override
    public List<T1> getContainingF2(String f2) {
        EntityManager em = emf.createEntityManager();
        TypedQuery<T1> q = em.createQuery("SELECT o FROM T1 AS o WHERE f2 LIKE :f2", T1.class);
        q.setParameter("f2", "%" + f2 + "%");
        List<T1> res = q.getResultList();
        em.close();
        return res;
    }

It is also possible to use SQL:

    @Override
    public List<T1> getAll() {
        EntityManager em = emf.createEntityManager();
        Query q = em.createNativeQuery("SELECT f1,f2 FROM t1");
        @SuppressWarnings("unchecked")
        List<Object[]> tmp = q.getResultList();
        List<T1> res = tmp.stream().map(oa -> new T1((Integer)oa[0], (String)oa[1])).collect(Collectors.toList());
        em.close();
        return res;
    }
    @Override
    public List<T1> getRangeOfPK(int f1_start, int f1_end) {
        EntityManager em = emf.createEntityManager();
        Query q = em.createNativeQuery("SELECT f1,f2 FROM t1 WHERE f1 BETWEEN :f1_start AND :f1_end");
        q.setParameter("f1_start", f1_start);
        q.setParameter("f1_end", f1_end);
        @SuppressWarnings("unchecked")
        List<Object[]> tmp = q.getResultList();
        List<T1> res = tmp.stream().map(oa -> new T1((Integer)oa[0], (String)oa[1])).collect(Collectors.toList());
        em.close();
        return res;
    }
    @Override
    public List<T1> getContainingF2(String f2) {
        EntityManager em = emf.createEntityManager();
        Query q = em.createNativeQuery("SELECT f1,f2 FROM t1 WHERE f2 LIKE :f2");
        q.setParameter("f2", "%" + f2 + "%");
        @SuppressWarnings("unchecked")
        List<Object[]> tmp = q.getResultList();
        List<T1> res = tmp.stream().map(oa -> new T1((Integer)oa[0], (String)oa[1])).collect(Collectors.toList());
        em.close();
        return res;
    }

and CriteriaBuilder:

    @Override
    public List<T1> getAll() {
        EntityManager em = emf.createEntityManager();
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<T1> cq = cb.createQuery(T1.class);
        Root<T1> r = cq.from(T1.class);
        cq.select(r);
        TypedQuery<T1> q = em.createQuery(cq);
        List<T1> res = q.getResultList();
        em.close();
        return res;
    }
    @Override
    public List<T1> getRangeOfPK(int f1_start, int f1_end) {
        EntityManager em = emf.createEntityManager();
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<T1> cq = cb.createQuery(T1.class);
        Root<T1> r = cq.from(T1.class);
        cq.select(r).where(cb.between(r.get("f1"), f1_start, f1_end));
        TypedQuery<T1> q = em.createQuery(cq);
        List<T1> res = q.getResultList();
        em.close();
        return res;
    }
    @Override
    public List<T1> getContainingF2(String f2) {
        EntityManager em = emf.createEntityManager();
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<T1> cq = cb.createQuery(T1.class);
        Root<T1> r = cq.from(T1.class);
        cq.select(r).where(cb.like(r.get("f2"), "%" + f2 + "%"));
        TypedQuery<T1> q = em.createQuery(cq);
        List<T1> res = q.getResultList();
        em.close();
        return res;
    }

It is recommended to use JPQL or CriteriaBuilder not SQL.

Special JDBC features:

As JDBC was intended to result in database independent code and the reality is that different databases come withs ligtly different SQL dialects, then JDBC comes with extensive features to workaround differences in SQL dialect.

JDBC provides the ability to:

Demo:

package javadb;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.List;

public class Special {
    private static List<String> parseList(String s) {
        return Arrays.asList(s.split(","));
    }
    public static void testInfo(Connection con) throws SQLException {
        DatabaseMetaData dbmd = con.getMetaData();
        System.out.printf("Database = %s %d.%d\n", dbmd.getDatabaseProductName(), dbmd.getDatabaseMajorVersion(), dbmd.getDatabaseMinorVersion());
        System.out.printf("Driver = %s %d.%d\n", dbmd.getDriverName(), dbmd.getDriverMajorVersion(), dbmd.getDriverMinorVersion());
        System.out.printf("JDBC version = %d.%d\n", dbmd.getJDBCMajorVersion(), dbmd.getJDBCMinorVersion());
        System.out.printf("SQL92 support = %b %b %b\n", dbmd.supportsANSI92EntryLevelSQL(), dbmd.supportsANSI92IntermediateSQL(), dbmd.supportsANSI92FullSQL());
    }
    public static void testFeatures(Connection con) throws SQLException {
        DatabaseMetaData dbmd = con.getMetaData();
        // SQL
        System.out.printf("Outer joins : %b %b\n", dbmd.supportsLimitedOuterJoins(), dbmd.supportsFullOuterJoins());
        System.out.printf("Subquery EXISTS : %b\n", dbmd.supportsSubqueriesInExists());
        System.out.printf("Subquery IN : %b\n", dbmd.supportsSubqueriesInComparisons());
        System.out.printf("Subquery = : %b\n", dbmd.supportsSubqueriesInQuantifieds());
        System.out.printf("UNION : %b\n", dbmd.supportsUnion());
        System.out.printf("UNION ALL : %b\n", dbmd.supportsUnionAll());
        System.out.printf("SELECT FOR UPDATE : %b\n", dbmd.supportsSelectForUpdate());
        // transactions
        System.out.printf("Transactions : %b\n", dbmd.supportsTransactions());
        System.out.printf("TX default : %d\n", dbmd.getDefaultTransactionIsolation());
        System.out.printf("TX NONE : %b\n", dbmd.supportsTransactionIsolationLevel(Connection.TRANSACTION_NONE));
        System.out.printf("TX UREAD NCOMMITTED : %b\n", dbmd.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED));
        System.out.printf("TX READ COMMITTED : %b\n", dbmd.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED));
        System.out.printf("TX REPEATABLE READ : %b\n", dbmd.supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ));
        System.out.printf("TX SERIALIZABLE : %b\n", dbmd.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE));
        // functions
        System.out.printf("CONVERT : %b\n", dbmd.supportsConvert());
        System.out.printf("Numeric functions = %s\n", parseList(dbmd.getNumericFunctions()));
        System.out.printf("String functions = %s\n", parseList(dbmd.getStringFunctions()));
        System.out.printf("TimeDate functions = %s\n", parseList(dbmd.getTimeDateFunctions()));
        System.out.printf("System functions = %s\n", parseList(dbmd.getSystemFunctions()));
        // various
        System.out.printf("SP : %b\n", dbmd.supportsStoredProcedures());
        if(dbmd.getJDBCMajorVersion() >= 4) {
            System.out.printf("UDF : %b\n", dbmd.supportsStoredFunctionsUsingCallSyntax());
        }
        System.out.printf("Batch : %b\n", dbmd.supportsBatchUpdates());
        System.out.printf("Multiple result sets from command : %b\n", dbmd.supportsMultipleResultSets());
        System.out.printf("Identifiers case senstive : %b\n", dbmd.supportsMixedCaseIdentifiers());
        System.out.printf("Quoted identifiers case senstive : %b\n", dbmd.supportsMixedCaseQuotedIdentifiers());
    }
    private static void dumpTables(ResultSet rs) throws SQLException {
        while(rs.next()) {
            System.out.printf("%s %s\n",  rs.getString("TABLE_NAME"), rs.getString("TABLE_TYPE"));
        }
        rs.close();
    }
    private static String columnType(String typnam, int strsiz, int numsiz, int dec) {
        switch(typnam) {
            case "CHAR":
            case "VARCHAR":
                return String.format("%s(%d)", typnam, strsiz);
            case "NUMERIC":
            case "DECIMAL":
                return String.format("%s(%d,%s)", typnam, numsiz, dec);
            default:
                return typnam;
        }
    }
    private static void dumpColumns(ResultSet rs) throws SQLException {
        while(rs.next()) {
            System.out.printf("%s %s %s\n",  rs.getString("TABLE_NAME"),
                                             rs.getString("COLUMN_NAME"),
                                             columnType(rs.getString("TYPE_NAME"), rs.getInt("COLUMN_SIZE"), rs.getInt("COLUMN_SIZE"), rs.getInt("DECIMAL_DIGITS")));
        }
        rs.close();
    }
    public static void testExplore(Connection con) throws SQLException {
        DatabaseMetaData dbmd = con.getMetaData();
        dumpTables(dbmd.getTables(null, null, "%", new String[] { "TABLE", "VIEW" } ));
        dumpColumns(dbmd.getColumns(null, null, "%", "%"));
    }
    public static void testEscape(Connection con) throws SQLException {
        DatabaseMetaData dbmd = con.getMetaData();
        List<String> strfunc = parseList(dbmd.getStringFunctions());
        List<String> tdfunc = parseList(dbmd.getTimeDateFunctions());
        List<String> sysfunc = parseList(dbmd.getSystemFunctions());
        // date time formats
        Statement stmt = con.createStatement();
        ResultSet rs;
        rs = stmt.executeQuery("SELECT {d '2018-05-23'} AS d,{t '22:30:00'} AS t, {ts '2018-05-23 22:30:00'} AS ts FROM t1");
        while(rs.next()) {
            System.out.printf("'%s' '%s' '%s'\n", rs.getDate("d"), rs.getTime("t"), rs.getTimestamp("ts"));
        }
        rs.close();
        // numeric functions
        /* not relevant in my opinion */
        // string functions
        if(strfunc.containsAll(Arrays.asList("LENGTH", "LEFT", "RIGHT", "LCASE", "UCASE"))) {
            rs = stmt.executeQuery("SELECT f2,{fn length(f2)} AS f2len,{fn left(f2,1)} AS f2left,{fn right(f2,1)} As f2right,{fn lcase(f2)} AS f2lc,{fn ucase(f2)} AS f2uc FROM t1");
            while(rs.next()) {
                System.out.printf("%s %d %s %s %s %s\n", rs.getString("f2"), rs.getInt("f2len"), rs.getString("f2left"), rs.getString("f2right"), rs.getString("f2lc"), rs.getString("f2uc"));
            }
            rs.close();
        }
        if(strfunc.containsAll(Arrays.asList("LOCATE", "SUBSTRING", "CONCAT", "REPLACE"))) {
            rs = stmt.executeQuery("SELECT f2,{fn locate('B',f2)} AS f2loc,{fn substring(f2,2,2)} AS f2sub,{fn concat(f2,'X')} as f2x, {fn replace(f2,'B','X')} As f2repl FROM t1");
            while(rs.next()) {
                System.out.printf("%s %d %s %s %s\n", rs.getString("f2"), rs.getInt("f2loc"), rs.getString("f2sub"), rs.getString("f2x"), rs.getString("f2repl"));
            }
            rs.close();
        }
        // time data functions
        if(tdfunc.containsAll(Arrays.asList("CURDATE", "CURTIME", "NOW"))) {
            rs = stmt.executeQuery("SELECT {fn curdate()} AS d,{fn curtime()} AS t, {fn now()} AS ts FROM t1");
            while(rs.next()) {
                System.out.printf("'%s' '%s' '%s'\n", rs.getDate("d"), rs.getTime("t"), rs.getTimestamp("ts"));
            }
            rs.close();
        }
        if(tdfunc.containsAll(Arrays.asList("YEAR", "MONTH", "DAYOFMONTH", "HOUR", "MINUTE", "SECOND"))) {
            rs = stmt.executeQuery("SELECT {fn year(now())} AS y,{fn month(now())} AS mo, {fn dayofmonth(now())} AS d,{fn hour(now())} AS h,{fn minute(now())} AS mi, {fn second(now())} AS s FROM t1");
            while(rs.next()) {
                System.out.printf("%4d-%02d-%02d %02d:%02d:%02d\n", rs.getInt("y"), rs.getInt("mo"), rs.getInt("d"), rs.getInt("h"), rs.getInt("mi"), rs.getInt("s"));
            }
            rs.close();
        }
        // system functions
        if(sysfunc.containsAll(Arrays.asList("DATABASE", "USER", "IFNULL"))) {
            rs = stmt.executeQuery("SELECT {fn database()} AS db,{fn user()} AS usr,{fn ifnull('X','not X')} As isnfalse, FROM {fn isnull(NULL,'Y')} As isntrue t1");
            while(rs.next()) {
                System.out.printf("%s %s %s %s\n", rs.getString("db"), rs.getString("usr"), rs.getString("isnfalse"), rs.getString("isntrue"));
            }
            rs.close();
        }
        // LIMIT (be careful - your driver may have forgot to implement it!)
        if(dbmd.getJDBCMajorVersion() >= 4 && dbmd.getJDBCMinorVersion() >= 1) {
            rs = stmt.executeQuery("SELECT f1,f2 FROM t1 {limit 2}");
            while(rs.next()) {
                System.out.printf("%d %s\n", rs.getString("f1"), rs.getString("f2"));
            }
            rs.close();
        }
        //
        stmt.close();
    }
    public static void test(String constr, String usr, String pwd) throws SQLException
    {
        try(Connection con = DriverManager.getConnection(constr, usr, pwd)) {
            testInfo(con);
            testFeatures(con);
            //testExplore(con);
            testEscape(con);
        }
    }
    public static void main(String[] args) throws SQLException {
        test("jdbc:mysql://localhost/Test", "arne", "xxxxxx");
        test("jdbc:oracle:thin:@localhost:1521:xe", "arne", "xxxxxx");
        test("jdbc:db2://localhost:50000/Test", "arne", "xxxxxx");
    }
}

Java SE vs Java EE:

It is worth nothing that typically a JDBC driver does not provide database connection pooling.

That sort of make sense as it is typically not needed for simple Java SE applications.

But of course database connection pooling is available.

Java EE application servers and web containers provide database connection pools via configuration and exposing them as data sources.

See here to see how to configure it in Tomcat.

And a JPA persistence.xml for a Java EE context can be seen here.

Most ORM frameworks provide database connection pools via configuration that are also usable in Java SE context.

Both Hibernate configuartion shown above and JPA via Hibernate configuration shown above use connection pool.

If a regular Java SE application need a connection pool for plain JDBC usage, then pool JDBC drivers exists. One of the most common is Apache DBCP.

SQLJ (embedded SQL):

It is not widely known that Java supports embedded SQL. Embedded SQL is usually associated with languages like COBOL and C.

But back in 1997-2002 a bunch of big database and Java companies actually defined SQLJ standard for embedded SQL in Java.

It has since then totally disappeared from mainstream usage, where plain JDBC and various ORM frameworks has taken over.

And Oracle DB and IBM DB2 are probably the only databases that come with SQLJ today.

I actually think that SQLJ is pretty smart.

SQLJ works like other embedded SQL products: a source code file with embedded SQL statements is run through a translator that generates plain Java code (typical using JDBC), that is then processed normal.

The token to indicate embedded SQL is "#sql".

Let us see an example.

EmbedSQL.sqlj:

package javadb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

// define a connection context class for use
#sql context ConCtx;

public class EmbedSQL extends Demo implements DAL {
    private String constr;
    private String usr;
    private String pwd;
    public EmbedSQL(String constr, String usr, String pwd) {
        this.constr = constr;
        this.usr = usr;
        this.pwd = pwd;
    }
    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(constr, usr, pwd);
    }
    // create connection context based on JDBC context
    private ConCtx getContext() throws SQLException {
        return new ConCtx(getConnection());
    }
    // iterator class when returning variable number of rows
    #sql private static iterator T1Iterator(int, String);
    @Override
    public T1 getOneByPK(int f1) throws SQLException {
        T1 res;
        ConCtx ctx = getContext();
        T1Iterator it;
        #sql [ctx] it = { SELECT f1,f2 FROM t1 WHERE f1 = :f1};
        int xf1;
        String xf2;
        #sql { fetch :it INTO :xf1, :xf2 };
        if(!it.endFetch()) {
            res = new T1(xf1, xf2);
        } else {
            res = null;
        }
        ctx.close();
        return res;
    }
    @Override
    public List<T1> getAll() throws SQLException {
        List<T1> res = new ArrayList<T1>();
        ConCtx ctx = getContext();
        T1Iterator it;
        #sql [ctx] it = { SELECT f1,f2 FROM t1 };
        while(true) {
            int xf1;
            String xf2;
            #sql { fetch :it INTO :xf1, :xf2 };
            if(it.endFetch()) break;
            res.add(new T1(xf1, xf2));
        }
        ctx.close();
        return res;
    }
    @Override
    public List<T1> getRangeOfPK(int f1_start, int f1_end) throws SQLException {
        List<T1> res = new ArrayList<T1>();
        ConCtx ctx = getContext();
        T1Iterator it;
        #sql [ctx] it = { SELECT f1,f2 FROM t1 WHERE f1 BETWEEN :f1_start AND :f2_end };
        while(true) {
            int xf1;
            String xf2;
            #sql { fetch :it INTO :xf1, :xf2 };
            if(it.endFetch()) break;
            res.add(new T1(xf1, xf2));
        }
        ctx.close();
        return res;
    }
    @Override
    public List<T1> getContainingF2(String f2) throws SQLException {
        List<T1> res = new ArrayList<T1>();
        ConCtx ctx = getContext();
        String f2wc = "%" + f2 + "%";
        T1Iterator it;
        #sql [ctx] it = { SELECT f1,f2 FROM t1 WHERE f2 LIKE :f2wc };
        while(true) {
            int xf1;
            String xf2;
            #sql { fetch :it INTO :xf1, :xf2 };
            if(it.endFetch()) break;
            res.add(new T1(xf1, xf2));
        }
        ctx.close();
        return res;
    }
    @Override
    public void saveChanges(T1 o) throws SQLException {
        ConCtx ctx = getContext();
        int f1 = o.getF1();
        String f2 = o.getF2();
        #sql [ctx] { UPDATE t1 SET f2 = :f2 WHERE f1 = :f1 };
        ctx.close();
    }
    @Override
    public void saveNew(T1 o) throws SQLException {
        ConCtx ctx = getContext();
        int f1 = o.getF1();
        String f2 = o.getF2();
        #sql [ctx] { INSERT INTO t1 VALUES(:f1,:f2) };
        ctx.close();
    }
    @Override
    public void remove(int f1) throws SQLException {
        ConCtx ctx = getContext();
        #sql [ctx] { DELETE FROM t1 WHERE f1 = :f1 };
        ctx.close();
    }
    public static void main(String[] args) throws SQLException {
        test("Embedded SQL (SQLJ) - DB2", new EmbedSQL("jdbc:db2://localhost:50000/Test", "arne", "xxxxxx"));
    }
}

Procedure to build and run for IBM DB2:

sqlj -dir=. -compile=false EmbedSQL.sqlj
copy *.ser javadb\*.*
javac -source 1.5  -target 1.5 javadb\*.java
java -cp .;<all the DB2 JDBC and SQLJ jar files> javadb.EmbedSQL

Other

There are plenty of other database technologies available in Java and some of them has been or still are widely used.

Examples include:

Calling stored procedures:

The usage of stored procedures are not universally considered a good idea. But the reality is that they are sometimes used. Especially in Oracle DB and MS SQLServer environments.

The basic API for accessing stored procedures is the same as for using plain SQL statements with parameters, but stored procedures do come with a few special features that require special handling in the API.

Two such features are:

The following example illustrates how to handle that.

Stored procedures (for MS SQLServer):

CREATE PROCEDURE usp_multi @arg INTEGER
AS
BEGIN
    SELECT @arg+1 AS v
    SELECT 2*@arg AS v
END;
GO
CREATE PROCEDURE usp_return @inarg INTEGER, @outarg INTEGER OUT
AS
BEGIN
    SELECT @inarg+1 AS v
    SELECT @outarg = @inarg+2 
    RETURN @inarg+3
END;
GO

Yes - they are trivial, but they will illustrate the points just fine.

Code:

package spudf;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

// use -Djava.library.path=C:\DivJava\sqljdbc_4.0\enu\auth\x64
public class TestSP {
    private static void testMultipleResultSets() throws SQLException {
        try(Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost;database=Test;integratedSecurity=true;")) {
            try(CallableStatement cstmt = con.prepareCall("{ call usp_multi(?) }")) {
                cstmt.setInt(1, 123);
                cstmt.execute();
                try(ResultSet rs1 = cstmt.getResultSet()) {
                    while(rs1.next()) {
                        System.out.println(rs1.getInt("v"));
                    }
                }
                cstmt.getMoreResults(); // switch from first to second result set
                try(ResultSet rs2 = cstmt.getResultSet()) {
                    while(rs2.next()) {
                        System.out.println(rs2.getInt("v"));
                    }
                }
            }
        }
    }
    private static void testMultipleReturnTypes() throws SQLException {
        try(Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost;database=Test;integratedSecurity=true;")) {
            try(CallableStatement cstmt = con.prepareCall("{ ? = CALL usp_return(?,?) }")) {
                cstmt.registerOutParameter(1, Types.INTEGER); // return value
                cstmt.setInt(2, 123); // in argument
                cstmt.registerOutParameter(3, Types.INTEGER); // out argument
                cstmt.execute();
                try(ResultSet rs = cstmt.getResultSet()) {
                    while(rs.next()) {
                        System.out.println(rs.getInt("v"));
                    }
                }
                System.out.println("return value = " + cstmt.getInt(1));
                System.out.println("out parameter = " + cstmt.getInt(3));
            }
        }
    }
    public static void main(String[] args) throws SQLException {
        testMultipleResultSets();
        testMultipleReturnTypes();
    }
}

Java SP and UDF

Everybody knows that most databases allows for SP (Stored Procedures) and UDF (User Defined Dunctions) in SQL.

Relative few are aware that many databases allows for Java SP and UDF aka SP and UDF written in Java.

This can be very conveneient. SQL is a great language for doing relational operations. SQL is *NOT* a great language for doing math, text manpulation and advanced logic in. Java on the other hand is an excellent language for those tasks.

Replacing functionality unsuited for SQL and T-SQL with same functionality in C# can sometimes result in much cleaner, more readable and more maintainable code.

NOTE: Java SP and UDF can be used in any context:

Among the database supporting Java SP and UDF are:

IMPORTANT: Java SP and UDF are defined differently in the different database, so they are not portable.

IBM DB2

Java example UDF's:

package udf;

import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

public class Util {
    public static String dateTimeFormat(Timestamp ts, String fmt) {
        DateFormat df = new SimpleDateFormat(fmt);
        return df.format(ts);
    }
    public static String regexReplace(String s, String pat, String rpl) {
        return s.replaceAll(pat, rpl);
    }
}

Java example SP:

package sp;

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

public class Demo {
    public static void test(int arg, ResultSet[] retrs) {
    	try {
    		// example is a bit primitive but it is not trivial to create a usable ResultSet from scratch
    		Connection con = DriverManager.getConnection("jdbc:default:connection");
    		Statement stmt = con.createStatement();
    		ResultSet rs = stmt.executeQuery("SELECT " + arg + " AS f1, 'Some text' AS f2 FROM sysibm.sysdummy1");
    		con.close();
			retrs[0] = rs;
		} catch (SQLException e) {
			e.printStackTrace();
		}
    }
}

For real code some additional code to handle NULL input, exceptions etc. should definitely be added.

SQL to load:

-- should use SQLJ.INSTALL_JAR to install jar files
CREATE FUNCTION DateTimeFormat(dt TIMESTAMP, fmt VARCHAR(50)) RETURNS VARCHAR(50) LANGUAGE java PARAMETER STYLE java FENCED THREADSAFE DETERMINISTIC EXTERNAL NAME 'udf.Util.dateTimeFormat'
CREATE FUNCTION RegexReplace(s VARCHAR(255), pat VARCHAR(50), rpl VARCHAR(50)) RETURNS VARCHAR(255) LANGUAGE java PARAMETER STYLE java FENCED THREADSAFE DETERMINISTIC EXTERNAL NAME 'udf.Util.regexReplace'
CREATE PROCEDURE SPDemo(arg INTEGER) DYNAMIC RESULT SETS 1 LANGUAGE java PARAMETER STYLE java FENCED THREADSAFE EXTERNAL NAME 'sp.Demo.test'

Demo:

db2 => SELECT DateTimeFormat(CURRENT TIMESTAMP,'dd-MM-yyyy HH:mm') FROM sysibm.sysdummy1

1
--------------------------------------------------
14-08-2018 21:18

  1 record(s) selected.

db2 => SELECT DateTimeFormat(CURRENT TIMESTAMP,'dd-MMM-yyyy HH:mm') FROM sysibm.sysdummy1

1
--------------------------------------------------
14-Aug-2018 21:18

  1 record(s) selected.

db2 => SELECT DateTimeFormat(CURRENT TIMESTAMP,'yyyyMMddHHmm') FROM sysibm.sysdummy1

1
--------------------------------------------------
201808142118

  1 record(s) selected.

db2 => SELECT RegexReplace('<all><one a=''1'' b=''11''/><one a=''2'' b=''22''/></all>','<one a=''(\d+)'' b=''(\d+)''/>','<one><a>$1</a><b>$2</b></one>') FROM sysibm.sysdummy1

1

--------------------------------------------------------------------------------
<all><one><a>1</a><b>11</b></one><one><a>2</a><b>22</b></one></all>


  1 record(s) selected.

db2 => CALL SPdemo(123)

  Result set 1
  --------------

  F1          F2
  ----------- ---------
          123 Some text

  1 record(s) selected.

  Return Status = 0

SQL to unload:

DROP FUNCTION DateTimeFormat
DROP FUNCTION RegexReplace
DROP PROCEDURE SPDemo
-- should use SQLJ.REMOVE_JAR to uninstall jar files

Apache Derby

The DB2 Java code and SQL should work fine with Apache Derby.

Article history:

Version Date Description
1.0 May 28th 2018 Initial version
1.1 August 8th 2018 Add section on calling stored procedures
1.2 August 14th 2018 Add section on Java SP and UDF
1.3 August 21st 2018 Add section on SQLJ

Other articles:

See list of all articles here

Comments:

Please send comments to Arne Vajhøj