Using JdbcUtil

The JdbcUtil class provides simplified methods for interacting with a database through JDBC. This class was created to provide a way to more easily handle complex queries in a format that is more easily maintainable, more easily apply parameters, and return fully instantiated and populated objects so that the user does not have to manually manage the resultset. It is important to note that this is not a persistence model; there is no metamodel stored in memory. However, for small datasets (in the tens to hundreds of recods, regarless of the complexity of the queries), JdbcUtil performs significantly faster than JPA/Hibernate (the larger the dataset -- i.e. thousands of records -- the more the two come into parity in performance).

The JdbcUtil class should not be instantiated directly, but should be extended. There are several annotations which allow the developer to instruct the class where to find resources and how to connect to the database.

All methods center around a SQL statement. The statement can be prepred one of two ways: as a String, or a JdbcStatement object. There is no significant advantage of one or the other performancewise, but only the latter can be used for sending LOBs to the database (i.e. in an INSERT or UPDATE).

:::java

package com.example.db;

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

import com.example.model.UserBean;

import com.roth.base.util.Data;
import com.roth.jdbc.annotation.ConnectionDataSource;
import com.roth.jdbc.annotation.SQLFileContext;
import com.roth.jdbc.util.JdbcUtil;

@ConnectionDataSource(jndiName = "example")  // (1)
@SQLFileContext(path = "/com/example/sql")  // (2)
public class ExampleUtil extends JdbcUtil {  // (3)
    private static final long serialVersionUID = -1L;

    public ExampleUtil() throws SQLException { super(); }  // (4)

    // (5) Version using String statement
    public ArrayList<UserBean> getUsers() throws SQLException (
        String statement = getSQLFile("getUsers.sql");
        return execQuery(statement, ArrayList.class, UserBean.class);
    }

    // (6) Version using JdbcStatement statement
    public ArrayList<UserBean> getUsers() throws SQLException (
        JdbcStatement statement = new JdbcStatement(getSQLFile("getUsers.sql"));
        return execQuery(statement, ArrayList.class, UserBean.class);
    }
}

1. The @ConnectionDataSource annotation defines what data source to use. This annotation is optional; if not present, the alternate constructor that takes a string parameter called jndiName should be used. If the class will always be used with one datasource, then it is recommended that the annotation is used. Otherwise if the class will be used with multiple data sources, then it is recommended that the alternate constructor should be used.
2. The @SQLFileContext annotation specifies what package the SQL files that correspond to this class may be found. When using a normal Dynamic Web Project, the SQL package is found in the same src folder as the class package. When using Maven, the SQL package is found in the src/main/resources folder.
3. Extend from JdbcUtil (you can also get the same from extending TableUtil; the difference simply would be the addition of TableUtil's methods).
4. At least one constructor based on JdbcUtil's constructors must be provided. The constructor must also include the throws SQLException clause. Refer to note 1 above about which constructor to use.
5. SQL statements processed as a String cannot use LOBs as parameters, and parameterized statements are early bound.
6. SQL statements processed as a JdbcStatement may have LOB parameters, and parameterized statements are late bound.

Parameterized Statements

Parameters in statements can be referenced by ordinal position (beginning with 1) or by name. Parameters may be referenced more than once in a statement. Additionally, parameters may be used to embed SQL fragments.

Statement Examples

:::sql
-- Example of oridnal references (Reference A)
SELECT t.* 
  FROM some_table t
 WHERE t.field_one = {1}
   AND t.field_two = {2}

 -- Example of named references (Reference B)
 SELECT t.* 
  FROM some_table t
 WHERE t.field_one = {fieldOne}
   AND t.field_two = {fieldTwo}

-- Example of ordinal reference for SQL fragment (Reference C)
SELECT t.*
  FROM some_table t
 WHERE t.field_one IN ({sql: 1})

 -- Example of named reference for SQL fragment (Reference D)
 SELECT t.*
  FROM some_table t
 WHERE t.field_one IN ({sql: fieldOneList}) 

Binding Examples

:::java
// Example of binding parameters to ordinal references (Reference A)
String statement = ...;
statement = applyParameters(statement, fieldOne, fieldTwo);

// Example of binding parameters to named references (Referrence B)
String statement = ...;
statement = applyParameterBean(statement, bean);
// The above assumes that bean contains the getters getFieldOne and getFieldTwo

// Example of binding SQL fragment (Reference C)
String statement = ...;
String fragment = "1, 2, 3";
statement = applyParameters(statement, fragment);

// Example of binding SQL fragment (Reference D)
String statement = ...;
SomeBean bean = ...;
bean.setFieldOneList("1, 2, 3");
statement = applyParameterBean(statement, bean);
// The above assumes that bean contains the getter getFieldOneList