package cars; import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import javax.annotation.Resource; import javax.enterprise.context.ApplicationScoped; import javax.inject.Named; import javax.sql.DataSource; import sql.SQL; @Named(value = "database") @ApplicationScoped public class Database { @Resource(name = "jdbc/cars") private DataSource carsSource; private List<String> buildList(ResultSet resultSet, String columnName) throws SQLException { List<String> list = new ArrayList<String>(); while (resultSet.next()) { list.add(resultSet.getString(columnName)); } return list; } private Car buildCar(ResultSet resultSet) throws SQLException { resultSet.next(); String vin = resultSet.getString("VIN"); String manufacturer = resultSet.getString("manufacturer"); String model = resultSet.getString("model"); return new Car(vin, manufacturer, model); } public Database() { } public List<String> getVinList() throws SQLException, IOException { Connection carsConnection = carsSource.getConnection(); try { Statement statement = carsConnection.createStatement(); ResultSet resultSet = statement.executeQuery(SQL.getSQL("vin-query")); return buildList(resultSet, "VIN"); } finally { carsConnection.close(); } } public Car getCar(String vin) throws SQLException, IOException{ String query = SQL.getSQL("car-query"); Connection carsConnection = carsSource.getConnection(); try { PreparedStatement statement = carsConnection.prepareStatement(query); statement.setString(1, vin); ResultSet resultSet = statement.executeQuery(); return buildCar(resultSet); } finally { carsConnection.close(); } } public List<String> getOwnerList(String vin) throws SQLException, IOException { String query = SQL.getSQL("owner-query"); Connection carsConnection = carsSource.getConnection(); try { PreparedStatement statement = carsConnection.prepareStatement(query); statement.setString(1, vin); ResultSet resultSet = statement.executeQuery(); return buildList(resultSet, "name"); } finally { carsConnection.close(); } } }
The Database class defines an application scoped database bean. It accesses a GlassFish database connection pool resource through the resource injected carsSource variable. Like other injected variables, programmers should never assign a value to this variable. They should only send messages to it. Also the variable should not be accessed in any constructor.
The Database class defines a read-only vinList property. This list is displayed in the index.xhtml page. The user selects an item from this list to provide data for a database query.
The getter for the vinList property is a good template for working with the JDBC Statement class. This class is suitable for database access with SQL statements that have no parameters.
The getCar() and getOwnerList() methods are used by the ownerQuery bean to set up its properties that are used in the response to a user query. Both of these methods throw exceptions, letting the ownerQuery client bean decide how to deal with the exception. Request scoped beans generally have more knowledge of the context in which an exception occurs. Thus they are better suited to deal with the exceptions.
The code in the getCar() and getOwnerList() methods is a good template for working with the JDBC PreparedStatement class. This class is required for database access with SQL statements that have parameters. These parameters appear in the SQL statement as the question mark ("?") character. They are numbered starting with 1. The number is used as the first argument in the statement set methods.