commdao数据库和java_对Java DAO模式的解析

package dao;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import org.apache.commons.logging.LogFactory;

import util.JDBCUtil;

import bean.EMP;

//实现接口

public class EMPDAOIm implements IEMPDAO{

Connection conn=JDBCUtil.getConnection();//获得与数据库的连接

private static org.apache.commons.logging.Log log =

LogFactory

.getLog(EMPDAOIm.class);//实现日志跟踪EMPDAOIm类

Statement stmt;

//往EMP表插入与员工信息

public void addEMP(EMP emp) {

log.info("插入EMP表");

// TODO Auto-generated method stub

String sql="insert into

EMP(empno,ename,job,mgr,sal,comm,deptno) values("

+emp.getEmpno()+",'"+emp.getEname()

+"','"+emp.getJob()+"',"+emp.getMgr()

+","+emp.getSal()+","+emp.getComm()+","+emp.getDeptno()+")";

log.debug(sql);

try {

stmt=conn.createStatement();

stmt.executeQuery(sql);

log.info("插入成功");

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

log.info("插入失败");

}

}

//删除某个员工

public void deleteEMP(EMP emp) {

log.info("删除某个员工");

String sql="delete from EMP where empno=

"+emp.getEmpno();

log.debug(sql);

try {

stmt.executeQuery(sql);

log.info("删除"+emp.getEmpno()+"成功");

} catch (SQLException e) {

// TODO: handle exception

e.printStackTrace();

log.info("删除失败");

}

}

//查询EMP表所有的员工信息

public List findAll()

{

// TODO Auto-generated method stub

log.info("查询所有EMP");

EMP emp;

List list=new

ArrayList();

String sql="select empno,ename,job,mgr,hiredate,sal,comm,deptno

from EMP ";//组装sql语句

log.debug(sql);

try {

Statement stmt=conn.createStatement();

ResultSet rs=stmt.executeQuery(sql);

while(rs.next()){

emp=new EMP();

//把查询的数据再次先组装成EMP

emp.setEmpno(rs.getInt("empno"));

emp.setEname(rs.getString("ename"));

emp.setJob(rs.getString("job"));

emp.setMgr((int) rs.getLong("mgr"));

emp.setHiredate((Date)rs.getTimestamp("hiredate"));

emp.setSal(rs.getDouble("sal"));

emp.setComm(rs.getDouble("comm"));

emp.setDeptno((int) rs.getLong("deptno"));

list.add(emp);

}

log.info("查询成功，输出结果");

} catch (SQLException e) {

// TODO: handle exception

e.printStackTrace();

log.info("查询失败");

}

return list;

}

//按某个员工id查询员工信息

public EMP findById(int i) {

// TODO Auto-generated method stub

EMP emp=new EMP();

log.info("按Id查询员工信息");

String sql="select empno,ename,job,mgr,hiredate,sal,comm,deptno

from EMP where empno= "+i;

log.debug(sql);

try {

ResultSet rs=stmt.executeQuery(sql);

log.info("查询成功，结果如下：");

while(rs.next()){

emp.setEmpno(rs.getInt("empno"));

emp.setEname(rs.getString("ename"));

emp.setJob(rs.getString("job"));

emp.setMgr((int) rs.getLong("mgr"));

emp.setHiredate(rs.getTimestamp("hiredate"));

emp.setSal(rs.getDouble("sal"));

emp.setComm(rs.getDouble("comm"));

emp.setDeptno((int) rs.getLong("deptno"));

}

} catch (SQLException e) {

// TODO: handle exception

e.printStackTrace();

log.info("查询失败");

}

return emp;

}

//更新某个员工信息

public void updateEMP(EMP emp) {

// TODO Auto-generated method stub

log.info("更新某个员工的某项信息");

String sql="update EMP set sal = sal + 300 where empno =

"+emp.getEmpno();

log.debug(sql);

try {

stmt.executeQuery(sql);

log.info("更新成功");

} catch (SQLException e) {

// TODO: handle exception

e.printStackTrace();

log.info("更新失败");

}

}

}