JDBC java for learning java for learn.ppt

• Common JDBC Components: • The JDBC API provides the following interfaces and classes −  DriverManager − This class manages a list of database drivers. Matches connection requests from the java application with the proper database driver using communication sub protocol. The first driver that recognizes a certain subprotocol under JDBC will be used to establish a database Connection.  Driver − This interface handles the communications with the database server. You will interact directly with Driver objects very rarely. Instead, you use DriverManager objects, which manages objects of this type. It also abstracts the details associated with working with Driver objects.  Connection − This interface with all methods for contacting a database. The connection object represents communication context, i.e., all communication with database is through connection object only.  Statement − You use objects created from this interface to submit the SQL statements to the database. Some derived interfaces accept parameters in addition to executing stored procedures.  ResultSet − These objects hold data retrieved from a database after you execute an SQL query using Statement objects. It acts as an iterator to allow you to move through its data.  SQLException − This class handles any errors that occur in a database application. •

Overview (1/2) • JDBC – JDBC is a standard interface for connecting to relational databases from Java – Allows a Java program to issue SQL statements and process the results. – The JDBC Classes and Interfaces are in the java.sql package – JDBC is Java API for executing SQL statements • Provides a standard API for tool/database developers • Possible to write database applications using a pure Java API • Easy to send SQL statements to virtually any relational database • What does JDBC do? – Establish a connection with a database – Send SQL statements – Process the results JDBC Driver JAVA Applet/ Application Database JDBC Call Database Command

• Reason for JDBC – Database vendors (Microsoft Access, Oracle etc.) provide proprietary (non standard) API for sending SQL to the server and receiving results from it – Languages such as C/C++ can make use of these proprietary APIs directly • High performance • Can make use of non standard features of the database • All the database code needs to be rewritten if you change database vendor or product – JDBC is a vendor independent API for accessing relational data from different database vendors in a consistent way Overview (2/2)

5 What is JDBC? • “An API that lets you access virtually any tabular data source from the Java programming language” • JDBC Data Access API – JDBC Technology – What’s an API? – What’s a tabular data source? • “… access virtually any data source, from relational databases to spreadsheets and flat files.” – JDBC Documentation • We’ll focus on accessing Oracle/MySQL databases

JDBC Model • JDBC consists of two parts: – JDBC API, a purely Java- based API – JDBC driver manager • Communicates with vendor- specific drivers JAVA Applet/ Application JDBC API Driver Manager Driver API Vendor Specific JDBC Driver JDBC-ODBC Bridge Database Vender Specific ODBC Driver Database Java Application Developer JDBC Developer Vender Specific JDBC developer

Type I Drivers • Use bridging technology • Requires installation/configuration on client machines • Not good for Web • e.g. ODBC Bridge

Type II Drivers • Native API drivers • Requires installation/configuration on client machines • Used to leverage existing CLI libraries • Usually not thread-safe • Mostly obsolete now • e.g. Intersolv Oracle Driver, WebLogic drivers

Type III Drivers • Calls middleware server, usually on database host • Very flexible -- allows access to multiple databases using one driver • Only need to download one driver • But it’s another server application to install and maintain • e.g. Symantec DBAnywhere

Type IV Drivers • 100% Pure Java -- the Holy Grail • Use Java networking libraries to talk directly to database engines • Only disadvantage: need to download a new driver for each database engine • e.g. Oracle, mSQL

JDBC Driver Type • JDBC-ODBC bridge plus ODBC driver • Native-API partly-Java driver • JDBC-Net pure Java driver • Native Protocol pure Java API driver

Flavors of JDBC Drivers JDBC Application Type I “Bridge” driver Type II “Native” driver Type III “Middleware” driver Type IV “Pure” driver ODBC ODBC Driver CLI (.lib) Middleware Server

JDBC Classes and Interfaces Steps to using a database query: • Load a JDBC “driver” • Connect to the data source • Send/execute SQL statements • Process the results

JDBC Driver • Acts as the gateway to a database • Not actually a “driver”, just a .jar file Java application Database Server JDBC Driver

JDBC Driver Installation • Must download the driver, copy it to cobweb then add the .jar file to your $CLASSPATH • To set up your classpath, ssh into cobweb and execute the following command: – export CLASSPATH=$CLASSPATH:<path to .jar file>:.

JDBC Driver Management • All drivers are managed by the DriverManager class • Example - loading an Oracle JDBC driver: – In the Java code: Class.forName(“oracle.jdbc.driver.OracleDriver”) • Driver class names: Oracle: oracle.jdbc.driver.OracleDriver MySQL: com.mysql.jdbc.Driver MS SQL Server: com.microsoft.jdbc.sqlserver.SQLServerDriver

JDBC Programming Steps Connect Query Process Results Close 1) Register the driver 2) Create a connection to the database 1) Create a statement 2) Query the database 1) Get a result set 2) Assign results to Java variables 1) Close the result set 2) Close the statement 3) Close the connection

4.1. Pseudocode as a Diagram DriveManager Connection Statement ResultSet creates creates creates Driver SQL SQL data data make link to driver

Database Programming Steps 1. Establish a connection 2. Begin transaction 3. Create a statement object 4. Associate SQL with the statement object 5. Provide values for statement parameters 6. Execute the statement object 7. Process the results 8. End transaction 9. Release resources

Skeleton Code Class.forName(DRIVERNAME); Connection con = DriverManager.getConnection( CONNECTIONURL, DBID, DBPASSWORD); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(“SELECT a, b, c FROM member); While(rs.next()) { Int x = rs.getInt(“a”); String s = rs.getString(“b”); Float f = rs.getFloat(“c”); } rs.close(); stmt.close(); con.close(); Loading a JDBC driver Connecting to a database Processing the result set Closing the connections Executing SQL

Establishing a Connection • Create a Connection object • Use the DriverManager to grab a connection with the getConnection method • Necessary to follow exact connection syntax • Problem 1: the parameter syntax for getConnection varies between JDBC drivers • Problem 2: one driver can have several different legal syntaxes

Step 1 : Loading a JDBC Driver • A JDBC driver is needed to connect to a database • Loading a driver requires the class name of the driver. Ex) JDBC-ODBC: sun.jdbc.odbc.JdbcOdbcDriver Oracle driver: oracle.jdbc.driver.OracleDriver MySQL: com.mysql.jdbc.Driver • Loaing the driver class Class.forName("com.mysql.jdbc.Driver"); • It is possible to load several drivers. • The class DriverManager manages the loaded driver(s)

Step 2 : Connecting to a Database (1/2) • JDBC URL for a database – Identifies the database to be connected – Consists of three-part: jdbc:<subprotocol>:<subname> Protocol: JDBC is the only protocol in JDBC Protocol: JDBC is the only protocol in JDBC Subname: indicates the location and name of the database to be accessed. Syntax is driver specific Subname: indicates the location and name of the database to be accessed. Syntax is driver specific Sub-protocol: identifies a database driver Sub-protocol: identifies a database driver E.g. jdbc:mysql://somewebsite.com/somedbname

JDBC URL Vendor of database, Location of database server and name of database Username Password Step 2 : Connecting to a Database (2/2) • The DriverManager allows you to connect to a database using the specified JDBC driver, database location, database name, username and password. • It returns a Connection object which can then be used to communicate with the database. Connection connection = DriverManager.getConnection("jdbc:mysql://somewebsite.com/somedb",“userid",“password"); JDBC URL Vendor of database, Location of database server and name of database Username Password

Establishing a Connection (cont.) Oracle Example • Connection con = DriverManager.getConnection(string, “username", “password"); • what to supply for string ? • “jdbc:oracle:thin:@augur.seas.gwu.edu:1521:orcl10g2” Driver Database URL Port # SID Type

Database URL String DB_URL = "jdbc:mysql://dbserver:3306/world"; The format of a database URL is: Protocol Sub-protocol Hostname Port DatabaseName Port is the TCP port number where the database server is listening.  3306 is the default port for MySQL Use hostname "localhost" for the local machine.

Database URL Example: These 4 URL refer to the same database "jdbc:mysql://localhost:3306/world" "jdbc:mysql://localhost/world" "jdbc:mysql:///world" "jdbc:mysql:/world" The hostname and port are optional. For MySQL driver: defaults are localhost and port 3306

Establishing a Connection (cont.) MySQL Example • Connection con = DriverManager.getConnection(string); • what to supply for string ? • “jdbc:mysql://<URL>:3306/<DB>?user=<user>&password=<pw>” Driver Port Username Password URL DB Name

JDBC sequence diagram 1.Get a Connection to the database. 2.Create a Statement using the Connection. 3.Execute the Statement with SQL string. 4.Use the results.

Step 3 : Executing SQL (1/2) • Statement object – Can be obtained from a Connection object – Sends SQL to the database to be executed • Statement has three methods to execute a SQL statement: – executeQuery() for QUERY statements • Returns a ResultSet which contains the query results – executeUpdate() for INSERT, UPDATE, DELETE, or DDL statements • Returns an integer, the number of affected rows from the SQL – execute() for either type of statement Statement statement = connection.createStatement();

Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery ("select RENTAL_ID, STATUS from ACME_RENTALS"); Statement stmt = conn.createStatement(); int rowcount = stmt.executeUpdate ("delete from ACME_RENTAL_ITEMS where rental_id = 1011"); Step 3 : Executing SQL (2/2) • Execute a select statement  Execute a delete statement

Executing Statements • Obtain a statement object from the connection: – Statement stmt = con.createStatement (); • Execute the SQL statements: – stmt.executeUpdate(“update table set field=‘value’”); – stmt.executeUpdate(“INSERT INTO mytable VALUES (1, ‘name’)”); – stmt.executeQuery(“SELECT * FROM mytable”);

JDBC Sequence diagram creates Statements for database actions selects a specific Connection type and instantiates it

Step 4 : Processing the Results (1/2) • JDBC returns the results of a query in a ResultSet object – ResultSet object contains all of the rows which satisfied the conditions in an SQL statement • A ResultSet object maintains a cursor pointing to its current row of data – Use next() to step through the result set row by row • next() returns TRUE if there are still remaining records – getString(), getInt(), and getXXX() assign each value to a Java variable Record 1 Record 2 Record 3 Record 4 ResultSet Internal Pointer The internal pointer starts one before the first record

Step 4 : Processing the Results (2/2) • Example Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(“SELECT ID, name, score FROM table1”); While (rs.next()){ int id = rs.getInt(“ID”); String name = rs.getString(“name”); float score = rs.getFloat(“score”); System.out.println(“ID=” + id + “ ” + name + “ ” + score);} NOTE You must step the cursor to the first record before read the results This code will not skip the first record ID name score 1 James 90.5 2 Smith 45.7 3 Donald 80.2 Table1 Output ID=1 James 90.5 ID=2 Smith 45.7 ID=3 Donald 80.2

Step 5 : Closing Database Connection  It is a good idea to close the Statement and Connection objects when you have finished with them  Close the ResultSet object rs.close();  Close the Statement object stmt.close();  Close the connection connection.close();

Retrieving Data • ResultSet rs = stmt.executeQuery(“SELECT id,name FROM employees where id = 1000”) • Some methods used in ResultSet: – next() – getString() – getInt()

Using the Results while (rs.next()) { float s = rs.getInt("id"); String n = rs.getString("name"); System.out.println(s + " " + n); }

Executing SQL Statements • Three different ways of executing SQL statements: – Statement (both static and dynamic SQL statements) – PreparedStatement (semi-static SQL statements) – CallableStatment (stored procedures) PreparedStatement class:Precompiled, parametrized SQL statements: – Structure is fixed – Values of parameters are determined at run-time

The PreparedStatement Object • A PreparedStatement object holds precompiled SQL statements • Use this object for statements you want to execute more than once • A PreparedStatement can contain variables (?) that you supply each time you execute the statement // Create the prepared statement PreparedStatement pstmt = con.prepareStatement(“ UPDATE table1 SET status = ? WHERE id =?”) // Supply values for the variables pstmt.setString (1, “out”); pstmt.setInt(2, id); // Execute the statement pstmt.executeUpdate();

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 = ?");

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();

Executing SQL Statements (cont.) String sql=“INSERT INTO Sailors VALUES(?,?,?,?)”; PreparedStatment pstmt=con.prepareStatement(sql); pstmt.clearParameters(); pstmt.setInt(1,sid); pstmt.setString(2,sname); pstmt.setInt(3, rating); pstmt.setFloat(4,age); // we know that no rows are returned, thus we use executeUpdate() int numRows = pstmt.executeUpdate();

ResultSets • PreparedStatement.executeUpdate only returns the number of affected records • PreparedStatement.executeQuery returns data, encapsulated in a ResultSet object (a cursor) ResultSet rs=pstmt.executeQuery(sql); // rs is now a cursor While (rs.next()) { // process the data }

ResultSets (cont.) A ResultSet is a very powerful cursor: • previous(): moves one row back • absolute(int num): moves to the row with the specified number • relative (int num): moves forward or backward • first() and last()

Matching Java-SQL Data Types getTimestamp() java.sql.TimeStamp TIMESTAMP getTime() java.sql.Time TIME getDate() java.sql.Date DATE getFloat() Double REAL getInt() Integer INTEGER getDouble() Double FLOAT getDouble() Double DOUBLE getString() String VARCHAR getString() String CHAR getBoolean() Boolean BIT ResultSet get method Java class SQL Type

JDBC: Exceptions and Warnings • Most of java.sql can throw and SQLException if an error occurs (use try/catch blocks to find connection problems) • SQLWarning is a subclass of EQLException; not as severe (they are not thrown and their existence has to be explicitly tested)

Transactions and JDBC (1/2) • Transaction: more than one statement that must all succeed (or all fail) together Ex) updating several tables due to customer purchase • If one fails, the system must reverse all previous actions • Also can’t leave DB in inconsistent state halfway through a transaction • COMMIT = complete transaction • ROLLBACK = cancel all actions

Transactions and JDBC (2/2) • The connection has a state called AutoCommit mode – If AutoCommit is true, then every statement is automatically committed – If AutoCommit is false, then every statement is added to an ongoing transaction – Default: true con.setAutoCommit(false); try { PreparedStatement pstmt = con.prepareStatement( "update BankAccount set amount = + ? where accountId = ?"); pstmt.setInt(1,-100); pstmt.setInt(2, 13); pstmt.executeUpdate(); pstmt.setInt(1, 100); pstmt.setInt(2, 72); pstmt.executeUpdate(); con.commit(); catch (SQLException e) { con.rollback(); }

55 JDBC 2 – Scrollable Result Set … Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); String query = “select students from class where type=‘not sleeping’ “; ResultSet rs = stmt.executeQuery( query ); rs.previous(); / / go back in the RS (not possible in JDBC 1…) rs.relative(-5); / / go 5 records back rs.relative(7); / / go 7 records forward rs.absolute(100); / / go to 100th record …

The DatabaseMetaData Object • The Connection object can be used to get a DatabaseMetaData object. • This object provides more than 100 methods to obtain information about the database.

DatabaseMetaData dbmd = conn.getMetaData(); String s1 = dbmd getURL(); String s2 = dbmd.getSQLKeywords(); boolean b1 = dbmd.supportsTransactions(); boolean b2 = dbmd.supportsSelectForUpdate(); How to Obtain Database Metadata 1. Get the DatabaseMetaData object. 2. Use the object’s methods to get the metadata. DatabaseMetaData dbmd = conn.getMetaData();

The ResultSetMetaData Object • The ResultSet object can be used to get a ResultSetMetaData object. • ResultSetMetaData object provides metadata, including: – Number of columns in the result set – Column type – Column name

How to Obtain Result Set Metadata 1. Get the ResultSetMetaData object. 2. Use the object’s methods to get the metadata. ResultSetMetaData rsmd = rset.getMetaData(); for (int i = 0; i < rsmd.getColumnCount(); i++) { String colname = rsmd.getColumnName(i); int coltype = rsmd.getColumnType(i); … } ResultSetMetaData rsmd = rset.getMetaData();

60 Metadata from DB • A Connection's database is able to provide schema information describing its tables, its supported SQL grammar, its stored procedures the capabilities of this connection, and so on – What is a stored procedure? – Group of SQL statements that form a logical unit and perform a particular task This information is made available through a DatabaseMetaData object.

61 Metadata from DB - example … Connection con = …. ; DatabaseMetaData dbmd = con.getMetaData(); String catalog = null; String schema = null; String table = “sys%”; String[ ] types = null; ResultSet rs = dbmd.getTables(catalog , schema , table , types ); …

62 JDBC – Metadata from RS public static void printRS(ResultSet rs) throws SQLException { ResultSetMetaData md = rs.getMetaData(); // get number of columns int nCols = md.getColumnCount(); // print column names for(int i=1; i < nCols; ++i) System.out.print( md.getColumnName( i)+","); / / output resultset while ( rs.next() ) { for(int i=1; i < nCols; ++i) System.out.print( rs.getString( i)+","); System.out.println( rs.getString(nCols) ); } }

The CallableStatement Object • A CallableStatement object holds parameters for calling stored procedures. • A callable statement can contain variables that you supply each time you execute the call. • When the stored procedure returns, computed values (if any) are retrieved through the CallabableStatement object.

CallableStatement cstmt = conn.prepareCall("{call " + ADDITEM + "(?,?,?)}"); cstmt.registerOutParameter(2,Types.INTEGER); cStmt.registerOutParameter(3,Types.DOUBLE); How to Create a Callable Statement • Register the driver and create the database connection. • Create the callable statement, identifying variables with a question mark (?).

1. Set the input parameters. 2. Execute the statement. 3. Get the output parameters. How to Execute a Callable Statement cstmt.setXXX(index, value); cstmt.execute(statement); var = cstmt.getXXX(index);

JDBC 2.0 Features • ResultSet enhancements • Batch updates • Statement Caching • JNDI • Connection Pooling & Caching • Distributed Transactions

ResultSet enhancements • Scrollability – The ability to move backward as well as forward through a result set. – The ability to move to any particular position in the result set – Sensitivity must be specified. Sensitivity can detect whether data is changed or not. – Sensitive or Insensitive Mode • Updatability – Can insert, modify, delete using while navigating a resultset

6 Types of ResultSet • forward-only/read-only • forward-only/updatable • scroll-sensitive/read-only • scroll-sensitive/updatable • scroll-insensitive/read-only • scroll-insensitive/updatable

Scrollable Resultset Scrollable ResultSet next() previous() relative() absolute() first() last() Cursor Table Oracle 8i Statement stmt = conn.createStatemen( ResultSet.TYPE_SCROLL_IN SENSITIVE, ResultSet.CONCURR_READ_ ONLY); ResultSet rset = stmt.executeQuery(); rset.absolute(2); ... Java Program Cache

APIs Statement createStatement (int resultSetType, int resultSetConcurrency) PreparedStatement prepareStatement (String sql, int resultSetType, int resultSetConcurrency) CallableStatement prepareCall (String sql, int resultSetType, int resultSetConcurrency) java.sql.Connection ResultSet.TYPE_FORWARD_ONLY ResultSet.TYPE_SCROLL_INSENSITIVE ResultSet.TYPE_SCROLL_SENSITIVE ResultSet.CONCUR_READ_ONLY ResultSet.CONCUR_UPDATABLE resultSetType resultSetConcurrency

APIs 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 java.sql.ResultSet void deleteRow(int row) throws SQLException void updateXXX(int idx, XXX x) throws SQLException void updateRow() throws SQLException void moveToInsertRow () throws SQLException void moveToCurrentRow() throws SQLException void insertRow() 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")); } ...

Example : delete row ... rs.absolute(5); rs.deleteRow(); ...

Example : update row Statement stmt = conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery("SELECT empno, sal FROM emp"); if (rs.absolute(10)) // (returns false if row does not exist) { rs.updateString(1, "28959"); rs.updateFloat("sal", 100000.0f); rs.updateRow(); } // Changes will be made permanent with the next COMMIT operation. ...

Example : insert row ... Statement stmt = conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery("SELECT empno, sal FROM emp"); rs.moveToInsertRow(); rs.updateString(1, "28959"); rs.updateFloat("sal", 100000.0f); rs.insertRow(); // Changes will be made permanent with the next COMMIT operation. rs.moveToCurrentRow(); // Go back to where we came from... ...

JDBC java for learning java for learn.ppt

More Related Content

Similar to JDBC java for learning java for learn.ppt

Recently uploaded

JDBC java for learning java for learn.ppt