JAVA-第十部分-Spring-MyBatis和SSM整合

99永远差一分
230 阅读5分钟

写在前面

MyBatis

  • 数据层/持久层的框架，内部封装了jdbc，只需要关注sql语句

基本使用

  • 映射文件 UserMapper.xml
<?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="userMapper">
    <select id="findAll" resultType="com.java.domain.User">
        select * from user;
    </select>
</mapper>
  • 核心文件 sqlMapConfig.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>
    <!--数据源环境-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql:///dbtest3"/>
                <property name="username" value="root"/>
                <property name="password" value="123456789"/>
            </dataSource>
        </environment>
    </environments>
    <!--加载映射文件-->
    <mappers>
        <mapper resource="com/java/mapper/UserMapper.xml"></mapper>
    </mappers>
</configuration>
  • 调用
//获得核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//获得session工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//获得seesion回话对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//执行操作 参数：映射文件中的namespace.id
List<User> userList = sqlSession.selectList("userMapper.findAll");
System.out.println(userList);
//释放资源
sqlSession.close();

映射文件

image.png

  • 条件查询
<select id="find" resultType="com.java.domain.User" parameterType="java.lang.Integer">
    select * from user where id = #{id};
</select>
//调用
//查询一个
User user = sqlSession.selectOne("userMapper.find", 1);
  • mybatis进行更新操作的时候，事务默认不提交
//调用
sqlSession.insert("userMapper.save", new User("lisi", "31232141"));
//mybatis默认事务不提交
sqlSession.commit();

//映射文件 传递对象
<insert id="save" parameterType="com.java.domain.User">
    insert into user values (#{id}, #{username}, #{password})
</insert>
  • 修改数据
<update id="update" parameterType="com.java.domain.User">
    update user set username=#{username}, password=#{password} where id = #{id};
</update>

//调用
sqlSession.update("userMapper.update",new User(4,"zhangsan","32131"));
  • 删除数据
<delete id="delete" parameterType="java.lang.Integer">
    delete from user where id = #{id};
</delete>
//调用
sqlSession.delete("userMapper.delete",4);

动态sql

  • Dynamic sql
  • if根据是否有条件拼接sql语句
<select id="findByCondition" parameterType="user" resultType="user">
    select * from user
    <where>
        <if test="id != 0">
            and id = #{id}
        </if>
        <if test="username != null">
            and username = #{username}
        </if>
        <if test="password != null">
            and password = #{password}
        </if>
    </where>
</select>
  • foreach
//原语句
select * from user WHERE id in ( ? , ? , ? )

<select id="findByIds" parameterType="list" resultType="user">
    select  * from user
    <where>
        <foreach collection="list" open="id in (" close=")" item="id" separator=",">
            #{id}
        </foreach>
    </where>
</select>

抽取

  • 抽取
<sql id="selectUser">select * from user</sql>
  • 引用
<include refid="selectUser"></include>

核心文件

environments

  • 配置环境 image.png
  • transacationManager jDBC类型，框架通过connection进行控制；MANAGED类型，几乎没做什么，从来不提交或回滚，通过容器管理，默认会关闭连接
  • dataSource POOLED池化；UNPOOLED每次被请求时打开或者关闭连接；JNDI为了能在如EJB或应用服务器这类容器中使用，容器可以集中或在外部配置数据源，然后放置一个JNDI上下文的引用

mappers

  • 加载映射文件 image.png

properties

  • 把数据源信息抽取，解耦合，通过这个标签进行加载
<properties resource="jdbc.properties"/>

typeAilases

  • 定义别名
  • 基本数据类型的包装类可以用小写形式
  • 要放在properties标签后面，会有顺序要求
<typeAliases>
    <typeAlias type="com.java.domain.User"  alias="user"/>
</typeAliases>

//映射文件的修改
<select id="find" resultType="user" parameterType="int">
    select * from user where id = #{id};
</select>
  • 扫包
<package name="com.java.domain"/>

typeHandlers

  • 类型处理器
  • 将Date转换成毫秒值存储在数据库中
  • 自定义类型处理器
public class DateTypeHander extends BaseTypeHandler<Date> {
    @Override
    //将java类型转换成数据库需要的类型
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
        long time = date.getTime();
        preparedStatement.setLong(i, time);
    }
    @Override
    //将数据库类型转换成java类型
    //string字段名称 resultSet结果集
    public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
        //获取结果集中的数据(long) 转换成Date类型
        long time = resultSet.getLong(s);
        Date date = new Date(time);
        return date;
    }
    @Override
    //将数据库类型转换成java类型
    //i 下标
    public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
        //获取结果集中的数据(long) 转换成Date类型
        long time = resultSet.getLong(i);
        Date date = new Date(time);
        return date;
    }
    @Override
    //将数据库类型转换成java类型
    public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        long time = callableStatement.getLong(i);
        Date date = new Date(time);
        return date;
    }
}
  • 核心文件配置
<!--自定义类型处理器-->
<typeHandlers>
    <typeHandler handler="com.java.handler.DateTypeHander"/>
</typeHandlers>

plugins

  • 插件，扩展功能
  • 分页助手 pageHelper
  • 坐标 pagehelper jsqlparser
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>4.2.1</version>
</dependency>
<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>1.4</version>
</dependency>
  • 配置
<!--配置分页助手插件-->
<plugins>
    <plugin interceptor="com.github.pagehelper.PageHelper">
        <!--指定方言，不同的数据库关键字有差别-->
        <property name="dialect" value="mysql"/>
    </plugin>
</plugins>
  • 在查询前调用
//设置分页相关参数 当前页 每页显示的条数
PageHelper.startPage(2,3);
  • 分页相关参数
//获得分页相关的参数
PageInfo<User> pageInfo = new PageInfo<User>(userList);
System.out.println("当前页" + pageInfo.getPageNum());
System.out.println("每条显示条数" + pageInfo.getPageSize());
System.out.println("总条数" + pageInfo.getTotal());
System.out.println("总页数" + pageInfo.getPages());
System.out.println("上一页" + pageInfo.getPrePage());
System.out.println("下一页" + pageInfo.getNextPage());
System.out.println("是否是第一页" + pageInfo.isIsFirstPage());
System.out.println("是否是最后一页" + pageInfo.isIsLastPage());

相关API

  • SqlSessionFactoryBuilder工厂构建器，通过加载mybatis核心文件的输入流构建
//获得核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//获得session工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
  • sqlSessionFactory工厂对象，创建SqlSession
//获得seesion回话对象
//openSession 默认开启事务，事务不会自动提交，需要手动提交
SqlSession sqlSession = sqlSessionFactory.openSession();
//设置为true，则不需要手动提交
sqlSessionFactory.openSession(true);
  • SqlSession，进行增删改查操作

接口代理实现持久层

  • 命名空间为接口全路径；方法名相同；方法返回值类型相同 image.png
  • 映射文件
<mapper namespace="com.java.dao.UserMapper">
    <select id="findAll" resultType="user">
        select * from user;
    </select>
    <select id="findById" resultType="user" parameterType="int">
        select * from user where id = #{id};
    </select>
</mapper>
  • 接口
public interface UserMapper {
    List<User> findAll() throws IOException;
    User findById(int id);
}
  • 通过代理对象调用
//mapper代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.findAll();
User user = mapper.findById(2);
System.out.println(userList);
System.out.println(user);

多表操作

一对一

  • 订单与用户与一对一
  • 通过引用对象，在实体中体现private User user;
SELECT *, o.id oid, u.id uid FROM orders o, user u WHERE o.uid = u.id;
  • 需要手动封装实体
<resultMap id="orderMap" type="order">
    <!--手动指定字段与实体属性映射关系-->
    <!--column 数据表的字段名称 property 实体的属性名称-->
    <id column="oid" property="id"/>
    <result column="ordertime" property="ordertime"/>
    <result column="total" property="total"/>
    <result column="uid" property="user.id"/>
    <result column="username" property="user.username"/>
    <result column="birthday" property="user.birthday"/>
    <result column="password" property="user.password"/>
</resultMap>
<select id="findAll" resultMap="orderMap">
    SELECT *, o.id oid, u.id uid FROM orders o, user u WHERE o.uid = u.id;
</select>
  • 另一种方式
<resultMap id="orderMap" type="order">
    <!--手动指定字段与实体属性映射关系-->
    <!--column 数据表的字段名称 property 实体的属性名称-->
    <id column="oid" property="id"/>
    <result column="ordertime" property="ordertime"/>
    <result column="total" property="total"/>
    <association property="user" javaType="user">
        <id column="uid" property="id"/>
        <id column="username" property="username"/>
        <id column="birthday" property="birthday"/>
        <id column="password" property="password"/>
    </association>
</resultMap>

一对多

  • 一个用户对应多个订单
  • 通过集合将多个订单封装到一个用户中 private List<Order> orderList;
<resultMap id="userMap" type="user">
    <id column="uid" property="id"/>
    <result column="username" property="username"/>
    <result column="password" property="password"/>
    <result column="birthday" property="birthday"/>
    <!--配置集合信息 property集合名称 oftype集合重的数据类型-->
    <collection property="orderList" ofType="order">
        <!--封装order数据-->
        <id column="oid" property="id"/>
        <result column="ordertime" property="ordertime"/>
        <result column="total" property="total"/>
    </collection>
</resultMap>
<select id="findAll" resultMap="userMap">
    SELECT *, o.id oid, u.id uid FROM user u, orders o WHERE  u.id = o.uid;
</select>

多对多

  • 通过集合封装在实体中 private List<Role> roleList;
<resultMap id="userRoleMap" type="user">
    <id column="userId" property="id"/>
    <result column="username" property="username"/>
    <result column="password" property="password"/>
    <result column="email" property="email"/>
    <result column="phoneNum" property="phoneNum"/>
    <!--配置集合信息 property集合名称 oftype集合中的数据类型-->
    <collection property="roleList" ofType="role">
        <!--role-->
        <id column="roleId" property="id"/>
        <result column="roleName" property="roleName"/>
        <result column="roleDesc" property="roleDesc"/>
    </collection>
</resultMap>
<select id="findUserAndRole" resultMap="userRoleMap">
    SELECT * FROM sys_user u, sys_user_role ur, sys_role r WHERE u.id = ur.userId AND r.id = ur.roleId;
</select>

注解开发

简单实用

  • 配置接口
@Select("select * from user;")
List<User> findAll();
@Select("select * from user where id = #{id};")
User findById(int id);
@Insert("insert into user values (#{id}, #{username}, #{password}, #{birthday});")
void save(User user);
@Update("update user set username=#{username} where id = #{id};")
void update(User user);
@Delete("delete from user where id = #{id};")
void delete(int id);
  • 配置核心文件
<!--加载映射关系-->
<mappers>
    <!--指定接口所在的包-->
    <package name="com.java.dao"/>
</mappers>

一对一查询

@Select("select *, o.id oid, u.id uid from orders o, user u where o.uid = u.id;")
@Results({
        @Result(column = "oid", property = "id"),
        @Result(column = "total", property = "total"),
        @Result(column = "ordertime", property = "ordertime"),
        @Result(column = "uid", property = "user.id"),
        @Result(column = "username", property = "user.username"),
        @Result(column = "password", property = "user.password"),
        @Result(column = "birthday", property = "user.birthday")
})
List<Order> findAll();
  • 另一种方式
@Select("select * from orders")
@Results({
        @Result(column = "id", property = "id"),
        @Result(column = "total", property = "total"),
        @Result(column = "ordertime", property = "ordertime"),
        @Result(
                property = "user", //要封装的属性名称
                column = "uid", //根据哪个字段查询user表
                javaType = User.class,
                //one 代表一对一查询，select代表查询目标接口的方法获得需要的数据
                one = @One(select = "com.java.dao.UserMapper.findById")
        )
})
List<Order> findAll();

一对多

@Select("select * from user;")
@Results({
        //表明主键
        @Result(id = true, column = "id", property = "id"),
        @Result(column = "username", property = "username"),
        @Result(column = "password", property = "password"),
        @Result(column = "birthday", property = "birthday"),
        @Result(
                property = "orderList",
                column = "id",//作为下一步查询的参数
                javaType = List.class,
                //一对多的封装
                many = @Many(select = "com.java.dao.OrderMapper.findByUid")
        )
})
List<User> findUserAndOrderAll();

多对多

  • 用户表
@Select("select * from sys_user;")
@Results({
        @Result(id = true, column = "id", property = "id"),
        @Result(column = "username", property = "username"),
        @Result(column = "password", property = "password"),
        @Result(column = "email", property = "email"),
        @Result(column = "phoneNum", property = "phoneNum"),
        @Result(
                property = "roleList",
                column = "id",
                javaType = List.class,
                many = @Many(select = "com.java.dao.RoleMapper.findByUserId")
        )
})
List<User> findUserAndRole();
  • 角色表
@Select("select * from sys_user_role ur, sys_role r where ur.roleId = r.id and ur.userId = #{userId};")
List<Role> findByUserId(int userId);

实现foreach

@Select("<script> " +
        "select * from sys_user where " +
        "<foreach collection= 'list' open='id in (' close=')' item='id' separator=','> #{id} </foreach>" +
        "</script>"
)
List<User> findByIds(List<Integer> ids);

实现where和if

@Select("<script> " +
        "select * from sys_user " +
            "<where> " +
                "<if test='id != 0'>" +
                    "and id = #{id} " +
                "</if>" +
                "<if test='username != null'>" +
                    "and username = #{username} " +
                "</if>" +
                "<if test='password != null'>" +
                    "and password = #{password} " +
                "</if>" +
            "</where>" +
        "</script>")
List<User> findByCondition(User user);

SSM框架整合

  • applicationContex.xml配置mybatis.xml，直接在容器中生成mapper的实现类
<!--配置sqlSession工厂 pom要导入mybatis-spring-->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <!--加载核心文件-->
    <property name="configLocation" value="classpath:sqlMapConfig-spring.xml"/>
</bean>

<!--扫描mapper所在的包 为mapper创建实现类-->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage" value="com.java.mapper"/>
</bean>
  • 核心配置文件只需要配置别名
    <typeAliases>
<!--        <typeAlias type="com.java.domain.Account"  alias="account"/>-->
        <package name="com.java.domain"/>
    </typeAliases>
avatar
文章
阅读
粉丝