MyBatis创建第二个项目

MyBatis创建第二个项目

Mysql文件配置

/*
 Navicat Premium Data Transfer

 Source Server         : Mybatis
 Source Server Type    : MySQL
 Source Server Version : 50528
 Source Host           : localhost:3306
 Source Schema         : mybatis

 Target Server Type    : MySQL
 Target Server Version : 50528
 File Encoding         : 65001

 Date: 14/03/2022 18:09:01
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `pwd` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '张三', '123');
INSERT INTO `user` VALUES (2, '李四', '236');
INSERT INTO `user` VALUES (3, '王五', '456');
INSERT INTO `user` VALUES (5, '法外狂徒', '478');

SET FOREIGN_KEY_CHECKS = 1;

配置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>com.wfy</groupId>
    <artifactId>MybatisDemo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</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>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>
</project>

创建数据库连接信息配置文件 db.properties

mysql.driver=com.mysql.cj.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&\
  characterEncoding=utf8&useUnicode=true&useSSL=false
mysql.username=root
mysql.password=root

创建MyBatis核心配置文件 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>
    <properties resource="db.properties"/>
    <environments default="development">
        <environment id="development">
            <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>
    <mappers>
        <mapper resource="com/wfy/mapper/CustomerMapper.xml"/>
    </mappers>
</configuration>

创建pojo实体

package com.wfy.pojo;

public class Customer {
    private  int id ;
    private  String username;
    private  String jobs ;
    private  String phone;

    public  Customer(){

    }

    public Customer(int id, String username, String jobs, String phone) {
        this.id = id;
        this.username = username;
        this.jobs = jobs;
        this.phone = phone;
    }

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getJobs() {
        return jobs;
    }

    public void setJobs(String jobs) {
        this.jobs = jobs;
    }

    public String getPhone() {
        return phone;
    }

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

    @Override
    public String toString() {
        return "Customer{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", jobs='" + jobs + '\'' +
                ", phone='" + phone + '\'' +
                '}';
    }
}

创建工具类

package com.wfy.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.Console;
import java.io.InputStream;

public class MyBatisUtils {

    static    SqlSessionFactory factory=null;

    static {
        String configFile= "mybatis-config.xml";
        try {
            InputStream stream= Resources.getResourceAsStream(configFile);
            factory = new SqlSessionFactoryBuilder().build(stream);
        }
        catch (Exception e)
        {
            System.out.println(e);
        }

    }

    public static SqlSession getSession()
    {
        return factory.openSession();
    }
}

创建映射文件CustomerMapper.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.wfy.mapper.CustomerMapper">
    <select id="findByNameAndJob" resultType="com.wfy.pojo.Customer" >

select * from customer
<where>
    <if test=" username !=null and username !=''">
        and username like concat ('%',#{username},'%')
    </if>
    <if test=" jobs !=null and jobs !=''">
        and jobs =#{jobs}
    </if>
</where>
    </select>
    <select id="findByNameOrJob" resultType="com.wfy.pojo.Customer">
        select * from customer
        <where>
        <choose>
            <when test="username != null and username !=''">
                and username like concat ('%',#{username},'%')
            </when>
            <when test="jobs !=null and jobs !=''">
                and jobs =#{jobs}
            </when>
            <otherwise>
                and phone is not null
            </otherwise>
        </choose>
        </where>
    </select>

    <select id="findCustomerByNameAndJob" resultType="com.wfy.pojo.Customer">
        select * from customer
<trim prefix="where" prefixOverrides="and">
    <if test="username !=null and username != ''">
        and username like concat('%',#{username},'%')
    </if>
    <if test=" jobs !=null and jobs !=''">
        and jobs =#{jobs}
    </if>
</trim>
    </select>

    <update id="updateCustomerBySet" parameterType="com.wfy.pojo.Customer" >
        update customer
        <set>
            <if test="username!=null and username!=''">
                username=#{username},
            </if>
            <if test="jobs!=null and jobs!=''">
                jobs=#{jobs},
            </if>
            <if test="phone!=null and phone!=''">
                phone=#{phone},
            </if>
        </set>
        where id=#{id}
    </update>

</mapper>

创建接口CustomerMapper

package com.wfy.mapper;

import com.wfy.pojo.Customer;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface CustomerMapper {
    List<Customer> findByNameAndJob(@Param("username") String username , @Param("jobs") String jobs) ;

    List<Customer> findByNameOrJob( @Param("username") String username , @Param("jobs") String jobs);

    List<Customer> findCustomerByNameAndJob(@Param("username") String username,@Param("jobs") String jobs);

    int updateCustomerBySet(Customer customer);
}

创建测试类

package Test;

import com.wfy.mapper.CustomerMapper;
import com.wfy.pojo.Customer;
import com.wfy.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class CustomerTest {

    @Test
    public void  TestfindByNameAndJob( ){
   //获取连接
        SqlSession sqlSession = MyBatisUtils.getSession();
        CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
        List<Customer> customers= (List<Customer>) mapper.findByNameAndJob("张三","教师");
        for(Customer customer:customers)
        System.out.println(customer);

        sqlSession.close();
    }

    @Test
    public void findByNameOrJobTest(){
        //获取连接
        SqlSession sqlSession = MyBatisUtils.getSession();
        CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
        List<Customer> customers =mapper.findByNameOrJob("WFY","CEO");
        for (Customer customer : customers) {
            System.out.println(customer);
        }
        sqlSession.close();
    }

    @Test
    public  void findCustomerByNameAndJob(){
        SqlSession sqlSession = MyBatisUtils.getSession();
        CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
        List<Customer> customerList = mapper.findCustomerByNameAndJob("绫小路清隆", "学生");
        for (Customer customer : customerList) {
            System.out.println(customer);
        }
           sqlSession.close();
    }

    @Test
    public  void updateCustomerByTest(){
        SqlSession sqlSession = MyBatisUtils.getSession();
        CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
      int rows = mapper.updateCustomerBySet(new Customer(1,"法外狂徒","律师","12345678"));
if(rows>0){
    System.out.println("成功修改"+rows+"条数据");
}else {
    System.out.println("执行修改操作失败");
}
sqlSession.commit();
sqlSession.close();
    }

}