查询操作
1.查询结果以list返回
/**
* 查询结果以list返回
* @param pageIndex 页数
* @param pageSize 每页记录条数
* @param attachTableName 在结果集中是否给key值附加表名，例如：user.id,与id
* @param sql 查询语句 例如："select u.id,u.name,u.sex,u.depart_id AS departId,d.* from user u,depart d where u.depart_id=d.id"
* @param values
* @throws SQLException
*/
private List<Map<String, Object>> queryList(int pageIndex,int pageSize,boolean attachTableName,String sql,Object... values) throws SQLException{
Connection conn = null;
PreparedStatement pStmt = null;
List<Map<String, Object>> dataList = null;
//校验参数
if(pageIndex <= 0){
pageIndex = 1;
}
if(pageSize <= 0){
pageSize = Config.PAGE_SIZE_DEFAULT;
}
conn = this.getConnection(conn);
pStmt = conn.prepareStatement(sql);
//设置参数
if(pStmt != null && values != null && values.length > 0){
for (int i = 0; i < values.length; i++) {
pStmt.setObject(i+1, values[i]);
}
}
//设置最大查询到第几条记录
pStmt.setMaxRows(pageIndex*pageSize);
ResultSet rs = pStmt.executeQuery();
//游标移动到要输出的第一条记录
rs.relative((pageIndex-1)*pageSize);
if(rs != null){
try {
dataList = new ArrayList<Map<String,Object>>();
ResultSetMetaData md = rs.getMetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等
//遍历结果集
while(rs.next()){
Map<String, Object> map = new LinkedHashMap();
for (int i = 1; i <= md.getColumnCount(); i++) {
map.put(attachTableName?(formatTableName(md.getTableName(i))+"."+md.getColumnLabel(i)):(md.getColumnLabel(i)), rs.getObject(i));
}
dataList.add(map);
}
}finally{
if(rs != null){
rs.close();
}
if(pStmt != null){
pStmt.close();
}
if (conn != null) {
this.closeConnection(conn);
}
}
}
return dataList;
}
调用list查询
public static void queryList(){
String sql = "select u.*,d.* from user u,depart d where u.depart_id=d.id";
try {
List<Map<String, Object>> dataList = baseImp.queryForListAttachTableName(2,2,sql, null);
// List<Map<String, Object>> dataList = baseImp.queryForList(2,2,sql, null);
for (Map<String, Object> map : dataList) {
for (String key : map.keySet()) {
System.out.print(key+"="+map.get(key)+" ");
}
System.out.println();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
结果
2.查询结果以jsonArray返回
/**
* 查询结果以ArrayList返回
* @param 在结果集中是否给key值附加表名，例如：user.id,与id
* @param sql 查询语句 例如："select u.id,u.name,u.sex,u.depart_id AS departId,d.* from user u,depart d where u.depart_id=d.id"
* @param values
* @throws SQLException
*/
private JSONArray queryJsonArray(int pageIndex,int pageSize,boolean attachTableName,String sql,Object... values) throws SQLException{
JSONArray jsonArray = null;
Connection conn = null;
PreparedStatement pStmt = null;
//校验参数
if(pageIndex <= 0){
pageIndex = 1;
}
if(pageSize <= 0){
pageSize = Config.PAGE_SIZE_DEFAULT;
}
conn = this.getConnection(conn);
pStmt = conn.prepareStatement(sql);
//设置参数
if(pStmt != null && values != null && values.length > 0){
for (int i = 0; i < values.length; i++) {
pStmt.setObject(i+1, values[i]);
}
}
//设置最大查询到第几条记录
pStmt.setMaxRows(pageIndex*pageSize);
ResultSet rs = pStmt.executeQuery();
//游标移动到要输出的第一条记录
rs.relative((pageIndex-1)*pageSize);
if(rs != null){
try {
jsonArray = new JSONArray();
ResultSetMetaData md = rs.getMetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等
//遍历结果集
while(rs.next()){
JSONObject jsonObject = new JSONObject();
for (int i = 1; i <= md.getColumnCount(); i++) {
jsonObject.put(attachTableName?(formatTableName(md.getTableName(i))+"."+md.getColumnLabel(i)):(md.getColumnLabel(i)), rs.getObject(i)+"");
}
jsonArray.add(jsonObject);
}
}finally{
if(rs != null){
rs.close();
}
if(pStmt != null){
pStmt.close();
}
if (conn != null) {
this.closeConnection(conn);
}
}
}
return jsonArray;
}
