JDBC Java Database Connectivity JDBC Use the java

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 Result. Set, 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 • Driver. Manager 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 Driver. Manager. get. Connection(). – 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 Insert. Movie { public static void main (String args []) throws SQLException { Driver. Manager. register. Driver (new oracle. jdbc. driver. Oracle. Driver()); Connection conn = Driver. Manager. get. Connection ("jdbc: oracle: thin: @oracle. csc. uvic. ca: 1521: TEACH", "thomo", “password"); // @machine. Name: 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 execute. Query. – For statements that create or modify tables, use execute. Update. • 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. create. Statement(); • 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 Insert. Movie { public static void main (String args []) throws SQLException { Driver. Manager. register. Driver (new oracle. jdbc. driver. Oracle. Driver()); Connection conn = Driver. Manager. get. Connection ("jdbc: oracle: thin: @oracle. csc. uvic. ca: 1521: TEACH", "user", "password"); // @machine. Name: port: SID, userid, password String title = "Movie ABCDEF"; int year = 2005; int length = 200; String studio. Name = "UVic"; No semicolon ending an SQL statement String statement. String = "INSERT INTO Movie(title, year, length, studio. Name) " + "VALUES( '" + title + "', " + year + ", " + length + ", '" + studio. Name + "')"; Statement stmt = conn. create. Statement(); stmt. execute. Update(statement. String); stmt. close(); } }

Getting Data Example Result. Set rset = stmt. execute. Query( "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 get. XXX methods • Use the get. XXX method of the appropriate type to retrieve the value in each column. – get. String() for VARCHAR, CHAR – get. Int() for INT, NUMBER – etc. while (rset. next()) { String s = rset. get. String("Title"); int n = rset. get. Int("Year"); System. out. println(s + " " + n); } However we can also do instead: String s = rset. get. String(1); int n = rset. get. Int(2); Column Name Column Position

Using the get. XXX methods (Continued) • JDBC allows a lot of flexibility as far as which get. XXX methods you can use to retrieve the different SQL types. • For example, the method get. Int 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 get. String method can retrieve any other datatype. However, in such a case we should convert strings to numbers.

import java. sql. *; class db. Access { public static void main (String args []) throws SQLException { Driver. Manager. register. Driver (new oracle. jdbc. driver. Oracle. Driver()); Connection conn = Driver. Manager. get. Connection ("jdbc: oracle: thin: @orcus. csc. uvic. ca: 1521: TEACH", "thomo", “password"); // @machine. Name: port: SID, userid, password Statement stmt = conn. create. Statement(); Result. Set rset = stmt. execute. Query( "SELECT title, year " + "FROM Movie"); while (rset. next()) System. out. println (rset. get. String("title") + " " + rset. get. String("year")); stmt. close(); } }

Prepared statements • Sometimes it is more convenient or more efficient to use a Prepared. Statement 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 Prepared. Statement object instead. – A Prepared. Statement is given an SQL template statement when it is created. – This is precompiled and ready to run many times without the need to be compiled each time a query conforming to it is given. • Example Prepared. Statement update. Movies = conn. prepare. Statement( "UPDATE Movie SET studio. Name = ? WHERE studio. Name = ? ");

import java. sql. *; class Update. Movie { public static void main (String args []) throws SQLException { Driver. Manager. register. Driver (new oracle. jdbc. driver. Oracle. Driver()); Connection conn = Driver. Manager. get. Connection ("jdbc: oracle: thin: @oracle. csc. uvic. ca: 1521: TEACH", "thomo", “password"); // @machine. Name: port: SID, userid, password Prepared. Statement update. Movie. Statement; String update. Movie. String = "UPDATE Movie " + "SET studio. Name = ? " + "WHERE studio. Name LIKE ? "; update. Movie. Statement = conn. prepare. Statement(update. Movie. String);

String studios. Bought. By. Paramount [] = {"Disney", "Fox"}; for(int i=0; i<studios. Bought. By. Paramount. length; i++) { update. Movie. Statement. set. String(1, "Paramount"); update. Movie. Statement. set. String(2, "%"+studios. Bought. By. Paramount[i]+"%"); update. Movie. Statement. execute. Update(); } update. Movie. Statement. close(); } }