JDBC JDBC is Java API twhich allows the
JDBC
• JDBC is Java API twhich allows the Java programmers to access database management system from Java code. • It is a java API which enables the java programs to execute SQL statements. • Defines how a java programmer can access the database in tabular format from Java code using a set of standard interfaces and classes written in the Java programming language. • JDBC provides methods for querying and updating the data in Relational Database Management system such as SQL, Oracle etc
JDBC Driver Types • • Type 1 JDBC-to-ODBC Driver. Type 2 Java/Native Code Driver. Type 3 JDBC Driver. Type 4 JDBC Driver.
Type 1 JDBC-to-ODBC Driver The JDBC type 1 driver, also known as the JDBC-ODBC bridge is a database driver implementation that employs the ODBC driver to connect to the database. The driver converts JDBC method calls into ODBC function calls. The driver is implemented in the sun. jdbc. odbc. Jdbc. Odbc. Driver class and comes with the Java 2 SDK, Standard Edition. The driver is platform-dependent as it makes use of ODBC which in turn depends on native libraries of the operating system. Also, using this driver has got other dependencies such as ODBC must be installed on the computer having the driver and the database which is being connected to must support an ODBC driver. Type 1 is the simplest of all but platform specific i. e only to Microsoft platform. Type 1 drivers are "bridge" drivers. They use another technology such as Open Database Connectivity (ODBC) to communicate with a database. This is an advantage because ODBC drivers exist for many Relational Database Management System (RDBMS) platforms. A Type 1 driver needs to have the bridge driver installed and configured before JDBC can be used with it. This can be a serious drawback for a production application.
Functions: • Translates query obtained by JDBC into corresponding ODBC query, which is then handled by the ODBC driver. • Sun provides a JDBC-ODBC Bridge driver. sun. jdbc. odbc. Jdbc. Odbc. Driver. • Client -> JDBC Driver -> ODBC Driver -> Database • There is some overhead associated with the translation work to go from JDBC to ODBC. Advantages: Almost any database for which ODBC driver is installed, can be accessed. Disadvantages: • Performance overhead since the calls have to go through the JDBC overhead bridge to the ODBC driver, then to the native database connectivity interface. • The ODBC driver needs to be installed on the client machine.
Type 1 JDBC Driver
Type 2 Java/Native Code Driver • The JDBC type 2 driver, also known as the Native-API driver is a database driver implementation that uses the client-side libraries of the database. • The driver converts JDBC method calls into native calls of the database API. • A native-API partly Java technology-enabled driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB 2, or other DBMS. • Type 2 drivers use a native API to communicate with a database system.
Functions: • This type of driver converts JDBC calls into calls to the client API for that database. • Client -> JDBC Driver -> Vendor Client DB Library -> Database Advantage Better performance than Type 1 since no jdbc to odbc translation is needed. Disadvantages • The vendor client library needs to be installed on the client machine. • Not all databases give the client side library.
Type 2 JDBC Driver
Type 3 JDBC Driver • The JDBC type 3 driver, also known as the network-protocol driver is a database driver implementation which makes use of a middle-tier between the calling program and the database. • The middle-tier (application server) converts JDBC calls directly or indirectly into the vendor-specific database protocol. • The same driver can be used for multiple databases. It depends on the number of databases the middleware has been configured to support. • Making use of the middleware provides additional advantages of security and firewall access. • A net-protocol fully Java technology-enabled driver translates JDBC API calls into a DBMS-independent net protocol which is then translated to a DBMS protocol by a server.
Functions: • Follows a three tier communication approach. • Can interface to multiple databases - Not vendor specific. • The JDBC Client driver written in java, communicates with a middleware-net-server using a database independent protocol, and then this net server translates this request into database commands for that database. • Thus the client driver to middleware communication is database independent. • Client -> JDBC Driver -> Middleware-Net Server -> Any Database • • Advantages Since the communication between client and the middleware server is database independent, there is no need for the vendor db library on the client machine. Also the client to middleware need'nt be changed for a new database. eg. Weblogic. • At client side a single driver can handle any database. (It works provided the middlware supports that database) Disadvantages • Requires database-specific coding to be done in the middle tier. • An extra layer added may result in a time-bottleneck. But typically this is overcome by providing efficient middleware
Type 3 JDBC Driver
Type 4 JDBC Driver • The JDBC type 4 driver, also known as the native-protocol driver is a database driver implementation that converts JDBC calls directly into the vendor-specific database protocol. • The type 4 driver is written completely in Java and is hence platform independent. • Since many of these protocols are proprietary the database vendors themselves will be the primary source for this style of driver.
Functions • Type 4 drivers are entirely written in Java that communicate directly with a vendor's database. No translation or middleware layers, are required, improving performance. • The driver converts JDBC calls into the vendor-specific database protocol so that client applications can communicate directly with the database server. • Completely implemented in Java to achieve platform independence. • e. g include the widely used Oracle thin driver - oracle. jdbc. driver. Oracle. Driver which connect to jdbc: oracle: thin URL format. • Client Machine -> Native protocol JDBC Driver -> Database server Advantages These drivers don't translate the requests into db request to ODBC or pass it to client api for the db, nor do they need a middleware layer for request indirection. Thus the performance is considerably improved. Disadvantage At client side, a separate driver is needed for each database.
Type 4 JDBC Driver
JDBC Drivers JDBC Type I “Bridge” ODBC Type II “Native” . lib Type III “Middleware” Type IV “Pure” ODBC Driver Middleware Server
Brief Overview of the JDBC Process Ø Loading the JDBC driver Ø Connecting to the DBMS Ø Creating and executing a statement. Ø Processing data returned by the DBMS Ø Terminating the connection with the DBMS
Loading the JDBC driver The JDBC driver must be loaded before connect to the DBMS. The driver is loaded by calling the Class. for. Name() method and passing it the name of the driver. Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver");
Connecting to the DBMS Once the driver is loaded, connect to the DBMS using method Driver. Manager. get. Connection() The Driver. Manager. get. Connection() method is passed the URL of the database, and the user. ID and password if required by the DBMS. The URL is a String object that contains the driver name and the name of the database that is being accessed. The Driver. Manager. get. Connection() method returns a Connection interface that is used throughout the process to reference the database.
String url =“jdbc: odbc: Customer. Information”; String user. ID=“sjec”; String password = “comp” try { Class. for. Name(“sun. jdbc. odbc. Jdbc. Odbc. Drive”); Connection conn = Driver. Manager. get. Connection(url, user. ID, password); }
Creating and executing a statement. The con. create. Statement() method is used to create a statement object. The statement object is then used to execute a query and return a resultset object that contains the response from the DBMS. Statement stmt; Result. Set rs; Try { String query = “SELECT * FROM customers”; stmt = con. create. Statement(); rs = stmt. execute. Query(query); con. close(); }
Processing data returned by the DBMS The java. sql. Result. Set object is assigned the results received from the DBMS after the query is processed. The java. sql. Result. Set object consists of methods used to interact with data. Result. Set rs; String First. Name; String Last. Name; while(rs. next()); { First. Name = rs. get. String(First. Name); Last. Name = rs. get. String(Last. Name); System. out. println(First. Name+” “+Last. Name); }
Terminating the connection with the DBMS The connection to the DBMS is terminated by using close() method of the Connection object once finished accessing the DBMS. con. close();
JDBC Architecture Java Application JDBC Driver Manager (T 1) JDBCODBC Bridge ODBC Driver (T 2) Java Portion Native Portion (T 3) Java Client Server Component Proprietary, vendor-specific database access protocol (T 4) Java Driver to vendor's protocol JDBC Drivers Provided with Sun's JDK
Database Connection Before the connection is made, the JDBC driver must be loaded and registered with the Driver. Manager. The purpose of loading and registering the JDBC driver is to bring the JDBC driver into the JVM. The JDBC driver is automatically registered with the Driver. Manager once the JDBC driver is loaded and is therefore available to the JVM and can be used. The Class. for. Name() methods throws a Class. Not. Found. Exception. try { Class. for. Name(“sun. jdbc. odbc. Jdbc. Odbc. Drive”); } catch(Class. Not. Found. Exception error) { System. out. println(“Unable to load JDBC/ODBC bridge”+error. get. Message()); }
The Connection • After the JDBC driver is successfully loaded and registered, connect to the database. • A Connection object represents a connection with a database. When we connect to a database by using connection method, we create a Connection Object, which represents the connection to the database. • The datasource that the JDBC component will connect to is defined using the URL format. The URL consist of 3 parts. These are jdbc which indicates that the JDBC protocol is to be used to read the URL. <subprotocol> which is the JDBc driver name. <subname> which is the name of the database. ex: String url = “jdbc: odbc: sjec”;
The connection to the databse is established by using one of the 3 get. Connection method. get. Connection(String url) get. Connection(String url, String id, String password); get. Connection(String url, Properties prop); Ex: get. Connection(“jdbc: odbc: sjec”, ”cse”, ”sem 7”);
• • The get. Connection() method request access to the database from the DBMS. It is up to the DBMS to grant or reject access. A connection object is returned by the get. Connection() method if access is granted. otherwise get. Connection() method throws a SQLException. Sometimes the DBMS grants access to a database to anyone. In this case get. Connection(String url) method is used.
String url = “jdbc: odbc: sjec”; try { Class. for. Name(“sun. jdbc. odbc. Jdbc. Odbc. Drive”); conn = Driver. Manager. get. Connection(url) } catch(Class. Not. Found. Exception error) { System. out. println(“Unable to load JDBC/ODBC bridge”+error. get. Message()); } catch(SQLException error) { System. out. println(“can not connect to the datanase”+error. get. Message()); }
Some databases limit access to authorized users and require to supply a user. ID and password. String url =“jdbc: odbc: Customer. Information”; String user. ID=“blde”; String password = “cse” Private Connection conn; try { Class. for. Name(“sun. jdbc. odbc. Jdbc. Odbc. Drive”); conn = Driver. Manager. get. Connection(url, user. ID, password); } catch(Class. Not. Found. Exception error) { System. out. println(“Unable to load JDBC/ODBC bridge”+error. get. Message()); } catch(SQLException error { System. out. println(“can not connect to the datanase”+error. get. Message()); }
Some DBMS requires additional information. This additional information is referred to as properties and must be associated with a Properties object, which is passed to the DBMS as a get. Connection() parameter. Connection con ; Properties props = new Properties(); try { File. Input. Stream fis = new File. Input. Stream(“DBProps. txt”); props. load(fis); } catch(IOException err) { System. out. println(“error loading property file”); } try { Class. for. Name(“sun. jdbc. odbc. Jdbc. Odbc. Drive”); con = Driver. Manager. get. Connection(url, props); } catch(Class. Not. Found. Exception error) { System. out. println(“Unable to load JDBC/ODBC bridge”+error. get. Message()); } catch(SQLException error) { System. out. println(“can not connect to the datanase”+error. get. Message()); }
Associating the JDBC/ODBC bridge with the Database 1 2 3 4 5 6 select start | settings | control panel select ODBC 32 to display the ODBC Data. Source. Administrator Add new user by selecting the add button select the driver and then finish Enter the name of the datasourcename. …. . .
Associating the JDBC/ODBC bridge with the Database 1. Make sure you have the Access 95 ODBC drivers installed. These ODBC drivers can be installed from the. Access install program. 2. Select Start Menu|Settings|Control Panels. 3. Click on 32 bit ODBC. 4. Click on the Add button and choose the Access Driver. 5. Type in a Data Source Name and Description (anything you like). 6. In the Database area, click on Select. 7. Select the Access database file; a sample database is located in MSofficeACCESSSamples (if you installed it during the Access installation). However, you can specify any Access database you want. 8. You may want to click on the Advanced button and set the Username and Password. Click on OK and then on Close to complete the configuration
Statement Objects Once a connection to the database is opened, then create and sends a query to access data contained in the database. The query is written SQL. One of the 3 types of Statement objects is used to execute the query. 1 Statement which executes a query immediately. 2 Prepaped. Statement which is used to execute a compiled query 3 Callable. Statement, which is used to execute stored procedures.
The Statement Object • It is used whenever a J 2 EE component needs to immediately execute a query without first having the query compiled. • There are 3 methods in Statement Object 1. execute. Query() : Returns one Result. Set object that contains rows, columns and metadata. 2. execute. Update(); method is used to execute queries that contain UPDATE and DELETE SQL statement. 3. execute(); method is used when there may be multiple results returned. file: ///C: Javajdk 1. 6. 0_12binstatement. java
Prepared. Statement Object • A SQL query must be compiled before the DBMS processes the query. • A SQL query can be precompiled and executed by using the Prepared. Statement object. • Here we use a question mark as place holder for a value that is inserted into the query after the query is compiled. This value changes each time the query is executed. • We use prepared. Statement() method to replace the question mark value with the value passed to the set. XXX() method. • The set. XXX() method takes two parameters. First is an integer that identifies question mark place holder. And second is value that replaces question mark placeholder. • file: ///C: Javajdk 1. 6. 0_12binPrepared. Statement. java
Callable. Statement • The Callable. Statement object is used to call a stored procedure. • The Callable. Statement object uses 3 types of parameters when calling a stored procedure. These parameters are IN, OUT and INOUT. • The IN parameter contain any data that needs to be passed to the stored procedure and whose value is assigned using the setxxx() method. • The OUT parameter contain the value returned by the stored procedure. The OUT parameter must be registered using the register. Out. Parameter() and is retrieved by getxxx() method. • The INOUT parameter is a single parameter used to both pass information to the stored procedure and retrieve information from a stored procedure. The register. Out. Parameter () requires 2 parameters. The first parameter is the integer that represents the number of the parameter. The second parameter is the data type of the value returned by the stored procedure. • file: ///C: Javajdk 1. 6. 0_12binCallable. Statement. java
Result. Set • The Result. Set object contains methods that are used to copy data from the Result into a Java variable for further processing. • Data in a Result. Set object is logically organized into a virtual table consisting of rows and column. • Resultset object also contains metadata such as column names, column size, and column data types. • The Result. Set uses a virtual curser to point to a row of the virtual table. • We must move the virtual curser to each row and use other methods of the Result. Set object to interact with the data stored in columns of that row. • The virtual curser is positioned above the first row of the data when the Result. Set is returned by the execute. Query() method. This means that virtual curser must be moved to the first row using next() method.
• The next() method returns a boolean true if the row contains a data. Otherwise, a boolean false is returned indicating that no more rows exist in the Result. Set. • Once the virtual curser points to a row, the getxxx() method is used to copy data from the row to a collection , object or variable. • The getxxx() parameter requires one parameter, which is an integer that represent he number of column that contains the data. Ex: get. Int(1); get. String(2); get. Int(3);
String url =“jdbc: odbc: Customer. Information”; String user. ID=“blde”; String password = “ise” Private Connection conn; try { Class. for. Name(“sun. jdbc. odbc. Jdbc. Odbc. Drive”); conn = Driver. Manager. get. Connection(url, user. ID, password); } catch(Class. Not. Found. Exception error) { System. out. println(“Unable to load JDBC/ODBC bridge”+error. get. Message()); } catch(SQLException error) { System. out. println(“can not connect to the datanase”+error. get. Message()); }
try { String query = “SELECT First. Name, Last. Name FROM Customers”; stmt = con. create. Statement(); rs = stmt. execute. Update(query); con. close(); } catch(SQLException error) { System. out. priintln( “cannot connect to the database”); } try { while(rs. next()) { First. Name = rs. get. String(1); Last. Name = rs. get. String(2); System. out. println(First. Name+” “+Last. Name); } } Catch(SQLException error) { System. out. println(“data entry error”); }
Scrollable Result. Set There are 6 methods of the Result. Set object that are used to position the virtual cluster. next() previous() first() last() absolute() relative() get. Row() The absolute method positions the virtual curster at the row specified by the integer passed as a parameter to the absolute() method. The relative() method moves the virtual curser the specified number of rows contained in the parameter. The parameter is a positive or negative integer where the sign represents the direction the virtual curser is moved. get. Row() method returns an integer that represents the number of current rows in the Result. Set.
The Statement object that is created using the create. Statement() of the Connection object must be setup to handle a scrollable Result. Set by passing the create. Statement() method one of three constant. TYPE_FORWARD_ONLY TYPE_SCROLL_INSENSITIVE TYPE_SCROLL_SENSITIVE. TYPE_FOREWARD_ONLY is the default setting. The TYPE_SCROLL_INSENSITIVE constant makes the Resultset insensitive to the changes made by another component.
String url =“jdbc: odbc: Customer. Information”; String user. ID=“blde”; String password = “ise” Private Connection conn; try { Class. for. Name(“sun. jdbc. odbc. Jdbc. Odbc. Drive”); conn = Driver. Manager. get. Connection(url, user. ID, password); } catch(Class. Not. Found. Exception error) { System. out. println(“Unable to load JDBC/ODBC bridge”+error. get. Message()); } catch(SQLException error) { System. out. println(“can not connect to the datanase”+error. get. Message()); }
try { String query = “SELECT First. Name, Last. Name FROM Customers”; stmt = con. create. Statement(TYPE_SCROLL_INSENSITIVE); rs = stmt. execute. Update(query); con. close(); } catch(SQLException error) { System. out. priintln( “cannot connect to the database”); } try { while(rs. next()) { rs. last(); rs. first(); First. Name = rs. get. String(1); Last. Name = rs. get. String(2); System. out. println(First. Name+” “+Last. Name); } } catch(SQLException error) { System. out. println(“data entry error”); }
Updatable Result. Set Rows contained in the Result. Set can be updatable by passing the create. Statemenet() method of the Connection object the CONQUER_UPDATABLE. Alternatively , the CONQUER_READ_ONLY constant can be passed to the create. Statement method to prevent the Result. Set from being updated. There are 3 ways in which a Result. Set can be channged. 1 updating values in a row 2 deleting a row 3 inserting a new row.
Update Result. Set • Once a execute. Query method of the Statement object returns a Result. Set, the updatexxx() method is used to change the value of a column in the current row of the resultset. • The updatexxx method required 2 parameters. • The update Row() method is called after all the updatexxx() methods are called. • The changes only occur in the Result. Set. The corresponding row in the table remains unchanged.
Update Result. Set String url = "jdbc: odbc: Customer. Information"; String user. ID = "jim"; String password = "keogh"; Statement st; Result. Set rs; Connection con; try { Class. for. Name( "sun. jdbc. odbc. Jdbc. Odbc. Driver"); con = Driver. Manager. get. Connection(url, user. ID, password); } catch (Class. Not. Found. Exception error) { System. err. println("Unable to load the JDBC/ODBC bridge. " + error); System. exit(1); } catch (SQLException error) { System. err. println("Cannot connect to the database. " + error); System. exit(2); }
try { String query = "SELECT First. Name, Last. Name FROM Customers WHERE First. Name = 'Mary' and Last. Name = 'Smith'"; st = con. create. Statement(Result. Set. CONCUR_UPDATABLE); rs = st. execute. Query (query); } catch ( SQLException error ) { System. err. println("SQL error. " + error); System. exit(3); } boolean Records = Results. next(); try { Results. update. String ("Last. Name", "Smith"); Results. update. Row(); st. close(); } catch (SQLException error ) { System. err. println("Data display error. " + error); System. exit(5); }
Delete Row in the Result. Set. • To delete a row from Resultset delete. Row() method is used. rs. delete. Row(10) Or rs. absolute(10); rs. delete. Row()
Insert Row in the Result. Set • • Similar to update the Resultset. The insert. Row method is called after the updatexxx() method. String url = "jdbc: odbc: Customer. Information"; String user. ID = "jim"; String password = "keogh"; Statement st Result. Set rs; Connection con; try { Class. for. Name( "sun. jdbc. odbc. Jdbc. Odbc. Driver"); con = Driver. Manager. get. Connection(url, user. ID, password); } catch (Class. Not. Found. Exception error) { System. out. println("Unable to load the JDBC/ODBC bridge. " + error); System. exit(1); } catch (SQLException error) { System. out. println("Cannot connect to the database. " + error); System. exit(2); }
try { String query = "SELECT First. Name, Last. Name FROM Customers"; st = con. create. Statement(CONCUR_UPDATABLE); rs = Data. Request. execute. Query (query); } catch ( SQLException error ) { System. out. println("SQL error. " + error); System. exit(3); } boolean Records = rs. next(); try { rs. update. String (1, "Tom"); rs. update. String (2, "Smith"); rs. insert. Row(); rs. close(); } catch (SQLException error ) { System. out. println("Data display error. " + error); System. exit(5); }
Transaction Processing A database transaction consist of a set of SQL statements, each of which must be successfully completed for the transaction to be completed. If one fails, SQL statements that executed successfully up to that point in the transaction must be rolled back. A database transaction is not completed until the commit() method of the Connection object is called. All SQL statements executed prior to the call to the commit() method can be rolled back. However once the commit() method is called, none of the SQL statements can be rolled back. The commit() method must be called regardless if the SQL statements is part of a transaction or not. commit method is automatically called , because DBMS has an Auto. Commit features that is by default set to true.
While is processing a transaction, the Auto. Commit features must be deactivated by calling set. Auto. Commit(false); Once the transaction is completed, the set. Auto. Commit(true) is called again to reactivate the Auto. Commit feature.
String url = "jdbc: odbc: Customer. Information"; String user. ID = "jim"; String password = "keogh"; Statement st 1, st 2 ; Connection con; try { Class. for. Name( "sun. jdbc. odbc. Jdbc. Odbc. Driver"); con = Driver. Manager. get. Connection(url, user. ID, password); } catch (Class. Not. Found. Exception error) { System. out. println("Unable to load the JDBC/ODBC bridge. " + error); System. exit(1); } catch (SQLException error) { System. out. println("Cannot connect to the database. " + error); System. exit(2); }
try { con. set. Auto. Commit(false) String query 1 = "UPDATE Customers SET Street = '5 Main Street' " + "WHERE First. Name = 'Bob'"; String query 2 = "UPDATE Customers SET Street = '10 Main Street' " + "WHERE First. Name = 'Tim'"; st 1= con. create. Statement(); st 2= con. create. Statement(); st 1. execute. Update (query 1 ); st 2. execute. Update (query 2 ); con. commit(); st 1. close(); st 2. close(); con. close(); }
catch(SQLException ex) { System. out. println("SQLException: " + ex. get. Message()); if (con != null) { try { System. out. println("Transaction is being rolled back "); con. rollback(); } catch(SQLException excep) { System. out. print("SQLException: "); System. out. println(excep. get. Message()); } } }
• A transaction may consist of many tasks, some of which don’t need to be rolled back should the entire transaction fail. • Ø Ø Ø Ex: Consider order processing. These include updating the customer account table inserting the order into the pending order table sending a customer a confirmation email. • Technically, all 3 tasks must be completed before the transaction is considered completed. suppose the email server is down when the transaction is ready to send the customer a confirmation email. Should the entire transaction be rolled back ? Probably not since it is more important that the order continue to be processed(ie delivered). The confirmation notice can be sent once the email server is back online.
Savepoint : • J 2 EE component can control the number of tasks that are rolled back by using savepoint. • A savepoint is a virtual marker that defines the task at which the rollback stops. • There can be many savepoints used in a transaction. • Each savepoint is identified by a unique name. Savepoint s 1 = con. set. Savepoint ("sp 1"); The savepoint name is then passed to the rollback() methods to specify the point within the transaction where the rollback is to stop. The release. Savepoint() method is called to remove the savepoint from the transaction. The name of the savepoint that is to be removed is passed to the release. Savepoint() method.
String url = "jdbc: odbc: Customer. Information"; String user. ID = "jim"; String password = "keogh"; Statement st 1, st 2 ; Connection con; try { Class. for. Name( "sun. jdbc. odbc. Jdbc. Odbc. Driver"); con = Driver. Manager. get. Connection(url, user. ID, password); } catch (Class. Not. Found. Exception error) { System. out. println("Unable to load the JDBC/ODBC bridge. " + error); System. exit(1); } catch (SQLException error) { System. out. println("Cannot connect to the database. " + error); System. exit(2); }
try { con. set. Auto. Commit(false) String query 1 = "UPDATE Customers SET Street = '5 Main Street' WHERE First. Name = 'Bob'"; String query 2 = "UPDATE Customers SET Street = '10 Main Street‘ WHERE First. Name = 'Tim'"; st 1= con. create. Statement(); st 1. execute. Update (query 1); Savepoint s 1 = con. set. Savepoint ("sp 1"); st 2= con. create. Statement(); st 2. execute. Update (query 2); con. commit(); st 1. close(); st 2. close(); con. release. Savepoint ("sp 1"); con. close(); } catch ( SQLException error ){ try { con. rollback(sp 1); } catch ( SQLException error ){ System. out. println("rollback error. " + error. get. Message()); System. exit(3); } System. out. println("SQL error. " + error. get. Message()); ); System. exit(4); }
add. Batch method : • Another way to combine SQL statement into a transaction is to batch together these statements into a single transaction and then execute the entire transaction. • We can do this by using the add. Batch() method of the Statement object. • The add. Batch() method receives a SQL statement as a parameter and places the SQL statement in the batch. • Once all the SQL statements that comprise the transaction are included in the batch, the execute. Batch() method is called to execute the enire batch at the same time. • The execute. Batch method() returns an int array that contains the number of SQL statements that were executed successfully. • The batch can be cleared of SQL statements by using the clear. Batch() method. • The transaction must be committed using the commit() method.
String url = "jdbc: odbc: Customer. Information"; String user. ID = "jim"; String password = "keogh"; Statement st; Connection con; try { Class. for. Name( "sun. jdbc. odbc. Jdbc. Odbc. Driver"); con = Driver. Manager. get. Connection(url, user. ID, password); } catch (Class. Not. Found. Exception error) { System. out. println("Unable to load the JDBC/ODBC bridge. " + error); System. exit(1); } catch (SQLException error) { System. out. println("Cannot connect to the database. " + error); System. exit(2); }
try { con. set. Auto. Commit(false) String query 1 = "UPDATE Customers SET Street = '5 Main Street‘ WHERE First. Name = 'Bob'"; String query 2 = "UPDATE Customers SET Street = '10 Main Street‘ WHERE First. Name = 'Tim'"; st= con. create. Statement(); st. add. Batch(query 1); st. add. Batch(query 2); int [ ] updated = st. execute. Batch (); con. commit(); st 1. close(); st 2. close(); con. close(); } catch(Batch. Update. Exception error) { System. out. println("Batch error. "); System. out. println("SQL State: " + error. get. SQLState()); System. out. println("Message: " + error. get. Message()); System. out. println("Vendor: " + error. get. Error. Code());
int [ ] updated = error. get. Updatecount(); int count = updated. length(); for( int i = 0; i < count; i++) { System. out. print (updated[i]); } SQLException sql = error; While (sql != null) { System. out. println("SQL error " + sql); sql = sql. getnext. Exception(); } try{ st. clear. Batch(); } catch(Batch. Update. Exception error) { System. out. println("Unable to clear the batch: " + error. get. Message()); } }
Result. Set Holdability • Whenever the commit() method is called, all Result. Set objects that were created for the transaction are closed. • We can control whether or not Result. Set objects are closed by passing following parameter to the create. Statement() HOLD_CURSORS_OVER_COMMIT CLOSE_CURSORS_AT_COMMIT
Row. Sets • The JDBC Row. Sets object is used to encapsulate a Result. Set for use with EJB. • A Row. Set object contains rows of data from the table that can be used in a disconnected operation.
Auto-Generated Keys • DBMS automatically generate unique keys for a table as rows are inserted into the table. • The get. Generated. Keys() method of the Statement object is called to return keys genetated by the DBMS.
Meta. Data • Metadata is data about data. • The Database. Meta. Data interface is used to retrieve information about databases, table, column, and indexes among other information about the DBMS. • Metadata about the database is retrieved by calling get. Meta. Data() of Connection object. • The get. Meta. Data() method returns a Database. Meta. Data object that contains information about the database and its component. • Database. Meta. Data object contains following methods. get. Database. Product. Name() get. User. Name() get. URL() get. Schemas() get. Primary. Keys() get. Procedures() get. Tables()
Result. Set Metadata There are 2 types of metadata that can be retrieved from the DBMS. • metadata that describes the database • metadata that describes Result. Set Metadata that describes the Result. Set is retrieved by calling the get. Meta. Data() method of the Result. Set object. This returns a Result. Set. Meta. Data object. Result. Set. Meta. Data rm = rs. get. Meta. Data(); Result. Set. Meta. Data objects provides following methods. get. Column. Count() get. Column. Name(int number); get. Column. Type(int number);
Exceptions There are 3 kinds of exceptions that are thrown by JDBC methods. These are SQLException SQLWarnings Data. Truncation. SQLExceptions commonly reflect a SQL syntax error in a query. This exception is mostly caused by connectivity issues with the database. The SQLWarning throws warnings received by the Connection from the DBMS. Whenever data is lost due to truncation of the data value, a Data. Truncation exception is thrown.
• execute() - will do the querying the database & also the update, insert, delete on the database. • execute. Update() - only the update, insert, delete on the database. • execute. Query() - only the querying the database. • • -------------------execute()- is for invoking the functions or stored procedures of SQL by the Callable. Statement. • execute. Update()- is for the operations such as insert, update or delete on SQL by Prepared. Statement, Statement. • • • execute. Query() - is for operation select of Sql by Prepared. Statement or Statement. --------------------execute. Update() is for non selecting statements execute. Query() is for selecting statements. ----------------------------------
• boolean execute() Executes the SQL statement in this Prepared. Statement object, which may be any kind of SQL statement. • Result. Set execute. Query() Executes the SQL query in this Prepared. Statement object and returns the Result. Set object generated by the query. • int execute. Update() Executes the SQL statement in this Prepared. Statement object, which must be an SQL INSERT, UPDATE or DELETE statement; or an SQL statement that returns nothing, such as a DDL statement.
• import java. util. *; String URL = "jdbc: oracle: thin: @amrood: 1521: EMP"; Properties info = new Properties( ); info. put( "user", "username" ); info. put( "password", "password" ); Connection conn = Driver. Manager. get. Connection(URL, info);
- Slides: 75