调用jsonArray查询
public static void queryJsonArray(){
// String sql = "select * from user u";
String sql = "select u.*,d.* from user u,depart d where u.depart_id=d.id";
// String sql = "select u.id AS uid,u.name,u.sex,u.depart_id AS departId,d.name from user u,depart d where u.depart_id=d.id";
// String 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";
try {
JSONArray jsonArray = baseImp.queryForJsonArrayAttachTableName(2,2,sql, null);
// JSONArray jsonArray = baseImp.queryForJsonArray(sql, null);
System.out.println(jsonArray.toString());
for (int i = 0; i < jsonArray.size(); i++) {
JSONObject jsonObject = jsonArray.getJSONObject(i);
Iterator<?> iterator = jsonObject.keys();
Object key = null;
while (iterator.hasNext()) {
key = iterator.next();
System.out.print(key+" "+jsonObject.get(key)+" ");
}
System.out.println();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
结果
[{"user.id":"4","user.name":"王五","user.sex":"0","user.depart_id":"3","depart.id":"3","depart.name":"研发部","depart.desc":"这是研发部"},{"user.id":"5","user.name":"赵六","user.sex":"1","user.depart_id":"1","depart.id":"1","depart.name":"测试部","depart.desc":"这是测试部"}]
user.id 4 user.name 王五 user.sex 0 user.depart_id 3 depart.id 3 depart.name 研发部 depart.desc 这是研发部
user.id 5 user.name 赵六 user.sex 1 user.depart_id 1 depart.id 1 depart.name 测试部 depart.desc 这是测试部
3.查询总记录条数
/**
* 查询记录条数
* @param sql 例如："select count(*) from user where xxx"
* @param values
* @throws SQLException
*/
public int queryCount(String sql,Object... values) throws SQLException{
int count = -1;
Connection conn = null;
PreparedStatement pStmt = null;
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]);
}
}
ResultSet rs = pStmt.executeQuery();
if(rs != null){
try {
while(rs.next()){
count = rs.getInt(1);
}
}finally{
if(rs != null){
rs.close();
}
if(pStmt != null){
pStmt.close();
}
if (conn != null) {
this.closeConnection(conn);
}
}
}
return count;
}
调用查询总记录条数
public static void queryCount(){
String sql = "select count(*) from user u";
try {
System.out.println("count="+baseImp.queryCount(sql, null));
} catch (SQLException e) {
e.printStackTrace();
}
}
