Java Database Connectivity (JDBC)

1. Java Database Connectivity (JDBC) CISC370/Object Oriented Programming with Java Happiness is good health and a bad memory.

2. Use and Distribution Notice • Possession of any of these files implies understanding and agreement to this policy. • The slides are provided for the use of students enrolled in Jeff Six's Object Oriented Programming with Java class (CISC 370) at the University of Delaware. They are the creation of Mr. Six and he reserves all rights as to the slides. These slides are not to be modified or redistributed in any way. All of these slides may only be used by students for the purpose of reviewing the material covered in lecture. Any other use, including but not limited to, the modification of any slides or the sale of any slides or material, in whole or in part, is expressly prohibited. • Most of the material in these slides, including the examples, is derived from multiple textbooks. Credit is hereby given to the authors of these textbook for much of the content. This content is used here for the purpose of presenting this material in CISC 370, which uses, or has used, these textbooks.

3. What is Java Database Connectivity (JDBC)? • JDBC is a technology that allows Java programs to directly interface with relational databases (using industry-standard SQL). • Using JDBC, your programs can: • Set up connections to a very wide variety of database systems (both commercial and free). • Execute arbitrary SQL statements and examine and manipulate the results. • Retrieve and manipulate database meta-data.

4. What is Java Database Connectivity (JDBC)? • The JDBC APIs have been around since Java 1.2 (the first Java 2 release). They are very stable, although new features are added with every major Java release (support for most of SQL3/SQL99 was introduced in JDBC 3.0/Java 1.3). • JDBC does not include high-level database design or display capabilities; it is a programming API designed to allow your program to interact with existing database – you still need a database/DB server.

5. Review of Databases and SQL • A relational database consists of one or more tables, often referred to as relations. • Each table has a name and one or more columns. • Each column has a name and a data type. • Each piece of data is stored as a row in a table. Database: “classroom_scheduling” table: “classrooms” table: “courses”

6. Databases: Tables and Types • Each column in a database table has a unique name, and a data type. Table: “courses” ID instructor size start date CISC370 Jeffrey A. Six 40 09/02/02 CISC220 James Durbano 20 09/01/02 char(7) varchar(200) integer date

7. SQL: Overview • SQL (Structured Query Language) is a standardized language used for database commands. • SQL commands fall into two groups: • Data manipulation – used for adding, extracting, changing, and deleting data; these command use the database. • Data definition – used for creating, configuring, and deleting tables, databases, views, and indexes; these commands administer the database.

8. SQL: Command Syntax • Each SQL command consists of a verb followed by clauses that expand upon and define what the verb should do. • Examples of common verbs include: • Data manipulation: SELECT, UPDATE, DELETE, INSERT • Data definition: CREATE, DROP

9. SQL: The CREATE Verb • The SQL verb CREATE is used to create tables, indexes, views, and other “objects” associated with a database. • To use CREATE to create some tables: CREATE TABLE instructors(emp_ID char(11) primary key,name varchar(250), dept integer) CREATE TABLE courses (ID char(7),instructor char(11), dept integer)

10. SQL: The SELECT Verb • The SQL verb SELECT is used for queries; it selects column values from rows that match the query criteria. • Some examples of queries: SELECT name,emp_ID FROM instructors SELECT instructors.name,courses.sizeFROM instructors,coursesWHERE instructors.dept = courses.dept SELECT * FROM instructors WHERE emp_ID<>’123-45-6789’

11. SQL: The INSERT Verb • The SQL verb INSERT is used to add a new item (row) of data to a table. • Some examples of inserting new data: INSERT INTO instructors (emp_ID, name, dept)VALUES (’123-45-6789’,’Jeffrey A. Six’, 12) INSERT INTO courses VALUES (’CISC370’, ’123-45-6789’, 12) (’CISC220’, ’567-43-5622’, 12) (’ELEG205’, ’987-65-4321’, 27)

12. SQL: The UPDATE Verb • The SQL verb UPDATE is used to modify data in a table. • Some examples of updating data: UPDATE courses SET ID = ‘CISC370’ WHERE ID = ‘CISC364’ UPDATE instructors SET dept = 23WHERE dept=21 OR dept>=59

13. % is a string wildcard in SQL SQL: The DELETE Verb • The SQL verb DELETE is used to remove data items (rows) from a table. • Some examples of deleting rows: DELETE FROM instructors WHERE dept = 23 AND name LIKE ’James %’ DELETE FROM coursesWHERE courses.dept > 100

14. JDBC Basic Architecture • The JDBC Basic Architecture: Java Application Application Java Core APIs JDBC API Database Server JDBC Driver JVM Database

15. JDBC: Types of Drivers Type 1:ODBCBridge Java App JDBC API JDBCBridge Driver MSODBC Database Server Database Java App JDBC API JDBC Driver Type 2:NativeDB API Native APILibrary NativeAPI Database

16. JDBC: Types of Drivers Type 3:Pure Java Java App JDBC API JDBCDriver Database Translator Database Server Database Type 4:PureJava Direct Java App JDBC API JDBCDriver Database Server Database

17. Overview of a JDBC-based Program • The basic sequence of operations for a program that will use JDBC to interact with a database: • Load the correct JDBC driver (using the classname) • Open a Connection to the database (using a URL) • Create statements (using the Connection) • Execute statements and process results • Close the Connection

18. JDBC Programming – Database URLs • JDBC always refers to databases by URLs. • The general structure of a JDBC URL is: jdbc:<subprotocol>:<dbname>[params] • Some common examples: jdbc:odbc:aMSAccessDB jdbc:odbc:oDatabase2;UID=jeffsix;PWD=simplePW jdbc:mysql://dbserver/myDB?user=jeff&password=easy jdbc:postgresql://psServer.mycorp.com:1878/myDB jdbc:sequelink://host5/someDB;uid=bob;password=pop

19. JDBC Programming – General Form import java.sql.*; public class SimpleJDBCExample { static String drv = "org.gjt.mm.mysql.Driver"; static String url = "jdbc:mysql://localhost/instructors"; public static void main(String [] args) { try { // create a new JDBC driver object instance Class.forName(drv).newInstance(); // create a Connection object Connection conn; conn = DriverManager.getConnection(url,“jeffsix",“arrrr!"); // create a Statement object Statement stmt; stmt = conn.createStatement(); // use the Statement object to execute a SQL command/statement int rc = stmt.executeUpdate("delete from instructors where dept=23"); // all done, so close the Statement and Connection objects stmt.close(); conn.close(); } catch (Exception sqe) { System.err.println("JDBC Error: " + sqe); } } }

20. JDBC Programming – General Form • The first thing to do in a JDBC program is to load the driver for our specific database (in this case, mySQL). This is easy to do... • Class.forName(drv).newInstance(); (if this call fails it will throw a ClassNotFoundException) • Next, we need to open a connection to the database specified in the URL, using username “jeffsix” and password “arrrr!”. This one line of code does so and returns a refernce to the Connection object: • DriverManager.getConnection(url, “jeffsix”,“arrrr!” (if this call fails it will throw a SQLException)

21. JDBC Programming – General Form • The program can now use the database Connection to create a Statement object. Easy to do… Statement stmt = conn.createStatement(); (if access is denied then this code will throw an SQLException) • The program can then use the Statement to execute an SQL command/statement. The method returns the number of table rows affected by the update comment. int rc = stmt.executeUpdate("delete " +"from instructors where dept=23"); (if failed this code will throw an SQLException) • When all done, close the Statement and the Connection.

22. The JDBC Connection Class • There are many methods a program can call on its valid Connection object. • The createStatement() method will create a Statement object that can be used to assemble and run SQL commands. The preparedStatement() creates an object that is associated with a predefined SQL command (the Statement object can be used for arbitrary statements – and can be reused for other SQL commands) • The getMetaData() method will return metadata associated with the database, including descriptions of all of the tables in the DB. • The prepareCall() method is used to call stored procedures in the SQL database.

23. The JDBC Statement Class • There are many methods a program can call on its valid Statement object. • The executeQuery() method executes a SQL query statement and returns the results (as a ResultSet). The executeUpdate() method executes a SQL update (such as UPDATE, INSERT, or DELETE). The execute() method executes other, more generic, SQL statements. • The setMaxRows() method allows the program to set the maximum number of data elements (rows) that can be returned from a query statement. • There are MANY more methods for both the Connection and Statement classes.

24. JDBC Programming - Queries • The most popular thing for a Java program to do via JDBC is to perform queries on a DB. • You can perform a query using a Statement object and its executeQuery() method. This method will returns a ResultSet object that gives you access to the query rows. • A ResultSet is just like a database table; it has zero or more rows (zero if no data elements match the query criteria) and each row has one or more columns (having no columns would make absolutely no sense).

25. This gets the first column as an integer. JDBC Programming - Queries • Performing a query and interacting with the ResultSet is very easy… ResultSet queryResults; theQueryResults = theStatement.executeQuery( "select distinct dept from instructors"); while(theQueryResults.next()) { System.out.println(“Dept:" + queryResults.getInt(1)); } theQueryResults.close(); • ResultSet objects keep track of their position internally, starting before the first row. You can use next() and other navigation methods to move through them (see the API docs for more methods).

26. JDBC Programming - ResultSets • There are multiple methods of extracting data from the current row in a ResultSet. • The getString() method returns the value of a particular column in the current row as a String. • The getInt() method returns the value of a particular column in the current row as an int. • The getBoolean() method returns the value of a particular column in the current row as an boolean. • The getDouble() method returns the value of a particular column in the current row as an double. • The getObject() method returns the value of a particular column in the current row as an Object. • There are really two versions of both of these methods…one takes the column as an integer index, one takes the column as its name as a String.

27. JDBC Statements - Types • There are three types of statement objects in JDBC programming: • Statement - This allows the execution of arbitrary statements on the database. • PreparedStatement - This kind of statement allows the program to execute the same SQL command repeatedly, while allowing substitutions for particular words or values (to make it more useful). • CallableStatement - This kind of statement allows the program to execute SQL stored procedures, with substitutions for arguments. A stored procedure is a function that is part of, and stored inside, a SQL database (hence the name).

28. JDBC Statements - PreparedStatements import java.sql.*; public class IDQuery { static String url = "jdbc:mysql://localhost/instructors"; public static void main(String [] args) { try { Class.forName("org.gjt.mm.mysql.Driver").newInstance(); Connection theConnection; PreparedStatement prep_statement; ResultSet theQueryResults; theConnection = DriverManager.getConnection(url,“jeffsix”,“arrrr!”); prep_statement = theConn.prepareStatement("SELECT name,dept FROM instructors WHERE ID=?"); for(int i = 0; i < args.length; rs.close(), i++) { prep_statement.setString(1, args[i]); theQueryResults = prep_statement.executeQuery(); if (theQueryResults.next()) System.out.println(theQueryResults.getString(1) + "," + theQueryResults.getString(2)); else System.out.println(“Query” + i + “not found!”); } prep_Statement.close(); theConnection.close(); } catch (Exception exp) { System.err.println("Execption! - " + exp); } } }

29. JDBC Statements – CallableStatements • The CallableStatement calls a SQL stored procedure inside the DB. The stored procedure is specified upon object creation (using the Connection’s prepareCall() method). • The syntax of this call is straightforward (even using the substitution abilities for parameters to the procedure)… • { ? = call procedurename(?,?) }for a procedure that takes 2 arguments; returns a value • { call procedurename(?,?,?,?) }for a procedure that takes 4 arguments; does not return a value • The syntax is easy to understand… CallableStatement callable;callable = conn.prepareCall("{ call replace(?,?) }");callable.setString(1, oldString);callable.setString(2, newString);int rows_modified = callable.executeUpdate();

30. JDBC Statements – Batch Mode • Your program can create a Batch of statements (any of the three types) and execute that batch on the database all at once… • The addBatch() method adds a statement to the current batch. • The executeBatch() method runs all of the statements in the batch on the database. • The clearBatch() method clears the statements from the batch without running them.

31. JDBC Statements – An Update Example import java.sql.*; public class addInstructorExample { public static void main(String [] args) { if (args.length != 3) { System.out.println(“Syntax: java addInstructorExample ID ‘name’ dept"); System.exit(0); } try { Class.forName(”org.gjt.mm.mysql.Driver”).newInstance(); Connection theConnection; PreparedStatement prep_statement; conn = DriverManager.getConnection("jdbc:mysql://localhost/theDB",“jeffsix",“arrrr!"); prep_statement = conn.prepareStatement("insert into instructors (ID,name,dept) values (?,?,?)"); prep_statement.setString(1,args[0]); prep_statement.setString(2,args[1]); prep_statement.setString(3,args[2]); int rows_modified = prep_statement.executeUpdate(); if (rows_modified == 1) System.out.println("Added " + args[0] + " to instructor table."); prep_statement.close(); theConnection.close(); } catch (Exception exp) { System.err.println("JDBC Error: " + exp); } } }

32. JDBC Data Types Many of the non- primitive types are defined in the java.sql pacakge. • As SQL defines its own datatypes, it is important to understand the mapping between SQL and Java datatypes… SQL Java SQL Java---------------------------------- ---------------------------------------------BIT boolean NUMERIC BigDecimalBIGINT long REAL floatBINARY byte[] SMALLINT shortCHAR String TIME TimeDATE Date TIMESTAMP TimestampDOUBLE double TINYINT byteFLOAT float VARBINARY byte[]INTEGER int VARCHAR char[] BLOB Blob REF Ref CLOB Clob STRUCT Struct

33. JDBC and Binary Data Types • SQLs offer two ways to support storage and retrieval of binary data in database tables: • Binary columns: BINARY / VARBINARY- these kinds of columns are suitable for small arrays of bytes (they are not very memory efficient). • Binary large object columns: BLOB / LONGBLOB- these kinds of columns are suitable for large arrays of bytes, but are not supported in all database systems (they are much more memory efficient). • BLOBs are useful in many databases: • BLOBs are used to store image, audio, signal, or video data • BLOBs are used to store serialized Java objects • BLOBs are suitable for storing entire documents inside databases (as documents are really just binary data)

34. JDBC and Binary Data Types • Storing binary data into the database is easy… • Using the SQL INSERT verb, the binary column is set using either the setObject() method and passing it a byte array or using the setBinaryStream() method and passing it an InputStream (which the JDBC driver will then used to fill in the column’s data field). • Retrieve binary data from the database is also easy: • Use a normal query, and either getObject() to return a byte array, getBlob() to return a Blob object, or getBinaryStream() to get an InputStream which can be used to read from this data field.

35. Summary • JDBC is a core Java technology that supports access to relational databases and database servers. • JDBC uses the standard Structured Query Language (SQL) for all database interaction. • In order to use JDBC with a particular database, you must install a driver for the database server you are using and load that driver in your programs. • Almost all JDBC functionality is accomplished through the use of SQL statements, of which there are three kinds… Statement, PreparedStatement, and CallableStatement.