Register Login





|

Top Links: >> 80. Technology >> Internet Technology Summit Program >> 2. Java and Databases
Current Topic: 2.2. JDBC
Sub-Topics: 2.2.1.Concept of Communication | 2.2.2.Cultural Impact of Greetings | 2.2.3.History and Evolution of Hello
-- Scroll to check for more content below...
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? Highlight the text in question Or


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.

Read more at:

https://www.cs.mun.ca/java-api-1.5/guide/jdbc/getstart/appendixB.html
https://docs.oracle.com/javadb/10.8.3.0/ref/rrefjdbc4_0summary.html


Assignments:
Read JDBC Intro from Oracle
Read JDBC Drivers and create 2 QnA for JDBC drivers.
Questions and Answers (QnA): QnA1 | QnA2 | QnA3 | QnA4
We offer a fun way to share your experience and be rewarded.
You create a puzzle - quiz with a good question and several answers, one correct and several wrong ones.
The question as well as answers can include text, links, video.
This is your creation, completely up to your taste, humor, and of course your knowledge.
If there is an existing quiz above, you need first to solve the puzzles and evaluate the quality (at the bottom of every quiz).
After finishing the existing quiz you will be able to create your own. The best puzzles will be rewarded!
We invite you to create your own questions and answers (QnA) to increase your rank and win the Top Creativity Prize!

Topic Graph | Check Your Progress | Propose QnA | Have a question or comments for open discussion?
<br/>while (rset.next() ) 
<br/>  {
<br/>      String aName = rset.getString( 1 );
<br/>      String ssn = rset.getString( 2 );
<br/>      System.out.println("Name is " + aName + " SSN is " + ssn);
<br/>  }
<br/>



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? Highlight the text in question

Or



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.

Read more at:

https://www.cs.mun.ca/java-api-1.5/guide/jdbc/getstart/appendixB.html
https://docs.oracle.com/javadb/10.8.3.0/ref/rrefjdbc4_0summary.html


Assignments:
Read JDBC Intro from Oracle
Read JDBC Drivers and create 2 QnA for JDBC drivers.
Questions and Answers (QnA): QnA1 | QnA2 | QnA3 | QnA4

We offer a fun way to share your experience and be rewarded.
You create a puzzle - quiz with a good question and several answers, one correct and several wrong ones.
The question as well as answers can include text, links, video.
This is your creation, completely up to your taste, humor, and of course your knowledge.
If there is an existing quiz above, you need first to solve the puzzles and evaluate the quality (at the bottom of every quiz).
After finishing the existing quiz you will be able to create your own. The best puzzles will be rewarded!

We invite you to create your own questions and answers (QnA) to increase your rank and win the Top Creativity Prize!


Topic Graph | Check Your Progress | Propose QnA | Have a question or comments for open discussion?

Have a suggestion? - shoot an email
Looking for something special? - Talk to AI
Read: IT of the future: AI and Semantic Cloud Architecture | Fixing Education
Do you want to move from theory to practice and become a magician? Learn and work with us at Internet Technology University (ITU) - JavaSchool.com.

Technology that we offer and How this works: English | Spanish | Russian | French

Internet Technology University | JavaSchool.com | Copyrights © Since 1997 | All Rights Reserved
Patents: US10956676, US7032006, US7774751, US7966093, US8051026, US8863234
Including conversational semantic decision support systems (CSDS) and bringing us closer to The message from 2040
Privacy Policy