MyBatis入门-实现数据库的增删改查(代码篇)

MyBatis入门级代码篇：

1. 建立一个Maven Java工程
2. 在pom.xml中导入依赖包

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>MyBatis03</groupId>
  <artifactId>MyBatis03</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>war</packaging>

  <name>MyBatis03 Maven Webapp</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
  </properties>
  

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.2.2</version>
    </dependency>
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.12</version>
    </dependency>

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.38</version>
    </dependency>
      <dependency>
          <groupId>junit</groupId>
          <artifactId>junit</artifactId>
          <version>4.12</version>
          <scope>compile</scope>
      </dependency>
  </dependencies>

  <build>
    <resources>
      <resource>
        <directory>src/main/java</directory>
        <includes>
          <include>**/*.xml</include>
        </includes>
      </resource>
      <resource>
        <directory>src/main/resources</directory>
        <includes>
          <include>**/*.*</include>
        </includes>
      </resource>
    </resources>
    <finalName>MyBatis03</finalName>
    <pluginManagement>
      <plugins>
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.1.0</version>
        </plugin>
        <!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->
        <plugin>
          <artifactId>maven-resources-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.8.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-surefire-plugin</artifactId>
          <version>2.22.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-war-plugin</artifactId>
          <version>3.2.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-install-plugin</artifactId>
          <version>2.5.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-deploy-plugin</artifactId>
          <version>2.8.2</version>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>
</project>

3、编写一个用户表t_user

use mybatis;
drop table if exists t_user;
create table t_user(
user_id int(11) not null auto_incremet,
username varchar(20) default null,
password varchar(20) default null,
create_time varchar(20) default null,
primary key(user_id))ENGINE=MyISAM DEFAULT CHARSET=utf8;

4、编写一个用户的实体类

package www.javaapi.com.cn.mybatis.entity;
import java.util.Date;
public class User {
    private Integer userId;
    private String username;
    private String password;
    private Date createTime;
    public Integer getUserId() {
        return userId;
    }

    @Override
    public String toString() {
        return "User{" +
                "userId=" + userId +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", createTime=" + createTime +
                '}';
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
}

5、编写一个UserMapper.java接口类

package www.javaapi.com.cn.mybatis.mapper;
import www.javaapi.com.cn.mybatis.entity.User;

import java.util.List;

public interface UserMapper {

    public void insert(User user); //1、插入用户

    public User selectById(Integer id); //2、查询单个用户

    public List<User> selectAll();//3、查询所有用户信息

    public void updateById(User user);//4、更新用户信息

    public void deleteById(Integer id);//5、删除
}

6、编写一个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="www.javaapi.com.cn.mybatis.mapper.UserMapper">
    <!--mybatis插入实例用户信息-->
    <insert id="insert" parameterType="www.javaapi.com.cn.mybatis.entity.User">
        insert into t_user(username,password,create_time) values(
        #{username,jdbcType=VARCHAR},
        #{password,jdbcType=VARCHAR},
        #{createTime,jdbcType=DATE,javaType=date}
        );
    </insert>
    <!--查询单个用户信息-->
    <select id="selectById" parameterType="int" resultType="www.javaapi.com.cn.mybatis.entity.User">
        select user_id, username,password,create_time from t_user
        where user_id=#{userId}
    </select>
    <!--查询所有用户信息-->
    <select id="selectAll" resultType="www.javaapi.com.cn.mybatis.entity.User">
        select user_id,username,password,create_time from t_user
    </select>
    <!--更新用户信息-->
    <update id="updateById" parameterType="www.javaapi.com.cn.mybatis.entity.User">
        update t_user set username=#{username},password=#{password} where user_id=#{userId}
    </update>
    <!--删除用户信息-->
    <delete id="deleteById" parameterType="int">
        delete from t_user where user_id=#{userId}
    </delete>
</mapper>

7、编写一个mybatis-config.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>
    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>
    <environments default="testMysql">
        <environment id="testMysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8"/>
                <property name="username" value="root"/>
                <property name="password" value="165217"/>
            </dataSource>
        </environment>
    </environments>
    <!--加载mybatis映射文件mapper-->
    <mappers>
        <mapper resource="www/javaapi/com/cn/mybatis/mapper/UserMapper.xml"/>
    </mappers>
</configuration>

8、写一个UserTest.java的测试类

package www.javaapi.com.cn.mybatis.test;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import www.javaapi.com.cn.mybatis.entity.User;
import www.javaapi.com.cn.mybatis.mapper.UserMapper;


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

public class UserTest {
    private SqlSessionFactory sqlSessionFactory;
    @Before
    public void init(){
        InputStream inputStream=null;
        try{
            //加载mybatis配置文件
            inputStream=org.apache.ibatis.io.Resources.getResourceAsStream("mybatis-config.xml");
        }catch(IOException e){
            e.printStackTrace();
        }
        //使用mybatis API操作
        SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
        sqlSessionFactory=builder.build(inputStream);
    }
    //2、mybatis插入数据
    @Test
    public void insert(){
        User user=new User();
        user.setUsername("张三");
        user.setPassword("123456");
        user.setCreateTime(new Date());
        SqlSession session=sqlSessionFactory.openSession();
        UserMapper mapper=session.getMapper(UserMapper.class);
        mapper.insert(user);
        session.commit();
        session.close();
    }
    //3、查询单个用户信息
    @Test
    public void getById(){
        SqlSession session=sqlSessionFactory.openSession();
        UserMapper mapper=session.getMapper(UserMapper.class);
        User user=mapper.selectById(1);
        System.out.println(user);
        session.close();
    }
    //查询所有的用户信息
    @Test
    public void selectAll(){
        SqlSession session=sqlSessionFactory.openSession();
        UserMapper mapper=session.getMapper(UserMapper.class);
        List<User> users=mapper.selectAll();
        for(User user:users){
            System.out.println(user);
        }
        session.close();
    }
    //4、更新用户信息
    @Test
    public void updateById(){
        SqlSession session=sqlSessionFactory.openSession();
        UserMapper mapper=session.getMapper(UserMapper.class);
        User user=mapper.selectById(1);
        user.setUsername("李四");
        user.setPassword("888888888888888");
        mapper.updateById(user);
        session.commit();
        session.close();
    }
    //5、删除用户
    @Test
    public void deleteById(){
        SqlSession session=sqlSessionFactory.openSession();
        UserMapper mapper=session.getMapper(UserMapper.class);
        mapper.deleteById(2);
        session.commit();
        session.close();
    }
}

9、编写日志信息

log4j.rootCategory=debug,stdout,R
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=[u63A7u5236u53F0]%p%r[%t]%C.%M(%L)|%m%n

log4j.appender.R=org.apache.log4j.DailyRollingFileAppender
log4j.appender.R.File=D:/mybatis_log.log
log4j.appender.R.layout=org.apache.log4j.PatternLayout
log4j.appender.R.layout.ConversionPattern=%d-[u65E5u5FD7u6587u4EF6]%p%t%c-%m%n
log4j.appender.R.stdout=org.apache.log4j.DailyRollingFileAppender

log4j.logger.org.apache.ibatis=debug
log4j.logger.org.apache.ibatis.common.jdbc.SimpleDataSource=debug
log4j.logger.com.ibatis.common.jdbc.ScriptRunner=debug
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=debug

log4j.logger.java.sql.Connection=info
log4j.logger.java.sql.Statement=info
log4j.logger.java.sql.PreparedStatement=info,stdout