Language-independent CRUD generator: Java Dao and View template examples

By Alvin Alexander. Last updated: June 4, 2016

As I continue the development of my language-independent CRUD generator application, I thought I'd share a look at a Java Dao class template that I worked on this morning, as well as two simple JSP view templates I created.

My Java Dao CRUD generator template

The Java Dao template is the first thing I've run into where I can see the need to add a little more functionality. Specifically in the areas where I need to build SQL queries, I can see where it would be very helpful to have a method that returns a comma-separated list of database table field names. I don't know enough about what I can do with Smarty templates yet, but at the moment it looks like this might be a lot easier to create in my CRUD tool than it would be to create with the Smarty templates capabilities, but I could be wrong.

To help you see this problem, here's the source code for my simple Java Dao template:

package <<$DAO_PACKAGE_NAME>>;

import java.sql.*;
import java.util.*;
import <<$MODEL_PACKAGE_NAME>>.*;
import <<$CONTROLLER_PACKAGE_NAME>>.*;

TODO/ERROR - this template has several known errors:
* need a getAllLike() method
* change method names to insert, update, delete, select, selectAll
* add a 'search' method?

public class <<$classname>>Dao
{
  
  // this method is used by the 'get' methods of this Dao class.
  private <<$classname>> getFromResultSet (ResultSet r) 
  throws SQLException
  {
    <<$classname>> <<$objectname>> = new <<$classname>>();
<<section name=id loop=$fields>>
    <<$objectname>>.set<<$fields[id]|capitalize>>( r.get<<$types[id]|capitalize>>("<<$fields[id]>>") );
<</section>>
    return <<$objectname>>;
  }

  /**
   * This method lets you select a <<$classname>> object by specifying the object's id.
   */
  public <<$classname>> selectByKey (Connection conn, int id) 
  throws SQLException
  {
    Commands currentRow = null;
    Statement statement = null;
    ResultSet resultSet = null;
    try
    {
      // TODO should do this with a PreparedStatement to avoid sql injection problems
      String query = " SELECT * FROM <<$tablename>> WHERE id = " + id;
      statement = conn.createStatement();
      resultSet = statement.executeQuery(query);
      if ( resultSet.next() )
      {
        currentRow = getFromResultSet(resultSet);
      }
    }
    catch (SQLException se)
    {
      // log exception if desired
      throw se;
    }
    finally
    {
      if ( statement != null )
      {
        statement.close();
      }
    }
  }

  /**
   * Insert a new <<$classname>> into the <<$tablename>> database table.
   */
  public void insert<<$classname>>(<<$classname>> <<$objectname>>, Connection connection)
  {
    try
    {
      // TODO/ERROR - need to fix this query, as it will have an extra comma at the end
      // create the insert statement
      String query = "INSERT INTO <<$tablename>> "
        + "(<<$fields_as_insert_csv_string>>)"
        + "VALUES (<<$prep_stmt_as_insert_csv_string>>)";

      // create the mysql insert preparedstatement
      PreparedStatement preparedStatement = connection.prepareStatement(query);
<<section name=insertid loop=$fields>>
      preparedStatement.set<<$types[insertid]|capitalize>>(<<$smarty.section.insertid.index_next>>, <<$objectname>>.get<<$fields[insertid]|capitalize>>();
<</section>>

      // execute the preparedstatement
      preparedStatement.execute();
      connection.close();
    }
    catch (Exception e)
    {
      // TODO log the exception however you normally do
    }
  }

  /**
   * Perform a SQL DELETE on the given id for the <<$tablename>> table.
   */
  public boolean deleteByKey (Connection connection, int id) 
  throws SQLException
  {
    try
    {
      String query = "DELETE FROM <<$tablename>> where id = ?";
      PreparedStatement preparedStatement = connection.prepareStatement(query);
      preparedStatement.setInt(1, id);
      preparedStatement.execute();
      connection.close();
    }
    catch (SQLException se)
    {
      // log exception if desired
      throw se;
    }
    finally
    {
      if ( preparedStatement != null )
      {
        preparedStatement.close();
      }
    }
  }


  /**
   * Update the given <<$classname>> object into the <<$tablename>> table.
   * Assumes the key for the object is the 'id' field.
   */
  public void update<<$classname>>(<<$classname>> <<$objectname>>, Connection connection)
  {
    try
    {
      // TODO/ERROR - need to fix this query, as it will have an extra comma at the end
      String query = "UPDATE <<$tablename>> SET "
                   + "<<$prep_stmt_as_update_csv_string>>"
                   + " WHERE id =?";

      // smarty template note: index_next lets the index start at 1 instead of 0
      // do all the 'set' statements for the fields
      PreparedStatement preparedStatement = connection.prepareStatement(query);
<<section name=updateid loop=$fields>>
      preparedStatement.set<<$types[updateid]|capitalize>>(<<$smarty.section.updateid.index_next>>, <<$objectname>>.get<<$fields[updateid]|capitalize>>();
<<assign var="nfields" value=$smarty.section.updateid.index_next>>
<</section>>
      // set the key

<<* using the smarty math function to get the right number here *>>
      preparedStatement.setInt(<<math equation="x + y" x=$nfields y=1>>, <<$objectname>>.getId());

      // execute the preparedstatement
      preparedStatement.execute();
      connection.close();
    }
    catch (Exception e)
    {
      // TODO log the exception however you normally do
    }
  }  
}

When I run this Dao template against the Drupal "users" database table I get this result:

package com.devdaily.foo.dao;

import java.sql.*;
import java.util.*;
import com.devdaily.foo.model.*;
import com.devdaily.foo.controller.*;

TODO/ERROR - this template has several known errors:
* need a getAllLike() method
* change method names to insert, update, delete, select, selectAll
* add a 'search' method?

public class UserDao
{
  
  // this method is used by the 'get' methods of this Dao class.
  private User getFromResultSet (ResultSet r) 
  throws SQLException
  {
    User user = new User();
    user.setUid( r.getInt("uid") );
    user.setName( r.getString("name") );
    user.setPass( r.getString("pass") );
    user.setMail( r.getString("mail") );
    user.setMode( r.getInt("mode") );
    user.setSort( r.getInt("sort") );
    user.setThreshold( r.getInt("threshold") );
    user.setTheme( r.getString("theme") );
    user.setSignature( r.getString("signature") );
    user.setCreated( r.getInt("created") );
    user.setAccess( r.getInt("access") );
    user.setLogin( r.getInt("login") );
    user.setStatus( r.getInt("status") );
    user.setTimezone( r.getString("timezone") );
    user.setLanguage( r.getString("language") );
    user.setPicture( r.getString("picture") );
    user.setInit( r.getString("init") );
    user.setData( r.getObject("data") );
    return user;
  }

  /**
   * This method lets you select a User object by specifying the object's id.
   */
  public User selectByKey (Connection conn, int id) 
  throws SQLException
  {
    Commands currentRow = null;
    Statement statement = null;
    ResultSet resultSet = null;
    try
    {
      // TODO should do this with a PreparedStatement to avoid sql injection problems
      String query = " SELECT * FROM users WHERE id = " + id;
      statement = conn.createStatement();
      resultSet = statement.executeQuery(query);
      if ( resultSet.next() )
      {
        currentRow = getFromResultSet(resultSet);
      }
    }
    catch (SQLException se)
    {
      // log exception if desired
      throw se;
    }
    finally
    {
      if ( statement != null )
      {
        statement.close();
      }
    }
  }

  /**
   * Insert a new User into the users database table.
   */
  public void insertUser(User user, Connection connection)
  {
    try
    {
      // TODO/ERROR - need to fix this query, as it will have an extra comma at the end
      // create the insert statement
      String query = "INSERT INTO users "
        + "(uid,name,pass,mail,mode,sort,threshold,theme,signature,created,access,login,status,timezone,language,picture,init,data)"
        + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

      // create the mysql insert preparedstatement
      PreparedStatement preparedStatement = connection.prepareStatement(query);
      preparedStatement.setInt(1, user.getUid();
      preparedStatement.setString(2, user.getName();
      preparedStatement.setString(3, user.getPass();
      preparedStatement.setString(4, user.getMail();
      preparedStatement.setInt(5, user.getMode();
      preparedStatement.setInt(6, user.getSort();
      preparedStatement.setInt(7, user.getThreshold();
      preparedStatement.setString(8, user.getTheme();
      preparedStatement.setString(9, user.getSignature();
      preparedStatement.setInt(10, user.getCreated();
      preparedStatement.setInt(11, user.getAccess();
      preparedStatement.setInt(12, user.getLogin();
      preparedStatement.setInt(13, user.getStatus();
      preparedStatement.setString(14, user.getTimezone();
      preparedStatement.setString(15, user.getLanguage();
      preparedStatement.setString(16, user.getPicture();
      preparedStatement.setString(17, user.getInit();
      preparedStatement.setObject(18, user.getData();

      // execute the preparedstatement
      preparedStatement.execute();
      connection.close();
    }
    catch (Exception e)
    {
      // TODO log the exception however you normally do
    }
  }

  /**
   * Perform a SQL DELETE on the given id for the users table.
   */
  public boolean deleteByKey (Connection connection, int id) 
  throws SQLException
  {
    try
    {
      String query = "DELETE FROM users where id = ?";
      PreparedStatement preparedStatement = connection.prepareStatement(query);
      preparedStatement.setInt(1, id);
      preparedStatement.execute();
      connection.close();
    }
    catch (SQLException se)
    {
      // log exception if desired
      throw se;
    }
    finally
    {
      if ( preparedStatement != null )
      {
        preparedStatement.close();
      }
    }
  }


  /**
   * Update the given User object into the users table.
   * Assumes the key for the object is the 'id' field.
   */
  public void updateUser(User user, Connection connection)
  {
    try
    {
      // TODO/ERROR - need to fix this query, as it will have an extra comma at the end
      String query = "UPDATE users SET "
                   + "uid=?,name=?,pass=?,mail=?,mode=?,sort=?,threshold=?,theme=?,signature=?,created=?,access=?,login=?,status=?,timezone=?,language=?,picture=?,init=?,data=?"
                   + " WHERE id =?";

      // smarty template note: index_next lets the index start at 1 instead of 0
      // do all the 'set' statements for the fields
      PreparedStatement preparedStatement = connection.prepareStatement(query);
      preparedStatement.setInt(1, user.getUid();
      preparedStatement.setString(2, user.getName();
      preparedStatement.setString(3, user.getPass();
      preparedStatement.setString(4, user.getMail();
      preparedStatement.setInt(5, user.getMode();
      preparedStatement.setInt(6, user.getSort();
      preparedStatement.setInt(7, user.getThreshold();
      preparedStatement.setString(8, user.getTheme();
      preparedStatement.setString(9, user.getSignature();
      preparedStatement.setInt(10, user.getCreated();
      preparedStatement.setInt(11, user.getAccess();
      preparedStatement.setInt(12, user.getLogin();
      preparedStatement.setInt(13, user.getStatus();
      preparedStatement.setString(14, user.getTimezone();
      preparedStatement.setString(15, user.getLanguage();
      preparedStatement.setString(16, user.getPicture();
      preparedStatement.setString(17, user.getInit();
      preparedStatement.setObject(18, user.getData();
      // set the key

      preparedStatement.setInt(19, user.getId());

      // execute the preparedstatement
      preparedStatement.execute();
      connection.close();
    }
    catch (Exception e)
    {
      // TODO log the exception however you normally do
    }
  }  
}

I haven't tested generated Dao code yet, so it probably has some errors in it besides what I've listed. The important thing though is that doesn't matter too much: Because this approach is based on templates, you can change the templates to look like anything you want. If you don't like my generated Dao/SQL code here, no problem, just create your own template to customize it the way you want it.

Java JSP CRUD views

While I'm in the neighborhood of discussing this morning's work, here are two Java JSP CRUD view templates I created this morning. I haven't tested these, but they look fine. Here's a "list" view JSP CRUD template that can be used to create something like a userList.jsp JSP file:

<%@ page import="<<$PACKAGE_NAME>>.<<$classname>>, java.util.List" errorPage="error.jsp" %>

<html>
<head>
  <title><<$classname>> List</title>
</head>

<body>

<h2><<$classname>> List</h2>

<table>

<<* --- the header row; put the field names here --- *>>
<tr>
<<section name=id loop=$fields>>
  <th><<$fields[id]>></th>
<</section>>
</tr>

<%
  // TODO get the list of users however you need to
  List <<$objectname>>s = <<$classname>>Controller.get<<$classname>>s();
  // TODO now need to iterate over this list of objects ...
  foreach (<<$classname>> : <<$objectname>>)
  {
%>
<<section name=id loop=$fields>>
  <tr>
    <td>
      <<* this demonstrates how to build a 'get' method *>>
      <%=<<$objectname>>.get<<$fields[id]|capitalize>>(); %>
    </td>
  </tr>
<</section>>
<%
  // end the foreach loop
  }
%>
</table>

</body>
</html>

And here's a "detail" view template JSP CRUD template. It can be used to create something like a userDetailView.jsp JSP:

<%@ page import="<<$PACKAGE_NAME>>.<<$classname>>" errorPage="error.jsp" %>

<<* --- this template lets the user view the detail for one object --- *>>
<<* --- (such as for one "user") --- *>>

<<* get the current object from the session or request *>>
<%
  // get the "user" object from the request
  <<$classname>> <<$objectname>> = (<<$classname>>)request.getObject("<<$objectname>>");
%>

<html>
<head>
  <title>Current <<$classname>></title>
</head>

<body>

<h2>Current <<$classname>></h2>

<form action="<<$classname>>Controller" method="post">

  <input type="hidden" name="ACTION" value="VIEW">
  <input type="hidden" name="CURRENT_PAGE" value="<<$classname>>View.jsp">

  <table>

<<* --- create an input field for each database table field --- *>>
<<section name=id loop=$fields>>
    <tr>
      <td><<$fields[id]>>:</td>
      <td><%=<<$objectname>>.get<<$fields[id]|capitalize>>(); %></td>
    </tr>
<</section>>

    <<* --- TODO: Add your own Edit, Delete, and Cancel links here, as desired --- *>>
    <tr>
      <td>
         
      </td>
      <td><input type=submit name="edit" value="  Edit  "><input
             type="button"
             value="  Cancel  "
             onClick="window.location.href='GO_SOMEWHERE.jsp'">
      </td>
    </tr>
  </table>

</form>
</body>
</html>

My CRUD generator application - Summary

I don't know if it makes sense sharing my CRUD generator application ideas here as I go along, but hopefully one of these days this can help serve as documentation for my CRUD application.

Again, I keep referring to this CRUD application as a "Java CRUD generator", but because it's based entirely on a templating approach, it's really a language/platform-independent CRUD generator application, and can serve as a CRUD generator for any programming language or platform, including: