how can I execute my DB query

[Balázs Csönge, modifié il y a 9 années.]

Hi,

We are using Liferay 6.2 CE with Activiti workflow engine. During a workflow execution I need to select information from the database. I have an own custom table in liferay schema.
Using the liferay undocumented api, is any simple way to do the following: ???????????????????????????????????????????????????????????????????????????????
1. xyutil.pleaseGiveMeTheLiferayOwnDBConnectionParameters
2. open a new session/connection based parameters received in step 1
3. make an sql query (with my own select * from mytable)
4. execute the sql and receive result set
5. close session/connection
???????????????????????????????????????????????????????????????????????????????

PLEASE HELP ME!

Regards

Balázs

[David H Nebinger, modifié il y a 9 années.]

Sure, but this is a lot harder than just defining an entity in service.xml for your table, flushing out the SB implementation of your service, and then invoking the XxxLocalServiceUtil.getXxx() method to do your query. Besides, you get the benefit of the Liferay caching, data marshaling, etc.

[Balázs Csönge, modifié il y a 9 années.]

The DataAccess.getConnection() was the magic tool, which were so nice to give me the connection of the Liferay own database.

import java.util.HashMap;

import com.liferay.portal.kernel.dao.jdbc.DataAccess;
import com.liferay.portal.kernel.log.Log;
import com.liferay.portal.kernel.log.LogFactoryUtil;

public class WFCodeSetDAO {

    private static Log log = LogFactoryUtil.getLog(WFCodeSetDAO.class);

	public static HashMap<string, string> getAllWFParams() throws Exception {
		Connection connection = null;
		PreparedStatement ptmt = null;
		ResultSet resultSet = null;
		
		String sql = "select CODE_ID, CODE_DESC from bmp_codeset where SET_ID =?";
		HashMap<string, string> codeMap= new HashMap&lt;&gt;();
        try {
        	connection = DataAccess.getConnection();
        	log.debug("Connection made");
            ptmt = connection.prepareStatement(sql);
            ptmt.setString(1, "WF_PARAM");
            log.debug("PreparedStatement created");
            resultSet = ptmt.executeQuery();
            log.debug("Query executed");

            while (resultSet.next()) {
            	codeMap.put(resultSet.getString("CODE_ID"), resultSet.getString("CODE_DESC"));
            }
            log.debug(Integer.toString(codeMap.size()) + " workflow parameter have been selected.");
            
        } catch (Exception e) {
            log.error("Error occured during the selection of workflow parameters. Cause: " + e.getMessage(), e);
        	throw new Exception(e);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (ptmt != null) {
                    ptmt.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (Exception e) {
				// we can ignore this error: it must not occur during normal operation (logging is also not necessary, but causes no harm)
                log.error("An error occured during closing the result set - the prepared statement - connection.", e);
            }
        }
        
        return codeMap;
		
    } 
	
}
</string,></string,>