Pass input parameters to a PreparedStatement

By using PreparedStatement, you can pass input parameters at runtime to your statements. Lets say there is 'employee' table of columns 'id' of type 'int' and 'name' of type 'string'.

   Connection conn = getConnection();

   PreparedStatement prepStmt;

   prepStmt = conn.prepareStatement("INSERT INTO employee values (?, ?)");

Below statements are used to pass value '1' to id and value 'Krishna' to name to the prepStmt at runtime.

   prepStmt.setInt(1, 1);

   prepStmt.setString(2, "Krishna");

   prepStmt.execute();

/* Step 1: Import sql package */
import java.sql.*;

public class SampleApp {
    
    /* Update username, password and driver details here */
    static Connection getConnection() throws ClassNotFoundException, SQLException{
         /* Step 2: Load Driver */
        System.out.println("Loading/Registering driver");  
       
        Class.forName("com.mysql.jdbc.Driver");
         
        /* Step 3: Open connection to database */
        System.out.println("Connecting to database");
        String url = "jdbc:mysql://localhost/world";
        String userName = "root";
        String pasword = "tiger";
        return DriverManager.getConnection(url, userName, pasword);
    }
    
    public static void main(String args[]) throws SQLException, ClassNotFoundException{     
        Connection conn = getConnection();
        Statement stmt = conn.createStatement();
        PreparedStatement prepStmt;
        prepStmt = conn.prepareStatement("INSERT INTO employee values (?, ?)");
        stmt.execute("CREATE TABLE employee (id int, name varchar(30), PRIMARY KEY(id))");
        
        prepStmt.setInt(1, 1);
        prepStmt.setString(2, "Krishna");
        prepStmt.execute();
        
        prepStmt.setInt(1, 2);
        prepStmt.setString(2, "Arjun");
        prepStmt.execute();
       
        String query = "SELECT * FROM employee";
        ResultSet rs = stmt.executeQuery(query);
        
        while(rs.next()){
            int id  = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println(id +" " + name);
        }
        System.out.println("Dropping table employee");
        query = "DROP TABLE employee";
        stmt.execute(query);
        
 rs.close();
        stmt.close();
        conn.close();
    }
}

Output

Loading/Registering driver
Connecting to database
1 Krishna
2 Arjun
Dropping table employee

PreparedStatement interface provides various setter methods, to set the designated parameter to specific value.

Method	Description
void setArray(int parameterIndex, Array x)	Sets the designated parameter to the given java.sql.Array object.
void setAsciiStream(int parameterIndex, InputStream x)	Sets the designated parameter to the given input stream.
void setAsciiStream(int parameterIndex, InputStream x, int length)	Sets the designated parameter to the given input stream, which will have the specified number of bytes.
void setAsciiStream(int parameterIndex, InputStream x, long length)	Sets the designated parameter to the given input stream, which will have the specified number of bytes.
void setBigDecimal(int parameterIndex, BigDecimal x)	Sets the designated parameter to the given java.math.BigDecimal value.
void setBinaryStream(int parameterIndex, InputStream x)	Sets the designated parameter to the given input stream.
void setBinaryStream(int parameterIndex, InputStream x, int length)	Sets the designated parameter to the given input stream, which will have the specified number of bytes.
void setBinaryStream(int parameterIndex, InputStream x, long length)	Sets the designated parameter to the given input stream, which will have the specified number of bytes.
void setBlob(int parameterIndex, Blob x)	Sets the designated parameter to the given java.sql.Blob object.
void setBlob(int parameterIndex, InputStream inputStream)	Sets the designated parameter to setBlob(int parameterIndex, InputStream inputStream, long length)a InputStream object.
void setBlob(int parameterIndex, InputStream inputStream, long length)	Sets the designated parameter to a InputStream object.
void setBoolean(int parameterIndex, boolean x)	Sets the designated parameter to the given Java boolean value.
void setByte(int parameterIndex, byte x)	Sets the designated parameter to the given Java byte value.
void setBytes(int parameterIndex, byte[] x)	Sets the designated parameter to the given Java array of bytes.
void setCharacterStream(int parameterIndex, Reader reader)	Sets the designated parameter to the given Reader object.
void setCharacterStream(int parameterIndex, Reader reader, int length)	Sets the designated parameter to the given Reader object, which is the given number of characters long.
void setCharacterStream(int parameterIndex, Reader reader, long length)	Sets the designated parameter to the given Reader object, which is the given number of characters long.
void setClob(int parameterIndex, Clob x)	Sets the designated parameter to the given java.sql.Clob object.
void setClob(int parameterIndex, Reader reader)	Sets the designated parameter to a Reader object.
void setClob(int parameterIndex, Reader reader, long length)	Sets the designated parameter to a Reader object.
void setDate(int parameterIndex, Date x)	Sets the designated parameter to the given java.sql.Date value using the default time zone of the virtual machine that is running the application.
void setDate(int parameterIndex, Date x, Calendar cal)	Sets the designated parameter to the given java.sql.Date value, using the given Calendar object.
void setDouble(int parameterIndex, double x)	Sets the designated parameter to the given Java double value.
void setFloat(int parameterIndex, float x)	Sets the designated parameter to the given Java float value.
void setInt(int parameterIndex, int x)	Sets the designated parameter to the given Java int value
void setLong(int parameterIndex, long x)	Sets the designated parameter to the given Java long value.
void setNCharacterStream(int parameterIndex, Reader value)	Sets the designated parameter to a Reader object.
void setNCharacterStream(int parameterIndex, Reader value, long length)	Sets the designated parameter to a Reader object.
void setNClob(int parameterIndex, NClob value)	Sets the designated parameter to a java.sql.NClob object.
void setNClob(int parameterIndex, Reader reader)	Sets the designated parameter to a Reader object.
void setNClob(int parameterIndex, Reader reader, long length)	Sets the designated parameter to a Reader object.
void setNString(int parameterIndex, String value)	Sets the designated parameter to the given String object.
void setNull(int parameterIndex, int sqlType)	Sets the designated parameter to SQL NULL.
void setNull(int parameterIndex, int sqlType, String typeName)	Sets the designated parameter to SQL NULL.
void setObject(int parameterIndex, Object x)	Sets the value of the designated parameter using the given object.
void setObject(int parameterIndex, Object x, int targetSqlType)	Sets the value of the designated parameter with the given object.
void setObject(int parameterIndex, Object x, int targetSqlType, int scaleOrLength)	Sets the value of the designated parameter with the given object.
void setRef(int parameterIndex, Ref x)	Sets the designated parameter to the given REF(<structured-type>) value.
void setRowId(int parameterIndex, RowId x)	Sets the designated parameter to the given java.sql.RowId object.
void setShort(int parameterIndex, short x)	Sets the designated parameter to the given Java short value.
void setSQLXML(int parameterIndex, SQLXML xmlObject)	Sets the designated parameter to the given java.sql.SQLXML object
void setString(int parameterIndex, String x)	Sets the designated parameter to the given Java String value.
void setTime(int parameterIndex, Time x)	Sets the designated parameter to the given java.sql.Time value.
void setTime(int parameterIndex, Time x, Calendar cal)	Sets the designated parameter to the given java.sql.Time value, using the given Calendar object.
void setTimestamp(int parameterIndex, Timestamp x)	Sets the designated parameter to the given java.sql.Timestamp value.
void setTimestamp(int parameterIndex, Timestamp x, Calendar cal)	Sets the designated parameter to the given java.sql.Timestamp value, using the given Calendar object.
void setURL(int parameterIndex, URL x)	Sets the designated parameter to the given java.net.URL value.

Prevoius                                                 Next                                                 Home