-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
1/54
Database Connectivity:SQL and JDBC
Web Programming
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
2/54
Introduction
2Web Programming
Most web applications are connected to some form ofexternal storage in order to drive its dynamic content. Thisstorage often takes the form of a relational database, due toits simplicity and the ease of extracting related data.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
3/54
Introduction
3Web Programming
SQL
Extraction of data from a relational database is accomplishedthrough the use of SQL, or Structured Query Language.
Defines a syntax and several keywords which can be understood bythe database system.
Most relational database systems provide a client program through which suchSQL commands can be entered, and its results displayed to the user. However,web applications cannot interface to the database using these programs.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
4/54
Introduction
4Web Programming
JDBC API
Comes as part of the J2EE platform.
Provides developers a standard, programmatic way of interfacingwith relational database systems.
Allows developers to issue SQL queries and make use of its resultsto generate dynamic content for the end client.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
5/54
Relational Databases
5Web Programming
The storage medium of choice for many web-basedapplications that require dynamic content.
The basic syntax needed to retrieve and manipulate data
that it stores is easy to pick up. Currently, it also has widespread industry support, meaning
that there are plenty of options available, with little to noshortage of technical resources that can be retrieved fromthe Internet.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
6/54
Relational Databases
6Web Programming
Store data as related sets of information.
Relational groupings are expressed as tables.
Each table contains columns that define the properties of each dataset stored within it.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
7/54
Relational Databases: Tables
7Web Programming
Actual tables in a database system are not as simple as ourexample.
Database tables are usually designed with logical
constraints to preserve the consistency of its data. Assignation of data types: each column is defined to be of a specific
data type. The system automatically rejects the insertion of new datasets if one or more of its column entries does not match the datatype specified.
Uniqueness: if a column is defined to be 'unique', the system will not
allow the insertion of a new data set which contains a value alreadyexisting in the system.
Others
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
8/54
SQL Statements
8Web Programming
Operations on relational databases are accomplishedthrough the use of SQL.
There are several types of SQL statements, though only twoof these will be covered in this chapter:
Data Retrieval
Data Manipulation
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
9/54
Data Retrieval
9Web Programming
Focuses on reading data from one or more tables in thedatabase.
Can either be left open-ended to retrieve ALL data sets within aspecified table (or group of tables).
Can be parameterized such that known column values are suppliedand data sets are retrieved such that they satisfy the givenconditions.
Only one SQL statement falls within this type: the SELECTstatement.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
10/54
SELECT Statement
10Web Programming
Used to query the database about information which thedatabase returns as a set of rows:
SELECT column(s) FROM tablename WHERE condition(s)
SELECT, FROM, and WHERE are SQL keywords
columns, tablename, and conditions are values supplied by thedeveloper.
SELECT: Marks the start of the SELECT statement
column(s): The name of the column/s whose value will be retrieved. If more thanone column is to be retrieved, the column names are separated by commas. If all
available columns are to be retrieved, an * (asterisk) is used instead of actualcolumn names.
FROM - Indicates the table from which the data is to be retrieved.
WHERE - An optional keyword that specifies conditions that must be fulfilled bydata entries before they can be included as a result. If more than one conditionis specified, each condition is separated by either an AND or OR keyword.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
11/54
The FROM Clause
11Web Programming
The FROM clause in a SELECT statement defines thetable(s) from which the data set will be gathered
If the data comes from only one table, then that table's name issimply supplied.
If the data that we need comes from more than one table, anoperation called a table join needs to be performed.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
12/54
How to Perform Table Joins?
12Web Programming
By listing all the tables to join, separated by commas
Simplest to do but ranks the lowest in terms of performance.
Performs a Cartesian product on the tables, resulting in a large,unwieldy table.
Example:
Given two tables users, and userdownloads, join is performed by
... FROM users, userdownloads [WHERE ...]
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
13/54
How to Perform Table Joins?
13Web Programming
By using one of several JOIN keywords:
table1 JOIN table2 on condition
Condition specifies which rows on both tables to join together
LEFT JOIN: Performs similar to JOIN, except that all entries intable1 is applied to the join, even if some rows do not fit thecondition.
RIGHT JOIN: Performs similar to JOIN, except that all entries intable2 is applied to the join, even if some rows don't fit the condition.
INNER JOIN: Only entries in both tables that match the condition are
considered for the join.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
14/54
Example for JOINs
14Web Programming
Given the following tables:
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
15/54
Example for JOINs
15Web Programming
If simple comma-delimitation was used, the following wouldbe the result:
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
16/54
Example for JOINs
16Web Programming
Using the LEFT JOIN on this tables, with the condition
User.userid = UserDownloads.userid
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
17/54
Example for JOINs
17Web Programming
Using the RIGHT JOIN on this table, with the samecondition
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
18/54
Example for JOINs
18Web Programming
Using INNER JOIN on this table, with the same condition
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
19/54
Table JOINs
19Web Programming
In most cases, an INNER JOIN yields the most relevantresults for join operations.
However, in cases where the entries of one table shouldappear no matter what, a LEFT JOIN or RIGHT JOIN ismore appropriate.
At all times, avoid using the comma-delimited join. While it issimpler and more convenient to write, the performance hitincurred in its usage makes the time investment of writing a
proper join worth it.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
20/54
The WHERE Clause
20Web Programming
Specifies a condition that must be matched by entries in theselected table in order for them to be part of the result.
Several operators that can be used to specify a condition:
= : Checks for equality between two given operands
=, > : Checks if the first operand is greater than equal to, or greater than the 2ndoperand
like : Performs a string comparison between two operands. Using this operation,two wildcard characters can be used to represent unknown values:
% : Matches any string of any length
Ex. 'A%s' will match any string starting with A and ending in s
_ : Matches any single character string.
Ex. 'b_t' will match bot, but, bit, bat, and bet.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
21/54
SELECT Statement Examples
21Web Programming
Retrieve all available data on the users table:
SELECT * from users;
Look for the addresses of users with a name of Smith
SELECT address from users where name ='Smith';
Retrieve entries for all users with a name starting with 'S'
SELECT * from users where name like 'S%';
SQL is not case-sensitive about the developer's use of itskeywords. However, it IS case-sensitive with regards to
values it performs comparisons on. The following statementwill return a different set of data compared to the one above:
SELECT * from users where name ='sMith';
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
22/54
Data Manipulation
22Web Programming
Used to modify the state of data in the database
SQL statements that fall within this type:
INSERT statement
UPDATE statement DELETE statement
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
23/54
INSERT Statement
23Web Programming
Used to insert new rows of information in existing databasetables
INSERT INTO table-name VALUES(value1, value2, ...)
table-name: Name of the table which will contain the new data row
The parameter given inside the VALUES keyword is a comma-delimited list of values that will be added into the table.
It is important to note that any call to INSERT must followthe integrity rules as defined in the data table.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
24/54
UPDATE Statement
24Web Programming
Updates existing rows in a table
UPDATE table-name set column-value(s) WHERE condition(s)
table-name: Name of the table which contains the rows to update
column-values: Comma-delimited list of column name and valuepairs
Any updates must conform to data integrity rules in thedatabase.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
25/54
DELETE Statement
25Web Programming
Removes a row from a specified table
DELETE FROM table-name WHERE condition(s)
table-name: name of the table containing the rows to be deleted
A comma-delimited list of conditions can optionally be specified aswell.
If no conditions are given, the statement will delete all rows in thespecified table.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
26/54
JDBC
26Web Programming
Java Database Connectivity
A standard API provided by Java for accessing databases
Developers can access databases no matter who the
vendor may be. The vendors provide the implementations to the abstract interfaces
defined in the API, providing the same set of functionality to thedeveloper.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
27/54
JDBC
27Web Programming
Key classes in the JDBC API:
java.sql.Connection: Represents a connection with the database.Abstracts the details of how to communicate with the databaseserver.
java.sql.DriverManager: Manages JDBC drivers used by theapplication. In conjunction with the proper driver URL and properauthentication, can provide applications with valid instances ofConnection objects.
javax.sql.DataSource: Abstracts the details (URL, authenticationdetails) of how to obtain a connection to the database. Newer andmore preferred method of obtaining Connection objects.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
28/54
JDBC
28Web Programming
(Key classes continued)
java.sql.Statement: Provides methods for the developer to executeSQL statements.
java.sql.ResultSet: Represents the results of an SQL statement.
These objects are usually returned from methods in the Statementobject.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
29/54
java.sql.DriverManager
29Web Programming
Allows a developer to retrieve a Connection object which hecan then use to perform database activities.
Two steps required:
First, the JDBC driver must first be registered with the DriverManager. This can
be done by using the Class.forName method to load the driver's class definitioninto memory.
Second, use the getConnection method in the DriverManagerm supplying aJDBC URL, as well as the username and password authenticated for databaseaccess. The URL must follow the syntax required by the particular databaseimplementation.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
30/54
java.sql.DriverManager
30Web Programming
Sample of how to get a Connection from a PostgreSQLdatabase:
String jdbcURL = "jdbc:postgresql://localhost:5432/jedi-db";
String user = "jedi";String password = "j3d1master";Connection conn = null;
try {Class.forName("org.postgresql.Driver");conn = DriverManager.getConnection(url, user, password);...
} catch (SQLException e) {// perform error handling here}
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
31/54
java.sql.DriverManager
31Web Programming
(Sample continued)
While this is a valid way of retrieving a Connection object, thismethod requires the developer to keep track of details that are mostlikely to change per deployment of the application:
Driver class name URL required for database access
Username and password cleared for database usage
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
32/54
javax.sql.DataSource
32Web Programming
An interface defined in the JDBC API since version 2 of itsspecification.
The recommended way for a developer to get a Connectionobject.
Retrieval of the Connection object is very straightforward.
Simply call the getConnection() method in a valid instance ofDataSource.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
33/54
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
34/54
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
35/54
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
36/54
Creating a Connection Pool
36Web Programming
Steps:
Click on the JDBC link on the pane to the left, then click ConnectionPools on the pane to the right.
In the screen that appears next, click on the New... button to bring
up a display similar to the one below:
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
37/54
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
38/54
Creating a Connection Pool
38Web Programming
(Steps continued)
In the next screen, scroll down until you see the properties to beassociated with this connection pool.
The following parameters need to have values supplied:
Password
ServerName
PortNumber
DatabaseName
User
After supplying all of the above values, click on the Finish button.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
39/54
Registering the Datasource
39Web Programming
Steps:
Click on the JDBC link found on the left pane, then click on JDBCResources. In the following screen, click on New ...
The fields are to be filled as follows:
JNDI Name: Enter the logical name by which the application will retrieve theDataSource. It is recommended that this name have jdbc/ as its prefix, to make iteasier for future server administrators to identify this element as a JDBCresource.
Pool name: Select the name of the connection pool created earlier.
Description: Enter text describing the DataSource. (optional)
Click on OK to finish.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
40/54
Retrieving the DataSource
40Web Programming
Retrieving an instance of a DataSource from an applicationserver is simple and can be accomplished using only a fewlines of code using a portion of the JNDI API.
Java Naming Directory Interface (JNDI) is the Java standard API for
accessing directories. A directory is a centralized location where Java applications can retrieve external
resources using a logical name.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
41/54
41Web Programming
...
Context ctxt = null;DataSource ds = null;
try {
// create an instance of the JNDI context to which we will perform lookups
ctxt = new InitialContext();
// retrieve the DataSource from the directory using a logical name
ds = (DataSource)ctxt.lookup("jdbc/PostgreSQLDS");
} catch (NamingException ne) {
System.err("Specified DataSource cannot be found");
}
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
42/54
Retrieving the DataSource
42Web Programming
The JNDI context abstracts the details of connecting to thedirectory, making resource lookup as simple as calling asingle method. Take note that the name used to lookup theresource must be the same name used in configuring the
DataSource. Once we have a valid DataSource instance, getting a
Connection object is as simple as:
Connection conn = ds.getConnection();
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
43/54
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
44/54
44
Web Programming
Context ctxt = null;
DataSource ds = null;Connection conn = null;Statement stmt = null;ResultSet rs = null;
try {ctxt = new InitialContext();ds = (DataSource)ctxt.lookup("jdbc/PostgreSQLDS");
conn = ds.getConnection();stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT * FROM users");
} catch (NamingException e) {System.err("Cannot find named datasource");
} catch (SQLException se) {System.err("Error occurred while performing query");
}
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
45/54
java.sql.ResultSet
45
Web Programming
Encapsulates the results of a query to the database.
The data inside a ResultSet object can best be visualized asa table. The information can then be retrieved one row at atime, with the ResultSet object keeping track of which row is
current.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
46/54
java.sql.ResultSet
46
Web Programming
To iterate over the rows exposed in the ResultSet, itprovides us with a method called next().
Calling the next() method moves the internal pointer the ResultSetobject keeps to point to the next row.
This method returns true if there is a next row to be found, and falseif there are no more rows left.
while (rs.next()) {
//read data from current row here
}
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
47/54
java.sql.ResultSet
47
Web Programming
To retrieve the data from each row, the ResultSet objectprovides us with a number of get methods which take in as aparameter either the column number of the columncontaining the data, or the column name.
getString method for retrieving the data as a String getInt method for retrieving integer data
getBoolean for retrieving boolean data
It is recommended however, that names be used to specify a column to readfrom instead of a row number.
Context ctxt = null;
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
48/54
48
Web Programming
Context ctxt null;DataSource ds = null;Connection conn = null;Statement stmt = null;ResultSet rs = null;
try {ctxt = new InitialContext();ds = (DataSource)ctxt.lookup("jdbc/PostgreSQLDS");conn = ds.getConnection();stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT * FROM users");
while (rs.next()) {String userName = rs.getString("name");String address = rs.getString("address");int userID = rs.getInt("userid");
// perform operations on retrieved data here.}
} catch (NamingException e) {System.err("Cannot find named datasource");
} catch (SQLException se) {System.err("Error occurred while performing query");
}
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
49/54
Releasing System Resources
49
Web Programming
It is a very important step that is often overlooked after anoperation has been completed.
Must be done explicitly and is the responsibility of theprogrammer.
Without performing such a release, the resources taken upby our operation can not be used in the future. For large-scale applications, this can rapidly result in loss of availableconnections.
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
50/54
Releasing System Resources
50
Web Programming
The release of resources can be performed by calling on theclose() methods available on each of the Connection,Statement, and ResultSet objects.
There is a specific order involved, the ResultSet must be closed first,
then the Statement, and finally the Connection object. Since the close method in each of those objects is defined to throw
an SQLException, enclose the call within a try-catch block.
Context ctxt = null;DataSource ds = null;Connection conn = null;
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
51/54
51
Web Programming
Statement stmt = null;ResultSet rs = null;
try {ctxt = new InitialContext();ds = (DataSource)ctxt.lookup("jdbc/PostgreSQLDS");conn = ds.getConnection();stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT * FROM users");
while (rs.next()) {String userName = rs.getString("name");
String address = rs.getString("address");int userID = rs.getInt("userid");
// perform operations on retrieved data here.}
rs.close();stmt.close();conn.close();
} catch (NamingException e) {System.err("Cannot find named datasource");
} catch (SQLException se) {System.err("Error occurred while performing query");
}
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
52/54
Releasing System Resources
52Web Programming
(Sample continued)
Common mistake of developers: simply placing the close methodswithin the program body.
Only addresses success conditions
Better solution: place the release code within a finally clause, toensure that it happens no matter what.
Context ctxt = null;DataSource ds = null;Connection conn = null;St t t t t ll
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
53/54
53Web Programming
Statement stmt = null;ResultSet rs = null;
try {ctxt = new InitialContext();ds = (DataSource)ctxt.lookup("jdbc/PostgreSQLDS");conn = ds.getConnection();stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT * FROM users");
while (rs.next()) {String userName = rs.getString("name");
String address = rs.getString("address");int userID = rs.getInt("userid");
// perform operations on retrieved data here.}
} catch (NamingException e) {System.err("Cannot find named datasource");
} catch (SQLException se) {
System.err("Error occurred while performing query");
} finally {try {
if (rs != null) {rs close();
-
8/3/2019 MELJUN_CORTES_JEDI Slides-Web Programming-Chapter05-SQL and JDBC
54/54
54Web Programming
rs.close();}
} catch (SQLException e) {}
try {if (stmt != null) {stmt.close();
}} catch (SQLException e) {}
try {if (conn != null) {
conn.close();}
} catch (SQLException e) {}}