学习笔记04——MySQL（四）

一、JDBC概述

1、存储数据的容器

在Java学习过程中，最开始的时候我们存储数据的容器是数组、集合、对象等等，产生的数据都是存储在内存空间的，这是一个临时空间，数据不能永久保存。

之后我们学习了文件，它是将数据存储在硬盘上的，能够实现数据持久化，永久性保存数据。数据库也是永久性保存数据的容器，而JDBC是用来干什么的呢，它是连接Java内存与硬盘（数据库）之间的桥梁。

2、JDBC

全称是Java DataBase Connectivity，可以理解成是以前的Socket桥梁，确切的说，JDBC是Java平台提供的一套统一的执行规范/标准(接口)，每一个不同厂商生产的数据库产品，但都实现了此规范。

JDBC的作用：

• 负责产生连接
• 传送SQL指令
• 处理响应信息
• 处理事务

3、JDBC驱动类型

• JDBC-ODBC：每一个客户机都需要安装，很耗费空间，管理不方便。
• JDBC-Native：类似第一种，每一个客户机安装，调用本地原生C/C++，具体到某一个数据库，想要改变，需要改变底层代码实现。
• JDBC-Net：使用了标准的Socket通信，不需要每一个客户机都进行安装，相对比较灵活。
• 纯JavaJDBC：一个纯粹的基于Socket进行的通信，不需要安装客户机上，由每一个数据库的厂商自身提供。

4、JDBC驱动包的下载

官网下载地址：MySQLhttps://www.mysql.com

选择一个合适的版本，解压zip包，找到一个mysql-connector-java-5.1.47.jar

二、JDBC六部曲

1、导包

• 在我们的工程里创建一个文件夹lib
• 将找到的这个jar文件包导入到Java工程里
• 做一个相关设置：File-->Project Structure-->Libraries-->点击中间的“+” -->选择Java-->选择jar文件路径

2、加载驱动类——Driver

Class.forName("com.mysql.jdbc.Driver");

3、获取连接

可以理解为以前的Socket

Connection conn = DriverManager.getConnection(url,user,password);

4、创建状态参数(流)

可以理解为是以前socket.getInputStream  socket.getOutputStream

Statement stat = conn.createStatement();

5、执行数据库操作

• 写操作（DML）：executeUpdate(sql);
• 读操作（DQL）：executeQuery(sql);
• 说明：写操作（insert delete update）时，对数据库进行更新。读操作（select）时，数据库没有发生变化。

6、关闭

• close();

三、几个问题

1、加载驱动类的时候，8.x和5.x版本不一致有问题

• 如果5.x版本    com.mysql.jdbc.Driver
• 如果8.x版本    com.mysql.cj.jdbc.Driver

2、获取连接时候，8.x和5.x版本不一致有问题

• 如果5.x版本    url改为jdbc:mysql://ip:port/database名
• 如果8.x版本    url给为jdbc:mysql://ip:port/database名?serverTimezone=CST

3、加载驱动的问题

可以通过四种方式加载驱动

• 通过反射机制：Class.forName("类全名");
• 通过DriverManager静态方法注册驱动：DriverManager.registerDriver(new Driver());
• 通过直接创建驱动对象：new Driver();
• 通过System类中设置属性值来加载：System.setProperty("jdbc.driver","com.mysql.cj.jdbc.Driver");

4、操作事务

• JDBC默认的开启事务  默认的自动提交事务
• 可以自己设置手动提交事务

//false表示手动提交   true默认 表示自动提交
conn.setAutoCommit(false);

• 可以设置事务的隔离级别

conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

• 事务最终的处理操作

conn.commit(); //提交
conn.rollback();//回滚

Savepoint s = conn.setSavepoint("x"); //设置保存点
conn.rollback(s); //保存点回滚

5、SQL注入

1）SQL注入

SQL注入是指通过SQL命令，拼接其他的字符串，让其他的那些字符串来改变原有SQL语句的执行，最终达到欺骗服务器的效果。里面拼接的其他字符，肯定是SQL语法认可的合法的字。

//'1'='1'一定正确，所以不管用户名、密码是啥，都能登录
select * from atm 
where aname = 'xxx' and apassword = 'xxx' or '1' = '1'

2）SQL注入产生的原因

1.判断不严谨导致的

2.SQL语句问题，允许拼接字符串，造成了安全问题

3）解决SQL注入的方法

可以利用PreparedStatement来处理SQL

PreparedStatement pstat = conn.prepareStatement(sql语句);

4）对比Statement与PreparedStatment

Statement	PreparedStatment
普通的状态参数	预处理的状态参数
创建时不需要SQL	创建时就需要预先加载SQL语句
此时没有执行	此时没有执行，但底层预先处理SQL需要查询的结果，性能高，可以利用动态化进行参数的处理

5）PreparedStatment的优点

• 增强SQL可读性
• 可以参数动态化
• 防止SQL注入
• 提高执行性能

四、模糊查询、分页查询、联合查询

1、模糊查询

//方式一：Sql语句正常写
select * from student where name like ?;
pstat.setString(1,"%"+'光'+"%");

//方式二：Sql语句稍微改变一下
select * from student where name like  \"%\" ? \"%\";
//sql语句本身的双引号和String类型的双引号冲突
pstat.setString(1,'光');

    //设计一个方法 做模糊查询 like
    //使用预处理状态参数 恰巧有动态的问号 遇到了百分号
    //  参数--->字母 A R   返回值--->ArrayList<Emp>
    public ArrayList<Emp> selectForLike(String letter){
        ArrayList<Emp> list = new ArrayList<Emp>();
        //String sql = "select * from emp where ename like ?";
        String sql = "select * from emp where ename like  \"%\" ? \"%\"";
        try {
            Class.forName(className);
            Connection conn = DriverManager.getConnection(url,user,password);
            PreparedStatement pstat = conn.prepareStatement(sql);
            //pstat.setString(1,"%"+letter+"%");//  %A%
            pstat.setString(1,letter);
            ResultSet rs = pstat.executeQuery();
            while(rs.next()){
                Emp emp = new Emp();
                emp.setEmpno(rs.getInt("empno"));
                emp.setEname(rs.getString("ename"));
                emp.setJob(rs.getString("job"));
                emp.setMgr(rs.getInt("mgr"));
                emp.setHiredate(rs.getDate("hiredate"));
                emp.setSal(rs.getFloat("sal"));
                emp.setComm(rs.getFloat("comm"));
                emp.setDeptno(rs.getInt("deptno"));
                list.add(emp);
            }
            rs.close();
            pstat.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

2、分页查询

• 执行一条SQL语句
• 注意我们需要利用MVC分层设计思想
• service层负责计算page
• dao层负责根据计算后的rowIndex执行查询
• 结果不止一条记录  ArrayList

    //设计一个方法 实现分页查询
    //  是否需要参数----> 起始行索引   每页显示几行(Web前端工程师自己规定--5行)
    //  是否需要返回值--> ArrayList<Emp>
    public ArrayList<Emp> selectByPaging(int rowIndex){
        ArrayList<Emp> list = new ArrayList<Emp>();
        String sql = "select * from emp order by sal desc limit ?,5";
        try {
            Class.forName(className);
            Connection conn = DriverManager.getConnection(url,user,password);
            PreparedStatement pstat = conn.prepareStatement(sql);
            pstat.setInt(1,rowIndex);
            ResultSet rs = pstat.executeQuery();
            while(rs.next()){
                Emp emp = new Emp();
                emp.setEmpno(rs.getInt("empno"));
                emp.setEname(rs.getString("ename"));
                emp.setJob(rs.getString("job"));
                emp.setMgr(rs.getInt("mgr"));
                emp.setHiredate(rs.getDate("hiredate"));
                emp.setSal(rs.getFloat("sal"));
                emp.setComm(rs.getFloat("comm"));
                emp.setDeptno(rs.getInt("deptno"));
                list.add(emp);
            }
            rs.close();
            pstat.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
   

//Service层
public class EmpService {

    private EmpDao dao = new EmpDao();
    public ArrayList<Emp> changePageToRowIndex(int page){
        //1.负责将page计算成 rowIndex
        int rowIndex = (page-1)*5;
        //2.调用dao 让他去做事
        ArrayList<Emp> list = dao.selectByPaging(rowIndex);
        //3.dao方法的返回值当做我们方法自己的结果 交个用户
        return list;
        return dao.selectByPaging((page-1)*5);
    }
}

3、联合查询

    //设计一个方法 用来查询所有emp关联dept的数据
    public ArrayList<Emp> selectAllEmpAndDept(){
        ArrayList<Emp> list = new ArrayList<Emp>();
        String sql = "select * from emp e , dept d where e.deptno = d.deptno";
        try {
            Class.forName(className);
            Connection conn = DriverManager.getConnection(url,user,password);
            PreparedStatement pstat = conn.prepareStatement(sql);
            ResultSet rs = pstat.executeQuery();
            while(rs.next()){
                Emp emp = new Emp();
                emp.setEmpno(rs.getInt("empno"));
                emp.setEname(rs.getString("ename"));
                emp.setJob(rs.getString("job"));
                emp.setMgr(rs.getInt("mgr"));
                emp.setHiredate(rs.getDate("hiredate"));
                emp.setSal(rs.getFloat("sal"));
                emp.setComm(rs.getFloat("comm"));
                Dept dept = new Dept();
                dept.setDeptno(rs.getInt("deptno"));
                dept.setDname(rs.getString("dname"));
                dept.setLoc(rs.getString("loc"));
                emp.setDept(dept);
                list.add(emp);
            }
            rs.close();
            pstat.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }