Java和MySQL简建立连接

JDBC

JDBC插入多条数据

package com.easy.view;

import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Test6 {

    public static void main(String[] args) throws Exception {
        //如何插入多条数据，减少数据库的压力
        
        
        //4个数据源
        //1.驱动：连接数据库的一个不可或缺的类，可以在com.mysql.jdbc包下找到Driver这个类
        String driver = "com.mysql.jdbc.Driver";
        //2.jdbc连接数据库的路径
        String url = "jdbc:mysql://127.0.0.1:3306/myemployees";
        
        //3.用户名
        String userName = "root";
        //4.密码
        String psw = "123456";
        
        //五个执行步骤
        //1.加载驱动
        Class.forName(driver);
        
        //2.连接数据库
        //获取数据库连接
        //连接对象
        java.sql.Connection conn = DriverManager.getConnection(url, userName, psw);
        
        
        
        String selectSql = "insert into employees (last_name) values (?)";
        
        //3.预编译sql，获取预编译对象：预先编译，在执行之前编译成二进制
        PreparedStatement pre = conn.prepareStatement(selectSql);
        
        //Batch就是把所有的代码放在一块儿执行
        for (int i = 0; i < 10; i++) {
            //方法括号里面的1表示第一个？
            pre.setString(1, String.valueOf(i));
            pre.addBatch();
        }
        
        int[] i = pre.executeBatch();
        
        for (int j : i) {
            System.out.println(j);
        }
        
        conn.close();
    }
}

JDBC查询

package com.easy.view;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Test6 {

    public static void main(String[] args) throws Exception {
        // 如何执行查询操作

        //4个数据源
        //1.驱动：连接数据库的一个不可或缺的类，可以在com.mysql.jdbc包下找到Driver这个类
        String driver = "com.mysql.jdbc.Driver";
        //2.jdbc连接数据库的路径
        String url = "jdbc:mysql://127.0.0.1:3306/myemployees";
        
        //3.用户名
        String userName = "root";
        //4.密码
        String psw = "123456";
        
        //五个执行步骤
        //1.加载驱动
        Class.forName(driver);
        
        //2.连接数据库
        //获取数据库连接
        //连接对象
        java.sql.Connection conn = DriverManager.getConnection(url, userName, psw);
        
        String selectSql = "select last_name, department_id from employees where employee_id = 104";
        
        //3.预编译sql，获取预编译对象：预先编译，在执行之前编译成二进制
        PreparedStatement pre =  conn.prepareStatement(selectSql);
        
        //4.执行查询操作
        ResultSet res = pre.executeQuery();
        //next相当于游标
        while(res.next()) {
            //String也可以写Object
            System.out.println(res.getString("last_name"));
            System.out.println(res.getObject("department_id"));
        }
        
        //5.关闭连接对象
        conn.close();
    }
}

JDBC动态传参

package com.easy.view;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Test6 {

    public static void main(String[] args) throws Exception {
        //动态传参where
        
        
        //4个数据源
        //1.驱动：连接数据库的一个不可或缺的类，可以在com.mysql.jdbc包下找到Driver这个类
        String driver = "com.mysql.jdbc.Driver";
        //2.jdbc连接数据库的路径
        String url = "jdbc:mysql://127.0.0.1:3306/myemployees";
        
        //3.用户名
        String userName = "root";
        //4.密码
        String psw = "123456";
        
        //五个执行步骤
        //1.加载驱动
        Class.forName(driver);
        
        //2.连接数据库
        //获取数据库连接
        //连接对象
        java.sql.Connection conn = DriverManager.getConnection(url, userName, psw);
        
        String selectSql = "select last_name, department_id, salary from employees where last_name = ? or year = ?";
        
        //3.预编译sql，获取预编译对象：预先编译，在执行之前编译成二进制
        PreparedStatement pre =  conn.prepareStatement(selectSql);
        pre.setString(1, "Ernst");
        pre.setInt(2, 50);

        //4.执行查询操作
        ResultSet res = pre.executeQuery();
        //next相当于游标
        while(res.next()) {
            //String也可以写Object
            System.out.println(res.getString("last_name"));
            System.out.println(res.getObject("department_id"));
            System.out.println(res.getObject("salary"));
        }
        
        //5.关闭连接对象
        conn.close();
    }
}

JDBC回滚

package com.easy.view;

import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Test6 {

    public static void main(String[] args) throws Exception {
        //关闭自动提交，使用rollback
        //4个数据源
        //1.驱动：连接数据库的一个不可或缺的类，可以在com.mysql.jdbc包下找到Driver这个类
        String driver = "com.mysql.jdbc.Driver";
        //2.jdbc连接数据库的路径
        String url = "jdbc:mysql://127.0.0.1:3306/myemployees";
        
        //3.用户名
        String userName = "root";
        //4.密码
        String psw = "123456";
        
        //五个执行步骤
        //1.加载驱动
        Class.forName(driver);
        
        //2.连接数据库
        //获取数据库连接
        //连接对象
        java.sql.Connection conn = DriverManager.getConnection(url, userName, psw);
        
        //默认自动提交，修改为手动提交
        conn.setAutoCommit(false);
        
        String sql = "delete from employees where employee_id = ?";
        
        //3.预编译sql，获取预编译对象：预先编译，在执行之前编译成二进制
        PreparedStatement pre =  conn.prepareStatement(sql);
        pre.setInt(1, 104);
        //4.拿到数据库里执行
        //i：在数据库内执行了多少条数据
        int i = pre.executeUpdate();
        
        System.out.println(i);
        
        conn.rollback();
        //5.关闭连接对象
        conn.close();
    }
}

JDBC将数据库中的信息装入List

package com.easy.view;

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

public class Test6 {

    public static void main(String[] args) throws Exception {
        //如何将数据库中的数据装入List
        
        //4个数据源
        //1.驱动：连接数据库的一个不可或缺的类，可以在com.mysql.jdbc包下找到Driver这个类
        String driver = "com.mysql.jdbc.Driver";
        //2.jdbc连接数据库的路径
        String url = "jdbc:mysql://127.0.0.1:3306/myemployees";
        
        //3.用户名
        String userName = "root";
        //4.密码
        String psw = "123456";
        
        //五个执行步骤
        //1.加载驱动
        Class.forName(driver);
        
        //2.连接数据库
        //获取数据库连接
        //连接对象
        java.sql.Connection conn = DriverManager.getConnection(url, userName, psw);
        
        String selectSql = "select last_name, department_id, salary from employees";
        
        //3.预编译sql，获取预编译对象：预先编译，在执行之前编译成二进制
        PreparedStatement pre = conn.prepareStatement(selectSql);
        
        //4.执行查询操作
        ResultSet res = pre.executeQuery();
        
        List<Employees> list = new ArrayList<Employees>();
        //next相当于游标
        while(res.next()) {
            Employees employees = new Employees();
            employees.setLast_name(res.getString("last_name"));
            list.add(employees);
        }
        for (Employees employees : list) {
            System.out.println(employees.getLast_name());
        }
        System.out.println(list);
        
        //5.关闭连接对象
        conn.close();
    }
}

JDBC模糊查询

package com.easy.view;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Test6 {

    public static void main(String[] args) throws Exception {
        // 如何进行模糊查询
        
        
        //4个数据源
        //1.驱动：连接数据库的一个不可或缺的类，可以在com.mysql.jdbc包下找到Driver这个类
        String driver = "com.mysql.jdbc.Driver";
        //2.jdbc连接数据库的路径
        String url = "jdbc:mysql://127.0.0.1:3306/sale";
        
        //3.用户名
        String userName = "root";
        //4.密码
        String psw = "123456";
        
        //五个执行步骤
        //1.加载驱动
        Class.forName(driver);
        
        //2.连接数据库
        //获取数据库连接
        //连接对象
        java.sql.Connection conn = DriverManager.getConnection(url, userName, psw);
        
        String name = "金";
        //这种写法不常用
//      String selectSql = "select username from user where username like '%" + name + "%'";
        
        
        String selectSql = "select username from user where username like ?";
        
        //3.预编译sql，获取预编译对象：预先编译，在执行之前编译成二进制
        PreparedStatement pre = conn.prepareStatement(selectSql);
        
        pre.setString(1, "%" + name + "%");

        //4.执行查询操作
        ResultSet res = pre.executeQuery();
        //next相当于游标
        while(res.next()) {
            //String也可以写Object
            System.out.println(res.getString("username"));
        }
        
        //5.关闭连接对象
        conn.close();
    }
}

JDBC如何写多条SQL语句

package com.easy.view;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Test6 {

    public static void main(String[] args) throws Exception {
        //如何在1个JDBC中写入多个SQL语句
        //4个数据源
        //1.驱动：连接数据库的一个不可或缺的类，可以在com.mysql.jdbc包下找到Driver这个类
        String driver = "com.mysql.jdbc.Driver";
        //2.jdbc连接数据库的路径
        String url = "jdbc:mysql://127.0.0.1:3306/school";
        
        //3.用户名
        String userName = "root";
        //4.密码
        String psw = "123456";
        
        //五个执行步骤
        //1.加载驱动
        Class.forName(driver);
        
        //2.连接数据库
        //获取数据库连接
        //连接对象
        java.sql.Connection conn = DriverManager.getConnection(url, userName, psw);
        
        String sql = "select * from student where classid = ?";
        String sql2 = "select * from teacher where class_id = ?";
        
        //3.预编译sql，获取预编译对象：预先编译，在执行之前编译成二进制
        PreparedStatement pre = conn.prepareStatement(sql);
        PreparedStatement pre2 = conn.prepareStatement(sql2);
        pre.setInt(1, 1);
        pre2.setInt(1, 1);
        
         //4.执行查询操作
         ResultSet res = pre.executeQuery();
         ResultSet res2 = pre2.executeQuery();
                
        //next相当于游标
                while(res.next()) {
                    System.out.println(res.getObject("name"));
                }
                while(res2.next()) {
                    System.out.println(res2.getObject("name"));
                }
        //5.关闭连接对象
        conn.close();
    }
}

JDBC如何用res.nex()判断是否在数据库中有重复的值

package com.easy.view;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Test6 {

    public static void main(String[] args) throws Exception {
        // 如何用res.next()判断是否重复
        
        //4个数据源
        //1.驱动：连接数据库的一个不可或缺的类，可以在com.mysql.jdbc包下找到Driver这个类
        String driver = "com.mysql.jdbc.Driver";
        //2.jdbc连接数据库的路径
        String url = "jdbc:mysql://127.0.0.1:3306/sale";
        
        //3.用户名
        String userName = "root";
        //4.密码
        String psw = "123456";
        
        //五个执行步骤
        //1.加载驱动
        Class.forName(driver);
        
        //2.连接数据库
        //获取数据库连接
        //连接对象
        java.sql.Connection conn = DriverManager.getConnection(url, userName, psw);
        
        String selectSql = "select username from user where username = '刘吉'";
        
        //3.预编译sql，获取预编译对象：预先编译，在执行之前编译成二进制
        PreparedStatement pre =  conn.prepareStatement(selectSql);
        
        //4.执行查询操作
        ResultSet res = pre.executeQuery();
        //next相当于游标
        //res.next()就是查看sql语句的查询结果，如果有值，则返回true；如果没有，则返回false
        if (res.next()) {
            System.out.println("用户名重复");
        }
        else {
            System.out.println("用户名不重复");
        }
        
        
        //5.关闭连接对象
        conn.close();
    }
}

JDBC手动提交

package com.easy.view;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Test6 {

    public static void main(String[] args) throws Exception {
        //关闭自动提交
        //4个数据源
        //1.驱动：连接数据库的一个不可或缺的类，可以在com.mysql.jdbc包下找到Driver这个类
        String driver = "com.mysql.jdbc.Driver";
        //2.jdbc连接数据库的路径
        String url = "jdbc:mysql://127.0.0.1:3306/myemployees";
        
        //3.用户名
        String userName = "root";
        //4.密码
        String psw = "123456";
        
        //五个执行步骤
        //1.加载驱动
        Class.forName(driver);
        
        //2.连接数据库
        //获取数据库连接
        //连接对象
        java.sql.Connection conn = DriverManager.getConnection(url, userName, psw);
        //默认自动提交
        conn.setAutoCommit(false);
        
        String sql = "delete from employees where employee_id = ?";
        
        //3.预编译sql，获取预编译对象：预先编译，在执行之前编译成二进制
        PreparedStatement pre =  conn.prepareStatement(sql);
        pre.setInt(1, 121);
        //4.拿到数据库里执行
        //i：在数据库内执行了多少条数据
        int i = pre.executeUpdate();
        
        System.out.println(i);
        
        conn.commit();
        //5.关闭连接对象
        conn.close();
    }
}

JDBC增删改

package com.easy.view;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Test6 {

    public static void main(String[] args) throws Exception {
        //如何执行增删改的操作
        //4个数据源
        //1.驱动：连接数据库的一个不可或缺的类，可以在com.mysql.jdbc包下找到Driver这个类
        String driver = "com.mysql.jdbc.Driver";
        //2.jdbc连接数据库的路径
        String url = "jdbc:mysql://127.0.0.1:3306/myemployees";
        
        //3.用户名
        String userName = "root";
        //4.密码
        String psw = "123456";
        
        //五个执行步骤
        //1.加载驱动
        Class.forName(driver);
        
        //2.连接数据库
        //获取数据库连接
        //连接对象
        java.sql.Connection conn = DriverManager.getConnection(url, userName, psw);
        
        String sql = "delete from employees where employee_id = 120";
        
        //3.预编译sql，获取预编译对象：预先编译，在执行之前编译成二进制
        PreparedStatement pre =  conn.prepareStatement(sql);
        
        //4.拿到数据库里执行
        //i：在数据库内执行了多少条数据
        int i = pre.executeUpdate();
        
        System.out.println(i);
        //5.关闭连接对象
        conn.close();
    }
}

MyBatis

main方法里面写

//获取一个sql的会话，连接数据库
SqlSession sqlSession = MyBatisTools.getSqlSession();

//获取接口对象，以class结尾
//Mapper叫作映射
EmployeesMapper emp = sqlSession.getMapper(EmployeesMapper.class);

//中间用emp就可以调用EmployeesMapper接口里面的方法

//提交：Mybatis不是自动提交
sqlSession.commit();
//关闭会话
sqlSession.close();

查询最高工资

//⑤查询部门最高工资，取出的max(salary)不可能用一个属性来接，因此定义int类型就好了
    int selectMaxsalary();

<!-- //⑤查询部门最高工资，取出的max(salary)不可能用一个属性来接，因此定义int类型就好了 -->
    <select id = "selectMaxsalary" resultType = "java.lang.Integer">
        select max(salary) from employees;
    </select>

//      int i = emp.selectMaxsalary();
//      System.out.println(i);

传多个参数用对象

//④传多个参数，用对象。保证实体类里面的属性和数据库中表的字段名一致
    Employees selectBysalaryAndyear(Employees employees);

    <!-- //④传多个参数，用对象 -->
    <select id = "selectBysalaryAndyear"  parameterType = "com.easyup.model.Employees" resultType = "com.easyup.model.Employees">
        <!-- {}里面写实体类里面属性的名字 -->
        select last_name from employees where salary = #{salary} and year = #{year};
    </select>

Employees employees = new Employees();
//      employees.setYear(22);
//      employees.setSalary(42000);
//      Employees i = emp.selectBysalaryAndyear(employees);
//      System.out.println(i.getLast_name());

动态SQL

    //⑥动态SQL
    //（1）where if，可以省略前面的and
    List<Employees> selectByNameAndJobId(Employees emp);
    //（2）set if：可以省略掉后面的逗号
    int updateLastName(Employees emp);
    //（3）foreach————SQL:list(1,2,3,4)：遍历集合
    List<Employees> selectEmpByids(List<Integer> ids);
    //（4）<sql><include>标签可以将select后面要查询的语句重复使用，不必每次都写
    Employees selectEmpById(Employees emp);


<!-- //⑥动态SQL
    //（1）where if，可以省略前面的and -->
    <select id = "selectByNameAndJobId" resultType = "com.easyup.model.Employees">
        select last_name, job_id
        from employees
        <where>
        <!-- where if 是去掉字if里面前面的and的，但是不能删除if里面后面的and -->
        <!-- where if 至少传入一个参数 -->
        
            <if test = "last_name != null and last_name != ''">
                last_name = #{last_name}
            </if>
            
            <if test= "job_id != null and job_id != ''">
                and job_id = #{job_id}
            </if>
        </where>
    </select>


    <!-- //（2）set if：可以省略掉后面的逗号 -->
    <update id = "updateLastName" >
        update employees
        <!--  删除语句后面的逗号 -->
        <set>
            <if test = "last_name != null and last_name != ''">
                last_name = #{last_name},
            </if >
            
            <if test= "job_id != null and job_id != ''">
                job_id = #{job_id}
            </if>
        </set>
        where employee_id = #{employee_id}
    </update>
    
    <!-- //（3）foreach————SQL:list(1,2,3,4)：遍历集合 -->
    <!-- 此处参数是int类型的集合，可以写参数也可以不写 -->
    <select id = "selectEmpByids" parameterType = "java.lang.Integer" resultType = "com.easyup.model.Employees">
        select last_name
        from employees
        where employee_id
        in
        <！-- item表示in（）里面的内容 -->
        <foreach collection = "list" open = "(" close = ")" separator = "," item = "item">
            #{item}
        </foreach>
    </select>
    
    
    <!-- //（4）<sql><include>标签可以将select后面要查询的语句重复使用，不必每次都写 -->
    <sql id = "empSql">
    last_name, job_id, salary, employee_id, year
    </sql>

    <select id = "selectEmpById" parameterType = "com.easyup.model.Employees"  resultType = "com.easyup.model.Employees">
        select 
        <include refid = "empSql"></include>
        from employees
        where employee_id = #{employee_id}
    </select>


//      Employees empl = new Employees(); 
        
        //where if方法的调用
//      empl.setLast_name("Ernst");
//      empl.setJob_id("IT_PROG");
        
//      List<Employees> list = emp.selectByNameAndJobId(empl);
//      for (Employees employees : list) {
//          System.out.println(employees.getLast_name());
//          System.out.println(employees.getJob_id());
//      }
        
        //set if方法的调用
//      empl.setLast_name("Pataballa11111");
//      empl.setEmployee_id(106);
//      int i = emp.updateLastName(empl);
//      System.out.println(i);
        
        //foreach方法的调用
//      List<Integer> list = new ArrayList<Integer>();
//      list.add(105);
//      list.add(106);
//      list.add(107);
//      list.add(108);
//      List<Employees> selectEmpByids = emp.selectEmpByids(list);
//      for (Employees employees : selectEmpByids) {
//          System.out.println(employees.getLast_name());
//      }
        
        //<include>方法的调用
//      empl.setEmployee_id(105);
//      Employees selectEmpById = emp.selectEmpById(empl);
//      System.out.println(selectEmpById);

动态传参需要写入参数

//③动态传参需要写入参数
    Employees selectById(int employee_id);


<!-- //③动态传参需要写入参数 -->
    <!-- 动态传参 -->
    <select id = "selectById"  parameterType = "java.lang.Integer" resultType = "com.easyup.model.Employees">
        <!-- {}里面写实体类里面属性的名字，或者参数的名字 -->
        select last_name from employees where employee_id = #{employee_id};
    </select>


//查看一个字段的单个信息
//      Employees employees= emp.selectById(108);
//      System.out.println(employees.getLast_name());

多表查询：一对多

    //多表查询：查询一个表和另一个表中相同字段有相同值，因此主表也有多个值，需要用List来接
    //（2）一对多
    List<Departments> selectDepEmp();


    <!-- //（2）一对多 -->
    <resultMap type = "com.easyup.model.Departments" id = "DepMap">
        <result column = "department_name" property = "departmentName"/>
        <collection property = "empList" ofType = "com.easyup.model.Employees">
            <result column = "last_name" property = "lastName"/>
        </collection>
    </resultMap>

    <select id = "selectDepEmp" resultMap = "DepMap">
        select departments.department_name, employees.last_name
        from departments, employees
        where departments.department_id = employees.department_id;
    </select>


        //一对多
//      List<Departments> list = dep.selectDepEmp();
//      for (Departments d : list) {
//          System.out.println(d.getDepartmentName());
//          for (Employees e : d.getEmpList()) {
//              System.out.println(e.getLastName());
//          }
//      }

多表查询：一对一

    //⑨多表查询
    //（1）一对一：两张表分别由同一字段。要明白是从谁出发去找谁。
    //注意事项：①分清楚多表查询中的一对一和懒加载的一对一的区别：多表查询的一对一只需在该类中有另外一个类的变量，②如果
    //从employees找card
    List<Employees> selectCard();
    //从card找employees
    List<Card> selectCardEmp();


    <!-- //⑨多表查询 -->
    <!-- //（1）一对一：两张表分别由同一字段。要明白是从谁出发去找谁。 -->
    <!-- 从Employees去取Card -->
    <resultMap type = "com.easyup.model.Employees" id = "EmpCard">
        <result column = "last_name" property = "lastName"/>
        <!-- 一对一的关系 -->
        <!-- 从员工表出发，和card是一对一的关系 -->
        <association property = "card" javaType = "com.easyup.model.Card">
            <result column = "card_num" property = "num"/>
        </association>
    </resultMap>
    
    <select id = "selectCard" resultMap = "EmpCard">
        select employees.last_name, card.card_num, employees.salary
        from employees,card
        where employees.card_id = card.id;
    </select>
    
    
    <!-- 从Card去取Employees -->
    <resultMap type = "com.easyup.model.Card" id = "CardEmp">
        <result column = "card_num" property = "num"/>
        <result column = "id" property = "Id"/>
        <!-- 一对一的关系 -->
        <!-- 从员工表出发，和card是一对一的关系 -->
        <!-- association里面的property写的是Employees里面的一个成员变量 -->
        <association property = "employees" javaType = "com.easyup.model.Employees">
            <result column = "last_name" property = "lastName"/>
            <result column = "salary" property = "salary"/>
            <result column = "year" property = "year"/>
            <result column = "employee_id" property = "employee_id"/>
        </association>
    </resultMap>
    
    <select id = "selectCardEmp" resultMap = "CardEmp">
        select employees.last_name, card.card_num, employees.salary, employees.year, employees.employee_id
        from employees,card
        where employees.card_id = card.id;
    </select>


        //从员工表找Card
//      List<Employees> selectCard = emp.selectCard();
//      for (Employees employees : selectCard) {
//          System.out.println(employees);
//      }
        
        //从Card找员工表
//      List<Card> selectCardEmp = emp.selectCardEmp();
//      
//      for (Card card : selectCardEmp) {
//          System.out.println(card);
//      }

刚插入一条数据，取出id

    //⑩刚插进去一条信息，然后取出主键：只能用于使用对象作为参数的情况
    int insertEmp(Employees emp);


    <!-- //⑩刚插进去一条信息，然后取出主键 -->
    <insert id = "insertEmp" parameterType = "com.easyup.model.Employees">
        <selectKey order = "AFTER" resultType = "java.lang.Integer" keyProperty = "employee_id">
            //固定方法名
            select last_insert_id()
        </selectKey>
            insert into employees
            (last_name) values (#{lastName})
    </insert>


        //向数据库中添加数据，然后马上取到对应的主键
//      Employees employees = new Employees();
//      employees.setLastName("小明33333333333");
//      int insertEmp = emp.insertEmp(employees);
//      System.out.println("执行了：" + insertEmp);
//      System.out.println("id：" + employees.getEmployee_id());

高级映射

    //⑦高级映射
    //增删改不需要结果集映射，只有查询的时候需要用到结果集映射
    Employees selectById11();


    <!-- //⑦高级映射 -->
    <!--//增删改不需要结果集映射，只有查询的时候需要用到结果集映射 -->
    <resultMap type = "com.easyup.model.Employees" id = "empMap">
        <result column = "last_name" property = "lastName"/>
    </resultMap>
    <select id = "selectById11" resultMap = "empMap">
        select <include refid = "empSql"></include>
        from employees 
        where employee_id = 108;
    </select>


//      Employees selectById11 = emp.selectById11();
//      System.out.println(selectById11);

懒加载，一对一

//懒加载，又叫延迟加载、按需加载
//把多表查询，拆成单表查询，比如从员工表找card表，只有调用getcard的时候才会去查card表
//注意事项：①Employees里面要有Card类型的card；②主查询要查询那个与子查询相关联的字段；③子查询的参数要写与主查询相关联的字段；④log文件要放在src的目录下

    //主查询的方法定义
    Employees selectCardlazy(@Param("empId")int employeeId);


    //主查询的XML
    <resultMap type = "com.easyup.model.Employees" id = "cc">
        <result column = "last_name" property = "lastName"/>
        <association property = "card" javaType = "com.easyup.model.Card"
        select = "com.easyup.mapper.CardMapper.selectCard" column = "card_id"
        fetchType = "lazy">
        </association>
    </resultMap>
    
    
    <select id = "selectCardlazy" resultMap = "cc">
        select last_name,card_id
        from employees
        where employee_id = #{empId}
    </select>


    //子查询的方法定义
    Card selectCard(@Param("id")int id);



    //子查询的XML
    <resultMap type = "com.easyup.model.Card" id = "aa">
        <result column = "id" property = "Id"/>
        <result column = "card_num" property = "num"/>
    </resultMap>

    <select id = "selectCard" resultMap = "aa">
        select id,card_num
        from card
        where id = #{id}
    </select>


        //调用方法
        Employees selectCardlazy = emp.selectCardlazy(104);
//      System.out.println(selectCardlazy.getLastName());
        System.out.println(selectCardlazy.getCard().getNum());

懒加载：一对多

//懒加载：一对多
//注意事项：①department类里面要有employees泛型的List；②collection里面的property要写empList；③子查询的筛选条件要写与主查询相关联的字段；④log文件要放在src的目录下

//从department查员工表，用懒加载
Departments selectFromDepToEmp(@Param("depId")int depId);


<resultMap type = "com.easyup.model.Departments" id = "Deplazy">
<result column = "department_name" property = "departmentName"/>
<result column = "department_id" property = "departmentId"/>
<collection property="empList" ofType = "com.easyup.model.Employees"
select = "com.easyup.mapper.EmployeesMapper.selectFromDepToEmp" column = "department_id"
fetchType = "lazy"
></collection>
</resultMap>

<select id = "selectFromDepToEmp" resultMap = "Deplazy">
    select department_name, department_id
    from departments
    where department_id = #{depId}
</select>


//从部门表找员工表的懒加载
List<Employees> selectFromDepToEmp(@Param("depId")int depId);


<resultMap type = "com.easyup.model.Employees" id = "hh">
    <result column = "last_name" property = "lastName"/>
</resultMap>

<select id = "selectFromDepToEmp" resultMap = "hh">
    select last_name
    from employees
    where department_id = #{depId}
</select>


    Departments selectFromDepToEmp = dep.selectFromDepToEmp(60);
    System.out.println(selectFromDepToEmp.getDepartmentName());
    
    for(Employees employees : selectFromDepToEmp.getEmpList()) {
        System.out.println(employees.getLastName());
    }

懒加载：一对多对一

//根据job_id可以找到多个员工
//接口中的方法定义
List<Jobs> selectJobsEmpLazy(@Param("jobId")String jobId);

//XML的写法
<resultMap type = "com.easyup.model.Jobs" id = "ff">
    <result column = "job_id" property = "jobId"/>
    <result column = "job_title" property = "jobTitle"/>
    <collection property = "empList" ofType = "com.easyup.model.Employees"
        select = "com.easyup.mapper.EmployeesMapper.selectFromJobsToEmployees" column = "job_id"
        fetchType = "lazy">
    </collection>
</resultMap>

<select id = "selectJobsEmpLazy" resultMap = "ff">
    select job_id, job_title
    from jobs
    where job_id = #{jobId}
</select>



//从Jobs表找员工表的懒加载
//方法的定义
List<Employees> selectFromJobsToEmployees(@Param("JobId")String jobId);
//XML的写法
<resultMap type = "com.easyup.model.Employees" id = "gg">
    <result column = "last_name" property = "lastName"/>
    <result column = "salary" property = "salary"/>
    <result column = "employee_id" property = "employee_id"/>
    <association property = "jobGrades" javaType = "com.easyup.model.JobGrades"
    select = "com.easyup.mapper.JobGradesMapper.selectSalaryLevel" column = "salary"
    fetchType = "lazy">
    </association>
</resultMap>

<select id = "selectFromJobsToEmployees" resultMap = "gg">
    select last_name, salary
    from employees
    where job_id = #{jobId}
</select>


//根据查到的员工的工资，去工资登记表里面查对应的工资等级
//方法的定义
JobGrades selectSalaryLevel(@Param("salary")int salary);

//XML的写法
<resultMap type = "com.easyup.model.JobGrades" id = "kk">
    <result column = "grade_level" property = "gradeLevel"/>
</resultMap>

<select id = "selectSalaryLevel" resultMap = "kk">
    select grade_level
    from job_grades
    where #{salary} between lowest_sal and highest_sal
</select>


    //main方法的写法
    //用jobs查员工表一对多
    List<Jobs> selectJobsEmpLazy = job.selectJobsEmpLazy("IT_PROG");
    for (Jobs jobs : selectJobsEmpLazy) {
        System.out.println(jobs.getJobId() + "  " + jobs.getJobTitle());
        for (Employees ee : jobs.getEmpList()) {
            System.out.println(ee.getLastName() + "  " + ee.getSalary());
            System.out.println(ee.getJobGrades().getGradeLevel());
        }
    }

模糊查询

    //⑧模糊查询
    //（1）老师教的第一种
    List<Employees> selectLike(String name);
    //（2）我们的第二种
    List<Employees> selectLikeName(String name);


    <!-- //⑧模糊查询-->
    <!-- //（1）老师教的第一种 -->
    <select id = "selectLike" parameterType = "java.lang.String" resultMap = "empMap">
        select last_name
        from employees
        where last_name like concat('%', #{name}, '%');
    </select>
    
    <!-- //（2）我们的第二种 -->
    <select id = "selectLikeName" parameterType = "java.lang.String" resultMap = "empMap">
        select <include refid = "empSql"></include>
        from employees
        where last_name like #{lastName};
    </select>


//      List<Employees> selectLike = emp.selectLike("Er");
//      for (Employees employees : selectLike) {
//          System.out.println(employees);
//      }
        
//      String name = "sti";
//      List<Employees> selectLikeName = emp.selectLikeName("%" + name + "%");
//      for (Employees employees : selectLikeName) {
//          System.out.println(employees);

取出一个表中一个字段所有的数据要用List来接

//②取出一个表中一个字段所有的数据要用List来接
    List<Employees> selectAll();


<!-- //②取出一个表中一个字段所有的数据要用List来接 -->
    <!-- 查询所有信息的时候不能写* -->
    <select id = "selectAll" resultType = "com.easyup.model.Employees">
        select last_name from employees;
    </select>


//查看一个字段的所有信息
//      List<Employees> list = emp.selectAll();
//      
//      for (Employees employees : list) {
//          System.out.println(employees.getLast_name());
//      }

增删改

    //①增删改的返回值l类型都是int
    int add();
    int delete();
    int update();


    <!-- id表示和哪一个方法进行联系 -->
    <!-- //①增删改的返回值都是int -->
    <insert id = "add">
        insert into employees (last_name)
        values ('小明22222222')
    </insert>
    
    <delete id = "delete">
        delete from employees where employee_id = 152;
    </delete>
    
    <update id = "update">
        update employees set last_name = '大吉哥' where employee_id = 152;
    </update>


//调用接口里面的方法，并接取返回值
//      int i = emp.add();
//      int j = emp.update();
//      int k = emp.delete();

注解查询：查询（一个参数，多个参数均可）

//注解查询：查询一个字段
@Select("select id, card_num from card where id = #{id}")
@Results({
    @Result(column = "card_num", property = "num")
})
Card selectCardId(@Param("id")int id);


    //查询
//      Card selectCardId = car.selectCardId(1);
//      System.out.println(selectCardId.getNum());


//注解查询：查询多个字段
@Select("select employee_id, last_name, salary, year, job_id from employees where employee_id = #{employee_id}")
@Results({
    @Result(column = "employee_id", property = "employee_id"),
    @Result(column = "last_name", property = "lastName"),
    @Result(column = "salary", property = "salary"),
    @Result(column = "year", property = "year"),
    @Result(column = "job_id", property = "job_id")
})
Employees selectemployees(@Param("employee_id") int employee_id);


//      Employees selectemployees = emp.selectemployees(104);
//      System.out.println(selectemployees);

注解查询：多个参数，一半注解，一半XML

//注解查询：多个参数，一半注解，一半XML
int insertEmp1(@Param("salary") int salary, @Param("lastName")String last_name);


<insert id = "insertEmp1">
        insert into employees
        (last_name, salary) values (#{lastName}, #{salary})
</insert>


//      int insertEmp1 = emp.insertEmp1(80000, "小花1111111111");
//      System.out.println(insertEmp1);

注解查询：增删改（无参）

    //注解查询
    @Insert("insert into card (card_num) values (211302)")
    int insertCard();
    
    @Delete("delete from card where card_num = 211302")
    int deleteCard();
    
    @Update("update card set card_num = 777 where id = 3")
    int updateCard();


        //注解查询：添加数据
//      int insertCard = car.insertCard();
//      System.out.println(insertCard);
        
        //注解查询：删除数据
//      int deleteCard = car.deleteCard();
//      System.out.println(deleteCard);
        
        //注解查询：更新数据
//      int updateCard = car.updateCard();
//      System.out.println(updateCard);

注解查询：增删改：多个参数

    //多个参数的写法：
    //更新
    @Update("update card set card_num = #{num} where id = #{id}")
    int updateCard2(@Param("id") int id, @Param("num") int num);
    //删除
    @Delete("delete from card where id = #{id} and card_num = num")
    int deleteCard2(@Param("id") int id, @Param("num") int num);
    //添加
    @Insert("insert into card (id, card_num) values (#{id}, #{num})")
    int insertCard3(@Param("id") int id, @Param("num") int num);


        //多个参数
        //更新
//      int updateCard2 = car.updateCard2(1, 1111111111);
//      System.out.println(updateCard2);

注解查询：增删改：使用对象动态传参

    //动态传参：使用对象
    //更新
    @Update("update card set card_num = #{num} where id = #{id}")
    int updateCard1(Card card);
    //删除
    @Delete("delete from card where id = #{id}")
    int deleteCard1(Card card);
    //添加
    @Insert("insert into card (card_num) values (#{num})")
    int insertCard1(Card card);


        //注解查询——动态传参：更新数据
//      Card card = new Card();
//      card.setId(1);
//      card.setNum(9);
//      int updateCard1 = car.updateCard1(card);
//      System.out.println(updateCard1);
        
        //注解查询——动态传参：删除数据
//      Card card = new Card();
//      card.setId(3);
//      int deleteCard1 = car.deleteCard1(card);
//      System.out.println(deleteCard1);
        
        //注解查询——动态查询：添加数据
//      Card card = new Card();
//      card.setNum(1203);
//      int insertCard1 = car.insertCard1(card);
//      System.out.println(insertCard1);

存储过程

INOUT参数的使用以及PLSQL

-- 传入一个参数，如果大于3则返回100，否则返回500
-- 用户变量作用于当前会话，局部变量作用于begin和end之间
-- INOUT的使用
delimiter $$
create procedure my_pro8(INOUT in_param varchar(20))
begin
    declare param varchar(20);
    if in_param > 3
    then set param := 100;
    else set param := 500;
    end if;
    
    set in_param := param;

end $$
delimiter ;

set @param = 3;
call my_pro8(@param);

select @param;



-- PLSQL while
-- PLSQL：过程语言，在存储过程中使用的语言，也就是和Java一样有循环、分支这些语句，但是很少用到

delimiter $$
create procedure my_pro11_while()
begin
    declare i int;
    set i := 0;
    
    while i < 10 do
    
    insert into boss (name) values (i);
        set i = i + 1;
        end while;


end $$
delimiter ;

call my_pro11_while();

select *
from boss;

既有输入又有输出参数的存储过程

-- 输出模式

delimiter $$
create procedure my_por2( IN in_param  varchar(30),
                 OUT out_param varchar(30))
-- 存储过程体
begin
-- 查询员工表中id为103号员工的信息
select last_name into out_param
from employees
where employee_id = in_param;
end $$
delimiter ;


set @param := null;
call my_por2(103, @param);

select @param;

有多个输出参数的存储过程

-- 如果要返回一行多列的数据 
delimiter $$
create procedure my_por5( IN in_param  varchar(30),
              OUT out_param varchar(30),
                          OUT out_param1 varchar(30),
                          OUT out_param2 varchar(30))

begin
select d.department_name, d.department_id, d.location_id   into out_param, out_param1, out_param2
from employees as e inner join departments as d
on e.department_id = d.department_id
and e.last_name = in_param;
end $$
delimiter ;

set @my_out1 := null;
set @my_out2 := null;
set @my_out3 := null;
call my_por5('Hunold', @my_out1, @my_out2, @my_out3);

select @my_out1, @my_out2,@my_out3;

只有输入参数的存储过程

delimiter $$
create procedure my_por1( IN in_param  varchar(30))
-- 存储过程体
begin
-- 查询员工表中id为103号员工的信息
select *
from employees
where employee_id = in_param;
end $$
delimiter ;

-- 调用存储过程
call my_por1(103);

子查询

from后子查询

-- from后子查询：子句当成一个表来使用，要有字段名字和表名
-- 一般将分组查询的表格作为from后子查询的子表
-- select后子查询，两表之间的关联写在子句里面，from后子查询两表之间的关联写在外面

-- 每个部门的平均工资的工资等级
select *
from (select avg(salary) as ag
from employees
group by department_id) as ag_emp inner join job_grades as j
on ag_emp.ag between j.lowest_sal and j.highest_sal;


-- 查询员工工资比本部门平均工资高（不包含临界值）的员工的员工号，姓名和工资
SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNER JOIN (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary > ag_dep.ag;

select后子查询

-- select后子查询：子句当成一个字段来使用

-- 利用部门表查询每个部门的员工个数、部门编号、部门名称
select d.department_id, d.department_name, 
(select count(employee_id)
from employees
where d.department_id = employees.department_id)
from departments as d;

标量子查询

-- 标量子查询：子查询的结果是一个标量
-- 查询工资比105号员工高的所有人信息

select *
from employees
where salary > (select salary
from employees
where employee_id = 105);

行子查询

-- 行子查询：子句返回结果集是一行多列
-- 行子查询可以使用等号，但是不可以使用大于、大于等于、小于、小于等于这类符号
-- 查询与103号年龄和工资都相同的人的信息alter

select *
from employees
where (year, salary) =
(select year, salary
from employees
where employee_id = 1);

列子查询

-- 列子查询：子查询的结果是一列多行
-- 查询比103号或104号员工高的员工的信息

select *
from employees
where salary > any(select salary
from employees
where employee_id in(103,104));

相关子查询

-- 相关子查询：子句有值的话返回true，主句才会进行比对和显示。如果为空的话就不会显示。
-- 相关子查询的子表写在where后面
-- 相关子查询的效率特别高，输出只有两个布尔值，ture和false

-- 查询部门表是否存在年龄大于21岁的员工，如果存在则输出该部门的信息。
select *
from departments
where exists (
select *
from employees
where employees.department_id = departments.department_id
and year > 21
);

其它

不等值内连接

-- 不等值内连接
-- 查询员工所对应的工资等级alter
select j.grade_level,e.last_name
from employees as e inner join job_grades as j
on e.salary between j.lowest_sal and j.highest_sal
and e.last_name = 'Hunold';

将数据按照第一个排序，如果第一个相同的情况下按照第二个排序

select * 
from rank 
order by times asc, time asc;

游标的使用

-- 游标

DELIMITER $$
create PROCEDURE my_pro12()
begin
-- 声明一个标志done， 用来判断游标是否遍历完
DECLARE done INT DEFAULT 0;
-- 声明一个变量，用来存放从游标中提取的数据
-- 特别注意这里的名字不能与由游标中使用的列明相同，
-- 否则得到的数据都是NULL
DECLARE lastName varchar(30) DEFAULT NULL;
DECLARE salaryParam int DEFAULT NULL;
-- 声明游标对应的 SQL 语句
DECLARE cur CURSOR FOR
select last_name, salary from employees;
-- 在游标循环到最后会将 done 设置为 1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 执行查询
open cur;
-- 遍历游标每一行
REPEAT
-- 把一行的信息存放在对应的变量中
FETCH cur INTO lastName, salaryParam;
if not done then
-- 这里就可以使用 lastName， salaryParam 对应的信息了
select lastName, salaryParam;
end if;
UNTIL done END REPEAT;
CLOSE cur;
end $$
DELIMITER ;

call my_pro12();