DATABASE PROGRAMMING

1. DATABASE PROGRAMMING 3 JDBC by the ASU Scholars

2. ADVANCED DATABASE CONCEPTS JDBC Susan D. Urban and Suzanne W. Dietrich Department of Computer Science and Engineering Arizona State University Tempe, AZ 85287-5406

3. OPEN DATABASE CONNECTIVITY (ODBC) • Standard application programming interface (API) for accessing a database. • A separate module or driver is required for each database to be accessed. • Based on the standard Call Level Interface (CLI) of the SQL Access Group (part of the X/Open Standard). • Can use the API to execute SQL statements, update tables, and retrieve metadata.

4. ODBC ISSUES IN A JAVA ENVIRONMENT • ODBC uses C to access the data source. This poses issues with implementation. Hence, it cannot be used in conjunction with Java. • OBDC’S API cannot be used by translating the API to Java since there is no pointer concept in Java. • ODBC requires the driver manager to be installed on every client installation.

5. JAVA DATABASE CONNECTIVITY (JDBC) • Java API for connecting programs written in Java to databases. • Based on ODBC. • Allows Java programs to send SQL statements to any relational database. • Platform independent. • JDBC drivers written in Java can be accessed from any computer in a heterogeneous network . • A JDBC-ODBC bridge can be used to access databases using the ODBC interface.

6. TWO-TIER JDBC ARCHITECTURES • Java application or applet talks directly to the data source. • Client sends requests to the server through user interfaces. • JDBC Driver communicates with the data source to access the data. Sun Microsystems Inc. 1999

7. THREE-TIER JDBC ARCHITECTURES • Uses a third tier between the client and the server. • Controls updates that are made to the database. • Secure and robust. Sun Microsystems Inc. 1999

8. DEVELOPING JDBC APPLICATIONS • Import JDBC classes (java.sql.*) • Load the JDBC Driver. • Connect to the database. • Use the JDBC API to access the database. • Disconnect from the database.

9. ESTABLISHING A CONNECTION TO A DATABASE • The first step in accessing data from any relational database using JDBC is to establish a connection with the data source. • The Connection object is used to get meta data and execute SQL statements. • The getConnection method returns a Connection object that represents a session with a specific database. • The parameters in the getConnection method are URL, username and password. Username and password are optional. • The URL consists of the protocol “jdbc”, sub-protocol “odbc”, and the Data Source Name(DSN).

10. EXAMPLE TO CONNECT TO A DATABASE /* dbNameis the registered name of the ODBC data source */ String url = "jdbc:odbc:" + dbName ; try { /* Load the jdbc-odbc driver */ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); /* Open a connection to the odbc data source */ con =DriverManager.getConnection(url,"",""); }

11. STATEMENTS • A Statement Object is used to send SQL queries to a database. • A Statement object should be created using the connection method createStatement().

12. TYPES OF STATEMENTS There are three types of statement objects: • Simple statementsUsed to execute SQL statements without any parameters. Statement stmt = connection.createStatement(); • Prepared StatementsUsed when a statement will be called several times and is stored as a pre-compiled statement with IN parameters.PreparedStatement pstmt = con.prepareStatement(“update employee set salary=? where ssn=?”); • Callable StatementsUsed with calls to database stored procedures and SQL statements with OUT parameters.

13. EXECUTING SIMPLE STATEMENTS • The execution of a statement returns results into a ResultSet object. The ResultSet object is then used to access query results. ResultSet rs = null; • The executeQuery() method is used to execute an SQL statement through the statement object. rs = stmt.executeQuery("SELECT a, b, c FROM Table1"); • The close() method is used to close the ResultSet. rs.close();

14. GETTING DATA FROM A ResultSet • The next() method is used to traverse through tuples in the ResultSet object. • The data stored in a ResultSet object is retrieved through a set of get methods that allows access to the various columns of the current row. • The results are printed out on a screen using the Servlet output stream. while(rs.next()) { out.println(rs.getString(“SSN”)); }

15. DATABASE METADATA • Metadata is the information in the database that is associated with the database schema: • Table names • Column names • Column types • The metadata associated with a database can be queried using JDBC. • The metadata associated with the result set object of a statement execution can also be queried.

16. DATABASE METADATA RETRIEVAL • Create a metadata object. DatabaseMetaData dbmd ; • Retrieve metadata from the database through the connection established. dbmd = con.getMetaData(); • The getTables() method of the metadata object is used to retrieve information about the tables in a database. The information is stored in a result set object. ResultSet rsTables = dbmd.getTables(null, null, null, null);

17. GETTING THE METADATA • The getString() method of the ResultSet object is used to locate a specific table. String tableName = rsTables.getString("TABLE_NAME"); • The getColumns() method is used to retrieve information about the columns and column types in a table, with the results stored in a ResultSet object. ResultSet rsColumns = dbmd.getColumns(null,null,tableName,null); while (rsColumns.next()) { … }