java之对Oracle当中的emp表实现增删改查以及对存储过程存储函数的调用

项目结构:

实现数据库连接:

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("数据库连接成功");
		}
	}
}

定义员工对象:

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接口定义

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;

        public int deleteAllEmp(Integer[] items) throws Exception;

}

对增删改查接口的实现

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;
	}

	/**
	 * 通过员工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;
	}

        /* (non-Javadoc)

	 * @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;
	}
	
}

测试用例类

package com.soft.test;
 
import static org.junit.Assert.*;
 
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
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.db.DBConnection;
import com.soft.entity.EmpEnitity;
 
import junit.framework.TestCase;
import oracle.jdbc.driver.OracleTypes;
import oracle.jdbc.oracore.OracleType;
/**
 * 
 * @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("用例测试结束");
	}
	@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();
		}
	}
	/**
	 * 用于实现对存储过程对象进行调用操作
	 * 在数据库当中所创建的存储过程为:
	create or replace procedure queryInformation(i_empno in number,
                                             o_ename out varchar2,
                                             o_esal out number,
                                             o_ejob out varchar2
		)
		as
		begin
		  --过去指定员工对象当中的名字，薪水和职位
		  select ename,sal,job into o_ename,o_esal,o_ejob from emp where empno=i_empno;
		end;
	 */
	@Test
	public void testProcedure()
	{
		DBConnection dbConnection=null;
		try
		{
			String sql="call queryInformation(?,?,?,?)";
			dbConnection=new DBConnection();
			Connection connection=dbConnection.getConnection();
			CallableStatement statement=connection.prepareCall(sql);//实现对存储过程的调用操作
//			in：对于存储过程对象当中输入参数值对象直接进行赋值操作.此处实现了对职工编号为7369的员工对象进行查询操作。
			statement.setInt(1,7369);//存储过程当中的第一个参数为输入参数后三个为输出参数值
//			out：对于存储过程当中的输出操作之对象要事先进行声明操作,进行输出参数和类型的注册操作
			statement.registerOutParameter(2, OracleTypes.VARCHAR);
			statement.registerOutParameter(3, OracleTypes.NUMBER);
			statement.registerOutParameter(4, OracleTypes.VARCHAR);
			statement.execute();//对存储过程进行调用执行操作
//			对存储过程当中执行完毕之后的输出结果进行取出操作
			String ename=statement.getString(2);
			double sal=statement.getDouble(3);
			String job=statement.getString(4);
			System.out.println("姓名:"+ename+",薪水:"+sal+",职位："+job);
		}
		catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("进行存储过程的调用失败");
		}
		finally
		{
			dbConnection.closeConnection();//对数据库连接进行关闭操作
		}
	}
	/*
	 * 实现对存储函数对象的调用操作
	数据库当中进行存储函数创建的SQL
	create or replace function queryFunction(i_empno in number)
	return number--进行函数对象返回值类型的定义操作
	as
	--进行函数变量的定义操作
	 psal emp.sal%type;--定义员工工资
	 pcomm emp.comm%type;--定义员工的奖金变量
	begin
	 select sal,comm into psal,pcomm from emp where empno=i_empno;
	 return psal*12+nvl(pcomm,0);--月薪*12+奖金=年薪
	end;
	*/
	@Test
	public void testFunction()
	{
		DBConnection dbConnection=null;
		try
		{
//	第一个？代表函数对象的返回值，用于实现对函数的返回结果进行存放，第二个?为向函数当中进行传入的参数值
			String sql="{?=call queryFunction(?)}";
			dbConnection=new DBConnection();
			Connection connection=dbConnection.getConnection();
			CallableStatement statement=connection.prepareCall(sql);//实现对存储过程的调用操作
//	对于存储函数当中的输入参数要进行赋值操作,即实现对指定员工编号的年薪收入进行查询操作
			statement.setInt(2,7369);
//	对于存储函数当中的输出参数要进行声明操作
			statement.registerOutParameter(1, OracleTypes.NUMBER);
			statement.execute();//对存储过程进行调用执行操作
//			对存储过程当中执行完毕之后的输出结果进行取出操作
			Double annualSqlary=statement.getDouble(1);
			System.out.println("编号为7369的员工对象的年薪为:"+annualSqlary);
		}
		catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("进行存储函数的调用失败");
		}
		finally
		{
			dbConnection.closeConnection();//对数据库连接进行关闭操作
		}
	}
}