Lifeary JDBC DataAccess API

Lifeary JDBC DataAccess API

This tutorial will demonstrate about DataAccess API of Liferay and how to access simple JDBC connection object in liferay. some times, developers may need simple database API to query rather using custom sql.  Liferay provides  com.liferay.portal.kernel.dao.jdbc.com.liferay.portal.kernel.dao.jdbc.DataAccess API which gives plan JDBC connection utility to execute queries along with Hibernate support through ServiceBuilder.

When to use DataAccessAPI? In the following cases you can use DataAccess API:

1. for simple select queries and joining multiple tables
2. Upgrading portlet – DataAccess API will be used in upgrade process to alter database tables data
3. Do not use DataAccess API for writing data and will lead to performance issues.

Example:

 String sql = "select cus.* from js_customer cus INNERJOIN js_category cat on cus.categoryId=cat.categoryId  where cus.userId="+1023;
 Connection connection = null;
 Statement statement = null;
 
 try{
 connection = DataAccess.getConnection();
 statement = connection.createStatement();
 ResultSet rs = statement.executeQuery(sql);
 while(rs.next()){
  // You logic goes here
 }
 rs.close();
 statement.close();
 connection.close();
 }catch(Exception e){
 e.printStackTrace();
 }