Legacy Data Base (Informix 11.5 RDBMS)connection & retrieval of data in Liferay 6.2 CE

Informix Dynamic Server

Informix Dynamic Server, also known as IDS, is an extensible Relational Database Management System originally developed by Informix Software Inc. IDS is now part of the IBM Software Group database portfolio.

Use the below link to download Informix 11.5 server and need to register.

https://www-01.ibm.com/marketing/iwm/iwm/web/preLogin.do?source=ifxids

How to Install and Configure Informix Dynamic Server ...?

Please refer the link  http://www.supermap.com/EN/online/Deskpro%206.0/SDTechTheme/ExpressHtml/DataSources_Informix.htm#Configuring

once the configuration completed we need to create a database "matrade". Create tables r_state & company (for table columns refer below service.xml) within matrade dbase to carry out below example. Insert some data in both tables. You can refer IBM Informix manual to create a database and table.

How to Configuring Informix in liferay ...?

1)  We need ifxjdbc.jar file and place it in 'tomcat/webapps/ROOT/WEB-INF/lib' so that accessible by all portlet. you can get this from https://www-01.ibm.com/marketing/iwm/tnd/search.jsp?go=y&rs=ifxjdbc

2)  Make the following entries inside the "portal-setup-wizard.properties" under PORTAL_HOME for our custom Informix data base.

jdbc.custom.default.driverClassName=com.informix.jdbc.IfxDriver

jdbc.custom.default.url= jdbc:informix-sqli://host-name:port-number/dbName:INFORMIXSERVER=serverName

jdbc.custom.default.username=username of server instance jdbc.custom.default.password=password of server server

Note:

host-name - server name or IP Address of the remote server

port-number - port number of informix server

dbName - name of the database going to be connect

serverName - name given to the server during configuration

username & password  - is the one given during configuring Informix

 

Ex:

########### Liferay Portal Informix Local Database Configuration ########## jdbc.custom.default.driverClassName=com.informix.jdbc.IfxDriver

jdbc.custom.default.url=jdbc:informix-sqli://localhost:9088/matrade:INFORMIXSERVER=ol_qqq

jdbc.custom.default.username= *******

jdbc.custom.default.password= *********

now restart the server.

 

3)  Inside the portlet create a new file "ext-spring.xml" inside of "WEB-INF/src/META-INF" and paste the following content.

<?xml version="1.0"?>

<beans default-destroy-method="destroy" default-init-method="afterPropertiesSet"

       xmlns="http://www.springframework.org/schema/beans"

       xmlns:aop="http://www.springframework.org/schema/aop"

       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

       xsi:schemaLocation="http://www.springframework.org/schema/aop

       http://www.springframework.org/schema/aop/spring-aop-3.0.xsd

       http://www.springframework.org/schema/beans

       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">

 

       <bean id="customDataSource"

              class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">

              <property name="targetDataSource">

                     <bean class="com.liferay.portal.dao.jdbc.util.DataSourceFactoryBean">

                           <property name="propertyPrefix" value="jdbc.custom.default." />

                     </bean>

              </property>

       </bean>

 

       <bean id="customHibernateSessionFactory"

              class="com.liferay.portal.kernel.spring.util.SpringFactoryUtil"

              factory-method="newBean">

              <constructor-arg

                     value="com.liferay.portal.spring.hibernate.PortletHibernateConfiguration" />

              <constructor-arg>

                     <map>

                           <entry key="dataSource" value-ref="customDataSource" />

                     </map>

              </constructor-arg>

       </bean>

       <bean id="customSessionFactory"

              class="com.liferay.portal.kernel.spring.util.SpringFactoryUtil"

              factory-method="newBean">

              <constructor-arg

                     value="com.liferay.portal.dao.orm.hibernate.PortletSessionFactoryImpl" />

              <constructor-arg>

                     <map>

                           <entry key="dataSource" value-ref="customDataSource" />

                           <entry key="sessionFactoryClassLoader" value-ref="portletClassLoader" />

                           <entry key="sessionFactoryImplementor"

                                         value-ref="customHibernateSessionFactory" />

                     </map>

              </constructor-arg>

       </bean>

 

How to retrieve Informix data using Custom SQL statement ...?

Define a new entity 'CompanyExt' inside service.xml, mapped to informix data source and not the default one. table="company" & table="r_state" refers to the tables in Informix DB. The additional three attributes data-source, tx-manager and session-factory are mandatory. save file and generate the service layer.

Below is "service.xml" file entry.

<entity name="CompanyExt" local-service="true" remote-service="true"

              data-source="customDataSource" session-factory="customSessionFactory"  table="company">

              <column name="cmpy_id" type="int" primary="true" />

              <column name="reg_num" type="int" />

              <column name="company_name" type="String" />

              <column name="state" type="int" />

<entity>

 

<entity name="State" local-service="true"  remote-service="true"

              data-source="customDataSource" session-factory="customSessionFactory"  table="r_state">

       <column name="state_code" type="int" primary="true"></column>

       <column name="state_desc" type="String"  ></column>

</entity>

Create "default.xml" under "WEB-INF/src/custom-sql" (create the folder with the same name) and put the follwing custom SQL.

<?xml version="1.0" encoding="UTF-8"?>

       <custom-sql>

              <sql id = "com.myexport.services.service.persistence.CompanyExtFinderImpl.fetchCompanyByState">

                     <![CDATA[

                     select * from company  where state=? ;

                     ]]>

              </sql>

       </custom-sql>

This file will have all the queries in simple SQL format in the form of key-value pairs. The key is the id that can be anything. The value is the actual query embedded within . If you look the value it is nothing but a simple SQL select statement.

Create a new finder implementation class “CompanyExtFinderImpl” inside “WEB- INF/src/com /slayer/service/persistence”. While creating this class make it implement “CompanyExtFinder” interface and extend “BasePersistenceImpl”.

Below is the 'CompanyExtFinderImpl.java' file

 

public class CompanyExtFinderImpl extends BasePersistenceImpl implements CompanyExtFinder{

public static String FETCH_COMPANY_BY_STATE=CompanyExtFinderImpl.class.getName()+".fetchCompanyByState";

@SuppressWarnings("unchecked")

public List fetchCompanyByState(int state){

// 1. Open an ORM session Session session = openSession();

// 2. Get SQL statement from XML file with its name

String sql = CustomSQLUtil.get(FETCH_COMPANY_BY_STATE);

System.out.println("The Query is >>>>>>>>>"+sql);

// 3. Transform the normal query to HQL query

SQLQuery query = session.createSQLQuery(sql);

// 4. Add the actual entity to be searched

query.addEntity("CompanyExt", CompanyExtImpl.class);

// 5. Replace positional parameters in the query

QueryPos queryPos=QueryPos.getInstance(query);

queryPos.add(Integer.toString(state));

// 6. Execute query and return results.

return (List<CompanyExt>query.list();

}

}

 

Make all the necessary imports required by the new code.

Update “CompanyExtLocalServiceImpl” with the below method to invoke our new custom finder.

 

public List fetchCompanyByState(int state) {

List companyExts=null;

companyExts = CompanyExtFinderUtil.fetchCompanyByState(state);

return companyExts;

}

Make the imports for LMSBookFinderUtil.

Below is the code in view.jsp to retrieve company details as per state code.

 

<%@page import="java.util.List"%>

<%@ taglib uri="http://java.sun.com/portlet_2_0" prefix="portlet"%>

<%@page import="com.myexport.services.service.StateLocalServiceUtil"%>

<%@page import="com.myexport.services.model.State"%>

<%@page import="com.myexport.services.service.CompanyExtLocalServiceUtil"%>

<%@page import="com.myexport.services.model.CompanyExt"%>

<%

List states=StateLocalServiceUtil.getStates(-1, -1);

for(State state:states){

List companyExts=CompanyExtLocalServiceUtil.fetchCompanyByState(state.getState_code());

if(companyExts!=null){

for(CompanyExt companyExt:companyExts){ %>

<table>

<thead>

<tr>

<th>Company Id</th>

<th>Register Number</th>

<th>Company Name</th>

</tr>

</thead>

<tr>

<td><%=companyExt.getCmpy_id()%></td>

<td><%=companyExt.getReg_num()%></td>

<td><%=companyExt.getCmpy_id()%></td>

</tr>

</table>

<%}}} %>

Once all done deploy the portlet and verify the result.

 

 

 

 

0