项目结构:
实现数据库连接:
[java]
- package com.soft.db;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.SQLException;
- /*
- * 进行Oracle数据库对象的连接操作
- */
- public class DBConnection {
- private String username="scott";
- private String password="admin";
- private String drive="oracle.jdbc.OracleDriver";
- private String url="jdbc:oracle:thin:@localhost:1521:orcl";
- private Connection connection;
- public DBConnection(){
- // TODO Auto-generated constructor stub
- try {
- Class.forName(drive);//根据drive类的地址来反向的对驱动类进行加载操作
- connection=DriverManager.getConnection(url, username, password);
- } catch (ClassNotFoundException | SQLException e) {
- System.out.println("数据库连接失败");
- e.printStackTrace();
- }
- }
- public Connection getConnection()
- {
- return this.connection;
- }
- public void closeConnection()
- {
- try {
- connection.close();
- } catch (SQLException e) {
- System.out.println("数据库连接关闭失败");
- e.printStackTrace();
- }
- }
- public static void main(String[] args) throws Exception{
- DBConnection dbConnection=new DBConnection();
- Connection connection=dbConnection.getConnection();
- if(connection!=null)
- {
- System.out.println("数据库连接成功");
- }
- }
- }
定义员工对象:
[java]
- package com.soft.entity;
- import java.math.BigDecimal;
- import java.sql.Date;
- public class EmpEnitity {
- private Integer empno;
- private String ename;
- private String job;
- private Integer mgr;
- private Date hiredate;
- private BigDecimal sal;
- private BigDecimal comm;
- private Integer deptno;
- public EmpEnitity() {
- // TODO Auto-generated constructor stub
- }
- public EmpEnitity(Integer empno, String ename, String job, Integer mgr,
- Date hiredate, BigDecimal sal, BigDecimal comm, Integer deptno)
- {
- super();
- this.empno = empno;
- this.ename = ename;
- this.job = job;
- this.mgr = mgr;
- this.hiredate = hiredate;
- this.sal = sal;
- this.comm = comm;
- this.deptno = deptno;
- }
- /**
- * @return the empno
- */
- public Integer getEmpno() {
- return empno;
- }
- /**
- * @param empno the empno to set
- */
- public void setEmpno(Integer empno) {
- this.empno = empno;
- }
- /**
- * @return the ename
- */
- public String getEname() {
- return ename;
- }
- /**
- * @param ename the ename to set
- */
- public void setEname(String ename) {
- this.ename = ename;
- }
- /**
- * @return the job
- */
- public String getJob() {
- return job;
- }
- /**
- * @param job the job to set
- */
- public void setJob(String job) {
- this.job = job;
- }
- /**
- * @return the mgr
- */
- public Integer getMgr() {
- return mgr;
- }
- /**
- * @param mgr the mgr to set
- */
- public void setMgr(Integer mgr) {
- this.mgr = mgr;
- }
- /**
- * @return the hiredate
- */
- public Date getHiredate() {
- return hiredate;
- }
- /**
- * @param hiredate the hiredate to set
- */
- public void setHiredate(Date hiredate) {
- this.hiredate = hiredate;
- }
- /**
- * @return the sal
- */
- public BigDecimal getSal() {
- return sal;
- }
- /**
- * @param sal the sal to set
- */
- public void setSal(BigDecimal sal) {
- this.sal = sal;
- }
- /**
- * @return the comm
- */
- public BigDecimal getComm() {
- return comm;
- }
- /**
- * @param comm the comm to set
- */
- public void setComm(BigDecimal comm) {
- this.comm = comm;
- }
- /**
- * @return the deptno
- */
- public Integer getDeptno() {
- return deptno;
- }
- /**
- * @param deptno the deptno to set
- */
- public void setDeptno(Integer deptno) {
- this.deptno = deptno;
- }
- /* (non-Javadoc)
- * @see java.lang.Object#toString()
- */
- @Override
- public String toString() {
- return "EmpEnitity [empno=" + empno + ", ename=" + ename + ", job="
- + job + ", mgr=" + mgr + ", hiredate=" + hiredate + ", sal="
- + sal + ", comm=" + comm + ", deptno=" + deptno + "]";
- }
- }
Dao接口定义
[java]
- package com.soft.dao;
- import java.util.List;
- import com.soft.entity.EmpEnitity;
- public interface EmpDao {
- public boolean saveEmp(EmpEnitity empEnitity) throws Exception;
- public boolean deleteEmp(Integer empno) throws Exception;
- public boolean updateEmp(EmpEnitity empEnitity) throws Exception;
- public List<EmpEnitity> queryAllEmp() throws Exception;
- public EmpEnitity queryEmpById(Integer empno) throws Exception;
- // 对指定部门编号当中的所有员工对象进行查询操作
- public List<EmpEnitity> queryEmpByDeptno(Integer deptno) throws Exception;
[java]
- public int deleteAllEmp(Integer[] items) throws Exception;
[java]
- }
对增删改查接口的实现
[java]
- package com.soft.daoImpl;
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.util.ArrayList;
- import java.util.List;
- import com.soft.dao.EmpDao;
- import com.soft.db.DBConnection;
- import com.soft.entity.EmpEnitity;
- public class EmpDaoImpl implements EmpDao{
- /* (non-Javadoc)
- * @see com.soft.dao.EmpDao#saveEmp(com.soft.entity.EmpEnitity)
- * 将指定的员工对象插入到数据库当中
- */
- @Override
- public boolean saveEmp(EmpEnitity empEnitity) throws Exception {
- DBConnection dbConnection=new DBConnection();
- Connection connection=dbConnection.getConnection();
- String sql;
- sql="insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(?,?,?,?,?,?,?,?)";
- PreparedStatement preparedStatement=connection.prepareStatement(sql);
- preparedStatement.setInt(1,empEnitity.getEmpno());
- preparedStatement.setString(2, empEnitity.getEname());
- preparedStatement.setString(3, empEnitity.getJob());
- preparedStatement.setInt(4, empEnitity.getMgr());
- preparedStatement.setDate(5, empEnitity.getHiredate());
- preparedStatement.setBigDecimal(6, empEnitity.getSal());
- preparedStatement.setBigDecimal(7, empEnitity.getComm());
- preparedStatement.setInt(8, empEnitity.getDeptno());
- ResultSet resultSet=preparedStatement.executeQuery();
- dbConnection.closeConnection();
- return false;
- }
- /* (non-Javadoc)
- * @see com.soft.dao.EmpDao#deleteEmp(java.lang.Integer)
- * 根据职工编号来对指定的职工对象当中的数据进行删除操作
- */
- @Override
- public boolean deleteEmp(Integer empno) throws Exception {
- DBConnection dbConnection=new DBConnection();
- Connection connection=dbConnection.getConnection();
- String sql=null;
- sql="delete from emp where emp.empno=?";
- PreparedStatement preparedStatement=connection.prepareStatement(sql);
- preparedStatement.setInt(1,empno);
- boolean flag=preparedStatement.execute();
- dbConnection.closeConnection();
- return flag;
- }
[java]
- /**
- * 通过员工ID来对多个员工对象进行删除操作
- * @throws Exception
- * 其中整形常量的值分别为:
- SUCCESS_NO_INFO -2 执行批处理操作成功但是该操作受影响的行数是未知的
- EXECUTE_FAILED -3
- */
- @Override
- public int deleteAllEmp(Integer[] items) throws Exception{
- DBConnection dbConnection=new DBConnection();
- Connection connection=dbConnection.getConnection();
- int[] s=new int[0];
- try {
- connection.setAutoCommit(false);//取消Connection连接对象当中的自动提交事务的设置
- String sql=null;
- sql="delete from emp where emp.empno=?";
- PreparedStatement preparedStatement=connection.prepareStatement(sql);
- for (Integer integer : items) {
- preparedStatement.setInt(1,integer.intValue());
- // 将当前要进行执行的SQL语句添加到批处理对象当中
- preparedStatement.addBatch();
- }
- // 进行批处理的执行操作
- s=preparedStatement.executeBatch();
- System.out.println("s="+s[0]);
- // 进行事务的提交
- connection.commit();
- } catch (Exception e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- // 进行事务的回滚操作
- connection.rollback();
- }
- return s.length;
- }
[java]
- /* (non-Javadoc)
[java]
- * @see com.soft.dao.EmpDao#updateEmp(com.soft.entity.EmpEnitity)
- */
- @Override
- public boolean updateEmp(EmpEnitity empEnitity) throws Exception {
- DBConnection dbConnection=new DBConnection();
- Connection connection=dbConnection.getConnection();
- String sql=null;
- sql="update emp set ename=?,job=?,mgr=?,hiredate=?,sal=?,comm=?,deptno=? where empno=?";
- PreparedStatement preparedStatement=connection.prepareStatement(sql);
- preparedStatement.setString(1, empEnitity.getEname());
- preparedStatement.setString(2, empEnitity.getJob());
- preparedStatement.setInt(3, empEnitity.getMgr());
- preparedStatement.setDate(4, empEnitity.getHiredate());
- preparedStatement.setBigDecimal(5, empEnitity.getSal());
- preparedStatement.setBigDecimal(6, empEnitity.getComm());
- preparedStatement.setInt(7, empEnitity.getDeptno());
- preparedStatement.setInt(8,empEnitity.getEmpno());
- boolean flag=preparedStatement.execute();
- dbConnection.closeConnection();
- return flag;
- }
- /* (non-Javadoc)
- * @see com.soft.dao.EmpDao#queryAllEmp()
- */
- @Override
- public List<EmpEnitity> queryAllEmp() throws Exception {
- List<EmpEnitity> list=new ArrayList<>();
- EmpEnitity empEnitity;
- DBConnection dbConnection=new DBConnection();
- Connection connection=dbConnection.getConnection();
- String sql=null;
- sql="select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp";
- PreparedStatement preparedStatement=connection.prepareStatement(sql);
- ResultSet resultSet=preparedStatement.executeQuery();
- while(resultSet.next())
- {
- empEnitity=new EmpEnitity();
- empEnitity.setEmpno(resultSet.getInt(1));
- empEnitity.setEname(resultSet.getString(2));
- empEnitity.setJob(resultSet.getString(3));
- empEnitity.setMgr(resultSet.getInt(4));
- empEnitity.setHiredate(resultSet.getDate(5));
- empEnitity.setSal(resultSet.getBigDecimal(6));
- empEnitity.setComm(resultSet.getBigDecimal(7));
- empEnitity.setDeptno(resultSet.getInt(8));
- list.add(empEnitity);
- }
- dbConnection.closeConnection();
- return list;
- }
- /* (non-Javadoc)
- * @see com.soft.dao.EmpDao#queryEmpById(java.lang.Integer)
- */
- @Override
- public EmpEnitity queryEmpById(Integer empno) throws Exception {
- DBConnection dbConnection=new DBConnection();
- Connection connection=dbConnection.getConnection();
- EmpEnitity empEnitity = null;
- String sql=null;
- sql="select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where emp.empno=?";
- PreparedStatement preparedStatement=connection.prepareStatement(sql);
- preparedStatement.setInt(1, empno);
- ResultSet resultSet=preparedStatement.executeQuery();
- if(resultSet.next())
- {
- empEnitity=new EmpEnitity();
- empEnitity.setEmpno(resultSet.getInt(1));
- empEnitity.setEname(resultSet.getString(2));
- empEnitity.setJob(resultSet.getString(3));
- empEnitity.setMgr(resultSet.getInt(4));
- empEnitity.setHiredate(resultSet.getDate(5));
- empEnitity.setSal(resultSet.getBigDecimal(6));
- empEnitity.setComm(resultSet.getBigDecimal(7));
- empEnitity.setDeptno(resultSet.getInt(8));
- }
- dbConnection.closeConnection();
- return empEnitity;
- }
- /* (non-Javadoc)
- * @see com.soft.dao.EmpDao#queryEmpByDeptno(java.lang.Integer)
- * 根据部门编号来查询指定部门当中的所有的员工信息
- */
- @Override
- public List<EmpEnitity> queryEmpByDeptno(Integer deptno) throws Exception {
- DBConnection dbConnection=new DBConnection();
- Connection connection=dbConnection.getConnection();
- List<EmpEnitity> list=new ArrayList<>();
- EmpEnitity empEnitity;
- String sql=null;
- sql="select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where emp.deptno=?";
- PreparedStatement preparedStatement=connection.prepareStatement(sql);
- preparedStatement.setInt(1, deptno);
- ResultSet resultSet=preparedStatement.executeQuery();
- while(resultSet.next())
- {
- empEnitity=new EmpEnitity();
- empEnitity.setEmpno(resultSet.getInt(1));
- empEnitity.setEname(resultSet.getString(2));
- empEnitity.setJob(resultSet.getString(3));
- empEnitity.setMgr(resultSet.getInt(4));
- empEnitity.setHiredate(resultSet.getDate(5));
- empEnitity.setSal(resultSet.getBigDecimal(6));
- empEnitity.setComm(resultSet.getBigDecimal(7));
- empEnitity.setDeptno(resultSet.getInt(8));
- list.add(empEnitity);
- }
- dbConnection.closeConnection();
- return list;
- }
测试用例类
[java]
- package com.soft.test;
- import static org.junit.Assert.*;
- import java.math.BigDecimal;
- import java.sql.Date;
- import java.util.ArrayList;
- import java.util.List;
- import org.junit.After;
- import org.junit.Before;
- import org.junit.Test;
- import com.soft.daoImpl.EmpDaoImpl;
- import com.soft.entity.EmpEnitity;
- import junit.framework.TestCase;
- /**
- *
- * @author Administrator
- *进行用例测试的时候,当要执行某一个单独的测试方法的时候,双击方法名然后进行Junit的执行即可
- */
- public class EmpDaoImplTest{
- EmpDaoImpl empDaoImpl;
- @Before
- public void init()
- {
- System.out.println("进行初始化操作");
- empDaoImpl=new EmpDaoImpl();
- }
- @Test
- public void saveEmpTest()
- {
- EmpEnitity empEntity;
- System.out.println("对saveEmp方法进行测试");
- boolean flag=false;
- empEntity=new EmpEnitity
- (
- 7943,"小清","ANALYST", 7698,Date.valueOf("2018-06-23") ,new BigDecimal(7894.05),new BigDecimal(1200),20
- );
- try
- {
- flag=empDaoImpl.saveEmp(empEntity);
- } catch (Exception e)
- {
- System.out.println("职工信息添加失败");
- e.printStackTrace();
- }
- assertTrue("员工信息存放成功", flag==true);
- }
- @Test
- public void deleteEmpTest()
- {
- System.out.println("对deleteEmp方法进行测试");
- boolean flag=false;
- try {
- flag=empDaoImpl.deleteEmp(7943);
- } catch (Exception e) {
- System.out.println("对指定员工对象当中的信息进行删除失败");
- e.printStackTrace();
- }
- }
- @Test
- public void queryAllEmpTest()
- {
- System.out.println("对queryAllEmp方法进行测试");
- List<EmpEnitity> empEnitities=new ArrayList<>();
- try {
- empEnitities=empDaoImpl.queryAllEmp();
- } catch (Exception e) {
- System.out.println("对全体员工对象当中的信息进行查询失败");
- e.printStackTrace();
- }
- for (EmpEnitity empEnitity : empEnitities) {
- System.out.println(empEnitity);
- }
- }
- @Test
- public void queryEmpByIdTest()
- {
- EmpEnitity empEnitity=null;
- System.out.println("对queryEmpById方法进行测试");
- try {
- empEnitity=empDaoImpl.queryEmpById(7499);
- } catch (Exception e) {
- System.out.println("根据员工编号来对员工信息进行查询失败");
- e.printStackTrace();
- }
- System.out.println(empEnitity);
- }
- @Test
- public void queryEmpByDeptnoTest()
- {
- System.out.println("对queryEmpByDeptno方法进行测试");
- List<EmpEnitity> empEnitities=new ArrayList<>();
- try {
- empEnitities=empDaoImpl.queryEmpByDeptno(20);
- } catch (Exception e) {
- System.out.println("对指定部门当中的全体员工对象当中的信息进行查询失败");
- e.printStackTrace();
- }
- for (EmpEnitity empEnitity : empEnitities) {
- System.out.println(empEnitity);
- }
- }
- /**
- * 在对某一对象进行修改之前必须要先查询到该对象
- */
- @Test
- public void updateEmpTest()
- {
- try {
- EmpEnitity empEnitity=empDaoImpl.queryEmpById(1008);
- empEnitity.setEname("周小清");
- boolean flag=empDaoImpl.updateEmp(empEnitity);
- } catch (Exception e) {
- System.out.println("对指定编号的职工对象进行修改失败");
- e.printStackTrace();
- }
- }
- @After
- public void finash()
- {
- System.out.println("用例测试结束");
- }
[html]
@Test
- public void deleteAllTest()
- {
- System.out.println("对deleteAllTest方法进行测试");
- Integer items[]={new Integer(7943),new Integer(7935)};
- try {
- empDaoImpl.deleteAllEmp(items);
- } catch (Exception e) {
- System.out.println("根据学号对多个职工对象进行删除操作失败");
- e.printStackTrace();
- }
- }
}
登录 | 立即注册