Wednesday , June 28 2017
Home / jdbc / JDBC PreparedStatement Example Program

JDBC PreparedStatement Example Program

In this tutorials, we are going to discuss about JDBC preparedstatement example. In JDBC PreparedStatement is an interface coming from java.sql package. It extends the Statement interface. As we already discussed in step by step JDBC example, we have 3 types of statements.

JDBC PreparedStatement :

Here are the some important points about JDBC PreparedStatement object.

  • In an application if we want to run a same query for multiple times with different parameters (values) then we can go with PreparedStatement.
  • If we use the normal Statement, it compiles the sql command for each time before going to execute.
  • If the same command is compiled again and again then the performance of an application is going to be decreased.
  • In case of PreparedStatement, first a command will be sent to database for compilation, then the compiled code will be stored in PreparedStatement object.
  • Now the code can be executed for any number of times by without recompiling the command again and again.
  • Another important limitation of the Statement object is, it can only transfer the data of type text format only. Where as PreparedStatement can transfer binary format also.
  • We can obtain the PreparedStatement object by calling the prepareStatement() method on connection object.

JDBC PreparedStatement Syntax :

PreparedStatement pstmt = connection.prepareStatement(“insert into student values(?,?,?)”);

In the above example, at first the insert command will be sent to database and compiles the command and then the compiled code will assigned into PreparedStatement object.

In the syntax, we used “?” symbol in place of values in insert command. “?” symbol is called as index parameter or replace operator or place resolution operator.

We can use only “?” symbols in the place of values. No other symbols are allowed here.

  • “?” symbol is not allowed for DDL commands.
  • “?” symbol is not allowed to replace table names and column names.

Example :

select ?,? from emp; illegal

select * from emp where ? = ?; illegal

select * from emp where empId = : ?; legal

update ? set sal = ? where empId = ?; illegal

Before we run the compiled code (stored in the PreparedStatement object), we need to set the values to the compiled code by calling setXxx() methods.

Here is the complete example for JDBC PreparedStatement.

JDBC PreparedStatement Example :


package com.onlinetutorialspoint.jdbc; 
 
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.PreparedStatement; 
import java.sql.SQLException; 
import java.util.Scanner; 
 
public class Jdbc_PreparedStaatement_Example { 
 
    public static void main(String[] args) throws Exception { 

        Connection connection = null; 
        PreparedStatement pstatement = null; 
        Scanner scanner = null; 
 
        try { 
             
            scanner = new Scanner(System.in); 
            int n = 0; 
            System.out.println("Enter no. of Students to insert"); 
            n = scanner.nextInt(); 
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/onlinetutorialspoint", "root", "systemuser23!"); 
            if (connection != null) 
            pstatement = connection.prepareStatement("insert into student values(?,?,?,?)"); 
            if (pstatement != null) { 
                for (int i = 1; i <= n; i++) { 
                    System.out.println("Enter " + i + " Student Details"); 
                    System.out.println("Enter Student No : "); 
                    int studentNo = scanner.nextInt(); 
                    System.out.println("Enter Student Name : "); 
                    String studentName = scanner.next(); 
                    System.out.println("Enter Student Address : "); 
                    String studentAddress = scanner.next(); 
                    System.out.println("Enter Student Age : "); 
                    int studentAge = scanner.nextInt(); 
                    pstatement.setInt(1, studentNo); 
                    pstatement.setString(2, studentName); 
                    pstatement.setString(3, studentAddress); 
                    pstatement.setInt(4, studentAge); 
                    int result = pstatement.executeUpdate(); 
                    if (result == 0) { 
                        System.out.println("Student details: are not inserted"); 
                    } else { 
                        System.out.println(result + " records(s) are  inserted"); 
                    } 
                } 
            } 
         } catch (ClassNotFoundException cnfe) { 
            cnfe.printStackTrace(); 
          } catch (SQLException se) { 
            se.printStackTrace(); 
          } catch (Exception ex) { 
            ex.printStackTrace(); 
         } finally { 
            try { 
                if (pstatement != null) { 
                    pstatement.close(); 
                } 
            } catch (SQLException se) { 
                se.printStackTrace(); 
            } 
            try { 
                if (connection != null) { 
                    connection.close(); 
                } 
            } catch (SQLException se) { 
                se.printStackTrace(); 
            } 
        } 
    } 
 
} 

Output :

Enter no. of Students to insert
2
Enter 1 Student Details
Enter Student No :
3005
Enter Student Name :
Rahul
Enter Student Address :
Banglore
Enter Student Age :
29
1 records(s) are  inserted
Enter 2 Student Details
Enter Student No :
3006
Enter Student Name :
Bharat
Enter Student Address :
Hyderabad
Enter Student Age :
32
1 records(s) are  inserted

About chandrashekhar

Hi Folks, you have reach this so far, that shows you like what you are learning. Then why don't you support us to improve for bettor tutorials by leaving your valuable comments and why not you keep in touch with us for latest updates on your favorite blog @ facebook , twitter , Or Google+ ,

Check Also

JDBC Updatable ResultSet Example

Whenever we create a ResultSet object which never allows us to update the database through …

Leave a Reply

Your email address will not be published. Required fields are marked *