Transcript PPT - Department of Computer Science

Data Base Connectivity From JAVA
Creating a Java program that access mySQL is not difficult.
Your Java program must can a number of functions to facilitate easy access to an
external database. They include:





Importing the correct packages that deal with SQL.
Creating a connection to the database.
Creating an initialization file, so that it is easy to point to another database.
Closing the connection to the database when you are complete.
Writing code to perform: inserts, updates, deletes and selects from the database.
Programming in java with databases is simpler if you create objects to perform the
actions and allow programs to call those objects. This allows the database to be
changed without requiring the main programs to be modified.
Data Base Connectivity From JAVA
Creating a Java program that access mySQL is not difficult.
Importing the correct packages that deal with SQL.
The following are the packages you need to include:
import
import
import
import
import
import
java.sql.Connection; // Java’s interface to SQL
java.sql.DriverManager; // Loads the appropriate SQL driver
java.sql.SQLException; // Handles errors from the database
java.util.Properties; // Configuration file to load the db.properties file
java.util.logging.Level; // Logs information
java.util.logging.Logger; // Logs information
Data Base Connectivity From JAVA
Creating a Java program that access mySQL is not difficult.
Creating a connection to the database
public DatabaseConnection() throws SchedulerException
{
logger = Logger.getLogger(this.getClass().getName());
//Properties contains all the attributes in the file
//load them into an object and the copy out the attributes
Properties props = new Properties();
try {
props.load(getClass().getResourceAsStream("db.properties"));
final String driver = props.getProperty("driver");
final String url = props.getProperty("url");
final String user = props.getProperty("user");
final String pass = props.getProperty("pass");
Class.forName(driver).newInstance();
connection",
//connect to the database
connect = DriverManager.getConnection(url, user, pass);
}
catch (Exception ex) {
logger.log(Level.SEVERE, "Unable to create database connection",
ex);
throw new SchedulerException("Unable to create database
ex);
}
}
Data Base Connectivity From JAVA
Creating an initialization file, so that it is easy to point to another database.
The initialization file must contain the: driver, url, user name, and password
db.properties file
driver = com.mysql.jdbc.Driver
url = jdbc:mysql://landsend.cs.drexel.edu/scheduler
user = jsalvage
pass = dbwiz
Data Base Connectivity From JAVA
When an object goes out of scope it is important to close the connection.
First check if the database connection is still valid, if so close it and set it to null, if not
throw an error.
protected void finalize()
{
if (connect != null) {
try {
connect.close();
connect = null;
}
catch (SQLException ex) {
logger.log(Level.SEVERE,
"Unable to close database connection",
ex);
}
}
}
}
Data Base Connectivity From JAVA
ADDING A RECORD TO THE DATABASE
Inserting data into a database uses an SQL INSERT statement, but requires some additional
formatting.
A PerparedStatement is an object that allows the execution of SQL statements from java.
It allows the SQL to be written with arguments that can then be set via a SetString or SetInt
method.
Observe declaring a PreparedStatement stm that inserts 5 values into the Course table:
final PreparedStatement stm = connect.prepareStatement(
"INSERT INTO Course VALUES(?, ?, ?, ?, ?)");
To set each argument, call the appropriate method setString or setInt with two
parameters. The first is the argument you wish to set in the PreparedStatement and
the second is the value of that argument.
Therefore, if you wish to set the first argument to “Computer Science”, use the
following method call:
stm.setString(1, “Computer Science”);
Similairly, to set the third argument to the 3 credits, use the following method call:
stm.setInt(3, 3);
Data Base Connectivity From JAVA
ADDING A RECORD TO THE DATABASE
To execute the statement, close it, and check to see it executed properly, use the
following sequence of calls:
int n = stm.executeUpdate();
stm.close();
if (n != 1)
throw new SchedulerException("Unable to add course");
}
catch (SQLException ex) {
logger.log(Level.SEVERE, "addStudent", ex);
throw new SchedulerException("Unable to add course",
ex);
}
}
Executing the SQL command is simply a matter of calling the executeUpdate method of the
PreparedStatement object.
Data Base Connectivity From JAVA
RETRIEVING RECORDS FROM A DATABASE
Retrieving data from a database uses an SQL SELECT statement, but requires some
additional formatting.
Again we will use the PreparedStatement to hold the SQL command.
The results of the query will be stored in a ResultSet object and then each record is
copied to an array list object in Java.
The PreparedStatement is assigned the SQL Select statement to execute. Unlike the
insert, no arguments are needed. While the SELECT is hardcoded here, it could just
as easily been built in a string variable and passed dynamically.
See the following example:
final PreparedStatement stm = connect.prepareStatement(
"SELECT * FROM Course ORDER BY dept, num");
Data Base Connectivity From JAVA
To execute the SELECT statement a similar call as with INSERT, except here we need to store
the result of the query in a ResultSet Object. Observer the following code:
final ResultSet result = stm.executeQuery();
Once the ResultSet is populated, we want to move the values from the result set to an
ArrayList. This can be done a number of ways, observe an example:
final List<Course> courses = new ArrayList<Course>();
while (result.next())
courses.add(toCourse(result));
Finally, once all the rows are copied, close the ResultSet and close the database statement.
result.close();
stm.close();
Data Base Connectivity From JAVA
RETRIEVING RECORDS FROM A DATABASE WITH A CONDITION
Just as we could set parameters in an INSERT statement, a SELECT statement can be
set up to use parameters for the values to limit the predicate with.
Again we will use the PreparedStatement to hold the SQL command.
The results of the query will be stored in a ResultSet object and the single record will
be added to our course object.
The only real difference is using ? in the WHERE clause of the SELECT statement
where the parameters will be placed.
Observe the following statement which creates a SELECT statement
final PreparedStatement stm = connect.prepareStatement(
"SELECT * FROM Course WHERE dept = ? AND num = ?");
The parameters are set with the following statements:
stm.setString(1, dept);
stm.setInt(2, num);
And then executed with the following statement:
final ResultSet result = stm.executeQuery();
Data Base Connectivity From JAVA
DELETING RECORDS FROM A DATABASE WITH A CONDITION
Deleting records from a SQL database isn’t very different in form than selecting data
from a database. You must prepare the statement in the same manner, just instead of
creating a SQL SELECT in the prepareStatement object, you create a SQL DELETE
Statement.
Observe the following code which declares a DELETE statement that deletes course
that match a department and course number:
final PreparedStatement stm = connect.prepareStatement(
"DELETE FROM Course WHERE dept = ? AND num = ?");
stm.setString(1, dept);
stm.setInt(2, num);
stm.executeUpdate();
stm.close();
Data Base Connectivity From JAVA
UPDATING RECORDS FROM A DATABASE WITH A CONDITION
The updating of records in a database follows the same pattern as SELECT and
DELETE. Observe the following code that sets the credits and name field of a course
that matches the department and name:
final PreparedStatement stm = connect.prepareStatement(
"UPDATE Course SET credits = ?, name = ?, description = ?" +
" WHERE dept = ? AND name = ?");
stm.setInt(1, course.getCredits());
stm.setString(2, course.getName());
stm.setString(3, course.getDescription());
stm.setString(4, course.getDepartment());
stm.setInt(5, course.getNumber());
stm.executeUpdate();
stm.close();
Data Base Connectivity From JAVA
package edu.drexel.cs350;
import
import
import
import
import
import
java.sql.Connection; // Java’s interface to SQL
java.sql.DriverManager; // Loads the appropriate SQL driver
java.sql.SQLException; // Handles errors from the database
java.util.Properties; // Configuration file to load the db.properties file
java.util.logging.Level; // Logs information
java.util.logging.Logger; // Logs information
/**
* Base class for those that use a database connection
*
* @author Sunny Huynh
* @version 1.0
* @since 1.0
*/
public abstract class DatabaseConnection
{
protected final Logger logger;
protected Connection connect = null;
Data Base Connectivity From JAVA
public DatabaseConnection() throws SchedulerException
{
logger = Logger.getLogger(this.getClass().getName());
connection",
Properties props = new Properties();
try {
props.load(getClass().getResourceAsStream("db.properties"));
final String driver = props.getProperty("driver");
final String url = props.getProperty("url");
final String user = props.getProperty("user");
final String pass = props.getProperty("pass");
Class.forName(driver).newInstance();
connect = DriverManager.getConnection(url, user, pass);
}
catch (Exception ex) {
logger.log(Level.SEVERE, "Unable to create database connection",
ex);
throw new SchedulerException("Unable to create database
ex);
}
}
Data Base Connectivity From JAVA
Driver specifies which backend database system to use
In this case, we need a mySQL driver since the database is mySQL
The URL specifies the location of the database as well as which database within
mySQL to use
db.properties file
driver = com.mysql.jdbc.Driver
url = jdbc:mysql://landsend.cs.drexel.edu/scheduler
user = jsalvage
pass = dbwiz
Data Base Connectivity From JAVA
When an object goes out of scope it is important to close the connection.
protected void finalize()
{
if (connect != null) {
try {
connect.close();
connect = null;
}
catch (SQLException ex) {
logger.log(Level.SEVERE,
"Unable to close database connection",
ex);
}
}
}
}
Data Base Connectivity From JAVA
/*
* DatabaseCourseManager.java
* Copyright (c) 2007 Drexel University. All rights reserved.
*/
package edu.drexel.cs350;
import
import
import
import
import
import
java.sql.PreparedStatement; //Executes a SQL statement
java.sql.ResultSet; //Stores the rows returned from the query
java.sql.SQLException;//Handles errors
java.util.ArrayList;//Dynamic structure
java.util.List;//Interface to an ArrayList
java.util.logging.Level; Used to log errors
/**
* Database backed course manager
*
* @author Sunny Huynh
* @version 1.0
* @since 1.0
*/
public class DatabaseCourseManager extends DatabaseConnection
implements CourseManager
{
Data Base Connectivity From JAVA
Example: AddCourse
A course contains:





Department Name
Department Number
Number of Credits
Name
Description
Therefore, the insert statement will contain five values. In it’s most basic form, a SQL INSERT
statement has the following syntax:
INSERT INTO TableName VALUES (list of values)
This form of SQL INSERT requires the knowledge of the order of the fields in the table. The
SQL table was created in the order the fields are listed above. Therefore, we can perform a SQL
insert by listing the values in their proper place.
Java allows this to be done without a lot of fancy string manipulation if you use the
PreparedStatement object. Observe the following code which associates each value to be
inserted with the proper question mark.
One huge benefit to using the PreparedStatement instead of building the string manually, is it
handles any special characters that would need to be escaped. i.e. double quote. In addition, it
will prevent SQL code from inadvertently being executed, but that is an advanced topic.
Data Base Connectivity From JAVA
/*
* @see edu.drexel.cs350.CourseManager#addCourse(edu.drexel.cs350.Course)
*/
public void addCourse(final Course course) throws SchedulerException
{
try {
final PreparedStatement stm = connect.prepareStatement(
"INSERT INTO Course VALUES(?, ?, ?, ?, ?)");
stm.setString(1, course.getDepartment());
stm.setInt(2, course.getNumber());
stm.setInt(3, course.getCredits());
stm.setString(4, course.getName());
stm.setString(5, course.getDescription());
int n = stm.executeUpdate();
stm.close();
if (n != 1)
throw new SchedulerException("Unable to add course");
}
catch (SQLException ex) {
logger.log(Level.SEVERE, "addStudent", ex);
throw new SchedulerException("Unable to add course", ex);
}
}
Executing the SQL command is simply a matter of calling the executeUpdate
method of the PreparedStatement object.
Data Base Connectivity From JAVA
RETRIEVING RECORDS FROM A DATABASE
Example: getAllCourses
We need to select data from the database and return it into a structure Java can understand.
In it’s most basic form, a SQL SELECT statement has the following syntax:
SELECT * FROM TableName ORDER BY ListOfFields
The ORDER BY clause is optional, but will allow the results to be sorted by the fields we list after
the keywords ORDER BY.
Again we will use the PreparedStatement to hold the SQL command.
The results of the query will be stored in a ResultSet object and then each record will be added
to our courses object.
Data Base Connectivity From JAVA
/*
* @see edu.drexel.cs350.CourseManager#getAllCourses()
*/
public Course[] getAllCourses()
{
try {
final PreparedStatement stm = connect.prepareStatement(
"SELECT * FROM Course ORDER BY dept, num");
final ResultSet result = stm.executeQuery();
final List<Course> courses = new ArrayList<Course>();
while (result.next())
courses.add(toCourse(result));
result.close();
stm.close();
return courses.toArray(new Course[0]);
}
catch (SQLException ex) {
logger.log(Level.SEVERE, "getAllCourses", ex);
return new Course[0];
}
}
Data Base Connectivity From JAVA
RETRIEVING RECORDS FROM A DATABASE WITH A CONDITION
Example: getCourse
We need to add a selection criteria to our SQL statement so only a specific of courses is
returned.
In it’s most complex form, a SQL SELECT statement has the following syntax:
SELECT * FROM TableName WHERE Field1 = value1 and Field2 = value2
The WHERE clause is optional, and allows the results to filtered based upon the selection criteria
you list.
Again we will use the PreparedStatement to hold the SQL command.
The results of the query will be stored in a ResultSet object and the single record will be added
to our course object.
Data Base Connectivity From JAVA
/*
* @see edu.drexel.cs350.CourseManager#getCourse(java.lang.String, int)
*/
public Course getCourse(final String dept, int num)
{
Course course = null;
try {
final PreparedStatement stm = connect.prepareStatement(
"SELECT * FROM Course WHERE dept = ? AND num
= ?");
stm.setString(1, dept);
stm.setInt(2, num);
final ResultSet result = stm.executeQuery();
if (result.next())
course = toCourse(result);
result.close();
stm.close();
}
catch (SQLException ex) {
logger.log(Level.SEVERE, "getCourse", ex);
}
return course;
}
Data Base Connectivity From JAVA
?");
/*
* @see edu.drexel.cs350.CourseManager#removeCourse(java.lang.String, int)
*/
public void removeCourse(final String dept, int num) throws SchedulerException
{
try {
final PreparedStatement stm = connect.prepareStatement(
"DELETE FROM Course WHERE dept = ? AND num =
stm.setString(1, dept);
stm.setInt(2, num);
stm.executeUpdate();
stm.close();
}
catch (SQLException ex) {
logger.log(Level.SEVERE, "removeCourse", ex);
throw new SchedulerException(ex);
}
}
Data Base Connectivity From JAVA
/*
* @see edu.drexel.cs350.CourseManager#updateCourse(edu.drexel.cs350.Course)
*/
public void updateCourse(final Course course) throws SchedulerException
{
try {
final PreparedStatement stm = connect.prepareStatement(
"UPDATE Course SET credits = ?, name = ?,
description = ?" +
" WHERE dept = ? AND name = ?");
stm.setInt(1, course.getCredits());
stm.setString(2, course.getName());
stm.setString(3, course.getDescription());
stm.setString(4, course.getDepartment());
stm.setInt(5, course.getNumber());
stm.executeUpdate();
stm.close();
}
catch (SQLException ex) {
logger.log(Level.SEVERE, "updateCourse", ex);
throw new SchedulerException(ex);
}
}
private Course toCourse(final ResultSet result) throws SQLException
{
final String dept = result.getString("dept");
final int num = result.getInt("num");
final Course course = new Course(dept, num);
course.setCredits(result.getInt("credits"));
course.setName(result.getString("name"));
course.setDescription(result.getString("description"));
return course;
}