What are the types of statements in JDBC?

The different types of Statements used in JDBC connection are as follows:

JDBC API has 3 Interfaces,

1. Statement
2. PreparedStatement
3. CallableStatement  

Statement

• This interface is used for executing a static SQL statement and returning the results it produces.
• The object of Statement class can be created using Connection.createStatement() method.

PreparedStatement

• A SQL statement is pre-compiled and stored in a PreparedStatement object.
• This object can then be used to efficiently execute this statement multiple times.
• The object of PreparedStatement class can be created using Connection.prepareStatement() method.
• This extends Statement interface.

Exampe Code:

import java.sql.*;

public class TwicePreparedStatement{

  public static void main(String[] args) {

  System.out.println("Twice use prepared statement example!\n");

  Connection con = null;

  PreparedStatement prest;

  try{

  Class.forName("com.mysql.jdbc.Driver");

  con = DriverManager.getConnection("jdbc:mysql:

//localhost:3306/jdbctutorial","root","root");

  try{

  String sql = "SELECT * FROM movies WHERE year_made = ?";

  prest = con.prepareStatement(sql);

  prest.setInt(1,2002);

  ResultSet rs1 = prest.executeQuery();

  System.out.println("List of movies that made in year 2002");

  while (rs1.next()){

  String mov_name = rs1.getString(1);

  int mad_year = rs1.getInt(2);

  System.out.println(mov_name + "\t- " + mad_year);

                                                }

  prest.setInt(1,2003);

  ResultSet rs2 = prest.executeQuery();

  System.out.println("List of movies that made in year 2003");

  while (rs2.next()){

  String mov_name = rs2.getString(1);

  int mad_year = rs2.getInt(2);

  System.out.println(mov_name + "\t- " + mad_year);

  }

  }

  catch (SQLException s){

  System.out.println("SQL statement is not executed!");

  }

  }

  catch (Exception e){

  e.printStackTrace();

  }

            }

}

CallableStatement

• This interface is used to execute SQL stored procedures.
• This extends PreparedStatement interface.
• The object of CallableStatement class can be created using Connection.prepareCall() method.

JDBC: Batch insert in database Java Example Program

Program:


import java.sql.*;
public class JdbcBatchInsert {
public static void main(String args[]) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
String url = "jdbc:mysql://localhost:3306/";
String db = "komal";
String driver = "com.mysql.jdbc.Driver";
String user = "root";
String pass = "root";
try {
Class.forName(driver);
con = DriverManager.getConnection(url + db, user, pass);
con.setAutoCommit(false);// Disables auto-commit.
st = con.createStatement();
st.addBatch("INSERT INTO person VALUES('4','Komal')");
st.addBatch("INSERT INTO person VALUES('5','Ajay')");
st.addBatch("INSERT INTO person VALUES('6','Santosh')");
st.executeBatch();
String sql = "select * from person";
rs = st.executeQuery(sql);
System.out.println("No  \tName");
while (rs.next()) {
System.out.print(rs.getString(1) + " \t");
System.out.println(rs.getString(2));
}
rs.close();
st.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

JDBC: Getting Column Names from Database Java Example Program

Program:


import java.sql.*;

public class ColumnName {
public static void main(String[] args) {
System.out.println("Getting Column Names Example!");
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";
String db = "abhi";
String driver = "com.mysql.jdbc.Driver";
String user = "root";
String pass = "root";
try{
Class.forName(driver);
con = DriverManager.getConnection(url+db, user, pass);
try{
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM abc");
ResultSetMetaData md = rs.getMetaData();
int col = md.getColumnCount();
System.out.println("Number of Column : "+ col);
System.out.println("Columns Name: ");
for (int i = 1; i <= col; i++){
String col_name = md.getColumnName(i);
System.out.println(col_name);
}
}
catch (SQLException s){
System.out.println("SQL statement is not executed!");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}

JDBC: Change column name in a database table Java Example Program

Program:

import java.io.*;
import java.sql.*;

public class ChangeColumnName {
public static void main(String[] args) {
System.out.println("Change column name in a database table example!");
Connection con = null;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection
("jdbc:mysql://localhost:3306/abhi","root","root");
try{
Statement st = con.createStatement();
BufferedReader bf = new BufferedReader(new InputStreamReader(System.in));
System.out.println("Enter table name:");
String table = bf.readLine();
System.out.println("Enter old column name:");
String old_col = bf.readLine();
System.out.println("Enter new column:");
String new_col = bf.readLine();
System.out.println("Enter data type:");
String type = bf.readLine();
int n = st.executeUpdate
("ALTER TABLE "+table+" CHANGE "+old_col+" "+new_col+" "+type);
System.out.println("Query OK, "+n+" rows affected");
}
catch (SQLException s){
System.out.println("Wrong entry!");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}

JDBC: Batch example in Java

Program:

import java.sql.*;
public class BatchEx {

public static void main (String[] args)  throws Exception     {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = null;
String userName = "root";
String password = "root";
String url ="jdbc:mysql://localhost/abhi";
java.util.Calendar  c=java.util.Calendar.getInstance();
conn = DriverManager.getConnection(url,userName,password);
PreparedStatement stmt = conn.prepareStatement("INSERT INTO xyz VALUES(?,?,?,?)");
stmt.setInt(1,15);
stmt.setInt(2,1);
c.clear();  
c.set(2011,9,25);
stmt.setDate(3, new java.sql.Date((c.getTime()).getTime()));
c.set(2011,9,27);
stmt.setDate(4,new java.sql.Date((c.getTime()).getTime()));

stmt.addBatch();
stmt.setInt(1,16);
stmt.setInt(2,2);
c.clear();
c.set(2011,10,12);
stmt.setDate(3,new java.sql.Date((c.getTime()).getTime()));
c.set(2011,10,27);
stmt.setDate(4,new java.sql.Date((c.getTime()).getTime()));
stmt.addBatch();
stmt.executeBatch();
conn.close();
}
}

JDBC: Listing all table name in Database Java Program

Program:

import java.sql.*;

public class AllTableName {
public static void main(String[] args) {
System.out.println("Listing all table name in Database!");
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";
String db = "abhi";
String driver = "com.mysql.jdbc.Driver";
String user = "root";
String pass = "root";
try{
Class.forName(driver);
con = DriverManager.getConnection(url+db, user, pass);
try{
DatabaseMetaData dbm = con.getMetaData();
String[] types = {"TABLE"};
ResultSet rs = dbm.getTables(null,null,"%",types);
System.out.println("Table name:");
while (rs.next()){
String table = rs.getString("TABLE_NAME");
System.out.println(table);
con.close();
}
}
catch (SQLException s){
System.out.println("No any table in the database");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}

JDBC: Adding new column in table example Java Program

Program:

import java.io.*;
import java.sql.*;

public class AddColumn {
public static void main(String[] args) {
System.out.println("Adding new column in table example!");
Connection con = null;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection
("jdbc:mysql://localhost:3306/ruth","root","root");
try{
Statement st = con.createStatement();
BufferedReader bf = new BufferedReader(new InputStreamReader(System.in));
System.out.println("Enter table name:");
String table = bf.readLine();
System.out.println("Enter column name:");
String col = bf.readLine();
System.out.println("Enter data type:");
String type = bf.readLine();
int n = st.executeUpdate("ALTER TABLE "+table+" ADD "+col+" "+type);
System.out.println("Query OK, "+n+" rows affected");
}
catch (SQLException s){
System.out.println("Tabel or column or data type is not found!");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}