Getting started

Maven users will need to add the following dependency to their pom.xml for this component:

<dependency>
  <groupId>net.sf.jkniv</groupId>
  <artifactId>jkniv-sqlegance</artifactId>
  <version>0.6.6</version>
</dependency>

Once you have configured your database, you will write SQL to manipulate your records at database. Here we began to use SQLegance, writing SQL.

Sql Context

The all sentences queries are keep in XML file, to get access the queries a central interface SqlContext is provided.

SqlContext sqlContext = SqlContextFactory.newInstance("/repository-sql.xml");

The file /repository-sql.xml is loaded from root classpath, maven project’s should be in src/main/resources folder.

Below present a full sample with yours tags:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<statements 
  xmlns="http://jkniv.sf.net/schema/sqlegance/statements"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://jkniv.sf.net/schema/sqlegance/statements
   http://jkniv.sf.net/schema/sqlegance/sqlegance-stmt.xsd">

  <include href="/another-sql-file.xml" />

  <select id="select-users" type="JPQL">
    select id, name from Users where id = 1
  </select>

  <update id="updateAuthor" type="NATIVE">
    update Author set username = #{username}, password = #{password}, email = #{email} where id = #{id}
  </update>

  <delete id="delete-author" type="NATIVE">
    delete from Author where id = #{id}
  </delete>

  <procedure id="update-itens" spname="sprUpdateItens">
    <parameter property="categoryid" mode="IN" />
  </procedure>

</statements>

SQL

Before to get a query it’s necessary load the sentences in SqlContext instance to get a query calling the methods:

import net.sf.jkniv.sqlegance.builder.SqlContextFactory;
import net.sf.jkniv.sqlegance.SqlContext;
import net.sf.jkniv.sqlegance.Sql;

public static void main(String[] args)
{
   SqlContext sqlContext = SqlContextFactory.newInstance("/repository-sql.xml");
   Sql statement = sqlContext.getQuery("select-users");

   String mysql = statement.getSql();

   System.out.println(mysql); // output -> select id, name from Users where id = 1
}

Invoking statement.getSql() just recover the static part of the query ignoring all conditional parts.

SQL Dynamically

<select id="select-roles">
  select id, name, status from Roles
  <where>
    <if test="status != null">
      and status = :status
    </if>
    <if test="name != null">
      and name like :name
    </if>
  </where>
</select>

To evaluate the conditional parts a input parameter must be passed to the statement.getSql(Object) method, any type of object can be used (POJO, Map, Properties etc).

    Sql statement = sqlContext.getQuery("select-roles");
    Role role = new Role();
    role.setStatus("ACTIVE");

    String mysql = statement.getSql(role);
    System.out.println(mysql); // output -> select id, name, state from Roles where status = :status

Just it!