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

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. phpMyAdmin is web-based software that handles the administration of MySQL database systems. It provides a way for database administators to manage their databases through both GUI tools and manual MySQL commands.

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 . These tables are created when you start the CoffeeMaker application.

Configure META-INF/context.xml to point to your database. If you are registered for CSC 326 @ NCSU, your TA will provide you with your webaddress.

If you have phpMyAdmin, go the the website. In the Database dropdown box in the left navigation pane, select your database. The page will refresh and a list of tables will appear in the left pane. You can click the browse icon next to any table name in order to view the data inside the particular table. In order to execute a query, click the SQL icon near the top of the left pane, type your query, and press “Go.” Your results will be displayed in the right pane.

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

3.1 Writing a Select query inside phpMyAdmin

Before running our queries from a Java program, it is sometimes helpful to begin writing your query inside phpMyAdmin. 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 seperated 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 will be 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.

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;
	}

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 phpMyAdmin

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 phpMyAdmin. 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 iTrust

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;
	}

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 phpMyAdmin

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 iTrust

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;
	}

In the JSP tutorial, you added add_inventory.jsp to complete the AddInventory user story of the CoffeeMaker example. Add your add_inventory.jsp file to CoffeeMaker_WebDB which contains the RecipeBookDB class. Now, you can implement the database functionality for the AddInventory user story. Create three methods addInventory(), useInventory(), and checkInventory().

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

Submit your work. Refer to the assignment instructions on how exactly to do this.

MySQL Reference Manual

phpMyAdmin