Using TableUtil

The TableUtil class is a descendant of JdbcUtil. This class allows you to interact with database tables in a similar fashion to a persistence model such as JPA, however this is not a persistence model.

The TableUtil class (or its descendants) allow for the developer to manage rows in a table without writing SQL statements. A model object (POJO) is created for each table; the only requirement to make it work is the presence of the @JdbcTable annotation (described below).

:::java
package com.example.model;

import java.io.Serializable;
import java.util.Date;

import com.roth.jdbc.annotation.JdbcTable;
import com.roth.jdbc.annotation.PermissiveBinding;
import com.roth.jdbc.model.StateBean;

@JdbcTable(name = "example", // (1)
           primaryKeyColumns = {"example_id"})  // (2)
@PermissiveBinding
public class ExampleBean implements Serializable, StateBean {
    private static final long serialVersionUID = 1L;

    private Long exampleId;
    // More fields implied

    public Long getExampleId() { return exampleId; }  // (3)
    public void setExampleId(Long exampleId) { this.exampleId = exampleId; }

    // More getters and setters implied

    @Override
    public boolean isNew() {  // (4)
        boolean result = exampleId == null; 
        // Do whatever needs to be done every time here.
        if (result) {
            // Do whatever needs to be done when the record is new here.
        }
        return result;
    }
}

1. The @JdbcTable annotation is required. It is what tells the TableUtil class what table in the database to work with. There are three attributes: name (the name of the table), schema (the name of the schema the table belongs to), and primaryKeyColumns (an array of Strings representing the column name or names that make up the primary key).
2. The @PermissiveBinding annotation is optional. If present, TableUtil will not throw an exception if there are discrepencies between the POJO and the table structure, but will log warnings to indicate which fields in the table are not represented in the POJO. Please note that the POJO can have things that are not represented in the table; TableUtil will not care about these.
3. Each column in the table should be represented by a getter and setter that correspond to the column name. Unlike persistence models, TableUtil does not referernce the POJO's member values directly; it references only getters and setters. See note below about the naming convention.
4. The StateBean interface implements the isNew method. This is used by TableUtil to determine whether to insert or update when the save method is called.

Naming Convention

Column names from the table are referenced in lower case. This makes the interaction uniform across different DBMSs. The Java equivalent of a column name is the same string, but with underscores removed, and all characters that followed underscores up-cased. When looking for the getter or setter, the first character is up-cased, and the word "get" or "set" is prepended.

Example:
example_id translates to getExampleId and setExampleId
customer_type_desc translates to getCustomerTypeDesc and setCustomerTypeDesc
address_line_1 translates to getAddressLine1 and setAddressLine1

Methods

The methods that TableUtil provides (aside from those inherited from JdbcUtil) are:

get

The get method retrieves a singular POJO for the requested record.

:::java
public <T> T get(Class<?> beanClass, String where) throws SQLException;
public <T> T get(Class<?> beanClass, String where, Object bean) throws SQLException;

// Examples:
ExampelBean bean = get(ExampleBean.class, "example_id = 1");  // (1)
ExampelBean bean = get(ExampleBean.class, "example_id = {exampleId}", searchBean);  // (2)

1. Use a pre-defined filter to get the row.
2. Use a template filter and a POJO to fill in the parameter. In this example, TableUtil will look for getExampleId in the POJO.

getList

The getList method retrieves multiple POJOs in a List for the requested records.

:::java
public <T> T getList(Class<?> beanClass) throws SQLException;
public <T> T getList(Class<?> beanClass, String where) throws SQLException;
public <T> T getList(Class<?> beanClass, String where, Object bean) throws SQLException;
public <T> T getList(Class<?> beanClass, String where, String order, Object bean) throws SQLException;

// Examples:
List<ExampelBean> beans = getList(ExampleBean.class);  // (1)
List<ExampelBean> beans = getList(ExampleBean.class, "example_id <= 10");  // (2)
List<ExampelBean> beans = getList(ExampleBean.class, "example_id = {exampleId}", searchBean);  // (3)
List<ExampelBean> beans = getList(ExampleBean.class, "color = {color}", "example_id", searchBean);  // (4)
List<ExampelBean> beans = getList(ExampleBean.class, null, "example_id", null);  // (5)

1. Get all rows in the table.
2. Get all rows from the table that satisfy the filter.
3. Get all rows from the table that satisfy the templated filter.
4. Get all rows from the table that satisfy the templated filter, and order by example_id.
5. Get all rows from the table, and order by example_id.

insert

The insert method inserts one or more rows into one or more tables.

:::java
public int insert(Object bean) throws SQLException;
public int[] insert(Object[] beans) throws SQLException;
public int[] insert(Collection<?> beans) throws SQLException;

Examples:
ExampleBean bean = ...;
int rowsAffected = insert(bean);  // (1)
StateBean[] beans = ...;
int[] rowsAffected = insert(beans);  // (2)
List<StateBean> beans = ...;
int[] rowsAffected = insert(beans);  // (3)

1. Insert a single row in the table defined in ExampleBean.
2. Insert a batch of rows in tables defined by each of the objects in the array. Since the array is of objects that implement the StateBean interface, it can contain records of different classes that will insert records in different tables. The batch is committed in one transaction. Note: the use of StateBean is not required here. It was used as an example.
3. Insert a batch of rows. This is the same as example 2, but is supplied a List rather than an array.

update

The update method updates one or more rows in one or more tables.

:::java
public int update(Object bean) throws SQLException;
public int[] update(Object[] beans) throws SQLException;
public int[] update(Collection<?> beans) throws SQLException;

Examples:
ExampleBean bean = ...;
int rowsAffected = update(bean);  // (1)
StateBean[] beans = ...;
int[] rowsAffected = update(beans);  // (2)
List<StateBean> beans = ...;
int[] rowsAffected = update(beans);  // (3)

1. Update a single row in the table defined in ExampleBean.
2. Update a batch of rows in tables defined by each of the objects in the array. Since the array is of objects that implement the StateBean interface, it can contain records of different classes that will update records in different tables. The batch is committed in one transaction. Note: the use of StateBean is not required here. It was used as an example.
3. Update a batch of rows. This is the same as example 2, but is supplied a List rather than an array.

save

The save method inserts or updates one or more rows in one or more tables. Unlike the insert and update methods, the save method requires that the POJO implement the StateBean interface. The isNew method is used to determine whether to insert or update.

:::java
public int save(Object bean) throws SQLException;
public int[] save(Object[] beans) throws SQLException;
public int[] save(Collection<?> beans) throws SQLException;

Examples:
ExampleBean bean = ...;
int rowsAffected = save(bean);  // (1)
StateBean[] beans = ...;
int[] rowsAffected = save(beans);  // (2)
List<StateBean> beans = ...;
int[] rowsAffected = save(beans);  // (3)

1. Insert or update a single row in the table defined in ExampleBean.
2. Insert or update a batch of rows in tables defined by each of the objects in the array. Since the array is of objects that implement the StateBean interface, it can contain records of different classes that will insert records in different tables. The batch is committed in one transaction.
3. Insert or update a batch of rows. This is the same as example 2, but is supplied a List rather than an array.

delete

The delete method deletes one or more rows from one or more tables.

:::java
public int delete(Object bean) throws SQLException;
public int[] delete(Object[] beans) throws SQLException;
public int[] delete(Collection<?> beans) throws SQLException;

Examples:
ExampleBean bean = ...;
int rowsAffected = delete(bean);  // (1)
StateBean[] beans = ...;
int[] rowsAffected = delete(beans);  // (2)
List<StateBean> beans = ...;
int[] rowsAffected = delete(beans);  // (3)

1. Delete a single row from the table defined in ExampleBean.
2. Delete a batch of rows from tables defined by each of the objects in the array. Since the array is of objects that implement the StateBean interface, it can contain records of different classes that will insert records in different tables. The batch is committed in one transaction. Note: the use of StateBean is not required here. It was used as an example.
3. Delete a batch of rows. This is the same as example 2, but is supplied a List rather than an array.

getMap

The getMap method gets a LinkedHashMap<String,String> from a specified table. This method is useful when used in conjunction with the select tag in JSPs.

:::java
public LinkedHashMap<String,String> getMap(String table, String key, String value) throws SQLException;
public LinkedHashMap<String,String> getMap(String table, String key, String value, String order) throws SQLException;
public LinkedHashMap<String,String> getMap(String table, String key, String value, String order, String where) throws SQLException;

Examples:
LinkedHashMap<String,String> options = getMap("customer_type", "customer_type_id", "description");   // (1)
LinkedHashMap<String,String> options = getMap("customer_type", "customer_type_id", "description", "description");   // (2)
String filter = applyParameters("cusotmer_cat_id = {1}", customerCatId);
LinkedHashMap<String,String> options = getMap("customer_type", "customer_type_id", "description", "description", filter);   // (3)

1. Gets a list of all id/description pairs in the table, with no specific order.
2. Gets a list of all id/description paris in the table, ordered by description.
3. Gets a list of all id/description pairs in the table that qualify for the filter (by category), ordered by description.