[学习交流] 【广州校区】+【原创】mybatis进阶+整合

输入映射和输出映射

Mapper.xml映射文件中定义了操作数据库的sql,每个sql是一个statement,映射文件是mybatis的核心

简单类型(第一天讲了)
POJO类型(第一天讲了)
pojo包装对象

输入类型为pojo包装对象

[Java] 纯文本查看 复制代码

public class QueryVo implements Serializable {
    private static final long serialVersionUID = 1L;
    private User user;
    List<Integer> idsList;
    Integer[] ids;
    "set and get method"
}

[XML] 纯文本查看 复制代码

<!-- 根据用户名模糊查询 -->
<select id="findUserByQueryVo" parameterType="QueryVo" resultType="User">
    select * from user where username like "%"#{user.username}"%"
</select>
<select id="countUser" resultType="Integer">
    select count(1) from user
 </select>

[Java] 纯文本查看 复制代码

public void testMapperQueryVo() throws Exception {
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    User user = new User("五");
    QueryVo vo = new QueryVo(user);
    List<User> us = userMapper.findUserByQueryVo(vo);
    for (User u : us) {
        System.out.println(u);
    }
    Integer i = userMapper.countUser();
    System.out.println(i);
}

输出ResultMap类型

如果sql查询字段名和pojo的属性名不一致，可以通过resultMap将字段名和属性名作一个对应关系

[Java] 纯文本查看 复制代码

public interface OrderMapper {
    "查询订单表order的所有数据"
    public List<Orders> selectOrdersList();
    "一对一关联 查询  以订单为中心 关联用户"
    public List<Orders> selectOrders();
    "一对多关联"
    public List<User> selectUserList();
}

[XML] 纯文本查看 复制代码

<resultMap type="Orders" id="orders">
    <result column="user_id" property="userId"/>
</resultMap>
 
<select id="selectOrdersList" resultMap="orders">
    SELECT id, user_id, number, createtime, note FROM orders 
</select>

[Java] 纯文本查看 复制代码

@Test
public void testOrderList() throws Exception {
    OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
    List<Orders> ordersList = mapper.selectOrdersList();
    for (Orders orders : ordersList) {
        System.out.println(orders);
    }
}

动态sql

通过mybatis提供的各种标签方法实现动态拼接sql

if标签:if后面跟的字段是实体类的,不是表的,语法用的java语法
where标签:去掉前AND
sql片段:提取公共sql,没啥用
foreach标签

[XML] 纯文本查看 复制代码

sql id="selector">
    select * from user
</sql>
<!--	根据性别和名字查询用户  where 可以去掉第一个前ANd   -->
<select id="selectUserBySexAndUsername" parameterType="User" resultType="User">
    <include refid="selector"/>
    <where>
        <if test="sex != null and sex != ''">
            and sex = #{sex} 
        </if>
        <if test="username != null and username != ''">
            and username = #{username}
        </if>
    </where>
</select>
<!-- 多个ID (1,2,3) list集合-->
<select id="selectUserByIds" parameterType="QueryVo" resultType="User">
    <include refid="selector"/>
    <where>
        <foreach collection="list" item="id" separator="," open="id in (" close=")">
            #{id}
        </foreach>
    </where>
</select>
<!-- Integer数组 vo中叫ids但是取的时候要用array-->
<select id="selectUserByIds" parameterType="QueryVo" resultType="User">
    <include refid="selector"/>
    <where>
        <foreach collection="array" item="id" separator="," open="id in (" close=")">
            #{id}
        </foreach>
    </where>
</select>

[Java] 纯文本查看 复制代码

public List<User> selectUserByIds(Integer[] ids);//要用array取
public List<User> selectUserByIds(List<Integer> ids); //要用list
public List<User> selectUserByIds(QueryVo vo);//直接取属性名

[Java] 纯文本查看 复制代码

@Test
public void testfindUserBySexAndUsername() throws Exception {
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    User user = new User();user.setSex("1");user.setUsername("张小明");
    List<User> users = userMapper.selectUserBySexAndUsername(user);
    for (User user2 : users) {
        System.out.println(user2);
    }
}

关联查询

互联网项目不会设计很多表的关联查询,带宽限制

一对一关联

resultMap不能省

[XML] 纯文本查看 复制代码

<!-- 
    一对一关联 查询  以订单为中心 关联用户
    public List<Orders> selectOrders();
-->
<resultMap type="Orders" id="order">
    <result column="id" property="id"/>
    <result column="user_id" property="userId"/>
    <result column="number" property="number"/>
    <!-- 一对一 -->
    <association property="user" javaType="User">
        <id  column="user_id" property="id"/>
        <result column="username" property="username"/>
    </association>
</resultMap>
<select id="selectOrders" resultMap="order">
    SELECT 
        o.id,
        o.user_id, 
        o.number,
        o.createtime,
        u.username 
    FROM orders o 
        left join user u 
        on o.user_id = u.id
</select>

一对多关联

[XML] 纯文本查看 复制代码

<!-- 
一对多关联
public List<User> selectUserList(); 
-->
<resultMap type="User" id="user">
    <id column="user_id" property="id"/>
    <result column="username" property="username"/>
    <!-- 一对多 -->
    <collection property="ordersList" ofType="Orders">
        <id column="id" property="id"/>
        <result column="number" property="number"/>
    </collection>
</resultMap>
<select id="selectUserList" resultMap="user">
    SELECT 
        o.id,
        o.user_id, 
        o.number,
        o.createtime,
        u.username 
    FROM user u
        left join orders o 
        on o.user_id = u.id
</select>

mybatis整合spring

SqlSessionFactory对象应该放到spring容器中作为单例存在
数据库的连接以及数据库连接池事务管理都交给spring容器来完成
传统dao的开发方式中，应该从spring容器中获得sqlsession对象
Mapper代理形式中，应该从spring容器中直接获得mapper的代理对象

sqlmapConfig.xml

[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>
    <!-- 设置别名 -->
    <typeAliases>
        <!-- 指定扫描包,会把包内所有的类都设置别名,别名的名称就是类名,大小写不敏感 -->
        <package name="cn.itcast.mybatis.pojo" />
    </typeAliases>
</configuration>

applicationContext.xml

SqlSessionFactoryBean属于mybatis-spring这个jar包
对于spring来说,mybatis是另外一个架构,需要整合jar包

[XML] 纯文本查看 复制代码

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p"
    xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans [url]http://www.springframework.org/schema/beans/spring-beans-4.0.xsd[/url]
    [url]http://www.springframework.org/schema/context[/url] [url]http://www.springframework.org/schema/context/spring-context-4.0.xsd[/url]
    [url]http://www.springframework.org/schema/aop[/url] [url]http://www.springframework.org/schema/aop/spring-aop-4.0.xsd[/url] [url]http://www.springframework.org/schema/tx[/url] [url]http://www.springframework.org/schema/tx/spring-tx-4.0.xsd[/url]
    [url]http://www.springframework.org/schema/util[/url] [url]http://www.springframework.org/schema/util/spring-util-4.0.xsd[/url]">

    <!-- 加载配置文件 -->
    <context:property-placeholder location="classpath:db.properties" />
    
    <!-- 数据库连接池 -->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driver}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
        <property name="maxActive" value="10" />
        <property name="maxIdle" value="5" />
    </bean>
    
    <!-- 配置SqlSessionFactory -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!-- 配置mybatis核心配置文件 -->
        <property name="configLocation" value="classpath:SqlMapConfig.xml" />
        <!-- 配置数据源 -->
        <property name="dataSource" ref="dataSource" />
    </bean>
</beans>

log4j.properties

[AppleScript] 纯文本查看 复制代码

# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

原始dao开发

注入sqlSessionFactory然后在方法内调用openSession,然后获取sql执行

[Java] 纯文本查看 复制代码

public class UserDaoImpl extends SqlSessionDaoSupport implements UserDao{
    public void insertUser(){
        this.getSqlSession().insert(arg0, arg1);
    }
}

Mapper代理形式开发dao方式一 : 配置mapper代理

applicationContext.xml

[XML] 纯文本查看 复制代码

<bean id="userMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
    <!-- 配置Mapper接口 -->
    <property name="mapperInterface" value="cn.itcast.mybatis.mapper.UserMapper" />
    <!-- 配置sqlSessionFactory -->
    <property name="sqlSessionFactory" ref="sqlSessionFactory" />
</bean>

[Java] 纯文本查看 复制代码

ApplicationContext ac = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
UserMapper mapper = ac.getBean("userMapper");
mapper....

方式二 : 扫描包形式配置mapper自动会去找sessionFactory

每个mapper代理对象的id就是类名，首字母小写

[XML] 纯文本查看 复制代码

<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <!-- 配置Mapper接口 -->
    <property name="basePackage" value="cn.itcast.mybatis.mapper" />
</bean>

[Java] 纯文本查看 复制代码

ApplicationContext ac = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
UserMapper mapper = ac.getBean(UserMapper.class);
mapper.....

帐号		自动登录	找回密码
密码			加入黑马

[学习交流] 【广州校区】+【原创】mybatis进阶+整合

0 个回复

浏览过的版块