不确定条件数据库查询代码

2011年5月30日 总结整理不确定条件数据库查询代码
代码就是这么写的，把能实现一类功能的函数方法总结成一个类，然后把这个类实现的功能扩充到最大，容错能力高，使程序尽可能的健壮。以下是代码：


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;


public class ConnectData {

public static Connection getConn(){
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/yao?user=root&password=root");
return con;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}

public static Map[] executeQuery(String sql, List<String> list) throws SQLException {
Map[] maps = null;
Connection con = null;
ResultSet rs = null;
PreparedStatement ps = null;
try {
con = ConnectData.getConn();
if (con == null) {
throw new NullPointerException("获取当前数据库连接失败");
}
int count = ConnectData.countMatches(sql, "?");
if (list == null || list.size() != count) {
throw new IllegalArgumentException("数据库预编译时索引不匹配");
}
ps = con.prepareStatement(sql);
for (int i = 0; i < list.size(); i++) {
ps.setString(i + 1, (String) list.get(i));
}
rs = ps.executeQuery();
ResultSetMetaData meta = rs.getMetaData();
int columnCount = meta.getColumnCount();
int rowCount = 0;
List result = new ArrayList();
while (rs.next()) {
Map map = new LinkedHashMap();
for (int i = 0; i < columnCount; i++) {
String value = rs.getString(i + 1);
map.put(meta.getColumnName(i + 1), (value == null) ? "" : value);
}
rowCount++;
result.add(map);
}

maps = (Map[]) result.toArray(new Map[result.size()]);
} finally {
if (rs != null)
rs.close();
if (ps != null)
ps.close();
if (con != null)
con.close();
}
return maps;

}
public static int countMatches(String str, String sub) {
if (str.equals("") || sub.equals("")) {
return 0;
}
int count = 0;
int idx = 0;
while ((idx = str.indexOf(sub, idx)) != -1) {
count++;
idx += sub.length();
}
return count;
}

//yao数据库中的bed表包含id、bedId、roomId、isused四个字段
public static void main(String []agrs){
List list=new ArrayList();
list.add("2");//sql语句中 ?的指定的条件。。。
try {
//Map[] 数组中的每个Map元素存的是查出来的每条数据
Map[] map=ConnectData.executeQuery("select * from bed where roomId=?", list);
System.out.println(map.length);
for(int i=0;i<map.length;i++){
System.out.print(map[i].get("id"));
System.out.print(map[i].get("roomId"));
System.out.print(map[i].get("bedId"));
System.out.println(map[i].get("isused"));
}
} catch (SQLException e) {
e.printStackTrace();
}
}

}