A Short Introduction to Databases and JDBC


Andy Meneely, Lauren Hayward, Sarah Heckman, and Laurie Williams [Contact Authors]
CSC 326 - Software Engineering
Department of Computer Science
North Carolina State University

Back to Software Engineering Tutorials


0.0 Contents
1.0 What are Databases and Queries?
2.0 Getting Started
3.0 Select Queries
4.0 Insert Queries
5.0 Update Queries
6.0 Exercise
7.0 Resources


1.0 What are Databases and Queries?

A relational database is a system for storing and organizing large amounts of data. Databases contain tables, which are made of tuples (or rows) and fields (or columns). This semester we will be writing database queries for a MySQL database. Queries are used to push and pull information from the database. Queries are made up of keywords (like Select, Insert, Update, From, Where) and table and field names.

Java interacts with databases with the built-in API known as JDBC, Java Database Connectivity. Using Java, the programmer sends the query in the form of a string to the database, which is then executed by the database system.

Although CoffeeMaker uses JDBC, one can also interact with a database directly using a database client. Two such database clients include phpMyAdmin (for remote databases) and MySQL Workbench (for local databases). phpMyAdmin is web-based software that handles the administration of MySQL database systems. MySQL Workbench is an application that handles the administration of local MySQL database systems. Both database clients provide a way for database administrators to manage their databases through both GUI tools and manual MySQL commands. You can also interact with the database through the command line. See the documentation for your database distribution for information about connecting to the command line database.

Top | Contents
2.0 Getting Started

Download CoffeeMaker_WebDB.zip for this tutorial and import the project into your workspace (see Eclipse Import/Export tutorial for details.) There are three tables in the CoffeeMaker database: recipe_book, inventory, and coffee_purchased. The database tables are defined in the class edu.ncsu.csc326.coffeemaker.db.CreateDBTables. These tables are created when you start the CoffeeMaker application as part of the CoffeeMaker constructor.

Configure META-INF/context.xml to point to your database. Use the database username and password that you are using for your iTrust database. Note that the url attribute is assigned the string "jdbc:mysql://localhost:3306/tutorials?createDatabaseIfNotExist=true". This will create the database called tutorials for you if it does not already exist. If you would prefer to create and use a different database, create the database in MySQL and update context.xml accordingly.

All of the SQL queries that you run from your web application may be tested in MySQL. You can use MySQL Workbench for testing queries.

Before continuing with the tutorial, add your CoffeeMaker_WebDB application to your Tomcat server and (re)start the server. Point your browser to http://localhost:8080/CoffeeMaker_WebDB and ensure that the menu is displayed.

Top | Contents
3.0 Select Queries

Use SELECT when you want to read a set of rows from a table or several tables.

3.1 Writing a Select query inside a database client

Before running our queries from a Java program, it is sometimes helpful to begin writing your query inside a database client. This allows you to check your query’s structure and make sure your query is returning the correct results.

Lets begin by returning all rows from the recipe_book table.

SELECT *
FROM recipe_book

recipe_book is the name of the table you are selecting from and * indicates that you want to select all fields or columns. SELECT and FROM are SQL keywords. Case doesn't matter in this configuration of MySQL, but the keywords are capitalized for emphasis.

Now suppose that you want to narrow your query to only return the recipes that have a price greater than 50. Our new query would be:

SELECT *
FROM recipe_book
WHERE price > 50

The WHERE clause is used to add additional conditions. In this case, we used it to narrow down the recipes selected by price.

So far we have been able to return a list of all fields inside the recipe_book table. What if we wanted all of the fields of recipes that have been purchased where the price is greater than 50? Then we would use a JOIN. (Many different types of joins exist, but for this tutorial we’ll only be dealing with “JOIN.”) When using a JOIN, be sure to indicate what fields in each table should match. In this case, we will join on the name field.

SELECT *
FROM recipe_book JOIN coffee_purchased
WHERE recipe_book.name=coffee_purchased.name AND price > 50

We used the keyword AND since we had multiple conditions in our WHERE clause. Also, because the name field is the same in both the recipe_book and coffee_purchased tables, we identified the tables and the field names and separated them with a ..

Lets narrow our query further so that instead of returning all fields in the two tables, we only return the recipe name and the timestamp of purchase.

SELECT recipe_book.name, purchasedDate
FROM recipe_book JOIN coffee_purchased
WHERE recipe_book.name=coffee_purchased.name AND price > 50

We used commas to separate the fields but did not use a comma after the last field.

Finally, lets consider a case where we want to know when Coffee was purchased.

SELECT recipe_book.name, purchasedDate
FROM recipe_book JOIN coffee_purchased
WHERE recipe_book.name=coffee_purchased.name AND recipe_book.name='Coffee'

In this case we are looking for a specific string. In MySQL, single quotes (') are used instead of double quotes (") to surround strings.

3.2 Writing a Select query inside CoffeeMaker

In this section, we will examine the method getRecipe() within RecipeBookDB. An action class could call this method. Let's examine the method line by line. The purpose of this method is to return a Recipe that holds the recipe of ingredients associated with the specified recipe name.

public static Recipe getRecipe(String name) throws RecipeException { }

CoffeeMaker uses the DBConnection class to generate the Connection object and associated Statements. First we'll create the DBConnection object and then the Connection.

DBConnection db = new DBConnection();
Connection conn = null;

Next, create a new PreparedStatement. PreparedStatements are a secure way to write database queries, and we strongly recommend you use them. What makes PreparedStatements so secure is that the structure of the query is set before user input is accepted.

PreparedStatement stmt = null;

We also create a Recipe object that will be returned from the getRecipe() method.

Recipe r = null;

Whenever an error occurs when accessing the database, a SQLException is thrown, so we open a try block. The reason that we left all of the above objects null is so that we can access them from within both the try and catch blocks for this method.

try {

We initialize conn by calling the getConnection() method in the DBConnection class.

conn = db.getConnection();

Next we will create a query that selects all fields from the recipe_book table where the recipe name is the designated name. The “?” indicates that user input will be used to designate what recipe name should go inside the query. For every “?”, the user input must be set. "?" is used in PreparedStatements as a place holder for user input. The user value is sanitized before concatenation to the rest of the SQL query. Using regular String concatenation doesn't sanitize input for characters that may allow for SQL Injection attacks.

stmt = conn.prepareStatement("SELECT * FROM recipe_book WHERE name=?");

Next, we set what user input will be used to replace our only “?”. The appropriate set method must be called based on the data type you are inserting into. The field name is of type varchar which is a String. Therefore, we use the setString() method to set the value. The parameter “1” indicates that we are going to replace the first “?”. The second parameter specifies the value to set in the query. Although we have initialized our PreparedStatement, the query inside it has not been executed.

stmt.setString(1, name);

Now, we actually execute the query, which returns a ResultSet. ResultSets are a list of results from your query.

ResultSet rs = stmt.executeQuery();

Note: A ResultSet's cursor is set to before the first row, so next() must be called to obtain the first row.

if (rs.next()) {

If our query returned any results, code inside the if statement will be executed. We will create a new Recipe object and populate it with the values returned from the query.

r = new Recipe();
r.setName(name);
r.setPrice(rs.getString("price"));
r.setAmtCoffee(rs.getString("amtCoffee"));
r.setAmtMilk(rs.getString("amtMilk"));
r.setAmtSugar(rs.getString("amtSugar"));
r.setAmtChocolate(rs.getString("amtChocolate"));

The method will either return our new recipe object or null depending on the outcome of the query.

Lets do some error handling. Whenever something goes wrong with the database, a SQLException will be thrown. We'll catch the SQLException and print the error message to the console.

catch (SQLException e) {
   e.printStackTrace();

Lastly, it is imperative that we close the database connection, so we include the code to do so in a finally block. No matter what happens in a method (including throwing an exception), a finally block will always be executed.

} finally {
   DBConnection.closeConnection(conn, stmt);
}
return r;

Put together, that method looks like this:

public static Recipe getRecipe(String name) throws RecipeException {
DBConnection db = new DBConnection();
Connection conn = null;
PreparedStatement stmt = null;
Recipe r = null;
try {
conn = db.getConnection();
stmt = conn.prepareStatement("SELECT * FROM recipe_book WHERE name=?");
stmt.setString(1, name);
ResultSet rs = stmt.executeQuery();
//Only one result b/c name is primary key
if (rs.next()) {
r = new Recipe();
r.setName(name);
r.setPrice(rs.getString("price"));
r.setAmtCoffee(rs.getString("amtCoffee"));
r.setAmtMilk(rs.getString("amtMilk"));
r.setAmtSugar(rs.getString("amtSugar"));
r.setAmtChocolate(rs.getString("amtChocolate"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBConnection.closeConnection(conn, stmt);
}
return r;
}
Top | Contents

4.0 Insert Queries

Next, we will learn how to insert data into a table. To do this, we will use the keywords INSERT INTO and VALUES.

4.1 Writing an Insert query inside a database client

We want to create a new recipe for the CoffeeMaker and add it to the recipe_book table.

Let's take a look at the recipe_book table using a database client. It has 6 fields: name, price, amtCoffee, amtMilk, amtSugar, amtChocolate. To add a new recipe for a Latte, our query would like this:

INSERT INTO recipe_book (name, price, amtCoffee, amtMilk, amtSugar, amtChocolate)
VALUES ('Latte', '60', '3', '2', '1', '0')

4.2 Writing an INSERT query inside CoffeeMaker

If we want to add a new recipe, we can use the addRecipe() method in the RecipeBookDB class. This method takes the recipe to add to the database as a parameter and returns true if the recipe was added and false otherwise.

public static boolean addRecipe(Recipe r) {

The structure of addRecipe() is very similar to the structure of getRecipe() in Section 3.2.

DBConnection db = new DBConnection();
Connection conn = null;
PreparedStatement stmt = null;
boolean recipeAdded = false;
try {

Just as before, we initialize conn and stmt. Notice we are using the same query we created in the previous section. However, this time we left out the listing of fields after the table name. This is because we are inserting data into all fields of the table. If we were inserting data into a subset of the table's fields, then we have to use the field listing after the table name in the query.

conn = db.getConnection();
stmt = conn.prepareStatement("INSERT INTO recipe_book VALUES(?,?,?,?,?,?)");

We set the values of the “?”s to be values designated by the user.

stmt.setString(1, r.getName());
stmt.setInt(2, r.getPrice());
stmt.setInt(3, r.getAmtCoffee());
stmt.setInt(4, r.getAmtMilk());
stmt.setInt(5, r.getAmtSugar());
stmt.setInt(6, r.getAmtChocolate());

Note: When doing an INSERT or UPDATE, use executeUpdate() as shown below. When using SELECT, use executeQuery().

int updated = stmt.executeUpdate();

The executeUpdate() method returns the number of rows updated in the recipe_book table and 0 if the query doesn't return anything. In our case, we expect one row to be updated with the newly added recipe. If one row is updated we want to notify the CoffeeMaker that the recipe was added successfully.

if (updated == 1) {
   recipeAdded = true;
}

Finally, we'll handle errors, close the connection, and return the status of the update.

} catch (SQLException e) {
   e.printStackTrace();
} finally {
   DBConnection.closeConnection(conn, stmt);
}
return recipeAdded;

Put together, our code looks like this:

public static boolean addRecipe(Recipe r) {
DBConnection db = new DBConnection();
Connection conn = null;
PreparedStatement stmt = null;
boolean recipeAdded = false;
try {
conn = db.getConnection();
stmt = conn.prepareStatement("INSERT INTO recipe_book VALUES(?,?,?,?,?,?)");
stmt.setString(1, r.getName());
stmt.setInt(2, r.getPrice());
stmt.setInt(3, r.getAmtCoffee());
stmt.setInt(4, r.getAmtMilk());
stmt.setInt(5, r.getAmtSugar());
stmt.setInt(6, r.getAmtChocolate());
int updated = stmt.executeUpdate();
if (updated == 1) {
recipeAdded = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBConnection.closeConnection(conn, stmt);
}
return recipeAdded;
}
Top | Contents

5.0 Update Queries

Sometimes we will need to edit rows in the database; in this case, we will use the UPDATE keyword.

5.1 Writing an Update query inside a database client

Suppose we discover we accidentally created the wrong recipe. We can edit the recipe using the UPDATE command. The UPDATE command will update all tuples in the table which meet the criteria in the WHERE clause with the values specified in the the SET clause.

UPDATE recipe_book
SET price=?, amtCoffee=?, amtMilk=?, amtSugar=?, amtChocolate=?
WHERE name=?

5.2 Writing an Update query inside CoffeeMaker

If we want to edit an existing recipe, we can use the editRecipe() method in the RecipeBookDB class. The editRecipe() method has two parameters: the name of the recipe to edit and the edited recipe.

public static String editRecipe(String name, Recipe r) {

Setting up the connection and prepared statement are identical to the methods we examined in the previous sections.

DBConnection db = new DBConnection();
Connection conn = null;
PreparedStatement stmt = null;
try {
   conn = db.getConnection();

Next, you’ll see the update query with question marks for the description and code.

stmt = conn.prepareStatement("UPDATE recipe_book SET price=?, amtCoffee=?, amtMilk=?, amtSugar=?, amtChocolate=? WHERE name=?");

We'll set the values for description and code that we pull from the bean.

stmt.setInt(1, r.getPrice());
stmt.setInt(2, r.getAmtCoffee());
stmt.setInt(3, r.getAmtMilk());
stmt.setInt(4, r.getAmtSugar());
stmt.setInt(5, r.getAmtChocolate());
stmt.setString(6, name);

Since this is an Update query, we call executeUpdate, which will execute our query and return how many rows in the database were updated.

int result = stmt.executeUpdate();
if (result == 0) { //nothing was updated
   name = null;
}

Lastly, we catch errors and close the connection.

} catch (SQLException e) {
   e.printStackTrace();
} finally {
   DBConnection.closeConnection(conn, stmt);
}
return name;

Put together, the code looks like this:

public static String editRecipe(String name, Recipe r) {
DBConnection db = new DBConnection();
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = db.getConnection();
stmt = conn.prepareStatement("UPDATE recipe_book SET price=?, amtCoffee=?, amtMilk=?, amtSugar=?, amtChocolate=? WHERE name=?");
stmt.setInt(1, r.getPrice());
stmt.setInt(2, r.getAmtCoffee());
stmt.setInt(3, r.getAmtMilk());
stmt.setInt(4, r.getAmtSugar());
stmt.setInt(5, r.getAmtChocolate());
stmt.setString(6, name);
int result = stmt.executeUpdate();
if (result == 0) { //nothing was updated
name = null;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBConnection.closeConnection(conn, stmt);
}
return name;
}
Top | Contents

6.0 Exercise

You will implement the AddInventory user story, which will use the database for maintaining inventory. There are two ways to solve the problem. The first is to maintain the inventory table with only one row and update the inventory as needed. The other way is to create a row each time inventory is added and used and aggregate each of the inventories. An example query to aggregate values is below.

SELECT sum(coffee) from inventory

The main steps for completion are:

  • Copy the add_inventory.jsp file you completed as part of the JSP tutorial. If you have not completed the JSP tutorial, you will want to complete add_inventory.jsp so you can test your solution.
  • Create a new class in edu.ncsu.csc326.coffeemaker.db called InventoryDB.java. Create three methods addInventory(), useInventory(), and checkInventory(). You may create additional helper methods. Use the provided PurchasedCoffeeDB.java and RecipeBookDB.java classes as guidelines for writing your InventoryDB class.
  • Update edu.ncsu.csc326.coffeemaker.Inventory class to use the database instead of the static fields. To get started with identifying which functionality you need to update, we recommend deleting the fields. The compilation errors will show you the areas of the code that should interact with the InventoryDB class.

Refer to the assignment instructions for details on submission.

Top | Contents

7.0 Resources

MySQL Reference Manual

phpMyAdmin

MySQL Workbench

Top | Contents

Back to Software Engineering Tutorials
A Short Introduction to Databases and JDBC ©2006-20013 North Carolina State University, Laurie Williams, Andy Meneely, Lauren Hayward,
and Sarah Heckman
Email the authors with any questions or comments about this tutorial.
Last Updated: Tuesday, August 27, 2013 11:17 AM