JDBC增删改查案例

JDBC增删改查案例 1.概述

​ 为了支持各大编程语言能够操纵数据库，完成对数据库的增删改查。各大厂商各自编写了连接自己数据库的JDBC实现程序。这个JDBC接口模板则由Java程序的设计者提供，由数据库厂商自己完成逻辑编写。对于最早的平台服务应用，由于没有各大ORM框架，我么基本所有的数据库操作都需要程序员自己编写，最底层就是对JDBC程序的封装。现在由于ORM框架的兴起，程序编写者只用更加注重业务逻辑的编写，对于数据库的连接管理基本都是框架自动完成，大大方便了程序编写者。

这里探究JDBC的应用，使用JDBC操纵MySQL数据库完成简单的增删改查逻辑。

2.基础环境介绍 2.1 数据库表

这里以员工（employee） 和部门（emp）表为例进行操作。

create table emp(id int primary key auto_increment,empname varchar(60));create table employee(id int primary key auto_increment comment '员工id',ename employeevarchar(60) comment '员工名称',gender varchar(1) comment '员工性别',empid int comment '部门id');alter table employee add foreign key(empid) references emp(id);insert into emp values(1,'广告部');insert into emp values(2,'开发部');insert into employee values(1,'tom','M',2);

2.2 版本 工具版本JDK1.8MySQL5.7IDEA2020.12.3 环境搭建

创建简单的maven工程即可，导入mysql连接依赖包

mysql mysql-connector-java 5.1.37

3.代码编写 3.1 连接工具类编写

我们将连接进行封装，可以快速获取连接，避免重复造轮子，提高开发效率。

package cn.jdbccase.demo.utils;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.util.Properties;public class JDBCUtils { public static Properties getdbproperties(){ Properties properties = new Properties(); // 获取类加载器 ClassLoader classLoader = JDBCUtils.class.getClassLoader(); try { properties.load(classLoader.getResourceAsStream("db.properties")); } catch (IOException e) { e.printStackTrace(); } return properties; } // 获取连接 public static Connection getCon(){ Properties properties = getdbproperties(); String url = properties.getProperty("jdbc.url"); String user = properties.getProperty("jdbc.user"); String password = properties.getProperty("jdbc.password"); String driver = properties.getProperty("jdbc.driver"); Connection con = null; try { // 注册驱动 Class.forName(driver); // 获取连接 con = DriverManager.getConnection(url, user, password); } catch (SQLException | ClassNotFoundException throwables) { throwables.printStackTrace(); } return con; } // 关闭资源 public static void closeResource(Connection con, Statement st) { if (st != null) { try { st.close(); } catch (SQLException e) { e.printStackTrace(); } } if (con != null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } }}

db.properties文件：

jdbc.url=jdbc:mysql://localhost:3306/jdbc_dbjdbc.user=rootjdbc.password=123456jdbc.driver=com.mysql.jdbc.Driver

3.2 查询测试

package cn.jdbccase.demo;import cn.jdbccase.demo.utils.JDBCUtils;import java.sql.*;public class demo1 { public static void main(String[] args) throws SQLException { Connection con = JDBCUtils.getCon(); Statement statement = con.createStatement(); ResultSet res = statement.executeQuery("select * from employee"); while (res.next()){ int id = res.getInt("id"); String ename = res.getString("ename"); String gender = res.getString("gender"); int empid = res.getInt("empid"); System.out.println(id + "t" + ename + "t" + gender + "t" + empid); } res.close(); JDBCUtils.closeResource(con,statement); }}

结果：

3.3 具体案例编写 3.3.1 Employee

get 和 set方法省略

public class employee { private Integer id; private String ename; private String gender; private Integer empid;....get and set 方法}

3.3.2 Emp

public class Emp { private Integer id; private String empname;....get and set 方法}

3.3.3 通用baseDao类编写

package cn.jdbccase.demo.dao;import cn.jdbccase.demo.utils.JDBCUtils;import java.lang.reflect.Field;import java.sql.*;import java.util.ArrayList;import java.util.List;public abstract class baseDao { public int update(Connection conn, String sql, Object..、params) { int count = 0; try { PreparedStatement ps = conn.prepareStatement(sql); // 添加参数 for (int i = 0; i < params.length; i++) { ps.setObject(i+1,params[i]); } //执行sql语句 count = ps.executeUpdate(); // 关闭资源 JDBCUtils.closeResource(null,ps); } catch (SQLException throwables) { throwables.printStackTrace(); } return count; } public T getInstance(Connection conn, Class cla, String sql, Object..、args) { PreparedStatement ps = null; ResultSet rs = null; try { ps = conn.prepareStatement(sql); for (int i = 0; i < args.length; i++) { ps.setObject(i + 1, args[i]); } rs = ps.executeQuery(); // 获取结果集的元数据 :ResultSetmetaData ResultSetmetaData rsmd = rs.getmetaData(); // 通过ResultSetmetaData获取结果集中的列数 int columnCount = rsmd.getColumnCount(); //利用反射封装对象 if (rs.next()) { T t = cla.newInstance(); for (int i = 0; i < columnCount; i++) { Object columValue = rs.getObject(i + 1); String columnLabel = rsmd.getColumnLabel(i + 1); Field field = cla.getDeclaredField(columnLabel); field.setAccessible(true); field.set(t, columValue); } return t; } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(null, ps, rs); } return null; } // 查询一批数据 public List getForList(Connection conn, Class clazz, String sql, Object..、args) { PreparedStatement ps = null; ResultSet rs = null; try { ps = conn.prepareStatement(sql); for (int i = 0; i < args.length; i++) { ps.setObject(i + 1, args[i]); } rs = ps.executeQuery(); ResultSetmetaData rsmd = rs.getmetaData(); int columnCount = rsmd.getColumnCount(); ArrayList list = new ArrayList(); while (rs.next()) { T t = clazz.newInstance(); for (int i = 0; i < columnCount; i++) { Object columValue = rs.getObject(i + 1); String columnLabel = rsmd.getColumnLabel(i + 1); Field field = clazz.getDeclaredField(columnLabel); field.setAccessible(true); field.set(t, columValue); } list.add(t); } return list; } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(null, ps, rs); } return null; }}

baseDao类里面使用了反射来封装查询结果，这里理解比较抽象，需要先理解反射机制。通过反射来分装结果集，可以令方法变的通用，更加的灵活。

3.3.4 EmployeeDao

根据面向接口编程的思想，若需要编写Employee层的逻辑，我们需要先定义操作接口。

package cn.jdbccase.demo.dao;import cn.jdbccase.demo.domain.employee;import java.sql.Connection;public interface EmployeeDao { // 添加员工 public Integer addEmployee(employee em, Connection con); // 根据id删除员工 public Integer delEmployById(Integer id, Connection con); // 根据id修改员工 public Integer updateEmployById(employee e, Connection con); // 根据id查询某个员工 public employee getEmployById(Integer id, Connection con);}

实现类的编写：

package cn.jdbccase.demo.dao;import cn.jdbccase.demo.domain.employee;import java.sql.Connection;public class EmployeeDaoImpl extends baseDao implements EmployeeDao{ @Override public Integer addEmployee(employee em, Connection con) { String sql = "insert into employee(ename,gender,empid) values(?,?,?)"; int count = update(con,sql, em.getEname(), em.getGender(), em.getEmpid()); return count; } @Override public Integer delEmployById(Integer id, Connection con) { String sql = "delete from employee where id = ?"; int count = update(con,sql, id); return count; } @Override public Integer updateEmployById(employee e, Connection con) { String sql = "update employee set ename = ?,gender = ?,empid = ? where id = ?"; int count = update(con,sql,e.getEname(),e.getGender(),e.getEmpid(),e.getId()); return count; } @Override public employee getEmployById(Integer id, Connection con) { String sql = "select * from employee where id = ?"; employee instance = (employee) getInstance(con, employee.class, sql, id); return instance; }}

3.3.5 测试类编写

package cn.jdbccase.demo.test;import cn.jdbccase.demo.dao.EmployeeDao;import cn.jdbccase.demo.dao.EmployeeDaoImpl;import cn.jdbccase.demo.domain.employee;import cn.jdbccase.demo.utils.JDBCUtils;import org.junit.Before;import org.junit.Test;import java.sql.Connection;public class DaoTest { EmployeeDao empdao = null; @Before public void init(){ empdao = new EmployeeDaoImpl(); } @Test public void testadd(){ Connection con = JDBCUtils.getConnection(); employee employee = new employee(); employee.setEname("Bob"); employee.setGender("M"); employee.setEmpid(2); Integer count = empdao.addEmployee(employee, con); System.out.println(count); JDBCUtils.closeResource(con,null); } @Test public void testdel(){ Connection con = JDBCUtils.getConnection(); Integer count = empdao.delEmployById(4, con); System.out.println(count); JDBCUtils.closeResource(con,null); } @Test public void testupdate(){ Connection con = JDBCUtils.getConnection(); employee employee = new employee(); employee.setId(1); employee.setEname("tom"); employee.setGender("F"); employee.setEmpid(1); Integer count = empdao.updateEmployById(employee,con); System.out.println(count); JDBCUtils.closeResource(con,null); } @Test public void testselect(){ Connection con = JDBCUtils.getConnection(); employee employ = empdao.getEmployById(1,con); System.out.println(employ); JDBCUtils.closeResource(con,null); }}

完成简单的增删改查