JDBC Best Practices are some coding practices which Java programmer should follow while writing JDBC code. As discussed inhow to connect to Oracle database from Java,JDBC API is used to connect and interact with a Database management System.We have touched some of the JDBC best practices in our last article 4 JDBC Performance tips, On which we have discussed simple tips to improve performance of Java application with database. By using JDBC you can execute DDL, DML and Stored Procedures. JDBC Best practices is probably most significant set of coding practices in Java because it significantly affect performance of Java application. I have seen substantial performance gain by simply following common JDBC best practices like running queries with auto commit mode disable. One of the query which we used in our example of JDBC Batch update was taking almost 30 second to finish with auto commit mode enabled but it just took under one second with auto commit mode disable and using explicit commit. This JDBC tutorial is collection of such practices which help you to write better JDBC code and in most cases result in improved performance.
1) What are the steps involved in establishing a connection? ANSWER : This involves two steps: (1) loading the driver and (2) making the connection. 2) How can you load the drivers? ANSWER : Loading the driver or drivers you want to use is very simple and involves just one line of code. If, for example, you want to use the JDBC-ODBC Bridge driver, the following code will load it: Eg. Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Your driver documentation will give you the class name to use. For instance, if the class name is jdbc.DriverXYZ , you would load the driver with the following line of code: Eg. Class.forName("jdbc.DriverXYZ"); 3) What Class.forName will do while loading drivers? ANSWER : It is used to create an instance of a driver and register it with the DriverManager. When you have loaded a driver, it is available for making a connection with a DBMS. 4) How can you make the connection? ANSWER : In establishing a connection is to have the appropriate driver connect to the DBMS. The following line of code illustrates the general idea: Eg. String url = "jdbc:odbc:Fred"; Connection con = DriverManager.getConnection(url, "Fernanda", "J8"); 5) How can you create JDBC statements? ANSWER : A Statement object is what sends your SQL statement to the DBMS. You simply create a Statement object and then execute it, supplying the appropriate execute method with the SQL statement you want to send. For a SELECT statement, the method to use is executeQuery. For statements that create or modify tables, the method to use is executeUpdate. Eg. It takes an instance of an active connection to create a Statement object. In the following example, we use our Connection object con to create the Statement object stmt : Statement stmt = con.createStatement(); 6) How can you retrieve data from the ResultSet? ANSWER : Step 1. JDBC returns results in a ResultSet object, so we need to declare an instance of the class ResultSet to hold our results. The following code demonstrates declaring the ResultSet object rs. Eg. ResultSet rs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES"); Step2. String s = rs.getString("COF_NAME"); The method getString is invoked on the ResultSet object rs , so getString will retrieve (get) the value stored in the column COF_NAME in the current row of rs 7) What are the different types of Statements? ANSWER : 1.Statement (use createStatement method) 2. Prepared Statement (Use prepareStatement method) and 3. Callable Statement (Use prepareCall) 8) How can you use PreparedStatement? ANSWER : This special type of statement is derived from the more general class, Statement.If you want to execute a Statement object many times, it will normally reduce execution time to use a PreparedStatement object instead. The advantage to this is that in most cases, this SQL statement will be sent to the DBMS right away, where it will be compiled. As a result, the PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement 's SQL statement without having to compile it first. Eg. PreparedStatement updateSales = con.prepareStatement("UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?"); 9) What setAutoCommit does? ANSWER : When a connection is created, it is in auto-commit mode. This means that each individual SQL statement is treated as a transaction and will be automatically committed right after it is executed. The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode Eg. con.setAutoCommit(false); Once auto-commit mode is disabled, no SQL statements will be committed until you call the method commit explicitly. Eg. con.setAutoCommit(false); PreparedStatement updateSales = con.prepareStatement( "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?"); updateSales.setInt(1, 50); updateSales.setString(2, "Colombian"); updateSales.executeUpdate(); PreparedStatement updateTotal = con.prepareStatement("UPDATE COFFEES SET TOTAL = TOTAL + ? WHERE COF_NAME LIKE ?"); updateTotal.setInt(1, 50); updateTotal.setString(2, "Colombian"); updateTotal.executeUpdate(); con.commit(); con.setAutoCommit(true); 10) How to call a Strored Procedure from JDBC? ANSWER : The first step is to create a CallableStatement object. As with Statement an and PreparedStatement objects, this is done with an open Connection object. A CallableStatement object contains a call to a stored procedure; Eg. CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}"); ResultSet rs = cs.executeQuery(); 11) How to Retrieve Warnings? ANSWER : SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warnings do not stop the execution of an application, as exceptions do; they simply alert the user that something did not happen as planned. A warning can be reported on a Connection object, a Statement object (including PreparedStatement and CallableStatement objects), or a ResultSet object. Each of these classes has a getWarnings method, which you must invoke in order to see the first warning reported on the calling object Eg. SQLWarning warning = stmt.getWarnings(); if (warning != null) { System.out.println("\n---Warning---\n"); while (warning != null) { System.out.println("Message: " + warning.getMessage()); System.out.println("SQLState: " + warning.getSQLState()); System.out.print("Vendor error code: "); System.out.println(warning.getErrorCode()); System.out.println(""); warning = warning.getNextWarning(); } } 12) How can you Move the Cursor in Scrollable Result Sets ? ANSWER : One of the new features in the JDBC 2.0 API is the ability to move a result set's cursor backward as well as forward. There are also methods that let you move the cursor to a particular row and check the position of the cursor. Eg. Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet srs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES"); The first argument is one of three constants added to the ResultSet API to indicate the type of a ResultSet object: TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE , and TYPE_SCROLL_SENSITIVE . The second argument is one of two ResultSet constants for specifying whether a result set is read-only or updatable: CONCUR_READ_ONLY and CONCUR_UPDATABLE . The point to remember here is that if you specify a type, you must also specify whether it is read-only or updatable. Also, you must specify the type first, and because both parameters are of type int , the compiler will not complain if you switch the order. Specifying the constant TYPE_FORWARD_ONLY creates a nonscrollable result set, that is, one in which the cursor moves only forward. If you do not specify any constants for the type and updatability of a ResultSet object, you will automatically get one that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY 13) What’s the difference between TYPE_SCROLL_INSENSITIVE , and TYPE_SCROLL_SENSITIVE? ANSWER : You will get a scrollable ResultSet object if you specify one of these ResultSet constants.The difference between the two has to do with whether a result set reflects changes that are made to it while it is open and whether certain methods can be called to detect these changes. Generally speaking, a result set that is TYPE_SCROLL_INSENSITIVE does not reflect changes made while it is still open and one that is TYPE_SCROLL_SENSITIVE does. All three types of result sets will make changes visible if they are closed and then reopened Eg. Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet srs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES"); srs.afterLast(); while (srs.previous()) { String name = srs.getString("COF_NAME"); float price = srs.getFloat("PRICE"); System.out.println(name + " " + price); } 14) How to Make Updates to Updatable Result Sets? ANSWER : Another new feature in the JDBC 2.0 API is the ability to update rows in a result set using methods in the Java programming language rather than having to send an SQL command. But before you can take advantage of this capability, you need to create a ResultSet object that is updatable. In order to do this, you supply the ResultSet constant CONCUR_UPDATABLE to the createStatement method. Eg. Connection con = DriverManager.getConnection("jdbc:mySubprotocol:mySubName"); Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet uprs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");
Difference between java.sql.Time, java.sql.Timestamp and java.sql.Dateis most common JDBC question appearing on many core Java interviews. As JDBC provides three classes java.sql.Date, java.sql.Time and java.sql.Timestamp to represent date and time and you already have java.util.Date which can represent both date and time, this question poses lot of confusion among Java programmer and that’s why this is one of those tricky Java questions which is tough to answer. It becomes really tough if differences between them is not understood correctly. We have already seen some frequently asked or common JDBC questions like why JDBC has java.sql.Date despite java.util.Date and Why use PreparedStatement in Java in our last tutorials and we will see difference between java.sql.Date, java.sql.Time and java.sql.Timestamp in this article. By the way apart from these JDBC interview questions, if you are looking to get most from JDBC you can also see 4 JDBC performance tips and 10 JDBC best practices to follow. Those article not only help you to understand and use JDBC better but also help on interviews. Let’s come back to difference sql time, timestamp and sql date.
Setting JNDI Database Connection pool in Spring and Tomcat is pretty easy. Tomcat server documentation gives enough information on how to setup connection pool in Tomcat 5, 6 or 7. Here we will use Tomcat 7 along with spring framework for creating connection pool in Tomcat server and accessing them in Spring using JNDI code. In our last article we have seen how to setup database connection pool in Spring for core Java application which doesn't run on web server or application server and doesn't have managed J2EE container. but if you are developing web application than its better to use server managed connection pool and access them using JNDI. Spring configuration will be generic and just based on JNDI name of Datasource so it will work on any J2EE Server e.g. glassfish, WebLogic, JBoss or WebSphere until JNDI name is same. Tomcat is my favorite web server and I use it a lot on development and its comes integrated with IDE like Eclipse and Netbeans. I am using it for all test and development purpose, Though beware with java.lang.OutOfMemoryError: PermGen space in tomcat,
It is pretty fast and provides functionality you outlined above. However, you will never be able to return first n rows "while result set is being loaded." although that is a nice functionality, it would be tremendously hard to implement it in the JDBC driver. you have no control of how data is obtained from the database unless you wrote the JDBC driver yourself. therefore, no luck there. you can try to combine some logic to access first n number of rows from the database, but request for n+1, n+2... records would require additional queries to the database which can get overly complex. so try RowSets. I am sure that you will be satisfied with the performance and functionality.
When the session expires, the connection will be closed automatically.
My habit is to get a Connection from a ConnectionPool only when I need it (before of a query), and release it to the pool as soon as I've finished to use it (after the query). I believe it's the best policy.
This Topic is always asked by old timer java developer as few years back Java was focusing on how to best communicate this Database and almost most of the application were facing performance issue with database query execution. Focus on few code snipet and basic understanding of jdbc driver should help you to clear this topic -------------------------------------------------------------------------------------------------------------------------------------- Java Database Connectivity API contains commonly asked Java interview questions. A good understanding of JDBC API is required to understand and leverage many powerful features of Java technology. Here are few important practical questions and answers which can be asked in a Core Java JDBC interview. These are the most commonly asked questions:- 1) what is Index and difference with cluster index?
Non-clustered
The data is present in arbitrary order, but the logical ordering is specified by the index. The data rows may be spread throughout the table regardless of the value of the indexed column or expression. The non-clustered index tree contains the index keys in sorted order, with the leaf level of the index containing the pointer to the record (page and the row number in the data page in page-organized engines; row offset in file-organized engines). In a non-clustered index:
The physical order of the rows is not the same as the index order.
Typically created on non-primary key columns used in JOIN, WHERE, and ORDER BY clauses.
There can be more than one non-clustered index on a database table.
Clustered
Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table. Clustered indices can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected. Since the physical records are in this sort order on disk, the next row item in the sequence is immediately before or after the last one, and so fewer data block reads are required. The primary feature of a clustered index is therefore the ordering of the physical data rows in accordance with the index blocks that point to them. Some databases separate the data and index blocks into separate files, others put two completely different data blocks within the same physical file(s). Create an object where the physical order of rows is the same as the index order of the rows and the bottom (leaf) level of clustered index contains the actual data rows. Good read on index : http://www.orafaq.com/node/1403 2) DB query performance and optimization? Among the various ways to tune the performance of a query are:
Rewriting the query. There is no one way to write a query. If you find that a query with an OUTER JOIN is taking a long time, rewrite it using an INNER JOIN. Or perhaps the WHERE clause is written in such a way that it is causing excessive database read operations.
Normalizing or de-normalizing tables. While normalizing tables results in ideal database design, it often leads to poor querying. If you find that frequently-used queries have to span too many normalized tables, consider some duplication of data. For example, in the Northwind database, the Unit Cost column is present in both the Order Details and Products table.
Adding indexes. Lack of well-defined indexes is a frequent cause of queries taking a long time to execute. Creating indexes will cause a query to lookup the index rather than the table.
Removing indexes. In some cases, it is a poorly defined index that is the cause of slow query execution. For example, an index which does not include the frequently looked up columns in a table. In such a case, it is better to drop the index and recreate it.
3) Benefits of prepared statement? DB execution happens in this steps 1.Parse the incoming SQL query 2. Compile the SQL query 3. Plan/optimize the data acquisition path 4. Execute the optimized query / acquire and return data - statement perform step 1-4 to do the each task and prepared statement will pre execute the 1-3 and perform only 4 for each task. - prepared statements are safe as they safeguard against sql injection on using direct query. - We can bind non standard java objects like blob etc in prepared statement, but not in statement. 4) DB execute plan and how to use it? 5) Foreign key and primary key difference? 6) Materialized view in database?
7) Truncate and delete difference? 8) How to design table? 9) Database transaction management? 10) what is cursor and trigger in dB?
These are very standard API question in interview:
What are available drivers in JDBC
JDBC technology drivers fit into one of four categories:
A JDBC-ODBC bridge provides JDBC API access via one or more ODBC drivers. Note that some ODBC native code and in many cases native database client code must be loaded on each client machine that uses this type of driver. Hence, this kind of driver is generally most appropriate when automatic installation and downloading of a Java technology application is not important. For information on the JDBC-ODBC bridge driver provided by Sun, see JDBC-ODBC Bridge Driver.
A native-API partly Java technology-enabled driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.
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. This net server middleware is able to connect all of its Java technology-based clients to many different databases. The specific protocol used depends on the vendor. In general, this is the most flexible JDBC API alternative. It is likely that all vendors of this solution will provide products suitable for Intranet use. In order for these products to also support Internet access they must handle the additional requirements for security, access through firewalls, etc., that the Web imposes. Several vendors are adding JDBC technology-based drivers to their existing database middleware products.
A native-protocol fully Java technology-enabled driver converts JDBC technology calls into the network protocol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server and is a practical solution for Intranet access. Since many of these protocols are proprietary the database vendors themselves will be the primary source for this style of driver. Several database vendors have these in progress.
What are the types of statements in JDBC?
The JDBC API has 3 Interfaces, (1. Statement, 2. PreparedStatement, 3. CallableStatement ). The key features of these are as follows: Statement
·This interface is used for executing a static SQL statement and returning the results it produces.
·The object of Statement class can be created using Connection.createStatement() method.
PreparedStatement
oA SQL statement is pre-compiled and stored in a PreparedStatement object.
oThis object can then be used to efficiently execute this statement multiple times.
oThe object of PreparedStatement class can be created using Connection.prepareStatement() method. This extends Statement interface.
CallableStatement
oThis interface is used to execute SQL stored procedures.
oThis extends PreparedStatement interface.
oThe object of CallableStatement class can be created using Connection.prepareCall() method.
What is a stored procedure? How to call stored procedure using JDBC API?
Stored procedure is a group of SQL statements that forms a logical unit and performs a particular task. Stored Procedures are used to encapsulate a set of operations or queries to execute on database. Stored procedures can be compiled and executed with different parameters and results and may have any combination of input/output parameters. Stored procedures can be called using CallableStatement class in JDBC API. Below code snippet shows how this can be achieved.
CallableStatement cs = con.prepareCaWhat is Connection pooling? What are the advantages of using a connection pool?
Connection Pooling is a technique used for sharing the server resources among requested clients. It was pioneered by database vendors to allow multiple clients to share a cached set of connection objects that provides access to a database.
Getting connection and disconnecting are costly operation, which affects the application performance, so we should avoid creating multiple connection during multiple database interactions. A pool contains set of Database connections which are already connected, and any client who wants to use it can take it from pool and when done with using it can be returned back to the pool.
Apart from performance this also saves you resources as there may be limited database connections available for your application. How to do database connection using JDBC thin driver ?
This is one of the most commonly asked questions from JDBC fundamentals, and knowing all the steps of JDBC connection is important.
0.import java.sql.*;
1.class JDBCTest {
2.public static void main (String args []) throws Exception
12.ResultSet rs = stmt.executeQuery("select 'Hi' from dual");
13.while (rs.next())
14.System.out.println (rs.getString(1)); // Print col 1 => Hi
15.stmt.close();
16.}
17.}
What does Class.forName() method do? Method forName() is a static method of java.lang.Class. This can be used to dynamically load a class at run-time. Class.forName() loads the class if its not already loaded. It also executes the static block of loaded class. Then this method returns an instance of the loaded class. So a call to Class.forName('MyClass') is going to do following
- Load the class MyClass.
- Execute any static block code of MyClass.
- Return an instance of MyClass.
JDBC Driver loading using Class.forName is a good example of best use of this method. The driver loading is done
0.Class.forName("org.mysql.Driver");
All JDBC Drivers have a static block that registers itself with DriverManager and DriverManager has static initializer method registerDriver() which can be called in a static blocks of Driver class. A MySQL JDBC Driver has a static initializer which looks like this: 1.static {
5.throw new RuntimeException("Can't register driver!");
6.}
7.
Class.forName() loads driver class and executes the static block and the Driver registers itself with the DriverManager.
Which one will you use Statement or PreparedStatement? Or Which one to use when (Statement/PreparedStatement)? Compare PreparedStatement vs Statement.
By Java API definitions: Statement is a object used for executing a static SQL statement and returning the results it produces. PreparedStatement is a SQL statement which is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times. There are few advantages of using PreparedStatements over Statements
1.Since its pre-compiled, Executing the same query multiple times in loop, binding different parameter values each time is faster. (What does pre-compiled statement means? The prepared statement(pre-compiled) concept is not specific to Java, it is a database concept. Statement precompiling means: when you execute a SQL query, database server will prepare a execution plan before executing the actual query, this execution plan will be cached at database server for further execution.)
2.In PreparedStatement the setDate()/setString() methods can be used to escape dates and strings properly, in a database-independent way.
3.SQL injection attacks on a system are virtually impossible when using PreparedStatements
What does setAutoCommit(false) do?
A JDBC connection is created in auto-commit mode by default. This means that each individual SQL statement is treated as a transaction and will be automatically committed as soon as it is executed. If you require two or more statements to be grouped into a transaction then you need to disable auto-commit mode using below command
0.con.setAutoCommit(false);
Once auto-commit mode is disabled, no SQL statements will be committed until you explicitly call the commit method. A Simple transaction with use of autocommit flag is demonstrated below.
1.con.setAutoCommit(false);
2.PreparedStatement updateStmt =
3.con.prepareStatement( "UPDATE EMPLOYEE SET SALARY = ? WHERE EMP_NAME LIKE ?");
ccon.setAWhat are database warnings and How can I handle database warnings in JDBC?
Warnings are issued by database to notify user of a problem which may not be very severe. Database warnings do not stop the execution of SQL statements. In JDBC SQLWarning is an exception that provides information on database access warnings. Warnings are silently chained to the object whose method caused it to be reported. Warnings may be retrieved from Connection, Statement, and ResultSet objects. Handling SQLWarning from connection object
0.//Retrieving warning from connection object
1.SQLWarning warning = conn.getWarnings();
2.
3.//Retrieving next warning from warning object itself
22.//Clear all warnings reported for this resultset object.
23.rs.clearWarnings();
The call to getWarnings() method in any of above way retrieves the first warning reported by calls on this object. If there is more than one warning, subsequent warnings will be chained to the first one and can be retrieved by calling the method SQLWarning.getNextWarning on the warning that was retrieved previously. A call to clearWarnings() method clears all warnings reported for this object. After a call to this method, the method getWarnings returns null until a new warning is reported for this object. Trying to call getWarning() on a connection after it has been closed will cause an SQLException to be thrown. Similarly, trying to retrieve a warning on a statement after it has been closed or on a result set after it has been closed will cause an SQLException to be thrown. Note that closing a statement also closes a result set that it might have produced.
What is Metadata and why should I use it?
JDBC API has 2 Metadata interfaces DatabaseMetaData & ResultSetMetaData. The DatabaseMetaData provides Comprehensive information about the database as a whole. This interface is implemented by driver vendors to let users know the capabilities of a Database Management System (DBMS) in combination with the driver based on JDBC technology ("JDBC driver") that is used with it. Below is a sample code which demonstrates how we can use the DatabaseMetaData
The ResultSetMetaData is an object that can be used to get information about the types and properties of the columns in a ResultSet object. Use DatabaseMetaData to find information about your database, such as its capabilities and structure. Use ResultSetMetaData to find information about the results of an SQL query, such as size and types of columns. Below a sample code which demonstrates how we can use the ResultSetMetaData
5.ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");
6.ResultSetMetaData rsmd = rs.getMetaData();
7.int numberOfColumns = rsmd.getColumnCount();
8.boolean b = rsmd.isSearchable(1);
What is RowSet? or What is the difference between RowSet and ResultSet? or Why do we need RowSet? or What are the advantages of using RowSet over ResultSet?
RowSet is a interface that adds support to the JDBC API for the JavaBeans component model. A rowset, which can be used as a JavaBeans component in a visual Bean development environment, can be created and configured at design time and executed at run time. The RowSet interface provides a set of JavaBeans properties that allow a RowSet instance to be configured to connect to a JDBC data source and read some data from the data source. A group of setter methods (setInt, setBytes, setString, and so on) provide a way to pass input parameters to a rowset's command property. This command is the SQL query the rowset uses when it gets its data from a relational database, which is generally the case. Rowsets are easy to use since the RowSet interface extends the standard java.sql.ResultSet interface so it has all the methods of ResultSet. There are two clear advantages of using RowSet over ResultSet
oRowSet makes it possible to use the ResultSet object as a JavaBeans component. As a consequence, a result set can, for example, be a component in a Swing application.
oRowSet be used to make a ResultSet object scrollable and updatable. All RowSet objects are by default scrollable and updatable. If the driver and database being used do not support scrolling and/or updating of result sets, an application can populate a RowSet object implementation (e.g. JdbcRowSet) with the data of a ResultSet object and then operate on the RowSet object as if it were the ResultSet object
What is a connected RowSet? or What is the difference between connected RowSet and disconnected RowSet? or Connected vs Disconnected RowSet, which one should I use and when?
Connected RowSet
A RowSet object may make a connection with a data source and maintain that connection throughout its life cycle, in which case it is called a connected rowset. A rowset may also make a connection with a data source, get data from it, and then close the connection. Such a rowset is called a disconnected rowset. A disconnected rowset may make changes to its data while it is disconnected and then send the changes back to the original source of the data, but it must reestablish a connection to do so. Example of Connected RowSet: A JdbcRowSet object is a example of connected RowSet, which means it continually maintains its connection to a database using a JDBC technology-enabled driver.
Disconnected RowSet
A disconnected rowset may have a reader (a RowSetReader object) and a writer (a RowSetWriter object) associated with it. The reader may be implemented in many different ways to populate a rowset with data, including getting data from a non-relational data source. The writer can also be implemented in many different ways to propagate changes made to the rowset's data back to the underlying data source. Example of Disconnected RowSet: A CachedRowSet object is a example of disconnected rowset, which means that it makes use of a connection to its data source only briefly. It connects to its data source while it is reading data to populate itself with rows and again while it is propagating changes back to its underlying data source. The rest of the time, a CachedRowSet object is disconnected, including while its data is being modified. Being disconnected makes a RowSet object much leaner and therefore much easier to pass to another component. For example, a disconnected RowSet object can be serialized and passed over the wire to a thin client such as a personal digital assistant (PDA).
What is the benefit of having JdbcRowSet implementation? Why do we need a JdbcRowSet like wrapper around ResultSet?
The JdbcRowSet implementation is a wrapper around a ResultSet object that has following advantages over ResultSet
oThis implementation makes it possible to use the ResultSet object as a JavaBeans component. A JdbcRowSet can be used as a JavaBeans component in a visual Bean development environment, can be created and configured at design time and executed at run time.
oIt can be used to make a ResultSet object scrollable and updatable. All RowSet objects are by default scrollable and updatable. If the driver and database being used do not support scrolling and/or updating of result sets, an application can populate a JdbcRowSet object with the data of a ResultSet object and then operate on the JdbcRowSet object as if it were the ResultSet object.
Adding sample programing which will help us to easily understand the jdbc connectivity with any database. Here Adding sample code for calling function and store procedure from plain java class, which required only oracle odbc 14.jar in classpath;
Table creation script:
CREATE TABLE EMPLOYEE ( ID NUMBER(10, 0) NOT NULL, FIRST_NAME VARCHAR2(20) NOT NULL, LAST_NAME VARCHAR2(20), SALARY NUMBER NOT NULL, DEPTNO NUMBER NOT NULL , CONSTRAINT EMPLOYEE_PK PRIMARY KEY (ID) ENABLE ) ;
CREATE TABLE DEPARTMENT ( DEPT_ID NUMBER NOT NULL, DEPT_NAME VARCHAR2(20), MANAGER NUMBER, LOCATION VARCHAR2(20) , CONSTRAINT DEPARTMENT_PK PRIMARY KEY ( DEPT_ID ) ENABLE );
Function and store procedure code
--- Procedure CREATE OR REPLACE PROCEDURE raise_sal(i_empno NUMBER, i_newsal NUMBER) AS BEGIN UPDATE employee SET salary = i_newsal WHERE id = i_empno; END;
--- Function
CREATE OR REPLACE FUNCTION FUNCTION_Sal (inID NUMBER) RETURN NUMBER IS sal NUMBER; BEGIN SELECT salary INTO sal FROM employee WHERE id = inID; RETURN sal; END;
Can you think of a questions which is not part of this post? Please don't forget to share it with me in comments section & I will try to include it in the list.
