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.
Pre-Requisites
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:
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:
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);
paramName is a string containing the
name of the parameter marker you want to replace
replaceValue is the actual value you
want to replace the parameter marker with.
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:
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:
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);
Exercise
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:
Accept the newly added Player from the input form as
a parameter.
Call the DatabaseAccess method addPlayer() and pass it
the player object.
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.
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.
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:
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.
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();
team.setId((String)(row.get("id")));
team.setCity((String)(row.get("city")));
team.setName((String)(row.get("name")));
teams.add(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:
In the handler method that loads the form,
invoke the getTeams() method.
Store the list returned by getTeams() to the model.
In your form HTML, replace the "team" input
field with a selection list, using Thymeleaf
iteration on the OPTION elements.
Creating the drop-down list
Exercises
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:
When using the LIKE keyword, you have to add the %
inside the parameter value when you map the value
to the named parameter. E.g. addValue("columnName", "%" + searchThing + "%")
if you're replacing the :columnName parameter with %searchThing%.
When calling query() with both a row mapper and a parameter
map, the order is ...
well, go look it up!! :D
If you want to do case-insensitive comparisons, add
;COLLATION=ENGLISH STRENGTH PRIMARY
to the end of the database URL in your application.properties file.
Don't forget to use the Thymeleaf attributes where you need
to e.g. th:id not id.
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.
