工作中碰到的需求,有三张表,一张厂区,一张区域,一张楼层表(其实是楼栋),为了练习,随便写的。这三张的表的关联关系是:
厂区>区域>楼层,楼层中有一个外键id指向区域的id,区域中有一个外键id指向厂区的id。表信息如下:
厂区表
区域表
楼层表
实体类:
@Data
public class AreaVO implements Serializable {
private int id;
private String factory;
private String area;
private String floor;
private String createEdit;
private Date createTime;
}
@Data注解式lombok插件,省去了get、set、toString方法。
Mapper层:
@Mapper
@Repository
public interface AreaVOMapper {
@Select("select t.factory, a.area, f.floor, f.create_edit createEdit, f.create_time createTime from factory t join area a on t.id = a.fac_id join floor f on a.id = f.area_id where t.factory = #{factory} and a.area = #{area}")
List<AreaVO> select(AreaVO areaVO);
}
省去xml配置,直接通过注解的方式写到方法上,该项目是springboot项目。
service层和实现类:
public interface AreaVOService {
List<AreaVO> select(AreaVO areaVO);
}
@Service
public class AreaVOServiceImpl implements AreaVOService {
@Autowired
private AreaVOMapper areaVOMapper;
@Override
public List<AreaVO> select(AreaVO areaVO) {
return areaVOMapper.select(areaVO);
}
}
这里没什么,要注意格式书写哦。下面是controller层:
@RestController
@RequestMapping("/test")
public class AreaController {
@Autowired
private AreaVOService areaVOService;
@RequestMapping("/select")
public List<AreaVO> select(@RequestBody Map<String ,Object> map){
String factory = (String) map.get("factory");
String area = (String) map.get("area");
AreaVO areaVO = new AreaVO();
areaVO.setFactory(factory);
areaVO.setArea(area);
List<AreaVO> list = areaVOService.select(areaVO);
System.out.println(list.get(0).toString());
return list;
}
}
使用postman工具测试的,所以都是传递的json数据,并没有写前端界面。调用接口显示如下:
很简单,但是第一次这么写,记录一下!下面贴上依赖的配置以及数据库的相关配置:
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<!--lombok插件-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.4</version>
<scope>provided</scope>
</dependency>
<!--oracle依赖 本地添加-->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.1.0</version>
</dependency>
<!--druid数据库连接池->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.9</version>
</dependency>
</dependencies>
server:
port: 8080
servlet:
context-path: /bootdemo
spring:
datasource:
driver-class-name: oracle.jdbc.OracleDriver
url: jdbc:oracle:thin:@22.56.51.131:1521:ora12c
username: acns
password: acns
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 5
minIdle: 5
# 配置获取连接等待超时的时间
maxWait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 300000