mybatis基础

 

简介

MyBatis是一个支持普通SQL查询、存储过程和高级映射的优秀持久层框架（ORM框架），可以使用简单的XML或注解配置，将接口和Java的POJO映射成数据库中的记录。

ORM概念：Object Relational Mapping，“对象关系映射”，解决了对象和关系型数据库之间的数据交互问题

数据库表--->实体类
数据库表中字段--->实体类的属性
数据库表中字段的类型--->实体类中属性的类型
数据库表中记录--->对象

 

搭建MyBatis环境

idea下载插件、配置maven

下载插件，下载完成后重启idea

配置maven

 

pom依赖

主要是mybatis和mysql驱动，另外，log4j是打印日志的，junit是做单元测试的

<?xml versinotallow="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:schemaLocatinotallow="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.qzcsbj.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>1.0-SNAPSHOT</version>


    <dependencies>
        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>

        <!-- mysql驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/log4j/log4j -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

        <!--单元测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>



    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
        </resources>
    </build>
</project>

 

resources下添加日志配置文件：log4j.properties

### 根logger 设置###
log4j.rootLogger=DEBUG, Console

### 输出信息到控制台 ###
log4j.appender.Cnotallow=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.Cnotallow=%d [%t] %-5p [%c] - %m%n

log4j.logger.java.sql.ResultSet=INFO
log4j.logger.org.apache=INFO
log4j.logger.java.sql.Cnotallow=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

　　

resources下添加数据库连接配置文件：jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.117.180:3306/gifts?useUnicode=true&characterEncoding=utf-8&useSSL=true
jdbc.username=root
jdbc.password=qzcsbj

 

resources下创建主配置文件：mybatis-config.xml

<?xml versinotallow="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <!--引入外部的数据库配置文件-->
    <properties resource="jdbc.properties"></properties>

    <!--环境-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>


    <!--引入映射文件-->
    <mappers>
        <mapper resource="com\qzcsbj\mapper\UserMapper.xml"/>
    </mappers>

</configuration>

 

准备测试数据

建库

 

建表、造数据

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL COMMENT '用户名',
  `password` varchar(255) NOT NULL COMMENT '密码',
  `realname` varchar(255) NOT NULL COMMENT '真实名',
  `sex` tinyint(1) DEFAULT NULL COMMENT '性别',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `phone` varchar(255) NOT NULL COMMENT '电话',
  `utype` tinyint(1) DEFAULT NULL COMMENT '用户类型',
  `addtime` datetime DEFAULT NULL COMMENT '添加时间',
  `adduser` varchar(255) DEFAULT NULL COMMENT '添加者',
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
insert into `user` values(1,'qzcsbj1','62f7f5673e94eca261b8fce7ae7863a4','qzcsbj1',1,'2018-09-06','13800000001',0,now(),'qzcsbj');
insert into `user` values(2,'qzcsbj2','62f7f5673e94eca261b8fce7ae7863a4','qzcsbj2',1,'2018-09-07','13800000002',0,now(),'qzcsbj');
insert into `user` values(3,'qzcsbj3','62f7f5673e94eca261b8fce7ae7863a4','qzcsbj3',1,'2018-09-08','13800000003',0,now(),'qzcsbj');
insert into `user` values(4,'qzcsbj4','62f7f5673e94eca261b8fce7ae7863a4','qzcsbj4',1,'2018-09-09','13800000004',0,now(),'qzcsbj');
insert into `user` values(5,'qzcsbj5','62f7f5673e94eca261b8fce7ae7863a4','qzcsbj5',1,'2018-09-10','13800000005',0,now(),'qzcsbj');

 

 idea连接数据库

 

 

 

 

示例：添加用户

User.java

package com.qzcsbj.bean;

import java.util.Date;

/**
 * @公众号 : 全栈测试笔记
 * @描述 : <>
 */
public class User {
    private Integer id;
    private String username;
    private String password;
    private String realname;
    private String sex;
    private Date birthday;
    private String phone;
    private String utype;
    private Date addtime;
    private String adduser;


    public User() {
    }

    public User(String username, String password, String realname, String sex, Date birthday, String phone, String utype, String adduser) {
        this.username = username;
        this.password = password;
        this.realname = realname;
        this.sex = sex;
        this.birthday = birthday;
        this.phone = phone;
        this.utype = utype;
        this.adduser = adduser;
    }

    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 String getPassword() {
        return password;
    }

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

    public String getRealname() {
        return realname;
    }

    public void setRealname(String realname) {
        this.realname = realname;
    }

    public String getSex() {
        return sex;
    }

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

    public Date getBirthday() {
        return birthday;
    }

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

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getUtype() {
        return utype;
    }

    public void setUtype(String utype) {
        this.utype = utype;
    }

    public Date getAddtime() {
        return addtime;
    }

    public void setAddtime(Date addtime) {
        this.addtime = addtime;
    }

    public String getAdduser() {
        return adduser;
    }

    public void setAdduser(String adduser) {
        this.adduser = adduser;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", realname='" + realname + '\'' +
                ", sex='" + sex + '\'' +
                ", birthday=" + birthday +
                ", phnotallow='" + phone + '\'' +
                ", utype='" + utype + '\'' +
                ", addtime=" + addtime +
                ", adduser='" + adduser + '\'' +
                '}';
    }
}

 

封装工具类：MyBatisUtils.java

package com.qzcsbj.utils;

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 java.io.IOException;
import java.io.InputStream;

/**
 * @公众号 : 全栈测试笔记
 * @描述 : <>
 */
public class MyBatisUtils {
    public static SqlSession getSession(){
        SqlSession sessinotallow=null;
        InputStream inputStream=null;
        try {
            inputStream = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            session = sqlSessionFactory.openSession(true);  // mybatis增、删、改要手动提交事务，true表示自动提交事务
            return session;
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if (inputStream != null){
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    public static void closeSession(SqlSession session){
        if(session != null){
            session.close();
        }
    }
}

　　

接口层mapper文件

package com.qzcsbj.mapper;

import com.qzcsbj.bean.User;

import java.util.List;

/**
 * @公众号 : 全栈测试笔记
 * @描述 : <>
 */
public interface UserMapper {
    public abstract int addUser(User user);
}

　　

映射文件

<?xml versinotallow="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.qzcsbj.mapper.UserMapper">

    <!--parameterType是参数类型，可以省略；新增、更新、删除，不需要写返回值，默认是int-->
    <insert id="addUser" parameterType="com.qzcsbj.bean.User">
        insert into user values(null,#{username},#{password},#{realname},#{sex},#{birthday},#{phone},#{utype},now(),#{adduser})
    </insert>

</mapper>

 

测试类

package com.qzcsbj;

import com.qzcsbj.bean.User;
import com.qzcsbj.mapper.UserMapper;
import com.qzcsbj.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.util.Date;
import java.util.List;

/**
 * @公众号 : 全栈测试笔记
 * @描述 : <>
 */
public class testMybatis {

    SqlSession session = null;
    UserMapper userMapper = null;

    @Before
    public void init(){
        System.out.println("初始化。。。");
        session = MyBatisUtils.getSession();
        userMapper = session.getMapper(UserMapper.class);
    }

    @After
    public void destory(){
        System.out.println("关闭session");
        MyBatisUtils.closeSession(session);
    }

    @Test
    public void testAddUser(){
        User user = new User("qzcsbj9","123456","qzcsbj9","1",new Date(),"13800000009","1","qzcsbj");
        int n = userMapper.addUser(user);
        System.out.println(n>0?"新增成功":"新增失败");
    }
}

 

结果

 

 

 

优化配置文件：mybatis-config.xml

typeAliases

自定义别名：

<typeAliases>
        <!--方法一：别名可以随意定义，比如：XXX，但是最好和类名一样，见名知意-->
        <!--<typeAlias type="com.qzcsbj.bean.User" alias="User"/>-->

        <!--方法二（推荐）：直接给所有的实体类取别名。默认的实体类的别名就是类名，不区分小大写-->
        <package name="com.qzcsbj.bean"/>
    </typeAliases>

 

此时，映射文件中就可以直接写类名

<?xml versinotallow="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.qzcsbj.mapper.UserMapper">

    <insert id="addUser" parameterType="User">
        insert into user values(null,#{username},#{password},#{realname},#{sex},#{birthday},#{phone},#{utype},now(),#{adduser})
    </insert>

</mapper>

 

mappers

mapper映射文件的引入有3种方式：

<mappers>
        <!--方法一-->
        <!--<mapper resource="com\qzcsbj\mapper\UserMapper.xml"/>-->

        <!--方法二-->
        <!--<mapper class="com.qzcsbj.mapper.UserMapper"/>-->

        <!--方法三（推荐）：写接口的包名，包下所有mapper接口会自动映射-->
        <package name="com.qzcsbj.mapper"/>
    </mappers>

 

示例：根据id查询、更新、删除

mapper接口

package com.qzcsbj.mapper;

import com.qzcsbj.bean.User;

import java.util.List;

/**
 * @公众号 : 全栈测试笔记
 * @描述 : <>
 */
public interface UserMapper {
    public abstract int addUser(User user);
    public User getUserById(int id);
    public int updateUserById(User user);
    public int deleteUserById(int id);
}

　　

映射文件

<?xml versinotallow="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.qzcsbj.mapper.UserMapper">

    <insert id="addUser" parameterType="User">
        insert into user values(null,#{username},#{password},#{realname},#{sex},#{birthday},#{phone},#{utype},now(),#{adduser})
    </insert>

    <select id="getUserById" resultType="User" parameterType="int">
        select * from user where id=#{id}
    </select>

    <update id="updateUserById" parameterType="User">
        update user set username=#{username},password=#{password},realname=#{realname},sex=#{sex},birthday=#{birthday},phnotallow=#{phone},utype=#{utype} where id=#{id}
    </update>

    <delete id="deleteUserById">
        delete from user where id=#{id}
    </delete>

</mapper>

 

测试类

package com.qzcsbj;

import com.qzcsbj.bean.User;
import com.qzcsbj.mapper.UserMapper;
import com.qzcsbj.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.util.Date;
import java.util.List;

/**
 * @公众号 : 全栈测试笔记
 * @描述 : <>
 */
public class testMybatis {

    SqlSession session = null;
    UserMapper userMapper = null;

    @Before
    public void init(){
        System.out.println("初始化。。。");
        session = MyBatisUtils.getSession();
        userMapper = session.getMapper(UserMapper.class);
    }

    @After
    public void destory(){
        System.out.println("关闭session");
        MyBatisUtils.closeSession(session);
    }

    @Test
    public void testAddUser(){
        User user = new User("qzcsbj9","123456","qzcsbj9","1",new Date(),"13800000009","1","qzcsbj");
        int n = userMapper.addUser(user);
        System.out.println(n>0?"新增成功":"新增失败");
    }

    @Test
    public void testGetUserById(){
        User user = userMapper.getUserById(1);
        System.out.println(user);
    }


    @Test
    public void testUpdateUserById(){
        // 先查询出来，再更新
        User user = userMapper.getUserById(1);
        user.setUsername("qzcsbj1-1");
        user.setRealname("qzcsbj1-1");
        user.setAdduser("hello");
        int n = userMapper.updateUserById(user);
        System.out.println(n>0?"更新成功":"更新失败");
    }

    @Test
    public void testDeleteUserById(){
        int n = userMapper.deleteUserById(11);
        System.out.println(n>0?"删除成功":"删除失败");
    }
}

 

结果

根据id查询

 

根据id更新

 

 

根据id删除

 

模糊查询

mapper接口

public abstract List<User> findUserByUsername(String username);

 

映射文件

<!--模糊查询-->
    <select id="findUserByUsername" resultType="User">
        select * from user where username like #{username}
    </select>

 

测试类

// 模糊查询
    @Test
    public void testLike(){
        String keyword = "qzcs";  // 用户输入的字符串
        List<User> users = userMapper.findUserByUsername("%" + keyword + "%");
        for (User user : users) {
            System.out.println(user);
        }
    }

 

 

获取新增成功后的主键

表中主键需要是自增的

 

mapper接口

public abstract int addUser(User user);

 

映射文件

<!--
        useGeneratedKeys="true"，开启自动增长的映射
        keyProperty="id"，表中自增字段id所对应对象中的属性名
    -->
    <insert id="addUser" parameterType="User" useGeneratedKeys="true" keyProperty="id">
        insert into user values(null,#{username},#{password},#{realname},#{sex},#{birthday},#{phone},#{utype},now(),#{adduser})
    </insert>

　　

测试类

@Test
    public void testAddUser(){
        User user = new User("qzcsbj9","123456","qzcsbj9","1",new Date(),"13800000009","1","qzcsbj");
        int n = userMapper.addUser(user);
        System.out.println(n>0?"新增成功，id = "+user.getId():"新增失败");
    }

 

结果

 

 

parameterType的入参：简单类型、pojo类型、HashMap类型

简单类型

参考：根据id查询用户 

pojo类型

参考：新增用户

传入的参数是Map类型（HashMap）

mapper接口

// 按时间区间查询
    public List<User> findUserByBirthday(Map<String,Object> map);

　　

映射文件

<select id="findUserByBirthday" resultType="User">
        select * from user where birthday between #{xxx} and #{yyy}
    </select>

　　

测试类

注意：map的key要和sql中的占位符保持名字一致

@Test
    public void testFindUserByBirthday(){
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("xxx","2018-09-07");
        map.put("yyy","2018-09-09");
        List<User> users = userMapper.findUserByBirthday(map);
        for (User user : users) {
            System.out.println(user);
        }
    }

　　

结果

 

数据库表字段名和实体类属性名不一样

数据库中表字段名username改为uname

 

mapper接口

public abstract List<User> findAll();

　　

映射文件

<select id="findAll" resultType="User">
       select * from user
    </select>

　　

测试类

@Test
    public void testFindAll(){
        List<User> all = userMapper.findAll();
        for (User user : all) {
            System.out.println(user);
        }
    }

　　

结果

username是null

 

解决方法一：

在sql中给uname取别名为username

<select id="findAll" resultType="User">
        select id,uname as "username",password,realname,sex,birthday,phone,utype,addtime,adduser from user
    </select>

 

解决方法二：

通过resultMap来映射字段名和实体类属性名的一一对应关系

<select id="findAll" resultMap="UserMap">
       select * from user
    </select>

    <resultMap id="UserMap" type="User">
        <!--主键字段用id标签-->
        <!--<id column="id" property="id"/>-->
        <!--普通字段用result标签-->
        <result column="uname" property="username"/>
    </resultMap>

　

结果　

 

【bak1】

【bak2】

 

__EOF__

本文作者：持之以恒（韧）​