JDBC

I want to learn java connectivity with oracle.How?

You should have Driver for connectivity .You can download from net .

Do following steps:-

1) Load that driver in your class   By help of Class.forNamr(Driver) 
2) Than get the connection    Connection con = null;   con =   DriverManger.getDataBaseConnection(url,user,password).
3) Create statement help of connection   Statement st = con.createStatement(\"Select * From tbl......\"); 
4) Execute Statement      ResultSet rs = null;      rs= st.execute();
5) Get you result from resultset .

How do I retrieve a whole row of data at once, instead of calling an individual ResultSet.getXXX method for each column

The ResultSet.getXXX methods are the only way to retrieve data from a ResultSet object,which means that you have to make a method call for each column of a row. 

It is unlikely that this is the cause of a performance problem, however, because it is difficult to see how a column could be fetched without at least the cost of a function call in any scenario.
We welcome input from developers on this issue.

What are four types of JDBC driver?

Type 1 :- DriversBridge drivers such as the jdbc-odbc bridge.They rely on an intermediary such as ODBC to transfer the SQL calls to the database and also often rely on native code. It is not a serious solution for an application 

Type 2:- DriversUse the existing database API to communicate with the database on the client. Faster than Type 1, but need native code and require additional permissions to work in an applet. Client machine requires software to run. 
Type 3 DriversJDBC-Net pure Java driver. It translates JDBC calls to a DBMS-independent network protocol, which is then translated to a DBMS protocol by a server. Flexible. Pure Java and no native code. 
Type 4 DriversNative-protocol pure Java driver. It converts JDBC calls directly into the network protocol used by DBMSs. 
This allows a direct call from the client machine to the DBMS server. It doesn\'t need any special native code on the client machine.

Recommended by Sun\'s tutorial, driver type 1 and 2 are interim solutions where direct pure Java drivers are not yet available. 

Driver type 3 and 4 are the preferred way to access databases using the JDBC API, because they offer all the advantages of Java technology, including automatic installation. For more info, visit Sun JDBC page
Type-1     Jdbc-Odbc Bridge Driver
Vendor :Sun Class: sun.jdbc.odbc.JdbcOdbcDriver
Jar: rt.jar
This driver is Database Vendor Independent.
Type-2  Native-API, Partly-Java driver
DB Vendor, Weblogic Servler
oracle.jdbc.driver.OracleDriver
ojdbc14.jar
Faster than any other driver.
Type-3
Java Native Net Protocol Driver
Third party such as IDS
ids.sql.IDSDriver
jdk14drv.jar
No need to depend on DB Vendor jar file.
Type-4
Java to Database protocol
DB Vendor :oracle.jdbc.driver.OracleDriver
ojdbc14.jar
Thin driver.
Other Examples of Type-4 Drivers
1) org.hsqldb.jdbcDriver: For HSQLDB Database.
2) com.mysql.jdbc.Driver : For MYSQL Databse
3) org.postgresql.Driver: For PostGreSQL
4) com.sybase.jdbc2.jdbc.SybDriver: Sybase database

Is the JDBC-ODBC Bridge multi-threaded?

No. 

The JDBC-ODBC Bridge does not support concurrent access from different threads. 
The JDBC-ODBC Bridge uses synchronized methods to serialize all of the calls that it makes to ODBC. 
Multi-threaded Java programs may use the Bridge, but they won\'t get the advantages of multi-threading.
 In addition, deadlocks can occur between locks held in the database and the semaphore used by the Bridge.
 We are thinking about removing the synchronized methods in the future. 
They were added originally to make things simple for folks writing Java programs that use a single-threaded ODBC driver.

Does the JDBC-ODBC Bridge support multiple concurrent open statements per connection?

No. You can open only one Statement object per connection when you are using the JDBC-ODBC Bridge.

How can I retrieve a String or other object type without creating a new object each time?

Creating and garbage collecting potentially large numbers of objects (millions) unnecessarily can really hurt performance.

It may be better to provide a way to retrieve data like strings using the JDBC API without always allocating a new object.

Where can I find info, frameworks and example source for writing a JDBC driver?

There a several drivers with source available, like MM.MySQL, eSimpleText Databas, FreeTDS, and RmiJdbc. There is at least one free framework, the jxDBCon-Open Source JDBC driver framework. 

Any driver writer should also review for Driver Writers.

How can I create a custom RowSetMetaData object from scratch?

One unfortunate aspect of RowSetMetaData for custom versions is that it is an interface. 
This means that implementations almost have to be proprietary.
The JDBC RowSet package is the most commonly available and offers the sun.jdbc.rowset.
RowSetMetaDataImpl class. After instantiation, any of the RowSetMetaData setter methods may be used.
 The bare minimum needed for a RowSet to function is to set the Column Count for a row and the Column Types for each column in the row. For a working code example that includes a custom RowSetMetaData.

It is an interface in javax.sql package.
The RowSet is a thin wrapper on ResultSet object.
RowSet object follows the JavaBeans model for properties and event notifications, i.e., it is a JavaBeans component.

The RowSet interface provides a set of JavaBean properties such as:
I. Connection properties (url, username, password)
II. Datasource property (dataSourceName)
III. Transaction Isolation property (transactionIsolation)
IV. Command property for executing query (command).
V. ResultSet type (type)
VI. Concurrency (concurrency)
VII. Event properties Allowing other components in an application to be notified when an event occurs on a rowset.

Create JdbcRowSet object:
JdbcRowSet jrs = new JdbcRowSetImpl();

How does a custom RowSetReader get called from a CachedRowSet?

The Reader must be registered with the CachedRowSet using 

CachedRowSet.setReader(javax.sql.RowSetReader reader). 

Once that is done, a call to CachedRowSet.execute() will, among other things, invoke the readData method.
Create CachedRowSet object
CachedRowSet crs = new CachedRowSetImpl();

 Pass SELECT SQL query for setCommand(String) method.
crs.setCommand(String query);

 Invoke execute(Connection) method and pass connection object.
crs.execute(con);

What is the fastest type of JDBC driver?

JDBC driver performance will depend on a number of issues:

(a) the quality of the driver code,
(b) the size of the driver code, 
(c) the database server and its load,
(d) network topology, 
(e) the number of times your request is translated to a different API.

Type-2
Native-API, Partly-Java driver
DB Vendor, Weblogic Server
oracle.jdbc.driver.OracleDriver

What is a ResultSet ?

A table of data representing a database result set, which is usually generated by executing a statement that queries the database.

 A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row.
The ResultSet object represents the data in a tabular format i.e., it represents the table of data that is CURSOR returned by executing SQL query. 
The Database CURSOR is a buffer (cache) where the query result can be saved (i.e., DB CURSOR is placed in the DB).
When ResultSet object is created, initially the cursor is positioned before the first row i.e., beforeFirst.

What is Connection pooling?

A Connection pooling is a technique used for sharing server resources among requesting clients. 
Connection pooling increases the performance of Web applications by reusing active database connections instead of creating a new connection with every request. 
Connection pool manager maintains a pool of open database connections.
The connection pooling mechanism is used to establish pool of connections (i.e., group of connections) before the client makes a request.
The connection is retrieved from the pool, used by the application, and finally returns back to the pool.
The Connection Pool contains physical connections.
With Connection Pooling, the connections are Reused but not Recreated every time when a connection is needed. The advantage of Connection Pooling is, which improves Performance.

What are the different JDBC drivers available?

Type 1 : JDBC-ODBC Bridge Driver 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. 

Type 2: Native API Partly Java 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. 
 Type 3: Network protocol Driver- 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.
 Type 4: JDBC Net pure Java Driver . 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.
Type-1
Jdbc-Odbc Bridge Driver
Sun
sun.jdbc.odbc.JdbcOdbcDriver
rt.jar
This driver is Database Vendor Independent.
Type-2
Native-API, Partly-Java driver
DB Vendor, Weblogic Servler
oracle.jdbc.driver.OracleDriver
ojdbc14.jar
Faster than any other driver.
Type-3
Java Native Net Protocol Driver
Third party such as IDS
ids.sql.IDSDriver
jdk14drv.jar
No need to depend on DB Vendor jar file.
Type-4
Java to Database protocol
DB Vendor
oracle.jdbc.driver.OracleDriver
ojdbc14.jar
Thin driver.

Is the JDBC-ODBC Bridge multi-threaded?

No. The JDBC-ODBC Bridge does not support multi threading. 
The JDBC-ODBC Bridge uses synchronized methods to serialize all of the calls that it makes to ODBC. 
Multi-threaded Java programs may use the Bridge, but they won\'t get the advantages of multi-threading.

What is cold backup, hot backup, warm backup recovery?

A Cold backup means all these files must be backed up at the same time, before the database is restarted. 
Hot backup (official name is online backup ) is a backup taken of each tablespace while the database is running and is being accessed by the users.
Warm back is a recovery technique where all the tables are locked and users cannot access at the time of backing up data. 
The backup operations can be postponed in case the users are accessing the database

What is the advantage of denormalization?

A Data denormalization is reverse procedure, carried out purely for reasons of improving performance. 
It maybe efficient for a high-throughput system to replicate data for certain data.
Denormalization is a time-space trade-off. Normalized data takes less space, but may require join to construct the desired result set, hence more time. If it\'s denormalized, data are replicated in several places. It then takes more space, but the desired view of the data is readily available.

How can you retrieve data from the ResultSet?

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.
ResultSet rs = stmt.executeQuery(\"SELECT COF_NAME, PRICE FROM COFFEES\");
String s = rs.getString(\"COF_NAME\");Eg.
ResultSet rs = stmt.executeQuery(\"select * from employee\");

What is DriverManager ?

A DriverManager is a class in java.sql package.It is the basic service for managing a set of JDBC drivers.
Driver class is automatically Loaded & Registered by the DriverManager class

What are the different types of Statements?

Regular statement (use createStatement method),
prepared statement (use prepareStatement method) and callable statement (use prepareCall) 
How can you use PreparedStatement? This special type of statement is derived from class Statement.
If you need aStatement object to execute many times, it will normally make sense 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.
PreparedStatement updateSales =con.prepareStatement(\"UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?\");

JDBC API supports 3 types of JDBC Statements: Statement(Used to Execute DDL or DML queries with no IN & no OUT),
PreparedStatement(Used to execute DML queries with IN parameters.), 
and CallableStatement(Used to Execute Stored Proc, functions may contains both IN and OUT parameters.).
JDBC Statements are used to execute SQL queries such as DDL, DML, Stored Procedures, or Database Functions.

What does setAutoCommit do?

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:con.setAutoCommit(false);
Once auto-commit mode is disabled, no SQL statements will be committed until you call the method commit explicitly. 
 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);

What does Class.forName return?

A class as loaded by the classloader.

Invoke Class.forName(\"Fully qualified driver class name\"), which is used to explicitly Load & Register the Driver class.

Returns the Class object associated with the class or interface with the given string name.

How do you handle your own transaction ?

A Connection Object has a method called setAutocommit.For handling our own transaction we can set the parameter to false and begin your transaction .
Finally commit the transaction by calling the commit method.

What\'s the difference between TYPE_SCROLL_INSENSITIVE , and TYPE_SCROLL_SENSITIVE?

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:
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);
}
There are three types of ResultSet objects:
i) TYPE_FORWARD_ONLY: Navigate in forward direction only, which is default.
15
ii) TYPE_SCROLL_INSENSITIVE: Navigate in Forward as well as Backward directions (Scrollable). But the ResultSet is insensitive to changes made to the underlying database while it is open i.e., the changes in data base are not reflected in ResultSet.
iii) TYPE_SCROLL_SENSITIVE: Navigate in Forward as well as Backward directions (Scrollable). Also the ResultSet is sensitive to changes made to the underlying database while it is open i.e., the changes in data source are reflected in ResultSet.

How do I check in my code whether a maximum limit of database connections have been reached?

Use DatabaseMetaData.getMaxConnections() and compare to the number of connections currently open.
Note that a return value of zero can mean unlimited or, unfortunately, unknown. 
Of course, driverManager.getConnection() will throw an exception if a Connection can not be obtained.

Why do I get UnsatisfiedLinkError when I try to use my JDBC driver?

The first thing is to be sure that this does not occur when running non-JDBC apps.If so, there is a faulty JDK/JRE installation.

If it happens only when using JDBC, then it,s time to check the documentation that came with the driver or the driver/DBMS support. 
JDBC driver types 1 through 3 have some native code aspect and typically require some sort of client install.
Along with the install, various environment variables and path or classpath settings must be in place.
Because the requirements and installation procedures vary with the provider, there is no reasonable way to provide details here. A type 4 driver, on the other hand, is pure Java and should never exhibit this problem. 
The trade off is that a type 4 driver is usually slower.