Pasted details of the project are below. For a neater look, here is the original
ID: 3766903 • Letter: P
Question
Pasted details of the project are below. For a neater look, here is the original word document : http://www.filedropper.com/project04-2
Here is the MurachDBCreate.sql: http://www.filedropper.com/murachdbcreate
Display Customer Invoices Report
Console:
Welcome to the Customer Invoices Report
frankjones@yahoo.com 10504M 11/18/04 $99.00
frankjones@yahoo.com 10501M 10/25/04 $59.50
johnsmith@hotmail.com 10505M 11/18/04 $297.50
johnsmith@hotmail.com 10500M 10/25/04 $495.00
seagreen@levi.com 10502M 10/25/04 $99.00
wendyk@warners.com 10503M 10/25/04 $112.00
Operation
This application connects to a database and displays a list of all customers and their invoices. Each line in this report includes the customer’s email address, the invoice number, the invoice date, and the invoice total.
Specifications
Create a Derby database named MurachDB that contains the necessary data. To do that, you can Use the SQL script stored in the MurachDBCreate.sql file. (can be found here: http://www.filedropper.com/murachdbcreate)
Create a class named CustomerInvoiceApp that connects to the database, gets a forward-only, read-only result set that contains the required data, and prints the data in this result set to the console. The rows in the result set should be sorted by the EmailAddress column.
Use a prepared statement to retrieve the data.
Use spaces to align the customer data in columns on the console. To do that, you can create a utility class named StringUtils that has a method that adds the necessary spaces to a string to reach a specified length.
If the application encounters any exceptions, it should print them to the console.
When the application finishes, it should close the objects for the result set, the prepared statement, and the database connection.
Explanation / Answer
Database connections
Holding a database connection open consumes resources on the database; it uses memory and databases are configured to have a maximum number of connections, so you increase to likelihood of running out of connections. Also the state of the session is maintained, so you can run into trouble with locks being accidentally held beyond their intended scope.
On the positive side, prepared statements stay compiled and ready for use, so if you code and use your SQL correctly, you can gain significant performance benefits from reusing prepared statements. However, doing so may complicate your code and care is advised.
Also, obtaining a connection is quite expensive, so that's why connection pools exist. These leave the connections open, but the client gets connections, uses them, then releases them back to the pool when done.
Result set
Holding result sets open will also hold certain locks open if you don't commit (which closes then result set), thus depending on your application, you can quickly hit deadlocks or severe liveliness issues. Regardless of whether you hold connections open, always close your result sets as soon as possible to release as much resource back to the database as you can.
But what if you can’t use Spring, or it’s just overkill to use it? That’s fine, but it seems that people get confused about how to structure their code so that their ‘finally’ blocks work correctly under every circumstance. And this can result in unexpected behavior in your programs. Here’s one example that I see quite often:
Connection connection;
try {
connection = dataSource.getConnection();
// Do stuff with connection.
} finally {
connection.close();
}
So what’s wrong with this code? Well, if the call to dataSource.getConnection() fails, an exception will be thrown. The ‘finally’ block will still execute however, but the ‘connection’ object will be null. This will result in a NullPointerException being thrown instead of the original exception. So the original exception will be masked by the NullPointerException. ‘Who cares?’ you might say. Well, I care if I’m investigating a production problem at 3am and have just wasted five bleary-eyed minutes wondering why a NullPointerException occurred when the underlying cause was really a problem getting a database connection.
“Easy”, you say, “just do something like this:”
Connection connection;
try {
connection = dataSource.getConnection();
// Do stuff with connection.
} finally {
if (connection != null) connection.close();
}
That’ s great, but what if you’ve got a statement that you need to close too? “No worries”, you say, “the code needs to morph into something like this:”
Connection connection;
Statement statement;
try {
connection = dataSource.getConnection();
statement=connection.createStatement();
// Do stuff with statement.
} finally {
if (statement != null) statement.close();
if (connection != null) connection.close();
}
Well, what if the call to statement.close() fails? It’ll skip closing the connection. Whilst I admit that it’s unlikely that a call to statement.close() would fail, my point is that these are just a few of the wide number of possible combinations of things that can happen in your code. Wouldn’t it be easier if you didn’t have to worry about all these possibilities?
Well, fortunately, there’s a simple strategy that you can use to always make sure that these sorts of objects get closed down in an orderly, watertight fashion. It goes like this: