javaEE mvc例子详解(二)
}
//查询全部信息
public List findAll(int currentPage, int lineSize, String keyword)
throws Exception {
// TODO Auto-generated method stub
List all=new ArrayList();
PreparedStatement pstmt=null;
String sql=null;
if(keyword==null||"".equals(keyword)){
sql="SELECT TOP "+lineSize+" empno,ename,job,hiredate,sal,comm,photo FROM " +
"(SELECT TOP "+lineSize+" empno,ename,job,hiredate,sal,comm,photo FROM " +
"(SELECT TOP "+currentPage*lineSize+" * FROM emp1 ORDER BY empno ASC)temptlb1 ORDER BY empno DESC)temptlb2 ORDER BY empno asc";
}else{
sql="SELECT empno,ename,job,hiredate,sal,comm,photo FROM emp1 WHERE empno LIKE '%"+keyword+"%' " +
"OR ename LIKE '%"+keyword+"%' OR job LIKE '%"+keyword+"%' OR hiredate LIKE '%"+keyword+"%'" +
" OR sal LIKE '%"+keyword+"%' OR comm LIKE '%"+keyword+"%' ";
}
try{
pstmt=this.conn.prepareStatement(sql);
// pstmt.setString(1, "%"+keyword+"%");
// pstmt.setString(2, "%"+keyword+"%");
// pstmt.setString(3, "%"+keyword+"%");
// pstmt.setString(4, "%"+keyword+"%");
// pstmt.setString(5, "%"+keyword+"%");
// pstmt.setString(6, "%"+keyword+"%");
System.out.println(sql);
ResultSet rs=pstmt.executeQuery();
Emp emp=null;
while(rs.next()){
emp=new Emp();
emp.setEmpno(rs.getInt(1));
emp.setEname(rs.getString(2));
emp.setJob(rs.getString(3));
emp.setHiredate(rs.getDate(4));
emp.setSal(rs.getFloat(5));
emp.setComm(rs.getFloat(6));
emp.setPhoto(rs.getString(7));
all.add(emp);
}
}catch(Exception e){
throw e;
}finally{
try{
pstmt.close();
}
catch(Exception e){
throw e;
}
}
return all;
}
//按ID查询
public Emp findEmpById(int empno) throws Exception {
// TODO Auto-generated method stub
Emp emp=null;
PreparedStatement pstmt=null;
try{
String sql="SELECT empno,ename,job,hiredate,sal,comm,photo FROM emp1 WHERE empno= ";
pstmt=this.conn.prepareStatement(sql);
pstmt.setInt(1, empno);
ResultSet rs=pstmt.executeQuery();
if(rs.next()){
emp=new Emp();
emp.setEmpno(rs.getInt(1));
emp.setEname(rs.getString(2));
emp.setJob(rs.getString(3));
emp.setHiredate(rs.getDate(4));
emp.setSal(rs.getFloat(5));
emp.setComm(rs.getFloat(6));
emp.setPhoto(rs.getString(7));
}
}catch(Exception e){
throw e;
}finally{
try{
pstmt.close();
}catch(Exception e){
throw e;
}
}
return emp;
}
//查询数据表里 记录集
public int getAllCount(String keyword) throws Exception {
// TODO Auto-generated method stub
int count=0;
PreparedStatement pstmt=null;
try{
String sql="SELECT COUNT(empno) FROM emp1 WHERE empno LIKE OR ename LIKE " +
"OR job LIKE OR hiredate LIKE OR sal LIKE OR comm LIKE OR photo LIKE ";
pstmt=this.conn.prepareStatement(sql);
pstmt.setString(1, "%"+keyword+"%");
pstmt.setString(2, "%"+keyword+"%");
pstmt.setString(3, "%"+keyword+"%");
pstmt.setString(4, "%"+keyword+"%");
pstmt.setString(5, "%"+keyword+"%");
pstmt.setString(6, "%"+keyword+"%");
pstmt.setString(7, "%"+keyword+"%");
ResultSet rs=pstmt.executeQuery();
if(rs.next()){
count=rs.getInt(1); //返回数据表里的所有数据