JDBC PreparedStatement – Insert a record

package net.dharmaraj;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
public class JDBCPreparedStatementInsertExample {
//=======================================================
public static void main(String[] args) {
try {
insertRecordIntoTable();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}//end of main method
//=======================================================
private static void insertRecordIntoTable() throws SQLException {

Connection dbConnection = null;
PreparedStatement preparedStatement = null;

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

try {
dbConnection = JDBCStatementCreateExample.getDBConnection();
preparedStatement = dbConnection.prepareStatement(insertTableSQL);

preparedStatement.setInt(1, 11);
preparedStatement.setString(2, "mkyong");
preparedStatement.setString(3, "system");
preparedStatement.setTimestamp(4, getCurrentTimeStamp());
preparedStatement.executeUpdate();
System.out.println("Record is inserted into DBUSER table!");
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
if (preparedStatement != null) {
preparedStatement.close();
}

if (dbConnection != null) {
dbConnection.close();
}
}
}
//=======================================================
private static java.sql.Timestamp getCurrentTimeStamp() {
java.util.Date today = new java.util.Date();
return new java.sql.Timestamp(today.getTime());
}
//=======================================================
}//end of Class

JDBC PreparedStatement – Create a table

package net.dharmaraj;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class JDBCPreparedStatementCreateExample {
//============================================================
public static void main(String[] args) {
try {
createTable();
} catch (SQLException e) {
System.out.println(e.getMessage());
}//end of try catch block
}//end of main method
//============================================================
private static void createTable() throws SQLException {

Connection dbConnection = null;
PreparedStatement preparedStatement = null;

String createTableSQL = "CREATE TABLE DBUSER1("
+ "USER_ID INT(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 = JDBCStatementCreateExample.getDBConnection();
preparedStatement = dbConnection.prepareStatement(createTableSQL);
System.out.println(createTableSQL);
preparedStatement.executeUpdate();
System.out.println("Table \"dbuser\" is created!");
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
if (preparedStatement != null) {
preparedStatement.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}//end of try catch block
}//end of Method
//============================================================
}//END OF Class

JDBC Statement example – Batch Update

package net.dharmaraj;
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 DateFormat dateFormat = new SimpleDateFormat(
"yyyy/MM/dd HH:mm:ss");

//========================================================================
public static void main(String[] args) {
try {
batchInsertRecordsIntoTable();
} catch (SQLException e) {
System.out.println(e.getMessage());
}//End of try catch block
}//end of main method
//========================================================================
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 = JDBCStatementCreateExample.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 String getCurrentTimeStamp() {

java.util.Date today = new java.util.Date();
return dateFormat.format(today.getTime());
}
//========================================================================
}//end of Class

JDBC Statement – Select list of the records

package net.dharmaraj;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import net.dharmaraj.JDBCStatementCreateExample;
public class JDBCStatementSelectExample {
public static void main(String[] args) {
try {
selectRecordsFromDbUserTable();
} catch (Exception e) {
System.out.println(e.getMessage());
}

}//end of Main Method
private static void selectRecordsFromDbUserTable() throws SQLException {

Connection dbConnection = null;
Statement statement = null;

String selectTableSQL = "SELECT USER_ID, USERNAME from DBUSER";

try {
dbConnection = JDBCStatementCreateExample.getDBConnection();
statement = dbConnection.createStatement();
System.out.println(selectTableSQL);
ResultSet rs = statement.executeQuery(selectTableSQL);
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 (statement != null) {
statement.close();
}

if (dbConnection != null) {
dbConnection.close();
}
}//end of try catch block

}
}//end of Class

JDBC Statement – Delete a record

package net.dharmaraj;
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 = "com.mysql.jdbc.Driver";
private static final String DB_CONNECTION = "jdbc:mysql://localhost/pkm";
private static final String DB_USER = "root";
private static final String DB_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);
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;
}
//==================================================================
}

JDBC Statement – Update a record

package net.dharmaraj;
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 = "com.mysql.jdbc.Driver";
private static final String DB_CONNECTION = "jdbc:mysql://localhost/pkm";
private static final String DB_USER = "root";
private static final String DB_PASSWORD = "";
//==================================================================
public static void main(String[] args) {
try {
updateRecordIntoDbUserTable();
} catch (SQLException e) {
System.out.println(e.getMessage());
}//end of try catch block
}//end of main method
//==================================================================
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);
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();
}
}//end of try catch finally block
}//end of method
//==================================================================
private static Connection getDBConnection() {
Connection dbConnection = null;
try {
Class.forName(DB_DRIVER);
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
}//end of try catch block

try {
dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,DB_PASSWORD);
return dbConnection;
} catch (SQLException e) {
System.out.println(e.getMessage());
}//end of try catch block
return dbConnection;
}//end of method
//==================================================================
}//end of Class

JDBC Statement – Insert a record

package net.dharmaraj;
import java.sql.Connection;
import java.sql.DriverManager;
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 = "com.mysql.jdbc.Driver";
private static final String DB_CONNECTION = "jdbc:mysql://localhost/pkm";
private static final String DB_USER = "root";
private static final String DB_PASSWORD = "";
private static final DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");

//==================================================================
public static void main(String[] args) {
try {
insertRecordIntoDbUserTable();
} catch (Exception e) {
System.out.println(e.getMessage());
}//end of try catch Block
}//end of Main Method
//==================================================================
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);
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();
}
}//end of try catch finally Block
}//end of insertRecordIntoDbUserTable()
//==================================================================
private static Connection getDBConnection() {
Connection dbConnection = null;
try {
Class.forName(DB_DRIVER);
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
}//end of try catch Block

try {
dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,DB_PASSWORD);
return dbConnection;
} catch (SQLException e) {
System.out.println(e.getMessage());
}//end of try catch block
return dbConnection;
}//end of Method
//==================================================================
private static String getCurrentTimeStamp() {
java.util.Date today = new java.util.Date();
return dateFormat.format(today.getTime());
}//end of Method
//==================================================================
}//end of Class