sts+Spring boot连接数据库SQLserver (mybatis注解方式)
SQLserver 表结构
CREATE TABLE [dbo].[user_test](
[user_id] [int] NOT NULL,
[user_name] [varchar](50) NOT NULL,
[sex] [tinyint] NOT NULL,
[created_time] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
项目目录结构
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>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.4.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.testProject1</groupId>
<artifactId>testProject1</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>testProject1</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-web</artifactId>
</dependency>
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>2.7</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
配置文件application.properties
#server.port=89
# mybatis \u914D\u7F6E
#mybatis.type-aliases-package=com.testProject1.springboot.entity
## mybatis.mapper-locations=classpath:Mapper/*.xml
#mybatis.configuration.map-underscore-to-camel-case=true
## -------------------------------------------------
## SqlServer \u914D\u7F6E
spring.datasource.url=jdbc:sqlserver://HFP-20170511NJC:1433;databasename=AirportData
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.username=sa
spring.datasource.password=modern
TestProject1Application.java
package com.testProject1.springboot;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
// 如果dao包是在默认包下可以不用下面这句
// @MapperScan(basePackages = "com.testProject1.springboot.Dao")
// //用于声明mapper类的位置
@MapperScan({ "com.testProject1.springboot.Dao" })
public class TestProject1Application {
public static void main(String[] args) {
SpringApplication.run(TestProject1Application.class, args);
}
}
usercontroller.java
package com.testProject1.springboot.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.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import com.testProject1.springboot.Service.userService;
import com.testProject1.springboot.entity.userentity;
//控制器类usercontroller(@RestController 用于声明一个Restful风格的控制器 @Autowired用于连接到userService，@RequstMapping用于声明请求映射方法)；
@RestController
public class usercontroller {
@Autowired
private userService userService;
@RequestMapping(value = "/find")
public Object showAll() {
return userService.showAll();
}
@RequestMapping(value = "/getUser", method = RequestMethod.GET)
@ResponseBody
public Object getUser(userentity user ) {
return userService.getUser(user);
}
}
userDao.java
package com.testProject1.springboot.Dao;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import com.testProject1.springboot.entity.userentity;
//Mapper类userDao（数据库操作，@Mapper将userDao声明为一个Mapper接口，@Results是结果映射列表，@Result中property是User类的属性名，colomn是数据库表的字段名，@Select, @Insert 分别代表了被标注的方法要执行的真实SQL）
@Mapper
public interface userDao
{
// @Results这段也可以不用写
// @Results({
//
// @Result(property = "userid", column = "user_id" ),
//
// @Result(property = "username", column = "user_name"),
//
// @Result(property = "sex", column = "sex"),
//
// @Result(property = "createdtime", column = "created_time")
//
// })
@Select("SELECT * From user_test")
//public User find(String name);
public List<userentity> findAll();
@Select("select * from user_test where user_name = #{user_name}")
public List<userentity> getUser(userentity user);
}
userentity.java
package com.testProject1.springboot.entity;
//实体类user（设置成员变量，与数据库中的字段对应，并且写好get\set方法）
public class userentity {
private Long user_id;
private String user_name;
private Boolean sex;
private String created_time;
public Long getUserId() {
return user_id;
}
public void setUserId(Long user_id) {
this.user_id = user_id;
}
public String getUserName() {
return user_name;
}
public void setUserName(String user_name) {
this.user_name = user_name;
}
public Boolean getSex() {
return sex;
}
public void setSex(Boolean sex) {
this.sex = sex;
}
public String created_time() {
return created_time;
}
public void setCreatedTime(String created_time) {
this.created_time = created_time;
}
}
userService.java
package com.testProject1.springboot.Service;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.testProject1.springboot.Dao.userDao;
import com.testProject1.springboot.entity.userentity;
//@Service用于将该类声明为一个服务类bean；@Autowired用于连接到userDao）
@Service
public class userService {
@Autowired
private userDao userDao;
public List<userentity> showAll()
{
return userDao.findAll();
}
public List<userentity> getUser(userentity user)
{
return userDao.getUser(user);
}
}
POSTMAN 调试
上述书使用 sts编译工具