从数据库到Spring boot开发后端接口

开发环境：IntelliJ IDEA 数据库：MySQL

一、数据库构建

现在有一张now表 一张load表 数据类型省略

二、Springboot框架开发

1、新建Springboot项目

File->New->New Project->Spring Initializr->选择环境和源->选择依赖。 这里选择MySQL Driver和MyBatis Framework，其他依赖可以之后需要时添加。

2、application.properties项目配置 MyBatis映射、缓存、热部署、端口、数据库、前后端不分离时前端首页等各种配置

spring.datasource.url=jdbc:mysql://localhost:3306/pretrans?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai spring.datasource.username=root server.port=8081 spring.datasource.password=123456 # mybatis mybatis.mapper-locations=classpath:mapper/*.xml

3、结合MyBatis建立mapper层获取数据

（1）实体类 有Load实体类对应数据库load表

public class Load { private Integer id; private Integer roadID; private String name; private Point start; private Point end; private State_now now; private Prediction prediction; private float length; private Integer lane_count; private Integer fRoadID1; private Integer fRoadID2; private Integer fRoadID3; private Integer tRoadID1; private Integer tRoadID2; private Integer tRoadID3; private String turns; private Integer viaLane1; private Integer viaLane2; private Integer viaLane3; private Integer fNode; private Integer tNode; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Point getStart() { return start; } public void setStart(Point start) { this.start = start; } public Point getEnd() { return end; } public void setEnd(Point end) { this.end = end; } public State_now getNow() { return now; } public void setNow(State_now now) { this.now = now; } public float getLength() { return length; } public void setLength(float length) { this.length = length; } public Integer getLane_count() { return lane_count; } public void setLane_count(Integer lane_count) { this.lane_count = lane_count; } public Integer getRoadID() { return roadID; } public void setRoadID(Integer roadID) { this.roadID = roadID; } public Integer getfRoadID1() { return fRoadID1; } public void setfRoadID1(Integer fRoadID1) { this.fRoadID1 = fRoadID1; } public Integer getfRoadID2() { return fRoadID2; } public void setfRoadID2(Integer fRoadID2) { this.fRoadID2 = fRoadID2; } public Integer getfRoadID3() { return fRoadID3; } public void setfRoadID3(Integer fRoadID3) { this.fRoadID3 = fRoadID3; } public Integer gettRoadID1() { return tRoadID1; } public void settRoadID1(Integer tRoadID1) { this.tRoadID1 = tRoadID1; } public Integer gettRoadID2() { return tRoadID2; } public void settRoadID2(Integer tRoadID2) { this.tRoadID2 = tRoadID2; } public Integer gettRoadID3() { return tRoadID3; } public void settRoadID3(Integer tRoadID3) { this.tRoadID3 = tRoadID3; } public String getTurns() { return turns; } public void setTurns(String turns) { this.turns = turns; } public Integer getViaLane1() { return viaLane1; } public void setViaLane1(Integer viaLane1) { this.viaLane1 = viaLane1; } public Integer getViaLane2() { return viaLane2; } public void setViaLane2(Integer viaLane2) { this.viaLane2 = viaLane2; } public Integer getViaLane3() { return viaLane3; } public void setViaLane3(Integer viaLane3) { this.viaLane3 = viaLane3; } public Integer getfNode() { return fNode; } public void setfNode(Integer fNode) { this.fNode = fNode; } public Integer gettNode() { return tNode; } public void settNode(Integer tNode) { this.tNode = tNode; } public Prediction getPrediction() { return prediction; } public void setPrediction(Prediction prediction) { this.prediction = prediction; } }

State_now实体类对应now字段

import java.sql.Timestamp; public class State_now { private String name; private Integer id; private Integer lid; private Integer count; private float speed; private Integer situation; private float feature1; private float feature2; private float feature3; private Timestamp time; private long timeLong; private float speedMax; private float speedMin; private String onlyTime; public String getName() { return name; } public void setName(String name) { this.name = name; } public float getSpeedMax() { return speedMax; } public void setSpeedMax(float speedMax) { this.speedMax = speedMax; } public float getSpeedMin() { return speedMin; } public void setSpeedMin(float speedMin) { this.speedMin = speedMin; } public String getOnlyTime() { return onlyTime; } public void setOnlyTime(String onlyTime) { this.onlyTime = onlyTime; } public long getTimeLong() { return timeLong; } public void setTimeLong(long timeLong) { this.timeLong = timeLong; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Integer getLid() { return lid; } public void setLid(Integer lid) { this.lid = lid; } public Integer getCount() { return count; } public void setCount(Integer count) { this.count = count; } public float getSpeed() { return speed; } public void setSpeed(float speed) { this.speed = speed; } public Integer getSituation() { return situation; } public void setSituation(Integer level) { this.situation = level; } public float getFeature1() { return feature1; } public void setFeature1(float feature1) { this.feature1 = feature1; } public float getFeature2() { return feature2; } public void setFeature2(float feature2) { this.feature2 = feature2; } public float getFeature3() { return feature3; } public void setFeature3(float feature3) { this.feature3 = feature3; } public Timestamp getTime() { return time; } public void setTime(Timestamp time) { this.time = time; } }

（2）LoadMapper接口（添加@Mapper注解）

import cn.pretrans.pretrans.entity.Load; import cn.pretrans.pretrans.entity.State_now; import org.apache.ibatis.annotations.Mapper; import java.sql.Timestamp; import java.util.List; @Mapper public interface LoadMapper { List<Load> getAllLoad(); State_now getStateNowByLidAndTime(Integer lid, Timestamp time); }

resource/mapper文件夹下新建loadMapper.xml （1）指定所映射的mapper接口 （2）配置实体类属性与数据库表字段的对应关系resultMap （3）通过查询select完成查询操作

XML文件如下

<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN" "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd"> <mapper namespace="cn.pretrans.pretrans.mapper.LoadMapper"> <resultMap id="loadMap" type="cn.pretrans.pretrans.entity.Load"> <id column="id" property="id"/> <result column="name" property="name"/> <result column="length" property="length"/> <result column="lane_count" property="lane_count"/> <association property="start" javaType="cn.pretrans.pretrans.entity.Point"> <result column="start_longitude" property="longitude"/> <result column="start_latitude" property="latitude"/> </association> <association property="end" javaType="cn.pretrans.pretrans.entity.Point"> <result column="end_longitude" property="longitude"/> <result column="end_latitude" property="latitude"/> </association> </resultMap> <select id="getAllLoad" resultType="cn.pretrans.pretrans.entity.Load" resultMap="loadMap"> SELECT id, `name`, `length`, lane_count, start_longitude, start_latitude, end_longitude, end_latitude FROM `load` </select> <select id="getStateNowByLidAndTime" resultType="cn.pretrans.pretrans.entity.State_now"> SELECT id, lid, `count`, speed, situation, feature1, feature2, feature3, `time` FROM now WHERE `time`=#{time} AND lid=#{lid} </select> </mapper>

4、Service层进行业务逻辑处理

（1）Service接口

import cn.pretrans.pretrans.entity.Load; import cn.pretrans.pretrans.entity.State_now; import java.sql.Timestamp; import java.util.ArrayList; import java.util.List; public interface LoadService { List<Load> getAllLoad(Timestamp time);//获取当前时刻所有路段信息 Timestamp get5(); }

（2）Service接口实现（添加@Service注解）

import cn.pretrans.pretrans.entity.Load; import cn.pretrans.pretrans.entity.State_now; import cn.pretrans.pretrans.mapper.LoadMapper; import cn.pretrans.pretrans.service.LoadService; import org.springframework.stereotype.Service; import javax.annotation.Resource; import java.sql.Timestamp; import java.util.ArrayList; import java.util.Calendar; import java.util.List; @Service public class LoadServiceImpl implements LoadService { @Resource private LoadMapper loadMapper; @Override public List<Load> getAllLoad(Timestamp time) { List<Load> loads=loadMapper.getAllLoad(); int size=loads.size(); for(int i=0;i<size;i++){ State_now s=loadMapper.getStateNowByLidAndTime(loads.get(i).getId(),time); if(s!=null){ s.setTimeLong(s.getTime().getTime()); loads.get(i).setNow(s); } } return loads; } public Long getTimeNow() { Timestamp t=Timestamp.valueOf("2019-07-18 03:00:00"); Long ts=System.currentTimeMillis(); ts%=75600000; return t.getTime()+ts; //循环从3：00开始显示 } @Override public Timestamp get5() { Long t=getTimeNow(); Long t5=(t/300000)*300000; return new Timestamp(t5); } }

（3）异常类设计（识别异常种类） 1、Unique约束的异常

/** * 违反了Unique约束的异常 */ public class DuplicateKeyException extends ServiceException { private static final long serialVersionUID = 1575222253909645563L; public DuplicateKeyException() { super(); } public DuplicateKeyException(String message, Throwable cause, boolean enableSuppression, boolean writableStackTrace) { super(message, cause, enableSuppression, writableStackTrace); } public DuplicateKeyException(String message, Throwable cause) { super(message, cause); } public DuplicateKeyException(String message) { super(message); } public DuplicateKeyException(Throwable cause) { super(cause); } }

2、数据不存在的异常

/** * 数据不存在异常 * @author Choococo */ public class DataNotFoundException extends ServiceException { private static final long serialVersionUID = 3247248436291557749L; public DataNotFoundException() { } public DataNotFoundException(String message, Throwable cause, boolean enableSuppression, boolean writableStackTrace) { super(message, cause, enableSuppression, writableStackTrace); } public DataNotFoundException(String message, Throwable cause) { super(message, cause); } public DataNotFoundException(String message) { super(message); } public DataNotFoundException(Throwable cause) { super(cause); } }

5、Controller层实现

（1）BaseController（控制层基类）

import cn.pretrans.pretrans.service.exception.*; import cn.pretrans.pretrans.util.ResponseResult; import org.springframework.web.bind.annotation.ExceptionHandler; import org.springframework.web.bind.annotation.ResponseBody; import javax.servlet.http.HttpSession; /** * 当前项目中所有控制器类的基类 */ public abstract class BaseController { /** * 正确响应的代号 */ public static final Integer SUCCESS = 200; public static final Integer SUCCESSFUL = 0; @ExceptionHandler({ServiceException.class})// 异常的范围 @ResponseBody public ResponseResult<Void> handleException(Exception e) { Integer state = null; if (e instanceof DuplicateKeyException) { state = 400; // 400-违反了Unique约束的异常 // return new ResponseResult<>(400, e); }else if (e instanceof DataNotFoundException) { // 406-数据不存在 state = 406; // return new ResponseResult<>(406, e); } return new ResponseResult<>(state,e); } }

（2）LoadController类

import cn.pretrans.pretrans.entity.Load; import cn.pretrans.pretrans.entity.State_now; import cn.pretrans.pretrans.service.LoadService; import cn.pretrans.pretrans.util.ResponseResult; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.*; import javax.servlet.http.HttpSession; import java.sql.Timestamp; import java.util.ArrayList; import java.util.Calendar; import java.util.List; @RestController @RequestMapping("/load") public class LoadController extends BaseController { @Autowired private LoadService loadService; @GetMapping("/now") public ResponseResult<List<Load>> getAllLoad(HttpSession session){ Timestamp T=loadService.get5(); List<Load> loads=loadService.getAllLoad(T); System.out.println("GET:now all, Time:"+T.toString()+" SIZE:"+loads.size()); return new ResponseResult<>(SUCCESS,loads); } }

此时，前端发送GET请求$axios.get(‘http://localhost:8081/load/now’)即可以请求数据。

当跨域访问时，需要进行相应配置

import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.http.HttpHeaders; import org.springframework.web.servlet.config.annotation.CorsRegistry; import org.springframework.web.servlet.config.annotation.WebMvcConfigurer; /** * 跨域配置模块 */ @Configuration public class CorsConfig implements WebMvcConfigurer { @Bean public WebMvcConfigurer corsConfigurer() { return new WebMvcConfigurer() { @Override public void addCorsMappings(CorsRegistry registry) { registry.addMapping("/**"). allowedOrigins("*"). //允许跨域的域名，可以用*表示允许任何域名使用 allowedMethods("*"). //允许任何方法（post、get等） allowedHeaders("*"). //允许任何请求头 allowCredentials(true). //带上cookie信息 exposedHeaders(HttpHeaders.SET_COOKIE).maxAge(3600L); //maxAge(3600)表明在3600秒内，不需要再发送预检验请求，可以缓存该结果 } }; } }