SpringBoot项目一:Spring-Boot整合oracle数据库之HikariCP数据源

前言：在大公司里面，一般采用Oracle数据库存储自己的客户信息数据。本文参考了包括springboot开发指南及网上一些其他优秀博客整合了自己的SpringBoot+Oracle数据库+HikariDataSource数据源项目，这个项目也将作为学习完整的springboot开发指南以及与实现前后端分离项目的基础。

1.前提条件：

1.1 SpringBoot版本：v2.1.4.RELEASE

1.2 oracle数据库版本：oracle 12C

1.3 windows系统：win10

1.4 开发工具：intellj IDEA 2018.2.3版本

1.5 项目构建工具：Maven 3.3.9

1.6 JDK版本：1.8

2. 搭建项目骨架

2.1 打开IDEA开发工具创建SpringBoot项目

1) 选择File菜单-->New -->Project -->Spring InitiaLizr-->选择Default:https://start.spring.io -->Next

2) 在弹出的Project Metadata（项目元数据）对话框中输入项目的groupId:com.spring.boot; artifactId: spring-boot-samples

3) 选择依赖模块starter-web和starter-test, 点击Finish按钮后IDEA开发工具会生成一个项目名为spring-boot-samples的spring-boot项目

4）选中spring-boot-samples项目-->右键-->New Module-->Maven，新建两个模块spring-samples-common和spring-samples-impl, 将spring-boot-samples项目pom文件中的spring-boot-starter-web和spring-boot-starter-test Maven依赖移动到

spring-samples-common的pom文件中，并加入spring-boot-starter-data-jpa, spring-boot-devtools, ojdbc6,HikariCP和commons-codec的依赖，spring-samples-common的pom文件maven依赖如下：

      <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>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <optional>true</optional>
        </dependency>

        <!-- Spring data JPA, default tomcat pool, exclude it -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.tomcat</groupId>
                    <artifactId>tomcat-jdbc</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <!--oracle驱动 -->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
        </dependency>
        <!-- 数据库连接池-->
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>3.3.1</version>
        </dependency>

        <!-- apache commons加密解密工具类 -->
        <dependency>
            <groupId>commons-codec</groupId>
            <artifactId>commons-codec</artifactId>
            <version>1.10</version>
        </dependency>
      

    </dependencies>

注：

Maven导入ojdbc6 jar包请参考：How to add Oracle JDBC driver in your Maven local repository和自学笔记--Maven导入ojdbc6(亲测可用)（不导入ojdbc6驱动的话，ojdbc6的maven依赖会报红，项目无法编译通过）
将ojdbc6 jar包添加进IDEA项目Maven依赖请参考：IntelliJ IDEA java项目导入jar包，打jar包

以上两步对项目能否正常启动非常重要

2.2 spring-samples-common项目配置文件

1) src/main/resource目录下新建application.properties和db.properties文件，内容如下：

application.properties

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
spring.jpa.hibernate.ddl-auto = update
spring.jpa.show-sql=true

# logging
logging.pattern.console=%d{yyyy-MM-dd HH:mm:ss} %-5level %logger{36} - %msg%n
logging.level.org.hibernate.SQL=debug
logging.level.org.hibernate.type.descriptor.sql=trace

db.properties:


driverClassName=oracle.jdbc.driver.OracleDriver
jdbcUrl=jdbc:oracle:thin:@localhost:1521:ORCL
#生产环境最好存秘钥，连接数据库时再解密处理
username=SYSTEM
#SYSTEM用户连接密码
password=dbpassword

2) src/main/ java目录下新建com.spring.samples.common.configration包，新建CommonConfig配置类，配置HikariCP

的数据源Bean（HikariCP数据源相比传统的commons-dbcp2,tomcat pooling和c3p0等数据源在相同时间内连接/关闭次数，预编译sql和执行sql和关闭statement等方面优势明显,如下图所示,图片来源：https://github.com/brettwooldridge/HikariCP）

package com.spring.samples.common.configration;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;


@Configuration
public class CommonConfig {
    @Bean
    DataSource dataSource(){
        HikariConfig config = new HikariConfig("/db.properties");
        HikariDataSource dataSource = new HikariDataSource(config);
        return dataSource;

    }

}

2.3 spring-samples-impl项目配置

1) pom文件中引入spring-samples-common项目的jar包依赖

    <dependencies>
        <dependency>
            <groupId>com.spring.boot</groupId>
            <artifactId>spring-common</artifactId>
            <version>0.0.1-SNAPSHOT</version>
        </dependency>

    </dependencies>

2) application.properties配置

server.port=8080
server.servlet.context-path=/myApp
#用户注册时的密码加密秘钥
password.key=EFGhkvcf9102@

3）删除原spring-boot-samples项目下src/main/java目录下的启动类和src/test/java目录下的junit测试类，在spring-samples-impl模块项目下的src/main/java目录下新建启动类MySpringApplication.java

package com.spring.samples.impl;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;


@SpringBootApplication(scanBasePackages ={"com.spring.samples.common","com.spring.samples.impl"} )

public class MySpringApplication {
    private static final Logger logger = LoggerFactory.getLogger(MySpringApplication.class);
    public static void main(String[] args) {
        SpringApplication application = new SpringApplication(MySpringApplication.class);
        application.run(args);
    }


}

debug模式启动MySpringApplication启动类成功后，控制台打印出如下日志：

"C:Program FilesJavajdk1.8.0_131binjava.exe" -agentlib:jdwp=transport=dt_socket,address=127.0.0.1:56499,suspend=y,server=n -XX:TieredStopAtLevel=1 -noverify -Dspring.output.ansi.enabled=always -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=56498 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false -Djava.rmi.server.hostname=localhost -Dspring.liveBeansView.mbeanDomain -Dspring.application.admin.enabled=true -javaagent:C:UsersHP.IntelliJIdea2018.2systemcaptureAgentdebugger-agent.jar=file:/C:/Users/HP/AppData/Local/Temp/capture.props -Dfile.encoding=UTF-8 -classpath "C:Program FilesJavajdk1.8.0_131jrelibcharsets.jar;C:Program FilesJavajdk1.8.0_131jrelibdeploy.jar;C:Program FilesJavajdk1.8.0_131jrelibextaccess-bridge-64.jar;C:Program FilesJavajdk1.8.0_131jrelibextcldrdata.jar;C:Program FilesJavajdk1.8.0_131jrelibextdnsns.jar;C:Program FilesJavajdk1.8.0_131jrelibextjaccess.jar;C:Program FilesJavajdk1.8.0_131jrelibextjfxrt.jar;C:Program FilesJavajdk1.8.0_131jrelibextlocaledata.jar;C:Program FilesJavajdk1.8.0_131jrelibextnashorn.jar;C:Program FilesJavajdk1.8.0_131jrelibextsunec.jar;C:Program FilesJavajdk1.8.0_131jrelibextsunjce_provider.jar;C:Program FilesJavajdk1.8.0_131jrelibextsunmscapi.jar;C:Program FilesJavajdk1.8.0_131jrelibextsunpkcs11.jar;C:Program FilesJavajdk1.8.0_131jrelibextzipfs.jar;C:Program FilesJavajdk1.8.0_131jrelibjavaws.jar;C:Program FilesJavajdk1.8.0_131jrelibjce.jar;C:Program FilesJavajdk1.8.0_131jrelibjfr.jar;C:Program FilesJavajdk1.8.0_131jrelibjfxswt.jar;C:Program FilesJavajdk1.8.0_131jrelibjsse.jar;C:Program FilesJavajdk1.8.0_131jrelibmanagement-agent.jar;C:Program FilesJavajdk1.8.0_131jrelibplugin.jar;C:Program FilesJavajdk1.8.0_131jrelibresources.jar;C:Program FilesJavajdk1.8.0_131jrelibrt.jar;D:myprojectspring-boot-samplesspring-samples-impltargetclasses;D:myprojectspring-boot-samplesspring-samples-commontargetclasses;C:UsersHP.m2repositoryorgspringframeworkbootspring-boot-starter-web2.1.4.RELEASEspring-boot-starter-web-2.1.4.RELEASE.jar;C:UsersHP.m2repositoryorgspringframeworkbootspring-boot-starter2.1.4.RELEASEspring-boot-starter-2.1.4.RELEASE.jar;C:UsersHP.m2repositoryorgspringframeworkbootspring-boot2.1.4.RELEASEspring-boot-2.1.4.RELEASE.jar;C:UsersHP.m2repositoryorgspringframeworkbootspring-boot-autoconfigure2.1.4.RELEASEspring-boot-autoconfigure-2.1.4.RELEASE.jar;C:UsersHP.m2repositoryorgspringframeworkbootspring-boot-starter-logging2.1.4.RELEASEspring-boot-starter-logging-2.1.4.RELEASE.jar;C:UsersHP.m2repositorychqoslogbacklogback-classic1.2.3logback-classic-1.2.3.jar;C:UsersHP.m2repositorychqoslogbacklogback-core1.2.3logback-core-1.2.3.jar;C:UsersHP.m2repositoryorgapachelogginglog4jlog4j-to-slf4j2.11.2log4j-to-slf4j-2.11.2.jar;C:UsersHP.m2repositoryorgapachelogginglog4jlog4j-api2.11.2log4j-api-2.11.2.jar;C:UsersHP.m2repositoryorgslf4jjul-to-slf4j1.7.26jul-to-slf4j-1.7.26.jar;C:UsersHP.m2repositoryjavaxannotationjavax.annotation-api1.3.2javax.annotation-api-1.3.2.jar;C:UsersHP.m2repositoryorgspringframeworkspring-core5.1.6.RELEASEspring-core-5.1.6.RELEASE.jar;C:UsersHP.m2repositoryorgspringframeworkspring-jcl5.1.6.RELEASEspring-jcl-5.1.6.RELEASE.jar;C:UsersHP.m2repositoryorgyamlsnakeyaml1.23snakeyaml-1.23.jar;C:UsersHP.m2repositoryorgspringframeworkbootspring-boot-starter-json2.1.4.RELEASEspring-boot-starter-json-2.1.4.RELEASE.jar;C:UsersHP.m2repositorycomfasterxmljacksoncorejackson-databind2.9.8jackson-databind-2.9.8.jar;C:UsersHP.m2repositorycomfasterxmljacksoncorejackson-annotations2.9.0jackson-annotations-2.9.0.jar;C:UsersHP.m2repositorycomfasterxmljacksoncorejackson-core2.9.8jackson-core-2.9.8.jar;C:UsersHP.m2repositorycomfasterxmljacksondatatypejackson-datatype-jdk82.9.8jackson-datatype-jdk8-2.9.8.jar;C:UsersHP.m2repositorycomfasterxmljacksondatatypejackson-datatype-jsr3102.9.8jackson-datatype-jsr310-2.9.8.jar;C:UsersHP.m2repositorycomfasterxmljacksonmodulejackson-module-parameter-names2.9.8jackson-module-parameter-names-2.9.8.jar;C:UsersHP.m2repositoryorgspringframeworkbootspring-boot-starter-tomcat2.1.4.RELEASEspring-boot-starter-tomcat-2.1.4.RELEASE.jar;C:UsersHP.m2repositoryorgapachetomcatembedtomcat-embed-core9.0.17tomcat-embed-core-9.0.17.jar;C:UsersHP.m2repositoryorgapachetomcatembedtomcat-embed-el9.0.17tomcat-embed-el-9.0.17.jar;C:UsersHP.m2repositoryorgapachetomcatembedtomcat-embed-websocket9.0.17tomcat-embed-websocket-9.0.17.jar;C:UsersHP.m2repositoryorghibernatevalidatorhibernate-validator6.0.16.Finalhibernate-validator-6.0.16.Final.jar;C:UsersHP.m2repositoryjavaxvalidationvalidation-api2.0.1.Finalvalidation-api-2.0.1.Final.jar;C:UsersHP.m2repositoryorgjbossloggingjboss-logging3.3.2.Finaljboss-logging-3.3.2.Final.jar;C:UsersHP.m2repositorycomfasterxmlclassmate1.4.0classmate-1.4.0.jar;C:UsersHP.m2repositoryorgspringframeworkspring-web5.1.6.RELEASEspring-web-5.1.6.RELEASE.jar;C:UsersHP.m2repositoryorgspringframeworkspring-beans5.1.6.RELEASEspring-beans-5.1.6.RELEASE.jar;C:UsersHP.m2repositoryorgspringframeworkspring-webmvc5.1.6.RELEASEspring-webmvc-5.1.6.RELEASE.jar;C:UsersHP.m2repositoryorgspringframeworkspring-aop5.1.6.RELEASEspring-aop-5.1.6.RELEASE.jar;C:UsersHP.m2repositoryorgspringframeworkspring-context5.1.6.RELEASEspring-context-5.1.6.RELEASE.jar;C:UsersHP.m2repositoryorgspringframeworkspring-expression5.1.6.RELEASEspring-expression-5.1.6.RELEASE.jar;C:UsersHP.m2repositoryorgspringframeworkbootspring-boot-starter-data-jpa2.1.4.RELEASEspring-boot-starter-data-jpa-2.1.4.RELEASE.jar;C:UsersHP.m2repositoryorgspringframeworkbootspring-boot-starter-aop2.1.4.RELEASEspring-boot-starter-aop-2.1.4.RELEASE.jar;C:UsersHP.m2repositoryorgaspectjaspectjweaver1.9.2aspectjweaver-1.9.2.jar;C:UsersHP.m2repositoryorgspringframeworkbootspring-boot-starter-jdbc2.1.4.RELEASEspring-boot-starter-jdbc-2.1.4.RELEASE.jar;C:UsersHP.m2repositoryorgspringframeworkspring-jdbc5.1.6.RELEASEspring-jdbc-5.1.6.RELEASE.jar;C:UsersHP.m2repositoryjavaxtransactionjavax.transaction-api1.3javax.transaction-api-1.3.jar;C:UsersHP.m2repositoryjavaxxmlbindjaxb-api2.3.1jaxb-api-2.3.1.jar;C:UsersHP.m2repositoryjavaxactivationjavax.activation-api1.2.0javax.activation-api-1.2.0.jar;C:UsersHP.m2repositoryorghibernatehibernate-core5.3.9.Finalhibernate-core-5.3.9.Final.jar;C:UsersHP.m2repositoryjavaxpersistencejavax.persistence-api2.2javax.persistence-api-2.2.jar;C:UsersHP.m2repositoryorgjavassistjavassist3.23.1-GAjavassist-3.23.1-GA.jar;C:UsersHP.m2repositorynetbytebuddybyte-buddy1.9.12byte-buddy-1.9.12.jar;C:UsersHP.m2repositoryantlrantlr2.7.7antlr-2.7.7.jar;C:UsersHP.m2repositoryorgjbossjandex2.0.5.Finaljandex-2.0.5.Final.jar;C:UsersHP.m2repositoryorgdom4jdom4j2.1.1dom4j-2.1.1.jar;C:UsersHP.m2repositoryorghibernatecommonhibernate-commons-annotations5.0.4.Finalhibernate-commons-annotations-5.0.4.Final.jar;C:UsersHP.m2repositoryorgspringframeworkdataspring-data-jpa2.1.6.RELEASEspring-data-jpa-2.1.6.RELEASE.jar;C:UsersHP.m2repositoryorgspringframeworkdataspring-data-commons2.1.6.RELEASEspring-data-commons-2.1.6.RELEASE.jar;C:UsersHP.m2repositoryorgspringframeworkspring-orm5.1.6.RELEASEspring-orm-5.1.6.RELEASE.jar;C:UsersHP.m2repositoryorgspringframeworkspring-tx5.1.6.RELEASEspring-tx-5.1.6.RELEASE.jar;C:UsersHP.m2repositoryorgspringframeworkspring-aspects5.1.6.RELEASEspring-aspects-5.1.6.RELEASE.jar;C:UsersHP.m2repositorycomoracleojdbc611.2.0.3ojdbc6-11.2.0.3.jar;C:UsersHP.m2repositorycomzaxxerHikariCP3.2.0HikariCP-3.2.0.jar;C:UsersHP.m2repositoryorgslf4jslf4j-api1.7.26slf4j-api-1.7.26.jar;C:UsersHP.m2repositorycommons-codeccommons-codec1.11commons-codec-1.11.jar;C:Program FilesJetBrainsIntelliJ IDEA 2018.2.3libidea_rt.jar" com.spring.samples.impl.MySpringApplication
Connected to the target VM, address: '127.0.0.1:56499', transport: 'socket'

  .   ____          _            __ _ _
 /\ / ___'_ __ _ _(_)_ __  __ _    
( ( )___ | '_ | '_| | '_ / _` |    
 \/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |___, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.1.4.RELEASE)

2019-05-26 23:38:09.155  INFO 16880 --- [           main] c.s.samples.impl.MySpringApplication     : Starting MySpringApplication on heshengfu1211 with PID 16880 (D:myprojectspring-boot-samplesspring-samples-impltargetclasses started by HP in D:myprojectspring-boot-samples)
2019-05-26 23:38:09.159  INFO 16880 --- [           main] c.s.samples.impl.MySpringApplication     : No active profile set, falling back to default profiles: default
2019-05-26 23:38:09.975  INFO 16880 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data repositories in DEFAULT mode.
2019-05-26 23:38:10.069  INFO 16880 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 83ms. Found 1 repository interfaces.
2019-05-26 23:38:10.468  INFO 16880 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration' of type [org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration$$EnhancerBySpringCGLIB$$6a97320d] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2019-05-26 23:38:11.014  INFO 16880 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
2019-05-26 23:38:11.041  INFO 16880 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2019-05-26 23:38:11.042  INFO 16880 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.17]
2019-05-26 23:38:11.049  INFO 16880 --- [           main] o.a.catalina.core.AprLifecycleListener   : Loaded APR based Apache Tomcat Native library [1.2.21] using APR version [1.6.5].
2019-05-26 23:38:11.049  INFO 16880 --- [           main] o.a.catalina.core.AprLifecycleListener   : APR capabilities: IPv6 [true], sendfile [true], accept filters [false], random [true].
2019-05-26 23:38:11.050  INFO 16880 --- [           main] o.a.catalina.core.AprLifecycleListener   : APR/OpenSSL configuration: useAprConnector [false], useOpenSSL [true]
2019-05-26 23:38:11.133  INFO 16880 --- [           main] o.a.catalina.core.AprLifecycleListener   : OpenSSL successfully initialized [OpenSSL 1.1.1a  20 Nov 2018]
2019-05-26 23:38:11.352  INFO 16880 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/myApp]  : Initializing Spring embedded WebApplicationContext
2019-05-26 23:38:11.352  INFO 16880 --- [           main] o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 2140 ms
2019-05-26 23:38:11.492  INFO 16880 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2019-05-26 23:38:11.495  WARN 16880 --- [           main] com.zaxxer.hikari.util.DriverDataSource  : Registered driver with driverClassName=oracle.jdbc.driver.OracleDriver was not found, trying direct instantiation.
2019-05-26 23:38:12.353  INFO 16880 --- [           main] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Driver does not support get/set network timeout for connections. (oracle.jdbc.driver.T4CConnection.getNetworkTimeout()I)
2019-05-26 23:38:12.359  INFO 16880 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2019-05-26 23:38:12.611  INFO 16880 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [
	name: default
	...]
2019-05-26 23:38:12.762  INFO 16880 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate Core {5.3.9.Final}
2019-05-26 23:38:12.765  INFO 16880 --- [           main] org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found
2019-05-26 23:38:13.040  INFO 16880 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.0.4.Final}
2019-05-26 23:38:13.246  INFO 16880 --- [           main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.Oracle12cDialect
2019-05-26 23:38:13.402  INFO 16880 --- [           main] org.hibernate.type.BasicTypeRegistry     : HHH000270: Type registration [byte[]] overrides previous : org.hibernate.type.BinaryType@38732372
2019-05-26 23:38:13.402  INFO 16880 --- [           main] org.hibernate.type.BasicTypeRegistry     : HHH000270: Type registration [[B] overrides previous : org.hibernate.type.BinaryType@38732372
2019-05-26 23:38:13.403  INFO 16880 --- [           main] org.hibernate.type.BasicTypeRegistry     : HHH000270: Type registration [Byte[]] overrides previous : org.hibernate.type.WrapperBinaryType@6c6c2a73
2019-05-26 23:38:13.403  INFO 16880 --- [           main] org.hibernate.type.BasicTypeRegistry     : HHH000270: Type registration [[Ljava.lang.Byte;] overrides previous : org.hibernate.type.WrapperBinaryType@6c6c2a73
2019-05-26 23:38:14.080  INFO 16880 --- [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2019-05-26 23:38:14.657  INFO 16880 --- [           main] o.s.s.concurrent.ThreadPoolTaskExecutor  : Initializing ExecutorService 'applicationTaskExecutor'
2019-05-26 23:38:14.705  WARN 16880 --- [           main] aWebConfiguration$JpaWebMvcConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2019-05-26 23:38:14.916  INFO 16880 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path '/myApp'
2019-05-26 23:38:14.920  INFO 16880 --- [           main] c.s.samples.impl.MySpringApplication     : Started MySpringApplication in 6.483 seconds (JVM running for 8.91)

3. 开发Resfull Api接口

3.1 创建表和实体类

1） SYSTEM用户登录PL/SQL Developer 创建用户表tbl_user_info, 建表sql脚本如下

create table SYSTEM.tbl_user_info(
   user_id number(18) primary key,
   user_account varchar2(20) not null,
   password varchar2(32) not null,
   user_name varchar2(30) not null,
   user_cnname varchar2(30),
   phone_num number(11) not null,
   family_address varchar2(100),
   email_address varchar2(30) not null,
   birth_day date
)initrans 6;

--注释
comment on table SYSTEM.tbl_user_info is '用户信息表';
comment on column SYSTEM.tbl_user_info.USER_ID is '用户ID，18位省份证号码';
comment on column tbl_user_info.password is '用户登录密码';
comment on column SYSTEM.tbl_user_info.USER_NAME is '用户姓名';
comment on column SYSTEM.tbl_user_info.user_account is '用户账号';
comment on column SYSTEM.tbl_user_info.user_cnname is '中文名';
comment on column SYSTEM.tbl_user_info.PHONE_NUM is '手机号码';
comment on column SYSTEM.tbl_user_info.FAMILY_ADDRESS is '家庭地址';
comment on column SYSTEM.tbl_user_info.EMAIL_ADDRESS is '邮箱';
comment on column SYSTEM.tbl_user_info.BIRTH_DAY is '出生日期，yyyy-MM-dd';

--创建索引
create unique index SYSTEM.pk_user_phone on SYSTEM.tbl_user_info(phone_num) initrans 16;
create unique index SYSTEM.pk_user_account on SYSTEM.tbl_user_info(user_account) initrans 16;

--创建同义词
create public synonym tbl_user_info for SYSTEM.tbl_user_info;

2）创建tbl_user_info对应的实体类：

package com.spring.samples.impl.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;
import java.util.Date;

@Entity
@Table(name="tbl_user_info")
public class UserInfo implements Serializable {

    @Id
    @Column(name="user_id",nullable = false)
    private Long userId;

    @Column(name="user_account",nullable = false)
    private String userAccount;

    @Column(name="user_name",nullable = false)
    private String userName;

    @Column(name="user_cnname")
    private String userCnname;

    @Column(nullable = false)
    private String password;

    @Column(name="phone_num",nullable = false)
    private Long phoneNum;

    @Column(name="family_address")
    private String familyAddress;

    @Column(name="emailAddress")
    private String emailAddress;

    @Column(name="birth_day")
    private Date birthDay;

    ....此处省略一系列set和get方法

    
}

3.2 分层开发Rest 接口

1） dao层代码

定义UserRepository类继承CrudRepository接口,代码如下

package com.spring.samples.impl.repository;

import com.spring.samples.impl.model.UserInfo;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface UserRepository extends CrudRepository<UserInfo,Long> {

    UserInfo findByUserAccount(String userAccount);

    @Modifying
    @Query(value="update tbl_user_info a set a.password=?2 where a.user_account=?1",nativeQuery = true)
    void modifyPassword(String userAccount,String password);

}

2) service层代码

定义IUserService接口及其实现类UserService,代码如下：

package com.spring.samples.impl.service;

import com.spring.samples.impl.model.UserInfo;

public interface IUserService {

    UserInfo saveUser(UserInfo userInfo);

    UserInfo findUserByUserAccount(String username);

    boolean login(String userAccount,String password);

    void modifyPassword(String userAccount,String password);
}

package com.spring.samples.impl.service.impl;

import com.spring.samples.common.utils.MD5Util;
import com.spring.samples.impl.model.UserInfo;
import com.spring.samples.impl.repository.UserRepository;
import com.spring.samples.impl.service.IUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class UserService implements IUserService {
    @Autowired
    private UserRepository userDao;
    @Value("${password.key}")
    private String key;
    @Override
    public UserInfo saveUser(UserInfo userInfo) {
        try {
            String password=MD5Util.md5(userInfo.getPassword(),key);
            userInfo.setPassword(password);
        } catch (Exception ex) {
           throw new RuntimeException(ex);
        }
        return userDao.save(userInfo);
    }

    @Override
    public UserInfo findUserByUserAccount(String userAccount) {

        return userDao.findByUserAccount(userAccount);
    }

    @Override
    public boolean login(String userAccount, String password) {
        UserInfo userInfo = userDao.findByUserAccount(userAccount);
        if(userInfo==null){
            return false;
        }
        try {
            return  MD5Util.verify(password,key,userInfo.getPassword());
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        }

    }

    @Override
    @Transactional
    public void modifyPassword(String userAccount, String password) {
        try {
            String passwordText = MD5Util.md5(password,key);
            userDao.modifyPassword(userAccount,passwordText);
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        }

    }
}

注意：

UserService类中保存和修改用户密码时采用了MD5加密，数据库中用户密码为安全计会要求存密文；
修改用户密码方法由于其对应的Dao方法使用了自定义@Modifing和@Query注解，需要加上@Transactional注解，接口调用时会报异常

MD5Util工具类代码如下：

package com.spring.samples.common.utils;

import org.apache.commons.codec.digest.DigestUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


public class MD5Util {

    private static final Logger logger = LoggerFactory.getLogger(MD5Util.class);

    /**
     *
     * @param text 明文
     * @param key  秘钥
     * @return 密文
     * @throws Exception
     */
    public static String md5(String text, String key) throws Exception {
        //加密后的字符串
        String encodeStr= DigestUtils.md5Hex(text+key);
        return encodeStr.toUpperCase();
    }

    /**
     *
     * @param text 明文
     * @param key  秘钥
     * @param md5  密文
     * @return
     * @throws Exception
     */
    public static boolean verify(String text, String key, String md5) throws Exception {
        //根据传入的密钥进行验证
        String md5Text = md5(text, key);
        if(md5Text.equalsIgnoreCase(md5))
        {
            System.out.println("MD5验证通过");
            return true;
        }
        return false;
    }



}

3）Controller层代码

BaseController基础类

package com.spring.samples.common.web;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class BaseController {

    protected final Logger logger = LoggerFactory.getLogger(this.getClass());

    //待完善


}

UserController类代码如下：

package com.spring.samples.impl.controller;

import com.spring.samples.common.enums.ResponseEnum;
import com.spring.samples.common.response.ServiceResponse;
import com.spring.samples.common.web.BaseController;
import com.spring.samples.common.web.form.UserForm;
import com.spring.samples.impl.model.UserInfo;
import com.spring.samples.impl.service.IUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

@RestController
public class UserController extends BaseController {
    @Autowired
    private IUserService userService;

    @RequestMapping(path="/saveUser",method= RequestMethod.POST)
    public UserInfo saveUser(@RequestBody UserInfo userInfo){
        logger.info("/saveUser request parameters:{}",userInfo.toString());
        return userService.saveUser(userInfo);
    }

    @RequestMapping(path = "/findUser",method=RequestMethod.GET)
    public UserInfo findUserbyUserAccount(@RequestParam("userAccount") String userAccount){
        logger.info("/findUser request param:{}",userAccount);
        return userService.findUserByUserAccount(userAccount);
    }

    @RequestMapping(path="/login",method= RequestMethod.POST)
    public ServiceResponse<String> userLogin(@RequestParam("userAccount") String userAccount, @RequestParam("password") String password,
                                             HttpServletRequest request){
        UserForm form = new UserForm();
        form.setUserAccount(userAccount);
        form.setPassword(password);
        logger.info("/login request params:{}",form);
        ServiceResponse<String> response = null;
        if(StringUtils.isEmpty(form.getUserAccount())){
            response = new ServiceResponse<>(ResponseEnum.PARAM_ERROR.getStatus(),"userAccount cannot be null");
            return response;
        }
        if(StringUtils.isEmpty(form.getPassword())){
            response = new ServiceResponse<>(ResponseEnum.PARAM_ERROR.getStatus(),"password cannot be null");
            return response;
        }
        HttpSession session = request.getSession();

        //在此之前未登录
        if(null==session.getAttribute("userAccount")){
            boolean  result = userService.login(form.getUserAccount(),form.getPassword());
            if(result){
                session.setAttribute("userAccount",form);
//                session.setMaxInactiveInterval(30);
                response = new ServiceResponse<>(ResponseEnum.SUCCESS.getStatus(),ResponseEnum.SUCCESS.getMsg());
                response.setData("login success");
            }else{
                response = new ServiceResponse<>(ResponseEnum.LOGIN_ERROR.getStatus(),ResponseEnum.LOGIN_ERROR.getMsg());
                response.setData("login failed");
            }
        }else{
            response = new ServiceResponse<>(ResponseEnum.SUCCESS.getStatus(),ResponseEnum.SUCCESS.getMsg());
            response.setData("You already have login success!");
        }
        return response;
    }

    @RequestMapping(path="/logout/{userName}",method = RequestMethod.GET)
    public ServiceResponse<String> logout(@PathVariable String userName, HttpServletRequest request){
        logger.info("/logout request param:{}",userName);
        ServiceResponse<String> response = null;
        if(StringUtils.isEmpty(userName)){
            response = new ServiceResponse<>(ResponseEnum.PARAM_ERROR.getStatus(),"userName cannot be null");
            return response;
        }
        HttpSession session = request.getSession();
        //session失效
        session.invalidate();
        response = new ServiceResponse<>(ResponseEnum.SUCCESS.getStatus(),"login out success");
        return response;
    }

    @RequestMapping(path="/modify/pass",method = RequestMethod.POST)
    public ServiceResponse<String> modifyPassword(@RequestBody UserForm form){
        logger.info("/modify/pass request params:{}",form);
        ServiceResponse<String> response = null;
        if(StringUtils.isEmpty(form.getUserAccount())){
            response = new ServiceResponse<>(ResponseEnum.PARAM_ERROR.getStatus(),"userAccount cannot be null");
            return response;
        }
        if(StringUtils.isEmpty(form.getPassword())){
            response = new ServiceResponse<>(ResponseEnum.PARAM_ERROR.getStatus(),"password cannot be null");
            return response;
        }
        userService.modifyPassword(form.getUserAccount(),form.getPassword());
        response = new ServiceResponse<>(ResponseEnum.SUCCESS.getStatus(),ResponseEnum.SUCCESS.getMsg());
        response.setData("modify password success");
        return response;
    }


}

以上UserController控制器提供了保存用户信息，根据用户账号查找用户信息，用户登录，退出登录和用户修改密码共5个Restful Api接口，启动项目后，采用postman工具验证了接口的可用性，均可行

代码gitee地址:https://gitee.com/heshengfu1211/spring-boot-samples/blob/master/.gitignore

这篇博客也是为了下一篇springboot整合react实现前后端分离项目作铺垫

版权声明：本文来源CSDN，感谢博主原创文章，遵循 CC 4.0 by-sa 版权协议，转载请附上原文出处链接和本声明。
原文链接：https://blog.csdn.net/heshengfu1211/article/details/90553440
站方申明：本站部分内容来自社区用户分享，若涉及侵权，请联系站方删除。

发表于 2020-03-01 22:48:26
阅读 ( 1687 )
分类：数据库

SpringBoot项目一:Spring-Boot整合oracle数据库之HikariCP数据源

你可能感兴趣的文章

精选的优质文章

0 条评论

官方社群

GO教程

推荐文章

猜你喜欢

随便看看