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.
|
No comments:
Post a Comment