mybatis

使用mybatis

package com.sfliu.controller;
import java.io.IOException;
import java.io.Reader;
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 com.sfliu.model.User;
public class TestMyBatis {
    public static void main(String[] args) throws IOException {
         // 读取配置文件
        Reader reader =Resources.getResourceAsReader("conf.xml");
        SqlSessionFactory sessionFactory= new SqlSessionFactoryBuilder().build(reader);
        SqlSession session = sessionFactory.openSession();
        // 以映射代替sql
                String statement="com.sfliu.model.userMapper.queryUserById";
        User user =session.selectOne(statement);
        System.out.println(user);
        session.close();
    }
}

单个对象的mapper.xml提供一种namespace.id=>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="com.sfliu.model.userMapper">
    <select id="queryUserById" resultType="com.sfliu.model.User" parameterType="int">
        select * from user where id = 1
    </select>
</mapper>

mybatis配置文件用于设置驱动写入并将需要的mapper.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" />
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/test" />
                <property name="username" value="root" />
                <property name="password" value="root" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
    <!-- 加载配置文件 -->
        <mapper resource="com/sfliu/model/userMapper.xml" />
    </mappers>
</configuration>

<T> T selectOne(String statement, Object parameter)
<E> List<E> selectList(String statement, Object parameter)
<K,V> Map<K,V> selectMap(String statement, Object parameter, String mapKey)
int insert(String statement, Object parameter)
int update(String statement, Object parameter)
int delete(String statement, Object parameter)

概念

mybatis形式上输入或者输出的参数只能有一个但是可以写成数组或者对象
如果参数是简单类型，八个基本类型和string,则可以使用任意占位符
如果是对象类型，只能是对象的属性,如user对象的id,password,username属性
如果返回值得个数是多少个，resultType都是单个对象的类名
#### sql语句
```xml
<insert id="insertIntoStudent"  parameterType="Model.User">
    insert into student(id,username,password) values (#{id},#{username},#{password})
</insert>
 <select id="queryUserById" resultType="Model.User" parameterType="int">
    select * from user where id = 1
</select>
<delete id="deleteUserByUserId" parameterType="int">
    delete form user where id = #{id}
</delete>
<update id="updateUserByUserId" parameterType="Model.User">
    update user set username=#{username},password=#{password} where id=#{id}
</update>
<select id="queryAllUsers" resultType="Model.User">
    select * from user
</select>

 // 使用session.selectList或者session.selectMap来承接对象或数组
// 查询所有的数组
String statement ="Model.userMapper.queryAllUsers";
List<User> users=session.selectList(statement);
// 
User user = new User(5,"刘乾","12334");
String statement = "Model.userMapper.insertIntoUser";
int insertNum = session.insert(statement,user);

mapper动态代理方式的crud，mybatis接口开发，约定优于配置

接口开发

package Interface;
import Model.User;
/**
 * 操作mybatis的接口,方法名与文件中标签的ID相同
 * 方法的输入参数和mapper文件中的标签的paramsType的类型一致
 * 返回值和resultType相同
 */
public interface UserMapper {
    // 获取单个user
    User queryUserById(int id);
}

// 要实现mapper文件和xml的一一对应，还需要满足两点
1.namespace的值，就是接口的全类名，根据接口名找到该文件
2.根据接口的方法名找到mapper.xml中的sql标签
以上两点可以保证当我们调用接口中的方法时，程序可以自动调用mapper.xml中的sql标签
3.习惯上是把接口和xml文件放到一块去

// 调用session的getMapper方法，传入接口的类名UserMapper.class
// 获取接口对象，调用接口对象的方法，可以调用xml标签
//UserMapper.java    interface接口
User queryUserById(int id);
List<User> queryAllUsers();
int insertIntoUser(User user);
int deleteUserByUserId(int id);
int updateUserByUserId(User user);
// 调用端
UserMapper userMapper=session.getMapper(UserMapper.class);
User user =userMapper.queryUserById(1);

properties写入配置信息

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=root

<properties resource="db.properties"></properties>
<dataSource type="POOLED">
    <property name="driver" value="${driver}" />
    <property name="url"
        value="${url}" />
    <property name="username" value="${username}" />
    <property name="password" value="${password}" />
</dataSource>

全局参数

 <settings>
    <setting name="cacheEnabled" value="false"/>
</settings> 

设置别名,用于在mapper.xml中使用

// 单个替换
<typeAliases>
        <typeAlias type="com.sfqian.model.User" alias="User" />
</typeAliases>
// 批量替换
 <typeAliases>
    <package name="com.sfqian.model"/></typeAliases>
// mybatis内置了一些常见的别名 

类型处理器

1.mybatis自带一些常见的类型处理器
2.可以自定义类型处理器
3.java类型和数据库类型进行转换

自定义类型转换器

// 需要实现TypeHandler接口
// 该接口有一个BaseTypeHandler，也可以通过继承该类来实现
package com.sfqian.handler;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

public class BooleanAndIntConverterByExtend<T> extends BaseTypeHandler<T>{

    // db到java代码
    @Override
    public T getNullableResult(ResultSet arg0, String arg1) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
    
    @Override
    public T getNullableResult(ResultSet arg0, int arg1) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public T getNullableResult(CallableStatement arg0, int arg1) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
    // 从java代码到数据库
    @Override
    public void setNonNullParameter(PreparedStatement arg0, int arg1, T arg2, JdbcType arg3) throws SQLException {
        // TODO Auto-generated method stub
        
    }
    
}

resultMap使用

// 如果类中的字段和表中的字段可以合理识别，则使用resultType
// 如果类的属性名和表中字段名不同,或者需要将其进行转换
<select id="queryUserByIdWithConverter" resultMap="userResult" parameterType="int">
        select * from user where id = ${id}
</select>
<resultMap type="com.sftest.model.User" id="userResult">
        <!-- 分为主键和非主键 -->
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="password" column="password"/>
        <result property="sex" column="" javaType="boolean" jdbcType="INTEGET"/>
    </resultMap>

输入参数

1.类型是简单类型,#{任意值} ，{属性值}

#{} 会给变量加上单引号
${} 不会，所以在使用字符串的时候都没有单引号加上一般使用'${vlaue}'的形式写sql，适合于动态排序,一般在拼接sql的时候使用
// 和数据库里面的值有关的用'${value}',之和sql有关的使用${value}
select * from user where user like '%${value}%'

hashMap传参

queryUserByHashMap(Map<String,Object>,map)
// 调用
Map<String,Object> stuMap= new Map<String,Object>()
stuMap.put("stuAge",234)
stuMap.put("stuname","jack")
// sql使用map中key
<select id="queryStudentByHashMap" parameterType="HashMap">
        select * from user where username=#{username} and password=#{password}
    </select> 

调用存储过程

// 存储过程的输入参数使用map或者hasmap，存储过程没有返回值，通过输出参数来模拟返回值
<select id="queryCountByGradeWithProcedure" statementType="CALLABLE" resultType="HashMap">
  {
  CALL queryCountByCradeWithProcedure(
  #{gName,jdbcType=VARCHAR,mode=IN},
  #{sCount,jdbcType=INTEGER,mode=OUT}
)
}
</select>
UserMapper.queryCountByGradeWithProcedure(params)
// count为储存过程中定义的参数返回值
params.get("count")

动态sql

<select id="findActiveBlogWithTitleLike"
     resultType="Blog">
// if标签用来判断参数
  SELECT * FROM BLOG
  WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
  
</select>
<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  // choose类似于switch,可以用来判断多个状态
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

// 使用where和if结合的方式避免出现or和and错误
<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG
  <where>
    <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>
// 如果需要改变where的规则可以使用，trim可以模拟标签，并加入属性
<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>
// set会删除如果最后一个元素没哟匹配的逗号
<update id="updateAuthorIfNecessary">
  update Author
    <set>
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </set>
  where id=#{id}
</update>
// 非常重要，传入的为集合或者数组将会对其进行遍历展示，有开头或者结尾
select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
open为遍历的开头，close是结束，separator是分割符号

// 如[1,2,3] 则会是('1','2','3')
</select>

使用注解来配置sql

// 插入
@Insert("insert into user (username,address,email) values (#{username},#{address},#{email})")
@Options(useGeneratedKeys = true, keyProperty = "id") 
public int insert(User user) throws Exception;
// 选择
@Select("select * from user where id=#{id}")
public User selectById(int id) throws Exception;
// update
@Update("update user set username=#{username},address=#{address},email=#{email} where id=#{id}")
public int update(User user) throws Exception;
// delete
@Delete("delete from user where id=#{id}")
public int delete(int id) throws Exception;
// 结果映射替代resultMap
@Select("select * from user")
@Results({ @Result(id = true, column = "id", property = "id"),
        @Result(column = "username", property = "user_name"),
        @Result(column = "city", property = "city") })
public List<User> selectAll() throws Exception;
// 复用映射，在同一文件夹下建立xml文件
<mapper namespace="twm.mybatisdemo.mapper.UserMapper">
    <!-- 自定义返回结果集 -->
    <resultMap id="userMap" type="twm.mybatisdemo.pojo.User">
        <id column="id" property="id" jdbcType="INTEGER" />
        <result property="user_name" column="username"></result>
        <result property="city" column="city"></result>
    </resultMap>
</mapper>
// 映射使用xml
@Select("select * from user")  
@ResultMap("twm.mybatisdemo.mapper.UserMapper.userMap")  
public List<User> selectAll() throws Exception;

关联查询

@Select("select * from user")
@Results({ @Result(id = true, column = "id", property = "id"),
        @Result(column = "username", property = "user_name"),
        @Result(column = "city", property = "city"),
        @Result(column = "account_id", property = "account",one = @One(select = "twm.mybatisdemo.mapper.AccountMapper.selectById")) })
public List<User> selectAll() throws Exception;

// 使用配置形式的关联查询
@Select("select * from user")
@ResultMap("twm.mybatisdemo.mapper.UserMapper.userMapWithAccount")
public List<User> selectAll() throws Exception;
// 结果集合映射的xml
<mapper namespace="twm.mybatisdemo.mapper.UserMapper">
    <!-- 自定义返回结果集 -->
    <resultMap id="userMapWithAccount" type="twm.mybatisdemo.pojo.User">
        <id column="id" property="id" jdbcType="INTEGER" />
        <result property="user_name" column="username"></result>
        <result property="city" column="city"></result>
        <association property="account" javaType="Account" column="account_id" 
            select="twm.mybatisdemo.mapper.AccountMapper.selectById" />
    </resultMap>
</mapper>

传递多个参数的方式

// sql默认会使用id参数,但是#{st}会指向Student对象,可以使用#{st.name}来获取值
public int getXxx (
    Integer id , 
    @Param("st")Student st
);
// 集合对象作为参数的用法
List : #{List[0]}
Set : #{Set.key}
数组:#{array[0]}
// 传入 Map<String,Object> map 对象

打印sql

<setting name="logImpl" value="STDOUT_LOGGING" />

Example类指定如何构建一个动态的where子句. 表中的每个non-BLOB列可以被包括在where子句中. 例子是展示此类用法的最好方式.
Example类可以用来生成一个几乎无限的where子句.
XZSADDDDDDDDDDD'd'z'sa'c'ASZq