java - JDBC - Techniques

最新推荐文章于 2022-07-18 16:40:49 发布

ytlcainiao

最新推荐文章于 2022-07-18 16:40:49 发布

阅读量500

点赞数

分类专栏： MySql

MySql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

一、JDBC Data Types

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

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());
/*  ww w . ja v a  2  s  .co  m*/
    // 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());
  }
}

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;
//from   ww  w. java  2s  . c om
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 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;
/*from w  w  w.jav  a 2s .  com*/
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 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.

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//from w ww .  j a va  2  s  .  com
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 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;
//ww w .  j ava2 s  .c  o  m
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 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 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;
/*ww  w.j a  v a 2  s  .co m*/
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 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;
/*www .ja v  a 2 s  . com*/
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 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;
/* ww w .  j av  a  2  s .  c o  m*/
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 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;
//from  w w  w. j a  v  a2 s . 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 = "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;
/*from  www  .  j a v  a 2 s . co m*/
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 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;
//  ww  w  .j  av a 2s  .  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 = "DROP TABLE Person ";

    stmt.executeUpdate(sql);

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

十一、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;
/*  w ww  .  j ava2  s  .  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 = "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;
/*from   w  w w  .  j a v  a 2 s.c om*/
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 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;
/*from w  w w  . j  ava  2 s.  c om*/
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 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;
/* www.  j a va2  s.c o  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 = "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;
/*from   w w  w.ja v  a 2 s .  co  m*/
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 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;
/*  ww  w  .jav a2  s .  c  o 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 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;
// w ww.j a  va2s  .  c  o m
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 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 fron 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;
//from   ww  w. j a v  a 2 s .  c o  m
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 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;
//w  ww.ja  va  2  s.  co  m
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();
  }
}