Informix Dynamic Server

 

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

Pre-requisite:

  • Informix dynamic server installed and configured

 

How to Configuring Informix in liferay …?

1) We need ifxjdbc.jar file and  need to place it in ‘tomcat/webapps/ROOT/WEB-INF/lib’  so that access able by all portlet.   you can get this from here

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

 

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=omkhan

jdbc.custom.default.password=test

 

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 contents

 

<?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 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=“biz_num” type=“String” />

<column name=“cust_id” type=“String” />

<column name=“roc_no” type=“String” />

<column name=“roc_date” type=“Date” />

<column name=“company_name” type=“String” />

<column name=“formerly_known” type=“String” />

<column name=“address1” type=“String” />

<column name=“address2” type=“String” />

<column name=“address3” type=“String” />

<column name=“address4” type=“String” />

<column name=“postcode” 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 this 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 <![CDATA[ and ]]>. 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<CompanyExt>

implements CompanyExtFinder{

 

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

@SuppressWarnings(“unchecked”)

public List<CompanyExt> 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<CompanyExt> fetchCompanyByState(int state) {

List<CompanyExt> 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”%>

<portlet:defineObjects />

 

 

<%

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

for(State state:states){

 

List<CompanyExt> companyExts=CompanyExtLocalServiceUtil.fetchCompanyByState(state.getState_code());

if(companyExts!=null){

for(CompanyExt companyExt:companyExts){ %>

 

<table>

<thead>

<tr>

<th>Company Id</th>

<th>Registere Number</th>

<th>Company Name</th>

</tr>

</thead>

<tr>

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

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

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

</tr>

</table>

<%}}} %>

Leave a Reply