Steps for creating connection with the database -


PreparedStatement Interface -

PreparedStatement interface inherits the Statement interface and provides a way to execute dynamic queries. Dynamic queries are queries that executes by taking input from user.
For example consider the following query -
String Sql_Query = "select * from student where roll = ?";
This is a dynamic query where value of roll denoted by ? (question mark) parameter, will be input by the user.
PreparedStatement provides several setter methods to provide such values at run time. These will be discussed next.

We can get a reference to PreparedStatement by using prepareStatement() method of Connection interface. There are several versions of prepareStatement() available, the most commonly used version has the following syntax -
PreparedStatement prepareStatement(String Sql_Query) throws SQLException

For example -
PreparedStatement ps = con.prepareStatement("select * from student where name = ? and city=?");
Where con represent active Connection object.
The value of name and city denoted by ? parameters will be set by user input by using setter methods provided by PreparedStatement interface -
1- void setInt(int parameter_Index, int val) throws SQLException - It sets the parameter indicated by parameter_Index to the integer value defined by val. The java driver converts this value to SQL INTEGER value when it sends it to database.
Note- first, second, third parameters have the parameter_Index = 1, 2, 3 respectively and so on.
2- void setString(int parameter_Index, String val) throws SQLException - It sets the parameter indicated by parameter_Index to the String value defined by val. The java driver converts this value to SQL varchar or varchar2 (depends upon String length) value when it send it to database.

Similar to setInt() and setString(), PreparedStatment also have the following methods that works exactly the same way as these two methods -
3- void setByte(int parameter_Index, byte val) throws SQLException
4- void setShort(int parameter_Index, short val) throws SQLException
5- void setLong(int parameter_Index, long val) throws SQLException
6- void setFloat(int parameter_Index, float val) throws SQLException
7- void setDouble(int parameter_Index, double val) throws SQLException
8- void setBoolean(int parameter_Index, boolean val) throws SQLException
9- void setArray(int parameter_Index, Array val) throws SQLException
10- void setDate(int parameter_Index, Date val) throws SQLException - It sets the parameter indicated by parameter_Index to the java.sql.Date value defined by val. The java driver converts this value to SQL DATEvalue when it send it to database.
11- void setTime(int parameter_Index, Time val) throws SQLException - It sets the parameter indicated by parameter_Index to the java.sql.Time value defined by val. The java driver converts this value to SQL TIME value when it send it to database.
12- void setTimestamp(int parameter_Index, Timestamp val) throws SQLException - It sets the parameter indicated by parameter_Index to the java.sql.Timestamp value defined by val. The java driver converts this value to SQL TIMESTAMP value when it send it to database.

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.
Similar to Statement interface, PreparedStatement have the same methods to execute SQL statements. The difference between two is that PreparedStatement methods does not accept parameters.

1- ResultSet executeQuery(String SQL_Query) -

This method is used to execute SQL Select statement. It has the following syntax -
public abstract ResultSet executeQuery() throws SQLException
It executes the select query specified in PreparedStatement object and returns the result in ResultSet.
For example - To get all data of a table named emp -
ResultSet rs = ps.executeQuery();
Here ps is the PreparedStatement object that contain the SQL Query. All data of emp table will be stored in 
ResultSet specified by rs.

Example - (Using MySQL RDBMS)

In this example we select and print the data from a table named "Employee" exists in database "Company". The data will be selected according to user input at run time.
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.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.*;
public class MY_JDBC_DEMO {

    public static void main(String[] args) {
        
        Connection con;
        try
        {
            BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
            System.out.println("Enter first employee id");
            int id1 = Integer.parseInt(br.readLine());
            System.out.println("Enter second employee id");
            int id2 = Integer.parseInt(br.readLine());
            
            // Load the driver
            Class.forName("com.mysql.jdbc.Driver");
            
            // Establish connection
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/Company", "root", "root");
            
            // Create Statement Object
            PreparedStatement ps = con.prepareStatement("select * from Employee where eid = ? or eid = ?");
            
            
            ps.setInt(1, id1); // set the value of first parameter (ist ?).
            ps.setInt(2, id2);  // set the value of second parameter (2nd ?).
            
            // Execute query
            ResultSet rs = ps.executeQuery();
            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 -

Enter first employee id
1
Enter second employee id
3

eid   ename   salary   
1   Rohit   800000
3   Ajay   1500000
    

2- int executeUpdate()-

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() throws SQLException
It executes the query specified in PreparedStatement object and returns the number of rows affected by the operation.
For example - To delete all data of table named emp
int rows_affected = ps.executeUpdate();

Example - (Using MySQL RDBMS)

In this example we insert a new row in a table named "Employee" exists in database "Company". The values to be inserted will be decided by user at run time.
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.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.*;
public class MY_JDBC_DEMO {

    public static void main(String[] args) {
        
        Connection con;
        try
        {
            BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
            System.out.println("Enter employee id");
            int id = Integer.parseInt(br.readLine());
            System.out.println("Enter employee name");
            String name = br.readLine();
            System.out.println("Enter employee salary");
            int sal = Integer.parseInt(br.readLine());            
            
            // Load the driver
            Class.forName("com.mysql.jdbc.Driver");
            
            // Establish connection
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/Company", "root", "root");
            
            // Create Statement Object
            PreparedStatement ps = con.prepareStatement("insert into Employee values(?, ?, ?)");
               
            ps.setInt(1, id); // set the value of first parameter (1st ?).
            ps.setString(2, name);  // set the value of second parameter (2nd ?).
            ps.setInt(3, sal); // set the value of 3rd parameter (3rd ?).
            
            // Execute query
            int rows = ps.executeUpdate();
            System.out.println("No of rows affected = "+ rows);        
            con.close();
        }
        catch(Exception ex)
        {
            System.out.println(ex);
        }
      
    }

}
        

Output -

Enter employee id
4
Enter employee name
Pranjay
Enter employee salary
3000000
No of rows affected = 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 - 

eid     ename   salary
1         Rohit         800000
2         Mohit      2000000
3         Ajay          1500000
4        Pranjay     3000000
    

Example 2 - (Using MySql RDBMS)

In this example we update the name and salary of an employee whose employee id will be input by the user at run time, from "Employee" table exists in "Compan" database.
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.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.*;
public class MY_JDBC_DEMO {

    public static void main(String[] args) {
        
        Connection con;
        try
        {
            BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
            System.out.println("Enter employee id");
            int id = Integer.parseInt(br.readLine());
            System.out.println("Enter employee name");
            String name = br.readLine();
            System.out.println("Enter employee salaray");
            int sal = Integer.parseInt(br.readLine());            
            
            // Load the driver
            Class.forName("com.mysql.jdbc.Driver");
            
            // Establish connection
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/Company", "root", "root");
            
            // Create Statement Object
            PreparedStatement ps = con.prepareStatement("update Employee set ename = ?, salary = ? where eid = ?");
               
            ps.setString(1, name);  // set the value of first parameter (1st ? i.e. ename).
            ps.setInt(2, sal); // set the value of 2nd parameter (2nd ? i.e. salary).
            ps.setInt(3, id); // set the value of third parameter (3rd ? i.e. eid).
            
            // Execute query
            int rows = ps.executeUpdate();
            System.out.println("No of rows affected = "+ rows);        
            con.close();
        }
        catch(Exception ex)
        {
            System.out.println(ex);
        }
      
    }

}
        

Output -

Enter employee id
4
Enter employee name
Sanjay
Enter employee salaray
500000
No of rows affected = 1
It changes the name and salary of employee whose eid = 4.
    
We can also see the result by typing the following query in MySql Command line -
mysql> select * from Employee;

eid   ename  salary
1       Rohit    800000
2       Mohit   2000000
3       Ajay      1500000
4       Sanjay   500000