JDBC
Statement Example
This
example explain do operations by using Statement Interface.
Using
Statement Interface
create
table
insert
records
update
records
delete
records
execute
batch records
select
records
import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.ResultSet;
import
java.sql.SQLException;
import
java.sql.Statement;
import
java.util.ArrayList;
import
java.util.Iterator;
/**
* @author
sanjeeva
* This use
Statement Interface
* create table
* insert record
* update record
* delete record
* execute batch
* select
records
*/
public
class
StatementExample {
Connection
connection
= null;
/**
* @param
args
*/
public
static
void
main(String[] args) {
StatementExample
example = new
StatementExample();
try
{
example.execute()
;
}
catch
(Exception e) {
e.printStackTrace();
}
}
private
void
execute() throws
SQLException{
boolean
execute = false;
try{
connection
= createConnection();
if(null
!= connection)
//This
is used to insert create a table ...
execute
= createTable(false,
connection);
if(execute)
System.out.println("Table
is created");
//This
is used to insert a record to the table ...
execute
= insertRecords(false,
connection);
if(execute)
System.out.println("One
record insert");
//This
is used to update the record/records in the table ...
execute
= updateRecords(false,
connection);
if(execute)
System.out.println("Record
update");
//This
is used to delete record/records from the table ...
execute
= deleteRecords(false,
connection);
if(execute)
System.out.println("Record
delete");
//This
is used to execute batch update in the table ...
execute
= executeBatchRecords(false,
connection);
if(execute)
System.out.println("Execute
batch");
//This
is used to select record/records from the table ...
selectRecords(connection);
}catch
(Exception e) {
e.printStackTrace();
}finally
{
if(null
!= connection)
connection.close();
}
}
private
Connection createConnection(){
String
username = "root";
String
password = "res13pg";
try
{
Class.forName("com.mysql.jdbc.Driver");
connection
=
DriverManager.getConnection("jdbc:mysql://localhost:3306 /myworkspace",username,password);
}
catch
(ClassNotFoundException e) {
e.printStackTrace();
}
catch
(SQLException e) {
e.printStackTrace();
}
return
connection;
}
private
boolean
createTable(boolean
execute, Connection connection) throws
SQLException{
String
createTableSQL = "CREATE TABLE
DBUSERTABLE( "
+
"USER_ID INT(10) NOT NULL, "
+
"USERNAME VARCHAR(20) NOT NULL, "
+
"CREATED_BY VARCHAR(20) NOT NULL, "
+
"CREATED_DATE DATE NOT NULL, "
+ "PRIMARY KEY (USER_ID) "
+
")";
Statement
statement = null;
try{
statement =
connection.createStatement();
statement.execute(createTableSQL);
execute
= true;
}catch(Exception
e){
System.out.println("Error
in execute().... " + e.toString());
}finally{
statement.close();
}
return
execute;
}
private
boolean
insertRecords(boolean
execute, Connection connection) throws
SQLException{
java.sql.Timestamp
date = new
java.sql.Timestamp(new
java.util.Date().getTime());
String
insertTableSQL = "INSERT INTO
DBUSERTABLE"
+ "(USER_ID, USERNAME, CREATED_BY,
CREATED_DATE) " + "VALUES"
+ "(1,'sanjeeva','system', "
+ "'"
+ date + "')";
Statement
statement = null;
try{
statement =
connection.createStatement();
statement.executeUpdate(insertTableSQL);
execute
= true;
}catch(Exception
e){
e.printStackTrace();
}finally{
statement.close();
}
return
execute;
}
private
boolean
updateRecords(boolean
execute, Connection connection) throws
SQLException{
String
updateTableSQL = "UPDATE
DBUSERTABLE"
+
" SET USERNAME = 'sandamali' "
+
" WHERE USER_ID = 1";
Statement
statement = null;
try{
statement =
connection.createStatement();
statement.executeUpdate(updateTableSQL);
execute
= true;
}catch(Exception
e){
e.printStackTrace();
}finally{
statement.close();
}
return
execute;
}
private
boolean
deleteRecords(boolean
execute, Connection connection) throws
SQLException{
String
deleteTableSQL = "DELETE FROM
DBUSERTABLE WHERE USER_ID = 1 ";
Statement
statement = null;
try{
statement =
connection.createStatement();
statement.executeUpdate(deleteTableSQL);
execute
= true;
}catch(Exception
e){
e.printStackTrace();
}finally{
statement.close();
}
return
execute;
}
private
boolean
executeBatchRecords(boolean
execute, Connection connection) throws
SQLException{
java.sql.Timestamp
date = new
java.sql.Timestamp(new
java.util.Date().getTime());
ArrayList<String>
insertTableList = new
ArrayList<String>();
String
insertTableSQL1 = "INSERT INTO
DBUSERTABLE"
+
"(USER_ID, USERNAME, CREATED_BY,
CREATED_DATE) " + "VALUES"
+
"(101,'Sanjeeva','system','"
+ date + "')";
insertTableList.add(insertTableSQL1);
String
insertTableSQL2 = "INSERT INTO
DBUSERTABLE"
+
"(USER_ID, USERNAME, CREATED_BY,
CREATED_DATE) " + "VALUES"
+
"(102,'Sandamali','system', '"
+ date + "')";
insertTableList.add(insertTableSQL2);
String
insertTableSQL3 = "INSERT INTO
DBUSERTABLE"
+
"(USER_ID, USERNAME, CREATED_BY,
CREATED_DATE) " + "VALUES"
+
"(103,'Thinuli','system', '"
+ date + "')";
insertTableList.add(insertTableSQL3);
Statement
statement = null;
try{
statement =
connection.createStatement();
connection.setAutoCommit(false);
Iterator<String>
iterator = insertTableList.iterator();
while
(iterator.hasNext()) {
statement.addBatch(iterator.next().trim());
}
statement.executeBatch();
connection.commit();
execute
= true;
}catch(Exception
e){
e.printStackTrace();
}finally{
statement.close();
}
return
execute;
}
private
void
selectRecords(Connection connection) throws
SQLException{
String
selectTableSQL = "SELECT USER_ID,
USERNAME from DBUSERTABLE";
Statement
statement = null;
try{
statement =
connection.createStatement();
ResultSet rs =
statement.executeQuery(selectTableSQL);
while
(rs.next()) {
String
userid = rs.getString("USER_ID");
String
username = rs.getString("USERNAME");
System.out.println("userid
| username --> " + userid + "
| " + username);
}
}catch(Exception
e){
System.out.println("Error
in executeUpdate().... " +
e.toString());
}finally{
statement.close();
}
}
}
Output
as follows :
Table
is created
One
record insert
Record
update
Record
delete
Execute
batch
userid
| username --> 101 | Sanjeeva
userid
| username --> 102 | Sandamali
userid
| username --> 103 | Thinuli
