I want to get the feedback this code about the best practice I should follow and if I am using the connection pooling properly.
I have classes as follows:
DAOConnectionFactory- to fetch pooled connections from the Datasource
WatcherService- the REST WS
WatcherDAO- called by WatcherService to insert data in DB
Watcher- this is bean class for Watcher
Based on some other reviews I made the methods in the WatcherDAO class as static and the perparedstatement and connection as local variables.
Context.xml
<?xml version="1.0" encoding="UTF-8"?>
<!--Context antiJARLocking="true" path="/PresenceRepository"/-->
<Context path="/PresenceRepository">
<Resource auth="Container"
cachePrepStmts="true"
defaultAutoCommit="true"
driverClassName="com.mysql.jdbc.Driver"
factory="com.zaxxer.hikari.HikariJNDIFactory"
global="jdbc/presencedb"
jdbcUrl="jdbc:mysql://192.168.253.128/opensips"
maxWaitMillis="10000" maximumPoolSize="20"
name="jdbc/presencedb"
password="root"
prepStmtCacheSize="30"
prepStmtCacheSqlLimit="500"
removeAbandoned="true"
removeAbandonedTimeout="300"
type="javax.sql.DataSource"
username="root"/>
</Context>
Connection class
public class DAOConnectionFactory {
private static final org.slf4j.Logger logger = LoggerFactory.getLogger(DAOConnectionFactory.class);
private static final String databaseJNDI = "jdbc/presencedb";
private static DataSource dataSource = null;
private DAOConnectionFactory(){}
static {
try {
dataSource = (DataSource) new InitialContext().lookup("java:comp/env/" + databaseJNDI);
} catch (NamingException e) {
logger.error("Error while creating datasource.", e);
}
}
protected static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
protected static void closeConnection(Connection connection, Statement statement, ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
logger.error("Error while closing resultset.", e);
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
logger.error("Error while closing statement.", e);
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
logger.error("Error while closing connection.", e);
}
}
}
}
Exposed REST
@Path("/watcher")
public class WatcherService {
private static final org.slf4j.Logger logger = LoggerFactory.getLogger(WatcherService.class);
@GET
@Path("/presentity/{presentityURI}")
@Produces("application/json")
public Response getWatcherForPresentityByStatus(@Context UriInfo uriInfo) {
MultivaluedMap<String, String> queryParameters = uriInfo.getQueryParameters();
MultivaluedMap<String, String> pathParameters = uriInfo.getPathParameters();
List<Watchers> watchersList;
GenericEntity< List< Watchers>> entity;
try {
if (uriInfo.getQueryParameters().containsKey("status") && uriInfo.getQueryParameters().containsKey("event")) {
watchersList = WatcherDAO.findByPresentityAndStatus(queryParameters, pathParameters);
} else {
watchersList = WatcherDAO.findByPresentityAndEvent(queryParameters, pathParameters);
}
Response.ResponseBuilder response;
if (!watchersList.isEmpty()) {
entity = new GenericEntity<List<Watchers>>(watchersList) {
};
response = Response.ok(entity);
} else {
response = Response.status(Response.Status.NOT_FOUND).entity("Requested resource could not be found.");
}
return response.build();
} catch (Exception e) {
logger.error("Error while fetching watcher details for presentity {}.", pathParameters.getFirst("presentityURI"), e);
return Response.status(500).entity("Server was unable to process the request.").build();
}
}
}
Interface to DB
public class WatcherDAO {
private static final Logger logger = LoggerFactory.getLogger(WatcherDAO.class);
private static final String SELECT_BY_PRES_AND_STATUS = "select watcher_username,watcher_domain from watchers where presentity_uri=? AND event=? AND status=?";
//Watcher Table: Primary key = id.
//Unique index: presentity_uri,watcher_username,event,watcher_domain
public static List<Watchers> findByPresentityAndStatus(MultivaluedMap<String, String> queryParameters, MultivaluedMap<String, String> pathParameters) throws SQLException {
Connection connection = null;
PreparedStatement preparedStatement = null;
int index = 0;
ResultSet rs = null;
try {
connection = DAOConnectionFactory.getConnection();
preparedStatement = connection.prepareStatement(SELECT_BY_PRES_AND_STATUS);
preparedStatement.setObject(++index, pathParameters.getFirst("presentityURI"));
preparedStatement.setObject(++index, queryParameters.getFirst("event"));
preparedStatement.setObject(++index, queryParameters.getFirst("status"));
rs = preparedStatement.executeQuery();
Watchers watchers;
List<Watchers> watchersList = new ArrayList<>();
while (rs.next()) {
watchers = new Watchers();
watchers.setWatcherUsername(rs.getString(1));
watchers.setWatcherDomain(rs.getString(2));
watchersList.add(watchers);
}
logger.debug("Query returned {} results.", watchersList.size());
return watchersList;
} catch (SQLException ex) {
logger.error("Error while fetching watcher for presentity {}.", pathParameters.getFirst("presentityURI"));
throw ex;
} catch (Exception ex) {
logger.error("Error while fetching watcher for presentity {}.", pathParameters.getFirst("presentityURI"));
throw ex;
} finally {
DAOConnectionFactory.closeConnection(connection, preparedStatement, rs);
}
}
}
