MyBatis实践

MyBatis简介

MyBatis前身是iBatis,是一个基于Java的数据持久层/对象关系映射(ORM)框架.

MyBatis是对JDBC的封装,使开发人员只需关注SQL本身,而不需花费过多的精力去处理如注册驱动、设置参数、创建Connection/Statement、解析结果集等JDBC过程性代码.MyBatis基于XML/注解的方式配置Statement,执行SQL,并将执行结果映射成Java对象, 大大降低了数据库开发的难度.

MyBatis is a first class persistence framework with support for custom SQL, stored procedures and advanced mappings. MyBatis eliminates almost all of the JDBC code and manual setting of parameters and retrieval of results. MyBatis can use simple XML or Annotations for configuration and map primitives, Map interfaces and Java POJOs (Plain Old Java Objects) to database records.
– MyBatis项目地址/在线文档.

初识MyBatis

使用MyBatis需要在pom.xml中添加如下依赖:

<code class="hljs xml"><dependency>    <groupid>org.mybatis</groupid>    <artifactid>mybatis</artifactid>    <version>3.3.0</version></dependency><dependency>    <groupid>mysql</groupid>    <artifactid>mysql-connector-java</artifactid>    <version>5.1.36</version></dependency></code>

Select

<code class="hljs xml"><code class="hljs xml"><!--{cke_protected}{C}%3C!%2D%2D%3Fxml%20version%3D%221.0%22%20encoding%3D%22UTF-8%22%20%3F%2D%2D%3E--><configuration>    <environments default="development">        <environment id="development">            <!--{cke_protected}{C}%3C!%2D%2D%20%E9%85%8D%E7%BD%AEJDBC%E4%BA%8B%E5%8A%A1%E7%AE%A1%E7%90%86%2D%2D%3E-->            <transactionmanager type="JDBC">            <!--{cke_protected}{C}%3C!%2D%2D%20%E9%85%8D%E7%BD%AE%E6%95%B0%E6%8D%AE%E6%BA%90%2D%2D%3E-->            <datasource type="POOLED">                <property name="driver" value="com.mysql.jdbc.Driver">                <property name="url" value="jdbc:mysql://host:port/db?characterEncoding=utf-8">                <property name="username" value="username">                <property name="password" value="password">            </property></property></property></property></datasource>        </transactionmanager></environment>    </environments>    <!--{cke_protected}{C}%3C!%2D%2D%20%E5%8A%A0%E8%BD%BDmapper%E6%98%A0%E5%B0%84%E6%96%87%E4%BB%B6%20%2D%2D%3E-->    <mappers>        <mapper resource="mybatis/mapper/UserDAO.xml">    </mapper></mappers></configuration></code></code>

<code class="hljs xml"><code class="hljs xml"><code class="hljs xml"><!--{cke_protected}{C}%3C!%2D%2D%3Fxml%20version%3D%221.0%22%20encoding%3D%22UTF-8%22%20%3F%2D%2D%3E--><mapper namespace="namespace">    <select id="selectUserById" parametertype="java.lang.Integer" resulttype="com.fq.domain.User">        SELECT * FROM user WHERE id = #{id};    </select>    <select id="selectUserByName" parametertype="java.lang.String" resulttype="com.fq.domain.User">        SELECT * FROM user WHERE name LIKE '%${value}%';    </select></mapper></code></code></code>

mapper映射文件名有UserDAO.xml/UserMapper.xml/User.xml等几种形式, 其一般存放在与mybatis-configuration.xml同级的mapper目录下,由于其主要作用为定义SQL语句与映射关系, 因此一般统称为mapper映射文件.

/** * @author jifang * @since 15/12/31 下午2:27. */public class User {    private Integer id;    private String name;    private String password;    public User() {    }    public User(Integer id, String name, String password) {        this.id = id;        this.name = name;        this.password = password;    }    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public String getPassword() {        return password;    }    public void setPassword(String password) {        this.password = password;    }    @Override    public String toString() {        return "User{" +                "id=" + id +                ", name='" + name + '/'' +                ", password='" + password + '/'' +                '}';    }}

/** * @author jifang * @since 16/2/24 下午6:15. */public class UserDAO {    private SqlSessionFactory factory;    @Before    public void setUp() throws IOException {        String resource = "mybatis/mybatis-configuration.xml";        factory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream(resource));    }    @Test    public void selectUserById() {        try (SqlSession session = factory.openSession()) {            User user = session.selectOne("namespace.selectUserById", 1);            System.out.println(user);        }    }    @Test    public void selectUserByName() {        try (SqlSession session = factory.openSession()) {            List users = session.selectList("namespace.selectUserByName", "student");            for (User user : users) {                System.out.println(user);            }        }    }}

Insert

    INSERT INTO user(name, password) VALUES(#{name}, #{password});

@Testpublic void insertUser() {    try (SqlSession session = factory.openSession()) {        User user = new User();        user.setName("new_name1");        user.setPassword("new_password");        session.insert("namespace.insertUser", user);        session.commit();    }}

自增主键返回

修改mapper文件,添加,可以将MySQL的自增主键(即刚刚插入数据时生成的ID)返回:

            SELECT LAST_INSERT_ID();        INSERT INTO user(name, password) VALUES(#{name}, #{password});

@Testpublic void insertUser() {    try (SqlSession session = factory.openSession()) {        System.out.println(session);        User user = new User(null, "new_name", "new_password");        session.insert("namespace.insertUser", user);        // 需要在commit之后才能获得自增主键        session.commit();        System.out.println(user.getId());    }}

该功能还可以通过的useGeneratedKeys/keyProperty两个属性合作完成, 详见MyBatis文档.

Update

    UPDATE user SET name = #{name}, password = #{password} WHERE id = #{id};

@Testpublic void updateUserById() {    try (SqlSession session = factory.openSession(true)) {        session.update("namespace.updateUserById",                new User(1, "feiqing", "ICy5YqxZB1uWSwcVLSNLcA=="));    }}

Delete

    DELETE FROM user WHERE id = #{id};

@Testpublic void deleteUserById() {    try (SqlSession session = factory.openSession(true)) {        session.delete("namespace.deleteUserById", 51615);    }}

小结

#{}/${}

SqlSession
提供操作数据库的方法(如:selectOne/selectList).但SqlSession是线程不安全的,因此最好将其定义成局部变量使用.

附: 最好在pom.xml中添加一个日志系统实现(logback/log4j), 这样会在调试程序时打印日志信息,便于查错, 以logback为例:

    ch.qos.logback    logback-classic    1.1.2

<code class="hljs xml"><code class="hljs xml"><code class="hljs xml"><code class="hljs java"><code class="hljs java"><code class="hljs applescript"><code class="hljs java"><code class="hljs xml"><code class="hljs cs"><code class="hljs haml"><code class="hljs java"><code class="hljs livecodeserver"><code class="hljs java"><code class="hljs xml"><code class="hljs xml"><configuration>    <property name="logRoot" value="/data/logs">    <property name="pattern" value="%d{HH:mm:ss.SSS} [%thread] %-5level %logger{0} - %msg%n">    <appender class="ch.qos.logback.core.ConsoleAppender" name="STDOUT">        <encoder>            <pattern>${pattern}</pattern>        </encoder>    </appender>    <appender class="ch.qos.logback.core.rolling.RollingFileAppender" name="FILE">        <rollingpolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">            <filenamepattern>${logRoot}/common-server.%d{yyyy-MM-dd}.log</filenamepattern>            <maxhistory>7</maxhistory>        </rollingpolicy>        <encoder>            <pattern>${pattern}</pattern>        </encoder>    </appender>    <root level="DEBUG">        <appender-ref ref="STDOUT">        <appender-ref ref="FILE">    </appender-ref></appender-ref></root></property></property></configuration></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

其他关于MyBatis日志的详细信息可参考MyBatis文档日志部分.

DAO开发

使用MyBatis开发DAO有两个方法,原始DAO开发与Mapper映射DAO开发.

原始DAO开发

原始DAO开发需要开发人员编写DAO接口与DAO实现,如根据ID查询用户信息:

<code class="hljs xml"><code class="hljs xml"><code class="hljs xml"><code class="hljs java"><code class="hljs java"><code class="hljs applescript"><code class="hljs java"><code class="hljs xml"><code class="hljs cs"><code class="hljs haml"><code class="hljs java"><code class="hljs livecodeserver"><code class="hljs java"><code class="hljs xml"><code class="hljs xml"><code class="hljs vbnet"><select id="selectUserById" parametertype="java.lang.Integer" resulttype="com.fq.domain.User">    SELECT * FROM user WHERE id = #{id};</select></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

/** * @author jifang * @since 16/2/22 上午10:20. */public interface UserDAO {    User selectUserById(Integer id) throws Exception;}

public class UserDAOImpl implements UserDAO {    private SqlSessionFactory factory;    public UserDAOImpl(SqlSessionFactory factory) {        this.factory = factory;    }    @Override    public User selectUserById(Integer id) throws Exception {        SqlSession session = factory.openSession();        User user = session.selectOne("namespace.selectUserById", id);        session.close();        return user;    }}

public class MyBatisClient {    @Test    public void originalClient() throws Exception {        UserDAO dao = new UserDAOImpl(new SqlSessionFactoryBuilder().                build(ClassLoader.getSystemResourceAsStream("mybatis/mybatis-configuration.xml")));        User user = dao.selectUserById(1);        System.out.println(user);    }}

Mapper映射开发

mapper映射开发方法只需编写DAO接口,MyBatis根据接口定义与mapper文件中的SQL语句动态创建接口实现.

            SELECT * FROM user WHERE id = #{id};    

注意: 此时namespace必须与UserDAO接口的全限定名相同.

/** * @author jifang * @since 16/2/22 下午2:57. */public class MyBatisClient {    private SqlSession session;    private SqlSessionFactory factory;    @Before    public void setUp() {        factory = new SqlSessionFactoryBuilder().                build(ClassLoader.getSystemResourceAsStream("mybatis/mybatis-configuration.xml"));        session = factory.openSession();    }    @Test    public void mapperClient() throws Exception {        UserDAO dao = session.getMapper(UserDAO.class);        User user = dao.selectUserById(1);        System.out.println(user);    }    @After    public void tearDown() {        session.close();    }}

mapper映射开发方法需要遵循以下规范:
mapper文件中的namespace与DAO接口的全限定名相同; mapper文件中的Statement的id与DAO接口方法名相同; mapper文件中的Statement的parameterType/resultType与DAO方法的入参/回参类型相同.

Mapper映射

mapper映射文件(如UserDAO.xml)主要作用是定义SQL语句(每个SQL是一个Statement),是MyBatis的核心.

MyBatis官方推荐使用mapper映射的方法来开发DAO,因此我们以后就不再过多介绍原始DAO的开发.

输入映射

多个形参

传递简单类型前面示例已经使用过,在此就不再赘述.当需要传递多个形参时,不再需要设置parameterType参数:

    UPDATE user SET name = #{1}, password = #{2} WHERE id = #{0};

void updateUserById(Integer id, String name, String password) throws Exception;

传入PO

MyBatis使用OGNL表达式解析对象属性值:

<code class="hljs xml"><code class="hljs xml"><code class="hljs xml"><code class="hljs java"><code class="hljs java"><code class="hljs applescript"><code class="hljs java"><code class="hljs xml"><code class="hljs cs"><code class="hljs haml"><code class="hljs java"><code class="hljs livecodeserver"><code class="hljs java"><code class="hljs xml"><code class="hljs xml"><code class="hljs vbnet"><code class="hljs java"><code class="hljs java"><code class="hljs cs"><code class="hljs xml"><code class="hljs java"><code class="hljs haml"><code class="hljs lasso"><code class="hljs vbnet"><select id="selectUserByNamePassword" parametertype="com.fq.domain.User" resulttype="com.fq.domain.User">    SELECT *    FROM user    WHERE name = #{name} AND password = #{password};</select></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

User selectUserByNamePassword(User user) throws Exception;

传入Map

<code class="hljs xml"><code class="hljs xml"><code class="hljs xml"><code class="hljs java"><code class="hljs java"><code class="hljs applescript"><code class="hljs java"><code class="hljs xml"><code class="hljs cs"><code class="hljs haml"><code class="hljs java"><code class="hljs livecodeserver"><code class="hljs java"><code class="hljs xml"><code class="hljs xml"><code class="hljs vbnet"><code class="hljs java"><code class="hljs java"><code class="hljs cs"><code class="hljs xml"><code class="hljs java"><code class="hljs haml"><code class="hljs lasso"><code class="hljs vbnet"><code class="hljs java"><code class="hljs vbnet"><select id="selectUserByMap" parametertype="java.util.Map" resulttype="com.fq.domain.User">    SELECT *    FROM user    WHERE name = #{name} AND password = #{password};</select></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

#{}花括号内对应Map的key.

User selectUserByMap(Map map) throws Exception;

输出映射

输出简单类型