Transcript Document

JDBC
Java Database Connectivity (JDBC)
• Use the java.sql package to query and update the database.
• JDBC is an API that allows java to communicate with a
database server using SQL commands.
• Most important members, such as Connection, Statement,
and ResultSet, are interfaces instead of being classes.
– This is because, the point of JDBC is to hide the specifics of
accessing a particular database.
– The implementation of the underlying classes is done in the vendor
provided driver and associated classes.
Basics
• DriverManager is responsible for keeping track of all the JDBC drivers
that are available on a system.
• First task of a JDBC program is to load an appropriate driver for the type
of database being used.
• After that a JDBC program should connect to the database by calling
DriverManager.getConnection().
– You specify the database to connect with a jdbc:URL. This URL has the
following general syntax: jdbc:subprotocol:host:port:databasename
import java.sql.*;
class InsertMovie
{
public static void main (String args []) throws SQLException
{
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@oracle.csc.uvic.ca:1521:TEACH", "thomo", “password");
// @machineName:port:SID, userid, password
…
Creating JDBC Statements
• A Statement object is what sends your SQL statement to the DBMS.
• You simply create a Statement object and then execute it:
– For a SELECT statement, use executeQuery.
– For statements that create or modify tables, use executeUpdate.
• It takes an instance of an active connection to create a Statement object.
– In the following example, we use our Connection object conn to create the
Statement object stmt:
– Statement stmt = conn.createStatement();
• At this point stmt exists, but it does not have an SQL statement to pass
on to DBMS.
– We need to supply that with execute…
import java.sql.*;
class InsertMovie {
public static void main (String args []) throws SQLException
{
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@oracle.csc.uvic.ca:1521:TEACH", "user", "password");
// @machineName:port:SID, userid, password
String title = "Movie ABCDEF";
int year = 2005;
int length = 200;
String studioName = "UVic";
No semicolon ending
an SQL statement
String statementString = "INSERT INTO Movie(title, year, length, studioName) " +
"VALUES( '" + title + "'," + year + "," + length + ",'" + studioName + "')";
Statement stmt = conn.createStatement();
stmt.executeUpdate(statementString);
stmt.close();
}
}
Getting Data
Example
ResultSet rset = stmt.executeQuery(
"SELECT title, year " +
"FROM Movie");
while (rset.next()) …
• Variable rset, contains the rows of the query result.
• The first call of next() positions a "cursor" on the first row.
• Successive invocations of next() move the cursor down one row at a
time.
Using the getXXX methods
• Use the getXXX method of the appropriate type to retrieve the value in
each column.
– getString() for VARCHAR, CHAR
– getInt() for INT, NUMBER
– etc.
while (rset.next())
{
String s = rset.getString("Title");
int n = rset.getInt("Year");
System.out.println(s + " " + n);
}
Column
Name
However we can also do instead:
String s = rset.getString(1);
int n = rset.getInt(2);
Column
Position
Using the getXXX methods (Continued)
• JDBC allows a lot of flexibility as far as which getXXX methods you can
use to retrieve the different SQL types.
• For example, the method getInt can be used to retrieve any of the
numeric or character types. The data it retrieves will be converted to an
INT;
– that is, if the SQL type is VARCHAR , JDBC will attempt to parse an integer
out of the VARCHAR .
• The getString method can retrieve any other datatype. However, in such
a case we should convert strings to numbers.
import java.sql.*;
class dbAccess {
public static void main (String args []) throws SQLException
{
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@orcus.csc.uvic.ca:1521:TEACH", "thomo", “password");
// @machineName:port:SID, userid, password
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(
"SELECT title, year " +
"FROM Movie");
while (rset.next())
System.out.println (rset.getString("title") + " " + rset.getString("year"));
stmt.close();
}
}
Prepared statements
• Sometimes it is more convenient or more efficient to use a
PreparedStatement object for sending SQL statements to the database.
• When we want to execute a Statement object many times, it will
normally reduce execution time to use a PreparedStatement object
instead.
– A PreparedStatement is given an SQL template statement when it is
created.
– This is precompiled and and ready to run many times without the need to be
compiled each time a query conforming to it is given.
• Example
PreparedStatement updateMovies = conn.prepareStatement(
"UPDATE Movie SET studioName = ? WHERE studioName = ?");
import java.sql.*;
class UpdateMovie
{
public static void main (String args []) throws SQLException
{
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@oracle.csc.uvic.ca:1521:TEACH", "thomo", “password");
// @machineName:port:SID, userid, password
PreparedStatement updateMovieStatement;
String updateMovieString = "UPDATE Movie " +
"SET studioName = ? " +
"WHERE studioName LIKE ?";
updateMovieStatement = conn.prepareStatement(updateMovieString);
String studiosBoughtByParamount [] = {"Disney", "Fox"};
for(int i=0; i<studiosBoughtByParamount.length; i++)
{
updateMovieStatement.setString(1, "Paramount");
updateMovieStatement.setString(2, "%"+studiosBoughtByParamount[i]+"%");
updateMovieStatement.executeUpdate();
}
updateMovieStatement.close();
}
}