Prepared Statement Example

PreparedStatement is an interface declared in the java.sql package. PreparedStatement is used to make the SQL statement execution efficient.

Ads

Tutorials   
JDBC Video Tutorial: Create, Read, Update and Delete (CRUD) Operation examples JDBC Video tutorial - How to read Data from MySQL Database? JDBC Video tutorial - How to delete Data in a MySQL Database? Solving java.lang.ClassNotFoundException: com.mysql.jdbc.Driver exception JDBC Video tutorial - How to update Data in a MySQL Database? JDBC Video Tutorial- How to insert data into MySQL database table? JDBC Video Tutorial: How to download JDBC Driver for MySQL? JDBC Tutorial - Writing first JDBC example and running in Eclipse Java Cross Join MySQL Database Connectivity Example In Java Change Column Name in MySQL Java Mysql Connection Example JDBC ODBC Connection In Java ResultSet In Java Prepared Statement Example JdbcRowSet interface Java JdbcRowSet Example First Step towards JDBC! JDBC Training, Learn JDBC yourself Jdbc Get Column Names JDBC Example with MySQL JDBC Prepared Statement Update JDBC Prepared Statement Insert JDBC Prepared Statement Example JDBC Prepared statement Close JDBC Prepared Statement Addbatch JDBC Nested Resultset JDBC-Odbc Connection JDBC-Odbc Connectivity JDBC Insert Preparedstatement JDBC Insert Null JDBC Get Metadata JDBC Insert Record Jdbc Insert Statement JDBC Meta Data Get tables Jdbc Mysql Connection String Jdbc Mysql Connection Url JDBC Next Jdbc Get Connection JDBC Get Int JDBC Exception JDBC Get Row Count JDBC Functions JDBC Fetch JDBC Execute Update Example JDBC Execute Statement JDBC Execute Query JDBC Drive For Mysql JDBC Connectivity Code In Java JDBC connection timeout
Ads

In this section we will discuss about the JDBC PreparedStatement.

PreparedStatement is an interface declared in the java.sql package. PreparedStatement is used to make the SQL statement execution efficient. In Java, when we use the JDBC to work with database java.sql provides two interfaces for executing the SQL queries, java.sql.Statement and java.sql.PreparedStatement. We can use either of the interfaces but, using these two interfaces are different in implementation. Statement is used to execute the static SQL statement whereas, PreparedStatement corresponds a precompiled SQL statement. When we use the PreparedStatement to execute the SQL statement, the statement is first compiled into the DBMS and then stored in PreparedStatement object. This object can then be used to execute this statement several times.

PreparedStatement has several setter methods for setting IN parameter values. Some of the setter methods are as follows :

  • setByte(int parameterIndex, byte x)
  • setBytes(int parameterIndex, byte[] x)
  • setDate(int parameterIndex, Date x)
  • setDouble(int parameterIndex, double x)
  • setFloat(int parameterIndex, float x)
  • setInt(int parameterIndex, int x)
  • setLong(int parameterIndex, long x)
  • setString(int parameterIndex, String x)
  • setShort(int parameterIndex, short x)
  • setArray(int parameterIndex, Array x)

PreparedStatement has several methods that helps developer to do additional work with the SQL statement these are as :

  • execute()
  • executeQuery()
  • executeUpdate()
  • getMetaData()
  • addBatch()
  • clearParameters()

Example

Here I am giving a simple example which will demonstrate you about the PreaparedStatement. In this example we will execute a SQL statement using PreparedStatement object. In this example we will use "INSERT INTO" SQL statement. An INSERT INTO statement is used to insert the value into the database table. In this example we will first create a database table using MySQL and then we will create a Java classes into which we will use the classes and interfaces of java.sql package for making connection with database and to insert value into the database table.

Database table

student

CREATE TABLE `student` (                
           `rollno` varchar(15) NOT NULL,        
           `name` varchar(20) DEFAULT NULL,      
           `class` varchar(5) DEFAULT NULL,      
           `section` varchar(5) DEFAULT NULL,    
           PRIMARY KEY (`rollno`)                
         ) ENGINE=InnoDB

PreparedStatementExample.java

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class PreparedStatementExample
{       
       public static void main(String args[])
         {
		   String driver="com.mysql.jdbc.Driver";
		   String url = "jdbc:mysql://localhost:3306/record";
           String user = "root";
           String password = "root";
           Connection con = null;
           PreparedStatement ps = null;
            try
               {
                  Class.forName(driver);
               }
             catch(ClassNotFoundException c)
               {
                  System.out.println(c);
               }
              try
               {
                  con = DriverManager.getConnection(url, user, password);
                  String sql = "INSERT INTO student(rollno, name, class, section) VALUES(?,?,?,?)";
				  ps = con.prepareStatement(sql);
                   ps.setString(1, "126");
                   ps.setString(2, "shashi");
                   ps.setString(3, "mca");
                   ps.setString(4, "8");
                  
                   int i = ps.executeUpdate();
                   if(i >0)
                    {
                       System.out.println("\nData inserted into table successfully");
                    }
                   else
                     System.out.println("Data can't be inserted");                 
               }
             catch(SQLException sqe)
               {
                  sqe.printStackTrace();
               }
         }
}

Output :

Database table before inserting a new record

When you will compile and execute the above Java code then the output will be as follows :

Database table after inserting a new record

Download Source Code

Advertisements

Share on Google+Share on Google+

Prepared Statement Example

Posted on: May 6, 2013 If you enjoyed this post then why not add us on Google+? Add us to your Circles

Advertisements

Ads
 

Discuss: Prepared Statement Example  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:0

Ads

 

Ads