6.1 JDBC STATEMENT INTRODUCTION
JDBC Statement is the API provided by Oracle to interact with the database. Once we get the database connection, we require a Statement Object to interact or I can say to fire the queries on the database.
It is important to understand that Statement API does not provide any way to use the place holders in Query like Prepared Statement. You need to use a static query though you can make the query dynamic using String concatenation.
6.2 JDBC Statement API
JDBC Statement is an interface and is available under java.sql package. To get the Statement object , we need to use the Connection object and call “createStatement()” method like below
Statement stmt = conn.createStatement();
It is always suggested to close the Statement Object to release all the resources occupied by the statement object once it is done with its processing. To close the Statement object, we can use “close()” method on statement object like below
Statement stmt = conn.createStatement();
stmt.close();
Below are the most commonly used methods of the Statement Object.
- void close() throws SQLException - This method is used to close the connection object so that all the resources occupied by the statement object will be release immediately.
- boolean execute (String SQL): execute method takes a String argument which is the SQL statement which needs to be executed. Usually this method is used to execute data definition language statements (DDL) like Create / Drop tables. If there is any resultset associated with the SQL: execution, this method will return true else false.
- int executeUpdate (String SQL): As it name suggest, this method is used to fire SQL Queries which updates the database statement so to execute INSERT, DELETE, UPDATE queries, we should use this method. This method takes the String argument which is the insert or delete or update sql query and returns an int which is the number of rows affected by this execution like number of rows deleted/ updated or inserted.
- ResultSet executeQuery (String SQL): executeQuery method takes a String argument which is the SQL statement which needs to be executed. Usually this method is used to execute Select SQL query and it returns the ResultSet Object which can be used to get the result of Select SQL statement.
- Connection getConnection() – getConnection() API can be used to get back the connection object which was used to create the statement Object.
- boolean isClosed()- isClosed() method can be used to verify if the Statement object is closed or not.
6.3 JDBC Statement Examples
We will use the MySQL database . Use the below sql statement to create a schema
Create schema JDBCTutorial;
6.3.1 – Write and Example to create a table with name “User” with 4 columns.
Solution –
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCStatementExample {
public static void main(String args[])
{
try
{
Connection conn= getConnection();
Statement stmt = conn.createStatement();
String sql = "CREATE TABLE 'User'
( 'id' INT NOT NULL AUTO_INCREMENT ,
'name' VARCHAR(45) NULL ,
'age' VARCHAR(45) NULL ,
'email' VARCHAR(45) NULL ,
PRIMARY KEY (`id`));";
boolean result = stmt.execute(sql);
System.out.println(result);
stmt.close();
conn.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
private static Connection getConnection()
{
Connection con = null;
String dbDriver = "com.mysql.jdbc.Driver";
String dbUsername = "root";
String dbPassword="password";
String dbHostname="localhost";
String dbPort="3306";
String schema="JdbcTutorial";
try {
String url = "jdbc:mysql://"+dbHostname+":"+dbPort+"/"+schema;
Class.forName(dbDriver);
con = DriverManager.getConnection(url, dbUsername, dbPassword);
}
catch (ClassNotFoundException ex1)
{
System.out.println("Failed to find driver class " + ex1.getMessage());
System.exit(1);
}
catch (SQLException ex2) {
System.out.println("Connection failed " + ex2.getMessage());
System.exit(2);
}
return con;
}
}
Output- On Running above Program ,execute method will create a table and will return false as Create table will not have any result set associated.
6.3.2 Write an example to insert record using executeUpdate() method in table created in section 6.3.1
Solution - Below example will take the name, age and email address from user and will insert those values. Remember in Introduction we mentioned that there is no API to for placeholder but we can use String Concatenation to make the String dynamic. Since we are taking the values from user, we cannot hardcode the values and thus we have used String Concatenation.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class JDBCInsertStatementExample {
public static void main(String args[])
{
try
{
Scanner scanner = new Scanner(System.in);
System.out.println("Enter name :");
String name = scanner.next();
System.out.println("Enter age :");
String age= scanner.next();
System.out.println("Enter email :");
String email = scanner.next();
Connection conn= getConnection();
Statement stmt = conn.createStatement();
String sql = "Insert into 'User' ('name', 'age', 'email') values ('" + name+ "'," + age+",'"+email+"')";
int recordsUpdated = stmt.executeUpdate(sql);
System.out.println("Number of Record Inserted = " + recordsUpdated);
stmt.close();
conn.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
private static Connection getConnection() {
Connection con = null;
String dbDriver = "com.mysql.jdbc.Driver";
String dbUsername = "root";
String dbPassword="password";
String dbHostname="localhost";
String dbPort="3306";
String schema="JdbcTutorial";
try {
String url = "jdbc:mysql://"+dbHostname+":"+dbPort+"/"+schema;
Class.forName(dbDriver);
con = DriverManager.getConnection(url, dbUsername, dbPassword);
}
catch (ClassNotFoundException ex1)
{
System.out.println("Failed to find driver class " + ex1.getMessage());
System.exit(1);
}
catch (SQLException ex2) {
System.out.println("Connection failed " + ex2.getMessage());
System.exit(2);
}
return con;
}
}
On Running above program, System will prompt to type name, age and email address. Also you can see that the executeUpdate method returns 1 because we have added one record. Refer below screenshot-
Refer below Database state
6.3.3 Write an example to get the records using executeQuery() method in table created in section 6.3.1 and data inserted in 6.3.2
Solution
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class JDBCSelectStatementExample {
public static void main(String args[])
{
try
{
Connection conn= getConnection();
Statement stmt = conn.createStatement();
String sql = "select * from 'User'";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next())
{
String name = rs.getString("name");
String age = rs.getString("age");
String email = rs.getString("email");
System.out.println("Name=" +name);
System.out.println("Age=" +age);
System.out.println("Email=" +email);
}
rs.close();
stmt.close();
conn.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
private static Connection getConnection() {
Connection con = null;
String dbDriver = "com.mysql.jdbc.Driver";
String dbUsername = "root";
String dbPassword="password";
String dbHostname="localhost";
String dbPort="3306";
String schema="JdbcTutorial";
try {
String url = "jdbc:mysql://"+dbHostname+":"+dbPort+"/"+schema;
Class.forName(dbDriver);
con = DriverManager.getConnection(url, dbUsername, dbPassword);
}
catch (ClassNotFoundException ex1)
{
System.out.println("Failed to find driver class " + ex1.getMessage());
System.exit(1);
}
catch (SQLException ex2) {
System.out.println("Connection failed " + ex2.getMessage());
System.exit(2);
}
return con;
}
}
On Running above Program, we can see below output
