DataBase operations - Using Statement

Statement interface is used to execute SQL statements with no parameters.
In this tutorial we are going to learn how to use Statement interface to

1. create table
2. Insert records into DB
3. Delete records from DB
4. Update records in DB
5. Select records from DB
6. Execute bulk updates using batch

------------------------------------------------------------------------------------------------------------

package tutorial;


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

public class Test_Statement {

Connection con=null;
Statement statement=null;
ResultSet resultSet=null;

//Constructor to load driver manager and create connection object
Test_Statement() throws ClassNotFoundException, SQLException{
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root", "password");

}

//Method to close connection after transactions are completed
void closeConnection() throws SQLException{
if(con!=null)
con.close();
}

//Program to create table, We use execute(String) which returns boolean value on success or failure of execution
void createUserTable() throws SQLException{
try {
statement=con.createStatement();
boolean result=statement.execute("create table Student (id integer,name varchar(20))");
if(result)
System.out.println("Table successfully created !!");
} catch (SQLException e) {
System.out.println(e.getMessage());
}
finally{
if(statement!=null)
statement.close();
}
}


//Program to insert records into database, executeUpdate(String) is used whose return type is integer
void insertUserTable(int id,String name) throws SQLException{
try {
statement=con.createStatement();
int result=statement.executeUpdate("insert  into Student values("+id+" ,'"+name+"')");
if(result>0){
System.out.println("Number of rows were affected during insertion:: "+result);
}

} catch (SQLException e) {
System.out.println(e.getMessage());
}
finally{
if(statement!=null)
statement.close();
}
}


//Program to Delete record from database, executeUpdate(String) is used whose return type is count of rows affected by query
void deleteUserTable(int id) throws SQLException{
try {
statement=con.createStatement();
int result=statement.executeUpdate("delete from Student where id = "+id);
if(result>0){
System.out.println("Number of rows were affected during deletion:: "+result);
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
finally{
if(statement!=null)
statement.close();
}
}


//Program to demonstrate adding update queries to a batch and executing batch as whole 
void batchTest() throws SQLException{
try {
statement=con.createStatement();
statement.addBatch("insert into student values (10,'Shrey')");
statement.addBatch("insert into student values (14,'Vini')");
statement.addBatch("insert into student values (16,'Sanjay')");
int [] count=statement.executeBatch();
for(int i=0;i<count.length;i++)
System.out.println("Number of rows affected due to Query "+(i+1)+" = "+count[i]);
} catch (SQLException e) {
System.out.println(e.getMessage());
}
finally{
if(statement!=null)
statement.close();
}
}


//Program to select records from Database
void selectTest() throws SQLException{
try {
statement=con.createStatement();
resultSet=statement.executeQuery("select * from student");
while(resultSet.next()){
System.out.print(resultSet.getString(1));
System.out.print("\t"+resultSet.getString(2));
System.out.println();
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
finally{
if(statement!=null)
statement.close();
}
}


public static void main(String[] args) {
try {

Test_Statement test=new Test_Statement();
test.createUserTable();
test.insertUserTable(2,"vini");
test.deleteUserTable(2);
test.batchTest();
test.selectTest();
test.closeConnection();

} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

}

}