springboot integrated mysql entry demo

一、mysql run environment

use docker-compose to build environment，file content：

docker-compose.yml

version: '3'
services:
  mysql:
    image: registry.cn-hangzhou.aliyuncs.com/zhengqing/mysql:5.7  
    container_name: mysql_3306                                    
    restart: unless-stopped                                       
    volumes:                                                      
      - "./mysql/my.cnf:/etc/mysql/my.cnf"
      - "./mysql/init-file.sql:/etc/mysql/init-file.sql"
      - "./mysql/data:/var/lib/mysql"
#      - "./mysql/conf.d:/etc/mysql/conf.d"
      - "./mysql/log/mysql/error.log:/var/log/mysql/error.log"
      - "./mysql/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d" 
    environment:                       
      TZ: Asia/Shanghai
      LANG: en_US.UTF-8
      MYSQL_ROOT_PASSWORD: root        
      MYSQL_DATABASE: demo             
    ports:                             
      - "3306:3306"

start mysql docker

docker-compose -f docker-compose.yml -p mysql5.7 up -d

create table user and insert some test data

CREATE TABLE `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) DEFAULT NULL,
 `age` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCRE
INSERT INTO demo.`user`(id, name, age)VALUES(1, 'jack', 18);
INSERT INTO demo.`user`(id, name, age)VALUES(2, 'alyssa', 19);

二、build the code project

2.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">
    <parent>
        <artifactId>springboot-demo</artifactId>
        <groupId>com.et</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>
<artifactId>mysql</artifactId>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-autoconfigure</artifactId>
        </dependency>
        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.29</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
</project>

2.2 entity

package com.et.mysql.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName("user")
public class UserPO {
    @TableId(value = "id",type = IdType.AUTO)
    private int id;
    @TableField("name")
    private String name;
    @TableField("age")
    private int age;
}

2.3 mapper

package com.et.mysql.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.et.mysql.entity.UserPO;
import org.springframework.stereotype.Repository;
@Repository
public interface UserMapper extends BaseMapper<UserPO> {
}

2.4 application.yaml

server:
  port: 8088
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.zaxxer.hikari.HikariDataSource
    url: jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=utf-8&useSSL=false
    username: root
    password: root

2.5 check the service is available？

package com.et.mysql;
import com.et.mysql.entity.UserPO;
import com.et.mysql.mapper.UserMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
@RunWith(SpringRunner.class)
@SpringBootTest(classes = DemoApplication.class)
public class MysqlTests {
    @Autowired
    private UserMapper userMapper;

    @Test
    public void testmysql(){
        for (UserPO userPO : userMapper.selectList(null)) {
            System.out.println(userPO.toString());
        }
    }
}

the result as same as expected

2024-01-26 13:29:22.950 INFO 30100 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2024-01-26 13:29:23.160 INFO 30100 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
UserPO(id=1, name=jack, age=18)
UserPO(id=2, name=alyssa, age=19)

三、code reponsitory

• https://github.com/Harries/springboot-demo/

四、reference

• https://spring.io/projects/spring-data-jdbc/#overview