SQLind is NOT yet another persistence framework for java but a complementary API provinding tools to help doing what other frameworks such hibernate are not made for : perform complex queries and wire results to objects. SQLind doesn't create complex queries from objects but create objects from complex queries. Major benefits of the API are :

Externalization of SQL in a developper friendly xml file

On fly queries update for debug purpose

Advanced XML templated definition (conditionnal sections, included queries, ...)

High performance ruptured bean grap wiring using hot transformation class generation

 see : [ Getting started ... ]
 

 Latest stable release :


 Required libraires :

 Optional libraires :


 see : [ Maven ... ]

All releases are available from SQLind maven repository here

SQLind is available from maven central repository.

include one of these sections in your pom.xml :

SQLind ... <dependencies> <dependency> <groupId>net.sqlind</groupId> <artifactId>SQLind</artifactId> <version>2.1.2-GA</version> </dependency> ... </dependencies>
SQLind4HQL ... <dependencies> <dependency> <groupId>net.sqlind</groupId> <artifactId>SQLind4HQL</artifactId> <version>1.0.3-FINAL</version> </dependency> ... </dependencies> SQLind4EJB ... <dependencies> <dependency> <groupId>net.sqlind</groupId> <artifactId>SQLind4EJB</artifactId> <version>1.0.2-FINAL</version> </dependency> ... </dependencies>

Here is a 3 steps short tutorial to create your fist query with SQLind :

STEP 1 : Download SQLind library and add it to your classpath

   see : [ Maven ... ] [ Release ... ]


STEP 2 : Create a XML query template file in your classpath
<?xml version="1.0"?> <queries id="myQueryLib" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.sqlind.net/sqlind.xsd"> <query id="myQuery" policy="lru" > select myColumn from myTable where myField = <var id="field"/> </query> </queries> If attribute policy is set to 'reload', the query definition will be reloaded from file each time getSQLQuery(..) is called. This is obviously bad for performances but can be very usefull to allow hot modification. Note that the performed query string is logged by log4j using an INFO level. The 'debug' policy have the same behavior except that console is used instead of log. The tag <var id="field"/> declares a parameter (same id can be used several times ).

   see : [ Advanced templating ... ]


STEP 3 : Use SQLind API to invoke query
SQLQueryHandler q = mapper.getSQLQuery("myQueries.xml", "myDBSchema","myQuery"); q.setParameter("field","myValue"); q.performSelectQuery(con, q.new ResultSetFecthedBehaviour() { @Override public void doForEach(ResultSet rs) throws Exception { System.out.println(rs.getObject("myColumn")); } }); }    see : [ Advanced wiring ... ]


NOTE : if no wiring is needed, it is possible to create multi statement queries (PL/SQL) <?xml version="1.0"?> <queries id="myQueryLib" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.sqlind.net/sqlind.xsd"> <query id="myDeleteScript" policy="debug" > begin DELETE from MyTable1; DELETE from MyTable2; end; </query> </queries> }

<var id="fieldId"/> declares a parameter <query id="myQuery" policy="lru" > select myColumn from myTable where myField = <var id="field"/> </query>

<section id="name"/> defines optional sections in query <query id="myQuery" policy="lru" > select myColumn from myTable where myField = <var id="field"/> <section id="withSecondFieldOption"> and mySecondField = <var id="mySecondField"/></section> </query> SQLQueryHandler q = mapper.getSQLQuery("myQueries.xml","myDBSchema","myQuery","withSecondFieldOption"); q.setParameter("field","myValue"); q.setParameter("mySecondField","myOtherValue"); q.performSelectQuery(con, q.new ResultSetFecthedBehaviour() { @Override public void doForEach(ResultSet rs) throws Exception { System.out.println(rs.getObject("myColumn")); } }); }

<include id="queryId" params="sectionName,injectionId=value,..."/> includes another query. (params attribute is optional) <query id="mySecondQuery" policy="lru" > <include id="myQuery"/> order by myField </query>

<inject id="injectId"/> creates an injection (substitution) point in the query
<query id="myQuery" policy="lru" > select myColumn from myTable where myField in (<inject id="fieldValues"/>) </query> SQLQueryHandler q = mapper.getSQLQuery("myQueries.xml", "myDBSchema","myQuery"); q.setInjection("fieldValues","'val1','val2','val3'"); q.performSelectQuery(con, q.new ResultSetFecthedBehaviour() { @Override public void doForEach(ResultSet rs) throws Exception { System.out.println(rs.getObject("myColumn")); } }); } In case of query over several schemas, the db schema must be set to null in getSQLQuery(...) method to avoid auto prefexing. In this case, inject tag can be used to set a custom prefixing to each table.

  Wiring policies

lru : use a java reflection cache (implementing Last Recent Used algorythm) to create beans from query.

    This policy can be used if no bean wiring is done, when rare and small queries are performed or if javassist API cannot be added to classpath.
  <query id="myQuery" policy="lru"> select myColumn,substr(my2ndColumn,1,2) as 2nd, my3rdColumn as 3rd from myTable group by myColumn, my2ndColumn, my3rdColumn </query>

fast : create a transformation class at runtime to map beans from query result.

    This policy have an overhead compilation time on the first call but give very fast performances.
 When used in a web container (Tomcat,Jboss...), some classpath issues can appears du to multiple classloader management.
 If some class cannot be found by javassist, they can be added using the -Dsqlindcp=<classpath> JVM argument.
<query id="myQuery" policy="fast"> select myColumn, substr(my2ndColumn,1,2) as 2nd, my3rdColumn as 3rd from myTable group by myColumn, my2ndColumn, my3rdColumn </query>

  Wiring behaviours

Create an annoted bean grap to fit your query

The SQLind(column="ColumnName or Alias") annotation placed on a setter method will map the matching field with the columnName column.
Note that severals columns can be defined on a given field to fit different queries : SQLind(column={"colName1","colName2"})
Column mapping is done trying to resolve simple column name (ex: 'table.ColName' or 'colName' ) or using an 'as' alias (ex: 'sum(colName) as total' )

The SQLind(link=Clazz) annotation placed on a setter method will propagate the mapping to the given aggregated bean. If the linked field is a single type,
the bean class will be entire part of the declaring bean level rupture definition (one to one relationship). If the field is a List of Bean,
the mapping will be done as a n+1 rupture level (one to many relationship).
 

public class LittleBean { private String myThirdField; @SQLind(column="3rd") public void setMyThirdField(String value) {this.myThirdField = value;} public String getMyThirdField() {return myThirdField;} }

public class LittlePea { private String mySecondField; @SQLind(column="2nd") public void setMySecondField(String value) {this.mySecondField = value;} public String getMySecondField() {return mySecondField;} }

public class Bean { private String myFirstField; private LittlePea myAggregatedBean; private List<LittleBean> myBeanList; @SQLind(column="myColumn") public void setMyFirstField(String value) {this.myFirstField = value;} public String getMyFirstField() {return myFirstField;} @SQLind(link=LittlePea.class) public void setMyAggregatedBean(LittlePea pea) {this.myAggregatedBean = pea;} public LittlePea getMyAggregatedBean() {return myAggregatedBean;} @SQLind(link=LittleBean.class) public void setMyBeanList(List<LittleBean> myBeanList) {this.myBeanList = myBeanList;} public List<LittleBean> getMyBeanList() {return myBeanList;} }

Perform query using a custom implemented behaviour
 

Considering the folowing datas in the table 'myTable' :	myColumn my2ndColumn my3rdColumn -------- ----------- ----------- A 1 a B 1 b B 2 c B 2 d
Here is the 'one bean per record' strategy SQLQueryHandler q = mapper.getSQLQuery("myQueries.xml", "mySchema","myQuery"); final List<Bean> beans = new ArrayList<Bean>(); q.performSelectQuery(con, q.new BeanWiringBehavior<Bean>(Bean.class) { @Override // called each time myColumn or my2ndColumn value changes public void doForEachRupture(Bean bean) {} @Override //called each time my3rdColumn value changes. public void doForEachSubRupture(Object bean) {} @Override // called on each record fetched. public void doForEachRecord(Bean bean) { beans.add(bean); } });
This implementation is equivalent to getSingleBeanList() method
Here is the 'one bean per rupture' strategy SQLQueryHandler q = mapper.getSQLQuery("myQueries.xml", "mySchema","myQuery"); final List<Bean> beans = new ArrayList<Bean>(); q.performSelectQuery(con, q.new BeanWiringBehavior<Bean>(Bean.class) { @Override // called each time myColumn or my2ndColumn value changes public void doForEachRupture(Bean bean) { beans.add(bean); } @Override //called each time my3rdColumn value changes. public void doForEachSubRupture(Object bean) {} @Override // called on each record fetched. public void doForEachRecord(Bean bean) {} });
This implementation is equivalent to getBeanList() method

SQLind4HQL and SQLind4EJB are extension libraries allowing to use SQLind with hibernate HQL and EJB QL.
The following examples are for hibernate but both API are very close (just replace Session by EntityManager and HQL by EJB in naming). <query id="myHQLquery" policy="lru"> select t2.aField as extra , t1.f1 as colB from entity.Entity1 as t1 , entity.Entity2 t2 where t1.key > <var id="key"/> <section id="IfSectionId"> or t1.f2='I'</section> </query> HQLQueryMapper mapper = HQLQueryMapper.getInstance(); Session session = getHibernateSession(); HQLQueryHandler q= mapper.getHQLQuery("resources/myQueries.xml", "myHQLquery"); q.setParameter("key",5); List<MyEntityBean> list =q.getBeanList(session, Entity1.class); @Entity // Note that query result can be mapped to any bean, not only entities. @Table(name="MyTable") public class Entity1{ private int key; private String f1; private String f2; private String extraCol; @Transient public String getExtraCol() {return extraCol;} @SQLind(column="extra") public void setExtraCol(String extraCol) {this.extraCol = extraCol;} @Column(name="F1") public String getF1() {return f1;} @SQLind(column="f1") public void setF1(String f1) {this.f1 = f1;} @Column(name="F2") public String getF2() {return f2;} @SQLind(column="f2") public void setF2(String f2) {this.f2 = f2;} @Id @Column(name="KEY") public int getKey() {return key;} @SQLind(column="key") public void setKey(Integer key) { // Use Class instead of primitive type if (key!=null) // and add this test to setter if null values are stored (directly avoid primitives in this case is best) this.key = key; } }
Obviosly, storing the query results in a persistent entity bean have a limited interest ,Criteria are better for that purpose until query use too many not direct relationshipped entities.

 see : [ Release ... ] [ Maven ... ]  [ Advanced wiring ... ] [ Advanced templating ... ]

 SQLind is a free API distributed under the Open Software License version 3.0 and can be use in any, commercial or non commercial, larger work.

 You can ask any question related to SQLind using the folowing email : infosqlind.net
 We encourage you to open a topic on the official source forge project forum here