Executing SQL Queries

Steps for creating connection with the database -


Step 3- Executing SQL Queries

To actually use the database there should be a way to execute queries.
JDBC provides Statement, PreparedStatement and CallableStatement interfaces that contain methods and properties we can use to send or receive data to/ from database.

Interface Use
java.sql.Statement For static queries.
java.sql.PreparedStatement For dynamic queries.
java.sql.CallableStatement For stored procedure.

Statement Interface -

It provides the simplest method for executing queries and work with static queries only.
First of all you need to have an object of Statement. You can get a reference to Statement by calling the createStatement() method of Connection interface. It has the following syntax -
Connection createStatement() throws SQLException 
Example -
Statement st = con.createStatement();
Where con is a reference to Connection class. 

Once you have created Statement object you can execute SQL queries -

  • Executing SQL Select Statement - executeQuery(String SQL_Query) method is used to execute SQL Select queries.
  • Executing SQL Insert, Update and Delete Statements - executeUpdate(String SQL_Query) method is used to execute SQL Insert,Update and Delete queries.

1- ResultSet executeQuery(String SQL_Query) -

This method is used to execute SQL Select statement. It has the following syntax -
public abstract ResultSet executeQuery(String SQL_Query) throws SQLException
It executes the specified SQL_Query and returns the result in ResultSet. Result will be discussed in detail 
in next section.
For example -
To get all data of a table named emp - 
ResultSet rs = st.executeQuery("select * from emp");
All data of emp table will be stored in ResultSet specified by rs.

ResultSet -

» java.sql.ResultSet is an interface that provides a way to access the data produced by executing SQL Select statement.
» It maintains a cursor to current row of data , Initially the cursor will be just before the first row.
» It accesses the rows of table in sequential order, however within rows columns can be accessed in any order.

ResultSet provides the following useful methods to access and manipulate table data -
1- boolean next()- It places the ResultSet cursor to the next row. As we know ResultSet cursor is initially positioned just before the first row, so first call to next() will move the cursor at first row, second call at second row and so on. It has the following syntax -
boolean next() throws SQLException
It returns true if the new current row is valid, false otherwise.
2- boolean previous()- It places the ResultSet cursor to the previous row. It has the following syntax -

boolean previous() throws SQLException
It returns true if cursor is now positioned at a valid row, false otherwise. If it returns false then cursor will be 
placed just before the first row.
3- boolean first()- It moves the ResultSet cursor to the first row. It has the following syntax -
boolean first() throws SQLException
It returns true if the new current row is valid, false if there is no row in ResultSet.
4- boolean last()- It moves the ResultSet cursor to the last row. It has the following syntax -
boolean last() throws SQLException
It returns true if the new current row is valid, false if there is no row in ResultSet.
 
5- boolean isBeforeFirst()- It is used to check whether ResultSet is before the first row or not. It has the following syntax -
boolean isBeforeFirst() throws SQLException
It returns true if cursor is before the first row, false otherwise.
 
6- boolean isAfterLast()- It is used to check whether ResultSet cursor is after the last row or not. It has the following syntax -
boolean isAfterLast() throws SQLException 
It returns true if cursor is after the last row, false otherwise.
7- boolean isFirst()- It is used to check whether ResultSet cursor is at the first row or not. It has the following syntax -
boolean isFirst() throws SQLException
It returns true if cursor is at the first row, false otherwise.
8- boolean isLast()- It is used to check whether ResultSet cursor is at the last row or not. It has the following syntax -
boolean isLast() throws SQLException
It returns true if cursor is at the last row, false otherwise.
9- int getInt()- It returns the integer value stored at specified column index or column label in the current row of ResultSet.
There are 2 versions of getInt(), one take column index as parameter and anther take column label as parameter-
int getInt(int Column_Index) throws SQLException 
It take column index as parameter and return the integer value stored at that index in the current row of ResultSet.
Note column index starts from 1.
    
int getInt(int Column_Label) throws SQLException
It take column label as parameter and return the integer value stored at that index in the current row of ResultSet.

10- String getString()- It returns the String value stored at specified column index or column label in the current row of ResultSet.
There are 2 versions of getString(), one take column index as parameter and anther take column label as parameter.
int getString(int Column_Index) throws SQLException
It take column index as parameter and return the string value stored at that index in the current row of ResultSet. 
int getString(int Column_Label) throws SQLException
It take column label as parameter and return the string value stored at that index in the current row of 
ResultSet.    
Similar to getInt() and getString(), ResultSet also have the following methods that works exactly the same way as these two methods -
11- getShort(int Column_Index/String Column_Label)- Returns the corresponding short value.
12- getLong(int Column_Index/String Column_Label)- Returns the corresponding long value.
13- getFloat(int Column_Index/String Column_Label)- Returns the corresponding float value.
14- getDouble(int Column_Index/String Column_Label)- Returns the corresponding double value.
15- getDate(int Column_Index/String Column_Label)- Returns the corresponding date value.

Example - (Using MySQL RDBMS)

In this example we select and print all the data of a table named "Employee" exists in database named "Company".
EMployee table
eid ename salary
1 Rohit 1000000
2 Mohit 2000000

Note - To see the detailed steps required to connect with MySql using NetBeans or Eclipse IDE please refer to the Connectivity using MySql RDBMS section.

import java.sql.*;
public class MY_JDBC_DEMO {

    public static void main(String[] args) {
        
        Connection con;
        try
        {
            //1. Load the driver
            Class.forName("com.mysql.jdbc.Driver");
            
            //2. Establish connection
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/Company", "root", "root");
            
            //3. Create Statement Object
            Statement st = con.createStatement();
            
            //4. Execute query
            ResultSet rs = st.executeQuery("select * from Employee");

           // to print values contained in table
            while(rs.next())
            {
                System.out.println(rs.getInt(1)+"   "+rs.getString(2)+"   "+rs.getInt(3));
            }
            
            //5. Close the connection
            con.close();
        }
        catch(Exception ex)
        {
            System.out.println(ex);
        }
      
    }
}        

Output -

1 Rohit 1000000
2 Mohit 2000000

ResultSetMetaData interface -

Metadata is data about data i.e. further information about data. ResultSetMetaData is used to get the Meta data of ResultSet. i.e. we can get deeper information about ResultSet like column names, column types, total number of columns.
We can get a reference of ResultSetMetaData by using getMetaData() method of ResultSet interface. It has the following syntax -
ResultSetMetaData getMetaData()  throws SQLException
For Example -
ResultSetMetaData rsmd = rs.getMetaData();
Where rs is reference of ResultSet interface.

Methods of ResultSetMetaData -

Some most commonly used methods of ResultSetMetaData are as follows -
1- int getColumnCount() throws SQLException - the number of columns in this ResultSet object.
2- String getColumnName(int column_Index) throws SQLException - It returns the column name available at specified column_Index.
3- int getColumnType(int column_Index) throws SQLException - It returns the column SQL type from java.sql.Types. available at specified column_Index.
java.sql.Types is a class that defines the constants that are used to identify generic SQL types, called JDBC types. Some constant field with values are as follows -
Constant Field Value
INTEGER 4
FLOAT 6
DOUBLE 8
CHAR 1
Boolean 16
NUMERIC 2
REAL 7
VARCHAR 12
DATE 91
TIME 92
TIMESTAMP 93
4- String getColumnTypeName(int column_Index) throws SQLException - It returns the database specific type name of the column specified by column_Index.
5- String getTableName(int column_Index) throws SQLException -
It returns the columns table name.

Example - (Using MySQL RDBMS)

In this example we select and print the data along with column names of a table named "Employee" exists in database named "Company".
EMployee table
eid ename salary
1 Rohit 1000000
2 Mohit 2000000

Note - To see the detailed steps required to connect with MySql using NetBeans or Eclipse IDE please refer to the Connectivity using MySql RDBMS section.

import java.sql.*;
public class MY_JDBC_DEMO {

    public static void main(String[] args) {
        
        Connection con;
        try
        {
            // Load the driver
            Class.forName("com.mysql.jdbc.Driver");
            
            // Establish connection
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/Company", "root", "root");
            
            // Create Statement Object
            Statement st = con.createStatement();
            
            // Execute query
            ResultSet rs = st.executeQuery("select * from Employee");
            ResultSetMetaData rsmd = rs.getMetaData();
            
            // Get total number of columns
            int cols = rsmd.getColumnCount();
            
            // To print columns names of table
            for(int i =1; i<=cols; i++)
            {
                System.out.print(rsmd.getColumnName(i)+"   ");
            }
            
            // for next line
            System.out.println();
            
            // to print values contained in table
            while(rs.next())
            {
                System.out.println(rs.getInt(1)+"   "+rs.getString(2)+"   "+rs.getInt(3));
            }
            
            // Close the connection
            con.close();
        }
        catch(Exception ex)
        {
            System.out.println(ex);
        }
      
    }
}
        

Output -

EMployee table
eid ename salary
1 Rohit 1000000
2 Mohit 2000000

2- int executeUpdate(String SQL_Query) -

This method is used to execute SQL Insert, Update, delete and other DDL statements that does not return anything. It has the following syntax -
public abstract int executeUpdate(String SQL_Query) throws SQLException
It executes the specified SQL_Query and returns the number of rows affected by the operation.
For example -
To delete all data of table named emp
int rows_affected = st.executeUpdate("delete from emp");

Example 1- (Using MySQL RDBMS)

In this example we insert a new row of data in table named "Employee" exists in database named "Company".
Note - To see the detailed steps required to connect with MySql using NetBeans or Eclipse IDE please refer to the Connectivity using MySql RDBMS section.

import java.sql.*;
public class MY_JDBC_DEMO {

    public static void main(String[] args) {
        
        Connection con;
        try
        {
            // Load the driver
            Class.forName("com.mysql.jdbc.Driver");
            
            // Establish connection
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/Company", "root", "root");
            
            // Create Statement Object
            Statement st = con.createStatement();
            
            // Execute query
            int row_affected = st.executeUpdate("insert into Employee values(3, 'Ajay', 1500000)");
            System.out.println("Number of Rows affected by query = "+row_affected);
            
            // Close the connection
            con.close();
        }
        catch(Exception ex)
        {
            System.out.println(ex);
        }
      
    }

Output -

Number of Rows affected by query = 1
As the output is one that means our query is successfully executes. We can also see the result by executing the following query in MySql Command window -
mysql> select * from Employee;
It produce the following output now -
1 Rohit 1000000
2 Mohit 2000000
3 Ajay 1500000

Example 2 - (Using MySQL RDBMS)

In this example we update the salary of employee named "Rohit" from 1000000 to 800000.
Note - To see the detailed steps required to connect with MySql using NetBeans or Eclipse IDE please refer to the Connectivity using MySql RDBMS section.

import java.sql.*;
public class MY_JDBC_DEMO {

    public static void main(String[] args) {
        
        Connection con;
        try
        {
            // Load the driver
            Class.forName("com.mysql.jdbc.Driver");
            
            // Establish connection
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/Company", "root", "root");
            
            // Create Statement Object
            Statement st = con.createStatement();
            
            // Execute query
            int row_affected = st.executeUpdate("update Employee set salary = 800000 where ename = 'Rohit'");
            System.out.println("Number of Rows affected by query = "+row_affected);
            
            // Close the connection
            con.close();
        }
        catch(Exception ex)
        {
            System.out.println(ex);
        }
      
    }
}
        

Output -

Number of Rows affected by query = 1
As the output is 1 that means our query is successfully executes. We can also see the result by executing the following query in MySql Command window -
mysql> select * from Employee;
It produce the following output now -
1 Rohit 800000
2 Mohit 2000000
3 Ajay 1500000

Example 3 - (Using MySQL RDBMS)

In this example we delete two rows from table where eid is 1 and 2.
Note - To see the detailed steps required to connect with MySql using NetBeans or Eclipse IDE please refer to the Connectivity using MySql RDBMS section.

import java.sql.*;
public class MY_JDBC_DEMO {

    public static void main(String[] args) {
        
        Connection con;
        try
        {
            // Load the driver
            Class.forName("com.mysql.jdbc.Driver");
            
            // Establish connection
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/Company", "root", "root");
            
            // Create Statement Object
            Statement st = con.createStatement();
            
            // Execute query
            int row_affected = st.executeUpdate("delete from Employee where eid in(1,2)");
            System.out.println("Number of Rows affected by query = "+row_affected);
            
            // Close the connection
            con.close();
        }
        catch(Exception ex)
        {
            System.out.println(ex);
        }
      
    }
}
        

Output -

Number of Rows affected by query = 2
It produce the output 2, because 2 rows are deleted by query execution i.e. two rows are affected.