spring cloud mybaits 微服務增删改查(二)

在上一篇的基礎上增加增删改查功能

建立資料庫：

CREATE DATABASE ssmdemo;

建立表：

DROP TABLE IF EXISTS tb_user;
CREATE TABLE tb_user (
id char(32) NOT NULL,
user_name varchar(32) DEFAULT NULL,
password varchar(32) DEFAULT NULL,
name1 varchar(32) DEFAULT NULL,
age int(10) DEFAULT NULL,
sex int(2) DEFAULT NULL,
birthday date DEFAULT NULL,
created datetime DEFAULT NULL,
updated datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入資料

INSERT INTO ssmdemo.tb_user ( user_name, password, name1, age, sex, birthday, created, updated) VALUES ( ‘www’, ‘123456’, ‘我’, ‘22’, ‘1’, ‘2019-09-25’, sysdate(), sysdate());
INSERT INTO ssmdemo.tb_user ( user_name, password, name1, age, sex, birthday, created, updated) VALUES ( ‘ss’, ‘123456’, ‘哇’, ‘22’, ‘1’, ‘2019-09-25’, sysdate(), sysdate());

子產品結構：

spring cloud mybaits 微服務增删改查(二)

entity層：

public class User {
    private String id;
    private String user_name;
    private String password;
    private String name1;
    private Integer age;
    private Integer sex;
    private Date birthday;
    private String created;
    private String updated;
     
   /*setter and getter */
   ...
}

mybatis

引入依賴

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.2.8</version>
</dependency>

mybatis配置：

1.全局配置：mybatis-config.xml

2.映射檔案: UserMapper.xml

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">
<!-- 根标簽 -->
<!-- spring和MyBatis完美整合，不需要mybatis的配置映射檔案 -->
<configuration>
    <!-- 環境，可以配置多個，default：指定采用哪個環境 -->
    <environments default="test">
        <!-- id：唯一辨別 -->
        <environment id="test">
            <!-- 事務管理器，JDBC類型的事務管理器 -->
            <transactionManager type="JDBC" />
            <!-- 資料源，池類型的資料源 -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/ssmdemo?serverTimezone=UTC" />
                <property name="username" value="root" />
                <property name="password" value="123456" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mappers/UserMapper.xml"/>
    </mappers>
</configuration>

UserMapper.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">
<!--mappers：根标簽，namespace：命名空間，随便寫，在同一個命名空間下保持唯一-->
<mapper namespace="com.springcloud.produce.mapper.UserMapper">
<!--resultType:sql語句查詢結果集的封裝類型，tb_user即為資料庫中的表-->
<!--resultMap标簽，是為了映射select查詢出來結果的集合，主要作用是将實體類中的字段和資料庫表中的字段進行關聯映射-->
<!--當實體類中的字段與資料庫表中的字段相同時，可以将resultMap标簽中的關聯關系忽略不寫-->

    <!--使用别名-->
    <select id="queryUserById" resultType="com.springcloud.mybatis.pojo.User">
        SELECT
        tuser.id as id,
        tuser.user_name as user_name,
        tuser.password as password,
        tuser.name1 as name1,
        tuser.age as age,
        tuser.birthday as birthday,
        tuser.sex as sex,
        tuser.created as created,
        tuser.updated as updated
        from
        tb_user tuser
        where tuser.id = #{id};
    </select>

    <select id="queryUserAll" resultType="com.springcloud.mybatis.pojo.User">
        select * from tb_user;
    </select>

    <!--插入資料-->
    <insert id="insertUser" parameterType="com.springcloud.mybatis.pojo.User">
        INSERT INTO tb_user (
        id,
        user_name,
        password,
        name1,
        age,
        sex,
        birthday,
        created,
        updated
        )
        VALUES
        (
        #{id},
        #{user_name},
        #{password},
        #{name1},
        #{age},
        #{sex},
        #{birthday},
        now(),
        now()
        );
    </insert>

    <update id="updateUser" parameterType="com.springcloud.mybatis.pojo.User">
        UPDATE tb_user
        <trim prefix="set" suffixOverrides=",">
            <if test="user_name!=null">user_name = #{user_name},</if>
            <if test="password!=null">password = #{password},</if>
            <if test="name1!=null">name = #{name1},</if>
            <if test="age!=null">age = #{age},</if>
            <if test="sex!=null">sex = #{sex},</if>
            <if test="birthday!=null">birthday = #{birthday},</if>
            updated = now(),
        </trim>
        WHERE
        (id = #{id});
    </update>

    <delete id="deleteUser">
        delete from tb_user where id=#{id}
    </delete>
</mapper>

Mapper層UserMapper.java

需要與UserMapper.xml中的id對應!!

package com.springcloud.produce.mapper;

import com.springcloud.produce.entity.User;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserMapper {
    //根據id查詢使用者資訊
    @Select("select * from tb_user where id = #{id}")
    public User queryUserById(String id);

    //查詢所有使用者資訊
    @Select("select * from tb_user")
    public List<User> queryUserAll();

    //新增使用者
    @Insert("insert into tb_user(id,user_name, password,name1, age, sex, birthday, created, updated) values(#{id},#{user_name}, #{password},#{name1}, #{age}, #{sex}, #{birthday}, #{created}, #{updated})")
    public void insertUser(User user);

    //更新使用者資訊
    @Update("update tb_user set user_name = #{user_name},updated = #{updated} where id = #{id}")
    public void updateUser(User user);

    @Delete("delete from tb_user where id = #{id}")
    //根據id删除使用者資訊
    public void deleteUser(String id);
}

service層：

UserService:

package com.springcloud.produce.service;

import com.springcloud.produce.entity.User;

import java.util.List;

public interface UserService {
    List<User> getLists();

    public User queryUserById(String id);

    public void addUser(User user);

    public void deleteUser(String id);

    public void updateUser(User user);

}

UserMapperImpl:

package com.springcloud.produce.service.impl;

import com.springcloud.produce.entity.User;
import com.springcloud.produce.mapper.UserMapper;
import com.springcloud.produce.service.UserService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

@Service
public class UserMapperImpl implements UserService{

    @Resource
    private UserMapper userMapper;
    
    @Override
    public List<User> getLists() {
        List<User> users = this.userMapper.queryUserAll();
        for(User user : users){
            System.out.println(user);
        }
        return users;
    }

    @Override
    public User queryUserById(String id) {
        return this.userMapper.queryUserById(id);
    }

    @Override
    public void addUser(User user) {
        this.userMapper.insertUser(user);
    }

    @Override
    public void deleteUser(String id) {
        this.userMapper.deleteUser(id);
    }

    @Override
    public void updateUser(User user) {
        this.userMapper.updateUser(user);
    }
}

controller層：

package com.springcloud.produce.controller;

import com.springcloud.produce.entity.User;
import com.springcloud.produce.service.UserService;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@RestController
public class userController {

    @Autowired
    private UserService userService;

    @Resource
    private DataSource dataSource;

    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("YYYY-MM-dd HH:mm:ss");
    Date date = new Date();

	//這個很重要！！！建構sqlSessionFactory
    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception{
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        return sqlSessionFactoryBean.getObject();
    }

    @RequestMapping("/selectUser")
    public User selectUser(){
        User user = this.userService.selectUser("1") ;
        return user;
    }

    @GetMapping("/{id}")
    public User queryUserById(@PathVariable String id){
        User user =this.userService.queryUserById(id);
        return user;
    }

    @RequestMapping("/")
    public String index(){
        return "hello";
    }

    @GetMapping("/list")
    public List<User> lists(){
        List<User> users = this.userService.getLists();
        return users;
    }

    @GetMapping("/addUser/{id}")
    public String addUser(@PathVariable String id) throws ParseException {
        User user = new User();
        user.setId(id);
        user.setAge(18);
        user.setSex(1);
        user.setBirthday(date);
        user.setCreated(simpleDateFormat.format(date));
        user.setUpdated(simpleDateFormat.format(date));
        user.setName1(id);
        user.setUser_name("wwww"+id);
        user.setPassword("123456");
        this.userService.addUser(user);
        return "success";
    }

    @GetMapping("/deteleUser/{id}")
    public String deleteUser(@PathVariable String id){
        this.userService.deleteUser(id);
        return "success";
    }

    @GetMapping("/updateUser/{id}")
    public String updateUser(@PathVariable String id){
        User user = this.userService.queryUserById(id);
        user.setUser_name("update");
        user.setUpdated(simpleDateFormat.format(date));
        this.userService.updateUser(user);
        return "success";
    }
}

最後運作項目測試一下，我就不貼圖了，測試過很多次了，如果有什麼問題，歡迎留言~~

在消費子產品中調用增删改查功能之後再寫部落格辣~~

源碼

下一篇打算總結一下mybatis的具體用法，總感覺自己還是沒有弄的很清楚!!!

spring cloud mybaits 微服務增删改查(二)

繼續閱讀

mybatis中#{}和${}和差別，以及防止sql注入。

從零開始搭建微服務：跨域處理

Spring Boot：靜态資源

IO模型淺析-阻塞、非阻塞、IO複用、信号驅動、異步IO、同步IO

谷粒商城：認證服務準備+60s短信驗證

cron 表達式解讀

myeclipse 搭建mybatis架構時報錯（1）Communications link failure 通訊連結故障

jeecg-boot的shiro認證過程2. jwtFilter

SpringSecurity整合短信驗證碼認證

基于Springboot+VUE+Mysql的大學生校園疫情防控管理系統

springboot使用 swagger-ui 2.10.5 有關版本更新帶來的問題

微服務-性能壓測\緩存redis和分布式鎖redisson和SpringCache

MyBatis學習筆記Mybatis學習筆記（maven、Idea版）

spring boot @SpringBootApplication @EnableAutoConfiguration 的工作原理

用mybatis的generator插件在項目中自動生成dao及entity

從mybatis到mybatis plus(mp)