Package com.zfabrik.util.jdbc

Class QueryBuilder

java.lang.Object
com.zfabrik.util.jdbc.QueryBuilder
public class QueryBuilder extends Object
Some simple utilities that help constructing SQL queries and update statements from field arrays. Parameter sets and filter expressions may be derived from value Java beans and filter Java beans respectively.

Naming conventions: A field name ALPHA_BETA (regardless of case) tranlates to the Java Bean property alphaBeta.

Java Bean property conventions: When reading conditions from beans, a property maxAlpha translates into an including upper bound for the field ALPHA, while a property minBeta translates into an excluding lower bound for the field BETA.

  • Constructor Details

    • QueryBuilder

      public QueryBuilder()

  • Method Details

    • insertClause

      public static String insertClause​(String table, String[] fields)
      constructs INSERT clause for the given table and fields to be used as a prepared statement string
      Parameters:
      table -
      fields -
      Returns:

    • insert

      public static int insert​(Connection conn, String table, Object bean)
      INSERTs the data defined by the given bean into the given table using the given connection. The fields names are derived from the bean properties using the following convention: a) Field names are lower case; b) an underscore is inserted where the property has a case switch to upper case
      Parameters:
      conn - the DB connection
      table - the name of the table
      bean - the bean providing the names and the data
      Returns:
      the result of the PreparedStatement.executeUpdate()

    • insert

      public static int insert​(Connection conn, String table, Map<String,​Object> data)
      INSERTs the data defined by the given map into the given table using the given connection. The fields names are derived from the map keys using the following convention: a) Field names are lower case; b) an underscore is inserted where the property has a case switch to upper case
      Parameters:
      conn - the DB connection
      table - the name of the table
      data - the data map provides the names and the data
      Returns:
      the result of the PreparedStatement.executeUpdate()

    • updateClause

      public static String updateClause​(String table, String[] fields)
      constructs UPDATE clause for the given table and fields to be used as a prepared statement string. In general it is necessary to append a WHERE clause
      Parameters:
      table -
      fields -
      Returns:

    • update

      public static int update​(Connection conn, String table, Object bean, String where, Object... whereParams)
      UPDATEs the data defined by the given bean into the given table using the given connection. The fields names are derived from the bean properties using the following convention: a) Field names are lower case; b) an underscore is inserted where the property has a case switch to upper case
      Parameters:
      conn - the DB connection
      table - the name of the table
      bean - the bean providing the names and the data
      where - a where clause using '?' as place holder for the data
      whereParams - parameters for the '?'s in the where clause
      Returns:
      the result of the PreparedStatement.executeUpdate()

    • update

      public static int update​(Connection conn, String table, Map<String,​Object> data, String where, Object... whereParams)
      UPDATEs the data defined by the given map into the given table using the given connection. The fields names are derived from the map keys using the following convention: a) Field names are lower case; b) an underscore is inserted where the property has a case switch to upper case
      Parameters:
      conn - the DB connection
      table - the name of the table
      data - the data map provides the names and the data
      where - a where clause using '?' as place holder for the data
      whereParams - parameters for the '?'s in the where clause
      Returns:
      the result of the PreparedStatement.executeUpdate()

    • selectClause

      public static String selectClause​(String table, String[] fields)
      constructs a select clause for the given table and fields

    • select

      public static List<Map<String,​Object>> select​(Connection conn, String table, String[] fields, String where, Object... whereParams)
      run a query

    • delete

      public static int delete​(Connection conn, String table, String where, Object... whereParams)
      DELETEs the rows specified by the given where statement in the given table using the given connection
      Parameters:
      conn - the DB connection
      table - the name of the table
      where - a where clause using '?' as place holder for the data
      whereParams - parameters for the '?'s in the where clause
      Returns:
      the result of the PreparedStatement.executeUpdate()

    • rowsCount

      public static int rowsCount​(Connection conn, String table)
      Returns the number of rows of the given table
      Parameters:
      conn -
      table -
      Returns:

    • beanParams

      public static Object[] beanParams​(Object bean, String[] fields, String... additionals)
      Reads Java bean property values from the given POJO for the given fields. The set of fields introspected is constructed from the passed array as well as some additional fields that will be appended.
      Parameters:
      bean -
      fields -
      additionals -
      Returns:

    • beanFields

      public static String[] beanFields​(Class<?> clz, String... omitted)
      compute an array of field names given a bean, following our naming conventions (see above). A list of field names to omit from the resulting array can be specified as convenience.
      Parameters:
      clz -
      Returns:

    • mask

      public static String mask​(String s)
      Translate the usual wildcards * and ? into SQL wildcards % and _.