JAVA----JDBC

1、什么是JDBC

1.1 数据库驱动程序

1.2 数据库操作流程

2、JDBC3.0 标准中常用接口与类

2.1 Driver接口

2.2 DriverManager类

2.2.1 常用方法

2.2.2 JDBC URL

2.3 Connection 接口

2.3.1 常用方法

2.4 Statement接口

2.4.1 常用方法

2.5 PreparedStatement接口

2.5.1 常用方法

2.6 ResultSet接口

ResultSet提供检索不同类型字段的方法

2.6.1 常用方法

2.7 Result对结果集进行滚动的方法

2.8 CallableStatement接口

3、JDBC的使用步骤

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

public class JdbcTest {
    //向Departments表中添加一条数据
    public void insertDepartments(String department_name,int location_id){
        Connection conn = null;
        Statement state = null;
        try {
            //驱动的注册
            Class.forName("com.mysql.jdbc.Driver");
            //创建connection连接对象
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myemployees?useUnicode=true&characterEncoding=utf-8","root","root");
            //执行SQL
            String sql = "insert into departments values(default,'"+ department_name +"',null,"+ location_id +")";
            state = conn.createStatement();
            int flat = state.executeUpdate(sql);
            System.out.println(flat);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(state != null){
                try {
                    state.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    //更新departments表中的department_id为6的数据，将部门名称修改为教学部，location_id修改为6
    public void updateDeparments(String department_name,int location_id,int department_id){
        Connection conn = null;
        Statement state = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myemployees?useUnicode=true&characterEncoding=utf-8","root","root");

            state = conn.createStatement();
            String sql = "update departments d set d.department_name = '"+department_name+"',d.location_id = "+location_id+" where d.department_id = "+ department_id+"";

            state.executeUpdate(sql);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(state != null){
                try {
                    state.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

    }

    public static void main(String[] args) {
        JdbcTest test = new JdbcTest();
        test.insertDepartments("研发部",8);
        test.updateDeparments("教学部",6,8);
    }
}

4、JDBC工具类

• jdbc.properties

driver=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/employees?useUnicode=true&characterEncoding=utf-8
userName=root
userPassword=root

• JDBCUtil

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;

public class JDBCUtil {
    private static String driver ;
    private static String jdbcUrl ;
    private static String userName ;
    private static String userPassword ;

    //静态代码块，在类加载时执行一次
    static{
        //读取Properties文件
        ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
        driver = bundle.getString("driver");
        jdbcUrl = bundle.getString("jdbcUrl");
        userName = bundle.getString("userName");
        userPassword = bundle.getString("userPassword");
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //获取Connection对象
    public static Connection getConnection(){
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(jdbcUrl,userName,userPassword);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }

    //关闭Statement资源
    public static void closeStatement(Statement state){
        if(state != null){
            try {
                state.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    //关闭Connection
    public static void closeConnection(Connection conn){
        try {
            if(conn != null)
                conn.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    //关闭资源
    public static void closeResource(Statement state, Connection conn, ResultSet rs){
        try {
            if(state != null)
                state.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if(conn != null)
                conn.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        try {
            if(rs != null)
                rs.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
	
	//事务回滚
    public static void rollback(Connection conn){
        try {
            if(conn != null)
                conn.rollback();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

5、ResultSet

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

public class JdbcTest {
    //查询Departments表中部门ID为6的部门信息
    public void seleectDepartmentsById(int departmentId){
        Connection conn = null;
        Statement state = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtil.getConnection();
            state = conn.createStatement();

            String sql = "select * from departments d where d.department_id = "+departmentId;

            //执行查询返回结果
            rs = state.executeQuery(sql);
            while(rs.next()){
                rs.getInt("department_id");
                rs.getString("department_name");
                rs.getInt(3);

                System.out.println(rs.getInt("department_id") + " " + rs.getString("department_name") + " " + rs.getInt(3));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeResource(state,conn,rs);
        }
    }

    //查询departments表中的所有数据，并通过ResultSet实现逻辑分页
    public void selectDeptPage(int currentPage,int pageRows){
        Connection conn = null;
        Statement state = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtil.getConnection();
            state = conn.createStatement();

            String sql = "select * from departments";
            //开始位置与结束位置
            int begin = (currentPage - 1) * pageRows;
            int end = currentPage * pageRows;

            //当前位置的计数器
            int currentNum = 0;
            while(rs.next()){
                //什么情况下获取结果集中的数据
                if(currentNum >= begin && currentNum < end){
                    System.out.println(rs.getInt("department_id") + " " + rs.getString("department_name"));
                    //结束操作ResultSet的边界条件
                    if(currentNum == end - 1){
                        break;
                    }
                }
                currentNum++;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeResource(state,conn,rs);
        }
    }
}

6、SQL注入问题

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

public class JdbcTest {
    //查询部门表,sql注入问题
    public void sqlInject(String departmentName,int locationId){
        Connection conn = null;
        Statement state = null;
        ResultSet rs = null;

        try {;
            conn = JDBCUtil.getConnection();
            state = conn.createStatement();

            String sql = "select * from departments where department_name = '"+ departmentName +"' and localtion_id = "+ locationId +"";
            System.out.println(sql);
            rs = state.executeQuery(sql);

            while(rs.next()){
                System.out.println(rs.getInt("department_id") + " " + rs.getString("department_name") + " " + rs.getInt("localtion_id"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeResource(state,conn,rs);
        }

    }

    public static void main(String[] args) {
        JdbcTest test = new JdbcTest();
        //研发部' or 1=1 --  会导致sql注入问题
        test.sqlInject("研发部' or 1=1 --",8);
    }
}

6、PreparedStatement对象的使用

6.1 PreparedStatement特点

6.2 PreparedStatement的预编译能力

6.3 解析过程

6.3.1 硬解析

6.3.2 软解析

6.4 预编译方式

6.4.1 依赖数据库驱动完成预编译(常用)

6.4.2 依赖数据库服务器完成预编译

jdbcUrl=jdbc:mysql://localhost:3306/employees?useUnicode=true&characterEncoding=utf-8&useServerPrepStmts=true

6.5 查询返回单条结果集

/**
 * 数据模型
 * 存放数据库中Departments表中的数据
 *
 */
public class Departments {
    private int departmentId;
    private String departmentName;
    private int locationId;

    public int getDepartmentId() {
        return departmentId;
    }

    public void setDepartmentId(int departmentId) {
        this.departmentId = departmentId;
    }

    public String getDepartmentName() {
        return departmentName;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }

    public int getLocationId() {
        return locationId;
    }

    public void setLocationId(int locationId) {
        this.locationId = locationId;
    }
}

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 查询返回单条结果集
 */
public class PreparedStatementDemo {
    //向Departments表中插入一条数据
    public void insertDepartments(String departmentName,int locationId){
        Connection conn = null;
        PreparedStatement state = null;

        try {
            conn = JDBCUtil.getConnection();
            state = conn.prepareStatement("insert into departments values(default,?,?)");

            state.setString(1,departmentName);
            state.setInt(2,locationId);

            state.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeResource(state,conn,null);
        }
    }
    //更新数据
    public void updateDepartment(int departmentId,String departmentName,int localtionId){
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = JDBCUtil.getConnection();
            ps = conn.prepareStatement("update departments set department_name = ?,location_id = ? where department_id = ?");
            ps.setString(1,departmentName);
            ps.setInt(2,localtionId);
            ps.setInt(3,departmentId);

            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeResource(ps,conn,null);
        }
    }

    //完成数据查询
    public Departments selectDepartmentsById(int departmentId){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Departments dept = null;

        try {
            conn = JDBCUtil.getConnection();
            ps = conn.prepareStatement("select * from departments where department_id = ?");
            ps.setInt(1,departmentId);
            rs = ps.executeQuery();

            while(rs.next()){
                dept = new Departments();
                dept.setDepartmentId(rs.getInt("department_id"));
                dept.setDepartmentName(rs.getString("department_name"));
                dept.setLocationId(rs.getInt("location_id"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeResource(ps,conn,null);
        }
        return dept;
    }
    public static void main(String[] args) {
        PreparedStatementDemo demo = new PreparedStatementDemo();
        Departments dept = demo.selectDepartmentsById(10);

        if(dept != null){
            System.out.println(dept.getDepartmentId() + " " + dept.getDepartmentName() + " " + dept.getLocationId());
        }
    }
}

6.6 查询返回多条结果集

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

/**
 * 查询返回多条结果集
 */
public class PreparedStatementDemo {
    //查询部门表中部门名称，找到那些包含“人力”的部门信息
    public List<Departments> selectDepartmentByLikeName(String departmentName){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        List<Departments> list = new ArrayList<>();
        try {
            conn = JDBCUtil.getConnection();
            ps = conn.prepareStatement("select * from departments where department_name like ?");
            ps.setString(1,"%" + departmentName + "%");//like后面不能跟 '%?%',应把%写在setString里

            while(rs.next()){
                Departments dept = new Departments();
                dept.setDepartmentId(rs.getInt("department_id"));
                dept.setDepartmentName(rs.getString("department_name"));
                dept.setLocationId(rs.getInt("localtion_id"));
                list.add(dept);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeResource(ps,conn,rs);
        }
        return list;
    }

    public static void main(String[] args) {
        PreparedStatementDemo demo = new PreparedStatementDemo();
        List<Departments> list = demo.selectDepartmentByLikeName("人力");

        for (Departments dept : list) {
            System.out.println(dept.getDepartmentId() + " " + dept.getDepartmentName() + " " + dept.getLocationId());
        }
    }
}

6.7 PreparedStatement批处理操作

• 批处理：在与数据库的一次连接中，批量的执行SQL语句

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

/**
 * 批量操作
 */
public class PreparedStatementDemo {
    //批量操作
    public void addBatch(List<Departments> list){
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = JDBCUtil.getConnection();
            ps = conn.prepareStatement("insert into departments values (default,?,?)");
            for(int i = 0;i < list.size();i++){
                ps.setString(1,list.get(i).getDepartmentName());
                ps.setInt(2,list.get(i).getLocationId());

                //添加批处理
                ps.addBatch();
            }
            int[] arr = ps.executeBatch();
            System.out.println(arr);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeResource(ps,conn,null);
        }

    }

    public static void main(String[] args) {
        PreparedStatementDemo demo = new PreparedStatementDemo();

        List<Departments> list = new ArrayList<>();
        for(int i = 1;i <= 10;i++){
            Departments dept = new Departments();
            dept.setDepartmentName("研发部" + i);
            dept.setLocationId(20 + i);
            list.add(dept);
        }
        demo.addBatch(list);
    }
}

7、JDBC的事务处理

import java.sql.Connection;
import java.sql.PreparedStatement;

public class PreparedStatementDemo {
    //事务处理
    public void deleteDepartments(String departmentName){
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = JDBCUtil.getConnection();
            //关闭事务的自动提交
            conn.setAutoCommit(false);

            ps = conn.prepareStatement("delete from departments where department_name like ?");
            ps.setString(1,"%" + departmentName + "%");
            ps.executeUpdate();
            //手动提交事务
            conn.commit();
            ps = conn.prepareStatement("insert into departments values(default,'开发部',2)");
            ps.executeUpdate();
            String str = null;
            str.length();
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            //如出现异常，需要回滚
            JDBCUtil.rollback(conn);
        } finally {
            JDBCUtil.closeResource(ps,conn,null);
        }
    }

    public static void main(String[] args) {
        PreparedStatementDemo demo = new PreparedStatementDemo();
        demo.deleteDepartments("教学部");//通过数据库查询，数据依然存在，是因为自动提交关闭，需手动提交事务
    }
}

8、动态SQL查询

• 根据用户给定的条件来决定执行什么样的查询

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

public class JDBCAdvanced {
    //动态查询
    public List<Departments> selectDeptByProperty(Departments departments){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Departments> list = new ArrayList<>();

        try {
            conn = JDBCUtil.getConnection();
            String sql = genSQL(departments);
            //打印SQL语句
            System.out.println(sql);
            ps = conn.prepareStatement(sql);

            rs = ps.executeQuery();

            while(rs.next()){
                Departments dept = new Departments();
                dept.setDepartmentId(rs.getInt("department_id"));
                dept.setDepartmentName(rs.getString("department_name"));
                dept.setLocationId(rs.getInt("location_id"));

                list.add(dept);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeResource(ps,conn,rs);
        }
        return list;
    }

    //拼接SQL语句
    private String genSQL(Departments dept) {
        StringBuilder sb = new StringBuilder("select * from departments where 1=1 ");
        if(dept.getDepartmentId() > 0){
            sb.append(" and department_id = ").append(dept.getDepartmentId());
        }
        if(dept.getDepartmentName() != null && dept.getDepartmentName().length() > 0){
            //注意单引号的拼接
            sb.append(" and department_name = '").append(dept.getDepartmentName()).append("'");
        }
        if(dept.getLocationId() > 0){
            sb.append(" and location_id = ").append(dept.getLocationId());
        }
        return sb.toString();
    }

    public static void main(String[] args) {
        JDBCAdvanced ad = new JDBCAdvanced();
        Departments dept = new Departments();
        dept.setDepartmentName("development");
        dept.setLocationId(20);
        List<Departments> list = ad.selectDeptByProperty(dept);

        for (Departments d : list) {
            System.out.println(d.getDepartmentId() + " " + d.getDepartmentName() + " " + d.getLocationId());
        }
    }
}

9、应用程序分层

• 分层优点
  
  

9.1 三层结构

• 项目分层
  

9.2 分层结构中实现业务

• 使用到了mysql-connector-java-8.0.21.jar、commons-beanutils-1.9.4.jar、commons-logging-1.2.jar
  

9.2.1 持久层 — dao

• 接口

package com.jdbcdemo.dao;

import java.util.List;

public interface BaseDao {
    public int executeUpdate(String sql,Object[] param);
    public <T> List<T> find(String sql, Object[] param, Class<T> clazz);
}

package com.jdbcdemo.dao;

import com.jdbcdemo.pojo.Departments;
import com.jdbcdemo.pojo.Dept;

import java.util.List;

public interface DepartmentsDao extends BaseDao{
    public List<Departments> selectDeptByName(String deptName);
    public void insertDept(Departments dept);

    public int updateDept(Departments dept);
    public int deleteDeptById(int departmentId);

    public List<Dept> selectDeptByLikeName(String deptName);
}

• 接口实现类

package com.jdbcdemo.dao.impl;

import com.jdbcdemo.commons.JDBCUtil;
import com.jdbcdemo.dao.BaseDao;
import com.mysql.cj.protocol.Resultset;
import org.apache.commons.beanutils.BeanUtils;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BaseDaoImpl implements BaseDao {

    /**
     * 封装通用的DML操作
     * @param sql
     * @param param
     * @return
     */
    @Override
    public int executeUpdate(String sql, Object[] param) {
        Connection conn = null;
        PreparedStatement ps = null;
        int rows = 0;
        try {
            conn = JDBCUtil.getConnection();
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql);
            //得到参数的个数
            ParameterMetaData pmd = ps.getParameterMetaData();
            //绑定参数
            for(int i = 0;i < pmd.getParameterCount();i++){
                ps.setObject(i + 1,param[i]);
            }
            rows = ps.executeUpdate();
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            JDBCUtil.rollback(conn);
        } finally {
            JDBCUtil.closeResource(ps,conn,null);
        }

        return rows;
    }

    /**
     * 完成通用查询的方法
     * 注意：通用的查询方法中 要求模型对象的属性名必须要和数据库表中的列名相同
     * @param sql
     * @param param
     * @param clazz
     * @param <T>
     * @return
     */
    @Override
    public <T> List<T> find(String sql, Object[] param, Class<T> clazz) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs  = null;
        List<T> list = new ArrayList<>();

        try {
            conn = JDBCUtil.getConnection();
            ps = conn.prepareStatement(sql);

            //得到参数的个数
            ParameterMetaData pmd = ps.getParameterMetaData();
            //绑定参数
            for(int i = 0;i < pmd.getParameterCount();i++){
                ps.setObject(i+1,param[i]);
            }

            //处理结果集
            rs = ps.executeQuery();

            //获取结果集等的信息
            ResultSetMetaData rsmd = rs.getMetaData();

            while(rs.next()){
                //完成ORM处理：通过反射
                T bean = clazz.newInstance();
                for(int i = 0;i < rsmd.getColumnCount();i++){
                    //获取列名
                    String columnName = rsmd.getColumnName(i + 1);
                    //获取列的值
                    Object value = rs.getObject(columnName);
                    //通过BeanUtil工具类将值放入到对象中
                    BeanUtils.setProperty(bean,columnName,value);
                }
                list.add(bean);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeResource(ps,conn,rs);
        }
        return list;
    }
}

package com.jdbcdemo.dao.impl;

import com.jdbcdemo.commons.JDBCUtil;
import com.jdbcdemo.dao.DepartmentsDao;
import com.jdbcdemo.pojo.Departments;
import com.jdbcdemo.pojo.Dept;

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

public class DepartmentsDaoImpl extends BaseDaoImpl implements DepartmentsDao {
    @Override
    public List<Departments> selectDeptByName(String deptName) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Departments> list = new ArrayList<>();

        try {
            conn = JDBCUtil.getConnection();
            ps = conn.prepareStatement("select * from departments where department_name = ?");
            ps.setString(1,deptName);
            rs = ps.executeQuery();

            while(rs.next()){
                Departments d = new Departments();
                d.setDepartmentId(rs.getInt("department_id"));
                d.setDepartmentName(rs.getString("department_name"));
                d.setLocationId(rs.getInt("location_id"));

                list.add(d);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            JDBCUtil.rollback(conn);
        } finally {
            JDBCUtil.closeResource(ps,conn,null);
        }
        return list;
    }

    @Override
    public void insertDept(Departments dept) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Departments> list = new ArrayList<>();
        try {
            conn = JDBCUtil.getConnection();
            conn.setAutoCommit(false);
            ps = conn.prepareStatement("insert into departments values(default,?,?)");
            ps.setString(1,dept.getDepartmentName());
            ps.setInt(2,dept.getLocationId());
            ps.execute();
            //手动提交
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            JDBCUtil.rollback(conn);
        } finally {
            JDBCUtil.closeResource(ps,conn,null);
        }
    }

    //更新部门信息
    @Override
    public int updateDept(Departments dept) {
        String sql = "update departments set department_name = ?,location_id = ? where department_id = ?";
        Object[] param = new Object[]{dept.getDepartmentName(),dept.getDepartmentId()};
        return this.executeUpdate(sql,param);
    }

    //删除部门信息
    @Override
    public int deleteDeptById(int departmentId) {
        String sql = "delete from departments where department_id = ?";
        Object[] param = new Object[]{departmentId};
        return this.executeUpdate(sql,param);
    }

    @Override
    public List<Dept> selectDeptByLikeName(String deptName) {
        String sql = "select * from departments where department_name like ?";
        Object[] param = new Object[]{"%" + deptName + "%"};
        return this.find(sql,param,Dept.class);
    }


}

9.2.2 业务层 — service

• 接口

package com.jdbcdemo.service;

import com.jdbcdemo.pojo.Departments;
import com.jdbcdemo.pojo.Dept;

import java.util.List;

public interface DepartmentsService {
    public void addDepartment(Departments dept);
    public int modifyDepartments(Departments dept);
    public int dropDepartments(int departmentId);
    public List<Dept> findDept(String deptName);
}

• 接口实现类

package com.jdbcdemo.service.impl;

import com.jdbcdemo.dao.DepartmentsDao;
import com.jdbcdemo.dao.impl.DepartmentsDaoImpl;
import com.jdbcdemo.pojo.Departments;
import com.jdbcdemo.pojo.Dept;
import com.jdbcdemo.service.DepartmentsService;

import java.util.List;

public class DepartmentsServiceImpl implements DepartmentsService {
    @Override
    public void addDepartment(Departments dept) {
        DepartmentsDao deptDao = new DepartmentsDaoImpl();
        deptDao.insertDept(dept);
    }

    @Override
    public int modifyDepartments(Departments dept) {
        DepartmentsDao deptDao = new DepartmentsDaoImpl();
        return deptDao.updateDept(dept);
    }

    @Override
    public int dropDepartments(int departmentId) {
        DepartmentsDao deptDao = new DepartmentsDaoImpl();
        return deptDao.deleteDeptById(departmentId);
    }

    @Override
    public List<Dept> findDept(String deptName) {
        DepartmentsDao deptDao = new DepartmentsDaoImpl();
        return deptDao.selectDeptByLikeName(deptName);
    }
}

9.2.3 工具层 – connons

package com.jdbcdemo.commons;

import java.sql.*;
import java.util.ResourceBundle;

public class JDBCUtil {
    private static String driver ;
    private static String jdbcUrl ;
    private static String userName ;
    private static String userPassword ;

    //静态代码块，在类加载时执行一次
    static{
        //读取Properties文件
        ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
        driver = bundle.getString("driver");
        jdbcUrl = bundle.getString("jdbcUrl");
        userName = bundle.getString("userName");
        userPassword = bundle.getString("userPassword");
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //获取Connection对象
    public static Connection getConnection(){
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(jdbcUrl,userName,userPassword);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }

    //关闭Statement资源
    public static void closeStatement(Statement state){
        if(state != null){
            try {
                state.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    //关闭Connection
    public static void closeConnection(Connection conn){
        try {
            if(conn != null)
                conn.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    //关闭资源
    public static void closeResource(Statement state, Connection conn, ResultSet rs){
        try {
            if(state != null)
                state.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if(conn != null)
                conn.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        try {
            if(rs != null)
                rs.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    //事务回滚
    public static void rollback(Connection conn){
        try {
            if(conn != null)
                conn.rollback();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

9.2.4 数据模型层 — pojo

package com.jdbcdemo.pojo;

/**
 * 数据模型
 * 存放数据库中Departments表中的数据
 *
 */
public class Departments {
    private int departmentId;
    private String departmentName;
    private int locationId;

    public int getDepartmentId() {
        return departmentId;
    }

    public void setDepartmentId(int departmentId) {
        this.departmentId = departmentId;
    }

    public String getDepartmentName() {
        return departmentName;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }

    public int getLocationId() {
        return locationId;
    }

    public void setLocationId(int locationId) {
        this.locationId = locationId;
    }
}

package com.jdbcdemo.pojo;

public class Dept {
    //对象名与数据库的列对应上了
    private int department_id;
    private String department_name;
    private int location_id;

    public int getDepartment_id() {
        return department_id;
    }

    public void setDepartment_id(int department_id) {
        this.department_id = department_id;
    }

    public String getDepartment_name() {
        return department_name;
    }

    public void setDepartment_name(String department_name) {
        this.department_name = department_name;
    }

    public int getLocation_id() {
        return location_id;
    }

    public void setLocation_id(int location_id) {
        this.location_id = location_id;
    }
}

9.2.5 测试层 — test

package com.jdbcdemo.test;

import com.jdbcdemo.pojo.Departments;
import com.jdbcdemo.pojo.Dept;
import com.jdbcdemo.service.DepartmentsService;
import com.jdbcdemo.service.impl.DepartmentsServiceImpl;

import java.util.List;

public class Test {
    public static void main(String[] args) {
        Departments dept = new Departments();
        dept.setDepartmentName("研发部");
        dept.setLocationId(30);
        DepartmentsService ds = new DepartmentsServiceImpl();
//        ds.addDepartment(dept);
//        int flag = ds.modifyDepartments(dept);
//        System.out.println(flag);
//        int flag = ds.dropDepartments(37);
//        System.out.println(flag);

        List<Dept> list = ds.findDept("人力");
        for (Dept d : list) {
            System.out.println(d.getDepartment_id() + " " + d.getDepartment_name() + " " + d.getLocation_id());
        }
    }
}

10、JDBC驱动加载原理

10.1 创建对象的方式

10.2 创建对象时三个重要的步骤

10.3 MySQL驱动类的实例化过程