spring boot项目从零开始-(7)集成Sql Server

简述

前提

已安装sql server2016或2017，用户名密码为：sa/123456

目录

步骤

pom.xml

<!-- sql server -->
<dependency>
     <groupId>com.microsoft.sqlserver</groupId>
     <artifactId>mssql-jdbc</artifactId>
     <version>7.2.2.jre8</version>
 </dependency>
 <dependency>
     <groupId>org.mybatis.spring.boot</groupId>
     <artifactId>mybatis-spring-boot-starter</artifactId>
     <version>2.0.1</version>
 </dependency>
 <dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>druid-spring-boot-starter</artifactId>
     <version>1.1.16</version>
 </dependency>

application.yml

spring:
  datasource:
    minIdle: 1
    maxActive: 5
    initialSize: 1
    filters: stat,wall
    poolPreparedStatements: false
    type: com.alibaba.druid.pool.DruidDataSource
    maxPoolPreparedStatementPerConnectionSize: 20
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    url: jdbc:sqlserver://localhost:1433;databaseName=manager
    username: sa
    password: 123456

mapper及model

package com.ydfind.start.dao;

import com.ydfind.start.model.ConfigItem;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface ConfigMapper {

    @Select("select id, name, item_key itemKey, item_value itemValue" +
            " from op_config where name=#{name}")
    List<ConfigItem> getConfigByName(String name);
 }

package com.ydfind.start.model;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

@Data
public class ConfigItem {

    @ApiModelProperty(value = "id")
    private Long id;

    @ApiModelProperty(value = "配置名称")
    private String name;

    @ApiModelProperty(value = "项key")
    private String itemKey;

    @ApiModelProperty(value = "项值")
    private String itemValue;
}

入口

添加注解
@ComponentScan({“com.ydfind.start”, “com.ydfind.common”})
//@MapperScan(“com.cmbchina.www.rec.dao”)

建表

CREATE TABLE [manager].dbo.op_config (
	id bigint NOT NULL,
	name varchar(128) COLLATE Chinese_PRC_CI_AS NOT NULL,
	item_value varchar(128) COLLATE Chinese_PRC_CI_AS NOT NULL,
	item_key varchar(64) COLLATE Chinese_PRC_CI_AS NULL,
	description varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	CONSTRAINT PK_op_config PRIMARY KEY (id)
)

测试

package com.ydfind.start.mapper;

import com.ydfind.start.BaseTest;
import com.ydfind.start.dao.ConfigMapper;
import com.ydfind.start.model.ConfigItem;
import org.junit.FixMethodOrder;
import org.junit.Test;
import org.junit.runners.MethodSorters;
import org.springframework.beans.factory.annotation.Autowired;

import java.util.List;

@FixMethodOrder(MethodSorters.DEFAULT)
public class ConfigMapperTest extends BaseTest {

    @Autowired
    private ConfigMapper configMapper;

    @Test
    public void testGetConfigs() {
        List<ConfigItem> items = configMapper.getConfigByName("AUTO_PUSH");
        System.out.println(items);
    }
}