Spring boot集成Mybatis和SQL Server

开发环境

系统:MacOS Mojave 版本10.14.6

IDE:IDEA2020.1.2

Java:Java1.8

数据库:SQL Server 2008 R2

概 述

Spring Boot 工程集成 MyBatis 来实现 MySQL 访问的示例我们见过很多,而最近用到了微软的 SQL Server 数据库,于是本文给出一个完整的 Spring Boot + MyBatis + SQL Server 的工程示例。

目录

1.新建 Spring Boot 项目

2.mybatis-generator 的安装和配置

3.自动生成DAO层文件

4.跑通接口

初始数据

创建数据库

打开SQL Server Management Studio,连接到服务器

直接新建查询,输入以下代码(sql代码.txt,见下载页),执行

CREATE DATABASE student_management
GO
USE student_management
CREATE TABLE [dbo].[student](
	[SNO] [char](6) NOT NULL,
	[SN] [varchar](10) NOT NULL,
	[SD] [char](16) NULL,
	[SB] [datetime] NULL,
	[SEX] [char](2) NULL,
PRIMARY KEY CLUSTERED 
(
	[SNO] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
INSERT [dbo].[student] ([SNO], [SN], [SD], [SB], [SEX]) VALUES (N's01   ', N'王玲', N'计算机          ', CAST(0x0000811C00000000 AS DateTime), N'女')
INSERT [dbo].[student] ([SNO], [SN], [SD], [SB], [SEX]) VALUES (N's02   ', N'李渊', N'计算机          ', CAST(0x000080B900000000 AS DateTime), N'男')
INSERT [dbo].[student] ([SNO], [SN], [SD], [SB], [SEX]) VALUES (N's03   ', N'罗军', N'计算机          ', CAST(0x000082B400000000 AS DateTime), N'男')
INSERT [dbo].[student] ([SNO], [SN], [SD], [SB], [SEX]) VALUES (N's04   ', N'赵泽', N'计算机          ', CAST(0x000085AE00000000 AS DateTime), N'女')
INSERT [dbo].[student] ([SNO], [SN], [SD], [SB], [SEX]) VALUES (N's05   ', N'许若', N'指挥自动化      ', CAST(0x0000811900000000 AS DateTime), N'男')
INSERT [dbo].[student] ([SNO], [SN], [SD], [SB], [SEX]) VALUES (N's06   ', N'王仙华', N'指挥自动化      ', CAST(0x0000826000000000 AS DateTime), N'男')
INSERT [dbo].[student] ([SNO], [SN], [SD], [SB], [SEX]) VALUES (N's07   ', N'朱祝', N'指挥自动化      ', CAST(0x000086DB00000000 AS DateTime), N'女')
INSERT [dbo].[student] ([SNO], [SN], [SD], [SB], [SEX]) VALUES (N's08   ', N'王明', N'数学            ', CAST(0x000082E800000000 AS DateTime), N'男')
INSERT [dbo].[student] ([SNO], [SN], [SD], [SB], [SEX]) VALUES (N's09   ', N'王学之', N'物理            ', CAST(0x0000834200000000 AS DateTime), N'男')
INSERT [dbo].[student] ([SNO], [SN], [SD], [SB], [SEX]) VALUES (N's10   ', N'吴谦', N'指挥自动化      ', CAST(0x0000822800000000 AS DateTime), N'女')
INSERT [dbo].[student] ([SNO], [SN], [SD], [SB], [SEX]) VALUES (N's11   ', N'崔雪', N'数学            ', CAST(0x0000811D00000000 AS DateTime), N'女')
INSERT [dbo].[student] ([SNO], [SN], [SD], [SB], [SEX]) VALUES (N's12   ', N'李想', N'英语            ', CAST(0x0000841B00000000 AS DateTime), N'男')
INSERT [dbo].[student] ([SNO], [SN], [SD], [SB], [SEX]) VALUES (N's13   ', N'季然', N'数学            ', CAST(0x0000845300000000 AS DateTime), N'女')
INSERT [dbo].[student] ([SNO], [SN], [SD], [SB], [SEX]) VALUES (N's14   ', N'顾梦莎', N'英语            ', CAST(0x000080D400000000 AS DateTime), N'女')
INSERT [dbo].[student] ([SNO], [SN], [SD], [SB], [SEX]) VALUES (N's15   ', N'费汉蒙', N'计算机          ', CAST(0x00007FE100000000 AS DateTime), N'男')
INSERT [dbo].[student] ([SNO], [SN], [SD], [SB], [SEX]) VALUES (N's16   ', N'华庭', N'数学            ', CAST(0x0000817900000000 AS DateTime), N'男')
INSERT [dbo].[student] ([SNO], [SN], [SD], [SB], [SEX]) VALUES (N's17   ', N'亨利', N'英语            ', CAST(0x0000832E00000000 AS DateTime), N'男')
INSERT [dbo].[student] ([SNO], [SN], [SD], [SB], [SEX]) VALUES (N's18   ', N'李爱民', N'英语            ', CAST(0x00007DDA00000000 AS DateTime), N'男')

一、新建 Spring Boot 项目

1.1、打开 IDEA ,New Project -> Spring Initialzr -> Next

1.2、填写Group、Artifact,修改 Java Version -> 8 -> Next

1.3、选择依赖 Web -> Spring Web

1.4、给项目命名:Project name、Project location,点击Finish

1.5、修改Spring Boot版本,打开pom.xml ->修改版本 2.2.1.RELEASE -> 右键pom.xml -> 点击 Maven => Reload project,重新解析依赖,把正确的依赖给引入,修改完毕!注意:第一次下载依赖时间会比较久…耐心等待。

1.6、测试该工程能否跑起来打开目录 src->main->java->com.lmtai.demo->DemoApplication,运行main方法,日志出现类似这段说明启动成功:Started DemoApplication in 1.578 seconds

二、mybatis-generator 的安装和配置

整合mybatis-generator,给我们的项目添加一个插件,用来自动生成数据库文件

2.1.1、打开pom.xml,添加依赖mybatis、mssql-jdbc:

<!-- mybatis 依赖 -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.1</version>
</dependency>
<!-- SQL Server的jdbc用来连接SQL Server数据库 -->
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
</dependency>

2.1.2、添加插件mybatis-generator

<!-- 自动生成文件dao、mapper、entity -->
<plugin>
    <groupId>org.mybatis.generator</groupId>
    <artifactId>mybatis-generator-maven-plugin</artifactId>
    <version>1.3.7</version>
    <configuration>
        <verbose>true</verbose>
        <!-- overwrite 是否覆盖原有的文件 -->
        <overwrite>true</overwrite>
    </configuration>
</plugin>

完整的pom.xml(见下载页)

2.2、在resources文件夹,新建这个插件的配置文件generatorConfig.xml(见下载页),添加如下内容(根据自己项目修改)文件第4行报红是正常的,不需要额外进行处理:

主要修改:

驱动位置第9行,改成自己的mssql-jdbc驱动位置;第20行URL及数据库名、21行用户名、22行密码。

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
  PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
  "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
  <!-- 配置文件,放在resource目录下即可 -->
  <!--数据库驱动个人配置-->
  <classPathEntry
    location="/Users/bowyn/.m2/repository/com/microsoft/sqlserver/mssql-jdbc/8.4.0.jre8/mssql-jdbc-8.4.0.jre8.jar"/>
  <context id="DB2Tables" targetRuntime="MyBatis3">
    <property name="autoDelimitKeywords" value="true"/>

    <!-- optional,旨在创建class时,对注释进行控制 -->
    <commentGenerator>
      <property name="suppressDate" value="true"/>
      <property name="suppressAllComments" value="true"/>
    </commentGenerator>
    <!--数据库链接地址、账号、密码-->
    <jdbcConnection driverClass="com.microsoft.sqlserver.jdbc.SQLServerDriver"
      connectionURL="jdbc:sqlserver://localhost:1433;databaseName=student_management"
      userId="sa"
      password="1">
      <property name="nullCatalogMeansCurrent" value="true"/>
    </jdbcConnection>
    <!-- 非必需,类型处理器,在数据库类型和java类型之间的转换控制-->
    <javaTypeResolver>
      <property name="forceBigDecimals" value="false"/>
    </javaTypeResolver>
    <!--生成Model类存放位置-->
    <javaModelGenerator targetPackage="com.lmtai.demo.model.entity"
      targetProject="src/main/java">
      <!-- 是否允许子包,即targetPackage.schemaName.tableName -->
      <property name="enableSubPackages" value="true"/>
      <!-- 是否对类CHAR类型的列的数据进行trim操作 -->
      <property name="trimStrings" value="true"/>
      <!-- 建立的Model对象是否 不可改变  即生成的Model对象不会有 setter方法,只有构造方法 -->
      <property name="immutable" value="false"/>
    </javaModelGenerator>
    <!--生成mapper映射文件存放位置-->
    <sqlMapGenerator targetPackage="mappers" targetProject="src/main/resources">
      <property name="enableSubPackages" value="true"/>
    </sqlMapGenerator>
    <!--生成Dao类(Mapper包)存放位置-->
    <javaClientGenerator type="XMLMAPPER" targetPackage="com.lmtai.demo.model.dao"
      targetProject="src/main/java">
      <property name="enableSubPackages" value="true"/>
    </javaClientGenerator>
    <!--生成对应表及类名-->
    <!-- 需要生成那些数据库(更改tableName数据库名字和domainObjectName生成的类名 -->
    <table tableName="student" domainObjectName="Student" enableCountByExample="false"
           enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false"
           selectByExampleQueryId="false">
    </table>
    <!-- 多个表直接使用多个table   -->
<!--    <table tableName="test" domainObjectName="Test" enableCountByExample="false"-->
<!--           enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false"-->
<!--           selectByExampleQueryId="false">-->
<!--    </table>-->

  </context>
</generatorConfiguration>

三、自动生成DAO层

3.1、配置完成点击IntelliJ IDEA右侧的Maven -> 项目名(demo) -> Plugins(插件) -> mybatis-generator(代码生成器)-> 双击运行 mybatis-generator:generate

3.2、配置成功的话,成功运行最后输出日志 如下图:

3.3、查看我们生成的项目结构,会发现自动生成的文件:实体类Student、映射类StudentMapper以及映射文件StudentMapper.xml。

3.4、至此配置完成,开始项目功能实现。

四、跑通接口

4.1、尝试运行下我们的程序,发现已经不能正常运行程序,

报错(1):

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.2.1.RELEASE)

2020-08-06 12:00:12.462  INFO 10404 --- [           main] com.lmtai.demo.DemoApplication           : Starting DemoApplication on BowyndeMacBook-Pro.local with PID 10404 (/Users/bowyn/Documents/Java/sqlserver/sourceCode/test-demo/target/classes started by bowyn in /Users/bowyn/Documents/Java/sqlserver/sourceCode/test-demo)
2020-08-06 12:00:12.464  INFO 10404 --- [           main] com.lmtai.demo.DemoApplication           : No active profile set, falling back to default profiles: default
2020-08-06 12:00:12.899  WARN 10404 --- [           main] o.m.s.mapper.ClassPathMapperScanner      : No MyBatis mapper was found in '[com.lmtai.demo]' package. Please check your configuration.
2020-08-06 12:00:13.164  INFO 10404 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
2020-08-06 12:00:13.170  INFO 10404 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2020-08-06 12:00:13.170  INFO 10404 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.27]
2020-08-06 12:00:13.225  INFO 10404 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2020-08-06 12:00:13.226  INFO 10404 --- [           main] o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 735 ms
2020-08-06 12:00:13.355  INFO 10404 --- [           main] o.s.s.concurrent.ThreadPoolTaskExecutor  : Initializing ExecutorService 'applicationTaskExecutor'
2020-08-06 12:00:13.454  WARN 10404 --- [           main] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceConfiguration$Hikari.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [com.zaxxer.hikari.HikariDataSource]: Factory method 'dataSource' threw exception; nested exception is org.springframework.boot.autoconfigure.jdbc.DataSourceProperties$DataSourceBeanCreationException: Failed to determine a suitable driver class
2020-08-06 12:00:13.454  INFO 10404 --- [           main] o.s.s.concurrent.ThreadPoolTaskExecutor  : Shutting down ExecutorService 'applicationTaskExecutor'
2020-08-06 12:00:13.456  INFO 10404 --- [           main] o.apache.catalina.core.StandardService   : Stopping service [Tomcat]
2020-08-06 12:00:13.525  INFO 10404 --- [           main] ConditionEvaluationReportLoggingListener : 

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2020-08-06 12:00:13.530 ERROR 10404 --- [           main] o.s.b.d.LoggingFailureAnalysisReporter   : 

***************************
APPLICATION FAILED TO START
***************************

Description:

Failed to configure a DataSource: 'url' attribute is not specified and no embedded datasource could be configured.

Reason: Failed to determine a suitable driver class


Action:

Consider the following:
	If you want an embedded database (H2, HSQL or Derby), please put it on the classpath.
	If you have database settings to be loaded from a particular profile you may need to activate it (no profiles are currently active).

提示我们的数据源没有经过正常的配置,因为我们引入了MyBatis之后,没有对数据库的一些信息进行配置,所以我们要在application.properties配置文件进行配置数据库信息。

# 数据库名字
spring.datasource.name=student_management_datasource
# URL IP:端口号;databaseName=数据库名
spring.datasource.url=jdbc:sqlserver://localhsot:1433;databaseName=student_management
# driver
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
# 数据库账户
spring.datasource.username=sa
# 数据密码
spring.datasource.password=1

配置完成,重新运行程序,发现程序已经正常启动,说明它已经用过我们的配置连接到数据库。

4.2、我们开始写Controller层和Service层我们让它以接口的形式,对外读取一条数据库的信息,可以把程序停下来。

编写Service层,新建service包,创建对应的Service及Service实现类(添加注解@Service):

1.Service接口:

package com.lmtai.demo.service;

import com.lmtai.demo.model.entity.Student;
import org.springframework.stereotype.Service;

/**
 * StudentService
 */
public interface StudentService {

    Student getStudent();
}

2.Service实现类:

package com.lmtai.demo.service.imlp;

import com.lmtai.demo.model.dao.StudentMapper;
import com.lmtai.demo.model.entity.Student;
import com.lmtai.demo.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;

/**
 * StudentService实现类
 */
@Service
public class StudentServiceImpl implements StudentService {
    @Autowired
    StudentMapper studentMapper;

    @Override
    public Student getStudent() {
        return studentMapper.selectByPrimaryKey(1);
    }
}

新建controller包,创建对应实体的Controller:

package com.lmtai.demo.controller;

import com.lmtai.demo.model.entity.Student;
import com.lmtai.demo.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ResponseBody;

/**
 * 学生管理控制器
 */
@Controller
public class StudentController {
    @Autowired
    StudentService studentService;

    /**
     * 返回对象的一些基本信息
     * @return
     * 由于采用的是JSON的通信方式,所以需要加上@ResponseBody,这样它会返回JSON格式内容
     */
    @GetMapping("/test")
    @ResponseBody
    public Student personalPage(){
        return studentService.getStudent();
    }
}

4.3、配置文件(application.properties)里去告诉MyBatis,Mapper文件在哪里,它应该从哪里去找:

# 扫描resources/mappers目录下的所有.xml结尾的Mapper文件
mybatis.mapper-locations=classpath:mappers/*.xml

4.4、我们尝试下启动程序:

报错(2):

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.2.1.RELEASE)

2020-08-06 13:30:54.127  INFO 10511 --- [           main] com.lmtai.demo.DemoApplication           : Starting DemoApplication on BowyndeMacBook-Pro.local with PID 10511 (/Users/bowyn/Documents/Java/sqlserver/sourceCode/test-demo/target/classes started by bowyn in /Users/bowyn/Documents/Java/sqlserver/sourceCode/test-demo)
2020-08-06 13:30:54.129  INFO 10511 --- [           main] com.lmtai.demo.DemoApplication           : No active profile set, falling back to default profiles: default
2020-08-06 13:30:54.802  WARN 10511 --- [           main] o.m.s.mapper.ClassPathMapperScanner      : No MyBatis mapper was found in '[com.lmtai.demo]' package. Please check your configuration.
2020-08-06 13:30:55.173  INFO 10511 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
2020-08-06 13:30:55.183  INFO 10511 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2020-08-06 13:30:55.183  INFO 10511 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.27]
2020-08-06 13:30:55.260  INFO 10511 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2020-08-06 13:30:55.260  INFO 10511 --- [           main] o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 1084 ms
2020-08-06 13:30:55.307  WARN 10511 --- [           main] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'studentController': Unsatisfied dependency expressed through field 'studentService'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'studentServiceImpl': Unsatisfied dependency expressed through field 'studentMapper'; nested exception is org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type 'com.lmtai.demo.model.dao.StudentMapper' available: expected at least 1 bean which qualifies as autowire candidate. Dependency annotations: {@org.springframework.beans.factory.annotation.Autowired(required=true)}
2020-08-06 13:30:55.310  INFO 10511 --- [           main] o.apache.catalina.core.StandardService   : Stopping service [Tomcat]
2020-08-06 13:30:55.392  INFO 10511 --- [           main] ConditionEvaluationReportLoggingListener : 

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2020-08-06 13:30:55.487 ERROR 10511 --- [           main] o.s.b.d.LoggingFailureAnalysisReporter   : 

***************************
APPLICATION FAILED TO START
***************************

Description:

Field studentMapper in com.lmtai.demo.service.imlp.StudentServiceImpl required a bean of type 'com.lmtai.demo.model.dao.StudentMapper' that could not be found.

The injection point has the following annotations:
	- @org.springframework.beans.factory.annotation.Autowired(required=true)


Action:

Consider defining a bean of type 'com.lmtai.demo.model.dao.StudentMapper' in your configuration.

提示:找不到StudentMapper这个对象,因为我们还没有添加注解告诉Spring,Mapper都被放在哪里,需要我们在主类(DemoApplication)进行配置(添加注解@MapperScan):

package com.lmtai.demo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan(basePackages = "com.lmtai.demo.model.dao")
public class DemoApplication {

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

}

配置完毕,重新运行程序,成功运行,到浏览器去看看我们的/test是否成功!

我这里已经成功跑通!至此打通了从上到下的链路。

彩蛋

我们发现Service实现类里的有一个问题,我们的studentMapper有红色波浪线(如下图),但是程序又可以运行。我们该如何去掉这个红色波浪线呢?

为什么要去掉红色波浪线?

1.看着难受。

2.它这种提示会给我们造成误判!它会让我们担心程序是不是写错了。

其实程序也没有错,因为对于IDEA它去寻找Mapper类的话,它是要求在类上有一些提示的,否则它也不知道哪个类,它就可以被引入,但是我们这个Mapper位置配置在主类(DemoApplication)上,它是给我们的MyBatis识别用的,相当于我们 【@MapperScan(basePackages = "com.lmtai.demo.model.dao") 告诉了MyBatis,却没有告诉IDEA,我们可以在Mapper(com.lmtai.demo.model.dao下所有的Mapper)上添加注解@Repository,这样IDEA就会认为这是一个资源,就不会再报错了。

package com.lmtai.demo.model.dao;

import com.lmtai.demo.model.entity.Student;
import org.springframework.stereotype.Repository;

@Repository
public interface StudentMapper {
    int deleteByPrimaryKey(Integer sno);

    int insert(Student record);

    int insertSelective(Student record);

    Student selectByPrimaryKey(Integer sno);

    int updateByPrimaryKeySelective(Student record);

    int updateByPrimaryKey(Student record);
}

加完后,再回到刚上标红线的位置看看:

红线已经消失了。

除了这个方法之外,也可以通过设置IDEA配置的方式,去抑制这个红色波浪线提示,但是这个方法不推荐,因为通过抑制了红色波浪线的提示,可能有一些提示看不到,那也就没办法修复了。

加注解是解决问题最快的方法。

 

下载权限

查看
  • ¥{{right.value}}
    {{right.value}}
    免费下载
    评论后下载
    登录后下载

  • {{attr.name}}:
您当前的等级为
您有每天免费下载所有资源次的特权,今日剩余 已取得下载权限
Java

什么是工厂模式?

2020-6-19 1:48:29

Java

Spring Boot配置log4j2日志

2020-8-6 14:52:16

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索