Access the Table data in Liferay DB by SQL query

In some scenario, we may require to access the data in the Liferay DB by creating DB connection.

1)  Add the liferayDataSource in the portlet.xml

<bean id="liferayDataSource" class="com.liferay.portal.kernel.util.InfrastructureUtil" factory-method="getDataSource" />

<property name="dataSource">

   <ref bean="liferayDataSource"/>

</property>

2)      Java Class

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;



import com.liferay.portal.kernel.dao.jdbc.DataAccess;

import com.liferay.portal.kernel.log.Log;

import com.liferay.portal.kernel.log.LogFactoryUtil;

import com.liferay.portal.kernel.util.StringPool;

import com.liferay.portal.kernel.util.Validator;



public class AccessDataBySQL {



    final private static Log logger = LogFactoryUtil.getLog(AccessDataBySQL.class);





    public static List<String> getAllFirstName(String query) {

        List<String> result = new ArrayList<>();

        Connection conn = null;

        PreparedStatement stmt = null;

        ResultSet rs = null;



        try {



            conn = DataAccess.getConnection();

            String sql =  "SELECT FIRSTNAME AS NAME FROM USER_" ;

            stmt = conn.prepareStatement(sql);

            rs = stmt.executeQuery();



            while (rs.next()) {



                result.add(rs.getString("NAME"));

            }

        } catch (SQLException sqle) {

            logger.error(sqle);

        }finally{

            DataAccess.cleanUp(conn, stmt, rs);

        }





        return result;

    }





}