001 springboot mybatis h2 数据库测试

package com.pig.springbooth2;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringbootH2Application {

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

}

# DataSource Config
spring:
  datasource:
    driver-class-name: org.h2.Driver
    schema: classpath:db/schema.sql #，每次启动程序，程序都会运行schema.sql文件，对数据库的数据操作
    data: classpath:db/data.sql #，每次启动程序，程序都会运行data.sql文件，对数据库的数据操作
    url: jdbc:h2:mem:test #配置h2数据库的连接地址
    username: sa
    password: 123456
  h2:
    console:
      enabled: true #开启web console功能

package com.pig.springbooth2.repository;

import com.pig.springbooth2.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@Mapper
public interface UserDao {
	@Select("select * from user where id= #{id}")
	User findById(Integer id);

	@Select("select * from user")
	List<User> findAll();
}

package com.pig.springbooth2.entity;


import lombok.Data;

@Data
public class User {
	private Long id;
	private String name;
	private Integer age;
	private String email;
}

package com.pig.springbooth2.controller;

import com.pig.springbooth2.entity.User;
import com.pig.springbooth2.repository.UserDao;
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.ResponseBody;

import java.util.List;

@Controller
public class HelloContraller {

	@Autowired
	private UserDao userDao;

	@ResponseBody
	@RequestMapping("/hello")
	public List<User> hello() {
		return userDao.findAll();
	}
}

DELETE FROM user;

INSERT INTO user (id, name, age, email) VALUES
  (1, ‘Jone‘, 18, ‘test1@baomidou.com‘),
  (2, ‘Jack‘, 20, ‘test2@baomidou.com‘),
  (3, ‘Tom‘, 28, ‘test3@baomidou.com‘),
  (4, ‘Sandy‘, 21, ‘test4@baomidou.com‘),
  (5, ‘Billie‘, 24, ‘test5@baomidou.com‘);

DROP TABLE IF EXISTS user;

CREATE TABLE user
(
  id BIGINT(20) NOT NULL COMMENT ‘主键ID‘,
  name VARCHAR(30) NULL DEFAULT NULL COMMENT ‘姓名‘,
  age INT(11) NULL DEFAULT NULL COMMENT ‘年龄‘,
  email VARCHAR(50) NULL DEFAULT NULL COMMENT ‘邮箱‘,
  PRIMARY KEY (id)
);

<?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.2.2.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.pig</groupId>
	<artifactId>springboot-h2</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>springboot-h2</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>
		</dependency>

		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
			<exclusions>
				<exclusion>
					<groupId>org.junit.vintage</groupId>
					<artifactId>junit-vintage-engine</artifactId>
				</exclusion>
			</exclusions>
		</dependency>
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.1.0</version>
		</dependency>
	</dependencies>

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

</project>

# 显示数据库日志
logging:
  level:
    com.pig.springbooth2.repository: debug

===

2019-12-12 23:39:23.755 DEBUG 2096 --- [nio-8080-exec-1] c.p.s.repository.UserDao.findAll : ==> Preparing: select * from user 
2019-12-12 23:39:23.777 DEBUG 2096 --- [nio-8080-exec-1] c.p.s.repository.UserDao.findAll : ==> Parameters: 
2019-12-12 23:39:23.804 DEBUG 2096 --- [nio-8080-exec-1] c.p.s.repository.UserDao.findAll : <== Total: 5