springboot数据库配置和连接

依赖

<!-- mybatis-plus https://github.com/baomidou/mybatis-plus -->
<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>mybatis-plus-boot-starter</artifactId>
  <version>3.2.0</version>
</dependency>
<!-- 数据库驱动依赖 -->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- 数据库连接池 https://github.com/alibaba/druid -->
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid</artifactId>
  <version>1.1.9</version>
</dependency>
<!-- Lombok(实体类注解) -->
<dependency>
  <groupId>org.projectlombok</groupId>
  <artifactId>lombok</artifactId>
  <optional>true</optional>
</dependency>

连接配置

spring:
  # 数据库配置
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://127.0.0.1:3307/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT
    username: root
    password: 123456
mybatis-plus:
  configuration:
    auto-mapping-behavior: full

入口文件配置 mapper 包位置 @MapperScan(”com.example.mapper”)

package com.example;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.*;
import org.springframework.boot.autoconfigure.*;

@MapperScan("com.example.mapper")
@SpringBootApplication
public class Home {
  public static void main(String[] args) throws Exception {
    SpringApplication.run(Home.class, args);
  }

}

实体类文件 src\main\java\com\example\entity\UserEn.java

package com.example.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;

import lombok.Data;
/**
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pass_word` varchar(50) DEFAULT NULL,
  KEY `new_index_1` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
 */
@Data()
@TableName(value = "user")
public class UserEn {
    // 指定主键使用数据库ID自增策略
    @TableId(type = IdType.AUTO)
    private Integer id;
    public String passWord;
}

mapper 文件 src\main\java\com\example\mapper\UserMapper.java

package com.example.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.entity.UserEn;

public interface UserMapper extends BaseMapper<UserEn> {
}

使用例子 Mapper 注解 @Autowired

package com.example.controller;

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 com.example.entity.UserEn;
import com.example.mapper.UserMapper;

@Controller
public class HelloController {

  @Autowired
  UserMapper userMapper;

  @RequestMapping(value = "/", method = RequestMethod.GET)
  // @ResponseBody
  public String index() {
    UserEn user1 = new UserEn();
    int num = userMapper.selectCount(null);
    System.out.println(num);
    return "index";
  }

  @RequestMapping("/name")
  // @ResponseBody
  public String name() {
    return "name";
  }

}

目录结构

• 使用jooq

spring-boot-starter-jooq

# 配置连接
# src/main/resources/application.yml
spring:
  datasource:
    url: jdbc:mysql://www.leng2011.icu:3306/exadmin
    username: root
    password: 112233445566
# 打印jooq日志
logging:
  level:
    org.jooq.tools.LoggerListener: DEBUG


# 依赖
<?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.example</groupId>
    <artifactId>myproject</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <!-- spring boot -->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.0.RELEASE</version>
    </parent>
    <!-- 依赖 -->
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jooq</artifactId>
        </dependency>

    </dependencies>


    <build>
        <plugins>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>

            <!-- 代码生成器插件 mvn jooq-codegen:generate -->
            <plugin>
                <groupId>org.jooq</groupId>
                <artifactId>jooq-codegen-maven</artifactId>
                <version>${jooq.version}</version>
                <configuration>
                    <jdbc>
                        <driver>com.mysql.cj.jdbc.Driver</driver>
                        <url>jdbc:mysql://www.leng2011.icu:3306/exadmin</url>
                        <user>root</user>
                        <password>112233445566</password>
                    </jdbc>
                    <generator>
                        <database>
                            <!-- 包含的数据库和表 -->
                            <includes>exadmin.*</includes>
                        </database>
                        <target>
                            <!-- 指定包名和位置 -->
                            <packageName>com.jooqgen</packageName>
                            <directory>./src/main/java</directory>
                        </target>
                        <generate>
                            <!-- 生成pojo文件,修改自行继承 -->
                            <pojos>true</pojos>
                            <!-- 生成dao文件 -->
                            <daos>true</daos>
                        </generate>
                    </generator>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>



# 使用
package com.example.controller;

import java.math.BigDecimal;
import java.util.ArrayList;

import javax.annotation.Resource;

import org.jooq.Condition;
import org.jooq.DSLContext;
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 com.jooqgen.exadmin.Tables;
import com.jooqgen.exadmin.tables.AdminConfigs;
import com.jooqgen.exadmin.tables.records.AdminConfigsRecord;

import org.jooq.Record;
import org.jooq.Record1;
import org.jooq.Record2;
import org.jooq.Record3;
import org.jooq.Record4;
import org.jooq.Result;
import org.jooq.impl.DSL;

@Controller
public class HelloController {

    @Autowired
    DSLContext dslContext;

    @RequestMapping("/")
    @ResponseBody
    String index() {
        // Record record = dslContext.selectFrom(AdminConfigs.ADMIN_CONFIGS)
        //         .where("id = 1")
        //         .fetchOne();
        // AdminConfigsRecord adminConfigsRecord = record.into(AdminConfigsRecord.class);
        Condition condition = DSL.field("id").eq("2")
                .and(DSL.field("name").eq(1))
                .and("id>1")
                .and(DSL.field("name").eq(2))
                .and("(id>10 AND id<1000)");

        Result<Record4<Object, Object, Object, String>> AdminConfigsRecord = dslContext
                // .select(Tables.ADMIN_CONFIGS.ID.sum().as("x"), Tables.ADMIN_CONFIGS.ID, Tables.ADMIN_CONFIGS.NAME, "GROUP_CONCAT(name)")
                .select(DSL.field("GROUP_CONCAT(name)"),
                        DSL.field("id"),
                        DSL.field("SUM(id)").as("sumID"),
                        DSL.field(Tables.ADMIN_CONFIGS.NAME))
                .from(Tables.ADMIN_CONFIGS)
                .where(condition)
                .groupBy(Tables.ADMIN_CONFIGS.NAME)
                .orderBy(Tables.ADMIN_CONFIGS.ID.asc())
                .fetch();
        return AdminConfigsRecord.toString();
    }

}

• RESTful风格响应

        <!-- 一套RESTful风格API接口响应参数规范化的解决方案 -->
        <dependency>
            <groupId>com.fengwenyi</groupId>
            <artifactId>api-result</artifactId>
            <version>2.7.4</version>
        </dependency>

// 返回pojo/vo
return ResultTemplate.success(adminConfigsRecord);