You have a privilege to create a quiz (QnA) related to this subject and obtain creativity score...
Different databases have different ways of communications, different API. Consistently following the principle Write once - run everywhere, Java offers a solution to this problem,
Java Database Connectivity (JDBC).
JDBC concepts is implemented via JDBC drivers, a software created by each DB vendor to transform internal API of a specific database into a unified JDBC API. Developers only need to know unified JDBC API. They should not be concerned of specific ways connecting to different databases. Of course, as any rule, this one also has some exceptions.
JDBC API is implemented in the Java library package java.sql. A set of classes in this library allows developers to accomplish all tasks of handling data in RDBMS.
Working with a database our program usually executes the following tasks:
1. Making sure that a proper JDBC driver is loaded by JVM. Of course, this requires this driver to be present in a jar file in your project library. Usually it is the lib directory in your project.
2. Connect to a database with a proper connection URL, user name and password.
3. Use the connection object to prepare an SQL statement: java.sql.Statement or java.sql.PreparedStatement
4. Execute the statement. In the case of the SELECT statement our program will retrieve java.sql.ResultSet (a set of records) from the database. Otherwise (INSERT, UPDATE, DELETE) this execution will change data.
5. This step is needed in the case of retrieving data with the SELECT statement. Our program will need to process the Result Set to get specific data from the records.
The table below provides source fragments to illustrate each task.
1
Make sure a proper JDBC driver is loaded.
The driver must be present in the project library as a jar file, such as ojdbc6.jar for Oracle
The following lines will load and register the driver:
String driverName = "oracle.jdbc.driver.OracleDriver"; // for Oracle DB
java.sql.Driver driver =
(java.sql.Driver) Class.forName(driverName).newInstance(); // fails if ojdbc6.jar is not in the lib
DriverManager.registerDriver(driver);
2
Connect to a databaseUsing Oracle XE default connection URL
String connectionUrl = "jdbc:oracle:thin:@localhost:1521:xe"; // hostName=localhost; port=1521; DB instance=xe
String user = "ITS"; // In the Oracle world, usually user is the name of a database to connect
String password = "tiger";
Connection con = DriverManager.getConnection(connectionUrl, user, password);
3. Use the connection object con to prepare an SQL statement. There are two types of statements: java.sql.Statement (a light version) and java.sql.PreparedStatement (a heavy version).
PreparedStatement is slower to create, but is reusable and performing better when program repeats the same SQL many times.
In the case of PreparedStatement we provide "?" instead of variable values and later replace "?" with run-time variables.
PreparedStatement is commonly used in JDBC programs.
3
Example of using PreparedStatement
PreparedStatement pstmt = con.prepareStatement(
"select NAME, SSN from PAYROLL where SSN = ?");
pstmt.setString(1, ssnVariable); // replacing the first (and only) "?" with ssnVariable
4
Execute the statement
pstmt.executeQuery(); // for SELECT
pstmt.execute(); // for INSERT, etc.
// While executing a query, a program will get back java.sql.ResultSet from the database
ResultSet rset = pstmt.executeQuery(); // ResultSet is a set of records retrieved by the query
5
Processing the Result Set Getting specific data from the records
retrieved by our program with the execute method.
// Check if ResultSet still not empty and get first column from the next string result
while (rset.next() )
{
String aName = rset.getString( 1 );
String ssn = rset.getString( 2 );
System.out.println("Name is " + aName + " SSN is " + ssn);
}
JDBC API version 2, 3, 4 Starting from JDBC 2 the core API has been provided in the Java package javax.sql.
JDBC 2 and higher provide support for the SQL99 data types and include important methods to increase productivity, supporting scrollable result sets, batch updates, programmatic inserts, deletes, updates, and many more features.
The JDBC 3 API introduced new changes in these areas:
Savepoint support
Added the Savepoint interface, which contains new methods to set a savepoint, to release a savepoint, and to roll back a transaction to a designated savepoint.
Reuse of prepared statements by connection pools
Added the ability for deployers to control how prepared statements are pooled and reused by connections.
Connection pool configuration
Defined a number of properties for the ConnectionPoolDataSource interface. These properties can be used to describe how PooledConnection objects created by DataSource objects should be pooled.
Retrieval of parameter metadata
Was it clear so far?
Added the new interface ParameterMetaData, which describes the number, type, and properties of parameters to prepared statements.
Retrieval of auto-generated keys
Added a means of retrieving values from columns containing automatically generated values.
Ability to have multiple open ResultSet objects
Added the new method getMoreResults(int) that takes an argument that specifies whether ResultSet objects returned by a Statement should be closed before returning any subsequent ResultSet objects.
Passing parameters to CallableStatement objects by name
Added methods to allow a string to identify the parameter to be set for a CallableStatement object.
Holdable cursor support
Added the ability to specify whether a ResultSet object is kept open after a transaction has been committed.
BOOLEAN data type
Added the data type java.sql.Types.BOOLEAN. BOOLEAN is logically equivalent to BIT.
Making internal updates to the data in Blob and Clob objects
Added methods to allow the data contained in Blob and Clob objects to be altered.
Retrieving and updating the object referenced by a Ref object
Added methods to retrieve the object referenced by a Ref object. Also added the ability to update a referenced object through the Ref object.
Updating of columns containing BLOB, CLOB, ARRAY and REF types
Addition of the updateBlob, updateClob, updateArray, and updateRef methods to the ResultSet interface.
DATALINK/URL data type
Added the data type java.sql.Types.DATALINK, allowing JDBC drivers to store and retrieve references to external data.
Transform groups and type mapping
Described the effect of transform groups and how this is reflected in the metadata.
Relationship between the JDBC SPI (Service Provider Interface) and the Connector architecture
Described the relationship between the JDBC SPI and the connector architecture.
DatabaseMetadata APIs
Added metadata for retrieving SQL type hierarchies and various other kinds of information relating to new features.
These features are present only in a JDK 6 or higher environment.
JDBC 4 API, which is available starting with JSE-6 and higher brings such features as:
New implementations of javax.sql.DataSource. See javax.sql.DataSource interface: JDBC 4.0 features.
Autoloading of JDBC drivers. In earlier versions of JDBC, applications had to manually register drivers before requesting Connections. With JDBC 4.0, applications no longer need to issue a Class.forName() on the driver name; instead, the DriverManager will find an appropriate JDBC driver when the application requests a Connection.
SQLExceptions. JDBC 4.0 introduces refined subclasses of SQLException.
Wrappers. JDBC 4.0 introduces the concept of wrapped JDBC objects. This is a formal mechanism by which application servers can look for vendor-specific extensions inside standard JDBC objects like Connections, Statements, and ResultSets.
Statement events. With JDBC 4.0, Connection pools can listen for Statement closing and Statement error events. New methods were added to javax.sql.PooledConnection: addStatementEventListener and removeStatementEventListener.
Streaming APIs. JDBC 4.0 adds new overloads of the streaming methods in CallableStatement, PreparedStatement, and ResultSet. These are the setXXX and updateXXX methods which take java.io.InputStream and java.io.Reader arguments. The new overloads allow you to omit the length arguments or to specify long lengths.
And more new methods were added to the following interfaces: javax.sql.Connection, javax.sql.DatabaseMetaData, and javax.sql.Statement.
JDBC API version 2, 3, 4 Starting from JDBC 2 the core API has been provided in the Java package javax.sql.
JDBC 2 and higher provide support for the SQL99 data types and include important methods to increase productivity, supporting scrollable result sets, batch updates, programmatic inserts, deletes, updates, and many more features.
The JDBC 3 API introduced new changes in these areas:
Savepoint support
Added the Savepoint interface, which contains new methods to set a savepoint, to release a savepoint, and to roll back a transaction to a designated savepoint.
Reuse of prepared statements by connection pools
Added the ability for deployers to control how prepared statements are pooled and reused by connections.
Connection pool configuration
Defined a number of properties for the ConnectionPoolDataSource interface. These properties can be used to describe how PooledConnection objects created by DataSource objects should be pooled.
Retrieval of parameter metadata
Was it clear so far?
onclick="window.location.href='/BASE/jsp/demo.jsp?checkFlavor=itsp&issueID=52&intro=general&group=aitu&ur=f'">
Added the new interface ParameterMetaData, which describes the number, type, and properties of parameters to prepared statements.
Retrieval of auto-generated keys
Added a means of retrieving values from columns containing automatically generated values.
Ability to have multiple open ResultSet objects
Added the new method getMoreResults(int) that takes an argument that specifies whether ResultSet objects returned by a Statement should be closed before returning any subsequent ResultSet objects.
Passing parameters to CallableStatement objects by name
Added methods to allow a string to identify the parameter to be set for a CallableStatement object.
Holdable cursor support
Added the ability to specify whether a ResultSet object is kept open after a transaction has been committed.
BOOLEAN data type
Added the data type java.sql.Types.BOOLEAN. BOOLEAN is logically equivalent to BIT.
Making internal updates to the data in Blob and Clob objects
Added methods to allow the data contained in Blob and Clob objects to be altered.
Retrieving and updating the object referenced by a Ref object
Added methods to retrieve the object referenced by a Ref object. Also added the ability to update a referenced object through the Ref object.
Updating of columns containing BLOB, CLOB, ARRAY and REF types
Addition of the updateBlob, updateClob, updateArray, and updateRef methods to the ResultSet interface.
DATALINK/URL data type
Added the data type java.sql.Types.DATALINK, allowing JDBC drivers to store and retrieve references to external data.
Transform groups and type mapping
Described the effect of transform groups and how this is reflected in the metadata.
Relationship between the JDBC SPI (Service Provider Interface) and the Connector architecture
Described the relationship between the JDBC SPI and the connector architecture.
DatabaseMetadata APIs
Added metadata for retrieving SQL type hierarchies and various other kinds of information relating to new features.
These features are present only in a JDK 6 or higher environment.
JDBC 4 API, which is available starting with JSE-6 and higher brings such features as:
New implementations of javax.sql.DataSource. See javax.sql.DataSource interface: JDBC 4.0 features.
Autoloading of JDBC drivers. In earlier versions of JDBC, applications had to manually register drivers before requesting Connections. With JDBC 4.0, applications no longer need to issue a Class.forName() on the driver name; instead, the DriverManager will find an appropriate JDBC driver when the application requests a Connection.
SQLExceptions. JDBC 4.0 introduces refined subclasses of SQLException.
Wrappers. JDBC 4.0 introduces the concept of wrapped JDBC objects. This is a formal mechanism by which application servers can look for vendor-specific extensions inside standard JDBC objects like Connections, Statements, and ResultSets.
Statement events. With JDBC 4.0, Connection pools can listen for Statement closing and Statement error events. New methods were added to javax.sql.PooledConnection: addStatementEventListener and removeStatementEventListener.
Streaming APIs. JDBC 4.0 adds new overloads of the streaming methods in CallableStatement, PreparedStatement, and ResultSet. These are the setXXX and updateXXX methods which take java.io.InputStream and java.io.Reader arguments. The new overloads allow you to omit the length arguments or to specify long lengths.
And more new methods were added to the following interfaces: javax.sql.Connection, javax.sql.DatabaseMetaData, and javax.sql.Statement.
