package com.liuzhu.oracle.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.liuzhu.oracle.dao.IEmpDAO;
import com.liuzhu.oracle.vo.Emp;
public class EmpDAO implements IEmpDAO
{
private Connection conn = null;
private PreparedStatement psmt = null;
public EmpDAO(Connection conn)
{
this.conn = conn;
}
public boolean doCreate(Emp vo) throws Exception
{
String sql = "insert into emp(empno, ename, job, hiredate, sal, comm) "
+ "values(?,?,?,?,?,?)";
this.psmt = this.conn.prepareStatement(sql);
this.psmt.setInt(1, vo.getEmpno());
this.psmt.setString(2, vo.getEname());
this.psmt.setString(3, vo.getJob());
this.psmt.setDate(4, new java.sql.Date(vo.getHiredate().getTime()));
this.psmt.setDouble(5, vo.getSal());
this.psmt.setDouble(6, vo.getComm());
if(this.psmt.executeUpdate() > 0)
{
return true;
}
return false;
}
@Override
public boolean doRemove(Integer id) throws Exception
{
// TODO Auto-generated method stub
return false;
}
@Override
public boolean doUpdate(Emp vo) throws Exception
{
// TODO Auto-generated method stub
return false;
}
@Override
public List<Emp> findAll(String column, String keyWord, int currentPage,
int lineSize) throws Exception
{
List<Emp> list = new ArrayList<Emp>();
String sql = " select * from ( "+" select empno, ename, job, hiredate, sal, comm, rownum rn "
+ " from emp " + " where " + column + " like ? and rownum<=?) temp "
+ " where temp.rn>? ";
this.psmt = this.conn.prepareStatement(sql);
this.psmt.setString(1, "%"+keyWord+"%");
this.psmt.setInt(2, currentPage*lineSize);
this.psmt.setInt(3, (currentPage-1)*lineSize);
ResultSet set = this.psmt.executeQuery();
while(set.next())
{
Emp emp = new Emp();
emp.setEmpno(set.getInt(1));
emp.setEname(set.getString(2));
emp.setJob(set.getString(3));
emp.setHiredate(set.getDate(4));
emp.setSal(set.getDouble(5));
emp.setComm(set.getDouble(6));
list.add(emp);
}
return list;
}
@Override
public Emp findById(Integer id) throws Exception
{
// TODO Auto-generated method stub
return null;
}
@Override
public Long getAllCount(String column, String keyWord) throws Exception
{
long count = 0;
String sql = " select count(empno) from emp where " + column
+ " like ? ";
this.psmt = this.conn.prepareStatement(sql);
this.psmt.setString(1, "%" + keyWord + "%");
ResultSet set = this.psmt.executeQuery();
while(set.next())
{
count = set.getLong(1);
}
return count;
}
}