JDBC CS 124 JDBC l l Java Database

JDBC CS 124

JDBC l l Java Database Connectivity Database Access Interface l l l provides access to a relational database (by allowing SQL statements to be sent and executed through a Java program) JDBC package: set of Java classes that facilitate this access (java. sql. *) Comes with JDK (since 1. 1)

JDBC Driver Need a driver, specific to the DB product, to mediate between JDBC and the database l the driver is a Java class that needs to be loaded first Java Program - load driver - establish connection - send SQL statements Relational DBMS

JDBC-ODBC Bridge l l Driver that interfaces with ODBC (Object Database Connectivity--also an access interface) Easiest way to access databases created by Microsoft products l l register database as an ODBC data source use JDBC-ODBC bridge as the JDBC driver (included in JDK distribution)

Key Classes in JDBC l Connection l l Statement l l need to create an instance of this class when establishing a connection to the database for issuing SQL statements Result. Set (interface) l a Result. Set object represents the table returned by an SQL select statement

Establishing a Connection Use the get. Connection() method l l l under the Driver. Manager class String argument: "jdbc: driver: name” returns a Connection object Class. for. Name(“sun. jdbc. odbc. Jdbc. Odbc. Driver”); // above line loads the jdbc-odbc driver String dbname = “jdbc: odbc: My. DB”; Connection c = Driver. Manager. get. Connection(dbname);

Creating a Statement Object Execute the create. Statement() method on the Connection object l l returns a Statement object afterwards, run methods on the Statement object to execute an SQL statement Statement s = c. create. Statement();

Methods of the Statement Class l l Methods of the Statement class require a string parameter containing the SQL statement execute. Query() l l l requires a String argument (a SELECT statement) returns a Result. Set object representing the table returned execute. Update() l l requires a String argument (an INSERT, UPDATE, or DELETE statement) returns an int (row count, in most cases)

The Result. Set Interface l l A Result. Set object represents the table returned by the select statement sent Navigation/retrieval methods l l next(): moves to the next row (first row if called for the first time), returns false if no rows remain get. XXX() methods return the value of a field for the current row

Resul. Set example Need braces because ORDER is a reserved word in SQL Result. Set rs; rs = s. execute. Query(“SELECT * FROM [ORDER]”); rs. next(); // gets the first row (use in a loop for multiple rows) // suppose the ORDER table has an integer field // called quantity int myvar = rs. get. Int(“quantity”); // if you knew that quantity is the 2 nd field in the table myvar = rs. get. Int(2);

Exercise l Create a Microsoft Access table l l Add an ODBC data source l l l insert sample rows use the Microsoft Access driver associate with the created database Create a Java program l l use JDBC-ODBC bridge create a loop that lists all rows of the table

execute. Query( ) example … Statement s = con. create. Statement(); Result. Set rs = s. execute. Query( “SELECT * FROM STUDENT WHERE QPI > 3. 0” ); while ( rs. next() ) { String name = rs. get. String(“Last. Name”); int y = rs. get. Int(“Year”); double qpi = rs. get. Double(“QPI”); System. out. println( name + “ ” + y + “ ” + qpi); }

execute. Update( ) example … Statement s = con. create. Statement(); int result; result = s. execute. Update( “DELETE FROM EMPLOYEE WHERE Dept. Code=‘CS’” ); System. out. println( result + “ rows deleted. ” );

The Prepared. Statement class l l Prepared. Statement: a Statement that specifies parameters through Java code The SQL statements take different forms when you specify different parameter values Useful when query is performed repeatedly Formatting of literal values is easier

Version 1 (Statement) // suppose last. Name is a String variable Query string is built manually Statement s = con. create. Statement(); Result. Set rs = s. execute. Query( “SELECT * FROM STUDENT WHERE Last. Name = ‘” + last. Name +”’” ); while ( rs. next() ) { String name = rs. get. String(“Last. Name”) + rs. get. String(“First. Name”); int y = rs. get. Int(“Year”); double qpi = rs. get. Double(“QPI”); System. out. println( name + “ ” + y + “ ” + qpi); }

Version 2 (Prepared. Statement) // suppose last. Name is a String variable Prepared. Statement s = con. prepare. Statement( “SELECT * FROM STUDENT WHERE Last. Name = ? ” ); s. set. String( 1, last. Name ); the appropriate literal Result. Set rs = s. execute. Query(); is “inserted” in the query while ( rs. next() ) { String name = rs. get. String(“Last. Name”) + rs. get. String(“First. Name”); int y = rs. get. Int(“Year”); double qpi = rs. get. Double(“QPI”); System. out. println( name + “ ” + y + “ ” + qpi); }

Summary l l JDBC allows you to write Java programs that manipulate a database A driver (often a separate product) is required that facilitates access Key classes: Connection, Statement, Prepared. Statement, and Result. Set Other features: metadata and stored-proc invocation