SpringBoot 2-连接数据库、配置logback打印sql日志等

前言：上节说到新建项目。本节连接数据库  logback  通用mapper配置 实现操作数据库

需要注意的已添加备注  。以下是贴的代码。ps：代码手写一遍加强记忆吧~

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.yxl.project</groupId>
	<artifactId>MyProject</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>MyProject</name>
	<description>Demo project for Spring Boot</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.0.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<!--引入web -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<!--引入web -->
		<!-- 集成Mybatis -->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.1</version>
		</dependency>
		<!-- 集成Mybatis -->
		<!-- mysql 驱动 -->
		<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!-- mysql 驱动 -->
		<!-- jpa 注解 https://mvnrepository.com/artifact/javax.persistence/javax.persistence-api -->
		<dependency>
			<groupId>javax.persistence</groupId>
			<artifactId>javax.persistence-api</artifactId>
			<version>2.2</version>
		</dependency>
		<!-- jpa 注解 https://mvnrepository.com/artifact/javax.persistence/javax.persistence-api -->
		<!--注解DATA 日志 log  -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.0</version>
        </dependency>
        <!--注解DATA 日志 log  -->
        
         <!-- https://mvnrepository.com/artifact/tk.mybatis/mapper -->
        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper</artifactId>
            <version>4.0.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/tk.mybatis/mapper -->
	</dependencies>

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


</project>

配置文件：

# 服务名 update
spring.application.name= MyFirstProject

#设置端口 编码格式
server.session-timeout=600
server.port=8080
spring.messages.encoding=UTF-8
server.tomcat.uri-encoding=UTF-8
spring.http.encoding.charset=utf-8
spring.http.encoding.enabled=true
spring.http.encoding.force=true
#连接数据库配置 update
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/myProject?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.initial-size=1
spring.datasource.druid.min-idle=1
spring.datasource.druid.max-active=20
spring.datasource.druid.test-on-borrow=true
spring.datasource.druid.stat-view-servlet.allow=true
#数据库连接池 hikariCP
spring.datasource.hikari.idle-timeout=60000
spring.datasource.hikari.connection-timeout=60000
# resultType 扫描bojo  update
mybatis.type-aliases-package=com.example.yxl.model
mybatis.mapper-locations=classpath:mapper/*.xml

#打印sql日志 配置 update
debug=false
logging.level.root=info
logging.level.tk.mybatis.springboot.mapper=info
logging.level.com.example.yxl.mapper=debug
logging.config= classpath:logback.xml

# 通用mapper配置  update
mapper.mappers=com.example.yxl.utils.MyMapper
mapper.not-empty=false
mapper.identity=MYSQL

controller ：

package com.example.yxl.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.example.yxl.model.Customer;
import com.example.yxl.service.CustomerService;

import lombok.extern.slf4j.Slf4j;

@RestController
@RequestMapping("/MyFirst")
@Slf4j
public class MyFirstController {

	@Autowired
	private CustomerService customerService;
	
	
	@RequestMapping("/index.jsp")
	public String say() {
		return " say ";
	}
	
	@RequestMapping("/getAllCus.jsp")
	public List<Customer> getAll() {
		List<Customer> list = customerService.getAll();
		log.info(list.toString());
		return list ;
	}
	
}

service：

package com.example.yxl.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.example.yxl.mapper.CustomerMapper;
import com.example.yxl.model.Customer;

@Service
public class CustomerService {

	@Autowired
	private CustomerMapper CustomerMapper; 
	
	public List<Customer> getAll(){
                //selectAll 用的是tk自带的方法
		List<Customer> list = CustomerMapper.selectAll();
		return list ;
	}
}

mapper：

package com.example.yxl.mapper;

import java.util.List;

import com.example.yxl.model.Customer;
import com.example.yxl.utils.MyMapper;

public interface CustomerMapper extends MyMapper<Customer> {

	List<Customer> getAll();
}

bean：

package com.example.yxl.model;

import javax.persistence.Column;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
/**
 * 
 * Copyright (c) 2018, 井立通
 * All rights reserved.
 * 文件名称: Customer.java
 * 作        者: yxl 2018年11月20日
 * 创建时间: 2018年11月20日
 * 功能说明:用户表
 */
@Table(name = "customer")
public class Customer {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;
	
	private String zid;
	
	private String name;
	
	private String account;
	
	@Column(name = "create_time")
	private String createTime;
	
	@Column(name = "update_time")
	private String updateTime;

	public Integer getId() {
		return id;
	}

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

	public String getZid() {
		return zid;
	}

	public void setZid(String zid) {
		this.zid = zid;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getAccount() {
		return account;
	}

	public void setAccount(String account) {
		this.account = account;
	}

	public String getCreateTime() {
		return createTime;
	}

	public void setCreateTime(String createTime) {
		this.createTime = createTime;
	}

	public String getUpdateTime() {
		return updateTime;
	}

	public void setUpdateTime(String updateTime) {
		this.updateTime = updateTime;
	}
	
	
	
}

 通用mapper tk：

package com.example.yxl.utils;
import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;

public interface MyMapper<T> extends Mapper<T>, MySqlMapper<T>{

}

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.example.yxl.mapper.CustomerMapper">
	<resultMap type="com.example.yxl.model.Customer" id="BaseResultMap">
		<id column="id" property="id" jdbcType="INTEGER" />
		<result column="zid" property="zid" jdbcType="VARCHAR" />
		<result column="name" property="name" jdbcType="VARCHAR" />
		<result column="acount" property="acount" jdbcType="VARCHAR" />
		<result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
		<result column="create_time" property="updateTime" jdbcType="TIMESTAMP" />
	</resultMap>
	
	<select id="getAll" resultMap = "BaseResultMap">
		select * from customer
	</select>

</mapper>

 logback：

<!-- Logback configuration. See http://logback.qos.ch/manual/index.html -->
<configuration scan="true" scanPeriod="10 seconds">
    <include resource="org/springframework/boot/logging/logback/base.xml"/>


    <property name="LOG_HOME" value="/data1/logs/wallet"/>
    <property name="APP_NAME" value="MyFirstProject"></property>


    <appender name="FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <encoder>
            <pattern>
                [ %-5level] [%date{yyyy-MM-dd HH:mm:ss}] %logger{96} [%line] - %msg%n
            </pattern>
            <charset>UTF-8</charset> <!-- 此处设置字符集 -->
        </encoder>
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <fileNamePattern>${LOG_HOME}/${APP_NAME}-%d{yyyy-MM-dd}.%i.log.gz</fileNamePattern>
            <timeBasedFileNamingAndTriggeringPolicy
                    class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
                <maxFileSize>64 MB</maxFileSize>
            </timeBasedFileNamingAndTriggeringPolicy>
        </rollingPolicy>
        <filter class="ch.qos.logback.classic.filter.ThresholdFilter">
            <level>DEBUG</level>
        </filter>
        <prudent>false</prudent>
    </appender>


  <logger name="com.example.yxl" level="info" additivity="false">
    <appender-ref ref="CONSOLE" />
    <appender-ref ref="FILE"/>
  </logger>
    <root level="info">
        <appender-ref ref="FILE"/>
        <appender-ref ref="CONSOLE"/>
    </root>

</configuration>