Practical guide to Openbravo’s SqlC

SqlC is a tool that builds a java class from a file with SQL statements. The java class contains the code necessary to connect it to a database, format the statement, execute it and return the resultset from the database. This is a common function for any statement execution. SqlC avoids this repetitive task building the java code from the definitions of the desired statements.

you can refer to Openbravo’s wiki for complete reference on SqlC. contrary to Openbravo DAL (data access layer) that exploit ORM (object relational model), SqlC simply use POJO (plain old java object) to achive their goal. At some point, this method have significant benefit:

  • for select statement, you can build complex sql query. no matter how many table joined, sub query, whatever, this no more than send sql select statement and let db server do the rest. the drawback is you need to maintain sql dialect for every database. if you work on 1 db, i.e openbravo with postgresql only, then SqlC is good for you.
  • for select statement which is return multiple rows, java class that builded by SqlC provide you a class, similar to java bean. I mean, this generated class contain member public variable, used to store return value. this scenario make you easier to manipulate return value.

enhancement on existing xsql file

when you need to improve openbravo functionality, you may need to improve related xsql (xml filed used by SqlC) too. for example, add one or more culumn as return value for a select statement. or add one column for insert statement those add one more parameter to java method used to insert record to database. I found some bug here when builds a java class. adding some column to return value, somehow SqlC failed to register member variable, cause an error: can not find symbol. here my tips when you need to improve existing xsql:

  1. if you want to add extra column for return value in the method that located in the middle or bottom of xql file, don’t do it directly. before change any code, just find 1st sql select statement, try to add extra column, then compile. this will trigger SqlC to add member variable properly. once done, add your required extra column on desired method.
  2. if you want to add extra column for insert statement, but at the same time need to add extra column for select also, then do it in 2 step. 1st: make it done for select statement. 2nd: add extra column for insert statement.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s