Automating JDBC CRUD Operations with Reflection

In a previous post–Fun with Reflection (Java)–I provided a brief demonstration of reflection by loading property files into an object.  I will now demonstrate how reflection can be used to automate some of the more tedious work surrounding JDBC CRUD operations–such as generating SQL and loading data from a java.sql.ResultSet into a class instance.

Consider the following class definition:

public class Employee
{
    private int id;
    private String firstName;
    private String middleName;
    private String lastName;
    private Date dateOfBirth;

    private String title;

    private double salary; 

   //rest of definition omitted for brevity.
}

for which exists a corresponding table in some database (PostgreSQL in this case–but that is trivial):

CREATE TABLE "Employees"
(

  id integer NOT NULL DEFAULT nextval('"Employees_ID_seq"'::regclass),
  "firstName" character varying(),
  "middleName" character varying(),
  "lastName" character varying(),
  "dateOfBirth" date,
  "title character varying(8),
  salary double precision,

  CONSTRAINT "employees_PK" PRIMARY KEY (id )
)

 

It is apparent that a mapping exists from the database table to the Java class.  As such, the work of inserting, querying, and updating–which require exhaustive repetition–can be drastically alleviated by using reflection.

Let us now consider an approach to generate an SQL INSERT INTO statement. The desired outcome is something like this (i.e. the SQL required for a prepared statement):

INSERT INTO "public"."Employees"("id","firstName","middleName",
                        "lastName","dateOfBirth","title","salary")
                  VALUES(?,?,?,?,?,?,?)

On the face of it, this statement can be generated easily by iterating over the list of fields in the class.  Consider the following method, which takes a class and corresponding table name.

public static String createInsertStatementSql(Class<?> zclass, String tableName)
{
  StringBuilder fields= new StringBuilder();
  StringBuilder vars= new StringBuilder();

  for(Field field : zclass.getDeclaredFields())
  {
      String name=field.getName();
      if(fields.length()>1)
      {
    fields.append(",");
    vars.append(",");
      }
      String quoted=quoteIdentifier(name);
      fields.append(quoted);
      vars.append("?");
  }

  String table=quoteIdentifier(SCHEMA) + "." + quoteIdentifier(tableName);
  String Sql="INSERT INTO " +table + "(" + fields.toString() + ") VALUES(" + vars.toString() + ")";
  return Sql; 
}

In an effort to make the method cleaner, another method–quoteIdentifier(String)–quotes the identifiers by simply concatenating the identifier with a preset identifier quote string (“)–this could be obtained from the database meta data via a connection. Furthermore, it assumes that the table is located in the public schema–stored in the SCHEMA string constant in this case.

Now it may be used like this:

Class<?> zclass=Employee.class;
String table="Employees";
String insertSql=TableUtil.createInsertStatementSql(zclass, table);

The next steps are to create the prepared statement, load the class instance field values, and finally execute the statement.  Following from the previous post, the instance field values can be read with reflection–and the value can then be stored in the respective prepared statement parameters:

public static PreparedStatement createInsertPreparedStatement(Connection conn, Object
                                       object,String tableName)
{
  PreparedStatement stmt=null;
  try
  {

      Class<?> zclass=object.getClass(); 
      String Sql=createInsertStatementSql(zclass, tableName);

      stmt=conn.prepareStatement(Sql); 

      Field[] fields=zclass.getDeclaredFields();
      for(int i=0;i<fields.length;i++)
      {
    Field field=fields[i];
    field.setAccessible(true); 

    Object value=field.get(object);
    stmt.setObject((i+1), value);
      }
  }

  catch(SecurityException | IllegalArgumentException
      | IllegalAccessException | SQLException e)

  {
      String string="Unable to create prepared statement: " + e.getMessage();
      throw new RuntimeException(string, e);
  } 

  return stmt;
}

And now to use it:

Employee emp=createEmployee(id++); //method not shown
                                                                                 //--simply sets employee field values
Date start= new Date();
PreparedStatement stmt=TableUtil.createInsertPreparedStatement(conn, emp,"Employees" );

he method createEmployee(int) uses the Data Factory (noted here) to generate the employee fields.  Finally, it is only necessary to call the executeupdate() method of the PreparedStatement.

Similarly, Reflection can be used to create a generic query utility.  Consider that we wish to use such a utility in the following fashion:

List <T> emps=TableUtil.query(conn, Employee.class, “Employees”,”id BETWEEN 1190 AND 1200″);

The primary concern of the query(…) method is to iterate over a java.sql.ResultSet for the Employees table, create instance of the Employee class, and finally load the result set fields into the respective class instance fields.

public static <T> Lis <T> query(Connection conn,Class<T> type,String tableName,String where)
           throws SQLException
{
        List<T> list= new ArrayList<T>();
        String table=quoteIdentifier(SCHEMA) + "." + quoteIdentifier(tableName);
        String Sql="SELECT * FROM " + table;

        if(!(where==null || where.isEmpty()))
        {
            Sql+=" WHERE " + where;
        }
 
        try(Statement stmt=conn.createStatement())
        {
            ResultSet rst=stmt.executeQuery(Sql);
            while(rst.next())
            {
                T t= type.newInstance();
                loadResultSetIntoObject(rst, t);
                list.add(t);
            }
        }
        catch(InstantiationException | IllegalAccessException e)
        {
            throw new RuntimeException("Unable to create record: " + e.getMessage(), e);
        }
        return list;
}

This method uses the Class.newInstance() method to create a new instance of the class (Class<T> type), and delegates the rest of the work to the helper method loadResultSetIntoObject(…)–see next code listing.

public static void loadResultSetIntoObject(ResultSet rst, Object object)

            throws IllegalArgumentException, IllegalAccessException, SQLException
{ 
  Class<?> zclass=object.getClass();
  for(Field field : zclass.getDeclaredFields())
  {
      String name=field.getName();
      field.setAccessible(true);
      Object value=rst.getObject(name);
      Class<?> type=field.getType();
      if(isPrimitive(type))
      {
    Class<?> boxed=boxPrimitiveClass(type);
    value=boxed.cast(value);
      }
      field.set(object, value);
  }
}

As with the previous example on reflection, this method iterates over the class declared fields, gets the field name, and sets the field to accessible–required to set the field value. The next step is to get the field’s type–Class<?> type=field.getType();. This is necessary for the simple reason that the Field.set(Object,Object) expects the value (second argument) to be an object. If the field type is a primitive, it cannot accept an object–the primitive must be boxed. As such, the type is checked to see if it is a primitive type (if(isPrimitive(type))) and if so, gets the corresponding boxed class (Class<?> boxed=boxPrimitiveClass(type);). Finally the boxed class can be used to cast the value ( value=boxed.cast(value);). The following code listing demonstrates an approach to test if a given class belongs to a primitive type, and to get the object (boxed) class for a primitive:

public static boolean isPrimitive(Class<?> type)
{
     return (type==int.class || type==long.class ||
             type==double.class  || type==float.class
            || type==boolean.class || type==byte.class
            || type==char.class || type==short.class);
}
 
 public static Class<?> boxPrimitiveClass(Class<?> type)
 {
     if(type==int.class){return Integer.class;}
     else if(type==long.class){return Long.class;}
     else if (type==double.class){return Double.class;}
     else if(type==float.class){return Float.class;}
     else if(type==boolean.class){return Boolean.class;}
     else if(type==byte.class){return Byte.class;}
     else if(type==char.class){return Character.class;}
     else if(type==short.class){return Short.class;}
     else
     {
         String string="class '" + type.getName() + "' is not a primitive";
         throw new IllegalArgumentException(string);
     }
 }

When performing an update, the desired SQL statement is something like this:

UPDATE “public”.”Employees” SET “firstName” = ?, “middleName” = ?, “lastName” = ?, “dateOfBirth” = ?, “title” = ?, “salary” = ? WHERE “id” = ?

Generally, generating this statement is no different than generating the insert statement.  The primary difference is that the update statement requires (usually) a where clause–for which the statement parameter is the nth parameter; and (generally) will correspond with the first field of the class that maps to the table–the primary key field.

The approach for generating the statement SQL will be to iterate over the class fields, and create the <column> = ? pair on each iteration–for fields that are not the primary key. If the field name matches the primary key, the where clause will be generated. See the following code listing:

public static String createUpdateStatementSql(Class<?> zclass, String tableName, String primaryKey)
{
  StringBuilder sets= new StringBuilder();
  String where=null;

  for(Field field : zclass.getDeclaredFields())
  {
      String name=field.getName();
      String pair=quoteIdentifier(name) + " = ?";
      if(name.equals(primaryKey))
      {
    where=" WHERE " + pair;
      }
      else
      {
    if(sets.length()>1)
    {
        sets.append(", ");
    }
    sets.append(pair);
      }
  }
  if(where==null)
  {
      String string="Primary key not found in '" + zclass.getName() + "'";
      throw new IllegalArgumentException(string);
  }

  String table=quoteIdentifier(SCHEMA) + "." + quoteIdentifier(tableName);
  String Sql="UPDATE " + table + " SET " + sets.toString() + where;

  return Sql;
}

Having created the column/parameter pairs string, it can be concatenated with the UPDATE key word as well as the schema/table name to generate the full SQL statement. Note that this method takes the field name corresponding to the primary key.

Now, the method to that creates the actual PreparedStatement as well as sets the parameter values:

public static PreparedStatement createUpdatePreparedStatement(Connection conn,Object object,
   String tableName, String primaryKey) throws SQLException
   {
       PreparedStatement stmt=null;

       try
       {
           Class<?> zclass=object.getClass();

           String Sql=createUpdateStatementSql(zclass, tableName, primaryKey);
           System.out.println(Sql);
           stmt=conn.prepareStatement(Sql);

           Field[] fields=zclass.getDeclaredFields();
           int pkSequence=fields.length;

           for(int i=0;i<fields.length;i++)
           {
               Field field=fields[i];
               field.setAccessible(true);
               Object value=field.get(object);

               String name=field.getName();
               if(name.equals(primaryKey))
               {
                   stmt.setObject(pkSequence, value);
               }
               else
               {
                   stmt.setObject(i, value);
               }
           }
       }
       catch(SecurityException | IllegalArgumentException
               | IllegalAccessException e)
       {
           String string="Unable to create PreparedStatement: " + e.getMessage();
           throw new RuntimeException(string,e);
       }
       return stmt;
   }

Consider the following code that performs a query:

String where="id = 1100";
List<Employee> emps=TableUtil.query(conn, Employee.class, "Employees",where);
Employee emp=emps.get(0);
System.out.println(emp)

which produces the the following output:
Employee{id=1100, firstName=Jackson, middleName=Daniel, lastName=Kujala, dateOfBirth=1931-07-08, title=Lallier Taetzsch , salary=0.07863904146791734}

and now you wish to make and update to the employee record:

emp.setFirstName("Daniel");
emp.setMiddleName("");
emp.setLastName("Jackson");
emp.setTitle("Archeologist");
emp.setSalary(0);
 
PreparedStatement stmt=TableUtil.createUpdatePreparedStatement(conn, emp, "Employees");
stmt.executeUpdate();
emps=TableUtil.query(conn, Employee.class, "Employees",where);
emp=emps.get(0);
System.out.println(emp);

Consider the new output:

Employee{id=1100, firstName=Daniel, middleName=, lastName=Jackson,  dateOfBirth=1931-07-08, title=Archeologist, salary=0.0}

As can be noted, whilst not a replacement for a JPA implementation in an enterprise context, reflection can be used to reduce the drudgery of hacking away to create SQL statements. More so, such a utility can reduce the need to include SQL statements within Java code–which even in small/experimental projects is best avoided.

Further enhancements can be made by using annotations–I will demonstrate this in a later post.

–Enjoy

Leave a Reply