Forums

Home » Liferay Portal » English » 3. Development

Combination View Flat View Tree View
Threads [ Previous | Next ]
toggle
External Database Connection & Fetching records anand gopalan October 25, 2012 4:24 AM
RE: External Database Connection & Fetching records anand gopalan October 25, 2012 4:26 AM
RE: External Database Connection & Fetching records Hitoshi Ozawa October 25, 2012 5:54 AM
RE: External Database Connection & Fetching records anand gopalan October 25, 2012 6:05 AM
RE: External Database Connection & Fetching records Gaurav Jain October 25, 2012 6:34 AM
RE: External Database Connection & Fetching records David H Nebinger October 25, 2012 6:48 AM
RE: External Database Connection & Fetching records Gaurav Jain October 26, 2012 12:11 AM
RE: External Database Connection & Fetching records David H Nebinger October 26, 2012 7:23 AM
RE: External Database Connection & Fetching records Siby Mathew October 25, 2012 8:47 AM
RE: External Database Connection & Fetching records David H Nebinger October 25, 2012 6:23 AM
RE: External Database Connection & Fetching records anand gopalan November 5, 2012 5:46 AM
RE: External Database Connection & Fetching records David H Nebinger November 5, 2012 6:03 AM
RE: External Database Connection & Fetching records anand gopalan November 5, 2012 10:17 PM
RE: External Database Connection & Fetching records Gautam Sharma November 6, 2012 1:54 AM
RE: External Database Connection & Fetching records Gautam Sharma October 26, 2012 8:12 AM
RE: External Database Connection & Fetching records anand gopalan November 5, 2012 11:06 PM
RE: External Database Connection & Fetching records anand gopalan November 6, 2012 1:54 AM
RE: External Database Connection & Fetching records Gautam Sharma November 6, 2012 2:02 AM
RE: External Database Connection & Fetching records anand gopalan November 6, 2012 2:50 AM
RE: External Database Connection & Fetching records Gautam Sharma November 6, 2012 2:52 AM
RE: External Database Connection & Fetching records anand gopalan November 6, 2012 4:07 AM
RE: External Database Connection & Fetching records Siby Mathew November 6, 2012 5:52 AM
RE: External Database Connection & Fetching records Gautam Sharma November 6, 2012 6:20 AM
RE: External Database Connection & Fetching records anand gopalan November 6, 2012 9:08 PM
RE: External Database Connection & Fetching records anand gopalan November 7, 2012 12:24 AM
RE: External Database Connection & Fetching records anand gopalan November 7, 2012 4:52 AM
RE: External Database Connection & Fetching records Gautam Sharma November 7, 2012 6:22 AM
RE: External Database Connection & Fetching records Gautam Sharma November 7, 2012 6:26 AM
RE: External Database Connection & Fetching records anand gopalan November 7, 2012 10:05 PM
RE: External Database Connection & Fetching records anand gopalan November 8, 2012 12:35 AM
RE: External Database Connection & Fetching records Siby Mathew November 8, 2012 2:57 AM
RE: External Database Connection & Fetching records anand gopalan November 8, 2012 3:29 AM
RE: External Database Connection & Fetching records Gautam Sharma November 8, 2012 4:40 AM
RE: External Database Connection & Fetching records anand gopalan November 8, 2012 5:02 AM
RE: External Database Connection & Fetching records Gautam Sharma November 8, 2012 5:13 AM
RE: External Database Connection & Fetching records Siby Mathew November 8, 2012 5:32 AM
RE: External Database Connection & Fetching records anand gopalan November 8, 2012 5:54 AM
RE: External Database Connection & Fetching records Siby Mathew November 8, 2012 6:16 AM
RE: External Database Connection & Fetching records David H Nebinger November 8, 2012 6:22 AM
RE: External Database Connection & Fetching records anand gopalan November 8, 2012 9:51 PM
RE: External Database Connection & Fetching records David H Nebinger November 9, 2012 5:43 AM
RE: External Database Connection & Fetching records anand gopalan November 8, 2012 10:30 PM
RE: External Database Connection & Fetching records anand gopalan November 9, 2012 3:21 AM
RE: External Database Connection & Fetching records Siby Mathew November 9, 2012 5:16 AM
anand gopalan
External Database Connection & Fetching records
October 25, 2012 4:24 AM
Answer

anand gopalan

Rank: Expert

Posts: 403

Join Date: March 2, 2012

Recent Posts

Hi,

I want to connect Oracle database its external database(Production database).

My Environment
Liferay:6.1.0 CE,
Database: oracle11
LifeIDE & Plugin SDK



Steps I followed to connect external database:

1. portal-ext.properties file

added below line in portal-ext.properties ( location : liferay\liferay-portal-6.1.0-ce-ga1\tomcat-7.0.23\webapps\ROOT\WEB-INF\classes )

#oracle
#
jdbc.default.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.default.url=jdbc:oracle:thin:@xxx.xxx:1521:xx
jdbc.default.username=xxxx
jdbc.default.password=xxx
jdbc.default.jndi.name=jdbc/LiferayPool

2. Added ojdbc-6.jar ( location: \liferay\liferay-portal-6.1.0-ce-ga1\tomcat-7.0.23\lib\ext)


My questions
1. did i miss any steps to connect the external database
2. I want to fetch the record from existing table:


FYI: Existing table name Employee and its records. & I want have right to create the table

My Service.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 6.1.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_6_1_0.dtd">


<namespace>xxxx</namespace> what name i need to declare here
<entity name="xxxx" local-service="true" remote-service="true"> what name i need to declare here

<service-builder package-path="com.test">
<author>agopalan</author>
<namespace>xxxx</namespace>

<entity name="xxxx" local-service="true" remote-service="true">
<column name="xxx" type="long" primary="true"></column>
<column name="xxx" type="String"></column>

<!-- Finder methods -->

<finder name="xxxxx" return-type="xxxx">
<finder-column name="xxxx" />
</finder>

</entity>
</service-builder>

Please help me.
anand gopalan
RE: External Database Connection & Fetching records
October 25, 2012 4:26 AM
Answer

anand gopalan

Rank: Expert

Posts: 403

Join Date: March 2, 2012

Recent Posts

Sorry typo error

FYI: Existing table name Employee and its has records. & I don't have right to create the table.
Hitoshi Ozawa
RE: External Database Connection & Fetching records
October 25, 2012 5:54 AM
Answer

Hitoshi Ozawa

Rank: Liferay Legend

Posts: 7954

Join Date: March 23, 2010

Recent Posts

#oracle
#
jdbc.default.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.default.url=jdbc:oracle:thin:@xxx.xxx:1521:xx
jdbc.default.username=xxxx
jdbc.default.password=xxx
jdbc.default.jndi.name=jdbc/LiferayPool


You don't have to define both jdbc and jndi properties. If you're going to define jdbc connection the portal-ext.properties, comment out
#jdbc.default.jndi.name=jdbc/LiferayPool
anand gopalan
RE: External Database Connection & Fetching records
October 25, 2012 6:05 AM
Answer

anand gopalan

Rank: Expert

Posts: 403

Join Date: March 2, 2012

Recent Posts

thank you Hitoshi,

what about my Service.xml.

I want to fetch the Employee Records, I don't have access to create a table.

How to declare in Service.xml ? Please let me know.
David H Nebinger
RE: External Database Connection & Fetching records
October 25, 2012 6:23 AM
Answer

David H Nebinger

Rank: Liferay Legend

Posts: 7237

Join Date: September 1, 2006

Recent Posts

First of all it will not be an 'external' database since you've changed your jdbc.default.xxx properties to point to it. The oracle database listed will be the only database used by Liferay.

At startup, Liferay will see that it's tables do not exist and will start to create it's tables in the oracle database.

If the credentials you've used do not have create table permissions, this is going to fail.

For an external database, the database must be different than the one that is used in the jdbc.default.xxx properties from portal-ext.properties. The external database is typically defined in the ext-spring.xml file used in the plugin where the service is being created, and you'll use the beans you define here as attributes on the entity in service.xml.

Note that Service Builder does not include the concepts of the catalog or schema, all tables must either be directly accessible in the default catalog (for the user credentials used for the connection) or there must be a public synonym defined within the Oracle database.

So if your default catalog is LPORTAL, Liferay would look for LPORTAL.EMPLOYEE. If you have the employee table in a different catalog, i.e. COMPANY.EMPLOYEE, Liferay would not know the table was there and would actually end up creating one as LPORTAL.EMPLOYEE (probably not what you want).

You can disable the table creation stuff in the service.properties file by setting "build.auto.upgrade" to false, but it won't solve your problem of accessing tables in a different catalog.
Gaurav Jain
RE: External Database Connection & Fetching records
October 25, 2012 6:34 AM
Answer

Gaurav Jain

Rank: Regular Member

Posts: 141

Join Date: January 31, 2011

Recent Posts

anand gopalan:
thank you Hitoshi,

what about my Service.xml.

I want to fetch the Employee Records, I don't have access to create a table.

How to declare in Service.xml ? Please let me know.



Hi Anand,

Seems like your database is not a liferay db (means created by liferay itself).
If so, then you can not access it simply with liferay.
You can connect to it in Legacy db manner.

For this, code a portlet plugin and there you can create direct sql queries to db.

- Specify a datasource entry in tomcat context.xml say with name jdbc/anandDB
- Now you have yo specify this in your portlet plugin portlet.properties file; something like: anand.jdbc.context=java:comp/env/jdbc/anandDB
- Now in your classes :
1Context context = new InitialContext();
2DataSource ds = (DataSource) context.lookup("anand.jdbc.context");
3Connection con = ds.getConnection();
4
5Statement stmt = con,createStatement();
6ResultSet rs = stmt.executeQuery("/*your query goes here*/");
David H Nebinger
RE: External Database Connection & Fetching records
October 25, 2012 6:48 AM
Answer

David H Nebinger

Rank: Liferay Legend

Posts: 7237

Join Date: September 1, 2006

Recent Posts

Gaurav Jain:
Seems like your database is not a liferay db (means created by liferay itself).
If so, then you can not access it simply with liferay.
You can connect to it in Legacy db manner.

For this, code a portlet plugin and there you can create direct sql queries to db.



Wrong, wrong, wrong. Do not listen to this, as it totally violates every principle we now have in database access, especially the primary concern of using an ORM system to remove dependency on SQL, marshalling, etc.

Guaray is new here (based upon his post count) and clearly he's trying to council you how to do it in a typical newbie fashion, where he doesn't have the experience to do things the right way and settles on a bad implementation that happens to work.

Guaray should read up on the doco for connecting service builder to an external database and he would soon learn that this archaic process he's proposing is totally unnecessary.
Siby Mathew
RE: External Database Connection & Fetching records
October 25, 2012 8:47 AM
Answer

Siby Mathew

Rank: Expert

Posts: 261

Join Date: March 4, 2011

Recent Posts

Hi Anand,
I did a search in the forum and found this post :
http://www.liferay.com/community/forums/-/message_boards/message/11580997

Here Mr.Sagar has posted on how to connect your entity to a specific data-source... Try it out....

Thanks,
Siby
Gaurav Jain
RE: External Database Connection & Fetching records
October 26, 2012 12:11 AM
Answer

Gaurav Jain

Rank: Regular Member

Posts: 141

Join Date: January 31, 2011

Recent Posts

David H Nebinger:
Gaurav Jain:
Seems like your database is not a liferay db (means created by liferay itself).
If so, then you can not access it simply with liferay.
You can connect to it in Legacy db manner.

For this, code a portlet plugin and there you can create direct sql queries to db.



Wrong, wrong, wrong. Do not listen to this, as it totally violates every principle we now have in database access, especially the primary concern of using an ORM system to remove dependency on SQL, marshalling, etc.

Guaray is new here (based upon his post count) and clearly he's trying to council you how to do it in a typical newbie fashion, where he doesn't have the experience to do things the right way and settles on a bad implementation that happens to work.

Guaray should read up on the doco for connecting service builder to an external database and he would soon learn that this archaic process he's proposing is totally unnecessary.


emoticon

Ofcourse your liferay system should be connected to a liferay db; so let liferay create its db or otherwise liferay will create required tables to the connected database.
But the way I have mentioned is for connecting to external dbs which are not liferay based.
Its not any violation to liferay in my opinion. Its just a way to connect and get records from external entities. But ofcourse your liferay application should be connected to a liferay db for running your liferay portal , otherwise your liferay portal is not going to run anyway.

@David, I understand your concern, and thanks for the suggestions as well.
But I hope these comments of mine will clarify my post. emoticon

@Anand, I will also suggest you to listen to Liferay experts/masters first emoticon
David H Nebinger
RE: External Database Connection & Fetching records
October 26, 2012 7:23 AM
Answer

David H Nebinger

Rank: Liferay Legend

Posts: 7237

Join Date: September 1, 2006

Recent Posts

Gaurav Jain:
But the way I have mentioned is for connecting to external dbs which are not liferay based.
Its not any violation to liferay in my opinion. Its just a way to connect and get records from external entities. But ofcourse your liferay application should be connected to a liferay db for running your liferay portal , otherwise your liferay portal is not going to run anyway.


The point you're missing is that this is bad practice in a portal environment.

In a portal environment, you don't want individual portlets creating and maintaining separate connections to a database. As the number of portlets that need individual database connections increase, you're adding additional resource requirements in the form of database connections, transaction management, etc.

Service builder, while not perfect, helps to mitigate these additional resource requirements.

Sure, it is definitely possible for you to hand-code SQL and manage database connections at a per-portlet level. The problem is that most developers do not see the impact of this decision at a wider scope, the scope of a full portal implementation.

As an architect I do see the impact of these individual decisions and have to counsel everyone against moving towards this kind of solution because, in the end, it will only negatively impact your portal project.
Gautam Sharma
RE: External Database Connection & Fetching records
October 26, 2012 8:12 AM
Answer

Gautam Sharma

Rank: Junior Member

Posts: 59

Join Date: April 30, 2012

Recent Posts

Hi Anand

Please go through below link.That shows how to connect to external databse using service builder concept

http://www.liferay.com/web/sten.martinez/blog/-/blogs/6831821

Thanks
anand gopalan
RE: External Database Connection & Fetching records
November 5, 2012 5:46 AM
Answer

anand gopalan

Rank: Expert

Posts: 403

Join Date: March 2, 2012

Recent Posts

At startup, Liferay will see that it's tables do not exist and will start to create it's tables in the oracle database.

If the credentials you've used do not have create table permissions, this is going to fail.


Hello David,

I have small doubts. As per your words, i have table User_ table in oralce database(FYI now I succesfully connect the oracle database).
now How can use service.xml? because i should compulsory give namespace and entity name. so if i give like below. Again It will create like user_User_. So how i will proceed further.

<namespace>user</namespace>

<entity name="User_" local-service="true">

but my Biggest question is? In Liferay we can't fetch records from existing table. If you know please send the procedure?

I checked in the Liferay Forum, the answer is service.xml for fetching record. But thing is Service.xml will create the table and then only fetching the record.
how It suite into my requirement.
David H Nebinger
RE: External Database Connection & Fetching records
November 5, 2012 6:03 AM
Answer

David H Nebinger

Rank: Liferay Legend

Posts: 7237

Join Date: September 1, 2006

Recent Posts

For liferay tables (if you're talking about their User_ table), you should stick with UserLocalServiceUtil to access user records.

For your own User_ table, well this is obviously more involved. First, Service Builder doesn't know anything about catalogs and schemas (a little short sighted if you ask me, but they do all of their dev using MySQL where it's not a problem).

You have two choices here:

1. Create a public synonym for your User_ table so the schema is not important. This may cause you problems when you have table name conflicts (i.e. your User_ table vs Liferay's User_ table).

2. Use separate logins to connect to your oracle database, where each login defaults to the schema that tables are accessed within. So for example you have the lportal user who defaults to the LPORTAL schema where the Liferay tables are, then you create a 'myschema' user who defaults to the "COMPANY_DATA" schema where your own User_ table is. When defining your entities, you just add the extra attributes to indicate they should use this other connection to the database, the one that's configured to log in with the 'myschema' user.

A couple of points per your reply:

* Namespace is used to prefix table names and should not be empty, but this really only gets invoked when your entities are using the default data source. If your entities are bound up using a different data source, the create sql scripts will not be generated.

* You can disable all table creation by changing the "build.auto.upgrade" flag to false in your service.properties file.
anand gopalan
RE: External Database Connection & Fetching records
November 5, 2012 10:17 PM
Answer

anand gopalan

Rank: Expert

Posts: 403

Join Date: March 2, 2012

Recent Posts

Hello Sir,

As per your guideline, what i understood is

If I use Different data source in service.xml I can fetch the records from any existing tables like User_ (Liferaytable), Employee_Details, sales_Details (non liferay table).

If its correct. Next I want know how to use Data source in service.xml, i will check in Liferay forum.

Thank you Sir.

Anand
anand gopalan
RE: External Database Connection & Fetching records
November 5, 2012 11:06 PM
Answer

anand gopalan

Rank: Expert

Posts: 403

Join Date: March 2, 2012

Recent Posts

Hello Gautam,

Is it work for Liferay6.1.0?
Gautam Sharma
RE: External Database Connection & Fetching records
November 6, 2012 1:54 AM
Answer

Gautam Sharma

Rank: Junior Member

Posts: 59

Join Date: April 30, 2012

Recent Posts

anand gopalan:
Hello Sir,

As per your guideline, what i understood is

If I use Different data source in service.xml I can fetch the records from any existing tables like User_ (Liferaytable), Employee_Details, sales_Details (non liferay table).

If its correct. Next I want know how to use Data source in service.xml, i will check in Liferay forum.

Thank you Sir.

Anand


Hi Anand,

yes you need to configure datasource.

for that add the following lines in $CATALINA_HOME/context.xml


<Resource name="jdbc/mydb"
auth="Container"
type="javax.sql.DataSource"
maxActive="100"
maxIdle="30"
maxWait="10000"
username="username"
password=""
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/mydb?autoReconnect=true"/>


and if you are using Eclipse please make sure you modify context.xml of Eclipse.
anand gopalan
RE: External Database Connection & Fetching records
November 6, 2012 1:54 AM
Answer

anand gopalan

Rank: Expert

Posts: 403

Join Date: March 2, 2012

Recent Posts

Hello Gautam,

http://www.liferay.com/web/sten.martinez/blog/-/blogs/6831821

After I follow the above url and I am getting error in ext-spring.xml


i have attached My service.xml, ext-spring.xml & ext-spring.jpg (error display)
Attachment

Attachments: ext-spring.xml (2.1k), ext-spring1.jpg (30.8k), service.xml (0.9k)
Gautam Sharma
RE: External Database Connection & Fetching records
November 6, 2012 2:02 AM
Answer

Gautam Sharma

Rank: Junior Member

Posts: 59

Join Date: April 30, 2012

Recent Posts

Hi Anand,

Please put below DTD in your ext-spring.xml file.

<?xml version="1.0" encoding="UTF-8"?>
<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">
anand gopalan
RE: External Database Connection & Fetching records
November 6, 2012 2:50 AM
Answer

anand gopalan

Rank: Expert

Posts: 403

Join Date: March 2, 2012

Recent Posts

</beans> was missing i added in the end.

Thank you Gautam, now I will write the business logic in doview and write view.jsp.
If i need any help. please help me.
Gautam Sharma
RE: External Database Connection & Fetching records
November 6, 2012 2:52 AM
Answer

Gautam Sharma

Rank: Junior Member

Posts: 59

Join Date: April 30, 2012

Recent Posts

sure i will emoticon
anand gopalan
RE: External Database Connection & Fetching records
November 6, 2012 4:07 AM
Answer

anand gopalan

Rank: Expert

Posts: 403

Join Date: March 2, 2012

Recent Posts

Hello Gautam,
2 tables User_ & Address (Liferay Table)

if User id is same display address table data.

Now I implementing and write UserPortlet.java in doview().


Address addressDetails = AddressLocalServiceUtil.findByUserId(user.getUserId());

renderRequest.setAttribute("addressDetails",addressDetails);

System.out.println("UserName1----->"+addressDetails.getUserName());

see the error message.
Attachment

Attachments: error1.jpg (34.7k)
Siby Mathew
RE: External Database Connection & Fetching records
November 6, 2012 5:52 AM
Answer

Siby Mathew

Rank: Expert

Posts: 261

Join Date: March 4, 2011

Recent Posts

You have two Address.java in your project.
1. com.liferay.portal.model.Address - Liferay class
2. com.test.model.Address - your custom class

Your problem :
a. You are trying to assign 1 to 2 which is wrong.
b. There is no findByUserId method. (See what method is available in the API)

Thanks,
Siby
Gautam Sharma
RE: External Database Connection & Fetching records
November 6, 2012 6:20 AM
Answer

Gautam Sharma

Rank: Junior Member

Posts: 59

Join Date: April 30, 2012

Recent Posts

Hi Anand,

Siby is right.You are having two imports for Address so remove the one which is not required.

Thanks
anand gopalan
RE: External Database Connection & Fetching records
November 6, 2012 9:08 PM
Answer

anand gopalan

Rank: Expert

Posts: 403

Join Date: March 2, 2012

Recent Posts

Thanks Siby and Gautam. I fixed after remove com.liferay.model.Address.
Now I deployed the Userdata-Portlet and I am getting below error.

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'com.test.service.AddressLocalService' defined in ServletContext resource [/WEB-INF/classes/META-INF/portlet-spring.xml]: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'com.test.service.AddressService' defined in ServletContext resource [/WEB-INF/classes/META-INF/portlet-spring.xml]: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'com.test.service.persistence.AddressPersistence' defined in ServletContext resource [/WEB-INF/classes/META-INF/portlet-spring.xml]: Cannot resolve reference to bean 'trainingDataSource' while setting bean property 'dataSource'; nested exception is org.springframework.beans.factory.NoSuchBeanDefinitionException: No bean named 'trainingDataSource' is defined
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:527)
.................
........................
Caused by: org.springframework.beans.factory.NoSuchBeanDefinitionException: No bean named 'trainingDataSource' is defined

Do I miss any Jar files under lib\ext folder? or I miss any steps to define the bean?
Steps I followed
1. write service.xml - Generate all related files
2. ext-spring.xml to define the datasource,session factory & transaction
3. change class name in portlet.xml
4. add <Resource> in context.xml
5. Write doView() to display the result

Regards
Anand
anand gopalan
RE: External Database Connection & Fetching records
November 7, 2012 12:24 AM
Answer

anand gopalan

Rank: Expert

Posts: 403

Join Date: March 2, 2012

Recent Posts

Any updates?
anand gopalan
RE: External Database Connection & Fetching records
November 7, 2012 4:52 AM
Answer

anand gopalan

Rank: Expert

Posts: 403

Join Date: March 2, 2012

Recent Posts

Hello siby & Gautam,

Any idea why I am getting the error. do I need to modify code in hibernate-spring.xml? or any other place.
Gautam Sharma
RE: External Database Connection & Fetching records
November 7, 2012 6:22 AM
Answer

Gautam Sharma

Rank: Junior Member

Posts: 59

Join Date: April 30, 2012

Recent Posts

Hey

give me some time i will share one sample application with you.
Gautam Sharma
RE: External Database Connection & Fetching records
November 7, 2012 6:26 AM
Answer

Gautam Sharma

Rank: Junior Member

Posts: 59

Join Date: April 30, 2012

Recent Posts

Hi Anand,


Please find attached sample application for your reference.

Let me know if you are still facing any issue.
Attachments: LegacyDataBase-portlet.war (114.2k)
anand gopalan
RE: External Database Connection & Fetching records
November 7, 2012 10:05 PM
Answer

anand gopalan

Rank: Expert

Posts: 403

Join Date: March 2, 2012

Recent Posts

Hello Gautam,

I defined the datasource=trainingDataSource in service.xml : and I am using Liferay6.1 & oracle11.

what I have to give the data source the url property? in which file i need to modify or add the datasource

Regards
Anand
anand gopalan
RE: External Database Connection & Fetching records
November 8, 2012 12:35 AM
Answer

anand gopalan

Rank: Expert

Posts: 403

Join Date: March 2, 2012

Recent Posts

Anybody know why I am getting the error?
Siby Mathew
RE: External Database Connection & Fetching records
November 8, 2012 2:57 AM
Answer

Siby Mathew

Rank: Expert

Posts: 261

Join Date: March 4, 2011

Recent Posts

Hi Anand,
Did you try using Gautam's sample application ?
Are you saying you got an error after deploying that ? (If yes, what is the new error)

In the sample application, the trainingdatasource is defined inside src/META-INF/ext-spring.xml

Thanks,
Siby
anand gopalan
RE: External Database Connection & Fetching records
November 8, 2012 3:29 AM
Answer

anand gopalan

Rank: Expert

Posts: 403

Join Date: March 2, 2012

Recent Posts

Hello Siby,

i didn't deploy, because I don't have book & book_inventory table and and I don't have permission to create a table in my oracle database.

I have attached all configuration files. for your reference. It may help to identify the error.

Please help me siby.

Regards
Anand
Attachments: datasource.zip (9.8k)
Gautam Sharma
RE: External Database Connection & Fetching records
November 8, 2012 4:40 AM
Answer

Gautam Sharma

Rank: Junior Member

Posts: 59

Join Date: April 30, 2012

Recent Posts

Hi Anand

I attached this for your reference.

You can have a look in this application and try to find out what is missing.

Thanks
anand gopalan
RE: External Database Connection & Fetching records
November 8, 2012 5:02 AM
Answer

anand gopalan

Rank: Expert

Posts: 403

Join Date: March 2, 2012

Recent Posts

Hello Gautam,

I missed only server-config.wsdd. I am using two liferay tables and from oracle database. Is it really required this server-config.wsdd file.

if i want to create server-config.wsdd in eclipse, how to create? it is like normal file creation and copy the fil from your application and place under web-inf and build WSDD.

Suppose if I call Non Liferay table then server-config.wsdd is required? please clarify my doubts?



Regards
Anand
Gautam Sharma
RE: External Database Connection & Fetching records
November 8, 2012 5:13 AM
Answer

Gautam Sharma

Rank: Junior Member

Posts: 59

Join Date: April 30, 2012

Recent Posts

Hi Anand

server-config.wsdd is not required.This is used when you want to expose your methods using web services.

Thanks
Siby Mathew
RE: External Database Connection & Fetching records
November 8, 2012 5:32 AM
Answer

Siby Mathew

Rank: Expert

Posts: 261

Join Date: March 4, 2011

Recent Posts

Hi Anand,
Please check if you have given the proper url as your jndiName in your ext-spring.xml
Please attach the entire stacktrace if error persists.

Thanks,
Siby
anand gopalan
RE: External Database Connection & Fetching records
November 8, 2012 5:54 AM
Answer

anand gopalan

Rank: Expert

Posts: 403

Join Date: March 2, 2012

Recent Posts

Hello Siby,

Now I didn't see that error message, now I am getting other error message. I have attached that and i have attach my full project.

FYI: what ever I have given in context.xml I gave same name in JNDI name in ext-spring.xml. Now I didn't get the error message.

Please send your valuable comments siby.

Regards
Anand
Attachments: UserData-portlet.zip (142.3k), context.xml (1.6k), error_msg.txt (49.6k)
Siby Mathew
RE: External Database Connection & Fetching records
November 8, 2012 6:16 AM
Answer

Siby Mathew

Rank: Expert

Posts: 261

Join Date: March 4, 2011

Recent Posts

Hi Anand,
I thought we had already discussed to delete your custom class "com.test.model.Address"
As you can see from the error message, your custom class is confusing hibernate :

Caused by: org.hibernate.DuplicateMappingException: duplicate import: Address refers to both com.test.model.Address and com.liferay.portal.model.Address (try using auto-import="false")


You said you want to use Liferay Address class, then why dont you directly use the Liferay API ?
As per the sample application: book and book_inventory are the tables in the external DB...rt ?
In your case, user_ is in the external DB, so specify that in the service.xml.

Please delete the Address entity from your service.xml and re-run the servicebuilder. (Only deleting the .java file will not help)

Thanks,
Siby
David H Nebinger
RE: External Database Connection & Fetching records
November 8, 2012 6:22 AM
Answer

David H Nebinger

Rank: Liferay Legend

Posts: 7237

Join Date: September 1, 2006

Recent Posts

First you should not be changing the bean definition of basePersistence in ext-spring.xml.

Second your trainingSessionFactory should be an instance of com.liferay.portal.dao.orm.hibernate.PortletSessionFactoryImpl.

Third, you don't have to define the mapping resources, that will be handled by spring.

Fourth, you do not need to define transaction management beans as that is handled by the underlying framework.

Fifth, don't listen to the other guy about deleting Address. It's just fine, you just bogarted the other bean definitions and that's causing your conflict, not the Address class itself.
anand gopalan
RE: External Database Connection & Fetching records
November 8, 2012 9:51 PM
Answer

anand gopalan

Rank: Expert

Posts: 403

Join Date: March 2, 2012

Recent Posts

Hello David & Siby,

Thanks for your valuable explanation

Can I get the example for 2 cases

1. If i want to fetch the records from the Liferay table and display the records

2. If I want to fetch the records from the Non Liferay table and display the records.

if you don't have example. Please give me the steps what I need to do.

Note: I don't have permission for create a table in database.

I am using Liferay6.1.0 & Oracle11.

Regards
Anand
anand gopalan
RE: External Database Connection & Fetching records
November 8, 2012 10:30 PM
Answer

anand gopalan

Rank: Expert

Posts: 403

Join Date: March 2, 2012

Recent Posts

1. ext-Spring.xml i created before it was not there. so please tell what is the default bean definition of basePersistence?

2. I removed Mapping Resources

<property name="mappingResources">
<list>
<value>META-INF/portlet-hbm.xml</value>
</list>
</property>

3. Do you mean below tag to remove

<bean id="transactionAdvice" class="org.springframework.transaction.interceptor.TransactionInterceptor">
<property name="transactionManager" ref="trainingTransactionManager" />
<property name="transactionAttributeSource">
<bean class="org.springframework.transaction.annotation.AnnotationTransactionAttributeSource">
<constructor-arg>
<bean class="com.liferay.portal.spring.annotation.PortalTransactionAnnotationParser" />
</constructor-arg>
</bean>
</property>
</bean>

Please answer sir.

Regards
Anand
anand gopalan
RE: External Database Connection & Fetching records
November 9, 2012 3:21 AM
Answer

anand gopalan

Rank: Expert

Posts: 403

Join Date: March 2, 2012

Recent Posts

Hello Gautam,

1. ext-Spring.xml i created before it was not there. so please tell what is the default bean definition of basePersistence?

2. I removed Mapping Resources

<property name="mappingResources">
<list>
<value>META-INF/portlet-hbm.xml</value>
</list>
</property>

3. Do you mean below tag to remove

<bean id="transactionAdvice" class="org.springframework.transaction.interceptor.TransactionInterceptor">
<property name="transactionManager" ref="trainingTransactionManager" />
<property name="transactionAttributeSource">
<bean class="org.springframework.transaction.annotation.AnnotationTransactionAttributeSource">
<constructor-arg>
<bean class="com.liferay.portal.spring.annotation.PortalTransactionAnnotationParser" />
</constructor-arg>
</bean>
</property>
</bean>


Do you have answer for my question? Please send your comments Gautam.
Siby Mathew
RE: External Database Connection & Fetching records
November 9, 2012 5:16 AM
Answer

Siby Mathew

Rank: Expert

Posts: 261

Join Date: March 4, 2011

Recent Posts

Hi Anand,
I think you should just keep the trainingDataSource in ext-spring.xml and delete everything else.
As David mentioned , it might be handled by the framework itself.
As you are already pointing to this datasource in the service.xml, it should refer correctly.

But still in my opinion, in the service.xml you should be referring to your User table (which is in the external DB ) and not the Address.

Thanks,
Siby
David H Nebinger
RE: External Database Connection & Fetching records
November 9, 2012 5:43 AM
Answer

David H Nebinger

Rank: Liferay Legend

Posts: 7237

Join Date: September 1, 2006

Recent Posts

anand gopalan:
1. If i want to fetch the records from the Liferay table and display the records


In this case you use the XxxLocalServiceUtil facade that is part of Liferay. You never try to create an entity that maps to Liferay's tables, you always use their entities.

2. If I want to fetch the records from the Non Liferay table and display the records.


You're already on the way there. You define the session factory in ext-spring.xml and use that definition in the creation of your beans.

BasePersistence, if you actually look at the class, is just a simple POJO container for the database entities, and acts as a base class for the generated entities. Liferay defines the values for BasePersistence so the values do not have to be defined for every entity created in service.xml. It ensures that all entities defined will default to the Liferay database connection.

When you override the values in the service.xml file in the entity definition, these values are used instead of the values in BasePersistence, and allow you to use an alternate database connection.

Being able to create a table or not has no effect on how you define the entities. As long as your external datasource is defined correctly the entity will be bound to the table in that datasource. You can disable the auto upgrade in service.properties to ensure that the SB code does not try to create the table.

Finally to answer #2, you still use the XxxLocalServiceUtil facade in your portlet code to access your own entities.