Liferay is easy: Call Procedure in Liferay using jdbc methods

Wednesday, May 16, 2018

Call Procedure in Liferay using jdbc methods

In the blog we will see how to call procedure in liferay using jdbc methods. For this i already created a procedure that return 3 result sets (user, contact and account). We iterate these result sets using jdbc methods.

Here is my procedure code:-

	CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(
	IN `Param1` INT,
	IN `Param2` INT,
	IN `Param3` INT
	)
	LANGUAGE SQL
	NOT DETERMINISTIC
	READS SQL DATA
	SQL SECURITY DEFINER
	COMMENT 'this is for testing'
	BEGIN
	SELECT * from user_ us where us.userId = Param1 ;
	SELECT * from contact_ c where c.contactId =Param2;
	select * from account_ ac where ac.accountId = Param3;
	END

view raw test.sql hosted with ❤ by GitHub

And when we call this procedure it will return 3 result sets:-

Liferay code for call this procedure:-

	try {
	Connection connection = DataAccess.getConnection();
	CallableStatement stmt = connection.prepareCall("{call test(?,?,?)}");
	stmt.setInt(1, 10158);
	stmt.setInt(2, 10159);
	stmt.setInt(3, 10156);

	boolean isResult = stmt.execute();

	if(isResult){
	ResultSet rs1 = stmt.getResultSet();
	while(rs1.next()){
	System.out.println("UserId=>"+rs1.getString("userId"));
	System.out.println("Company Id=>"+rs1.getString("companyId"));
	}
	stmt.getMoreResults(Statement.CLOSE_CURRENT_RESULT);
	ResultSet rs2 = stmt.getResultSet();
	while(rs2.next()){
	System.out.println("ContactId=>"+rs2.getString("contactId"));
	System.out.println("Email Address=>"+rs2.getString("emailAddress"));
	}

	stmt.getMoreResults(Statement.CLOSE_CURRENT_RESULT);
	ResultSet rs3 = stmt.getResultSet();
	while(rs3.next()){
	System.out.println("Name =>"+rs3.getString("name"));
	System.out.println("Legal Name =>"+rs3.getString("legalName"));
	}

	}
	} catch (SQLException e) {
	e.printStackTrace();
	}