Saturday, May 18, 2013

Direct SQL insert statements to Liferay out-of-the-box tables


I came across a scenario where i need to insert some values into default liferay table on my custom portlet deployment. Particularly ListType table..

FYI, I am using Liferay 6.1 EE GA2

Two approaches came into my mind

1. Using Hook's upgrade process approach

Step 1: file of your portlet should have below entries

# to run insert statements

Step 2: liferay-hook.xml should have below entry

Step 3:


import com.liferay.portal.kernel.upgrade.UpgradeProcess;

public class UpgradeProcess_1_1_0 extends UpgradeProcess {

public int getThreshold() { return 110; }

protected void doUpgrade() throws Exception {
// your upgrade code here. } }

runSQL("insert into ListType (listTypeId, name, type_) values (22000, 'local-client-address', 'com.liferay.portal.model.Organization.address')");
runSQL("insert into ListType (listTypeId, name, type_) values (22001, 'global-client-address', 'com.liferay.portal.model.Organization.address')");

That's it, you are good to go, it will insert relevant data into respective ListType table.

2. Using SqlUpdateFactoryUtil Liferay API method to insert according to my needs in my Entity (I have service layer if you don't have one you can create a facade i.e., entity without columns)

Step 1: For e.g., if your entity name is Dummy, in DummyLocalServiceUtil add below method

        public void insertStaticData(){

insert into ListType (listTypeId, name, type_) values (22000, 'local-client-address', 'com.liferay.portal.model.Organization.address');
insert into ListType (listTypeId, name, type_) values (22001, 'global-client-address', 'com.liferay.portal.model.Organization.address');
// DataSource dataSource = (DataSource) PortalBeanLocatorUtil.locate("liferayDataSource");
String insertQuery = "insert into ListType (listTypeId, name, type_) values (22002, 'local-client-address', 'com.liferay.portal.model.Organization.address')";

SqlUpdate _sqlUpdate = SqlUpdateFactoryUtil.getSqlUpdate(orgTypePersistence.getDataSource(), insertQuery, new int[]{});

int count = _sqlUpdate.update();
System.out.println("after update : no of records update : count length : "+ count);

Step 2: add an application startup events using hook, add below entry to

Step 3:


import com.liferay.portal.kernel.log.Log;
import com.liferay.portal.kernel.log.LogFactoryUtil;
import com.liferay.portal.model.ListType;
import com.liferay.portal.service.ListTypeServiceUtil;

import java.util.List;

public class OrgStaticDataStartUpAction extends SimpleAction {

public void run(String[] ids) throws ActionException {
// get types
List types = null;

try {
types = ListTypeServiceUtil.getListTypes("com.liferay.portal.model.Organization.address");

boolean recordsExists = true;
for (ListType listType : types) { // need to check for our custom values local & global if not add
if(!"local-client-address2".equalsIgnoreCase(listType.getName()) && !"global-client-address".equalsIgnoreCase(listType.getName()) ){
recordsExists = false;

if(!recordsExists){"Record not exists: starting insert");
OrgTypeLocalServiceUtil.insertStaticData();"Record not exists: end insert");
catch (Exception e) {
//type = new ListTypeImpl();


private static final Log LOGGER = LogFactoryUtil.getLog(OrgStaticDataStartUpAction.class);


Hope this will be helpful to some people. Blogging after long time - cheers :)