SME: JDBC

JDBC ( Java Database Connectivity )

JDBC API is a Java API that can access a Relational Database.

JDBC stands for Java Database Connectivity It is a standard Java API for database-independent connectivity between the Java programming language and databases.

With JDBC APIs we can do the following with database:

Making a connection to a database
Creating SQL statements
Executing that SQL queries in the database
Viewing & Modifying the resulting records

The JDBC API provides the following interfaces and classes:

DriverManager: manages a list of database drivers.
Driver: interface handles the communications with the database server.
Connection : interface with all methods for contacting a database.
Statement : submits the SQL statements to the database.
ResultSet: holds data retrieved from a database after executing an SQL query using Statement objects.
SQLException: handles any errors that occur in a database application.

The java.sql and javax.sql are the primary packages for JDBC 4.0.

Core JDBC classes, interfaces, and exceptions in the java.sql package:

• DriverManager:

This class loads JDBC drivers in memory. It is a “factory” class and can also be used to create java.sql.Connection objects to data sources (such as Oracle,MySQL, etc.).

• Connection:
This interface represents a connection with a data source. The Connection object is used for creating Statement, PreparedStatement, and CallableStatement objects.

• DatabaseMetaData:
This interface provides detailed information about the database as a whole. The Connection object is used for creating DatabaseMetaData objects.

• Statement:
This interface represents a static SQL statement. It can be used to retrieve ResultSet objects.

• PreparedStatement:
This interface extends Statement and represents a precompiled SQL statement. It can be used to retrieve ResultSet objects.

• CallableStatement:
This interface represents a database stored procedure. It can execute stored procedures in a database server.

• ResultSet:
This interface represents a database result set generated by using SQL’s SELECT statement. Statement, PreparedStatement, CallableStatement, and other JDBC objects can create ResultSet objects.

• ResultSetMetaData:
This interface provides information about the types and properties of the columns in a ResultSet object.

• SQLException:
This class is an exception class that provides information on a database access error or other errors.

JDBC give access to such information by providing several levels of error conditions:

• SQLException:
SQLExceptions are Java exceptions that, if not handled, will terminate the client application. SQLException is an exception that provides information on a database access error or other errors.

• SQLWarning:
SQLWarnings are subclasses of SQLException, but they represent nonfatal errors or unexpected conditions, and as such, can be ignored. 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.

• BatchUpdateException:
BatchUpdateException is an exception thrown when an error occurs during a batch update operation. In addition to the information provided by SQLException, a BatchUpdateException provides the update counts for all commands that were executed successfully during the batch update, that is, all commands that were executed before the error occurred. The order of elements in an array of update
counts corresponds to the order in which commands were added to the batch.

• DataTruncation:
DataTruncation is an exception that reports a DataTruncation warning (on reads) or throws a DataTruncation exception (on writes) when JDBC unexpectedly truncates a data value.

The SQLException class extends the java.lang.Exception class and defines an additional
method called getNextException(). This allows JDBC classes to chain a series of SQLException objects together.

In addition, the SQLException class defines the getMessage() , getSQLState(), and getErrorCode() methods to provide additional information about an error or exception.

In JDBC, several key interfaces comprise the metadata portion:

• DatabaseMetaData: Provides information about the database as a whole.

• ResultSetMetaData:
Used to identify the types and properties of the columns in a ResultSet object.

• RowSetMetaData:
An object that contains information about the columns in a RowSet object. This interface is
an extension of the ResultSetMetaData interface with methods for setting the values in a
RowSetMetaData object.

• ParameterMetaData:
An object that can be used to get information about the types and properties of the parameters in a
PreparedStatement object.

• DriverPropertyInfo:
Driver properties for making a connection. The DriverPropertyInfo class is of interest only
to advanced programmers who need to interact with a Driver via the method getDriverProperties()
to discover and supply properties for connections.

The following sections show how to create a simple JDBC application.
It will show you how to open a database connection, execute a SQL query, and display the results.
We need to follow the following steps to building a JDBC application:

Register the JDBC driver
Open a connection
Execute a sql command, for example a query or an update statement
Extract data from result set after query
Clean up the environment

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "org.hsqldb.jdbcDriver";
static final String DB_URL = "jdbc:hsqldb:mem:db_file";

// Database credentials
   static final String USER = "sa";
   static final String PASS = "";

public static void main(String[] args) {
    Connection conn = null;
    Statement stmt = null;
    try {
      // Register JDBC driver
       Class.forName(JDBC_DRIVER);

      // Open a connection
      System.out.println("Connecting to database...");
      conn = DriverManager.getConnection(DB_URL, USER, PASS);

      // Execute a query
      System.out.println("Creating statement...");
      stmt = conn.createStatement();
      String sql;
      sql = "SELECT id, first, last, age FROM Employees";

   stmt.executeUpdate("CREATE TABLE Employees ( id INTEGER IDENTITY, first VARCHAR(256), last VARCHAR(256),age INTEGER)");

    stmt.executeUpdate("INSERT INTO Employees VALUES(1,'Jack','Smith', 100)");

      ResultSet rs = stmt.executeQuery(sql);

      // Extract data from result set
         while (rs.next()) {

       // Retrieve by column name
         int id = rs.getInt("id");
         int age = rs.getInt("age");
         String first = rs.getString("first");
         String last = rs.getString("last");

        System.out.print("ID: " + id);
        System.out.print(", Age: " + age);
        System.out.print(", First: " + first);
        System.out.println(", Last: " + last);
      }
      // Clean-up environment
      rs.close();
      stmt.close();
      conn.close();
    } catch (SQLException se) {
      se.printStackTrace();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      // finally block used to close resources       try {
        if (stmt != null)
          stmt.close();
      } catch (SQLException se2) {
      }
      try {
        if (conn != null)
          conn.close();
      } catch (SQLException se) {
        se.printStackTrace();
      }
    }
    System.out.println("Goodbye!");
}
}

Note

The following code uses the hsql database as the relational database engine.
hsql database is a pure Java language based database, which means the database system is written in Java language.
Therefore the whole database system and JDBC driver is all included in a jar file.
The JDBC driver name for hsql is org.hsqldb.jdbcDriver.
The URL we used to connect to database to hsql database is jdbc:hsqldb:mem:db_file.
mem in the URL tells the hsql database sytem to create a memory based table.
So we can execute the create table statement again and again.
The user name to connect to the hsql database is sa and the password is left empty.

USER = "sa";
PASS = "";

JDBC Tutorial - JDBC HelloWorld MySQL

JDBC is a standard Java API for database-independent connectivity between the Java programming language and databases.
But the URL needed to connect to database is varies from database to database.
The following code shows how to connect to MySQL.

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  static final String DB_URL = "jdbc:mysql://localhost/EMP";
  static final String USER = "username";
  static final String PASS = "password";

  public static void main(String[] args) {
    Connection conn = null;
    Statement stmt = null;
    try {
      Class.forName(JDBC_DRIVER);
      conn = DriverManager.getConnection(DB_URL, USER, PASS);
      stmt = conn.createStatement();
      String sql = "SELECT id, first, last, age FROM Employees";
      ResultSet rs = stmt.executeQuery(sql);
      while (rs.next()) {
        int id = rs.getInt("id");
        int age = rs.getInt("age");
        String first = rs.getString("first");
        String last = rs.getString("last");

        System.out.print("ID: " + id);
        System.out.print(", Age: " + age);
        System.out.print(", First: " + first);
        System.out.println(", Last: " + last);
      }
      rs.close();
      stmt.close();
      conn.close();
    } catch (SQLException se) {
      se.printStackTrace();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        if (stmt != null)
          stmt.close();
        if (conn != null)
          conn.close();
      } catch (SQLException se) {
        se.printStackTrace();
      }
    }
  }
}

//Note :In order to run the application above we need to load mysql jdbc jar file in the class path.

JDBC Tutorial - JDBC PostgreSQL

The following code shows how to connect to PostgreSQL database.

Example

import java.sql.Connection;
import java.sql.DriverManager;

public class Main {
  public static void main(String[] argv) throws Exception {
    Class.forName("org.postgresql.Driver");
    Connection connection = DriverManager.getConnection(
        "jdbc:postgresql://127.0.0.1:5432/testdb", "userName","password");
    if (connection != null) {
      System.out.println("Connected");
    } else {
      System.out.println("Failed to make connection!");
    }
  }
}

JDBC Tutorial - JDBC Oracle

The following code shows how to connect to Oracle database.

Example

import java.sql.Connection;
import java.sql.DriverManager;

public class Main {
  public static void main(String[] argv) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection connection = DriverManager.getConnection(
        "jdbc:oracle:thin:@localhost:1521:databaseName", "username","password");
    if (connection != null) {
      System.out.println("Connected");
    } else {
      System.out.println("Failed to make connection!");
    }
  }
}

JDBC Tutorial - JDBC Connections

Register JDBC Driver

In order to make a connection to the database system we need to register the driver for the database system in your program.
The driver name is database system dependent.

Example

The most common approach to register a driver is to use Java's Class.forName() method to load the driver's class file into memory, which automatically registers it.

try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException ex) {
   System.out.println("Error: unable to load driver class!");
   System.exit(1);
}

Database URL

After loading the driver, we can open a connection using the DriverManager.getConnection() method.
There are three overloaded DriverManager.getConnection() methods:

getConnection(String url)
getConnection(String url, Properties prop)
getConnection(String url, String user, String password)

A database URL is an address that points to the database.
Following table lists some popular JDBC driver names and database URL.

RDBMS	JDBC driver name	URL format
MySQL	com.mysql.jdbc.Driver	jdbc:mysql://hostname/databaseName
ORACLE	oracle.jdbc.driver.OracleDriver	jdbc:oracle:thin:@hostname:portNumber:databaseName
DB2	COM.ibm.db2.jdbc.net.DB2Driver	jdbc:db2:hostname:port Number/databaseName
Sybase	com.sybase.jdbc.SybDriver	jdbc:sybase:Tds:hostname: port Number/databaseName

Example 2

The following code shows how to use Oracle's thin driver and its corresponding URL.
The code assumes that there is Oracle database running on 192.0.0.1 IP address whose port is 1521.
The database name is EMP.

jdbc:oracle:thin:@192.0.0.1:1521:EMP

The following code shows how to pass in URL value and user name and password to getConnection() method to get a Connection object.

String URL = "jdbc:oracle:thin:@192.0.0.1:1521:EMP";
String USER = "username";
String PASS = "password"
Connection conn = DriverManager.getConnection(URL, USER, PASS);

Embed user name and password in a database URL

The second form of the DriverManager.getConnection( ) method requires only a database URL:

DriverManager.getConnection(String url);

To use the method above we have to embed the username and password to the database URL.
Here is a general form:

jdbc:oracle:driver:username/password@database

We can rewrite the example as follows:

String URL = "jdbc:oracle:thin:username/password@192.0.0.1:1521:EMP";
Connection conn = DriverManager.getConnection(URL);

Using a database URL and a Properties object

The third form of the DriverManager.getConnection() method requires a database URL and a Properties object:

DriverManager.getConnection(String url, Properties info);

A Properties object holds a set of keyword-value pairs.
The following code shows how to use URL and Properties object to make the same connection to Oracle database.

String URL = "jdbc:oracle:thin:@192.0.0.1:1521:EMP";
Properties info = new Properties( );
info.put( "user", "username" );
info.put( "password", "password" );

Connection conn = DriverManager.getConnection(URL, info);

Closing JDBC connections

Before exiting JDBC application, we are required to explicitly close all the connections to the database to end each database session.
To ensure that a connection is closed, put the close() method in a finally block since a finally block always executes regardless if an exception occurs or not.
To close above opened connection, call close() method from Connection object as follows:

} finally {
  // finally block used to close resources
  try {
    if (stmt != null)
      stmt.close();
  } catch (SQLException se2) {
  }
  try {
    if (conn != null)
      conn.close();
  } catch (SQLException se) {
    se.printStackTrace();
  }
}

JDBC Tutorial - JDBC Statements

After a connection is obtained we can interact with the database with sql statement.
Usually the sql statement is executed by Statement interface returned from Connection.
JDBC Statement is for general-purpose access to your database.
It is useful when using static SQL statements.
The Statement interface cannot accept parameters.
Statement object is created using the Connection object's createStatement() method as follows:

Statement stmt = conn.createStatement( );

Methods from Statement

We can use Statement to execute a SQL statement with one of its three execute methods.

boolean execute(String SQL)
executes SQL statements such as create database, create table. It returns true value if a ResultSet object can be retrieved; otherwise, it returns false.
int executeUpdate(String SQL)
executes SQL statements which will affect the data rows, for example, an INSERT, UPDATE, or DELETE statement. It returns the numbers of rows affected by the SQL statement.
ResultSet executeQuery(String SQL) executes a SELECT statement and get result back. Returns a ResultSet object.

Close Statement Obeject

We need to close a Statement object to clean up the allocated database resources.

If we close the Connection object first, it will also close the Statement object.

Statement stmt = null;
try {
   stmt = conn.createStatement( );
   stmt execute method();
}
catch (SQLException e) {

}
finally {
   stmt.close();
}

JDBC Tutorial - JDBC PreparedStatement

PreparedStatement is used to execute the SQL statements many times.  The PreparedStatement interface accepts input parameters at runtime. 

The PreparedStatement interface extends the Statement interface.

We can supply arguments dynamically for PreparedStatement.


Example
The following code shows how to create PreparedStatement Object. 

PreparedStatement pstmt = null;
try {
   String SQL = "Update Employees SET age = ? WHERE id = ?";
   pstmt = conn.prepareStatement(SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   . . .
}


Parameters in PreparedStatement
All parameters in JDBC are represented by the ? symbol.

? is known as the parameter marker.  

We must supply values for every parameter before executing the SQL statement.

The setXXX() methods from PreparedStatement bind values to the parameters, where XXX represents the Java data type.  

Unlike the Java array or List in collection framework.  

The index of parameters in PreparedStatement starts from position 1. 


Close PreparedStatement Obeject
We need to close PreparedStatement object to free up the resource allocated for it.

Closing the Connection object will close the PreparedStatement object as well. 

PreparedStatement pstmt = null;
try {
   String SQL = "Update Employees SET age = ? WHERE id = ?";
   pstmt = conn.prepareStatement(SQL);
}
catch (SQLException e) {
}
finally {
   pstmt.close();
}

JDBC Tutorial - JDBC CallableStatement Stored Procedures

The CallableStatement interface can accept runtime input parameters. 

CallableStatement is used to execute database stored procedures.  

Connection object can also create the CallableStatement to call a database stored procedure.

Example

Suppose, we have the following MySQL stored procedure.

DELIMITER $$

DROP PROCEDURE IF EXISTS `EMP`.`getEmpName` $$

CREATE PROCEDURE `EMP`.`getEmpName` 
   (IN EMP_ID INT, OUT EMP_FIRST VARCHAR(255))
BEGIN
   SELECT first INTO EMP_FIRST FROM Emp WHERE ID = EMP_ID;
END $$

DELIMITER ;

The stored procedure above defines two parameters, one is EMP_ID and the other is  EMP_FIRST. It returns the first name of an employee by employee id. 

IN and OUT before the parameter name tells the types of the parameters. 

IN is for data input and OUT is for data output.  

In the code above we pass IN the employee id and get OUT the first name. 

We can also have INOUT parameters which can both accept value and pass value out.

There are totally three types of parameters: IN, OUT, and INOUT. 



Both PreparedStatement and CallableStatement can accept parameters.  

The PreparedStatement object only uses the IN parameter.  

The CallableStatement object can use all three.


Parameter Description
IN A parameter whose value is unknown when the SQL statement is created.    

We pass values to IN parameters with the setXXX() methods.
OUT A parameter whose value is returned from the SQL statement.    

We get values from the OUT parameters with the getXXX() methods.
INOUT A parameter can pass value in and out.    

We bind variables with the setXXX() methods and retrieve values with the getXXX() methods.

Parameter	Description
IN	A parameter whose value is unknown when the SQL statement is created. We pass values to IN parameters with the `setXXX()` methods.
OUT	A parameter whose value is returned from the SQL statement. We get values from the OUT parameters with the `getXXX()` methods.
INOUT	A parameter can pass value in and out. We bind variables with the setXXX() methods and retrieve values with the getXXX() methods.

The following code shows how to call the stored procedure.

conn is an object of Connection.

CallableStatement cstmt = null;
String SQL = "{call getEmpName (?, ?)}";
cstmt = conn.prepareCall (SQL);

Close CallableStatement Obeject

We need to close the CallableStatement object to free up the resource.

Closing the Connection object first it will close the CallableStatement object

as well.

CallableStatement cstmt = null;
String SQL = "{call getEmpName (?, ?)}";
cstmt = conn.prepareCall (SQL);
cstmt.close();

JDBC Tutorial - JDBC ResultSet

The JDBC java.sql.ResultSet is  used to handle the result returned from the SQL

select statement. The SQL select statements reads data from a database and return

the data in a result set.  

The result from a select statement is in a tabular form. It has columns and rows. 

A ResultSet object maintains a cursor that points to the current row in the result

set.  

For a certain row we can use methods from java.sql.ResultSet to get the data

column by column.

Methods in ResultSet

The methods of the ResultSet interface have three categories:

Navigational methods moves the cursor back and forth.
Getter methods get the data from current row.
Update methods update the data at the current row.


ResultSet Types
The cursor is movable based on the properties of the ResultSet.  

These properties are set when creating the JDBC Statement.

The Resultset types are listed below, the default is TYPE_FORWARD_ONLY.


Type Description
ResultSet.TYPE_FORWARD_ONLY The cursor can only move forward.
ResultSet.TYPE_SCROLL_INSENSITIVE The cursor can scroll forwards and backwards. The ResultSet is not sensitive to changes made to the database after the ResultSet was created.
ResultSet.TYPE_SCROLL_SENSITIVE The cursor can scroll forwards and backwards. And the ResultSet is sensitive to changes made to the database after the result set was created.


Concurrency of ResultSet
The ResultSet Concurrency types are listed below.  The default concurrency type is CONCUR_READ_ONLY.


Concurrency Description
ResultSet.CONCUR_READ_ONLY Read-only result set. This is the default
ResultSet.CONCUR_UPDATABLE updateable result set.


Example
JDBC provides the following methods from Connection object  to create statements with certain types of ResultSet.


createStatement(int resultSetType, int resultSetConcurrency);
prepareStatement(String SQL, int resultSetType, int resultSetConcurrency);
prepareCall(String sql, int resultSetType, int resultSetConcurrency);

The following code creates a Statement object to create a  forward-only, read only ResultSet object

Statement stmt = conn.createStatement(
                        ResultSet.TYPE_FORWARD_ONLY,
                        ResultSet.CONCUR_READ_ONLY);
 

ResultSet Navigation
We can use the following methods from ResultSet interface to move the cursor.

Methods Description
beforeFirst() Moves the cursor to before the first row
afterLast() Moves the cursor to after the last row
first() Moves the cursor to the first row
last() Moves the cursor to the last row
absolute(int row) Moves the cursor to the specified row
relative(int row) Moves the cursor number of rows forward or backwards relative to where it is.
previous() Moves the cursor to the previous row. 
next() Moves the cursor to the next row.
int getRow() Returns the row number that the cursor is pointing to.
moveToInsertRow() Moves the cursor to where we can insert a new row into the database.  The current row number is not changed. 
moveToCurrentRow() Moves the cursor back to the current row if the cursor is currently at the insert row; otherwise, this method does nothing


 

ResultSet Column Data
We have two ways to get data in the ResultSet. 


By Column Index
By Column Name

For example, the following two methods get int value from a column. The first one is by column name and second one is by column index.

public int getInt(String columnName)
public int getInt(int columnIndex) 

The column index starts at 1. 


Update ResultSet
We can update current row in ResultSet object.

We need to indicate the column name or index during the update. 

For example, to update a String column of the current row we can use the  the following methods.

public void updateString(int columnIndex, String s) throws SQLException
public void updateString(String columnName, String s) throws SQLException


To push the update changes to the database, invoke one of the following methods.


Methods Description
updateRow() Updates the corresponding row in the database.
deleteRow() Deletes the current row from the database
refreshRow() Refreshes the result set to reflect any changes in the database.
cancelRowUpdates() Cancels any updates made on the current row.
insertRow() Inserts a row into the database when the cursor is pointing to the insert row.



JDBC Tutorial - JDBC Data Types

Type	Description
ResultSet.TYPE_FORWARD_ONLY	The cursor can only move forward.
ResultSet.TYPE_SCROLL_INSENSITIVE	The cursor can scroll forwards and backwards. The ResultSet is not sensitive to changes made to the database after the ResultSet was created.
ResultSet.TYPE_SCROLL_SENSITIVE	The cursor can scroll forwards and backwards. And the ResultSet is sensitive to changes made to the database after the result set was created.

Concurrency	Description
ResultSet.CONCUR_READ_ONLY	Read-only result set. This is the default
ResultSet.CONCUR_UPDATABLE	updateable result set.

Methods	Description
beforeFirst()	Moves the cursor to before the first row
afterLast()	Moves the cursor to after the last row
first()	Moves the cursor to the first row
last()	Moves the cursor to the last row
absolute(int row)	Moves the cursor to the specified row
relative(int row)	Moves the cursor number of rows forward or backwards relative to where it is.
previous()	Moves the cursor to the previous row.
next()	Moves the cursor to the next row.
int getRow()	Returns the row number that the cursor is pointing to.
moveToInsertRow()	Moves the cursor to where we can insert a new row into the database. The current row number is not changed.
moveToCurrentRow()	Moves the cursor back to the current row if the cursor is currently at the insert row; otherwise, this method does nothing

Methods	Description
updateRow()	Updates the corresponding row in the database.
deleteRow()	Deletes the current row from the database
refreshRow()	Refreshes the result set to reflect any changes in the database.
cancelRowUpdates()	Cancels any updates made on the current row.
insertRow()	Inserts a row into the database when the cursor is pointing to the insert row.

Java has a data type system, for example, int, long, float, double, string. Database systems also have a type system, such as int, char, varchar, text, blob, clob. 

The JDBC driver can convert the Java data type to the appropriate database type back and forth. 

The following table lists the default database data type and the Java data type mapping. 

The mapping is used when calling the setXXX() method from the PreparedStatement or CallableStatement object or the ResultSet.updateXXX()/getXXX() method. 


SQL JDBC/Java setXXX getXXX updateXXX
VARCHAR java.lang.String setString getString updateString
CHAR java.lang.String setString getString updateString
LONGVARCHAR java.lang.String setString updateString
BIT boolean setBoolean getBoolean updateBoolean
NUMERIC java.math.BigDecimal setBigDecimal getBigDecimal updateBigDecimal
TINYINT byte setByte getByte updateByte
SMALLINT short setShort getShort updateShort
INTEGER int setInt getInt updateInt
BIGINT long setLong getLong updateLong
REAL float setFloat getFloat updateFloat
FLOAT float setFloat getFloat updateFloat
DOUBLE double setDouble getDouble updateDouble
VARBINARY byte[ ] setBytes getBytes updateBytes
BINARY byte[] setBytes getBytes updateBytes
DATE java.sql.Date setDate getDate updateDate
TIME java.sql.Time setTime getTime updateTime
TIMESTAMP java.sql.Timestamp setTimestamp getTimestamp updateTimestamp
CLOB java.sql.Clob setClob getClob updateClob
BLOB java.sql.Blob setBlob getBlob updateBlob
ARRAY java.sql.Array setARRAY getARRAY updateARRAY
REF java.sql.Ref setRef getRef updateRef
STRUCT java.sql.Struct setStruct getStruct updateStruct

SQL	JDBC/Java	setXXX	getXXX	updateXXX
VARCHAR	java.lang.String	setString	getString	updateString
CHAR	java.lang.String	setString	getString	updateString
LONGVARCHAR	java.lang.String	setString	updateString
BIT	boolean	setBoolean	getBoolean	updateBoolean
NUMERIC	java.math.BigDecimal	setBigDecimal	getBigDecimal	updateBigDecimal
TINYINT	byte	setByte	getByte	updateByte
SMALLINT	short	setShort	getShort	updateShort
INTEGER	int	setInt	getInt	updateInt
BIGINT	long	setLong	getLong	updateLong
REAL	float	setFloat	getFloat	updateFloat
FLOAT	float	setFloat	getFloat	updateFloat
DOUBLE	double	setDouble	getDouble	updateDouble
VARBINARY	byte[ ]	setBytes	getBytes	updateBytes
BINARY	byte[]	setBytes	getBytes	updateBytes
DATE	java.sql.Date	setDate	getDate	updateDate
TIME	java.sql.Time	setTime	getTime	updateTime
TIMESTAMP	java.sql.Timestamp	setTimestamp	getTimestamp	updateTimestamp
CLOB	java.sql.Clob	setClob	getClob	updateClob
BLOB	java.sql.Blob	setBlob	getBlob	updateBlob
ARRAY	java.sql.Array	setARRAY	getARRAY	updateARRAY
REF	java.sql.Ref	setRef	getRef	updateRef
STRUCT	java.sql.Struct	setStruct	getStruct	updateStruct


Example
The following examples shows how to convert Java Date and Time classes  

to match the SQL data type.



public class Main {
  public static void main(String[] args) {
    java.util.Date javaDate = new java.util.Date();
    long javaTime = javaDate.getTime();
    System.out.println("The Java Date is:" + javaDate.toString());

    // SQL DATE
    java.sql.Date sqlDate = new java.sql.Date(javaTime);
    System.out.println("The SQL DATE is: " + sqlDate.toString());

    // SQL TIME
    java.sql.Time sqlTime = new java.sql.Time(javaTime);
    System.out.println("The SQL TIME is: " + sqlTime.toString());
    
    // SQL TIMESTAMP
    java.sql.Timestamp sqlTimestamp = new java.sql.Timestamp(javaTime);
    System.out.println("The SQL TIMESTAMP is: " + sqlTimestamp.toString());
  }
}

The code above generates the following result.






Handling NULL Values
SQL uses NULL values to indicate empty 

while Java null means no memory has been allocated.



To handle NULL value from database properly  

we should avoid using getXXX() methods that return primitive data types. 



Since the JDBC driver may convert the NULL value to 0 and 

we may have 0 value in the same column.



Or we can use the wasNull() method from the ResultSet to check 

if the value was null. 



Statement stmt = conn.createStatement( );
String sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = stmt.executeQuery(sql);

int id = rs.getInt(1);
if( rs.wasNull( ) ) {
   id = 0; // or -1
}

 
 

SQL Timestamp
A simple table script in Oracle database.



CREATE TABLE Person ( 
  USER_ID       NUMBER (5)    NOT NULL, 
  USERNAME      VARCHAR2 (20)  NOT NULL, 
  CREATED_BY    VARCHAR2 (20)  NOT NULL, 
  CREATED_DATE  DATE          NOT NULL, 
  PRIMARY KEY ( USER_ID ) 
 )

Use the setTimestamp from PreparedStatement to insert Timestamp to database.



import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Main {
  private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
  private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:YourDatabase";
  private static final String DB_USER = "user";
  private static final String DB_PASSWORD = "password";

  public static void main(String[] argv) throws Exception {
    Class.forName(DB_DRIVER);
    Connection dbConnection = DriverManager.getConnection(DB_CONNECTION,
        DB_USER, DB_PASSWORD);
    PreparedStatement preparedStatement = null;
    java.util.Date today = new java.util.Date();
    String insertTableSQL = "INSERT INTO DBUSER"
        + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
        + "(?,?,?,?)";
    preparedStatement = dbConnection.prepareStatement(insertTableSQL);
    preparedStatement.setTimestamp(4,new java.sql.Timestamp(today.getTime()));

    dbConnection.commit();
    dbConnection.close();
  }
}

Or we can use setDate method.



java.util.Date today = new java.util.Date();

preparedStatement.setDate(4, new java.sql.Date(today.getTime()));
 

 JDBC Tutorial - JDBC Transactions





Transactions treats a group of SQL statements as one logical unit, 

if any statement fails, the whole transaction fails and rolls back.



By default the JDBC Connection is in auto-commit mode, 

which means every SQL statement is committed to the database when it is completed. 



To enable manual-transaction, use the Connection object's setAutoCommit() method.  



For example, the following code turns off auto-commit:

conn.setAutoCommit(false);

To commit the changes then call commit() method on connection object as follows:

conn.commit( );

To roll back updates to the database, use the following code:

conn.rollback( );

The following example shows how to use commit and rollback.

 
try{
   conn.setAutoCommit(false);
   Statement stmt = conn.createStatement();
   
   String SQL = "INSERT INTO Employees VALUES (1, 'name')";
   stmt.executeUpdate(SQL);  
   String SQL = "INSERT INTO Employees VALUES (2, 'anotherName')";
   stmt.executeUpdate(SQL);
   conn.commit();
}catch(SQLException se){
   conn.rollback();
}


Using Savepoints
A savepoint defines a rollback point within a transaction.



If an error occurs after a savepoint,

we can rollback to undo either all the changes or only the changes made after 

the savepoint. 



The Connection object has two methods to related to savepoints.



setSavepoint(String savepointName) defines a new savepoint. 

It also returns a Savepoint object. 



releaseSavepoint(Savepoint savepointName) deletes a savepoint.  



It requires a Savepoint object as a parameter, 

which is generated by the setSavepoint() method. 



rollback(String savepointName) method rolls back work to the specified savepoint.



The following example illustrates the use of a Savepoint object:



try{
   conn.setAutoCommit(false);
   Statement stmt = conn.createStatement();
   
   Savepoint savepoint1 = conn.setSavepoint("Savepoint1");
   String SQL = "INSERT INTO Employees VALUES (1, 'name')";
   stmt.executeUpdate(SQL);  
   String SQL = "INSERT INTO Employees VALUES (2, 'new name')";
   stmt.executeUpdate(SQL);
   conn.commit();

}catch(SQLException se){
   conn.rollback(savepoint1);
}


Example
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Main {
  private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
  private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:YourDatabase";
  private static final String DB_USER = "user";
  private static final String DB_PASSWORD = "password";

  public static void main(String[] argv) throws Exception {
    Class.forName(DB_DRIVER);
    Connection dbConnection = DriverManager.getConnection(DB_CONNECTION,
        DB_USER, DB_PASSWORD);

    PreparedStatement preparedStatementInsert = null;
    PreparedStatement preparedStatementUpdate = null;

    String insertTableSQL = "INSERT INTO Person"
        + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES" + "(?,?,?,?)";

    String updateTableSQL = "UPDATE Person SET USERNAME =? "
        + "WHERE USER_ID = ?";

    java.util.Date today = new java.util.Date();
    dbConnection.setAutoCommit(false);

    preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL);
    preparedStatementInsert.setInt(1, 9);
    preparedStatementInsert.setString(2, "101");
    preparedStatementInsert.setString(3, "system");
    preparedStatementInsert.setTimestamp(4,
        new java.sql.Timestamp(today.getTime()));
    preparedStatementInsert.executeUpdate();

    preparedStatementUpdate = dbConnection.prepareStatement(updateTableSQL);
    preparedStatementUpdate.setString(1, "new string");
    preparedStatementUpdate.setInt(2, 999);
    preparedStatementUpdate.executeUpdate();

    dbConnection.commit();
    dbConnection.close();
  }
}

JDBC Tutorial - JDBC Exceptions Handling

A SQL exception can occur in the driver or from the database. 

For example, a syntax error in the SQL statement will result in a SQL exception. 

Or we do not have permission to update a table.



When such an exception occurs, an object of type SQLException will be passed 

to the catch clause.



The SQLException object has the following methods :




Method Description
getErrorCode( ) Gets the error number.
getMessage( ) Gets error message.
getSQLState( ) Gets the SQLstate string. 

For a database error, the five-digit XOPEN SQLstate code is returned. 
getNextException( ) Gets the next Exception object in the exception chain.
printStackTrace( ) Prints the current exception and its backtrace to a standard error stream.
printStackTrace(PrintStream s) Prints this throwable and its backtrace to the print stream specified.
printStackTrace(PrintWriter w) Prints this throwable and its backtrace to the print writer specified.

Method	Description
getErrorCode( )	Gets the error number.
getMessage( )	Gets error message.
getSQLState( )	Gets the SQLstate string. For a database error, the five-digit XOPEN SQLstate code is returned.
getNextException( )	Gets the next Exception object in the exception chain.
printStackTrace( )	Prints the current exception and its backtrace to a standard error stream.
printStackTrace(PrintStream s)	Prints this throwable and its backtrace to the print stream specified.
printStackTrace(PrintWriter w)	Prints this throwable and its backtrace to the print writer specified.


Example
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

 
public class Main {
  // JDBC driver name and database URL
  static final String JDBC_DRIVER = "org.hsqldb.jdbcDriver";
  static final String DB_URL = "jdbc:hsqldb:mem:db_file";

  // Database credentials
  static final String USER = "sa";
  static final String PASS = "";

  public static void main(String[] args) {
    Connection conn = null;
    Statement stmt = null;
    try {
      // STEP 2: Register JDBC driver
      Class.forName(JDBC_DRIVER);

      // STEP 3: Open a connection
      System.out.println("Connecting to database...");
      conn = DriverManager.getConnection(DB_URL, USER, PASS);

      // STEP 4: Execute a query
      System.out.println("Creating statement...");
      stmt = conn.createStatement();
      String sql;
      sql = "SELECT id, first, last, age FROM Employees";
      stmt.executeUpdate("CREATE TABLE Employees ( id INTEGER IDENTITY, first VARCHAR(256),  last VARCHAR(256),age INTEGER)");
      stmt.executeUpdate("INSERT INTO Employees VALUES(1,'Jack','Smith', 100)");

      ResultSet rs = stmt.executeQuery(sql);

      // STEP 5: Extract data from result set
      while (rs.next()) {
        // Retrieve by column name
        int id = rs.getInt("id");
        int age = rs.getInt("age");
        String first = rs.getString("first");
        String last = rs.getString("last");

        System.out.print("ID: " + id);
        System.out.print(", Age: " + age);
        System.out.print(", First: " + first);
        System.out.println(", Last: " + last);
      }
      // STEP 6: Clean-up environment
      rs.close();
      stmt.close();
      conn.close();
    } catch (SQLException se) {
      se.printStackTrace();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      // finally block used to close resources
      try {
        if (stmt != null)
          stmt.close();
      } catch (SQLException se2) {
      }
      try {
        if (conn != null)
          conn.close();
      } catch (SQLException se) {
        se.printStackTrace();
      }
    }
    System.out.println("Goodbye!");
  }
}

JDBC Tutorial - JDBC Batch Processing

Batch Processing can group SQL statements into one block and pass them with one call to the database.
Batch process reduces the amount of communication overhead and improves performance.

We can use DatabaseMetaData.supportsBatchUpdates() method to check if the database supports batch update processing.

The addBatch() method of Statement, PreparedStatement, and CallableStatement is used to add individual statements to the batch.

The executeBatch() is used to execute of the batch and returns an array of integers. Each element of the array represents the update count for the corresponding update statement.

We can remove statements added with the addBatch() method with the clearBatch() method.
The following code shows how to do a batch update using Statement object.

Statement stmt = conn.createStatement();
conn.setAutoCommit(false);

String SQL = "INSERT INTO Employees VALUES(2,'name')";
stmt.addBatch(SQL);
SQL = "INSERT INTO Employees VALUES(2,'new name')";
stmt.addBatch(SQL);
SQL = "UPDATE Employees SET age = 5 WHERE id = 1";
stmt.addBatch(SQL);
int[] count = stmt.executeBatch();
conn.commit();

Batching with PrepareStatement Object

The following code shows how to do a batch update using PrepareStatement object

String SQL = "INSERT INTO Employees (id, firstName)VALUES(?, ?)";
PreparedStatemen pstmt = conn.prepareStatement(SQL);

//Set auto-commit to false
conn.setAutoCommit(false);

// Set the variables
pstmt.setInt( 1, 101 );
pstmt.setString( 2, "name" );
// Add it to the batch
pstmt.addBatch();

// Set the variables
pstmt.setInt( 1, 102 );
pstmt.setString( 2, "new name" );
// Add it to the batch
pstmt.addBatch();

//add more batches
//...
//Create an int[] to hold returned values
int[] count = stmt.executeBatch();

//Explicitly commit statements to apply changes
conn.commit();

Example

The following code is a full runnable example showing how to do batch in JDBC.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Main {
  private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
  private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:YourDatabase";
  private static final String DB_USER = "user";
  private static final String DB_PASSWORD = "password";

  public static void main(String[] argv) throws Exception {
    Connection dbConnection = null;
    PreparedStatement preparedStatement = null;
    Class.forName(DB_DRIVER);
    dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
        DB_PASSWORD);

    String insertTableSQL = "INSERT INTO Person"
        + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
        + "(?,?,?,?)";
    preparedStatement = dbConnection.prepareStatement(insertTableSQL);

    dbConnection.setAutoCommit(false);

    java.util.Date today = new java.util.Date();

    preparedStatement.setInt(1, 101);
    preparedStatement.setString(2, "101");
    preparedStatement.setString(3, "system");
    preparedStatement.setTimestamp(4,new java.sql.Timestamp(today.getTime()));
    preparedStatement.addBatch();

    preparedStatement.setInt(1, 102);
    preparedStatement.setString(2, "102");
    preparedStatement.setString(3, "system");
    preparedStatement.setTimestamp(4,new java.sql.Timestamp(today.getTime()));
    preparedStatement.addBatch();

    preparedStatement.setInt(1, 103);
    preparedStatement.setString(2, "103");
    preparedStatement.setString(3, "system");
    preparedStatement.setTimestamp(4,new java.sql.Timestamp(today.getTime()));
    preparedStatement.addBatch();

    preparedStatement.executeBatch();

    dbConnection.commit();

    preparedStatement.close();
    dbConnection.close();

  }
}

JDBC Tutorial - JDBC ASCII and Binary Data

We can use a PreparedStatement object to save image files, doc files or other binary data into database table which has CLOB and BLOB data typed column.

setAsciiStream() saves large ASCII values.
setCharacterStream() saves large UNICODE values.
setBinaryStream() saves large binary values.

Example

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {
  static final String JDBC_DRIVER = "org.hsqldb.jdbcDriver";
  static final String DB_URL = "jdbc:hsqldb:mem:db_file";
  static final String USER = "sa";
  static final String PASS = "";

  public static void main(String[] args) throws Exception {
    Connection conn = null;
    PreparedStatement pstmt = null;
    Statement stmt = null;
    ResultSet rs = null;
    Class.forName(JDBC_DRIVER);
    conn = DriverManager.getConnection(DB_URL, USER, PASS);
    stmt = conn.createStatement();
    createXMLTable(stmt);
    File f = new File("build.xml");
    long fileLength = f.length();
    FileInputStream fis = new FileInputStream(f);
    String SQL = "INSERT INTO XML_Data VALUES (?,?)";
    pstmt = conn.prepareStatement(SQL);
    pstmt.setInt(1, 100);
    pstmt.setAsciiStream(2, fis, (int) fileLength);
    pstmt.execute();
    fis.close();
    SQL = "SELECT Data FROM XML_Data WHERE id=100";
    rs = stmt.executeQuery(SQL);
    if (rs.next()) {
      InputStream xmlInputStream = rs.getAsciiStream(1);
      int c;
      ByteArrayOutputStream bos = new ByteArrayOutputStream();

     while ((c = xmlInputStream.read()) != -1)
        bos.write(c);
      System.out.println(bos.toString());
    }
    rs.close();
    stmt.close();
    pstmt.close();
    conn.close();
  }

  public static void createXMLTable(Statement stmt) throws SQLException {
    String streamingDataSql = "CREATE TABLE XML_Data (id INTEGER, Data CLOB)";
       // stmt.executeUpdate("DROP TABLE XML_Data");
    stmt.executeUpdate(streamingDataSql);
  }
}

JDBC Tutorial - JDBC Create Database

The following code shows how to create a database in MySQL database by using JDBC connection.
The SQL used to create database is listed as follows.

CREATE DATABASE STUDENTS

The code loads the driver first and then makes a connection to the database.
Then it creates a Statement and issues the create database command.

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Main {
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  static final String DB_URL = "jdbc:mysql://localhost/";

  static final String USER = "username";
  static final String PASS = "password";

  public static void main(String[] args) throws Exception {
    Connection conn = null;
    Statement stmt = null;

    Class.forName(JDBC_DRIVER);
    conn = DriverManager.getConnection(DB_URL, USER, PASS);
    stmt = conn.createStatement();

    String sql = "CREATE DATABASE STUDENTS";
    stmt.executeUpdate(sql);
    System.out.println("Database created successfully...");

    stmt.close();
    conn.close();
  }
}

JDBC Tutorial - JDBC Select Database

The following code shows how to select a database using the JDBC connection URL.
The code hardcoded the database name in the JDBC connection URL as follows.

jdbc:mysql://localhost/STUDENTS

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Main {
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";

  static final String USER = "username";
  static final String PASS = "password";

  public static void main(String[] args) throws Exception {
    Connection conn = null;
    Statement stmt = null;

    Class.forName(JDBC_DRIVER);
    conn = DriverManager.getConnection(DB_URL, USER, PASS);

    stmt.close();
    conn.close();
  }
}

JDBC Tutorial - JDBC Drop Database

The following code shows how to drop a database.
It issues the following SQL command to do the drop action.

DROP DATABASE STUDENTS

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Main {
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  static final String DB_URL = "jdbc:mysql://localhost/";

  static final String USER = "username";
  static final String PASS = "password";

  public static void main(String[] args) throws Exception {
    Connection conn = null;
    Statement stmt = null;

    Class.forName(JDBC_DRIVER);
    conn = DriverManager.getConnection(DB_URL, USER, PASS);
    System.out.println("Deleting database...");
    stmt = conn.createStatement();

    String sql = "DROP DATABASE STUDENTS";
    stmt.executeUpdate(sql);

    stmt.close();
    conn.close();
  }
}

JDBC Tutorial - JDBC Create Table

The following code shows how to create a new table through JDBC connection.
It issues the following SQL command to create a table.

CREATE TABLE Person
(id INTEGER not NULL, 
 firstName VARCHAR(50), 
 lastName VARCHAR(50), 
 age INTEGER, 
 PRIMARY KEY ( id ))

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Main {
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";

  static final String USER = "username";
  static final String PASS = "password";

  public static void main(String[] args) throws Exception {
    Connection conn = null;
    Statement stmt = null;

    Class.forName(JDBC_DRIVER);
    conn = DriverManager.getConnection(DB_URL, USER, PASS);
    System.out.println("Deleting database...");
    stmt = conn.createStatement();

    conn = DriverManager.getConnection(DB_URL, USER, PASS);

    stmt = conn.createStatement();

    String sql = "CREATE TABLE Person" + "(id INTEGER not NULL, "
        + " firstName VARCHAR(50), " + " lastName VARCHAR(50), "
        + " age INTEGER, " + " PRIMARY KEY ( id ))";

    stmt.executeUpdate(sql);
    System.out.println("Created table in given database...");
    stmt.close();
    conn.close();
  }
}

PreparedStatement to create a table

The following code shows how to use PreparedStatement to create a table.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Main {
  private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
  private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:YourDatabase";
  private static final String DB_USER = "user";
  private static final String DB_PASSWORD = "password";

  public static void main(String[] argv) throws Exception {
    Connection dbConnection = null;
    PreparedStatement preparedStatement = null;

    String createTableSQL = "CREATE TABLE Person("
        + "USER_ID NUMBER(5) NOT NULL, "
        + "USERNAME VARCHAR(20) NOT NULL, "
        + "CREATED_BY VARCHAR(20) NOT NULL, "
        + "CREATED_DATE DATE NOT NULL, " 
        + "PRIMARY KEY (USER_ID) "
        + ")";

    Class.forName(DB_DRIVER);
    dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
        DB_PASSWORD);

    preparedStatement = dbConnection.prepareStatement(createTableSQL);

    System.out.println(createTableSQL);

    preparedStatement.executeUpdate();

    preparedStatement.close();
    dbConnection.close();

  }
}

JDBC Tutorial - JDBC Drop Table
The following code shows how to drop a table from MySQL database.
It issues the drop table command as follows.

DROP TABLE Person

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Main {
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";

  static final String USER = "username";
  static final String PASS = "password";

  public static void main(String[] args) throws Exception {
    Connection conn = null;
    Statement stmt = null;

    Class.forName(JDBC_DRIVER);
    conn = DriverManager.getConnection(DB_URL, USER, PASS);
    System.out.println("Deleting database...");
    stmt = conn.createStatement();

    conn = DriverManager.getConnection(DB_URL, USER, PASS);

    stmt = conn.createStatement();

    String sql = "DROP TABLE Person ";

    stmt.executeUpdate(sql);

    System.out.println("Created table in given database...");
    stmt.close();
    conn.close();
  }
}

JDBC Tutorial - JDBC Insert Record

The following code shows how to insert record to database table.
It uses the Statement to execute the hardcoded sql commands.

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Main {
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";

  static final String USER = "username";
  static final String PASS = "password";

  public static void main(String[] args) throws Exception {
    Connection conn = null;
    Statement stmt = null;

    Class.forName(JDBC_DRIVER);
    conn = DriverManager.getConnection(DB_URL, USER, PASS);
    System.out.println("Deleting database...");
    stmt = conn.createStatement();

    conn = DriverManager.getConnection(DB_URL, USER, PASS);

    stmt = conn.createStatement();

    String sql = "INSERT INTO Person VALUES (1, 'A', 'B', 18)";
    stmt.executeUpdate(sql);
    sql = "INSERT INTO Person VALUES (2, 'C', 'D', 25)";
    stmt.executeUpdate(sql);
    sql = "INSERT INTO Person VALUES (3, 'E', 'F', 30)";
    stmt.executeUpdate(sql);
    sql = "INSERT INTO Person VALUES(4, 'S', 'M', 28)";
    stmt.executeUpdate(sql);
    stmt.close();
    conn.close();
  }
}

Example PreparedStatement

The following code shows how to insert a record using PreparedStatement.
It is using the Oracle JDBC driver.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Main {
  private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
  private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:YourDatabase";
  private static final String DB_USER = "user";
  private static final String DB_PASSWORD = "password";

  public static void main(String[] argv) throws Exception {
    Connection dbConnection = null;
    PreparedStatement preparedStatement = null;
    Class.forName(DB_DRIVER);
    dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
        DB_PASSWORD);

    String insertTableSQL = "INSERT INTO Person"
        + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
        + "(?,?,?,?)";

    preparedStatement = dbConnection.prepareStatement(insertTableSQL);

    preparedStatement.setInt(1, 11);
    preparedStatement.setString(2, "yourName");
    preparedStatement.setString(3, "system");
    java.util.Date today = new java.util.Date();

    preparedStatement.setTimestamp(4,
        new java.sql.Timestamp(today.getTime()));

    preparedStatement.executeUpdate();

    preparedStatement.close();
    dbConnection.close();

  }
}

JDBC Tutorial - JDBC Select Record

The following code shows how to retrieve data from a database table.
It issues a select command and gets the result by reading the ResultSet interface.

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Main {
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";

  static final String USER = "username";
  static final String PASS = "password";

  public static void main(String[] args) throws Exception {
    Connection conn = null;
    Statement stmt = null;

    Class.forName(JDBC_DRIVER);
    conn = DriverManager.getConnection(DB_URL, USER, PASS);
    System.out.println("Deleting database...");
    stmt = conn.createStatement();

    conn = DriverManager.getConnection(DB_URL, USER, PASS);

    stmt = conn.createStatement();
    String sql = "SELECT id, first, last, age FROM Person";
    ResultSet rs = stmt.executeQuery(sql);
    while (rs.next()) {
      // Retrieve by column name
      int id = rs.getInt("id");
      int age = rs.getInt("age");
      String first = rs.getString("firstName");
      String last = rs.getString("lastName");

      // Display values
      System.out.print("ID: " + id);
      System.out.print(", Age: " + age);
      System.out.print(", First: " + first);
      System.out.println(", Last: " + last);
    }
    rs.close();
    stmt.close();
    conn.close();
  }
}

Example PreparedStatement

The following code shows how to execute a select statement with PreparedStatement by using the Oracle JDBC driver.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Main {
  private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
  private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:YourDatabase";
  private static final String DB_USER = "user";
  private static final String DB_PASSWORD = "password";

  public static void main(String[] argv) throws Exception {
    Connection dbConnection = null;
    PreparedStatement preparedStatement = null;
    Class.forName(DB_DRIVER);
    dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
        DB_PASSWORD);

    String selectSQL = "SELECT USER_ID, USERNAME FROM Person WHERE USER_ID = ?";
    preparedStatement = dbConnection.prepareStatement(selectSQL);
    preparedStatement.setInt(1, 1001);

    ResultSet rs = preparedStatement.executeQuery();

    while (rs.next()) {

      String userid = rs.getString("USER_ID");
      String username = rs.getString("USERNAME");

      System.out.println("userid : " + userid);
      System.out.println("username : " + username);

    }

    preparedStatement.close();
    dbConnection.close();

  }
}

JDBC Tutorial - JDBC Update Record

The following code shows how to update a record in database table.
After updating the table, it uses select statement to check the result.

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Main {
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";

  static final String USER = "username";
  static final String PASS = "password";

  public static void main(String[] args) throws Exception {
    Connection conn = null;
    Statement stmt = null;

    Class.forName(JDBC_DRIVER);
    conn = DriverManager.getConnection(DB_URL, USER, PASS);
    System.out.println("Deleting database...");
    stmt = conn.createStatement();

    conn = DriverManager.getConnection(DB_URL, USER, PASS);

    stmt = conn.createStatement();
    
      String sql = "UPDATE Registration SET age = 30 WHERE id in (1, 2)";
        stmt.executeUpdate(sql);
    
    sql = "SELECT id, first, last, age FROM Person";
    ResultSet rs = stmt.executeQuery(sql);
    while (rs.next()) {
      // Retrieve by column name
      int id = rs.getInt("id");
      int age = rs.getInt("age");
      String first = rs.getString("firstName");
      String last = rs.getString("lastName");

      // Display values
      System.out.print("ID: " + id);
      System.out.print(", Age: " + age);
      System.out.print(", First: " + first);
      System.out.println(", Last: " + last);
    }
    rs.close();
    stmt.close();
    conn.close();
  }
}

Example PreparedStatement

The following code shows how to use PreparedStatement to update database record with Oracle JDBC driver.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Main {
  private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
  private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:YourDatabase";
  private static final String DB_USER = "user";
  private static final String DB_PASSWORD = "password";

  public static void main(String[] argv) throws Exception {
    Connection dbConnection = null;
    PreparedStatement preparedStatement = null;
    Class.forName(DB_DRIVER);
    dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
        DB_PASSWORD);
    String updateTableSQL = "UPDATE Person SET USERNAME = ? "
        + " WHERE USER_ID = ?";
    preparedStatement = dbConnection.prepareStatement(updateTableSQL);

    preparedStatement.setString(1, "newValue");
    preparedStatement.setInt(2, 1001);

    preparedStatement.executeUpdate();

    preparedStatement.executeUpdate();
    preparedStatement.close();
    dbConnection.close();

  }
}

JDBC Tutorial - JDBC Delete Record

The following code shows how to delete records from a table.
It issues the delete sql command.

DELETE FROM Person WHERE id = 1

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
// w w  w .j  a  v  a2s  . co m
public class Main {
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";

  static final String USER = "username";
  static final String PASS = "password";

  public static void main(String[] args) throws Exception {
    Connection conn = null;
    Statement stmt = null;

    Class.forName(JDBC_DRIVER);
    conn = DriverManager.getConnection(DB_URL, USER, PASS);
    System.out.println("Deleting database...");
    stmt = conn.createStatement();

    conn = DriverManager.getConnection(DB_URL, USER, PASS);

    stmt = conn.createStatement();

    String sql = "

The following code shows how to delete records from a table.

It issues the delete sql command.

DELETE FROM Person WHERE id = 1

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
// w w  w .j  a  v  a2s  . co m
public class Main {
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";

  static final String USER = "username";
  static final String PASS = "password";

  public static void main(String[] args) throws Exception {
    Connection conn = null;
    Statement stmt = null;

    Class.forName(JDBC_DRIVER);
    conn = DriverManager.getConnection(DB_URL, USER, PASS);
    System.out.println("Deleting database...");
    stmt = conn.createStatement();

    conn = DriverManager.getConnection(DB_URL, USER, PASS);

    stmt = conn.createStatement();

    String sql = "DELETE FROM Person

The following code shows how to delete records from a table.

It issues the delete sql command.

DELETE FROM Person WHERE id = 1

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Main {
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";

  static final String USER = "username";
  static final String PASS = "password";

  public static void main(String[] args) throws Exception {
    Connection conn = null;
    Statement stmt = null;

    Class.forName(JDBC_DRIVER);
    conn = DriverManager.getConnection(DB_URL, USER, PASS);
    System.out.println("Deleting database...");
    stmt = conn.createStatement();

    conn = DriverManager.getConnection(DB_URL, USER, PASS);

    stmt = conn.createStatement();

    String sql = "DELETE FROM Person WHERE id = 1";
    stmt.executeUpdate(sql);
    stmt.close();
    conn.close();
  }
}

Example PreparedStatement

The following code shows how to delete a record with PreparedStatement by using the Oracle JDBC Driver.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Main {
  private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
  private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:YourDatabase";
  private static final String DB_USER = "user";
  private static final String DB_PASSWORD = "password";

  public static void main(String[] argv) throws Exception {
    Connection dbConnection = null;
    PreparedStatement preparedStatement = null;
    Class.forName(DB_DRIVER);
    dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
        DB_PASSWORD);
    String deleteSQL = "DELETE Person WHERE USER_ID = ?";
    preparedStatement = dbConnection.prepareStatement(deleteSQL);
    preparedStatement.setInt(1, 1001);

    preparedStatement.executeUpdate();
    preparedStatement.close();
    dbConnection.close();

  }
}

JDBC Tutorial - JDBC Stored Procedure In Parameter

The following example shows how to call a stored procedure with in parameters.
A stored procedure for Oracle written in PL/SQL is listed below.

It inserts a row to Person table. The parameters in the procedure are all marked with IN as input parameters.

CREATE OR REPLACE PROCEDURE insertPERSON(
     p_userid IN PERSON.USER_ID%TYPE,
     p_username IN PERSON.USERNAME%TYPE,
     p_createdby IN PERSON.CREATED_BY%TYPE,
     p_date IN PERSON.CREATED_DATE%TYPE)
IS
BEGIN

  INSERT INTO PERSON ("USER_ID", "USERNAME", "CREATED_BY", "CREATED_DATE") 
  VALUES (p_userid, p_username,p_createdby, p_date);

  COMMIT;

END;
/


Example
The following code creates a CallableStatement from Connection and 

passes in the name of the stored procedure.



The parameters for the stored procedure are all marked with question marks.



Then the parameters is set to a value with setXXX method from CallableStatement.



import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

public class Main {
  private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
  private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:YourDatabase";
  private static final String DB_USER = "user";
  private static final String DB_PASSWORD = "password";

  public static void main(String[] argv) throws Exception {
    Class.forName(DB_DRIVER);
    Connection dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
        DB_PASSWORD);

    CallableStatement callableStatement = null;
    String insertStoreProc = "{call insertPERSON(?,?,?,?)}";

    java.util.Date today = new java.util.Date();
    callableStatement = dbConnection.prepareCall(insertStoreProc);
    callableStatement.setInt(1, 1000);
    callableStatement.setString(2, "name");
    callableStatement.setString(3, "system");
    callableStatement.setDate(4, new java.sql.Date(today.getTime()));

    callableStatement.executeUpdate();
    System.out.println("Record is inserted into PERSON table!");
    callableStatement.close();
    dbConnection.close();
  }
}

JDBC Tutorial - JDBC Stored Procedure OUT Parameter

The following sections show how to call a stored procedure with both IN and OUT parameters.
A stored procedure for Oracle database written in PL/SQL language is listed as follows.

There are four parameters in the procedure and the last three of them are OUT parameters which means data will be passed out from those parameters.

CREATE OR REPLACE PROCEDURE getPERSONByUserId(
     p_userid IN PERSON.USER_ID%TYPE,
     o_username OUT PERSON.USERNAME%TYPE,
     o_createdby OUT  PERSON.CREATED_BY%TYPE,
     o_date OUT PERSON.CREATED_DATE%TYPE)
IS
BEGIN

  SELECT USERNAME , CREATED_BY, CREATED_DATE
  INTO o_username, o_createdby,  o_date 
  FROM  PERSON WHERE USER_ID = p_userid;

END;
/

Example

The Java code to call a stored procedure is listed as follows and the OUT parameters is used in getXXX() method from the CallableStatement.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;

public class Main {
  private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
  private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:YourDatabase";
  private static final String DB_USER = "user";
  private static final String DB_PASSWORD = "password";

  public static void main(String[] argv) throws Exception {
    Class.forName(DB_DRIVER);
    Connection dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
        DB_PASSWORD);

    CallableStatement callableStatement = null;
    String getPERSONByUserIdSql = "{call getPERSONByUserId(?,?,?,?)}";
    callableStatement = dbConnection.prepareCall(getPERSONByUserIdSql);

    callableStatement.setInt(1, 10);
    callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
    callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
    callableStatement.registerOutParameter(4, java.sql.Types.DATE);

    callableStatement.executeUpdate();

    String userName = callableStatement.getString(2);
    String createdBy = callableStatement.getString(3);
    Date createdDate = callableStatement.getDate(4);

    System.out.println("UserName : " + userName);
    System.out.println("CreatedBy : " + createdBy);
    System.out.println("CreatedDate : " + createdDate);
    callableStatement.close();
    dbConnection.close();
  }
}

Sunday, October 25, 2015

JDBC

Note

Example

Example

Example

Register JDBC Driver

Example

Database URL

Example 2

Embed user name and password in a database URL

Using a database URL and a Properties object

Closing JDBC connections

Methods from Statement

Close Statement Obeject

Example

Parameters in PreparedStatement

Close PreparedStatement Obeject

Example

Close CallableStatement Obeject

Methods in ResultSet

ResultSet Types

Concurrency of ResultSet

Example

ResultSet Navigation

ResultSet Column Data

Update ResultSet

Example

Handling NULL Values

SQL Timestamp

Using Savepoints

Example

Example

Batching with PrepareStatement Object

Example

Example

Example

Example

Example

Example

PreparedStatement to create a table

Example

Example

Example PreparedStatement

Example

Example PreparedStatement

Example

Example PreparedStatement

Example

Example

Example

Example PreparedStatement

Example

Example

No comments:

Post a Comment