Java comes with several database access technologies. And additional options are available as third party libraries.
All the regular examples below will use the following database table:
CREATE TABLE T1 (
F1 INTEGER NOT NULL,
F2 VARCHAR(50),
PRIMARY KEY(F1)
);
The examples with join will use the following database tables:
CREATE TABLE myorder (
orderid INTEGER NOT NULL,
customer VARCHAR(50),
PRIMARY KEY (orderid)
);
CREATE TABLE orderline (
orderlineid INTEGER NOT NULL,
orderid INTEGER NOT NULL,
itemname VARCHAR(50),
itemprice DECIMAL(9,2) NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY(orderlineid)
);
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.
JDBC is the foundation for all database access in Java. All the higher level interfaces (including ORM) are build on top of JDBC.
JDBC is 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 JDBCDALModern("jdbc:oracle:thin:@localhost:1521:xe", "arne", "xxxxxx"));
test("Plain JDBC - DB2", new JDBCDALModern("jdbc:db2://localhost:50000/Test", "arne", "xxxxxx"));
}
}
There are 4 types of JDBC drivers:
A type 1 JDBC driver use ODBC to access database:
A type 2 JDBC driver use a native client library to access database:
A type 3 JDBC driver use a proxy server to access database:
A type 4 JDBC driver access database directly over network or using file IO:
If available then I recommend using a type 4 driver. Type 1 is deprecated/removed. Type 2 depends on native code. Type 3 is very rare.
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:
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.
Spring framework contains a nice wrapper around JDBC called Spring JDBC.
package javadb;
import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
public class SpringJDBCDAL extends Demo implements DAL {
private String constr;
private String usr;
private String pwd;
private RowMapper<T1> t1mapper = (rs,n) -> new T1(rs.getInt("f1"), rs.getString("f2"));
public SpringJDBCDAL(String constr, String usr, String pwd) {
this.constr = constr;
this.usr = usr;
this.pwd = pwd;
}
private DataSource getDataSource() throws SQLException {
DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setUrl(constr);
ds.setUsername(usr);
ds.setPassword(pwd);
return ds;
}
@Override
public T1 getOneByPK(int f1) throws SQLException {
JdbcTemplate q = new JdbcTemplate(getDataSource());
T1 res = q.queryForObject("SELECT f1,f2 FROM t1 WHERE f1 = ?", t1mapper, f1);
return res;
}
@Override
public List<T1> getAll() throws SQLException {
JdbcTemplate q = new JdbcTemplate(getDataSource());
List<T1> res = q.query("SELECT f1,f2 FROM t1", t1mapper);
return res;
}
@Override
public List<T1> getRangeOfPK(int f1_start, int f1_end) throws SQLException {
JdbcTemplate q = new JdbcTemplate(getDataSource());
List<T1> res = q.query("SELECT f1,f2 FROM t1 WHERE f1 BETWEEN ? AND ?", t1mapper, f1_start, f1_end);
return res;
}
@Override
public List<T1> getContainingF2(String f2) throws SQLException {
JdbcTemplate q = new JdbcTemplate(getDataSource());
List<T1> res = q.query("SELECT f1,f2 FROM t1 WHERE f2 LIKE ?", t1mapper, "%" + f2 + "%");
return res;
}
@Override
public void saveChanges(T1 o) throws SQLException {
JdbcTemplate q = new JdbcTemplate(getDataSource());
q.update("UPDATE t1 SET f2 = ? WHERE f1 = ?", o.getF2(), o.getF1());
}
@Override
public void saveNew(T1 o) throws SQLException {
JdbcTemplate q = new JdbcTemplate(getDataSource());
q.update("INSERT INTO t1 VALUES(?,?)", o.getF1(), o.getF2());
}
@Override
public void remove(int f1) throws SQLException {
JdbcTemplate q = new JdbcTemplate(getDataSource());
q.update("DELETE FROM t1 WHERE f1 = ?", f1);
}
public static void main(String[] args) throws SQLException {
test("Spring JDBC - MySQL", new SpringJDBCDAL("jdbc:mysql://localhost/Test", "arne", "xxxxxx"));
test("Spring JDBC - Oracle", new SpringJDBCDAL("jdbc:oracle:thin:@localhost:1521:xe", "arne", "xxxxxx"));
test("Spring JDBC - DB2", new SpringJDBCDAL("jdbc:db2://localhost:50000/Test", "arne", "xxxxxx"));
}
}
It is obvious that Spring JDBC saves a lot of code compared to plain JDBC.
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.
An important feature of Hibernate is its level 2 cache.
When using SQL then joing two tables is straight forward - just do the join in the SQL.
When using an ORM then joining two tables are still posisble, but understanding what is happening under the hood can be a bit tricky.
First there is the syntax problem on how to tell to map a child class with a foregn key referencing the parent child to a parent class containing a collection of child classes.
Second there is question on how to do it efficiently.
Two key concepts are eager loading versus lazy loading and fetch select versus fetch join.
Eager loading versus lazy loading:
Fetch select versus fetch join:
So we have 3 cases:
| loading | fetch | number of SELECT | benefit | drawback |
|---|---|---|---|---|
| lazy | select | 1 for parent class 0..numberparents for child class |
only load data that are needed | may cause extra queries |
| eager | select | 1 for parent class numberparents for child class |
(none) | lots of queries may load data that will never be used |
| eager | join | 1 | minimize queries | may load data that will never be used |
It seems obvious that:
Unfortunatetly there are cases where an ORM framework end up doing eager loading with fetch select.
With Hibernate the table relation is defined in the mapping:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="javadb.Order" table="myorder">
<id name="orderId" column="orderid">
<generator class="assigned"/>
</id>
<property name="customer">
<column name="customer"/>
</property>
<bag name="orderLines" lazy="false" fetch="join"> <!-- use lazy="true" and fetch="select" for lazy loading -->
<key>
<column name="orderid"/> <!-- join field on many side -->
</key>
<one-to-many class="javadb.OrderLine"/>
</bag>
</class>
<class name="javadb.OrderLine" table="orderline">
<id name="orderLineId" column="orderlineid">
<generator class="assigned"/>
</id>
<property name="orderId">
<column name="orderid"/>
</property>
<property name="itemName">
<column name="itemname"/>
</property>
<property name="itemPrice">
<column name="itemprice"/>
</property>
<property name="quantity">
<column name="quantity"/>
</property>
</class>
</hibernate-mapping>
Data classes:
package javadb;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.Transient;
public class Order {
private int orderId;
private String customer;
private List<OrderLine> orderLines;
public Order() {
this(0, "", new ArrayList<OrderLine>());
}
public Order(int orderId, String customer, List<OrderLine> orderLines) {
this.orderId = orderId;
this.customer = customer;
this.orderLines = orderLines;
}
public int getOrderId() {
return orderId;
}
public void setOrderId(int orderId) {
this.orderId = orderId;
}
public String getCustomer() {
return customer;
}
public void setCustomer(String customer) {
this.customer = customer;
}
public List<OrderLine> getOrderLines() {
return orderLines;
}
public void setOrderLines(List<OrderLine> orderLines) {
this.orderLines = orderLines;
}
public BigDecimal getCost() {
BigDecimal res = BigDecimal.ZERO;
for(OrderLine ol : orderLines) {
res = res.add(ol.getItemCost());
}
return res;
}
}
package javadb;
import java.math.BigDecimal;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Transient;
public class OrderLine {
private int orderLineId;
private int orderId;
private String itemName;
private BigDecimal itemPrice;
private int quantity;
public OrderLine() {
this(0, 0, "", BigDecimal.ZERO, 0);
}
public OrderLine(int orderLineId, int orderId, String itemName, BigDecimal itemPrice, int quantity) {
this.orderLineId = orderLineId;
this.orderId = orderId;
this.itemName = itemName;
this.itemPrice = itemPrice;
this.quantity = quantity;
}
public int getOrderLineId() {
return orderLineId;
}
public void setOrderLineId(int orderLineId) {
this.orderLineId = orderLineId;
}
public int getOrderId() {
return orderId;
}
public void setOrderId(int orderId) {
this.orderId = orderId;
}
public String getItemName() {
return itemName;
}
public void setItemName(String itemName) {
this.itemName = itemName;
}
public BigDecimal getItemPrice() {
return itemPrice;
}
public void setItemPrice(BigDecimal itemPrice) {
this.itemPrice = itemPrice;
}
public int getQuantity() {
return quantity;
}
public void setQuantity(int quantity) {
this.quantity = quantity;
}
public BigDecimal getItemCost() {
return itemPrice.multiply(new BigDecimal(quantity));
}
}
Demo:
package javadb;
import java.io.File;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
public class TestJoinHib {
private static void dump(Order o) {
System.out.printf("%s total %.2f:\n", o.getCustomer(), o.getCost());
for(OrderLine ol : o.getOrderLines()) {
System.out.printf(" %-20s %9.2f %3d %12.2f\n", ol.getItemName(), ol.getItemPrice(), ol.getQuantity(), ol.getItemCost());
}
}
private static void find(SessionFactory sf, int orderid) { // join
System.out.printf("find orderid=%d:\n", orderid);
Session s = sf.openSession();
Order o = s.load(Order.class, orderid);
dump(o);
s.close();
}
private static void listHQLImpl(SessionFactory sf) {
System.out.println("find all (HQL - implicit):");
Session s = sf.openSession();
@SuppressWarnings("unchecked")
List<Order> lsto = s.createQuery("SELECT o FROM Order o").list(); // no join
for(Order o : lsto) {
dump(o);
}
s.close();
}
private static void listHQLExplJoin(SessionFactory sf) {
System.out.println("find all (HQL - explicit join):");
Session s = sf.openSession();
@SuppressWarnings("unchecked")
List<Order> lsto = s.createQuery("SELECT DISTINCT o FROM Order o JOIN o.orderLines ol").list(); // no join
for(Order o : lsto) {
dump(o);
}
s.close();
}
private static void listHQLExplJoinFetch(SessionFactory sf) {
System.out.println("find all (HQL - explicit join fetch):");
Session s = sf.openSession();
@SuppressWarnings("unchecked")
List<Order> lsto = s.createQuery("SELECT DISTINCT o FROM Order o JOIN FETCH o.orderLines ol").list(); // join
for(Order o : lsto) {
dump(o);
}
s.close();
}
private static void listCriteria(SessionFactory sf) { // join
System.out.println("find all (Criteria):");
Session s = sf.openSession();
Criteria crit = s.createCriteria(Order.class).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
@SuppressWarnings("unchecked")
List<Order> lsto = crit.list();
for(Order o : lsto) {
dump(o);
}
s.close();
}
public static void main(String[] args) {
SessionFactory sf = new Configuration().configure(new File("/work/hibconf2.xml")).buildSessionFactory();
find(sf, 1);
find(sf, 2);
listHQLImpl(sf);
listHQLExplJoin(sf);
listHQLExplJoinFetch(sf);
listCriteria(sf);
sf.close();
}
}
We see that:
result in JOIN, while:
result in multiple SELECT.
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" value="true"/>-->
<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 confnam) {
emf = Persistence.createEntityManagerFactory(confnam);
}
@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("javadb"));
}
}
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.
Hibernate when used as JPA provide still supports level 2 cache. See details on how to use in Database and cache.
With JPA the table relation is defined via annotations on the data classes:
package javadb;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.Transient;
@Entity
@Table(name="myorder")
public class Order {
private int orderId;
private String customer;
private List<OrderLine> orderLines;
public Order() {
this(0, "", new ArrayList<OrderLine>());
}
public Order(int orderId, String customer, List<OrderLine> orderLines) {
this.orderId = orderId;
this.customer = customer;
this.orderLines = orderLines;
}
@Id
@Column(name="orderid")
public int getOrderId() {
return orderId;
}
public void setOrderId(int orderId) {
this.orderId = orderId;
}
@Column(name="customer")
public String getCustomer() {
return customer;
}
public void setCustomer(String customer) {
this.customer = customer;
}
@OneToMany(mappedBy = "orderId", // property name in child class - not field name in child table
fetch = FetchType.EAGER) // use FetchType.LAZY for lazy loading
public List<OrderLine> getOrderLines() {
return orderLines;
}
public void setOrderLines(List<OrderLine> orderLines) {
this.orderLines = orderLines;
}
@Transient
public BigDecimal getCost() {
BigDecimal res = BigDecimal.ZERO;
for(OrderLine ol : orderLines) {
res = res.add(ol.getItemCost());
}
return res;
}
}
package javadb;
import java.math.BigDecimal;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Transient;
@Entity
@Table(name="orderline")
public class OrderLine {
private int orderLineId;
private int orderId;
private String itemName;
private BigDecimal itemPrice;
private int quantity;
public OrderLine() {
this(0, 0, "", BigDecimal.ZERO, 0);
}
public OrderLine(int orderLineId, int orderId, String itemName, BigDecimal itemPrice, int quantity) {
this.orderLineId = orderLineId;
this.orderId = orderId;
this.itemName = itemName;
this.itemPrice = itemPrice;
this.quantity = quantity;
}
@Id
@Column(name="orderlineid")
public int getOrderLineId() {
return orderLineId;
}
public void setOrderLineId(int orderLineId) {
this.orderLineId = orderLineId;
}
@Column(name="orderid")
public int getOrderId() {
return orderId;
}
public void setOrderId(int orderId) {
this.orderId = orderId;
}
@Column(name="itemname")
public String getItemName() {
return itemName;
}
public void setItemName(String itemName) {
this.itemName = itemName;
}
@Column(name="itemprice")
public BigDecimal getItemPrice() {
return itemPrice;
}
public void setItemPrice(BigDecimal itemPrice) {
this.itemPrice = itemPrice;
}
@Column(name="quantity")
public int getQuantity() {
return quantity;
}
public void setQuantity(int quantity) {
this.quantity = quantity;
}
@Transient
public BigDecimal getItemCost() {
return itemPrice.multiply(new BigDecimal(quantity));
}
}
Demo:
package javadb;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.criteria.CriteriaQuery;
public class TestJoinJPA {
private static void dump(Order o) {
System.out.printf("%s total %.2f:\n", o.getCustomer(), o.getCost());
for(OrderLine ol : o.getOrderLines()) {
System.out.printf(" %-20s %9.2f %3d %12.2f\n", ol.getItemName(), ol.getItemPrice(), ol.getQuantity(), ol.getItemCost());
}
}
private static void find(EntityManagerFactory emf, int orderid) { // join
System.out.printf("find orderid=%d:\n", orderid);
EntityManager em = emf.createEntityManager();
Order o = em.find(Order.class, orderid);
dump(o);
em.close();
}
private static void listJPQLImpl(EntityManagerFactory emf) {
System.out.println("find all (JPQL - implicit):");
EntityManager em = emf.createEntityManager();
@SuppressWarnings("unchecked")
List<Order> lsto = em.createQuery("SELECT o FROM Order AS o").getResultList(); // no join
for(Order o : lsto) {
dump(o);
}
em.close();
}
private static void listJPQLExplJoin(EntityManagerFactory emf) {
System.out.println("find all (JPQL - explicit join):");
EntityManager em = emf.createEntityManager();
@SuppressWarnings("unchecked")
List<Order> lsto = em.createQuery("SELECT DISTINCT o FROM Order AS o JOIN o.orderLines ol").getResultList(); // no join
for(Order o : lsto) {
dump(o);
}
em.close();
}
private static void listJPQLExplJoinFetch(EntityManagerFactory emf) {
System.out.println("find all (JPQL - explicit join fetch):");
EntityManager em = emf.createEntityManager();
@SuppressWarnings("unchecked")
List<Order> lsto = em.createQuery("SELECT DISTINCT o FROM Order AS o JOIN FETCH o.orderLines ol").getResultList(); // join
for(Order o : lsto) {
dump(o);
}
em.close();
}
private static void listCriteria(EntityManagerFactory emf) { // no join
System.out.println("find all (Criteria):");
EntityManager em = emf.createEntityManager();
CriteriaQuery<Order> cq = em.getCriteriaBuilder().createQuery(Order.class);
cq.select(cq.from(Order.class));
List<Order> lsto = em.createQuery(cq).getResultList();
for(Order o : lsto) {
dump(o);
}
em.close();
}
public static void main(String[] args) {
EntityManagerFactory emf = Persistence.createEntityManagerFactory("javadb");
find(emf, 1);
find(emf, 2);
listJPQLImpl(emf);
listJPQLExplJoin(emf);
listJPQLExplJoinFetch(emf);
listCriteria(emf);
emf.close();
}
}
The JPA standard defines lazy vs eager loading, but fetch select vs fetch join is somewhat implementation specific. The results below are for Hibernate as implementation.
We see that:
result in JOIN, while:
result in multiple SELECT.
MyBatis is an alternative and sligtly different type of ORM framework.
The history goes like:
iBatis existed for multiple platforms: iBatis for Java, iBatis.NET for .NET, RBatis for Ruby and jBati for JavaScript. Only the Java version got any traction.
MyBatis is Java only.
MyBatis has a very pragmatic approach to ORM. It assumes that the database exist and has a given structure and that the Java code has to work with that.
MyBatis can be used in two differnt ways:
Example with MyBatis and SQL in map file.
MyBatisDAL.java:
package javadb;
import java.io.IOException;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MyBatisDAL extends Demo implements DAL {
SqlSession session;
public MyBatisDAL(SqlSession session) {
this.session = session;
}
@Override
public T1 getOneByPK(int f1) throws SQLException {
return (T1)session.selectOne("demo.getOneByPK", f1);
}
@Override
public List<T1> getAll() throws SQLException {
return session.selectList("demo.getAll");
}
@Override
public List<T1> getRangeOfPK(int f1_start, int f1_end) throws SQLException {
Map<String,Integer> m = new HashMap<String,Integer>();
m.put("f1_start", f1_start);
m.put("f1_end", f1_end);
return session.selectList("demo.getRangeOfPK", m);
}
@Override
public List<T1> getContainingF2(String f2) throws SQLException {
return session.selectList("demo.getContainingF2", f2);
}
@Override
public void saveChanges(T1 o) throws SQLException {
session.update("demo.saveChanges", o);
}
@Override
public void saveNew(T1 o) throws SQLException {
session.insert("demo.saveNew", o);
}
@Override
public void remove(int f1) throws SQLException {
session.delete("demo.remove", f1);
}
public static void main(String[] args) throws SQLException, IOException {
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("SqlMapConfig.xml"), "demo");
SqlSession session = ssf.openSession(true);
test("MyBatis", new MyBatisDAL(session));
session.close();
}
}
SqlMapConfig.xml:
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default = "demo">
<environment id = "demo">
<transactionManager type = "JDBC"/>
<dataSource type = "POOLED">
<property name = "driver" value = "com.mysql.jdbc.Driver"/>
<property name = "url" value = "jdbc:mysql://localhost/Test"/>
<property name = "username" value = "root"/>
<property name = "password" value = ""/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="MyBatisDAL.xml"/>
</mappers>
</configuration>
MyBatisDAL.xml:
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="demo">
<select id="getOneByPK" resultType="javadb.T1">
SELECT f1,f2 FROM t1 WHERE f1 = #{f1}
</select>
<select id="getAll" resultType="javadb.T1">
SELECT f1,f2 FROM t1
</select>
<select id="getRangeOfPK" resultType="javadb.T1" parameterType="map">
SELECT f1,f2 FROM t1 WHERE f1 BETWEEN #{f1_start} AND #{f1_end}
</select>
<select id="getContainingF2" resultType="javadb.T1">
SELECT f1,f2 FROM t1 WHERE f2 LIKE {fn CONCAT('%',#{f2},'%')}
</select>
<update id="saveChanges">
UPDATE t1 SET f2 = #{f2} WHERE f1 = #{f1}
</update>
<insert id="saveNew">
INSERT INTO t1 VALUES(#{f1},#{f2})
</insert>
<delete id="remove">
DELETE FROM t1 WHERE f1 = #{f1}
</delete>
</mapper>
Example with MyBatis and SQL in annotations:
MyBatisDAL2.java:
package javadb;
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MyBatisDAL2 extends Demo {
public static interface RealMyBatisDAL extends DAL {
@Select("SELECT f1,f2 FROM t1 WHERE f1 = #{f1}")
public T1 getOneByPK(int f1) throws SQLException;
@Select("SELECT f1,f2 FROM t1")
public List<T1> getAll() throws SQLException;
@Select("SELECT f1,f2 FROM t1 WHERE f1 BETWEEN #{f1_start} AND #{f1_end}")
public List<T1> getRangeOfPK(@Param("f1_start") int f1_start, @Param("f1_end")int f1_end) throws SQLException;
@Select("SELECT f1,f2 FROM t1 WHERE f2 LIKE {fn CONCAT('%',#{f2},'%')}")
public List<T1> getContainingF2(String f2) throws SQLException;
@Update("UPDATE t1 SET f2 = #{f2} WHERE f1 = #{f1}")
public void saveChanges(T1 o) throws SQLException;
@Insert("INSERT INTO t1 VALUES(#{f1},#{f2})")
public void saveNew(T1 o) throws SQLException;
@Delete("DELETE FROM t1 WHERE f1 = #{f1}")
public void remove(int f1) throws SQLException;
}
public static void main(String[] args) throws SQLException, IOException {
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("SqlMapConfig2.xml"), "demo");
SqlSession session = ssf.openSession(true);
session.getConfiguration().addMapper(RealMyBatisDAL.class);
DAL real = session.getMapper(RealMyBatisDAL.class);
test("MyBatis", real);
session.close();
}
}
SqlMapConfig2.xml:
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default = "demo">
<environment id = "demo">
<transactionManager type = "JDBC"/>
<dataSource type = "POOLED">
<property name = "driver" value = "com.mysql.jdbc.Driver"/>
<property name = "url" value = "jdbc:mysql://localhost/Test"/>
<property name = "username" value = "root"/>
<property name = "password" value = ""/>
</dataSource>
</environment>
</environments>
</configuration>
JDO (Java Data Objects) is a Java ORM framework.
Version 1.0 was released in September 2003. Version 2.0 was released in May 2006. Version 3.0 was released in April 2010.
For most practical purposed JDO is obsolete today. The Java world has chosen JPA over JDO.
But JDO is not a bad technology at all. In fact it is very similar to Hibernate and JPA.
There are multiple implementations of JDO:
The example will be using JDO 2.x - not latest and greatest 3.x. Reason: I expect most JDO code to be from the time when 2.x was latest and greatest.
JDO 2.x use mapping XML file (JDO 3.x can use annotations).
A unique characteristica for JDO is that the data classes need to be run through an enhancer - so code generation is happening at compile time unlike Hibernate and JPA that generates code dynamically at runtime.
Example:
package javadb;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
import javax.jdo.JDOHelper;
import javax.jdo.PersistenceManager;
import javax.jdo.PersistenceManagerFactory;
import javax.jdo.Query;
import javax.jdo.Transaction;
public class JDODALJDOQL extends Demo implements DAL {
private PersistenceManagerFactory pmf;
public JDODALJDOQL(String cfgfnm) {
InputStream is = this.getClass().getResourceAsStream(cfgfnm);
Properties p = new Properties();
try {
p.load(is);
is.close();
} catch (IOException e) {
e.printStackTrace();
}
pmf = JDOHelper.getPersistenceManagerFactory(p);
}
@Override
public T1 getOneByPK(int f1) {
PersistenceManager pm = pmf.getPersistenceManager();
T1 res = (T1)pm.getObjectById(T1.class, f1);
pm.close();
return res;
}
@Override
public List<T1> getAll() {
List<T1> res = new ArrayList<T1>();
PersistenceManager pm = pmf.getPersistenceManager();
Iterator<T1> it = pm.getExtent(T1.class, false).iterator();
while(it.hasNext()) {
T1 o = it.next();
res.add(o);
}
pm.close();
return res;
}
@Override
public List<T1> getRangeOfPK(int f1_start, int f1_end) {
List<T1> res = new ArrayList<T1>();
PersistenceManager pm = pmf.getPersistenceManager();
Query q = pm.newQuery(Query.JDOQL, "SELECT FROM javadb.T1 WHERE p_f1_start <= f1 && f1 <= p_f1_end");
q.declareParameters("int p_f1_start, int p_f1_end");
@SuppressWarnings("unchecked")
List<T1> temp = (List<T1>)q.executeWithArray(f1_start, f1_end);
res.addAll(temp);
q.close(temp);
pm.close();
return res;
}
@Override
public List<T1> getContainingF2(String f2) {
List<T1> res = new ArrayList<T1>();
PersistenceManager pm = pmf.getPersistenceManager();
Query q = pm.newQuery(Query.JDOQL, "SELECT FROM javadb.T1 WHERE f2.matches(p_f2)");
q.declareParameters("java.lang.String p_f2");
@SuppressWarnings("unchecked")
List<T1> temp = (List<T1>)q.executeWithArray(".*" + f2 + ".*");
res.addAll(temp);
q.close(temp);
pm.close();
return res;
}
@Override
public void saveChanges(T1 o) {
PersistenceManager pm = pmf.getPersistenceManager();
Transaction tx = pm.currentTransaction();
T1 o2 = (T1)pm.getObjectById(T1.class, o.getF1());
tx.begin();
o2.setF2(o.getF2());
// note: no persistence call - that is happening implicit
tx.commit();
pm.close();
}
@Override
public void saveNew(T1 o) {
PersistenceManager pm = pmf.getPersistenceManager();
Transaction tx = pm.currentTransaction();
tx.begin();
pm.makePersistent(o);
tx.commit();
pm.close();
}
@Override
public void remove(int f1) {
PersistenceManager pm = pmf.getPersistenceManager();
Transaction tx = pm.currentTransaction();
T1 o = (T1)pm.getObjectById(T1.class, f1);
tx.begin();
pm.deletePersistent(o);
tx.commit();
pm.close();
}
public static void main(String[] args) throws SQLException {
test("JDO", new JDODALJDOQL("/" + args[0] + ".properties"));
}
}
package.jdo mapping file:
<?xml version="1.0"?>
<!DOCTYPE jdo PUBLIC "-//Sun Microsystems, Inc.//DTD Java Data Objects Metadata 2.0//EN" "http://java.sun.com/dtd/jdo_2_0.dtd">
<jdo>
<package name="javadb">
<class name="T1" identity-type="application" table="t1">
<field name="f1" primary-key="true">
<column name="f1" jdbc-type="INTEGER"/>
</field>
<field name="f2">
<column name="f2" length="50" jdbc-type="VARCHAR"/>
</field>
</class>
</package>
</jdo>
The examples use JDOQL (JDO Query Language) for queries:
@Override
public List<T1> getRangeOfPK(int f1_start, int f1_end) {
List<T1> res = new ArrayList<T1>();
PersistenceManager pm = pmf.getPersistenceManager();
Query q = pm.newQuery(Query.JDOQL, "SELECT FROM javadb.T1 WHERE p_f1_start <= f1 && f1 <= p_f1_end");
q.declareParameters("int p_f1_start, int p_f1_end");
@SuppressWarnings("unchecked")
List<T1> temp = (List<T1>)q.executeWithArray(f1_start, f1_end);
res.addAll(temp);
q.close(temp);
pm.close();
return res;
}
@Override
public List<T1> getContainingF2(String f2) {
List<T1> res = new ArrayList<T1>();
PersistenceManager pm = pmf.getPersistenceManager();
Query q = pm.newQuery(Query.JDOQL, "SELECT FROM javadb.T1 WHERE f2.matches(p_f2)");
q.declareParameters("java.lang.String p_f2");
@SuppressWarnings("unchecked")
List<T1> temp = (List<T1>)q.executeWithArray(".*" + f2 + ".*");
res.addAll(temp);
q.close(temp);
pm.close();
return res;
}
It is also possible to use SQL:
@Override
public List<T1> getRangeOfPK(int f1_start, int f1_end) {
List<T1> res = new ArrayList<T1>();
PersistenceManager pm = pmf.getPersistenceManager();
Query q = pm.newQuery(Query.SQL, "SELECT * FROM t1 WHERE f1 BETWEEN ? AND ?");
q.setResultClass(T1.class);
@SuppressWarnings("unchecked")
List<T1> temp = (List<T1>)q.executeWithArray(f1_start, f1_end);
res.addAll(temp);
q.close(temp);;
pm.close();
return res;
}
@Override
public List<T1> getContainingF2(String f2) {
List<T1> res = new ArrayList<T1>();
PersistenceManager pm = pmf.getPersistenceManager();
Query q = pm.newQuery(Query.SQL, "SELECT * FROM t1 WHERE f2 LIKE ?");
q.setResultClass(T1.class);
@SuppressWarnings("unchecked")
List<T1> temp = (List<T1>)q.executeWithArray("%" + f2 + "%");
res.addAll(temp);
q.close(temp);
pm.close();
return res;
}
jpox.properties:
javax.jdo.PersistenceManagerFactoryClass = org.jpox.PersistenceManagerFactoryImpl
javax.jdo.option.NontransactionalRead = true
javax.jdo.option.ConnectionDriverName = com.mysql.jdbc.Driver
javax.jdo.option.ConnectionURL = jdbc:mysql://localhost/Test
javax.jdo.option.ConnectionUserName = root
javax.jdo.option.ConnectionPassword =
org.jpox.autoCreateSchema = false
org.jpox.validateTables = false
org.jpox.validateConstraints = false
Compiling, enhancing and running with JPOX:
javac -cp ... <source>
java -cp ... org.jpox.enhancer.JPOXEnhancer package.jdo
java -cp ... <application main>
nucleus.properties:
javax.jdo.PersistenceManagerFactoryClass = org.datanucleus.api.jdo.JDOPersistenceManagerFactory
javax.jdo.option.NontransactionalRead = true
javax.jdo.option.ConnectionDriverName = com.mysql.cj.jdbc.Driver
javax.jdo.option.ConnectionURL = jdbc:mysql://localhost/Test
javax.jdo.option.ConnectionUserName = root
javax.jdo.option.ConnectionPassword =
datanucleus.schema.autoCreateAll = false
Compiling, enhancing and running with DataNucleus:
javac -cp ... <source>
java -cp ... org.datanucleus.enhancer.DataNucleusEnhancer package.jdo
java -cp ... <application main>
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
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({fn now()})} AS y,{fn month({fn now()})} AS mo, {fn dayofmonth({fn now()})} AS d,{fn hour({fn now()})} AS h,{fn minute({fn now()})} AS mi, {fn second({fn 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, {fn isnull(NULL,'Y')} As isntrue FROM 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");
}
}
For more practical examples of how to use these features see examples here (the examples are all for databases running on VMS, but the logic is not VMS specific in any way).
Apache DBCP provide database connection pool for applications that do not have such through other means (Java EE server or ORM provider).
Usage is pretty simple.
Load driver:
Class.forName("org.apache.commons.dbcp2.PoolingDriver");
Create connection pool:
private static void startPool(String pool, int min, int max, String url, String un, String pw) throws SQLException {
PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
PoolableConnectionFactory pcf = new PoolableConnectionFactory(new DriverManagerConnectionFactory(url, un, pw), null);
GenericObjectPool<PoolableConnection> cp = new GenericObjectPool<>(pcf);
cp.setMinIdle(min);
cp.setMaxTotal(max);
pcf.setPool(cp);
driver.registerPool(pool, cp);
}
Use connection pool:
try(Connection con = DriverManager.getConnection("jdbc:apache:commons:dbcp:" + pool)) {
// do something
}
Destroy connection pool:
private static void stopPool(String pool) throws SQLException {
PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
driver.closePool(pool);
}
There are some differences between the typical Java SE scenario and the typical Java EE scenario.
In a simple Java SE application then a database connection pool is usually not needed, so JDBC drivers do not provide a database connection pool.
If a Java SE application needs a database connection pool for plain JDBC usage, then pool JDBC drivers exists. One of the most common is Apache DBCP. See section above.
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.
Java EE applications usually do need a database connection pool, so Java EE application servers and web containers provide database connection pools via configuration and exposing them as data sources.
See here for how to configure it in Tomcat.
And a JPA persistence.xml for a Java EE context can be seen here.
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();
}
}
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 Java 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.
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
The DB2 Java code and SQL should work fine with Apache Derby.
All of the above are for relational (SQL) databases. For NoSQL databases see:
| 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 |
| 1.4 | January 12th 2020 | Add section on Apache DBCP |
| 1.5 | February 21st 2020 | Cleanup and add section on MyBatis |
| 1.6 | October 13th 2020 | Add section on Spring JDBC |
| 1.7 | January 24th 2021 | Add sections on Hibernate and JPA join |
| 1.8 | June 12th 2022 | Add section on JDO |
See list of all articles here
Please send comments to Arne Vajhøj