Run Native SQL Query in Hybris

1. Overview

In this Article, I will show how to create and how to run a native SQL query in Hybris.

First of all, I want to share with you my discomfort of using native SQL directly in Hybris, in fact Hybris FlexibleSearch and ModelService should satisfy the majority of your needs, however if this is not the case, Hybris provides a way to attack the database directly with native SQL queries.

2. Implementation

2.1. Insert a Record

This an INSERT SQL native query example :

public void insertRecordIntoTable() {

	Connection connection = null;
	PreparedStatement preparedStatement = null;
	
	String query = "INSERT INTO _TABLE_ (_ATTR_1_, _ATTR_2_) VALUES(?,?)";

	try {
		connection = Registry.getCurrentTenant().getDataSource().getConnection();
		preparedStatement = connection.prepareStatement(query);

		preparedStatement.setString(1, "value 1");
		preparedStatement.setString(2, "value 2");

		preparedStatement.executeUpdate();

	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		// This line may throw a RuntimeException if something went wrong !
		Utilities.tryToCloseJDBC(connection, preparedStatement, null);
	}
}

2.2. Delete a Record

This a DELETE SQL native query example :

public void deleteRecordFromTable() {

	Connection connection = null;
	PreparedStatement preparedStatement = null;

	String query = "DELETE FROM _TABLE_ WHERE _ID_ = ?";

	try {
		connection = Registry.getCurrentTenant().getDataSource().getConnection();
		preparedStatement = connection.prepareStatement(query);

		preparedStatement.setInt(1, 9999);

		preparedStatement.executeUpdate();

	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		// This line may throw a RuntimeException if something went wrong !
		Utilities.tryToCloseJDBC(connection, preparedStatement, null);
	}
}

2.3. Select Records

This a SELECT SQL native query example :

public void selectRecordsFromTable() {

	Connection connection = null;
	PreparedStatement preparedStatement = null;
	ResultSet resultSet = null;

	String query = "SELECT _ATTR_1_, ATTR_2_ FROM _TABLE_ WHERE _ID_ = ?";

	try {
		connection = Registry.getCurrentTenant().getDataSource().getConnection();
		preparedStatement = connection.prepareStatement(query);

		preparedStatement.setInt(1, 9999);

		resultSet = preparedStatement.executeQuery();

		while (resultSet.next()) {
			String attribute1 = resultSet.getString("_ATTR_1_");
			String attribute2 = resultSet.getString("ATTR_2_");
		}

	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		// This line may throw a RuntimeException if something went wrong !
		Utilities.tryToCloseJDBC(connection, preparedStatement, resultSet);
	}
}

Note that, this is will not work for you if your are using the embedded HSQL DB, because HSQL DB allows only one connection at a time to the database.

3. Conclusion

Even if using native SQL in Hybris is not so recommended, however sometimes we face situations where we don’t have many choices.