Transcript Java Database Programming

CSCI 6962:
Server-side Design and Programming
JDBC Database
Programming
Outline
•
•
•
•
•
•
Introduction to JDBC
Connecting to a database server
Executing queries and reading result sets
Prepared statements
Executing update statements
Synchronized database access
JDBC Definition
•
Java Database Connectivity (JDBC):
set of classes that provide methods to
–
–
–
Connect to a database through a database server (using a driver)
Query database using SQL syntax, getting “list” of records that
match query
Manipulate database by executing SQL commands to modify,
insert, and delete records
web container
JSF
page
Managed
bean
database server
JDBC
database
driver
DBMS
database
JDBC Components
• Major objects involved:
– Connection: represents connection to a database through a server
– Statement: represents SQL statement executed on database via
that connection
– ResultSet: represents “list” of records matching a query
Database server
Statement object
select * from widgets
ResultSet object
ID name price
ID name price
ID name price
database
Connecting to the Database Server
• Load the database driver
– Not necessary in most recent version, but safe thing to do
Syntax:
Class.forName("driver class").newInstance();
• Name of driver class based on provider (see their documentation)
– Derby: org.apache.derby.jdbc.ClientDriver
– MySQL: com.mysql.jdbc.Driver
Connecting to the Database Server
• Need to provide url of database
• Form: jdbc:servertype:serverURL:port/databasename
– Derby: jdbc:derby://localhost:1527/DBname
– MySQL: jdbc:mysql://localhost:3306/Dbname
Connecting to the Database Server
• Syntax:
connectionobject =
DriverManager.getConnection("databaseURL",
"username",
"password");
• Derby example:
• Should close connection when done
connectionobject.close();
Exception Handling in JDBC
• Any database-related statement may throw SQLException
– Your code must put in try/catch block
– May also need to catch other exceptions
• ClassNotFoundException for missing database driver
Diagnostic message
displayed
Better idea: Redirect to an
error page
Connecting to the Database Server
Executing Queries
• Create new statement object using the connection
• Execute an SQL query using that statement
• Store results in a ResultSet object
• Syntax:
statement = connection.createStatement();
statement.executeQuery(“SQL query”);
Reading ResultSets
• Can only do simple access:
– Read in field values from current record
– Move to next record
• Syntax to move to next record: ResultSetObject.next();
– Returns false if no next record, true otherwise
– Must execute once before reading first record
– Usually while loop to read until no more records
while(ResultSetObject.next()) {
code to read in current record
}
Reading ResultSets
• Syntax to read field from current record:
value = ResultSetObject.getType(fieldname);
Specify type data is to be read in as
varChar  getString
int  getInt
double  getDouble
Specify field name
used in database
Widget Example
• Goal: Display all Widgets in datatable
– Same as before, but no longer hardwired
• Database access in Widget class
• getAllWidgets:
– Queries for all widgets, extracts ID of each widget
– Constructs widget with that ID, adds to list
• Constructor:
– Queries for widget with given ID
– Extracts name, price to set its properties
getAllWidgets Code
Execute SQL
query for all
widgets
Loop through
all results
Get the value of the ID field
Use it to construct a widget
and add to the list
Close connection and return list
Inserting Parameter Values
• Queries often based on variables
– Example: finding widget with given ID
• Must insert values into query
– If value is string, must make sure quote marks ‘ ‘
surround the value!
Insert given ID into the query
Constructor Code
Execute SQL query for
widgets with given ID
Advance to first (and
only) result
Extract name as string
and price as double
Prepared Statements
• Tell database server basic form of statements in advance
– Database server can do all work for that type of statement once
• “Fill in blanks” for actual values when actually execute statement
– Easier syntax than inserting manually
– More secure against SQL injection attacks!
• Example: Extracting widget with given ID
– All statements of form:
"SELECT * FROM widgets WHERE ID = ____“
Prepared Statements
• Declare as PreparedStatement
PreparedStatement lookup = null;
• Define prepared statement using
connection.prepareStatement(template);
• Place ‘?’ where actual values will be inserted
lookup = connection.prepareStatement("SELECT * FROM
widgets WHERE ID = ?");
Prepared Statements
• Use setType (index, value) to insert value into statement
Type of field (like get method in ResultSet)
Which ‘?’ to insert the value into
lookup.setString(1, ID);
Insert ISBN into first
(and only) ‘?’ in
lookup
• Execute query on the prepared statement
resultsConstructor = lookup.executeQuery();
Executing Update Statements
• Syntax:
int chng = statement.executeUpdate(SQL)
or
int chng = preparedstatement.executeUpdate()
• Returns number of records changed by statement
– Often used for validation
Updating Price in Widget Class
Performing Update from Bean
• Call static Widget method with ID, price
• If price changed, display new inventory
• If no change, display error message in JSF page
Synchronized Database Access
• Database updates occur “simultaneously” on busy sites
• Can interfere with one another
• Example: Quantity update after purchase
– Query for previous quantity
– Subtract 1
– Update database with new quantity
Synchronized Database Access
• Java runs separate clients as “parallel” threads which
execute “simultaneously”
– Processor swaps back and forth between threads
• Problem if following sequence occurs:
–
–
–
–
–
–
–
Current quantity = 100
Client 1 code to get current quantity executes (value = 100)
Processor swaps to client 2 thread
Client 2 code to get current quantity (value still = 100)
Client 2 code sets new quantity to 99 and stores in database
Processor swaps back to client 1 thread
Client 1 code also sets new quantity to 99 and stores in
database!
Synchronized Database Access
Problem: this code should not be interrupted!
Client 1
thread
Client 2
thread
Get quantity
Set quantity = 99
Quantity = 100
Store 99 in
database
Get quantity
Set quantity = 99
Quantity = 100
Store 99 in
database
Synchronized Database Access
• Can declare sections of code to be synchronized
– Only one thread may execute it at a time
– Another thread cannot start the code until the first has finished it
• Syntax: synchronized(object) { code }
Only one thread at a time should be able to execute this code on this object
Synchronized Database Access