public List<Object> findByUser(final Integer userId,final int first,final int pageSize) {
String sql = " SELECT * FROM (select h.iid as hiid,h.id as hid,h.doc_type_id as qid,h.name as qname,"+
" h.title as htitle, h.author as hauther,to_char(h.eend_time,'YYYY-MM-DD hh24:mi:ss') as heend_time,h.summary as hsummary, "+
" h.dy_company as hdy_company, h.docKind as hdocKind, h.status as hstatus,"+
" h.employee_name|| ':' || h.o_comment as commentss,rownum rw"+
" from (SELECT d.id,d.title,d.author,d.summary,d.doc_type_id,d.status,d.dockind,d.dy_company,"+
" s.id as iid, s.end_time as eend_time, l.employee_name, s.o_comment,q.name FROM documents d "+
" left join rz_review_documents s on s.doc_id = d.id "+
" left join rz_user_login l on s.user_id = l.id"+
" left join doc_types q on q.id = d.doc_type_id"+
" where (s.insteadof_user_id= ? or s.user_id = ?) and s.isauthor=0 order by s.end_time desc ) h )t3 "+
" where t3.rw <=? and t3.rw >?";
List<Object> data = getObjBySql(sql,userId,first,pageSize);
return data;
}
private List<Object> getObjBySql(String docSql,Integer userId,int first,int pageSize) {
Connection conn = null;
PreparedStatement ps = null;
org.hibernate.Session session = null;
ResultSet rs = null;
List<Object> list = new ArrayList<Object>();
try {
session = this.getSession();
conn = session.connection();
ps = conn.prepareStatement(docSql);
ps.setInt(1, userId);
ps.setInt(2, userId);
ps.setInt(3, first+pageSize);
ps.setInt(4, first);
rs = ps.executeQuery();
ResultSetMetaData md = rs.getMetaData();
int columnCount = md.getColumnCount();
// Map rowData;
while (rs.next()) {
// rowData = new HashMap(columnCount);
Map rowData = new HashMap();
for (int i = 1; i <= columnCount; i++) {
rowData.put(md.getColumnName(i), rs.getObject(i));
}
list.add(rowData);
}
return list;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
// session.close();
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
System.out.println("获取数据关闭ResultSet异常");
e.printStackTrace();
} finally {
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
System.out.println("获取数据Statement异常");
e.printStackTrace();
}
finally {
try {
conn.close();
} catch (Exception e) {
System.out.println("获取数据Connection异常");
e.printStackTrace();
}
}
}
}
}
}
return null;
}
String sql = " SELECT * FROM (select h.iid as hiid,h.id as hid,h.doc_type_id as qid,h.name as qname,"+
" h.title as htitle, h.author as hauther,to_char(h.eend_time,'YYYY-MM-DD hh24:mi:ss') as heend_time,h.summary as hsummary, "+
" h.dy_company as hdy_company, h.docKind as hdocKind, h.status as hstatus,"+
" h.employee_name|| ':' || h.o_comment as commentss,rownum rw"+
" from (SELECT d.id,d.title,d.author,d.summary,d.doc_type_id,d.status,d.dockind,d.dy_company,"+
" s.id as iid, s.end_time as eend_time, l.employee_name, s.o_comment,q.name FROM documents d "+
" left join rz_review_documents s on s.doc_id = d.id "+
" left join rz_user_login l on s.user_id = l.id"+
" left join doc_types q on q.id = d.doc_type_id"+
" where (s.insteadof_user_id= ? or s.user_id = ?) and s.isauthor=0 order by s.end_time desc ) h )t3 "+
" where t3.rw <=? and t3.rw >?";
List<Object> data = getObjBySql(sql,userId,first,pageSize);
return data;
}
private List<Object> getObjBySql(String docSql,Integer userId,int first,int pageSize) {
Connection conn = null;
PreparedStatement ps = null;
org.hibernate.Session session = null;
ResultSet rs = null;
List<Object> list = new ArrayList<Object>();
try {
session = this.getSession();
conn = session.connection();
ps = conn.prepareStatement(docSql);
ps.setInt(1, userId);
ps.setInt(2, userId);
ps.setInt(3, first+pageSize);
ps.setInt(4, first);
rs = ps.executeQuery();
ResultSetMetaData md = rs.getMetaData();
int columnCount = md.getColumnCount();
// Map rowData;
while (rs.next()) {
// rowData = new HashMap(columnCount);
Map rowData = new HashMap();
for (int i = 1; i <= columnCount; i++) {
rowData.put(md.getColumnName(i), rs.getObject(i));
}
list.add(rowData);
}
return list;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
// session.close();
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
System.out.println("获取数据关闭ResultSet异常");
e.printStackTrace();
} finally {
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
System.out.println("获取数据Statement异常");
e.printStackTrace();
}
finally {
try {
conn.close();
} catch (Exception e) {
System.out.println("获取数据Connection异常");
e.printStackTrace();
}
}
}
}
}
}
return null;
}