JDBC动态参数,动态sql示例

/**
* 图书借阅
* 
* @param xh
*            学号
* @param bookName
*            图书名称
* @param bookStatus
*            图书状态
* @return
*/
public List<Map<String, Object>> tsjy(String xh, String bookName, String bookStatus) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
List<Map<String, Object>> map = null;
try {
connection = JdbcUtils.getConnection();
StringBuffer sql = new StringBuffer();
sql.append("select wid,sfrzh,tsmc,jsrq,yhrq,xhrq,jhbz from USR_DATAI.T_TS_JY ");
List<Object> params = new ArrayList<Object>();
if (StringUtil.isNotEmpty(xh)) {
sql.append("where SFRZH = ? ");
params.add(xh);
}
if (StringUtil.isNotEmpty(bookName)) {
sql.append("and TSMC like ? ");
params.add("%" + bookName + "%");
}
if (StringUtil.isNotEmpty(bookStatus)) {
sql.append("and JHBZ = ? ");
params.add(bookStatus);
}
sql.append("order by jsrq desc");
log.info("图书借阅sql语句:{}", sql.toString());
preparedStatement = connection.prepareStatement(sql.toString());
setParameter(params, preparedStatement);
resultSet = preparedStatement.executeQuery();
map = JdbcUtils.rsToMap(resultSet);
} catch (Exception e) {
} finally {
JdbcUtils.releaseDB(connection, preparedStatement, resultSet);
}


return map;

}

public void setParameter(List<Object> params, PreparedStatement preparedStatement) throws SQLException {
for (int i = 0; i < params.size(); i++) {
Object p = params.get(i);
if (p instanceof Integer) {
preparedStatement.setInt(i + 1, (Integer) p);
} else if (p instanceof String) {
preparedStatement.setString(i + 1, (String) p);
}
}
}

public static List<Map<String, Object>> rsToMap(ResultSet rs)
throws SQLException, InstantiationException, IllegalAccessException {


// 结果集 中列的名称和类型的信息
ResultSetMetaData rsm = rs.getMetaData();
int colNumber = rsm.getColumnCount();
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
// 遍历每条记录
while (rs.next()) {
Map<String, Object> map = new HashMap<String, Object>();
// 实例化对象
// 取出每一个字段进行赋值
for (int i = 1; i <= colNumber; i++) {
String key = rsm.getColumnName(i);
Object value = rs.getObject(i);
map.put(key, value);


}
list.add(map);
}
return list;
}

/**
* 返回结果只有一条数据
* @param rs
* @return
* @throws SQLException
* @throws InstantiationException
* @throws IllegalAccessException
*/
public static Map<String,Object> rsToOneMap(ResultSet rs)
throws SQLException, InstantiationException, IllegalAccessException {


// 结果集 中列的名称和类型的信息
ResultSetMetaData rsm = rs.getMetaData();
int colNumber = rsm.getColumnCount();
Map<String, Object> map = new HashMap<String, Object>();
// 遍历每条记录
while (rs.next()) {
// 实例化对象
// 取出每一个字段进行赋值
for (int i = 1; i <= colNumber; i++) {
String key = rsm.getColumnName(i);
Object value = rs.getObject(i);
map.put(key, value);
}
}
return map;
}