JDBC

1. JDBC

2. Java Database Connectivity (JDBC) • Usethe 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.

3. 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 …

4. 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…

5. 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"; String statementString = "INSERT INTO Movie(title, year, length, studioName) " + "VALUES( '" + title + "'," + year + "," + length + ",'" + studioName + "')"; Statement stmt = conn.createStatement(); stmt.executeUpdate(statementString); stmt.close(); } } No semicolon ending an SQL statement

6. 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.

7. 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); } However we can also do instead: String s = rset.getString(1); int n = rset.getInt(2); Column Name Column Position

8. 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.

9. 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(); } }

10. 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 = ?");

11. 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);

12. 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(); } }