springboot整合spring jdbc

springboot整合spring jdbc

目录结构

maven引入jar包

 

<?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 https://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.4.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>springboot-jdbc</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springboot-jdbc</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>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--lombok用来简化实体类：需要安装lombok插件-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <!--SpringBoot 添加jdbc支持-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.28</version>
        </dependency>

        <!-- 引入本地oracle6 jar包（因oracle收费，maven无法直接引入oracle jar)-->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.1.0</version>
            <scope>system</scope>
            <systemPath>${project.basedir}/src/main/resources/lib/ojdbc6.jar</systemPath>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <includeSystemScope>true</includeSystemScope>
                </configuration>
            </plugin>
            <!--修改 maven plugin 版本-->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-resources-plugin</artifactId>
                <version>3.1.0</version>
            </plugin>
            <!--添加maven配置跳过测试-->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <configuration>
                    <skipTests>true</skipTests>
                </configuration>
            </plugin>
        </plugins>

    </build>

</project>

application.properties基础配置

#设置当前应用的名称
#spring.application.name=springboot
#配置tomcat端口号
server.port=8888

#多环境配置，测试，生产用不同的配置文件
#spring.profiles.active=test
# 数据源oracle
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:orcl
spring.datasource.username=gdkjbm
spring.datasource.password=eams
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver

演示效果

控制层Controller

package com.springboot.controller;

import com.springboot.service.ExamInfoService;
import com.springboot.util.R;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/test")
public class IndexController {

    @Autowired
    private ExamInfoService examInfoService;

    @GetMapping("/ok")
    public R test1(){
        return R.ok();
    }

    @GetMapping("/error")
    public R test2(){
        return R.error();
    }

    /**
     * 返回 list Map
     * @return
     */
    @GetMapping("/list")
    public R list(){
        List<Map<String, Object>> list = examInfoService.getExamInfoAll();
        return R.ok().data("list", list);
    }

    /**
     * 返回 Map
     * @return
     */
    @GetMapping("/map")
    public R map(){
        List<Map<String, Object>> list = examInfoService.getExamInfoAll();
        Map<String, Object> map = list.get(0);
        return R.ok().data(map);
    }

}

业务层（service）

package com.springboot.service.impl;

import com.springboot.dao.ExamInfoDao;
import com.springboot.entity.ExamInfo;
import com.springboot.service.ExamInfoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

@Service
public class ExamInfoServiceImpl implements ExamInfoService {

    @Autowired
    ExamInfoDao examInfoDao;

    @Override
    public List<Map<String, Object>> getExamInfoAll() {
        return examInfoDao.getExamInfoAll();
    }

    @Override
    public ExamInfo getExamInfo(String uuid) {
        return examInfoDao.getExamInfo(uuid);
    }
}

package com.springboot.service;

import com.springboot.entity.ExamInfo;

import java.util.List;
import java.util.Map;

public interface ExamInfoService {

    List<Map<String, Object>> getExamInfoAll();

    ExamInfo getExamInfo(String uuid);

}

数据访问层（dao）

package com.springboot.dao.impl;

import com.springboot.dao.ExamInfoDao;
import com.springboot.entity.ExamInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public class ExamInfoDaoImpl implements ExamInfoDao {

    // 注入JdbcTemplate对象
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Map<String, Object>> getExamInfoAll() {
        String sql = " select * from exam_info ";
        // 返回列表，这里需要注意的是，返回的Map其中的每个entry封装了列名以及对应的值
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        return maps;
    }

    @Override
    public ExamInfo getExamInfo(String uuid) {
        return null;
    }

}

package com.springboot.dao;

import com.springboot.entity.ExamInfo;

import java.util.List;
import java.util.Map;

public interface ExamInfoDao {

    List<Map<String, Object>> getExamInfoAll();

    ExamInfo getExamInfo(String uuid);

}

实体类

package com.springboot.entity;

import java.util.Date;

public class ExamInfo {

    private String uuid;

    private String examYear;

    private String examBatchCode;

    private String examCategoryCode;
    
    private String examCategoryName;
    
    private String examDate;

    private String examTime;

    private String examPlace;

    private String examAddress;

    private String creator;

    private Date createTime;

    private String notes;

	@Override
	public String toString() {
		return "ExamInfo [uuid=" + uuid + ", examYear=" + examYear + ", examBatchCode=" + examBatchCode
				+ ", examCategoryCode=" + examCategoryCode + ", examCategoryName=" + examCategoryName + ", examDate="
				+ examDate + ", examTime=" + examTime + ", examPlace=" + examPlace + ", examAddress=" + examAddress
				+ ", creator=" + creator + ", createTime=" + createTime + ", notes=" + notes + "]";
	}

	public String getUuid() {
        return uuid;
    }

    public void setUuid(String uuid) {
        this.uuid = uuid == null ? null : uuid.trim();
    }

    public String getExamYear() {
        return examYear;
    }

    public void setExamYear(String examYear) {
        this.examYear = examYear == null ? null : examYear.trim();
    }

    public String getExamBatchCode() {
        return examBatchCode;
    }

    public void setExamBatchCode(String examBatchCode) {
        this.examBatchCode = examBatchCode == null ? null : examBatchCode.trim();
    }

    public String getExamCategoryCode() {
        return examCategoryCode;
    }

    public void setExamCategoryCode(String examCategoryCode) {
        this.examCategoryCode = examCategoryCode == null ? null : examCategoryCode.trim();
    }

    public String getExamCategoryName() {
		return examCategoryName;
	}

	public void setExamCategoryName(String examCategoryName) {
		this.examCategoryName = examCategoryName == null ? null : examCategoryName.trim();
	}

	public String getExamDate() {
        return examDate;
    }

    public void setExamDate(String examDate) {
        this.examDate = examDate == null ? null : examDate.trim();
    }

    public String getExamTime() {
        return examTime;
    }

    public void setExamTime(String examTime) {
        this.examTime = examTime == null ? null : examTime.trim();
    }

    public String getExamPlace() {
        return examPlace;
    }

    public void setExamPlace(String examPlace) {
        this.examPlace = examPlace == null ? null : examPlace.trim();
    }

    public String getExamAddress() {
        return examAddress;
    }

    public void setExamAddress(String examAddress) {
        this.examAddress = examAddress == null ? null : examAddress.trim();
    }

    public String getCreator() {
        return creator;
    }

    public void setCreator(String creator) {
        this.creator = creator == null ? null : creator.trim();
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public String getNotes() {
        return notes;
    }

    public void setNotes(String notes) {
        this.notes = notes == null ? null : notes.trim();
    }
}

工具类

package com.springboot.util;

import lombok.Data;

import java.util.HashMap;
import java.util.Map;

//统一返回结果的类
@Data
public class R {

    private Boolean success;

    private Integer code;

    private String message;

    private Map<String, Object> data = new HashMap<String, Object>();

    //把构造方法私有
    private R() {}

    //成功静态方法
    public static R ok() {
        R r = new R();
        r.setSuccess(true);
        r.setCode(20000);
        r.setMessage("成功");
        return r;
    }

    //失败静态方法
    public static R error() {
        R r = new R();
        r.setSuccess(false);
        r.setCode(20001);
        r.setMessage("失败");
        return r;
    }

    public R success(Boolean success){
        this.setSuccess(success);
        return this;
    }

    public R message(String message){
        this.setMessage(message);
        return this;
    }

    public R code(Integer code){
        this.setCode(code);
        return this;
    }

    public R data(String key, Object value){
        this.data.put(key, value);
        return this;
    }

    public R data(Map<String, Object> map){
        this.setData(map);
        return this;
    }
}

package com.springboot.util;

import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

public class ResponseUtil {

    public static void out(HttpServletResponse response, R r) {
        ObjectMapper mapper = new ObjectMapper();
        response.setStatus(HttpStatus.OK.value());
        response.setContentType(MediaType.APPLICATION_JSON_UTF8_VALUE);
        try {
            mapper.writeValue(response.getWriter(), r);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

 数据库表结构

CREATE TABLE "GDKJBM"."EXAM_INFO" 
   (	"UUID" VARCHAR2(64), 
	"EXAM_YEAR" VARCHAR2(64), 
	"EXAM_BATCH_CODE" VARCHAR2(64), 
	"EXAM_CATEGORY_CODE" VARCHAR2(64), 
	"EXAM_DATE" VARCHAR2(64), 
	"EXAM_TIME" VARCHAR2(64), 
	"EXAM_PLACE" VARCHAR2(200), 
	"EXAM_ADDRESS" VARCHAR2(500), 
	"NOTES" CLOB, 
	"CREATOR" VARCHAR2(64), 
	"CREATE_TIME" DATE, 
	"EXAM_CATEGORY_NAME" VARCHAR2(200)
   )