Using Custom SQL Queries

Service Builder creates finder methods that retrieve entities by their attributes (their column values). When you add a column as a parameter for the finder in your service.xml file and run Service Builder, it generates the finder method in your persistence layer and adds methods to your service layer that invoke the finder. If your queries are simple enough, you can use Dynamic Query to access Liferay’s database. If you want to do something more complicated (like JOINs), you can write your own custom SQL queries.

Deploy an Example

Start a new Liferay instance by running

docker run -it -m 8g -p 8080:8080 liferay/portal:7.4.3.112-ga112

Sign in to Liferay at http://localhost:8080. Use the email address [e-mail] and the password test. When prompted, change the password to learn.

Then, follow these steps to deploy the example:

1. Download and unzip the liferay-p9z0.zip example project.

   curl https://resources.learn.liferay.com/dxp/latest/en/liferay-development/liferay-internals/extending-liferay/liferay-p9z0.zip -O
   

   unzip liferay-p9z0.zip
   

2. Build and deploy the project module.

   cd liferay-p9z0
   

   ./gradlew deploy -Ddeploy.docker.container.id=$(docker ps -lq)
   

   Note

   This command is the same as copying the deployed jars to /opt/liferay/osgi/modules on the Docker container.

3. Confirm the deployment in the Liferay Docker container console.

   STARTED com.liferay.p9z0.web_1.0.0 [1386]
   STARTED com.liferay.p9z0.api_1.0.0 [1384]
   STARTED com.liferay.p9z0.service_1.0.0 [1385]
   

4. To verify the example module is working, open your browser to https://localhost:8080.

5. Add the P9Z0 Portlet to a page. You can find the example portlet under Sample in Widgets.

   

6. Add an entry by entering a name and a description. Leave Hidden unchecked. Click Add, and the new entry appears under P9Z0 Entries.

7. Add another entry with a different name and description. This time, check Hidden. The new entry doesn’t appear under P9Z0 Entries.

This example uses custom SQL to retrieve only entries with a specified value in the database (hidden_ = false).

Adding Custom SQL to the Code

1. Open P9Z0EntryLocalServiceImpl.java. The code for the custom SQL request is defined in the getEntries method.

public List<P9Z0Entry> getP9Z0Entries(boolean hidden) {
	Session session = null;

	try {
		session = p9z0EntryPersistence.openSession();

		String sql = "select * from P9Z0_P9Z0Entry where hidden_ = ?";

		SQLQuery sqlQuery = session.createSynchronizedSQLQuery(sql);

		sqlQuery.addEntity("P9Z0_P9Z0Entry", P9Z0EntryImpl.class);

		QueryPos queryPos = QueryPos.getInstance(sqlQuery);

		queryPos.add(hidden);

		return (List<P9Z0Entry>)QueryUtil.list(
			sqlQuery, p9z0EntryPersistence.getDialect(), QueryUtil.ALL_POS,
			QueryUtil.ALL_POS);
	}
	catch (Exception exception) {
		throw new SystemException(exception);
	}
	finally {
		p9z0EntryPersistence.closeSession(session);
	}
}

1. Store your SQL query in a string, replacing any variable values with a ?.

String sql = "select * from P9Z0_P9Z0Entry where hidden_ = ?";

1. Create an SQLQuery object. Liferay uses this object to handle SQL queries. The session creates an SQLQuery object to avoid issues in a clustered environment. Use the addEntity method to define the class of the object your query returns.

SQLQuery sqlQuery = session.createSynchronizedSQLQuery(sql);

sqlQuery.addEntity("P9Z0_P9Z0Entry", P9Z0EntryImpl.class);

1. If you used the ? placeholder in the string, create a QueryPos object from the SQLQuery. Add the values to be replaced in the order they appear on the string. This example only uses one.

QueryPos queryPos = QueryPos.getInstance(sqlQuery);

queryPos.add(hidden);

1. QueryUtil executes the query in the database. The list method is used to make GET calls and returns a list of the values in the response.

return (List<P9Z0Entry>)QueryUtil.list(
	sqlQuery, p9z0EntryPersistence.getDialect(), QueryUtil.ALL_POS,
	QueryUtil.ALL_POS);

Related Topics

• Business Logic with Service Builder
• Module Projects