第二章 MyBatis入门

1.Junit的使用

        在maven中导入junit依赖：

    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>

注解的用法： 

@Test:表示一个可以独立运行的单元测试方法；

@Before:在每个单元测试方法执行之前运行;

@After：在每个单元测试方法之后运行；

@BeforeClass:使用该注解的方法必须是静态方法，会在所有单元测试方法运行之前执行一次；

@AfterClass:必须是静态方法，会在所有单元测试方法之后运行一次。

 2. MyBatis

2.1 ORM思想

        ORM(对象关系映射 Object Relational Mapping)是通过描述对象和数据库之间映射的元数据，将程序中的对象自动持久化到关系数据库中。

通过自定义注解和反射实现简单的orm:

自定义注解：

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;


@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ColumnInformation {
    String colName();
    String colType();
    int colSize() default 20;
}

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;


@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface PrimaryKey {
    boolean isAutoIncrement();
}

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface Table {
    String tableName();
}

给实体对象的添加注解：

student：

import com.wen.annotation.ColumnInformation;
import com.wen.annotation.PrimaryKey;
import com.wen.annotation.Table;

import java.util.Date;

@Table(tableName = "student")
public class Student {
    @PrimaryKey(isAutoIncrement = true)
    @ColumnInformation(colName = "s_id",colType = "int",colSize = 11)
    private Integer s_id;

    @ColumnInformation(colName = "s_name",colType = "varchar",colSize = 10)
    private String s_name;

    @ColumnInformation(colName = "s_birth",colType = "date")
    private Date s_birth;

    @ColumnInformation(colName = "s_sex",colType = "char",colSize = 1)
    private String s_sex;

    public Student() {
    }

    public Student(Integer s_id, String s_name, Date s_birth, String s_sex) {
        this.s_id = s_id;
        this.s_name = s_name;
        this.s_birth = s_birth;
        this.s_sex = s_sex;
    }

    public Student(String s_name, Date s_birth, String s_sex) {
        this.s_name = s_name;
        this.s_birth = s_birth;
        this.s_sex = s_sex;
    }

    public Integer getS_id() {
        return s_id;
    }

    public void setS_id(Integer s_id) {
        this.s_id = s_id;
    }

    public String getS_name() {
        return s_name;
    }

    public void setS_name(String s_name) {
        this.s_name = s_name;
    }

    public Date getS_birth() {
        return s_birth;
    }

    public void setS_birth(Date s_birth) {
        this.s_birth = s_birth;
    }

    public String getS_sex() {
        return s_sex;
    }
    public void setS_sex(String s_sex) {
        this.s_sex = s_sex;
    }

    @Override
    public String toString() {
        return "Student{" +
                "s_id=" + s_id +
                ", s_name='" + s_name + '\'' +
                ", s_birth=" + s_birth +
                ", s_sex='" + s_sex + '\'' +
                '}';
    }
}

score：

import com.wen.annotation.ColumnInformation;
import com.wen.annotation.PrimaryKey;
import com.wen.annotation.Table;


@Table(tableName = "score")
public class Score {
    @PrimaryKey(isAutoIncrement = true)
    @ColumnInformation(colName = "s_id",colType = "int",colSize = 20)
    private Integer s_id;

    @ColumnInformation(colName = "c_id",colType = "int",colSize = 20)
    private Integer c_id;

    @ColumnInformation(colName = "s_score",colType = "decimal",colSize = 3)
    private Double s_score;

    public Score() {
    }

    public Score(Integer s_id, Integer c_id, Double s_score) {
        this.s_id = s_id;
        this.c_id = c_id;
        this.s_score = s_score;
    }

    public Integer getS_id() {
        return s_id;
    }

    public void setS_id(Integer s_id) {
        this.s_id = s_id;
    }

    public Integer getC_id() {
        return c_id;
    }

    public void setC_id(Integer c_id) {
        this.c_id = c_id;
    }

    public Double getS_score() {
        return s_score;
    }

    public void setS_score(Double s_score) {
        this.s_score = s_score;
    }

    @Override
    public String toString() {
        return "Score{" +
                "s_id=" + s_id +
                ", c_id=" + c_id +
                ", s_score=" + s_score +
                '}';
    }
}

orm实现：

inteeface:

import java.util.List;
import java.util.Map;

public interface IGenericDao<T> {
    /**
     * 查询全部数据
     * @param t
     * @return
     */
    List<Map<String,Object>> listAll(T t);

    /**
     * 插入数据
     * @param t
     * @return
     */
    int insert(T t);

    /**
     * 修改数据
     * @param t
     * @return
     */
    int update(T t);

    /**
     * 删除数据
     * @param t
     * @return
     */
    int delete(T t);
}

impl:

import com.wen.annotation.ColumnInformation;
import com.wen.annotation.PrimaryKey;
import com.wen.annotation.Table;
import com.wen.orm.dao.IGenericDao;
import com.wen.orm.util.BaseDao;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.List;
import java.util.Map;


public class GenericDaoImpl<T> implements IGenericDao<T> {
    private String getSql(Class t,String sign) {
        StringBuilder sql = new StringBuilder();
        if(t.isAnnotationPresent(Table.class)){
            Table table = (Table) t.getAnnotation(Table.class);
            Field[] declaredFields = t.getDeclaredFields();

            StringBuilder colSql = new StringBuilder();

            int count = 0;
            String colNameByPrimaryKey ="";
            switch (sign){
                case "selectAll":
                    sql.append("select ");
                    for (Field f:declaredFields) {
                        ColumnInformation annotation = f.getAnnotation(ColumnInformation.class);
                        colSql.append(annotation.colName()+",");
                    }
                    sql.append(colSql.substring(0,colSql.length()-1)).append(" from ").append(table.tableName());
                    break;
                case "insert":
                    sql.append("insert into ");
                    sql.append(table.tableName());
                    sql.append("(");

                    for (Field f:declaredFields) {
                        if(f.isAnnotationPresent(PrimaryKey.class)){
                            continue;
                        }
                        ColumnInformation annotation = f.getAnnotation(ColumnInformation.class);
                        colSql.append(annotation.colName()+",");
                        count++;
                    }
                    sql.append(colSql.substring(0,colSql.length()-1));
                    sql.append(") value (?");
                    for (int i = 0; i < count-1; i++) {
                        sql.append(",?");
                    }
                    sql.append(")");
                    break;
                case "update":
                    sql.append("update ");
                    sql.append(table.tableName());
                    sql.append(" set ");
                    for (Field f:declaredFields) {
                        ColumnInformation annotation = f.getAnnotation(ColumnInformation.class);
                        if(f.isAnnotationPresent(PrimaryKey.class)){
                            colNameByPrimaryKey = annotation.colName();
                            continue;
                        }
                        colSql.append(annotation.colName()+"= ?,");
                    }
                    sql.append(colSql.substring(0,colSql.length()-1));
                    sql.append(" where "+colNameByPrimaryKey+"= ?");
                    break;
                case "delete":
                    sql.append("delete from ");
                    sql.append(table.tableName());
                    for (Field f:declaredFields) {
                        ColumnInformation annotation = f.getAnnotation(ColumnInformation.class);
                        if(f.isAnnotationPresent(PrimaryKey.class)){
                            colNameByPrimaryKey = annotation.colName();
                            break;
                        }
                    }
                    sql.append(" where "+colNameByPrimaryKey+"= ?");
                    break;
                default:
                    break;
            }
        }
        return sql.toString();
    }
    @Override
    public List<Map<String, Object>> listAll(T t) {
        String sql = getSql(t.getClass(),"selectAll");
        System.out.println(sql);
        return BaseDao.executeQuery(sql,null);
    }

    @Override
    public int insert(T t) {
        Class<?> aClass = t.getClass();
        String sql = getSql(aClass,"insert");
        System.out.println(sql);

        Field[] declaredFields = aClass.getDeclaredFields();
        Object[] params  = new Object[declaredFields.length];
        int i = 0;
        for (Field field:declaredFields) {
            if(field.isAnnotationPresent(PrimaryKey.class)){
                continue;
            }
            try {
                PropertyDescriptor propertyDescriptor = new PropertyDescriptor(field.getName(), aClass);
                Method readMethod = propertyDescriptor.getReadMethod();
                params[i++] = readMethod.invoke(t);
            } catch (IntrospectionException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
        }
        System.out.println(i);
        Object[] newParams = new Object[i];
        System.arraycopy(params,0,newParams,0,i);

        for (Object o :newParams) {
            System.out.println(o);
        }
        return BaseDao.executeUpdate(sql,newParams);
    }

    @Override
    public int update(T t) {
        Class<?> aClass = t.getClass();
        String sql = getSql(aClass,"update");
        System.out.println(sql);

        Field[] declaredFields = aClass.getDeclaredFields();
        Object oByPrimaryKey = "";
        Object[] params = new Object[declaredFields.length];
        int i = 0;
        for (Field f:declaredFields) {
            try {
                PropertyDescriptor propertyDescriptor = new PropertyDescriptor(f.getName(), aClass);
                if(f.isAnnotationPresent(PrimaryKey.class)){
                    oByPrimaryKey = propertyDescriptor.getReadMethod().invoke(t);
                }else {
                    params[i++] = propertyDescriptor.getReadMethod().invoke(t);
                }
            } catch (IntrospectionException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
        }
        params[i] = oByPrimaryKey;
        for (Object o: params) {
            System.out.println(o);
        }
        return BaseDao.executeUpdate(sql,params);
    }

    @Override
    public int delete(T t) {
        Class<?> aClass = t.getClass();
        String sql = getSql(aClass,"delete");
        System.out.println(sql);
        Field[] declaredFields = aClass.getDeclaredFields();
        Object[] params = null;
        for (Field f:declaredFields) {
            if(f.isAnnotationPresent(PrimaryKey.class)){
                try {
                    params = new Object[]{new PropertyDescriptor(f.getName(),aClass).getReadMethod().invoke(t)};
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                } catch (IntrospectionException e) {
                    e.printStackTrace();
                }
            }
        }
        System.out.println(params[0]);
        return BaseDao.executeUpdate(sql,params);
    }
}

测试：

import com.wen.entity.Score;
import com.wen.entity.Student;
import com.wen.orm.dao.IGenericDao;
import com.wen.orm.dao.impl.GenericDaoImpl;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

public class Test {
    public static void main(String[] args) throws ParseException {
        IGenericDao genericDao = new GenericDaoImpl();
        Student student = new Student();
        Score score = new Score(8,3,98.0);

        student.setS_id(15);
        student.setS_name("小温");
        student.setS_birth(new SimpleDateFormat("yyyy-MM-dd").parse("2008-1-1"));
        student.setS_sex("男");


       //genericDao.insert(student);
        //genericDao.insert(score);
        //genericDao.update(student);
        genericDao.delete(student);

        //genericDao.delete(score);

        System.out.println("======================");
        List<Map<String, Object>> list = genericDao.listAll(student);
        for (Map map:list) {
            System.out.println(map);
        }
    }
}

2.2 MyBatis简介

        一个基于Java的持久层/数据访问层框架。

2.3 结构

①接口层：给应用程序提供一系列的数据接口；

②接口层传递参数：sql命令，在数据处理曾进行处理，返回对应的结果映射；

③基础支撑层：提供最基础的底层的操作：连接管理(连接池),事务管理(增、删、该)，配置加载(读取配置信息)，缓存(一级缓存，二级缓存)

2.4 环境搭建 

整体结构：

 

        Mybatis官网：mybatis – MyBatis 3 | 入门

①建立maven项目，导入需要的jar包；

        pom.xml：

    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.5.7</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.11</version>
    </dependency>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>

②创建配置数据库连接信息的properties文件；

        db.properties：

jdbc.driver = com.mysql.cj.jdbc.Driver
jdbc.url = jdbc:mysql://127.0.0.1:3306/***?useSSL=false&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
jdbc.userName = 
jdbc.password = 

③配置数据库连接属性和mybati日志；

Mybatis.xml:

配置文件的属性是有序的，顺序不对会直接报错；

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--引入JDBC的配置文件-->
    <properties resource="db.properties">

    </properties>
    <settings>
        <!--使用自带日志-->
        <setting name="logImpl" value="STDOUT_LOGGING" />
        <!--自动将列名由下划线(goods_name)改为小驼峰(goodsName)-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <typeAliases>
        <!--设置某个类的简写别名,别名日志显示为乱码原因不明-->
        <typeAlias type="com.entity.Goods" alias="Goods"/>
        <!--针对某个包下的所有类设置别名-->
        <package name="com.entity"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.userName}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <!--mapper文件的开发方式
            <mapper resource="com/mapper/GoodsMapper.xml"/>
        -->
        <!--mapper接口的开发方式-->
        <mapper class="com.mapper.GoodsMapper"/>

        <!--通过定义包来定义Mapper接口
        <package name="com.mapper"/>
        -->
    </mappers>
    
</configuration>

④在mapper包下创建对应实体的mapper接口(GoodsMapper.java)

 

import com.entity.Goods;
import com.entity.PageInfo;

import java.util.List;
import java.util.Map;

public interface GoodsMapper {
    /**
     * 两表查询，包含商品种类
     * @return
     */
    List<Map> goodsList();

    /**
     * goods集合
     * @return
     */
    List<Goods> listAllGoods();

    /**
     * 带分页的两表查询，含商品分类
     * pageInfo
     * @return
     */
    List<Map> goodsListByPage(PageInfo pageInfo);

    /**
     * 可一次插入多条商品记录
     * @param goodsList
     * @return
     */
    int insertGoods(List<Goods> goodsList);

    /**
     * 单商品修改
     * @param goods
     * @return
     */
    int updateGoods(Goods goods);

    /**
     * 单商品删除
     * @param goodsId
     * @return
     */
    int deleteGoods(int goodsId);

    /**
     * 删库，重置自增列
     * @return
     */
    int cleanTable();
}

⑤在mapper包下创建对应实体的mapper文件(GoodsMapper.xml)

采用mapper接口开发的方式时，要注意：

①配置文件和其对应的接口，除了文件类型不同，其他的完全相同；

②resources中创建多级目录要用“/”来分割，不能用“.”；

③xml文件中对应的查询语句的"id"要与对应接口的方法名完全一致;

④返回的结果的类型或参数的类型，如果是自己创建的类型需要写完整，或者在MyBatis.xml中配置好别名；

⑤在sql语句中有多个参数时，名称要与传入的一致。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.mapper.GoodsMapper">
    <select id="goodsList" resultType="map">
        select goods_id,goods_name,price,produce_date,address,categoryName
        from tb_goods,category
        where tb_goods.category_id = category.categoryId
    </select>

    <select id="listAllGoods" resultType="Goods">
        select goodsId,goodsName,price,produceDate,address,categoryId from goods
    </select>

    <select id="goodsListByPage" resultType="map" parameterType="com.entity.PageInfo">
        select goods_id,goods_name,price,produce_date,address,category_id,categoryName
        from tb_goods,category
        where tb_goods.category_id = category.categoryId
        order by goods_id
        limit #{page},#{limit}
    </select>
    <insert id="insertGoods" parameterType="java.util.ArrayList">
        insert into tb_goods(goods_name,price,produce_date,address,category_id)
        values
       <foreach collection="list" item="goods" index="index" separator=",">
           (#{goods.goodsName}, #{goods.price}, #{goods.produceDate}, #{goods.address}, #{goods.categoryId})
       </foreach>

    </insert>
    <delete id="deleteGoods">
        delete from tb_goods where goods_id = #{id}
    </delete>
    <delete id="cleanTable">
        truncate tb_goods;
    </delete>
</mapper>

⑥ 使用mybatis完成crud

import com.dao.IGoodsDao;
import com.entity.Goods;
import com.entity.PageInfo;
import com.mapper.GoodsMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

public class GoodsDaoImpl implements IGoodsDao {
    /**
     * 创建一个SqlSession的工厂用于创建sqlSession对象
     */
    private static SqlSessionFactory sessionFactory;
    static{
        try {
            InputStream resourceAsStream = Resources.getResourceAsStream("Mybatis.xml");
            sessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    @Override
    public List<Map> goodsList() {
        SqlSession sqlSession = sessionFactory.openSession(true);
        GoodsMapper mapper = sqlSession.getMapper(GoodsMapper.class);
        List<Map> goodsList = mapper.goodsList();
        sqlSession.close();
        return goodsList;
    }

    @Override
    public PageInfo goodsListByPage(int page, int limit) {

        PageInfo pageInfo = new PageInfo();
        pageInfo.setPage((page-1)*limit);
        pageInfo.setLimit(limit);
        pageInfo.setTotalCount(goodsList().size());
        pageInfo.setTotalPage();


        SqlSession sqlSession = sessionFactory.openSession(true);
        GoodsMapper mapper = sqlSession.getMapper(GoodsMapper.class);
        List<Map> goodsList = mapper.goodsListByPage(pageInfo);
        sqlSession.close();
        pageInfo.setData(goodsList);
        return pageInfo;
    }


    @Override
    public int updateGoods(Goods goods) {
        SqlSession sqlSession = sessionFactory.openSession(true);
        GoodsMapper mapper = sqlSession.getMapper(GoodsMapper.class);
        int count = mapper.updateGoods(goods);
        sqlSession.close();
        return count;
    }

    @Override
    public int deleteGoods(int goodsId) {
        SqlSession sqlSession = sessionFactory.openSession(true);
        GoodsMapper mapper = sqlSession.getMapper(GoodsMapper.class);
        int count = mapper.deleteGoods(goodsId);
        sqlSession.close();
        return count;
    }

    @Override
    public int insertGoodsList(List goodsList) {
        SqlSession sqlSession = sessionFactory.openSession(true);
        GoodsMapper mapper = sqlSession.getMapper(GoodsMapper.class);
        int count = mapper.insertGoods(goodsList);
        sqlSession.close();
        return count;
    }

    @Override
    public int resetTable() {
        SqlSession sqlSession = sessionFactory.openSession(true);
        GoodsMapper mapper = sqlSession.getMapper(GoodsMapper.class);
        mapper.cleanTable();
        int count = mapper.insertGoods(mapper.listAllGoods());
        sqlSession.close();
        return count;
    }
}

 ⑦mybatis的事务

mybatis默认情况下是手动事务，在进行增删改操作之后要提交事务：

①提交：sqlSession.commit();

②设置自动事务:

        在MyBatis.xml中，修改transactionManager配置为：         

                <transactionManager type="MANAGED">
                          <property name="closeConnection" value="false"/>
                </transactionManager>

        创建sqlSession时，openSession()设置为true,默认为false：              

                sessionFactory.openSession(true)。