Java框架篇：mybatis入门案例

1. 数据库准备

-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL COMMENT '用户名称',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `sex` char(1) DEFAULT NULL COMMENT '性别',
  `address` varchar(256) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '王五', null, '2', null);
INSERT INTO `user` VALUES ('10', '张三', '2014-07-10', '1', '北京市');
INSERT INTO `user` VALUES ('16', '张小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('22', '陈小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('24', '张三丰', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('25', '陈小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('26', '王五', null, null, null);

2. 创建maven项目

①、②、
③、

3. 引入依赖

位置：mybatis_crud\pom.xml

<dependencies>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.5.5</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.47</version>
    </dependency>
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.12</version>
    </dependency>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
  </dependencies>

4. 创建主配置文件

位置：mybatis_crud\src\main\resources\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="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
                <property name="username" value="root"/>
                <property name="password" value=""/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/lq/dao/UserDao.xml"/>
    </mappers>
</configuration>

5. 引入log4j.properties配置文件

位置：mybatis_crud\src\main\resources\log4j.properties

# Set root category priority to INFO and its only appender to CONSOLE.
#log4j.rootCategory=INFO, CONSOLE            debug   info   warn error fatal
log4j.rootCategory=debug, CONSOLE, LOGFILE

# Set the enterprise logger category to FATAL and its only appender to CONSOLE.
log4j.logger.org.apache.axis.enterprise=FATAL, CONSOLE

# CONSOLE is set to be a ConsoleAppender using a PatternLayout.
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n

# LOGFILE is set to be a File appender using a PatternLayout.
log4j.appender.LOGFILE=org.apache.log4j.FileAppender
log4j.appender.LOGFILE.File=d:\axis.log
log4j.appender.LOGFILE.Append=true
log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
log4j.appender.LOGFILE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n

6. 创建实体类

位置：mybatis_crud\src\main\java\com\lq\domain\User.java

package com.lq.domain;

import java.io.Serializable;
import java.util.Date;

public class User implements Serializable {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

7. 创建UserDao接口

位置：mybatis_crud\src\main\java\com\lq\dao\UserDao.java

package com.lq.dao;

import com.lq.domain.User;

import java.util.List;

public interface UserDao {

    /**
     * 查询所有用户
     * @return
     */
    List<User> findAll();

    /**
     * 注册用户
     * @param user 需要保存的用户
     */
    void saveUser(User user);

    /**
     * 修改用户
     * @param user
     */
    void updateUser(User user);

    /**
     * 删除用户
     * @param userId 用户id
     */
    void deleteUser(int userId);

    /**
     * 根据用户id查询用户信息
     * @param userId 用户id
     * @return
     */
    User getIdUser(int userId);
}

8. User的映射文件

位置： mybatis_crud\src\main\resources\com\lq\dao\UserDao.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.lq.dao.UserDao">
<!--    查询所有-->
    <select id="findAll" resultType="com.lq.domain.User">
        select * from user
    </select>
<!--    注册用户-->
    <insert id="saveUser" parameterType="com.lq.domain.User">
        insert into user(username,birthday,sex,address) value (#{username},#{birthday},#{sex},#{address});
    </insert>
<!--    修改用户-->
    <update id="updateUser" parameterType="com.lq.domain.User">
        UPDATE `user` SET username=#{username},birthday=#{birthday},`sex`=#{sex},`address`=#{address} WHERE id=#{id}
    </update>
<!--    删除用户-->
    <delete id="deleteUser" parameterType="int">
        delete from user where id=#{uid}
    </delete>
<!--    根据用户id查询用户信息-->
    <select id="getIdUser" resultType="com.lq.domain.User">
        select * from USER where id=#{uid};
    </select>
</mapper>

9. 编写测试类

位置：mybatis_crud\src\test\java\com\lq\test\MybatisTest.java

package com.lq.test;

import com.lq.dao.UserDao;
import com.lq.domain.User;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.InputStream;
import java.util.Date;
import java.util.List;

public class MybatisTest {
    InputStream in;
    SqlSession session;
    UserDao userDao;
    @Before
    public void init()throws Exception {
        // 1、读取配置文件
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        // 2、创建SqlSessionFactory
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        // 3、使用工厂生产SQLSession对象
        session = factory.openSession();
        // 4、使用SQLSession创建Dao接口的代理对象
        userDao = session.getMapper(UserDao.class);
    }

    @After
    public void destroy()throws Exception {
        // 6、释放资源
        session.close();
        in.close();
    }

    /**
     * 显示所有用户
     */
    @Test
    public void fandAll() {
        // 5、使用代理对象执行方法
        List<User> users = userDao.findAll();
        for (User user : users) {
            System.out.println(user);
        }
    }

    /**
     * 注册用户
     */
    @Test
    public void saveUser(){
        User user=new User();
        user.setUsername("mybatis");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setAddress("蟠桃花园");
        // 5、执行保存方法
        userDao.saveUser(user);
        // 6、提交事务回滚
        session.commit();
    }

    /**
     * 修改用户
     */
    @Test
    public void updateUser(){
        User user=new User();
        user.setId(26);
        user.setUsername("mybatis");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setAddress("蟠桃花园");
        // 5、执行修改方法
        userDao.updateUser(user);
        // 6、提交事务回滚
        session.commit();
    }

    /**
     * 删除用户
     */
    @Test
    public void deleteUser(){
        // 5、执行删除方法
        userDao.deleteUser(26);
        // 6、提交事务回滚
        session.commit();
    }

    /**
     * 根据用户id显示用户信息
     */
    @Test
    public void getIdUser() {
        // 5、使用代理对象执行方法
        User user = userDao.getIdUser(16);
        System.out.println(user);
    }

}