mysql like preparedstatement,关于 PreparedStatement

If you have a SQL statement that needs to be executed multiple times, it is more efficient to use a JDBC PreparedStatement object to run it. JDBC PreparedStatement class supports the following main features:

SQL statements PreparedStatement objects are pre-compiled on the database server side.

IN parameters are supported in SQL statements in PreparedStatement objects.

Batch execution mode is supported to run the run SQL statement multiple times in a single transaction.

您可以使用Connection的prepareStatement()方法建立好一個預先編譯(precompile)的SQL語句，當中參數會變動的部份，先指定"?"這個佔位字元，例如：

PreparedStatement stmt = conn.prepareStatement(

"INSERT INTO message VALUES(?, ?, ?, ?, ?)");

使用PreparedStatement也可以進行批次處理，直接來看個例子就知道如何使用：

PreparedStatement stmt = conn.prepareStatement(

"INSERT INTO Users VALUES(?,?, ?)");

User[] users = ...;

for(int i=0; i

stmt.setInt(1, users[i].getID());

stmt.setString(2, users[i].getName());

stmt.setString(3, users[i].getPassword());

stmt.addBatch( );

}

stmt.executeBatch();

A PreparedStatement object should be created from a Connection object with the prepareStatement() method and executed like a regular Statement object as shown in the following program:

import java.util.*;

import java.sql.*;

import javax.sql.*;

import javax.naming.*;

public class MySqlPreparedSelect {

public static void main(String [] args) {

Connection con = null;

try {

com.mysql.jdbc.jdbc2.optional.MysqlDataSource ds

= new com.mysql.jdbc.jdbc2.optional.MysqlDataSource();

ds.setServerName("localhost");

ds.setPortNumber(3306);

ds.setDatabaseName("HerongDB");

ds.setUser("Herong");

ds.setPassword("TopSecret");

con = ds.getConnection();

// PreparedStatement for SELECT statement

PreparedStatement sta = con.prepareStatement(

"SELECT * FROM Profile WHERE ID = 2");

// Execute the PreparedStatement as a query

ResultSet res = sta.executeQuery();

// Get values out of the ResultSet

res.next();

String firstName = res.getString("FirstName");

String lastName = res.getString("LastName");

System.out.println("User ID 2: "+firstName+' '+lastName);

// Close ResultSet and PreparedStatement

res.close();

sta.close();

con.close();

} catch (Exception e) {

System.err.println("Exception: "+e.getMessage());

e.printStackTrace();

}

}

}

posted on 2008-10-02 16:26 picture talk 阅读(971) 评论(0)  编辑  收藏