mybatis连接数据库 实现增删改查操作

框架

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>cn.kgc</groupId>
  <artifactId>mybatiskb12</artifactId>
  <version>1.0</version>

  <name>mybatiskb12</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.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
  </properties>

  <dependencies>
    <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>
  </dependencies>

  <build>
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
      <plugins>
        <!-- clean lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#clean_Lifecycle -->
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.1.0</version>
        </plugin>
        <!-- default lifecycle, jar packaging: see https://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_jar_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-jar-plugin</artifactId>
          <version>3.0.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>
        <!-- site lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#site_Lifecycle -->
        <plugin>
          <artifactId>maven-site-plugin</artifactId>
          <version>3.7.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-project-info-reports-plugin</artifactId>
          <version>3.0.0</version>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>
</project>

App

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 javax.lang.model.element.VariableElement;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

/**
 * mybatis: 半自动化ORM(Object Relationship Mapping)框架
 * User {userName,userPass}
 *  select ...from where user_name = #{userName} and user_pass = #{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 info = new UserInfo();
        info.setUser_province("江苏省");
        List<UserInfo> list = userMapper.findBy(info);
        for (UserInfo userInfo : list) {
            System.out.println(userInfo);
        }
        sqlSession.close();
    }
}

UserInfo

package cn.kgc.entity;

public class UserInfo {
    private Integer user_id;
    private String user_name;
    private String user_account;
    private String user_pass;
    private String user_gender;
    private String user_phone;
    private String user_pid;
    private String user_province;
    private String user_city;
    private String user_district;
    private String user_address;
    private Double user_balance;

    public String getUser_gender() {
        return user_gender;
    }

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



    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_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 Double getUser_balance() {
        return user_balance;
    }

    public void setUser_balance(Double user_balance) {
        this.user_balance = user_balance;
    }

    @Override
    public String toString() {
        return "UserInfo{" +
                "user_id=" + user_id +
                ", user_name='" + user_name + '\'' +
                ", user_account='" + user_account + '\'' +
                ", user_pass='" + user_pass + '\'' +
                ", user_gender='" + user_gender + '\'' +
                ", user_phone='" + user_phone + '\'' +
                ", user_pid='" + user_pid + '\'' +
                ", user_province='" + user_province + '\'' +
                ", user_city='" + user_city + '\'' +
                ", user_district='" + user_district + '\'' +
                ", user_address='" + user_address + '\'' +
                ", user_balance=" + user_balance +
                '}';
    }
}

UserMapper

/*
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;
import java.util.regex.Pattern;

public interface UserMapper {
    @Select("select count(user_id) from user_info where user_account=#{user_account} and user_pass=md5(#{user_pass})")
    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);
}

*/
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 {
    //user_account sql中的字段，#{user_account} java中的属性
    @Select("select count(user_id) from user_info where user_account=#{user_account} and user_pass=md5(#{user_pass})")
    int login(User user);

    List<UserInfo> findBy(UserInfo userInfo);

    int removeBy(int[] array);

    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">
<!--相面这个路径是连接map和 的重点-->
<mapper namespace="cn.kgc.mapper.UserMapper">

    <!--autoMapping 当数量不一样的时候用   不自动映射
    <resultMap id="userMap" type="UserInfo" autoMapping="false">
        &lt;!&ndash;字段名不一致&ndash;&gt;
        <result column="user_account" property="userAccount"></result>
        &lt;!&ndash;类型不一致   mysql的类型映射成java的类型&ndash;&gt;
        <result column="user_balance" jdbcType="DECIMAL" property="userBalance" javaType="java.lang.Double"/>
    </resultMap>

    &lt;!&ndash;id代表方法名 对应mapper接口里面的方法 参数:   返回值是什么:resultMap 里面写的是 上面 的resultMap 的id&ndash;&gt;
    <select id="findBy" parameterType="UserInfo" resultMap="userMap">

    </select>-->
    <!--循环:
    <foreach collection="array|list|map"="alias" open="BEGIN_SIGN" close="END_SIN" separator="SEP_SIGN"
    index="ix">
        #{alias},#{ix}
    </foreach>

    -->
    <!--resultType指定的是我们创建的实体类-->
    <select id="findBy" parameterType="UserInfo" resultType="UserInfo">
        select * from user_info
        /* select|insert|update|delete id:捆绑Mapper接口中的方法名称
            parameterMap|resultMap 为了修正实体类的属性和数据表中字段名称,类型,数量不一致而存在的
            parameterType|resultType 直接关联实体类型
            简单固定的where条件:where field=value and ....
            <!--复杂动态的where条件:<where></where> 1无条件不添加where,多条件是自动去除第一个and
            单分支:<if test="CONDITION"></if> -->
            <!--多分支:<choose><when test="COND01"></when>...<otherwise></otherwise></choose>-->
            */
        <where>
            <!--<if test="null != user_id"></if>-->
            <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>

    <!--parameterType:非基本类型 只允许存在3个值   array|list|map-->
    <delete id="removeBy" >
        delete from user_info where user_id
        <foreach collection="array" item="id" open="in (" close=")" separator=",">
            #{id}
        </foreach>
    </delete>

    <update id="update" parameterType="UserInfo">
        update user_info
        /*<!--复杂动态的修改操作:<set></set>-->*/
        <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>
            <!--/*这里只能写>=   如果想<= 两边掉过来*/-->
            <if test="null != user_balance">
                user_gender>=#{user_balance},
            </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_name},#{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>

datasource.properties

mysql.driver=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://192.168.75.245:3306/test?useSSL=false
mysql.username=root
mysql.password=Fang@123

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:类型别名 -->
    <typeAliases>
        <package name="cn.kgc.entity"/>
    </typeAliases>
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"/>
            <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>
    <!--  <mapper class|resource 二选一 ，缺陷：每个*Mapper.xml都需要单独配置-->
    <!--
    <mappers>
        <mapper resource="cn/kgc/mapper/userMapper.xml"/>
    </mappers>
    -->
    <mappers>
        <package name="cn.kgc.mapper"/>
    </mappers>
</configuration>

注意事项:

实体类 里面的属性和getset方法的位置不能倒过来

userMapper.xml

datasource.properties

mybatis.xml 包内自动解析