Hi Devs,
We have been following a convention to put Database query string in a
constant elsewhere in the code when performing DB CRUD operations.
e.g.
try {
String sqlStmt = IdPManagementConstants.SQLQueries.GET_IDP_BY_NAME_SQL;
prepStmt = dbConnection.prepareStatement(sqlStmt);
prepStmt.setInt(1, tenantId);
prepStmt.setInt(2, MultitenantConstants.SUPER_TENANT_ID);
prepStmt.setString(3, idPName);
rs = prepStmt.executeQuery();
...
However I would propose to keep the query in the same location as the
SQL parameter binding and result set reading. Shall we make it within
local final variable(constant) in the method itself.
The advantage is that the Query is visible on the same screen as it is
being used. This reduces number of screen flips. Also remembering the
query at somewhere else is error prone.
This also help easy review and do query optimizations.
e.g.
final String INSERT_IDP_SQL = "INSERT INTO IDP (NAME, DISPLAY_NAME,
DESCRIPTION) VALUES(?,?,?)";
this.jdbcTemplate.executeUpdate(INSERT_IDP_SQL, (preparedStatement, bean) -> {
MetaIdentityProvider metaIdentityProvider =
identityProvider.getMetaIdentityProvider();
preparedStatement.setString(1, metaIdentityProvider.getName());
preparedStatement.setString(2, metaIdentityProvider.getDisplayName());
preparedStatement.setString(3, metaIdentityProvider.getDescription());
...
Please note that they are the same in terms of memory use and performance
wise as compiler make them constants.
Putting them as constants serve no purpose as the query will never be
reused in proper designed Data Access layer. We should reuse the code, not
the query.
WDYT?
--
*Ruwan Abeykoon*
*Associate Director/Architect**,*
*WSO2, Inc. http://wso2.com <https://wso2.com/signature> *
*lean.enterprise.middleware.*
_______________________________________________
Dev mailing list
[email protected]
http://wso2.org/cgi-bin/mailman/listinfo/dev
