Overview of This Lesson

Refresh this page because I am probably still making changes to it.

In the previous lesson we learned how to create a Database Configuration class to configure a database connection, and a Database Access class to perform database operations. We did a very basic INSERT query, but it wasn't the kind of code we'd usually use in a database-driven application. Ideally, we want to be able to pass dynamic data into a method that can insert a record with that data. To do that safely, we need to use a parameterized query.

After learning about parameterized queries, we'll learn how to read a set of records from the database using SELECT queries.


Go through Review of Maps if you didn't get to do much with HashMaps in term 2 Java.

Resources for this Lesson

You should also read over and bookmark the SQL Injection Cheat Sheet from OWASP (Open Web Application Security Project): it has some excellent information about SQL Injection - much more than we'll ever cover in this class.

Here's a list of API links used throughout this lesson:

Parameterized Queries

In the previous lesson we had the following method in our database access class:

public void addPlayer() {

    String sql = "INSERT INTO players (firstName, lastName, number, team)"
            + " VALUES ('Carey', 'Price', 31, 'MTL');";

    jdbc.update(sql, new HashMap());

The problem with this method is that we're hard coding the values for the row we're trying to add to the players table. It would be better if wwe could pass a Player object into our method, instead:

public void addPlayer(Player player) {

That way, a person could take a player object that was created from form inputs and insert that player as a row into the table.

How would we then create the SQL Query? You might assume we would do something such as:

String sql = "INSERT INTO players (firstName, lastName, number, team)"
    + " VALUES ('" + player.getFirstName() + "', '" 
    + player.getLastName() + "', " + player.getNumber()
    + ", '" + player.getTeam() + "');";

Unfortunately this kind of code leaves you open to SQL Injection Attacks.

SQL Injection occurs when a user attemps to inject malicious SQL code via your input fields. For example, imagine you have a form that allows a user to search for a player by name, and the user entered the following for the search field:

whatever'; DELETE FROM players;

Assuming your query is set as:

String sql = "SELECT * FROM players WHERE lastName LIKE '%" + searchValue + "%';";

Then the query string you've built will end up as:

SELECT * FROM players WHERE firstName LIKE '%whatever'; DELETE FROM players; %';

When this is executed by the SQL server, it will do the SELECT statement, then it will execute the DELETE statement, which deletes all the records from the players table!

Even if a user didn't know the name of the players table, they could still do harm. Imagine you had a table of customer contact information and encrypted passwords and you had a form that allowed a customer to view and edit their contact information. Your query to look up a customer might be:

String sql = "SELECT * FROM customers WHERE username='" + userName + "';";

If your customer enters their ID or user name on a form as:

' OR 1;

Your query becomes:

SELECT * FROM customers WHERE username='' OR 1';

Since the expression 1 is always true, this statement will select all the columns from all the records in the customer's table and return them. Now the person who entered that value has access to all your customer's contact information and encrypted password values.

To prevent SQL injection attacks, you need to make sure any special characters that are normally used in SQL statements are escaped. This would include the single- and double-quotes and a variety of other special characters. This can be done easily by using parameterized queries.

A parameterized query is an SQL query that uses parameter variables as part of the actual statement. The programmer then "plugs in" the actual values for the parameters using a special class that maps each data value to one of the parameters. The class's methods escape any special characters, so that injection attacks become impossible. Additionally, many database engines examine queries and their parameters before those queries are executed just to be sure that no SQL injection can occur.

When you create a parameterized query string, you use markers in place of the variable values you want your query to contain. Markers are assigned a unique name, so we call them "named parameters". For example:

String sql = "INSERT INTO teams (id, city, name) VALUES (:id, :city, :name);";

Here there are 3 named parameters: they all start with the colon character so that it's clear that these are markers and not literal characters in the query. Note that you don't need to enclose string parameters in quotes: the methods we use will do that for you!

We want the program to replace those markers with actual values. For example, if you wanted to insert a new row into the teams table where the id was "FOO", the city was "Fooville" and the name of the team was "Biscuits". You would want to replace :id with "Foo", replace :city with "Fooville", and replace :name with "Biscuits".

Once you've created your query that includes named parameters, you then use a MapSqlParameterSource to map each parameter to an actual value. The MapSqlParameterSource class is just like a HashMap, but it's made especially for working with SQL query parameters and only uses Strings as the Key in each entry.

To map actual values to each parameter, you construct a new instance of the MapSqlParameterSource class and use its addValue() method to add an actual value to each parameter:

MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue(paramName, replaceValue);

MapSqlParameterSource contains entries of key-value pairs: each key is a named parameter as a String, and each value is the actual value you want to plug into, or map, to that named parameter.

For example, to add our team from Fooville, we could say:

String sql = "INSERT INTO teams (id, city, name) "
        + "VALUES (:id, :city, :name);";
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("id", "FOO");
params.addValue("city", "Fooville");
params.addValue("name", "Biscuits");

The addValue() method always has to accept a String for the first argument, which represents the name of the parameter, and any Object as the second parameter, which represents the value to plug into that parameter.

The addValue() method returns another MapSqlParameterSource, so you can actually chain these together:

params.addValue("id", "FOO").addValue("city", "Fooville")
    .addValue("name", "Biscuits");

The addValue() method and the JDBC templates are also really good about data types. If you are inserting an Inventory record with an integer quantity and a floating point price, it's fine to just say:

String sql = "INSERT INTO inventory (itemName, price, quantity) "
        + "VALUES (:name, :price, :qty);";
MapSqlParamterSource params = new MapSqlParameterSource();
params.addValue("itemName", "Kibble, 10kg, Tuna").addValue("price", 14.95).addValue("qty", 45);

Lastly, once you've mapped each parameter in your query to an actual value, you invoke the appropriate method to execute the query, and pass it the map of parameters:

jdbc.update(sql, params);


Let's try it all out with an exercise. Set up a new project with dependencies for Spring Web, dev tools, Lombok, Thymeleaf, H2 Database, and Spring Data JDBC. Then add the necessary properties to your application properties file that allows you to connect to your H2 database.

Copy the SQL from your previous lesson's project that creates the players table and the teams table, and populates the teams table.

Copy the most recent version of the Player class (the one with the String team member) and add an int data member for an ID field: this will correspond to the auto-increment ID in the players table. You'll need to add an additional constructor: you currently have a no-args and all-args constructor (created by Lombok) so manually another constructor that takes all args except the id (we might need this in this or a later exercise).

Add a form (and a handler method that loads the form - you probably want to bind your form to a Player instance) that allows a user to input the first and last name of the player, the player's number, and the players team. For now, use a text field for the team id (e.g. "CGY" or "TOR"), but later we'll go back to using a drop-down list.

Add a DatabaseConfig and a DatabaseAccess class to your .database package.

In the DatabaseAccess class, add a addPlayer() method that accepts a Player object as an argument. The method should create a parameterized query that inserts that player into the players table. Execute the query. The addPlayer() method doesn't need to return anything.

Add a handler method for form processing:

  1. Accept the newly added Player from the input form as a parameter.
  2. Call the DatabaseAccess method addPlayer() and pass it the player object.
  3. Load an output page that says "record added successfully".

Run your program and then try it out. After you get your output page, go to the H2-Console and make sure your player was added to the players table. You can even go back to the form and try adding a second player - make sure it was added to the players table.

Select Queries

So far we've written some code that performs INSERT queries on our database tables. We can also run SELECT queries and retrieve a collection of rows from our database tables. For example, we should be able to retrieve the list of teams so we can populate the drop-down list on our add-player form, and we might also want to retrieve a list of players to display in a table.

First, we need a bean for Teams. Note that you could definitely use the Teams enumeration from previous examples, and in fact, it would be an excellent exercise to redo today's entire example project using the Teams enum instead. But for this particular demo, I'm going to use a Bean because it fits better with what I want to show you.

The Team Bean

We can now add a method to the database access class to retrieve each team in the teams table. Since the method is going to return a list of team objects, we need to use List<Team> as the return type:

public List<Team> getTeams() {

    String sql = "SELECT * FROM teams ORDER BY city;";
    ArrayList<Team> teams = new ArrayList<Team>();


Note that we don't need to use CopyOnWriteArrayList() because we're only reading the teams, we're not writing to the teams list.

The next thing we want to do is run our query. We have no named parameters for this one, so we don't need to worry about mapping any values to any parameters.

We execute a SELECT query using the JDBC Template queryForList(sqlString, paramMap) method. There are other query methods that we'll discuss in later lessons, but this one is perfect for now.

The queryForList() method accepts the SQL SELECT query to execute, and a map of any named parameters. We don't have any parameters in this query, so we'll use an empty HashMap.

The queryForList() method does return something interesting: a List<> of Map<K, V> objects!

Each list element contains a Map where the keys are the field/column names and the values are the field/column values for a particular row. So each List element is a row from the table. Each row is a Map of entries where each entry is a column name and column value.

a box representing a list element, containing 3 map.entry objects as key-value pairs
One element in the returned List<> contains a set of Map<K, V> entries

Therefore, when we invoke queryForList(), the return value needs to be stored in a List<E> where the generic type <E> is actually a Map<K, V>. For the map, each entry is a key-value pair where the type <K> is a String containing the column name, and type <V> is an Object containing the column value. Object is used for the value because the column could be anything: String, integer, floating point, Date, etc.

public List<Team> getTeams() {

    String sql = "SELECT * FROM teams ORDER BY city;";
    ArrayList<Team> teams = new ArrayList<Team>();

    // a list of map entries
    List<Map<String, Object>> rows = jdbc.queryForList(sql, new HashMap());


The syntax definitely looks interesting! But it shouldn't be too hard to understand: For the teams table, if there are 7 records, then there are 7 elements in the List<> called rows.

Each element in rows is a Map<K, V> where K contains the name of the column and V contains the value of that column. So the first element of List<> is the Map<String, Object> that contains:

Key: "id", Value: "CGY"
Key: "city", Value: "Calgary"
Key: "name", Value: "Flames"

So you can imagine that your List<> rows contains 7 elements, and each element is a Map with three entries in it: on entry for the id column and value, one entry for the city column and value, and a third entry for the name column and value.

a set of list elements, each containing a map with 3 entries; one list element for each team
The List would contain 7 elements, each element contains a Map with 3 entries.

Once we have our List of Maps, we want to iterate through each List element and get the values from each field:

public List<Team> getTeams() {

    String sql = "SELECT * FROM teams ORDER BY city;";
    ArrayList<Team> teams = new ArrayList<Team>();

    // a list of map entries
    List<Map<String, Object>> rows = jdbc.queryForList(sql, new HashMap());

    for (Map<String, Object> row : rows) {
        Team team = new Team();

    return teams;  

The get method invoked on a Map element retrieves an entry's value by it's key. In this case, the keys are the column names. The Map's value type is Object, so each value needs to be cast back into its proper data type. For example, if you read an integer field, you would need to cast the Object back into a primitive int or an Integer object, whichever is most appropriate.

Once you've used the values to construct/set a new Team object, we add that team object to the list of teams.

Notice that once we've finished the loop, all the team rows have been added to our teams list, so we can then return that list from the method.

It's a bit of work to populate and return the list, but if you plan ahead when creating your beans or your database tables, you can use a shortcut!!

If your column names in your table match the bean data member names exactly, you can just do this:

public List<Team> getTeams() {

    String sql = "SELECT * FROM teams ORDER BY city;";

    ArrayList<Team> teams = (ArrayList<Team>)jdbc.query(sql, 
            new BeanPropertyRowMapper<Team>(Team.class));
    return teams;

This is a different query method being used here: the query() method being used here accepts an SQL statement as a String and a Row Mapper object. A Row Mapper maps the fields in each returned row to something. In this case, we are passing it a

new BeanPropertyRowMapper<Team>(Team.class)

The BeanPropertyRowMapper<B> maps each row's fields to a specific Bean, which you specify as the argument Team.class in the constructor and also by the concrete type <Team>. So what this really does is it creates a Team object for each row returned by query, by mapping each column with the corresponding data member in the bean, as long as the column name and the data member name are exactly the same. It then adds each newly created Team object to an ArrayList, and returns the final array list of Team objects for us.

Note that you can only use the BeanPropertyRowMapper if the column names of your table exactly match the bean's data member names! For example, the Team bean has data members id, city, and name. Therefore, the Teams table must have columns called id, city, and name.

The query() method returns a List<B> where the generic type <B> is your bean, in this case it's the concrete type <Team. Therefore, our query method executes the SQL query and then takes the rows returned and converts each row into a Team object, and adds each Team object to the List<Team>, which we cast into a ArrayList.

Once you've written the method to retrieve a List of team objects, you can use this to create the OPTION elements of the section list:

  1. In the handler method that loads the form, invoke the getTeams() method.
  2. Store the list returned by getTeams() to the model.
  3. In your form HTML, replace the "team" input field with a selection list, using Thymeleaf iteration on the OPTION elements.
    code for teams drop-down list plus source view on html page; msg wendi if you need this code
    Creating the drop-down list


1. Add a page to your players/teams demo that displays all the players in the players table. Challenge: Make sure you list the name of the team and not the team id value. There are a couple of ways to do this - see if you can figure it out!

2. Create a form that allows a user to enter information about a book (isbn, title, author, price). Allow the user to select a genre from a set of radio buttons. Read the genres from the genres table in the database and use the data to create the radio buttons. When a radio button is selected, the genre id value is used for the book's genre field.

After a book is saved to the books table in the database, go back to the form page and list all of the books in the books table. Display them in an HTML table below the form.

3. a) Add a search page to your Players/Teams demonstration program. Allow the user to search by a value that could appear in a player's first name or last name. Display the search results in any kind of list you like (an actual HTML list, or just in a set of divs, whatever: maybe try something different than a table. Or even better: use the same page that you created in question #1 so you don't have to write the same code again!

Tips and Notes:

3. b) Try the search program again but this time allow the user to search by teams. Display the teams in a set of check boxes. The user can search for players in any number of teams (e.g. they might select Montreal and Toronto). List all the players found on all the selected teams.