This website uses cookies to ensure you get the best experience. Learn More.
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 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 id="customSessionFactory"
value="com.liferay.portal.dao.orm.hibernate.PortletSessionFactoryImpl" />
<entry key="sessionFactoryClassLoader" value-ref="portletClassLoader" />
<entry key="sessionFactoryImplementor"
value-ref="customHibernateSessionFactory" />
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>
<td><%=companyExt.getCmpy_id()%></td>
<td><%=companyExt.getReg_num()%></td>
</table>
<%}}} %>
Once all done deploy the portlet and verify the result.