StatementPreparer example

The following example shows how a SQL template is processed by StatementPreparer to create a java.sql.PreparedStatement.

  1. Consider a StatementPreparer created and initialised using using the following code:

    StatementPreparer preparer = new StatementPreparer();
    preparer.addSql("customer-sql.xml", getClass());

    Where the file customer-sql.xml contains:
    <sql-queries>
    	<sql key="customerSearch">
    		<![CDATA[
    		SELECT
    			*
    		FROM
    			Customer
    		WHERE
    			(1=1)
    		<#if firstName?exists>
    		AND
    			first_name = :firstName
    		</#if>
    		<#if age?exists>
    		AND
    			age = :age
    		</#if>
    		]]>
    	</sql>
    </sql-queries>	
    
  2. The following call is made to prepare(..):

    HashMap values = new HashMap();
    values.put("firstName", "John");
    PreparedStatement statement = preparer.prepare(conn, "customerSearch", values);
  3. The StatementPreparer locates the SQL template using the specified key. Then it processes it processes as a FreeMarker template using the values map as the context to produce:
    SELECT
    	*
    FROM
    	Customer
    WHERE
    	(1=1)
    AND
    	first_name = :firstName
    
  4. This result is then converted into prepared statement SQL, and the positions of the parameters are noted:
    SELECT
    	*
    FROM
    	Customer
    WHERE
    	(1=1)
    AND
    	first_name = ?
    
  5. Finally a java.sql.PreparedStatement is created using this sql and the values are bound in the order determined previously. Internally, the prepared statement will produce SQL something like:
    SELECT
    	*
    FROM
    	Customer
    WHERE
    	(1=1)
    AND
    	first_name = 'John'
    

Related topics

StatementPreparer