8/11/2011

Direct Connection to the Liferay Database

In Liferay, if you want to modify or read entities you would normally use dynamic queries. You can find a lot of tutorials here at liferay-blogging about the DynamicQuery API.


Sometimes you need a direct access to the database to use SQL for whatever reason there might be. This is a little tutorial to show you how you can archieve this with Liferay 6 and Tomcat 6.0.29. To access the database directly you need to define a datasource in your tomcat.
Open the file /conf/context.xml and add the following in the <Context> section:

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

This defines an access point for your database for the database lportal and the user "root" with the password "root". Change it to fit your needs and open the web.xml of the portlet or hook that needs to access the database.

Add the following:
<resource-ref>
    <description>DB Connection</description>
    <res-ref-name>jdbc/Liferay</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>

This makes the dataSource accessible in your portlet. To access and use it, trythe following snippet.It should print our all screen names of all registered users:
Context ctx = new InitialContext(); 
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/Liferay"); 
Connection conn = ds.getConnection();
Statement stmt = conn.createStatement(); 
ResultSet set = stmt.executeQuery("select screenname from user_"); 
if(set.next()) {
  System.out.println("Screenname :"+set.getString(1);
 } 
conn.close();

If you like this tutorial it would be very nice, if you could click on some of the google ads you see on the right side. It helps me run this block and motivates me ;)

If you have any questions, feel free to leave a comment.