để đưa parameter vào dễ dàng hơn nhé.Thông thường PreparedStatement của Java sẽ có dạng như sau:
PreparedStatement p = con.prepareStatement("select * from people where
(first_name = ? or last_name = ?) and address = ?");
p.setString(1, name);
p.setString(2, name);
p.setString(3, address);
Với câu SQL đơn giản có ít đối số đầu vào thế này ta có thể dễ dàng nhớ được index của từng paramđể có thể set value cho nó.Nhưng với 1 câu có nhiều đối số vào thì sao? Ta phải đếm từng param và set index cho nó ?
Dưới đây mình sẽ hướng dẫn bạn 2 cách để dễ dàng làm việc với nó:
Cách 1:
PreparedStatement p = con.prepareStatement("select * from people where
(first_name = ? or last_name = ?) and address = ?");
int index = 1;
p.setString(index++, name);
p.setString(index++ , name);
p.setString(index++, address);
Cách trên rất hữu dụng khi ta dùng index++.Nhưng các bạn có để ý thấy với việc 2 param 1,2 nhận cùng 1 giá trị ko ?Chúng ta lại phải mất công để set giá trị lại cho nó.Thật vô nghĩ.Tôi sẽ hướng dẫn cách bạn cách tối ưu hơn nữa.
Cách 2:
String query = "select * from people where (first_name = :name or last_name
= :name) and address = :address");
NamedParameterStatement p = new NamedParameterStatement(con, query);
p.setString("name", name);
p.setString("address", address);
Code của custom class NamedParameterStatement
public class NamedParameterStatement {
/** The statement this object is wrapping. */
private final PreparedStatement statement;
/** Maps parameter names to arrays of ints which are the parameter indices. */
@SuppressWarnings("rawtypes")
private final Map indexMap;
/**
* Creates a NamedParameterStatement. Wraps a call to c.
* {@link Connection#prepareStatement(java.lang.String) prepareStatement}.
*
* @param connection
* the database connection
* @param query
* the parameterized query
* @throws SQLException
* if the statement could not be created
*/
@SuppressWarnings("rawtypes")
public NamedParameterStatement(Connection connection, String query)
throws SQLException {
indexMap = new HashMap();
String parsedQuery = parse(query, indexMap);
statement = connection.prepareStatement(parsedQuery);
}
/**
* Parses a query with named parameters. The parameter-index mappings are
* put into the map, and the parsed query is returned. DO NOT CALL FROM
* CLIENT CODE. This method is non-private so JUnit code can test it.
*
* @param query
* query to parse
* @param paramMap
* map to hold parameter-index mappings
* @return the parsed query
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
static final String parse(String query, Map paramMap) {
// I was originally using regular expressions, but they didn't work well
// for ignoring
// parameter-like strings inside quotes.
int length = query.length();
StringBuffer parsedQuery = new StringBuffer(length);
boolean inSingleQuote = false;
boolean inDoubleQuote = false;
int index = 1;
for (int i = 0; i < length; i++) {
char c = query.charAt(i);
if (inSingleQuote) {
if (c == '\'') {
inSingleQuote = false;
}
} else if (inDoubleQuote) {
if (c == '"') {
inDoubleQuote = false;
}
} else {
if (c == '\'') {
inSingleQuote = true;
} else if (c == '"') {
inDoubleQuote = true;
} else if (c == ':' && i + 1 < length
&& Character.isJavaIdentifierStart(query.charAt(i + 1))) {
int j = i + 2;
while (j < length
&& Character.isJavaIdentifierPart(query.charAt(j))) {
j++;
}
String name = query.substring(i + 1, j);
c = '?'; // replace the parameter with a question mark
i += name.length(); // skip past the end if the parameter
List indexList = (List) paramMap.get(name);
if (indexList == null) {
indexList = new LinkedList();
paramMap.put(name, indexList);
}
indexList.add(new Integer(index));
index++;
}
}
parsedQuery.append(c);
}
// replace the lists of Integer objects with arrays of ints
for (Iterator itr = paramMap.entrySet().iterator(); itr.hasNext();) {
Map.Entry entry = (Map.Entry) itr.next();
List list = (List) entry.getValue();
int[] indexes = new int[list.size()];
int i = 0;
for (Iterator itr2 = list.iterator(); itr2.hasNext();) {
Integer x = (Integer) itr2.next();
indexes[i++] = x.intValue();
}
entry.setValue(indexes);
}
return parsedQuery.toString();
}
/**
* Returns the indexes for a parameter.
*
* @param name
* parameter name
* @return parameter indexes
* @throws IllegalArgumentException
* if the parameter does not exist
*/
private int[] getIndexes(String name) {
int[] indexes = (int[]) indexMap.get(name);
if (indexes == null) {
throw new IllegalArgumentException("Parameter not found: " + name);
}
return indexes;
}
/**
* Sets a parameter.
*
* @param name
* parameter name
* @param value
* parameter value
* @throws SQLException
* if an error occurred
* @throws IllegalArgumentException
* if the parameter does not exist
* @see PreparedStatement#setObject(int, java.lang.Object)
*/
public void setObject(String name, Object value) throws SQLException {
int[] indexes = getIndexes(name);
for (int i = 0; i < indexes.length; i++) {
statement.setObject(indexes[i], value);
}
}
/**
* Sets a parameter.
*
* @param name
* parameter name
* @param value
* parameter value
* @throws SQLException
* if an error occurred
* @throws IllegalArgumentException
* if the parameter does not exist
* @see PreparedStatement#setString(int, java.lang.String)
*/
public void setString(String name, String value) throws SQLException {
int[] indexes = getIndexes(name);
for (int i = 0; i < indexes.length; i++) {
statement.setString(indexes[i], value);
}
}
/**
* Sets a parameter.
*
* @param name
* parameter name
* @param type
* parameter type
* @throws SQLException
* if an error occurred
* @throws IllegalArgumentException
* if the parameter does not exist
* @see PreparedStatement#setString(int, java.lang.String)
*/
public void setNull(String name, int type) throws SQLException {
int[] indexes = getIndexes(name);
for (int i = 0; i < indexes.length; i++) {
statement.setNull(indexes[i], type);
}
}
/**
* Sets a parameter.
*
* @param name
* parameter name
* @param value
* parameter value
* @throws SQLException
* if an error occurred
* @throws IllegalArgumentException
* if the parameter does not exist
* @see PreparedStatement#setInt(int, int)
*/
public void setInt(String name, int value) throws SQLException {
int[] indexes = getIndexes(name);
for (int i = 0; i < indexes.length; i++) {
statement.setInt(indexes[i], value);
}
}
/**
* Sets a parameter.
*
* @param name
* parameter name
* @param value
* parameter value
* @throws SQLException
* if an error occurred
* @throws IllegalArgumentException
* if the parameter does not exist
* @see PreparedStatement#setInt(int, int)
*/
public void setLong(String name, long value) throws SQLException {
int[] indexes = getIndexes(name);
for (int i = 0; i < indexes.length; i++) {
statement.setLong(indexes[i], value);
}
}
/**
* Sets a parameter.
*
* @param name
* parameter name
* @param value
* parameter value
* @throws SQLException
* if an error occurred
* @throws IllegalArgumentException
* if the parameter does not exist
* @see PreparedStatement#setTimestamp(int, java.sql.Timestamp)
*/
public void setTimestamp(String name, Timestamp value) throws SQLException {
int[] indexes = getIndexes(name);
for (int i = 0; i < indexes.length; i++) {
statement.setTimestamp(indexes[i], value);
}
}
/**
* Returns the underlying statement.
*
* @return the statement
*/
public PreparedStatement getStatement() {
return statement;
}
/**
* Executes the statement.
*
* @return true if the first result is a {@link ResultSet}
* @throws SQLException
* if an error occurred
* @see PreparedStatement#execute()
*/
public boolean execute() throws SQLException {
return statement.execute();
}
/**
* Executes the statement, which must be a query.
*
* @return the query results
* @throws SQLException
* if an error occurred
* @see PreparedStatement#executeQuery()
*/
public ResultSet executeQuery() throws SQLException {
return statement.executeQuery();
}
/**
* Executes the statement, which must be an SQL INSERT, UPDATE or DELETE
* statement; or an SQL statement that returns nothing, such as a DDL
* statement.
*
* @return number of rows affected
* @throws SQLException
* if an error occurred
* @see PreparedStatement#executeUpdate()
*/
public int executeUpdate() throws SQLException {
return statement.executeUpdate();
}
/**
* Closes the statement.
*
* @throws SQLException
* if an error occurred
* @see Statement#close()
*/
public void close() throws SQLException {
statement.close();
}
/**
* Adds the current set of parameters as a batch entry.
*
* @throws SQLException
* if something went wrong
*/
public void addBatch() throws SQLException {
statement.addBatch();
}
/**
* Executes all of the batched statements.
*
* See {@link Statement#executeBatch()} for details.
*
* @return update counts for each statement
* @throws SQLException
* if something went wrong
*/
public int[] executeBatch() throws SQLException {
return statement.executeBatch();
}
}
Source code rất rõ ràng.Các bạn có thể tự nghiên cứu logic nhé.
Chúc thành công.Hãy share cho bạn bè nhé !!!Nguồn : JavaWorld
Sign up here with your email
ConversionConversion EmoticonEmoticon