记学校教学中的一次数据库教学（校内课设使用原生jdbc连接数据库）

最新推荐文章于 2022-04-06 11:13:24 发布

jjdexiao521

最新推荐文章于 2022-04-06 11:13:24 发布

阅读量268

点赞数 1

CC 4.0 BY-SA版权

分类专栏： java

本文链接：https://blog.youkuaiyun.com/jjdexiao521/article/details/87601603

java 专栏收录该内容

17 篇文章

订阅专栏

本文深入解析了Java环境下数据库操作的全过程，从简单的Bean类Employee的定义开始，逐步介绍了数据库工具类DBUtil的使用，以及数据访问层BaseDao的实现。通过具体的EmployeeDao接口及其实现类implEmployeeDaoImpl，详细展示了如何进行数据库的增删改查操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

远古时期-数据库连接查询处理

简单的Bean类代码

简单的Bean类代码

Employee.java

package edu.pt.pojo;

import java.util.Date;

public class Employee {

	private Integer empno;
	private String ename;
	private String job;
	private Integer mgr;
	private Date hiredate;
	private Double sal;
	private Double comm;
	private Integer deptno;

	public Employee() {
	}

	public Integer getEmpno() {
		return empno;
	}

	public void setEmpno(Integer empno) {
		this.empno = empno;
	}

	public String getEname() {
		return ename;
	}

	public void setEname(String ename) {
		this.ename = ename;
	}

	public String getJob() {
		return job;
	}

	public void setJob(String job) {
		this.job = job;
	}

	public Integer getMgr() {
		return mgr;
	}

	public void setMgr(Integer mgr) {
		this.mgr = mgr;
	}

	public Date getHiredate() {
		return hiredate;
	}

	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}

	public Double getSal() {
		return sal;
	}

	public void setSal(Double sal) {
		this.sal = sal;
	}

	public Double getComm() {
		return comm;
	}

	public void setComm(Double comm) {
		this.comm = comm;
	}

	public Integer getDeptno() {
		return deptno;
	}

	public void setDeptno(Integer deptno) {
		this.deptno = deptno;
	}

	@Override
	public String toString() {
		return "Employee [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hiredate="
				+ hiredate + ", sal=" + sal + ", comm=" + comm + ", deptno=" + deptno + "]";
	}

}

一个后边需要的数据库工具类DBUtil.java

package edu.pt.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 数据库操作工具类
 */
public class DBUtil {

	private static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
	private static final String USER = "scott";
	private static final String PASSWORD = "orcl";
	private static final String DRIVER = "oracle.jdbc.OracleDriver";
	
	/**
	 * 加载驱动
	 */
	static {
		try {
			Class.forName(DRIVER);
		} catch (ClassNotFoundException e) {
			System.out.println("检查oracle驱动是否正确导入......");
		}
	}
	
	/**
	 * 获得连接
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection(URL, USER, PASSWORD);
	}
	
	/**
	 * 释放资源
	 * @param resultSet
	 * @param statement
	 * @param connection
	 */
	public static void release(Statement statement, Connection connection) {
		release(null, statement, connection);
	}
	
	/**
	 * 释放资源
	 * @param resultSet
	 * @param statement
	 * @param connection
	 */
	public static void release(ResultSet resultSet, Statement statement, Connection connection) {
		if(resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
}

数据访问层。

BaseDao.java

package edu.pt.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.pt.util.DBUtil;

public class BaseDao {

	/**
	 * 增删改数据
	 * @param sql
	 * @param params
	 * @return
	 */
	public int executeUpdate(String sql, Object ... params) {
		Connection connection = null;
		PreparedStatement statement = null;
		
		try {
			// 获得连接
			connection = DBUtil.getConnection();
			// 发送sql语句获得执行语句
			statement = connection.prepareStatement(sql);
			// 设置参数
			for (int i = 0; i < params.length; i++) {
				statement.setObject(i + 1, params[i]);
			}
			// 执行并返回数据
			return statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(statement, connection);
		}
		return 0;
	}
	
	/**
	 * 查询单个对象
	 * @param sql
	 * @param rowMapper
	 * @param params
	 * @return
	 */
	public <T> T getOne(String sql, RowMapper<T> rowMapper, Object ... params) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		T t = null;
		try {
			// 获得连接
			connection = DBUtil.getConnection();
			// 发送sql语句获得执行语句
			statement = connection.prepareStatement(sql);
			// 设置参数
			for (int i = 0; i < params.length; i++) {
				statement.setObject(i + 1, params[i]);
			}
			// 执行并返回结果集
			resultSet = statement.executeQuery();
			// 处理结果集
			if(resultSet.next()) {
				t = rowMapper.mapper(resultSet);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet,statement, connection);
		}
		return t;
	}
	
	/**
	 * 查询多个对象
	 * @param sql
	 * @param rowMapper
	 * @param params
	 * @return
	 */
	public <T> List<T> getList(String sql, RowMapper<T> rowMapper, Object ... params) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<T> list = new ArrayList<>();
		try {
			// 获得连接
			connection = DBUtil.getConnection();
			// 发送sql语句获得执行语句
			statement = connection.prepareStatement(sql);
			// 设置参数
			for (int i = 0; i < params.length; i++) {
				statement.setObject(i + 1, params[i]);
			}
			// 执行并返回结果集
			resultSet = statement.executeQuery();
			// 处理结果集
			while (resultSet.next()) {
				T t = rowMapper.mapper(resultSet);
				list.add(t);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet,statement, connection);
		}
		return list;
	}
}

RowMapper接口

package edu.pt.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 行映射器
 * @author hans
 *
 * @param <T>
 */
public interface RowMapper<T> {
	
	/**
	 * 映射
	 * @param resultSet
	 * @return
	 */
	T mapper(ResultSet resultSet)  throws SQLException;
	
}

EmployeeDao.java

package edu.pt.dao;

import java.util.List;

import edu.pt.pojo.Employee;

/**
 * 员工数据
 * @author hans
 *
 */
public interface EmployeeDao {

	/**
	 * 插入员工数据
	 * @param employee
	 * @return
	 */
	boolean add(Employee employee);
	
	/**
	 * 通过编号删除员工数据
	 * @param id
	 * @return
	 */
	boolean delete(Integer id);
	
	/**
	 * 修改员工数据
	 * @param employee
	 * @return
	 */
	boolean update(Employee employee);
	
	/**
	 * 通过编号查询员工数据
	 * @param id
	 * @return
	 */
	Employee getById(Integer id);
	
	/**
	 * 查询所有员工数据
	 * @return
	 */
	List<Employee> getAll();
	
}

实现类impl EmployeeDaoImpl

package edu.pt.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import edu.pt.dao.BaseDao;
import edu.pt.dao.EmployeeDao;
import edu.pt.dao.RowMapper;
import edu.pt.pojo.Employee;

public class EmployeeDaoImpl extends BaseDao implements EmployeeDao {

	@Override
	public boolean add(Employee employee) {
		// 准备sql语句
		String sql = "INSERT INTO emp (empno,ename,mgr,job,sal,comm,deptno) VALUES (?,?,?,?,?,?,?,?)";
		// 发送参数执行sql语句,并返回结果
		int row = executeUpdate(sql, employee.getEmpno(), employee.getEname(), employee.getMgr(), employee.getJob(),
				employee.getSal(), employee.getComm(), employee.getDeptno());
		// 返回操作结果
		return row == 1;
	}

	@Override
	public boolean delete(Integer id) {
		String sql = "DELETE FROM emp WHERE empno = ?";
		int row = executeUpdate(sql, id);
		return row == 1;
	}

	@Override
	public boolean update(Employee employee) {
		String sql = "UPDATE emp SET ename=?,mgr=?,job=?,sal=?,comm=?,deptno=? WHERE empno = ?";
		// 发送参数执行sql语句,并返回结果
		int row = executeUpdate(sql, employee.getEname(), employee.getMgr(), employee.getJob(), employee.getSal(),
				employee.getComm(), employee.getDeptno(), employee.getEmpno());
		// 返回操作结果
		return row == 1;
	}

	private RowMapper<Employee> rowMapper = new RowMapper<Employee>() {
		@Override
		public Employee mapper(ResultSet resultSet) throws SQLException {
			Employee employee = new Employee();
			employee.setEmpno(resultSet.getInt(1));
			employee.setEname(resultSet.getString(2));
			employee.setMgr(resultSet.getInt(3));
			employee.setJob(resultSet.getString(4));
			employee.setSal(resultSet.getDouble(5));
			employee.setComm(resultSet.getDouble(6));
			employee.setDeptno(resultSet.getInt(7));
			return employee;
		}
	};
	
	@Override
	public Employee getById(Integer id) {
		String sql = "SELECT empno,ename,mgr,job,sal,comm,deptno FROM emp WHERE empno = ?";
		return getOne(sql, rowMapper, id);
	}

	@Override
	public List<Employee> getAll() {
		String sql = "SELECT empno,ename,mgr,job,sal,comm,deptno FROM emp";
		return getList(sql, rowMapper);
	}
}