PreparedStatement 命名参数查询 -

bjtdeyx

浏览: 169712 次
性别:
来自: 成都

最近访客更多访客>>

wxf0322

fengling4

james1110

xiangkun

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

PreparedStatement 命名参数查询

博客分类：

java
j2ee
数据库

preparedstatement 命名参数查询

网上找到的都有错，我改良了其中一个，可以正常使用！！
如下：

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

public class NamedParameterStatement {
private final PreparedStatement statement;

private final Map indexMap;

public NamedParameterStatement(Connection connection, String query)
  throws SQLException {
 indexMap = new HashMap();
 String parsedQuery = parse(query, indexMap);
 statement = connection.prepareStatement(parsedQuery);
}

static final String parse(String query, Map paramMap) {

 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 = '?';
    i += name.length();

    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);
 }

 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();
}

private int[] getIndexes(String name) {
 int[] indexes = (int[]) indexMap.get(name);
 if (indexes == null) {
  throw new IllegalArgumentException("Parameter not found: " + name);
 }
 return indexes;
}

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);
 }
}

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);
 }
}

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);
 }
}

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);
 }
}

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);
 }
}

public PreparedStatement getStatement() {
 return statement;
}

public boolean execute() throws SQLException {
 return statement.execute();
}

public ResultSet executeQuery() throws SQLException {
 return statement.executeQuery();
}

public int executeUpdate() throws SQLException {
 return statement.executeUpdate();
}

public void close() throws SQLException {
 statement.close();
}

public void addBatch() throws SQLException {
 statement.addBatch();
}

public int[] executeBatch() throws SQLException {
 return statement.executeBatch();
}
}

调用方法：

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);
ResutlSet rs=p.executeQuery();
。。。。。。。。