mybatis 映射mysql

本文主要是介绍mybatis 映射mysql，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

mybatis 映射mysql

step1: F:\project\goodsql52\goodQuery\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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.5.2</version>
    <relativePath/> <!-- lookup parent from repository -->
  </parent>
  <groupId>com.MyRedis</groupId>
  <artifactId>test</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>test</name>
  <description>Demo project for Spring Boot</description>
  <properties>
    <java.version>1.8</java.version>
  </properties>
  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter</artifactId>
      <exclusions>
        <exclusion>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-logging</artifactId>
        </exclusion>
      </exclusions>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
      <exclusions>
        <exclusion>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-logging</artifactId>
        </exclusion>
      </exclusions>

    </dependency>
    <!--  thymeleaf模板引擎-->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-thymeleaf</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-log4j</artifactId>
      <version>1.3.8.RELEASE</version>
    </dependency>


    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-devtools</artifactId>
      <scope>runtime</scope>
      <optional>true</optional>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
    <!-- Spring Boot Redis依赖 -->
    <!-- 注意：1.5版本的依赖和2.0的依赖不一样，注意看哦 1.5我记得名字里面应该没有“data”, 2.0必须是“spring-boot-starter-data-redis” 这个才行-->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-redis</artifactId>
      <!-- 1.5的版本默认采用的连接池技术是jedis  2.0以上版本默认连接池是lettuce, 在这里采用jedis，所以需要排除lettuce的jar -->
      <exclusions>
        <exclusion>
          <groupId>redis.clients</groupId>
          <artifactId>jedis</artifactId>
        </exclusion>
        <exclusion>
          <groupId>io.lettuce</groupId>
          <artifactId>lettuce-core</artifactId>
        </exclusion>
      </exclusions>
    </dependency>

    <!-- 添加jedis客户端 -->
    <dependency>
      <groupId>redis.clients</groupId>
      <artifactId>jedis</artifactId>
    </dependency>

    <!--spring2.0集成redis所需common-pool2-->
    <!-- 必须加上，jedis依赖此  -->
    <!-- spring boot 2.0 的操作手册有标注 大家可以去看看 地址是：https://docs.spring.io/spring-boot/docs/2.0.3.RELEASE/reference/htmlsingle/-->
    <dependency>
      <groupId>org.apache.commons</groupId>
      <artifactId>commons-pool2</artifactId>
      <version>2.5.0</version>
    </dependency>

    <!-- 将作为Redis对象序列化器 -->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>fastjson</artifactId>
      <version>1.2.47</version>
    </dependency>
    <!---->
    <dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>1.2.0</version>
    </dependency>
    <!---->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
        <configuration> <fork>true</fork> </configuration>
      </plugin>
    </plugins>
  </build>

</project>

step2: F:\project\goodsql52\goodQuery\src\main\resources\application.properties

server.port=8088
server.servlet.context-path=/
spring.mvc.view.prefix=/templates/
spring.mvc.view.suffix=.html

spring.datasource.url=jdbc:mysql://localhost/gft?characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=gft123456

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
mybatis.mapper-locations= classpath:**/mapper/*.xml
spring.datasource.druid.initial-size= 5
spring.datasource.druid.min-idle= 5
spring.datasource.druid.max-active= 20
spring.datasource.druid.max-wait= 60000
spring.datasource.druid.time-between-eviction-runs-millis= 60000
spring.datasource.druid.min-evictable-idle-time-millis= 300000 
spring.datasource.druid.validation-query= SELECT 1 FROM DUAL
spring.datasource.druid.test-while-idle= true
spring.datasource.druid.test-on-borrow= false
spring.datasource.druid.test-on-return= false
spring.datasource.druid.pool-prepared-statements= true
spring.datasource.druid.max-pool-prepared-statement-per-connection-size= 20
spring.datasource.druid.filters= stat,wall
spring.datasource.druid.use-global-data-source-stat= true
spring.datasource.druid.connect-properties= druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
spring.datasource.druid.stat-view-servlet.login-username= admin
spring.datasource.druid.stat-view-servlet.login-password=123456
spring.datasource.druid.reset-enable= false
spring.datasource.druid.stat-view-servlet.url-pattern= /druid/*
spring.datasource.druid.web-stat-filter=
spring.datasource.druid.url-pattern= /* #��ӹ��˹���
spring.datasource.druid.exclusions= "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*" #���Թ��˸�ʽ


spring.redis.database=0
spring.redis.host=192.168.52.129
spring.redis.port=6379
spring.redis.password=123456
spring.redis.pool.max-active=8
spring.redis.pool.max-wait=-1
spring.redis.pool.max-idle=8
spring.redis.pool.min-idle=0
spring.redis.timeout=3000

step3: F:\project\goodsql52\goodQuery\src\main\resources\mapper\UserDao.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.myredis.test.dao.UserDao">
    <resultMap id="BaseResultMap" type="com.myredis.test.entity.UserEntity">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="username" jdbcType="VARCHAR" property="username" />
        <result column="password" jdbcType="VARCHAR" property="password" />
        <result column="createtime" jdbcType="TIMESTAMP" property="createtime" />
    </resultMap>
    <sql id="Base_Column_List">
        id, username, `password`, createtime
    </sql>

    <select id="selectAllPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from user
    </select>

    <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from user
        where id = #{id,jdbcType=INTEGER}
    </select>

    <select id="getListByPatameter" parameterType="map" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/> from user
        <where>
            <if test="username !=null and username !=''">
                and username = #{username}
            </if>
            <if test="password !=null and password !=''">
                and password = #{password}
            </if>
            <if test="createtime !=null and createtime !=''">
                and createtime like concat('%',#{createtime},'%')
            </if>


        </where>
    </select>
    <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
        delete from user
        where id = #{id,jdbcType=INTEGER}
    </delete>
    <insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.myredis.test.entity.UserEntity" useGeneratedKeys="true">
        insert into user (id,username, `password`, createtime
        )
        values (#{id,jdbcType=INTEGER},#{username,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}, #{createtime,jdbcType=TIMESTAMP}
               )
    </insert>
    <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.myredis.test.entity.UserEntity" useGeneratedKeys="true">
        insert into user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="username != null">
                username,
            </if>
            <if test="password != null">
                `password`,
            </if>
            <if test="createtime != null">
                createtime,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="username != null">
                #{username,jdbcType=VARCHAR},
            </if>
            <if test="password != null">
                #{password,jdbcType=VARCHAR},
            </if>
            <if test="createtime != null">
                #{createtime,jdbcType=TIMESTAMP},
            </if>
        </trim>
    </insert>
    <update id="updateByPrimaryKeySelective" parameterType="com.myredis.test.entity.UserEntity">
        update user
        <set>
            <if test="username != null">
                username = #{username,jdbcType=VARCHAR},
            </if>
            <if test="password != null">
                `password` = #{password,jdbcType=VARCHAR},
            </if>
            <if test="createtime != null">
                createtime = #{createtime,jdbcType=TIMESTAMP},
            </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>
    <update id="updateByPrimaryKey" parameterType="com.myredis.test.entity.UserEntity">
        update user
        set username = #{username,jdbcType=VARCHAR},
            `password` = #{password,jdbcType=VARCHAR},
            createtime = #{createtime,jdbcType=TIMESTAMP}
        where id = #{id,jdbcType=INTEGER}
    </update>
</mapper>

step4: F:\project\goodsql52\goodQuery\src\main\java\com\myredis\test\TestApplication.java

package com.myredis.test;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.annotation.ComponentScan;

@SpringBootApplication
public class TestApplication {

    public static void main(String[] args) {
        SpringApplication.run(TestApplication.class, args);
    }

}

step5: F:\project\goodsql52\goodQuery\src\main\java\com\myredis\test\service\UserService.java

package com.myredis.test.service;

import com.myredis.test.entity.UserEntity;

import java.util.List;
import java.util.Map;

public interface UserService {
    UserEntity selectByPrimaryKey(Integer id);  //query single

    List<UserEntity> selectAllPrimaryKey();  //query  all

    UserEntity getListByPatameter(String username, String password, String createtime);  //多条件查询

    int deleteByPrimaryKey(Integer id);  //delete

    int insert(UserEntity record);
}

step6: F:\project\goodsql52\goodQuery\src\main\java\com\myredis\test\service\impl\UserServiceImpl.java

package com.myredis.test.service.impl;

import com.myredis.test.dao.UserDao;
import com.myredis.test.entity.UserEntity;
import com.myredis.test.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private UserDao userDao;

    @Override
    public UserEntity selectByPrimaryKey(Integer id) {
        return userDao.selectByPrimaryKey(id);
    }

    @Override
    public List<UserEntity> selectAllPrimaryKey() {
        return userDao.selectAllPrimaryKey();
    }

    @Override
    public UserEntity getListByPatameter(String username, String password, String createtime) {
        return userDao.getListByPatameter(username,password,createtime);
    }

    @Override
    public int deleteByPrimaryKey(Integer id) {
        return userDao.deleteByPrimaryKey(id);
    }

    @Override
    public int insert(UserEntity record) {
        return userDao.insert(record);
    }
}

step7: F:\project\goodsql52\goodQuery\src\main\java\com\myredis\test\entity\UserEntity.java

package com.myredis.test.entity;

import java.io.Serializable;
import java.util.Date;

/**
 * user
 * @author
 */
public class UserEntity implements Serializable {
    /**
     * 主键
     */
    private Integer id;

    /**
     * 用户名
     */
    private String username;

    /**
     * 密码
     */
    private String password;

    /**
     * 创建时间
     */
    private Date createtime;

    private static final long serialVersionUID = 1L;

    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 Date getCreatetime() {
        return createtime;
    }

    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }

    @Override
    public boolean equals(Object that) {
        if (this == that) {
            return true;
        }
        if (that == null) {
            return false;
        }
        if (getClass() != that.getClass()) {
            return false;
        }
        UserEntity other = (UserEntity) that;
        return (this.getId() == null ? other.getId() == null : this.getId().equals(other.getId()))
                && (this.getUsername() == null ? other.getUsername() == null : this.getUsername().equals(other.getUsername()))
                && (this.getPassword() == null ? other.getPassword() == null : this.getPassword().equals(other.getPassword()))
                && (this.getCreatetime() == null ? other.getCreatetime() == null : this.getCreatetime().equals(other.getCreatetime()));
    }

    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((getId() == null) ? 0 : getId().hashCode());
        result = prime * result + ((getUsername() == null) ? 0 : getUsername().hashCode());
        result = prime * result + ((getPassword() == null) ? 0 : getPassword().hashCode());
        result = prime * result + ((getCreatetime() == null) ? 0 : getCreatetime().hashCode());
        return result;
    }

    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append(getClass().getSimpleName());
        sb.append(" [");
        sb.append("Hash = ").append(hashCode());
        sb.append(", id=").append(id);
        sb.append(", username=").append(username);
        sb.append(", password=").append(password);
        sb.append(", createtime=").append(createtime);
        sb.append(", serialVersionUID=").append(serialVersionUID);
        sb.append("]");
        return sb.toString();
    }
}

step8: F:\project\goodsql52\goodQuery\src\main\java\com\myredis\test\dao\UserDao.java

package com.myredis.test.dao;

import com.myredis.test.entity.UserEntity;
import com.myredis.test.entity.UserEntity;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
import org.springframework.ui.Model;

import java.util.List;
import java.util.Map;

/**
 * UserDao继承基类
 */
@Mapper
public interface UserDao{

    int deleteByPrimaryKey(Integer id);

    int insert(UserEntity record);

    int insertSelective(UserEntity record);

    UserEntity selectByPrimaryKey(Integer id);

    List<UserEntity> selectAllPrimaryKey();

    UserEntity getListByPatameter(String username,String password,String createtime);

    int updateByPrimaryKeySelective(UserEntity record);

    int updateByPrimaryKey(UserEntity record);

    List<UserEntity> getListByPatameter(Map<String, Object> map);
}

step9: F:\project\goodsql52\goodQuery\src\main\java\com\myredis\test\controller\IndexController.java

package com.myredis.test.controller;

import com.myredis.test.entity.UserEntity;
import com.myredis.test.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@Controller
@RequestMapping("api")
public class IndexController {
    @Autowired
    private UserService userService;

    @ResponseBody
    @RequestMapping("/query")
    public UserEntity query() {
        UserEntity mUserEntity = userService.selectByPrimaryKey(1);
        return mUserEntity;
    }

    @ResponseBody
    @RequestMapping("/get")
    public UserEntity get() {
        UserEntity mUserEntity = userService.getListByPatameter("gft", "123456", "2017-12-18");
        return mUserEntity;
    }

    @ResponseBody
    @RequestMapping("/all")
    public List<UserEntity> all() {
        List<UserEntity> list = userService.selectAllPrimaryKey();
        return list;
    }

    @ResponseBody
    @RequestMapping("/delete")
    public int delete() {
        int deleteId = userService.deleteByPrimaryKey(4);
        return deleteId;
    }

    @ResponseBody
    @RequestMapping("/insert")
    public int insert() {
        UserEntity mUserEntity = new UserEntity();
        mUserEntity.setId(109);
        mUserEntity.setUsername("张飞");
        mUserEntity.setPassword("123456");
        mUserEntity.setCreatetime(stringToDate("2021-09-18"));
        int insertId = userService.insert(mUserEntity);
        return insertId;
    }

    @RequestMapping(value = {"/postman"}, method = RequestMethod.POST)
    public String getQuery(HttpServletRequest request, HttpServletResponse response)
            throws IOException {
        String id = request.getParameter("id");
        System.out.println(id + "\t");
        return "user";
    }

    public static Date stringToDate(String time) {
        DateFormat format = new SimpleDateFormat("yyyy-MM-dd");//日期格式
        Date date = null;
        try {
            date = format.parse(time);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return date;
    }

}

run http://localhost:8088/api/all

[
    {
        "id": 1,
        "username": "ygz",
        "password": "123456",
        "createtime": "2021-06-04T16:00:00.000+00:00"
    },
    {
        "id": 2,
        "username": "gft",
        "password": "123456",
        "createtime": "2017-12-17T16:00:00.000+00:00"
    },
    {
        "id": 3,
        "username": "liwei",
        "password": "123456",
        "createtime": "2017-12-17T16:00:00.000+00:00"
    },
    {
        "id": 5,
        "username": "张飞",
        "password": "123456",
        "createtime": "2021-09-17T16:00:00.000+00:00"
    },
    {
        "id": 108,
        "username": "张飞",
        "password": "123456",
        "createtime": "2021-09-17T16:00:00.000+00:00"
    },
    {
        "id": 109,
        "username": "张飞",
        "password": "123456",
        "createtime": "2021-09-17T16:00:00.000+00:00"
    }
]

这篇关于mybatis 映射mysql的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！