Pdf of jdbc tutorial

Type 2 drivers are useful in situations, where a type 3 or type 4 driver is not available yet for your database. The type 1 driver is not considered a deployment-level driver, and is typically used for development and testing purposes only. The programming involved to establish a JDBC connection is fairly simple. Import JDBC Packages The Import statements tell the Java compiler where to find the classes you reference in your code and are placed at the very beginning of your source code.

To use the standard JDBC package, which allows you to select, insert, update, and delete data in SQL tables, add the following imports to your source code: import java. Registering the driver is the process by which the Oracle driver's class file is loaded into the memory, so it can be utilized as an implementation of the JDBC interfaces.

You need to do this registration only once in your program. You can register a driver in one of two ways. Approach I - Class. This method is preferable because it allows you to make the driver registration configurable and portable.

The following example uses Class. OracleDriver ; DriverManager. For easy reference, let me list the three overloaded DriverManager. A database URL is an address that points to your database. Formulating a database URL is where most of the problems associated with establishing a connection occurs. Using a Database URL with a username and password The most commonly used form of getConnection requires you to pass a database URL, a username, and a password: Assuming you are using Oracle's thin driver, you'll specify a host:port:databaseName value for the database portion of the URL.

It is used to pass driver properties to the driver during a call to the getConnection method. To make the same connection made by the previous examples, use the following code: import java. However, if you forget, Java's garbage collector will close the connection when it cleans up stale objects.

Relying on the garbage collection, especially in database programming, is a very poor programming practice. You should make a habit of always closing the connection with the close method associated with connection object.

A finally block always executes, regardless of an exception occurs or not. To close the above opened connection, you should call close method as follows: conn. They also define methods that help bridge data type differences between Java and SQL data types used in a database.

The following table provides a summary of each interface's purpose to decide on the interface to use. Interfaces Recommended Use Statement Use this for general-purpose access to your database. Useful when you are using static SQL statements at runtime. The Statement interface cannot accept parameters. The PreparedStatement interface accepts input parameters at runtime. CallableStatement Use this when you want to access the database stored procedures. The CallableStatement interface can also accept runtime input parameters.

Closing Statement Object Just as you close a Connection object to save database resources, for the same reason you should also close the Statement object. A simple call to the close method will do the job.

If you close the Connection object first, it will close the Statement object as well. However, you should always explicitly close the Statement object to ensure proper cleanup. This sample code has been written based on the environment and database setup done in the previous chapters.

Import required packages import java. This statement gives you the flexibility of supplying arguments dynamically. You must supply values for every parameter before executing the SQL statement. The setXXX methods bind values to the parameters, where XXX represents the Java data type of the value you wish to bind to the input parameter.

If you forget to supply the values, you will receive an SQLException. Each parameter marker is referred by its ordinal position. The first marker represents position 1, the next position 2, and so forth. This method differs from that of Java array indices, which starts at 0.

All of the Statement object's methods for interacting with the database a execute , b executeQuery , and c executeUpdate also work with the PreparedStatement object. However, the methods are modified to use SQL statements that can input the parameters.

Closing PreparedStatement Object Just as you close a Statement object, for the same reason you should also close the PreparedStatement object. If you close the Connection object first, it will close the PreparedStatement object as well. However, you should always explicitly close the PreparedStatement object to ensure proper cleanup.

Prepare - Example Code Following is the example, which makes use of the PreparedStatement along with opening and closing statments. The PreparedStatement object only uses the IN parameter. The CallableStatement object can use all the three. The following code snippet shows how to employ the Connection. Using the CallableStatement objects is much like using the PreparedStatement objects. You must bind values to all the parameters before executing the statement, or you will receive an SQLException.

If you have IN parameters, just follow the same rules and techniques that apply to a PreparedStatement object; use the setXXX method that corresponds to the Java data type you are binding.

The registerOutParameter method binds the JDBC data type, to the data type that the stored procedure is expected to return. Once you call your stored procedure, you retrieve the value from the OUT parameter with the appropriate getXXX method. This method casts the retrieved value of SQL type to a Java data type.

Closing CallableStatement Object Just as you close other Statement object, for the same reason you should also close the CallableStatement object. If you close the Connection object first, it will close the CallableStatement object as well.

However, you should always explicitly close the CallableStatement object to ensure proper cleanup. Executing stored procedure The java. ResultSet interface represents the result set of a database query.

A ResultSet object maintains a cursor that points to the current row in the result set. The term "result set" refers to the row and column data contained in a ResultSet object. The updates can then be updated in the underlying database as well. The cursor is movable based on the properties of the ResultSet. These properties are designated when the corresponding Statement that generates the ResultSet is created.

Type Description ResultSet. The cursor can scroll forward and backward, and the result set is sensitive to changes made by others to the database that occur after the result set was created. Concurrency Description ResultSet. This is the default ResultSet. This method returns false if the previous row is off the result set. This method returns false if there are no more rows in the result set. The current cursor location is remembered. For a better understanding, let us study Navigate - Example Code as discussed below.

Navigate - Example Code Following is the example, which makes use of few navigation methods described in the Result Set tutorial. Moving cursor to the last Displaying record For example, if the column you are interested in viewing contains an int, you need to use one of the getInt methods of ResultSet: S. The column index starts at 1, meaning the first column of a row is 1, the second column of a row is 2, and so on. Similarly, there are get methods in the ResultSet interface for each of the eight Java primitive types, as well as common types such as java.

String, java. Object, and java. There are also methods for getting SQL data types java. Date, java. Time, java. TimeStamp, java.

Clob, and java. Check the documentation for more information about using these SQL data types. For a better understanding, let us study the Viewing - Example Code as discussed below. Viewing - Example Code Following is the example, which makes use of few getInt and getString methods described in the Result Set chapter. This example is very similar to previous example explained in the Navigation Result Set Section. This sample code has been written based on the environment and the database setup done in the previous chapters.

For example, to update a String column of the current row of a result set, you would use one of the following updateString methods: S. Updating a row in the result set changes the columns of the current row in the ResultSet object, but not in the underlying database. To update your changes to the row in the database, you need to invoke one of the following methods. This method can only be invoked when the cursor is pointing to the insert row. For a better understanding, let us study the Updating - Example Code as discussed below.

It should be noted that tables you are working on should have Primary Key set properly. List result set for reference List result set showing new set It uses a default mapping for most data types. Default mappings were created to provide consistency between drivers. ResultSet object provides corresponding getXXX method for each data type to retrieve column value. Each method can be used with column name or by its ordinal position.

Time and java. Following example shows how the Date and Time classes format the standard Java date and time values to match the SQL data type requirements. Date; import java. Time; import java. Timestamp; import java. Date javaTime ; System. Time javaTime ; System. Timestamp javaTime ; System. Transactions enable you to control if, and when, changes are applied to the database. It treats a single SQL statement or a group of SQL statements as one logical unit, and if any statement fails, the whole transaction fails.

To enable manual- transaction support instead of the auto-commit mode that the JDBC driver uses by default, use the Connection object's setAutoCommit method. If you pass a boolean false to setAutoCommit , you turn off auto-commit.

You can pass a boolean true to turn it back on again. For example, if you have a Connection object named conn, code the following to turn off auto-commit: conn. For a better understanding, let us study the Commit - Example Code as discussed below. Commit - Example Code Following is the example, which makes use of commit and rollback described in the Transaction tutorial.

Inserting one row Commiting data here When you set a savepoint you define a logical rollback point within a transaction. If an error occurs past a savepoint, you can use the rollback method to undo either all the changes or only the changes made after the savepoint.

It also returns a Savepoint object. Notice that it requires a Savepoint object as a parameter. This object is usually a savepoint generated by the setSavepoint method. For a better understanding, let us study the Savepoints - Example Code as discussed below.

Savepoints - Example Code Following is the example, which makes use of setSavepoint and rollback described in the Transaction tutorial. JDBC is used for database-independent connectivity between the Java programming language and a wide range of databases. Also, it can access any type of tabular data, notably data stored in a Relational Database.

Mainly, there are four types of JDBC drivers:. Can look at the below list of JDBC concepts that are from core basics to advance level. Learning JDBC is very important for java programmers to connect and execute database-related queries. Connections: Before performing any database operation via JDBC, we have to open a database connection. For opening a database connection, we can call the getConnection method of DriverManager class.

The Syntax of Connection is as follows:.