JDBC

1. JDBC

2. Introduction to JDBC Programming Mohamed Grida

3. JDBC Programming • After completing this lesson, you should be able to do the following: • Connect to a database using Java Database Connectivity (JDBC) • Create and execute a query using JDBC • Invoke prepared statements • Commit and roll back transactions

4. JDBC • JDBC is a standard interface for connecting to relational databases from Java. • The JDBC classes and interfaces are in the java.sql package. • JDBC 1.22 is part of JDK 1.1; JDBC 2.0 is part of Java 2

5. Overview of Querying a Database With JDBC Connect Query Processresults Close

6. Stage 1: Connect Connect Register the driver Connect to the database Query Processresults Close

7. A JDBC Driver • Is an interpreter that translatesJDBC method calls to vendor-specific database commands • Implements interfaces in java.sql • Can also provide a vendor’s extensions to the JDBC standard Database commands JDBC calls Driver Database

8. Oracle JDBC Drivers • Thin driver • a 100% Java driver for client-side use without an Database installation, particularly with applets • OCI drivers (Database Specific) • for client-side use with an Oracle client installation • JDBC-ODBC Bridge (Built in JAVA) • Call the DSN created in ODBC which specify the DB driver and the DB Location • Translates JDBC into open database connectivity (ODBC) calls

9. About JDBC URLs • JDBC uses a URL to identify the database connection. jdbc:<subprotocol>:<subname> DSN Protocol Subprotocol jdbc:odbc:<driver>:@<database>

10. How to Make the Connection 1. Register the driver. Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver"); 2. Connect to the database. Connection conn = DriverManager.getConnection (URL, userid, password); Connection con = DriverManager.getConnection ("jdbc:odbc:thin:dsnname",“Mohamed",“ww");

11. Using Connection java.sql.Connection Creating Statement createStatment() prepareStatment(String) prepareCall(String) Transaction Management commit() rollback() Get database metadata getMetaData() Connection related close() isClosed()

12. Stage 2: Query Connect Create a statement Query Query the database Processresults Close

13. The Statement Object • A Statement object sends your SQL statement to the database. • You need an active connection to create a JDBC statement. • Statement has three methods to execute a SQL statement: • executeQuery() for QUERY statements • executeUpdate() for INSERT, UPDATE, DELETE, or DDL statements • execute() for either type of statement

14. How to Query the Database 1. Create an empty statement object. 2. Execute the statement. Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery(statement); int count = stmt.executeUpdate(statement); boolean isquery = stmt.execute(statement);

15. Querying the Database: Examples • Execute a select statement. Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery ("select RENTAL_ID, STATUS from ACME_RENTALS"); • Execute a delete statement. Statement stmt = conn.createStatement(); int rowcount = stmt.executeUpdate ("delete from ACME_RENTAL_ITEMS where rental_id = 1011");

16. Stage 3: Process the Results Connect Query Step through the results Assign results to Java variables Processresults Close

17. The ResultSet Object • JDBC returns the results of a query in a ResultSet object. • A ResultSet maintains a cursor pointing to its current row of data. • Use next() to step through the result set row by row. • getString(), getInt(), and so on assign each value to a Java variable.

18. How to Process the Results • 1. Step through the result set. • 2. Use getXXX() to get each column value. while (rset.next()) { … } String val = rset.getString(colname); String val = rset.getString(colIndex); boolean more = rset.next(); while ( more ) { String title = rset.getString("TITLE"); String year = rset.getString("YEAR"); … // Process or display the data}

19. ResultSet maps database types to Java types. Mapping Database Types to Java Types ResultSet rset = stmt.executeQuery ("select RENTAL_ID, RENTAL_DATE, STATUS from ACME_RENTALS"); int id = rset.getInt(1); Date rentaldate = rset.getDate(2); String status = rset.getString(3); Col Name RENTAL_ID RENTAL_DATE STATUS Type NUMBER DATE VARCHAR2

20. Stage 4: Close Connect Query Close the result set Processresults Close the statement Close Close the connection

21. How to Close the Connection 1. Close the ResultSet object. 2. Close the Statement object. 3. Close the connection (not necessary for server-side driver). rset.close(); stmt.close(); conn.close();

22. How to Create a Prepared Statement 1.Register the driver and create the database connection. 2.Create the prepared statement, identifying variables with a question mark (?). PreparedStatement pstmt = conn.prepareStatement("update ACME_RENTALS set STATUS = ? where RENTAL_ID = ?"); PreparedStatement pstmt = conn.prepareStatement("select STATUS from ACME_RENTALS where RENTAL_ID = ?");

23. How to Execute a Prepared Statement 1. Supply values for the variables. 2. Execute the statement. pstmt.setXXX(index, value); pstmt.executeQuery(); pstmt.executeUpdate(); PreparedStatement pstmt = conn.prepareStatement("update ACME_RENTALS set STATUS = ? where RENTAL_ID = ?"); pstmt.setString(1, "OUT"); pstmt.setInt(2, rentalid); pstmt.executeUpdate();