【Java】 MyBatis的使用

一、ORM(Object Relation Mapping)框架

1. ORM框架所解决的问题：阻抗不匹配（数据库查询所得到的数据和Java类型不匹配）
2. ORM框架需要依赖于数据源(DataSource)，常用的数据库连接池：C3P0、Druid、DBCP
3. 常见的ORM框架MyBatis 、 Hibernate 、 Spring JDBC 、 JPA

二、MyBatis初步使用

1. 配置数据源

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${mysql.driverClassName}"></property>
        <property name="url" value="${mysql.url}"></property>
        <property name="username" value="${mysql.username}"></property>
        <property name="password" value="${mysql.password}"></property>
        <property name="maxWait" value="${mysql.maxWait}"></property>
        <property name="maxActive" value="${mysql.maxActive}"></property>
        <property name="minIdle" value="${mysql.minIdle}"></property>
    </bean>

2. 配置MyBatis的SqlSessionFactoryBean

    <bean id="sqlSessionFactoryBean" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"></property>
        <property name="mapperLocations" value="classpath:mapper/*.xml"></property>
    </bean>

3. 配置Mapper扫描

    <bean id="mapperScanner" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactoryBean"></property>
        <property name="basePackage" value="com.dao"></property>
    </bean>

三、多表联查

1. 创建vo对象，并定义结果集

    <resultMap id="UserVoMap" type="com.lanou.entity.vo.UserVo">
        <id property="uid" column="uid" jdbcType="VARCHAR"></id>
        <result property="username" column="username" jdbcType="VARCHAR"></result>
        <result property="address" column="address" jdbcType="VARCHAR"></result>
        <result property="gender" column="phone" jdbcType="VARCHAR"></result>
        <result property="phone" column="phone" jdbcType="VARCHAR"></result>
    </resultMap>

2. 完成相应业务逻辑实现

	select 
		a.id as id,
		a.username as username,
		b.address as address,
		b.gender as gender,
		b.phone as phone  
	from 
		tb_user as a,
		tb_userinfo as b
	where 
		a.username=#{username}
		and a.password=#{password}
		and a.id=b.uid

四、一次性多表插入

方案一:多条语句插入

1. 更改数据库连接: allowMultiQueries=true
2. 完成业务逻辑实现

<insert id="insertData" parameterType="java.util.Map">
	insert into tb_user(id,username,password) values (replace(uuid(),'-',''), #{user.username}, #{user.password});
	insert into tb_userinfo(
		id,
		gender,
		address,
		uid
	) values(
		replace(uuid(),'-',''),
		#{userinfo.gender},
		#{userinfo.address},
		(select id from tb_user where username=#{user.username}, {user.password})
	)
</insert>

方案二:使用存储过程

1. 将statement设置成callable
2. call 相应的存储过程

方案三:多次插入

1. 将业务进行分层

	<!-- UserMapper.xml -->

    <select id="findIdByUsername" parameterType="java.lang.String" resultType="java.lang.String">
        select id from tb_user where username=#{username}
    </select>

    <insert id="insertUser" parameterType="com.lanou.entity.User">
        insert into
            tb_user( id, username, password)
        values
            (replace(uuid(), '-', ''), #{username}, #{password})
    </insert>

	<!-- UserInfoMapper.xml -->

    <insert id="insertUserInfo" parameterType="com.entity.vo.UserVo">
        insert into
            tb_userinfo(uid, address, gender, phone)
        values
            (#{uid}, #{address}, #{gender}, #{phone})
    </insert>

2.在业务层执行相应逻辑

    @Override
    public void addUserInfo(User user, UserVo userVo) {

    	//第一步:先对user表进行插入
        userDao.insertUser(user);

        //第二步:查询用户生成的id，并将该id设置到即将插入userinfo表的对象中
        userVo.setUid(userDao.findIdByUsername(user.getUsername()));

        //第三步:执行userinfo表的插入操作
        userInfoDao.insertUserInfo(userVo);
    }