SpringBoot连接Oracle第一个Demo(粘贴即用版)

1.创建项目,选择依赖,附pom,这里我配了springcloud、Eureka,可以选择性删除

<?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.2.5.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
        <spring-cloud.version>Hoxton.SR3</spring-cloud.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-netflix-eureka-server</artifactId>
        </dependency>

        <dependency>
            <groupId>com.oracle.ojdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc14</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/cn.easyproject/orai18n -->
        <dependency>
            <groupId>cn.easyproject</groupId>
            <artifactId>orai18n</artifactId>
            <version>12.1.0.2.0</version>
        </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>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
    </dependencies>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>${spring-cloud.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

2.配置application.properties

#给当前服务起一个名字
spring.application.name=eureka
#设置端口号
server.port=8080
#默认情况下, Eureka Server也是一个普通的微服务,所以当它还是一个注册中心的时候,
# 他会有两层身份: 1.注册中心: 2,普通服务,
# 即当前服务会自己把自己注册到自己上面来#register-with-eureka设置为false,
# 表示当前项目不要注册到注册中心上
eureka.client.register-with-eureka=false
#表示是否从eureka Server 上获取注册信息
eureka.client.fetch-registry=false

spring.datasource.url=jdbc:oracle:thin:@localhost:1521/orcl
spring.datasource.username=pbp_qx
spring.datasource.password=pbp
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.max-idle=10
spring.datasource.max-wait=10000
spring.datasource.min-idle=5
spring.datasource.initial-size=5

3.使用idea连接Oracle,自动映射想要输出的表

这里我为了拓展方便,建立了一个basedo

package com.example.demo.entity;

public class BaseDO {

    private String token;// app token
    // 代码
    private String code;
    // 序列号
    private Integer serialno;
    // 备注
    private String remark;
    // 记录状态
    private String rstate;
    // 记录拼音码
    private String rspell;
    // 记录最后修改时间
    private String rlastmodifytime;
    // 最后修改人主键
    private String rlastmodifier;
    // 最后修改人姓名
    private String rlastmodifiername;
    // 记录创建时间
    private String rcreationtime;
    // 记录创建人主键
    private String rcreator;
    // 记录创建版本
    private String rcreationversion;



    public String getToken() {
        return token;
    }

    /**
     *
     * @return 代码
     */
    public String getCode() {
        return code;
    }

    /**
     * @return 序列号
     */
    public Integer getSerialno() {
        return serialno;
    }

    /**
     * @return 备注
     */
    public String getRemark() {
        return remark;
    }

    /**
     *
     * @return 记录状态
     */
    public String getRstate() {
        return rstate;
    }

    /**
     *
     * @return 记录拼音码
     */
    public String getRspell() {
        return rspell;
    }

    /**
     *
     * @return 最后修改时间
     */
    public String getRlastmodifytime() {
        return rlastmodifytime;
    }

    /**
     *
     * @return 最后修改主键
     */
    public String getRlastmodifier() {
        return rlastmodifier;
    }

    /**
     *
     * @return 最后修改人姓名
     */
    public String getRlastmodifiername() {
        return rlastmodifiername;
    }

    /**
     *
     * @return 创建时间
     */
    public String getRcreationtime() {
        return rcreationtime;
    }

    /**
     *
     * @return 创建人主键
     */
    public String getRcreator() {
        return rcreator;
    }

    /**
     * @return 创建版本
     */
    public String getRcreationversion() {
        return rcreationversion;
    }

    public void setToken(String token) {
        this.token = token;
    }

    /**
     * @param code
     *            代码
     */
    public void setCode(String code) {
        this.code = code;
    }

    /**
     * @param serialno
     *            序列号
     */
    public void setSerialno(Integer serialno) {
        this.serialno = serialno;
    }

    /**
     * @param remark
     *            备注
     */
    public void setRemark(String remark) {
        this.remark = remark;
    }

    /**
     * @param rstate
     *            记录状态
     */
    public void setRstate(String rstate) {
        this.rstate = rstate;
    }

    /**
     * @param rspell
     *            记录拼音码
     */
    public void setRspell(String rspell) {
        this.rspell = rspell;
    }

    /**
     * @param rlastmodifytime
     *            最后修改时间
     */
    public void setRlastmodifytime(String rlastmodifytime) {
        this.rlastmodifytime = rlastmodifytime;
    }

    /**
     * @param rlastmodifier
     *            最后修改人主键
     */
    public void setRlastmodifier(String rlastmodifier) {
        this.rlastmodifier = rlastmodifier;
    }

    /**
     * @param rlastmodifiername
     *            最后修改人姓名
     */
    public void setRlastmodifiername(String rlastmodifiername) {
        this.rlastmodifiername = rlastmodifiername;
    }

    /**
     * @param rcreationtime
     *            创建时间
     */
    public void setRcreationtime(String rcreationtime) {
        this.rcreationtime = rcreationtime;
    }

    /**
     * @param rcreator
     *            创建人主键
     */
    public void setRcreator(String rcreator) {
        this.rcreator = rcreator;
    }

    /**
     * @param rcreationversion
     *            创建版本
     */
    public void setRcreationversion(String rcreationversion) {
        this.rcreationversion = rcreationversion;
    }

}

package com.example.demo.entity;


public class CusCustomer extends BaseDO {

  private String pkcus;
  private String cusname;
  private String pkit;
  private String identificationno;
  private String pkpo;
  private String pkparent;
  private String pksta;
  private String staname;
  private String pkorg;
  private String orgname;
  private String pkog;
  private String pkstate;
  private String pkregi;
  private String pkhs;
  private String pkindu;
  private String pkcusType;
  private String pkhouseOwnership;
  private String addr;
  private String mobilephoneno;
  private String contactphoneno;
  private String officephoneno;
  private String email;
  private java.sql.Date dateofbirth;
  private String ifHead;
  private String ifLoandemand;
  private String pkcrSys;
  private String pkcrAffirm;
  private String creditlineSys;
  private String creditlineAffirm;
  private String pkcl;
  private String pkmark;
  private String pkgender;
  private String pkel;
  private String pkedo;
  private String pkms;
  private String pkttl;
  private String pkps;
  private String pkoc;
  private String pknation;
  private String pkentDevelopProspect;
  private String pkentScale;
  private String pkentProductManageMode;
  private String pkmanageFeatureType;
  private String wechat;
  private String pksys;
  private String addr2;
  private String pkur;
  private String pkgz;
  private String pkzb;
  private String pkbu;
  private java.sql.Date filetime;
  private String ifValid;
  private java.sql.Date creditratingdateAudit;
  private String englishname;
  private String website;
  private String limitAmount;
  private String cusintegral;
  private String cusintegralOutdate;
  private String cusintegralExchangeTotal;


  public String getPkcus() {
    return pkcus;
  }

  public void setPkcus(String pkcus) {
    this.pkcus = pkcus;
  }


  public String getCusname() {
    return cusname;
  }

  public void setCusname(String cusname) {
    this.cusname = cusname;
  }


  public String getPkit() {
    return pkit;
  }

  public void setPkit(String pkit) {
    this.pkit = pkit;
  }


  public String getIdentificationno() {
    return identificationno;
  }

  public void setIdentificationno(String identificationno) {
    this.identificationno = identificationno;
  }


  public String getPkpo() {
    return pkpo;
  }

  public void setPkpo(String pkpo) {
    this.pkpo = pkpo;
  }


  public String getPkparent() {
    return pkparent;
  }

  public void setPkparent(String pkparent) {
    this.pkparent = pkparent;
  }


  public String getPksta() {
    return pksta;
  }

  public void setPksta(String pksta) {
    this.pksta = pksta;
  }


  public String getStaname() {
    return staname;
  }

  public void setStaname(String staname) {
    this.staname = staname;
  }


  public String getPkorg() {
    return pkorg;
  }

  public void setPkorg(String pkorg) {
    this.pkorg = pkorg;
  }


  public String getOrgname() {
    return orgname;
  }

  public void setOrgname(String orgname) {
    this.orgname = orgname;
  }


  public String getPkog() {
    return pkog;
  }

  public void setPkog(String pkog) {
    this.pkog = pkog;
  }


  public String getPkstate() {
    return pkstate;
  }

  public void setPkstate(String pkstate) {
    this.pkstate = pkstate;
  }


  public String getPkregi() {
    return pkregi;
  }

  public void setPkregi(String pkregi) {
    this.pkregi = pkregi;
  }


  public String getPkhs() {
    return pkhs;
  }

  public void setPkhs(String pkhs) {
    this.pkhs = pkhs;
  }


  public String getPkindu() {
    return pkindu;
  }

  public void setPkindu(String pkindu) {
    this.pkindu = pkindu;
  }


  public String getPkcusType() {
    return pkcusType;
  }

  public void setPkcusType(String pkcusType) {
    this.pkcusType = pkcusType;
  }


  public String getPkhouseOwnership() {
    return pkhouseOwnership;
  }

  public void setPkhouseOwnership(String pkhouseOwnership) {
    this.pkhouseOwnership = pkhouseOwnership;
  }


  public String getAddr() {
    return addr;
  }

  public void setAddr(String addr) {
    this.addr = addr;
  }


  public String getMobilephoneno() {
    return mobilephoneno;
  }

  public void setMobilephoneno(String mobilephoneno) {
    this.mobilephoneno = mobilephoneno;
  }


  public String getContactphoneno() {
    return contactphoneno;
  }

  public void setContactphoneno(String contactphoneno) {
    this.contactphoneno = contactphoneno;
  }


  public String getOfficephoneno() {
    return officephoneno;
  }

  public void setOfficephoneno(String officephoneno) {
    this.officephoneno = officephoneno;
  }


  public String getEmail() {
    return email;
  }

  public void setEmail(String email) {
    this.email = email;
  }


  public java.sql.Date getDateofbirth() {
    return dateofbirth;
  }

  public void setDateofbirth(java.sql.Date dateofbirth) {
    this.dateofbirth = dateofbirth;
  }


  public String getIfHead() {
    return ifHead;
  }

  public void setIfHead(String ifHead) {
    this.ifHead = ifHead;
  }


  public String getIfLoandemand() {
    return ifLoandemand;
  }

  public void setIfLoandemand(String ifLoandemand) {
    this.ifLoandemand = ifLoandemand;
  }


  public String getPkcrSys() {
    return pkcrSys;
  }

  public void setPkcrSys(String pkcrSys) {
    this.pkcrSys = pkcrSys;
  }


  public String getPkcrAffirm() {
    return pkcrAffirm;
  }

  public void setPkcrAffirm(String pkcrAffirm) {
    this.pkcrAffirm = pkcrAffirm;
  }


  public String getCreditlineSys() {
    return creditlineSys;
  }

  public void setCreditlineSys(String creditlineSys) {
    this.creditlineSys = creditlineSys;
  }


  public String getCreditlineAffirm() {
    return creditlineAffirm;
  }

  public void setCreditlineAffirm(String creditlineAffirm) {
    this.creditlineAffirm = creditlineAffirm;
  }


  public String getPkcl() {
    return pkcl;
  }

  public void setPkcl(String pkcl) {
    this.pkcl = pkcl;
  }


  public String getPkmark() {
    return pkmark;
  }

  public void setPkmark(String pkmark) {
    this.pkmark = pkmark;
  }


  public String getPkgender() {
    return pkgender;
  }

  public void setPkgender(String pkgender) {
    this.pkgender = pkgender;
  }


  public String getPkel() {
    return pkel;
  }

  public void setPkel(String pkel) {
    this.pkel = pkel;
  }


  public String getPkedo() {
    return pkedo;
  }

  public void setPkedo(String pkedo) {
    this.pkedo = pkedo;
  }


  public String getPkms() {
    return pkms;
  }

  public void setPkms(String pkms) {
    this.pkms = pkms;
  }


  public String getPkttl() {
    return pkttl;
  }

  public void setPkttl(String pkttl) {
    this.pkttl = pkttl;
  }


  public String getPkps() {
    return pkps;
  }

  public void setPkps(String pkps) {
    this.pkps = pkps;
  }


  public String getPkoc() {
    return pkoc;
  }

  public void setPkoc(String pkoc) {
    this.pkoc = pkoc;
  }


  public String getPknation() {
    return pknation;
  }

  public void setPknation(String pknation) {
    this.pknation = pknation;
  }


  public String getPkentDevelopProspect() {
    return pkentDevelopProspect;
  }

  public void setPkentDevelopProspect(String pkentDevelopProspect) {
    this.pkentDevelopProspect = pkentDevelopProspect;
  }


  public String getPkentScale() {
    return pkentScale;
  }

  public void setPkentScale(String pkentScale) {
    this.pkentScale = pkentScale;
  }


  public String getPkentProductManageMode() {
    return pkentProductManageMode;
  }

  public void setPkentProductManageMode(String pkentProductManageMode) {
    this.pkentProductManageMode = pkentProductManageMode;
  }


  public String getPkmanageFeatureType() {
    return pkmanageFeatureType;
  }

  public void setPkmanageFeatureType(String pkmanageFeatureType) {
    this.pkmanageFeatureType = pkmanageFeatureType;
  }


  public String getWechat() {
    return wechat;
  }

  public void setWechat(String wechat) {
    this.wechat = wechat;
  }

  public String getPksys() {
    return pksys;
  }

  public void setPksys(String pksys) {
    this.pksys = pksys;
  }


  public String getAddr2() {
    return addr2;
  }

  public void setAddr2(String addr2) {
    this.addr2 = addr2;
  }


  public String getPkur() {
    return pkur;
  }

  public void setPkur(String pkur) {
    this.pkur = pkur;
  }


  public String getPkgz() {
    return pkgz;
  }

  public void setPkgz(String pkgz) {
    this.pkgz = pkgz;
  }


  public String getPkzb() {
    return pkzb;
  }

  public void setPkzb(String pkzb) {
    this.pkzb = pkzb;
  }


  public String getPkbu() {
    return pkbu;
  }

  public void setPkbu(String pkbu) {
    this.pkbu = pkbu;
  }


  public java.sql.Date getFiletime() {
    return filetime;
  }

  public void setFiletime(java.sql.Date filetime) {
    this.filetime = filetime;
  }


  public String getIfValid() {
    return ifValid;
  }

  public void setIfValid(String ifValid) {
    this.ifValid = ifValid;
  }


  public java.sql.Date getCreditratingdateAudit() {
    return creditratingdateAudit;
  }

  public void setCreditratingdateAudit(java.sql.Date creditratingdateAudit) {
    this.creditratingdateAudit = creditratingdateAudit;
  }


  public String getEnglishname() {
    return englishname;
  }

  public void setEnglishname(String englishname) {
    this.englishname = englishname;
  }


  public String getWebsite() {
    return website;
  }

  public void setWebsite(String website) {
    this.website = website;
  }


  public String getLimitAmount() {
    return limitAmount;
  }

  public void setLimitAmount(String limitAmount) {
    this.limitAmount = limitAmount;
  }


  public String getCusintegral() {
    return cusintegral;
  }

  public void setCusintegral(String cusintegral) {
    this.cusintegral = cusintegral;
  }


  public String getCusintegralOutdate() {
    return cusintegralOutdate;
  }

  public void setCusintegralOutdate(String cusintegralOutdate) {
    this.cusintegralOutdate = cusintegralOutdate;
  }


  public String getCusintegralExchangeTotal() {
    return cusintegralExchangeTotal;
  }

  public void setCusintegralExchangeTotal(String cusintegralExchangeTotal) {
    this.cusintegralExchangeTotal = cusintegralExchangeTotal;
  }

}

4.建立Dao

package com.example.demo.dao;

import com.example.demo.entity.CusCustomer;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;

@Repository
public class CusCustomerDao {

    @Resource
    private JdbcTemplate jdbcTemplate;

    public CusCustomer getPersonInfo(String cusname) {
        String querySQL = "select pkcus,CUSNAME from CUS_CUSTOMER where CUSNAME=?";
        RowMapper<CusCustomer> rowMapper = new BeanPropertyRowMapper<>(CusCustomer.class);
        CusCustomer vo = null;
        try {
            vo = jdbcTemplate.queryForObject(querySQL, new Object[] { cusname }, rowMapper);
        } catch (DataAccessException e) {
            e.printStackTrace();
            return null;
        }
        return vo;
    }


}

5.建立service和serviceimpl

package com.example.demo.service;

import com.example.demo.entity.CusCustomer;

public interface CusCustomerService {

    public CusCustomer getPersonInfo(String cusname);
}

package com.example.demo.service.impl;

import com.example.demo.dao.CusCustomerDao;
import com.example.demo.entity.CusCustomer;
import com.example.demo.service.CusCustomerService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class CusCustomerServiceImpl implements CusCustomerService {

    @Autowired
    private CusCustomerDao customerDAO;


    @Override
    public CusCustomer getPersonInfo(String cusname) {
        return customerDAO.getPersonInfo(cusname);
    }

}

6.建立controller

package com.example.demo.controller;

import com.example.demo.entity.CusCustomer;
import com.example.demo.service.CusCustomerService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/user")
public class CusCustomerController {

    @Autowired
    private CusCustomerService cusCustomerService;

    @RequestMapping("/hello")
    public String hello(@Value("hello") String name){
        return name;
    }

    @RequestMapping("/getPersonInfo")
    public CusCustomer getPersonInfo(String cusname){
        CusCustomer vo = cusCustomerService.getPersonInfo(cusname);
        return vo;
    }


}

7.地址访问:

http://localhost:8080/user/getPersonInfo?cusname=崔进玲

8.效果图