JDBC

1. JDBC

2. Database Connectivity • Relational databases • Manage all information generated by businesses • Centric to business applications • Established and Mature • Application developers require standard interface to relational databases • Change in database vendor should not require change in application programs

3. What is JDBC? • A pure Java API for database communication similar to ODBC (JDBC created in 1995) • JDBC and ODBC are based on the same standard: X/Open's SQL Call-Level Interface • A set of classes that perform database transactions • Connect to relational databases • Send SQL commands • Process results

4. Benefits of JDBC • No proprietary DB code (although pass-through is supported) • Don’t have to rely on single vendor...ever • Don’t need to make DB vendor decision early • Easier for DB vendors • Don’t need to provide a query language, only implement API

5. JDBC API and Drivers • JDBC API is generic: java.sql package • A Database system needs to supply a driver. • A Java program need to use a specific driver.

6. JDBC Architecture Java Application JDBC API JDBC Driver Manager JDBC Driver API (T1) JDBC- ODBC Bridge (T4) Java Driver to vendor's protocol JDBC Drivers ODBC Driver Proprietary, vendor-specific database access protocol Provided with Sun's JDK

7. Extracting Data From a Table • Need to use SQL: • Structured Query Language • Language use to communicate with database systems. • There are lots of fancy things you can do with SQL, we will just look at simple stuff…

8. SQL • There are many different types of SQL commands (SELECT is just one). • For example: to create a new record: "INSERT INTO password (Name, Password) VALUES ('Dave', 'blah')"

9. Database Interaction • General Idea: • establish some kind of connection with a database. • send SQL commands. • get back error codes, and/or sets of records.

10. Creating a Connection • java.sql.Connection interface corresponds to a session (a connection with a specific database). • DriverManager will create a Connection object. Connection foo = DriverManager.getConnection(DBURL);

11. Two Examples • MS-SQL Server Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); Connection con = DriverManager.getConnection(“jdbc:microsoft:sqlserver://server1:1433”, userName, password); • JDBC-ODBC Bridge Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con = DriverManager.getConnection("jdbc:odbc:people", userName, password);

12. Connection and Statement • Connection: • Manage properties of the connection • autocommit, etc. • create a Statement object • Statement: • used to execute an SQL statement and retrieve results. Statement stmt = conn.createStatement ();

13. ResultSet • A ResultSet is returned when you execute an SQL statement: ResultSet rs = stmt.executequery("SELECT * FROM password");

14. Using ResultSet • Extract rows from the result (step through rows one at a time). • Extract a field (column) value from the current row. • lots of ways to get at values (as String, int, Object, etc).

15. Example //name is the 1st attribute of Student while (rset.next ()) System.out.println (rset.getString (1));

16. Releasing Resources //close the result set, statement, and the connection rset.close(); stmt.close(); conn.close();