大数据——Mybatis操作Mysql

案例：在Mysql的test库下有一张用户表user_info，有如下字段，通过MyBatista实现对表的增删改查

+---------------+--------------------------+------+-----+---------+----------------+
| Field         | Type                     | Null | Key | Default | Extra          |
+---------------+--------------------------+------+-----+---------+----------------+
| user_id       | int(6) unsigned zerofill | NO   | PRI | NULL    | auto_increment |
| user_name     | varchar(10)              | NO   |     | NULL    |                |
| user_account  | varchar(30)              | NO   |     | NULL    |                |
| user_pass     | varchar(50)              | NO   |     | NULL    |                |
| user_phone    | varchar(20)              | NO   | UNI | NULL    |                |
| user_gender   | char(1)                  | NO   |     | NULL    |                |
| user_pid      | varchar(20)              | NO   | UNI | NULL    |                |
| user_province | varchar(50)              | NO   |     | NULL    |                |
| user_city     | varchar(50)              | NO   |     | NULL    |                |
| user_district | varchar(50)              | NO   |     | NULL    |                |
| user_address  | varchar(200)             | NO   |     | NULL    |                |
| user_balance  | decimal(10,2)            | NO   |     | 2000.00 |                |
+---------------+--------------------------+------+-----+---------+----------------+

一、创建Maven工程

pom.xml中导入Mybatis和Mysql依赖

    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.5.6</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.47</version>
    </dependency>

创建Mysql配置文件datasource.properties

mysql.driver=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://192.168.131.200:3306/test?useSSL=false
mysql.username=root
mysql.password=12345678

二、创建实体类

       1.模拟用户登陆

package cn.kgc.entity;

public class User {
    /**
     * user_account:账号
     * user_pass:密码
     */
    private String user_account;
    private String user_pass;

    public User(String user_account, String user_phone) {
        this.user_account = user_account;
        this.user_pass = user_phone;
    }

    public String getUser_account() {
        return user_account;
    }

    public String getUser_pass() {
        return user_pass;
    }
}

        2.创建用户基本信息实体类

package cn.kgc.entity;

public class UserInfo {
    private Integer user_id;//用户ID
    private String user_name;//用户姓名
    private String user_account;//用户账号
    private String user_pass;//用户密码
    private String user_phone;//用户手机号
    private String user_pid;//用户身份证号
    private String user_gender;//用户性别
    private String user_province;//用户所在省
    private String user_city;//用户所在城市
    private String user_district;//用户所在街道
    private String user_address;//用户地址
    private String user_balance;//用户余额
    //创建无参构造
    public UserInfo() {
    }
    
    //创建get/set方法
    public Integer getUser_id() {
        return user_id;
    }

    public void setUser_id(Integer user_id) {
        this.user_id = user_id;
    }

    public String getUser_name() {
        return user_name;
    }

    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

    public String getUser_account() {
        return user_account;
    }

    public void setUser_account(String user_account) {
        this.user_account = user_account;
    }

    public String getUser_pass() {
        return user_pass;
    }

    public void setUser_pass(String user_pass) {
        this.user_pass = user_pass;
    }

    public String getUser_phone() {
        return user_phone;
    }

    public void setUser_phone(String user_phone) {
        this.user_phone = user_phone;
    }

    public String getUser_pid() {
        return user_pid;
    }

    public void setUser_pid(String user_pid) {
        this.user_pid = user_pid;
    }

    public String getUser_gender() {
        return user_gender;
    }

    public void setUser_gender(String user_gender) {
        this.user_gender = user_gender;
    }

    public String getUser_province() {
        return user_province;
    }

    public void setUser_province(String user_province) {
        this.user_province = user_province;
    }

    public String getUser_city() {
        return user_city;
    }

    public void setUser_city(String user_city) {
        this.user_city = user_city;
    }

    public String getUser_district() {
        return user_district;
    }

    public void setUser_district(String user_district) {
        this.user_district = user_district;
    }

    public String getUser_address() {
        return user_address;
    }

    public void setUser_address(String user_address) {
        this.user_address = user_address;
    }

    public String getUser_balance() {
        return user_balance;
    }

    public void setUser_balance(String user_balance) {
        this.user_balance = user_balance;
    }
    //重写toString方法
    @Override
    public String toString() {
        return "UserInfo{" +
                "user_id=" + user_id +
                ", user_name='" + user_name + '\'' +
                ", user_account='" + user_account + '\'' +
                ", user_pass='" + user_pass + '\'' +
                ", user_phone='" + user_phone + '\'' +
                ", user_pid='" + user_pid + '\'' +
                ", user_gender='" + user_gender + '\'' +
                ", user_province='" + user_province + '\'' +
                ", user_city='" + user_city + '\'' +
                ", user_district='" + user_district + '\'' +
                ", user_address='" + user_address + '\'' +
                ", user_balance='" + user_balance + '\'' +
                '}';
    }
}

三、创建接口

package cn.kgc.mapper;

import cn.kgc.entity.User;
import cn.kgc.entity.UserInfo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface UserMapper {
    //sql语句少时可以使用注解
    @Select("select count(user_id) from user_info  where user_account=#{user_account} and user_phone=#{user_phone}")
    int login(User user);
    
    //查找
    List<UserInfo> findBy(UserInfo userInfo);
    //删除
    int removeBy(int[] ids);
    //修改
    int update(UserInfo userInfo);
    //插入
    int add(List<UserInfo> list);
    
    //通过参数注解完成别名映射
    @Select("select count(user_id) from user_info  where user_account=#{account} and user_pass=md5(#{password})")
    int loginByAccPass(@Param("account") String account, @Param("password") String password);
}

四、userMapper.xml

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="cn.kgc.mapper.UserMapper">
    <resultMap id="userMap" type="UserInfo" autoMapping="false">
        <!--
         <result column="user_account" property="userAccount"></result>
         <result column="user_balance" jdbcType="DECIMAL" property="userBalance" javaType="java.lang.Double"></result>
        -->
    </resultMap>
    <!-- select/insert/update/delete id:捆绑Mapper接口中的方法名称 -->
    <!-- parameterMap/resultMap 为了修正实体类的属性和数据表中的字段名称、类型、数量不一致而存在的，尽量避免使用 -->
    <!-- parameterType/resultType 直接关联实体类型，需要在主类中做别名映射，否则需要全称 -->
    <!-- 简单固定的where条件：where field=value and ... -->
    <!-- 复杂动态的where条件: <where></where> 1、无条件时不添加where 2、多条件时自动去除第一个and -->
    <!-- 复杂动态的修改操作: <set></set> 多字段修改时自动去除最后一个逗号 -->
    <!-- 单分支:<if test="CONDITION"></if> -->
    <!-- 多分支:<choose><when test="CONDITION1"></when>...<otherwise></otherwise></choose> -->
    <!-- 条件表达式不能出现</<= 与标签语法冲突 需要转成>/>= -->
    <!-- parameterType: 非基本类型array/list/map 其他均不可出现 -->
    <!-- 循环:
        <foreach collection="array/list/map" item="alias" open="BEGIN_SIGN" close="END_SIGN" separator="SEP_SIGN" index="ix">
            #{alias},#{ix}
        </foreach>
    -->
    <select id="findBy" parameterType="UserInfo" resultType="UserInfo">
        select * from user_info
        <where>
            <choose>
                <when test="null != user_id">
                    user_id=#{user_id}
                </when>
                <when test="null != user_phone">
                    user_phone=#{user_phone}
                </when>
                <when test="null != user_pid">
                    user_pid=#{user_pid}
                </when>
                <otherwise>
                    <if test="null != user_name">
                        and user_name like concat(#{user_name},'%')
                    </if>
                    <if test="null != user_gender">
                        and user_gender=#{user_gender}
                    </if>
                    <if test="null != user_province">
                        and user_province=#{user_province}
                    </if>
                    <if test="null != user_city">
                        and user_city=#{user_city}
                    </if>
                    <if test="null != user_district">
                        and user_district=#{user_district}
                    </if>
                    <if test="null != user_balance">
                        and user_balance>=#{user_balance}
                    </if>

                </otherwise>
            </choose>
        </where>
    </select>

    <delete id="removeBy">
        delete from user_info where user_id in

        <foreach collection="array" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </delete>

    <update id="update" parameterType="UserInfo">
        update user_info
        <set>
            <if test="null != user_phone">
                user_phone=#{user_phone},
            </if>
            <if test="null != user_pid">
                user_pid=#{user_pid},
            </if>

            <if test="null != user_name">
                user_name=#{user_name},
            </if>
            <if test="null != user_gender">
                user_gender=#{user_gender},
            </if>
            <if test="null != user_province">
                user_province=#{user_province},
            </if>
            <if test="null != user_city">
                user_city=#{user_city},
            </if>
            <if test="null != user_district">
                user_district=#{user_district},
            </if>
            <if test="null != user_address">
                user_address=#{user_address},
            </if>
        </set>
        where user_id=#{user_id}
    </update>

    <insert id="add">
        insert into user_info(user_name,user_account,user_pass,user_phone,user_gender,
        user_pid,user_province,user_city,user_district,user_address) values
        <foreach collection="list" item="user" separator=",">
            (#{user.user_namme},#{user.user_account},#{user.user_pass},#{user.user_phone},#{user.user_gender},
            #{user.user_pid},#{user.user_province},#{user.user_city},#{user.user_district},#{user.user_address})
        </foreach>
    </insert>

</mapper>

五、全局配置文件mybatis.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>
    <properties resource="datasource.properties"/>
    <typeAliases>
        <package name="cn.kgc.entity"/>
    </typeAliases>
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <!-- POOLED:连接池-->
            <dataSource type="POOLED">
                <property name="driver" value="${mysql.driver}"/>
                <property name="url" value="${mysql.url}"/>
                <property name="username" value="${mysql.username}"/>
                <property name="password" value="${mysql.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!--<mapper resource ="cn.kgc.mapper/userMapper.xml"/> 缺陷:每份*.xml都需要配 -->
        <package name="cn.kgc.mapper"/>
    </mappers>
</configuration>

六、主程序代码

package cn.kgc;

import cn.kgc.entity.UserInfo;
import cn.kgc.mapper.UserMapper;
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;
import java.util.List;

/**
 * mybatis: 半自动化ORM（Object Relationship Mapping）框架
 * User{userName,userPass}
 * select ... from where userName=#{userName} and userPass=#userPass{userPass}
 * 动态SQL
 */
public class App 
{
    public static void main( String[] args ) throws IOException {
        final String RESOURCE = "mybatis.xml";
        InputStream stream = Resources.getResourceAsStream(RESOURCE);
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(stream);
        SqlSession sqlSession = factory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        UserInfo userInfo = new UserInfo();
        userInfo.setUser_id(1);
        List<UserInfo> list = userMapper.findBy(userInfo);
        for (UserInfo info : list) {
            System.out.println(info);
        }
        sqlSession.close();
    }
}

 七、结果展示