JDBC Java Database Connectivity

Outline of Presentation  Introduction to JDBC  JDBC Driver Types  General Architecture  JDBC Components

    

Database Programming using JDBC java.sql.* Package Accessing Database from a JSP Page Deploying Java Beans in a JSP Page Introduction To Struts Framework

Introduction to JDBC  JDBC is a front end tool which is used to connect front end java application to back end database  JDBC is an alternative to ODBC and ADO that provides database access to programs written in

Java.  JDBC drivers are available for most DBMS products

A JDBC Driver  Is an interpreter that translates JDBC method calls to

vendor-specific database commands Database commands

JDBC calls Driver

Database

 Implements interfaces in java.sql  Can also provide a vendor’s extensions to the JDBC

standard

JDBC Driver Types

General Architecture

JDBC Components

Database Programming Using JDBC STEPS:  1.Load the Driver  2.Establish a connection  3.Create JDBC Statements  4.Execute SQL Statements  5.GET ResultSet  6.Close connections

Using JDBC 1. Load the driver: Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”); //Type1 Driver 2. Establish a connection to the database:  A connection URL string includes the literal jdbc:, followed by the name of the driver and a URL to the database String url = "jdbc:odbc:dsn”; Create a Connection object: Connection con = DriverManager.getConnection(url, “scott”,”tiger”); 3. Create a statement object Statement stmt = conn.createStatement(); Associate SQL with the statement object String queryString = “select * from emp";

Using JDBC (Continued) 4.Execute the SQL Statements: Example statements: ResultSet rs = stmt.executeQuery(querystring); int result = stmt.executeUpdate(updatestring);  Compiled queries can be processed via a PreparedStatement object  Stored procedures can be processed via a CallableStatement object 5. GET Result while (rs.next()) { int empno = rs.getInt(1); String empname = rs.getString(2); } 6. Close connection  stmt.close();  con.close();

Using a PreparedStatement // Once you have a connection, you can create a // "prepared statement" object. A prepared statement is // precompiled, and can be used repeatedly with new values // for its parameters. // Use question marks for parameter place-holders. PreparedStatement prepStmt = con.prepareStatement( "INSERT INTO Artist (ArtistID, Name, " + "Nationality, BirthDate, DeceasedDate)" + "VALUES (ArtistSeq.nextVal, ?, ?, ?, ? )" ); // Now supply values for the parameters // Parameters are referenced in order starting with 1. prepStmt.setString( 1, "Galvan" ); prepStmt.setString( 2, "French" ); prepStmt.setInt ( 3, 1910 ); prepStmt.setNull ( 4, Types.INTEGER ); // The PreparedStatement object methods: // 1) executeUpdate -- statements that modify the database // 2) executeQuery -- SELECT statements (reads) prepStmt.executeUpdate(); System.out.println( "Prepared statement executed" ); // Now do it again prepStmt.setString( prepStmt.setString( prepStmt.setInt ( prepStmt.setInt (

1, 2, 3, 4,

"Monet" ); "French" ); 1840 ); 1879 );

prepStmt.executeUpdate(); System.out.println( "Prepared statement executed again" );

Accessing a stored procedure // Once you have a connection, you can create a // "callable statement" object to access the stored procedure. // Inside the curly braces, you call the procedure, and use // question marks for parameter place-holders. CallableStatement callStmt = con.prepareCall( "{call Record_sale( ?, ?, ?, ?, ?, ?)}" ); // Now supply values for the parameters // Parameters are referenced in order starting with 1. callStmt.setString( 1, "Barry Goldwater" ); callStmt.setString( 2, "Tobey" ); callStmt.setString( 3, "Mystic Fabric" ); callStmt.setString( 4, "105/135" ); callStmt.setInt ( 5, 24000 );

// And register the OUT variable // This variable returns information to this program // from the stored procedure. callStmt.registerOutParameter( 6, java.sql.Types.VARCHAR ); System.out.println( "Parameters set for CallableStatement" ); // The CallableStatement object has an additional method* // for use when the stored procedure uses multiple SQL statements. // 1) executeUpdate -- statements that modify the database // 2) executeQuery -- SELECT statements (reads) // *3) execute -- procedures with multiple SQL statements callStmt.execute(); System.out.println( "Stored procedure executed" ); // Get the OUT variable contents (parameter 6) String result = callStmt.getString( 6 );

Java.sql.* package java.sql.Connection Statement createStatement (int resultSetType, int resultSetConcurrency) PreparedStatement prepareStatement (String sql, int resultSetType, int resultSetConcurrency) CallableStatement prepareCall (String sql, int resultSetType, int resultSetConcurrency)

resultSetType

resultSetConcurrency

ResultSet.TYPE_FORWARD_ONLY

ResultSet.CONCUR_READ_ONLY

ResultSet.TYPE_SCROLL_INSENSITIVE

ResultSet.CONCUR_UPDATABLE

ResultSet.TYPE_SCROLL_SENSITIVE

Java.sql.* package java.sql.ResultS et void beforeFirst() throws SQLException void afterLast() throws SQLException boolean first() throws SQLException

boolean last() throws SQLException boolean absolute(int row) throws SQLException boolean relative(int row) throws SQLException

Example : Backward Statement stmt = conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

ResultSet rs = stmt.executeQuery("SELECT empno, sal FROM emp"); rs.afterLast(); while ( rs.previous() ) {

System.out.println(rs.getString("empno") + " " + rs.getFloat("sal")); } ...

Accessing the Database using JSP Consider stocks database There are three tables. Both customer and stocks have a one-to-many relationship with portfolios. The database stocks.mdb was registered with the ODBC driver as “CoolStocks”

customer stocks portfolio id lname fname

symbol id company symbol price num_shares

Register w/ODBC Create an ODBC data source. Click on the Start button. Choose Settings, Control Panel Double-click on ODBC Data Sources Choose the System DSN tab Click Add Click on the desired driver (Microsoft ODBC for oracle) Click on the Finish button Enter a Data Source Name (I called my database CoolStocks and that name appears in the java code below)

A Simple JSP/JDBC Example JSP JDBC Example 1