JDBC PreparedStatement Example – Select List Of The Records

Here’s an example to show you how to select records from table via JDBC PreparedStatement, and display the records via a ResultSet object. To issue a select query, calls the PreparedStatement.executeQuery() method like this :

String selectSQL = "SELECT USER_ID, USERNAME FROM DBUSER WHERE USER_ID = ?";
PreparedStatement preparedStatement = dbConnection.prepareStatement(selectSQL);
preparedStatement.setInt(1, 1001);
ResultSet rs = preparedStatement.executeQuery(selectSQL );
while (rs.next()) {
	String userid = rs.getString("USER_ID");
	String username = rs.getString("USERNAME");	
}

Full example…

package com.mkyong.jdbc;
 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
public class JDBCPreparedStatementSelectExample {
 
	private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
	private static final String DB_USER = "user";
	private static final String DB_PASSWORD = "password";
 
	public static void main(String[] argv) {
 
		try {
 
			selectRecordsFromTable();
 
		} catch (SQLException e) {
 
			System.out.println(e.getMessage());
 
		}
 
	}
 
	private static void selectRecordsFromTable() throws SQLException {
 
		Connection dbConnection = null;
		PreparedStatement preparedStatement = null;
 
		String selectSQL = "SELECT USER_ID, USERNAME FROM DBUSER WHERE USER_ID = ?";
 
		try {
			dbConnection = getDBConnection();
			preparedStatement = dbConnection.prepareStatement(selectSQL);
			preparedStatement.setInt(1, 1001);
 
			// execute select SQL stetement
			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);
 
			}
 
		} catch (SQLException e) {
 
			System.out.println(e.getMessage());
 
		} finally {
 
			if (preparedStatement != null) {
				preparedStatement.close();
			}
 
			if (dbConnection != null) {
				dbConnection.close();
			}
 
		}
 
	}
 
	private static Connection getDBConnection() {
 
		Connection dbConnection = null;
 
		try {
 
			Class.forName(DB_DRIVER);
 
		} catch (ClassNotFoundException e) {
 
			System.out.println(e.getMessage());
 
		}
 
		try {
 
			dbConnection = DriverManager.getConnection(
                             DB_CONNECTION, DB_USER,DB_PASSWORD);
			return dbConnection;
 
		} catch (SQLException e) {
 
			System.out.println(e.getMessage());
 
		}
 
		return dbConnection;
 
	}
 
}

Result

List of the records with “user_id = 1001″ are retrieved from table “DBUSER” and displayed.

Read More

JDBC Statement Example – Batch Update

Here’s an example to show you how to insert few records in batch process, via JDBC Statement.

dbConnection.setAutoCommit(false);
 
statement = dbConnection.createStatement();
statement.addBatch(insertTableSQL1);
statement.addBatch(insertTableSQL2);
statement.addBatch(insertTableSQL3);
 
statement.executeBatch();
 
dbConnection.commit();

Note
Batch Update is not limit to Insert statement, it’s apply for Update and Delete statement as well.

See full JDBC batch update example …

package com.mkyong.jdbc;
 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
 
public class JDBCBatchUpdateExample {
 
	private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
	private static final String DB_USER = "user";
	private static final String DB_PASSWORD = "password";
	private static final DateFormat dateFormat = new SimpleDateFormat(
			"yyyy/MM/dd HH:mm:ss");
 
	public static void main(String[] argv) {
 
		try {
 
			batchInsertRecordsIntoTable();
 
		} catch (SQLException e) {
 
			System.out.println(e.getMessage());
 
		}
 
	}
 
	private static void batchInsertRecordsIntoTable() throws SQLException {
 
		Connection dbConnection = null;
		Statement statement = null;
 
		String insertTableSQL1 = "INSERT INTO DBUSER"
				+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) " + "VALUES"
				+ "(101,'mkyong','system', " + "to_date('"
				+ getCurrentTimeStamp() + "', 'yyyy/mm/dd hh24:mi:ss'))";
 
		String insertTableSQL2 = "INSERT INTO DBUSER"
				+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) " + "VALUES"
				+ "(102,'mkyong','system', " + "to_date('"
				+ getCurrentTimeStamp() + "', 'yyyy/mm/dd hh24:mi:ss'))";
 
		String insertTableSQL3 = "INSERT INTO DBUSER"
				+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) " + "VALUES"
				+ "(103,'mkyong','system', " + "to_date('"
				+ getCurrentTimeStamp() + "', 'yyyy/mm/dd hh24:mi:ss'))";
 
		try {
			dbConnection = getDBConnection();
			statement = dbConnection.createStatement();
 
			dbConnection.setAutoCommit(false);
 
			statement.addBatch(insertTableSQL1);
			statement.addBatch(insertTableSQL2);
			statement.addBatch(insertTableSQL3);
 
			statement.executeBatch();
 
			dbConnection.commit();
 
			System.out.println("Records are inserted into DBUSER table!");
 
		} catch (SQLException e) {
 
			System.out.println(e.getMessage());
 
		} finally {
 
			if (statement != null) {
				statement.close();
			}
 
			if (dbConnection != null) {
				dbConnection.close();
			}
 
		}
 
	}
 
	private static Connection getDBConnection() {
 
		Connection dbConnection = null;
 
		try {
 
			Class.forName(DB_DRIVER);
 
		} catch (ClassNotFoundException e) {
 
			System.out.println(e.getMessage());
 
		}
 
		try {
 
			dbConnection = DriverManager.getConnection(
				DB_CONNECTION, DB_USER,DB_PASSWORD);
			return dbConnection;
 
		} catch (SQLException e) {
 
			System.out.println(e.getMessage());
 
		}
 
		return dbConnection;
 
	}
 
	private static String getCurrentTimeStamp() {
 
		java.util.Date today = new java.util.Date();
		return dateFormat.format(today.getTime());
 
	}
 
}

Result

3 records are inserted into database via batch update process.

Why need to use Batch Update?

The above batch update is same with normal executeUpdate() method like this :

statement.executeUpdate(insertTableSQL1);
statement.executeUpdate(insertTableSQL2);
statement.executeUpdate(insertTableSQL3);

But batch update has performance benefit if you want to insert many records, because executeBatch() reduces the number of JDBC calls to database.

Read More

JDBC Statement Example – Delete A Record

Here is an example to show you how to delete a record from a table via JDBC statement. To issue a delete statement, calls the Statement.executeUpdate() method like this :

Statement statement = dbConnection.createStatement();
// execute the delete SQL stetement
statement.executeUpdate(deleteTableSQL);

Full example…

package com.mkyong.jdbc;
 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
 
public class JDBCStatementDeleteExample {
 
 private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
 private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
 private static final String DB_USER = "user";
 private static final String DB_PASSWORD = "password";
 
 public static void main(String[] argv) {
 
  try {
 
   deleteRecordFromDbUserTable();
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
 }
 
 private static void deleteRecordFromDbUserTable() throws SQLException {
 
  Connection dbConnection = null;
  Statement statement = null;
 
  String deleteTableSQL = "DELETE DBUSER WHERE USER_ID = 1";
 
  try {
   dbConnection = getDBConnection();
   statement = dbConnection.createStatement();
 
   System.out.println(deleteTableSQL);
 
   // execute delete SQL stetement
   statement.execute(deleteTableSQL);
 
   System.out.println("Record is deleted from DBUSER table!");
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  } finally {
 
   if (statement != null) {
    statement.close();
   }
 
   if (dbConnection != null) {
    dbConnection.close();
   }
 
  }
 
 }
 
 private static Connection getDBConnection() {
 
  Connection dbConnection = null;
 
  try {
 
   Class.forName(DB_DRIVER);
 
  } catch (ClassNotFoundException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  try {
 
   dbConnection = DriverManager.getConnection(
                             DB_CONNECTION, DB_USER,DB_PASSWORD);
   return dbConnection;
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  return dbConnection;
 
 }
 
}

Result

A record with “user_id=1″ is deleted from table.

DELETE DBUSER WHERE USER_ID = 1
Record IS deleted FROM DBUSER TABLE!

Read More

JDBC Statement Example – Update A Record

Here’s an example to show you how to update a record in a table via JDBC statement. To issue a update statement, calls the Statement.executeUpdate() method like this :

Statement statement = dbConnection.createStatement();
// execute the update SQL stetement
statement.executeUpdate(updateTableSQL);

Full example…

package com.mkyong.jdbc;
 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
 
public class JDBCStatementUpdateExample {
 
 private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
 private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
 private static final String DB_USER = "user";
 private static final String DB_PASSWORD = "password";
 
 public static void main(String[] argv) {
 
  try {
 
   updateRecordIntoDbUserTable();
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
 }
 
 private static void updateRecordIntoDbUserTable() throws SQLException {
 
  Connection dbConnection = null;
  Statement statement = null;
 
  String updateTableSQL = "UPDATE DBUSER"
    + " SET USERNAME = 'mkyong_new' "
    + " WHERE USER_ID = 1";
 
  try {
   dbConnection = getDBConnection();
   statement = dbConnection.createStatement();
 
   System.out.println(updateTableSQL);
 
   // execute update SQL stetement
   statement.execute(updateTableSQL);
 
   System.out.println("Record is updated to DBUSER table!");
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  } finally {
 
   if (statement != null) {
    statement.close();
   }
 
   if (dbConnection != null) {
    dbConnection.close();
   }
 
  }
 
 }
 
 private static Connection getDBConnection() {
 
  Connection dbConnection = null;
 
  try {
 
   Class.forName(DB_DRIVER);
 
  } catch (ClassNotFoundException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  try {
 
   dbConnection = DriverManager.getConnection(
                              DB_CONNECTION, DB_USER,DB_PASSWORD);
   return dbConnection;
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  return dbConnection;
 
 }
 
}

Result

The username of “user_id = 1″ is updated to a new value ‘mkyong_new’.

UPDATE DBUSER SET USERNAME = 'mkyong_new'  WHERE USER_ID = 1
Record IS updated INTO DBUSER TABLE!

Read More

JDBC Statement Example – Insert A Record

Here’s an example to show you how to insert a record into table via JDBC statement. To issue a insert statement, calls the Statement.executeUpdate() method like this :

Statement statement = dbConnection.createStatement();
// execute the insert SQL stetement
statement.executeUpdate(insertTableSQL);

Full example…

package com.mkyong.jdbc;
 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
 
public class JDBCStatementInsertExample {
 
 private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
 private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
 private static final String DB_USER = "user";
 private static final String DB_PASSWORD = "password";
 private static final DateFormat dateFormat = new SimpleDateFormat(
   "yyyy/MM/dd HH:mm:ss");
 
 public static void main(String[] argv) {
 
  try {
 
   insertRecordIntoDbUserTable();
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
 }
 
 private static void insertRecordIntoDbUserTable() throws SQLException {
 
  Connection dbConnection = null;
  Statement statement = null;
 
  String insertTableSQL = "INSERT INTO DBUSER"
    + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) " + "VALUES"
    + "(1,'mkyong','system', " + "to_date('"
    + getCurrentTimeStamp() + "', 'yyyy/mm/dd hh24:mi:ss'))";
 
  try {
   dbConnection = getDBConnection();
   statement = dbConnection.createStatement();
 
   System.out.println(insertTableSQL);
 
   // execute insert SQL stetement
   statement.executeUpdate(insertTableSQL);
 
   System.out.println("Record is inserted into DBUSER table!");
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  } finally {
 
   if (statement != null) {
    statement.close();
   }
 
   if (dbConnection != null) {
    dbConnection.close();
   }
 
  }
 
 }
 
 private static Connection getDBConnection() {
 
  Connection dbConnection = null;
 
  try {
 
   Class.forName(DB_DRIVER);
 
  } catch (ClassNotFoundException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  try {
 
   dbConnection = DriverManager.getConnection(
                               DB_CONNECTION, DB_USER,DB_PASSWORD);
   return dbConnection;
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  return dbConnection;
 
 }
 
 private static String getCurrentTimeStamp() {
 
  java.util.Date today = new java.util.Date();
  return dateFormat.format(today.getTime());
 
 }
 
}

Result

A record is inserted into a table named “DBUSER”.

INSERT INTO DBUSER(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) 
VALUES(1,'mkyong','system', to_date('2011/04/04 13:59:03', 'yyyy/mm/dd hh24:mi:ss'))
Record IS inserted INTO DBUSER TABLE!

Read More

JDBC Statement Example – Create A Table

Here’s an example to show you how to create a table in database via JDBC statement. To issue a create statement, calls the Statement.execute() method like this :

Statement statement = dbConnection.createStatement();
// execute create SQL stetement
statement.execute(createTableSQL);

Full example…

package com.mkyong.jdbc;
 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
 
public class JDBCStatementCreateExample {
 
 private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
 private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
 private static final String DB_USER = "user";
 private static final String DB_PASSWORD = "password";
 
 public static void main(String[] argv) {
 
  try {
 
   createDbUserTable();
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
 }
 
 private static void createDbUserTable() throws SQLException {
 
  Connection dbConnection = null;
  Statement statement = null;
 
  String createTableSQL = "CREATE TABLE DBUSER("
    + "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) "
    + ")";
 
  try {
   dbConnection = getDBConnection();
   statement = dbConnection.createStatement();
 
   System.out.println(createTableSQL);
                        // execute the SQL stetement
   statement.execute(createTableSQL);
 
   System.out.println("Table \"dbuser\" is created!");
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  } finally {
 
   if (statement != null) {
    statement.close();
   }
 
   if (dbConnection != null) {
    dbConnection.close();
   }
 
  }
 
 }
 
 private static Connection getDBConnection() {
 
  Connection dbConnection = null;
 
  try {
 
   Class.forName(DB_DRIVER);
 
  } catch (ClassNotFoundException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  try {
 
   dbConnection = DriverManager.getConnection(
     DB_CONNECTION, DB_USER,DB_PASSWORD);
   return dbConnection;
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  return dbConnection;
 
 }
 
}

Result

Here’s the result.

CREATE TABLE DBUSER(
  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) 
)
TABLE "user" IS created!

Read More