import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;
/**
* @$comment 查询 (较高频率的执行拼接sql,会降低数据并发性能)
* @param sql -- 拼接sql 如: select * from test where id='1111111'
* @return List<HashMap>
*/
public static List<HashMap<String, Object>> getListSql(Connection conn,String sql) throws SQLException {
List<HashMap<String, Object>> list=new java.util.ArrayList<HashMap<String, Object>>();
Statement stmt=null;
ResultSet rs=null;
ResultSetMetaData rsmd=null;
int cols;
try{
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
rsmd=rs.getMetaData();
cols=rsmd.getColumnCount();
while(rs.next()){
HashMap<String,Object> hm=new HashMap<String,Object>();
for(int j=1;j<=cols;j++){
hm.put((rsmd.getColumnName(j)).toLowerCase(),rs.getString(j));
}
list.add(hm);
}
if(rs!=null) rs.close();
if(stmt!=null) stmt.close();
if(conn!=null) conn.close();
}catch(Exception e){
try{
if(rs!=null) rs.close();
if(stmt!=null) stmt.close();
if(conn!=null) conn.close();
}catch(Exception e1){
}
throw new SQLException(e);
}
return list;
}
/**
* @$comment 查询 (对于简单的查询语句,推荐使用此方式执行sql查询)
* @param sql -- 占位符sql 如: select * from test where id=?
* @param objs ---new Object[]{"123456"}
* @return List<HashMap>
*/
public static List<HashMap<String, Object>> getListSql(Connection conn,String sql,Object[] objs) throws SQLException {
List<HashMap<String, Object>> list=new java.util.ArrayList<HashMap<String, Object>>();
PreparedStatement pst = null;
ResultSet rs=null;
ResultSetMetaData rsmd=null;
int cols;
try{
pst=conn.prepareStatement(sql);
for(int i=0;i<objs.length;i++) {
initValue( objs[i], pst,i+1);
}
rs=pst.executeQuery();
rsmd=rs.getMetaData();
cols=rsmd.getColumnCount();
while(rs.next()){
HashMap<String,Object> hm=new HashMap<String,Object>();
for(int j=1;j<=cols;j++){
hm.put((rsmd.getColumnName(j)).toLowerCase(),rs.getString(j));
}
list.add(hm);
}
if(rs!=null) rs.close();
if(pst!=null) pst.close();
if(conn!=null) conn.close();
}catch(Exception e){
try{
if(rs!=null) rs.close();
if(pst!=null) pst.close();
if(conn!=null) conn.close();
}catch(Exception e1){
}
throw new SQLException(e);
}
return list;
}
/**
* 初始化参数
* @throws NumberFormatException
* @throws SQLException
*/
public int initValue(Object v,PreparedStatement pst,int i) throws NumberFormatException, SQLException {
if(v==null) {
pst.setObject(i, null);
}else if(v instanceof String) {
pst.setString(i, v.toString());
}else if(v instanceof Long) {
pst.setLong(i, Long.valueOf(v.toString()));
}else if(v instanceof Short) {
pst.setShort(i, Short.valueOf(v.toString()));
}else if(v instanceof Boolean) {
pst.setBoolean(i, Boolean.valueOf(v.toString()));
}else if(v instanceof Double) {
pst.setDouble(i, Double.valueOf(v.toString()));
}else if(v instanceof Float) {
pst.setFloat(i, Float.valueOf(v.toString()));
}else {
pst.setObject(i, v);
}
return 1;
}
注意:有变量参数的sql语句,应该使用占位符(?)或者绑定变量(:id) 的sql形式执行,没有特殊要求或情况应禁止使用拼接sql,可防止数据库资源消耗过大等