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

xiaoxiao2023-12-02 158

前言：在大公司里面，一般采用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>  <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>  <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>  <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 Files\Java\jdk1.8.0_131\bin\java.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:\Users\HP\.IntelliJIdea2018.2\system\captureAgent\debugger-agent.jar=file:/C:/Users/HP/AppData/Local/Temp/capture.props -Dfile.encoding=UTF-8 -classpath "C:\Program Files\Java\jdk1.8.0_131\jre\lib\charsets.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\deploy.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\access-bridge-64.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\cldrdata.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\dnsns.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\jaccess.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\jfxrt.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\localedata.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\nashorn.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\sunec.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\sunjce_provider.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\sunmscapi.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\sunpkcs11.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\zipfs.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\javaws.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\jce.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\jfr.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\jfxswt.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\jsse.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\management-agent.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\plugin.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\resources.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\rt.jar;D:\myproject\spring-boot-samples\spring-samples-impl\target\classes;D:\myproject\spring-boot-samples\spring-samples-common\target\classes;C:\Users\HP\.m2\repository\org\springframework\boot\spring-boot-starter-web\2.1.4.RELEASE\spring-boot-starter-web-2.1.4.RELEASE.jar;C:\Users\HP\.m2\repository\org\springframework\boot\spring-boot-starter\2.1.4.RELEASE\spring-boot-starter-2.1.4.RELEASE.jar;C:\Users\HP\.m2\repository\org\springframework\boot\spring-boot\2.1.4.RELEASE\spring-boot-2.1.4.RELEASE.jar;C:\Users\HP\.m2\repository\org\springframework\boot\spring-boot-autoconfigure\2.1.4.RELEASE\spring-boot-autoconfigure-2.1.4.RELEASE.jar;C:\Users\HP\.m2\repository\org\springframework\boot\spring-boot-starter-logging\2.1.4.RELEASE\spring-boot-starter-logging-2.1.4.RELEASE.jar;C:\Users\HP\.m2\repository\ch\qos\logback\logback-classic\1.2.3\logback-classic-1.2.3.jar;C:\Users\HP\.m2\repository\ch\qos\logback\logback-core\1.2.3\logback-core-1.2.3.jar;C:\Users\HP\.m2\repository\org\apache\logging\log4j\log4j-to-slf4j\2.11.2\log4j-to-slf4j-2.11.2.jar;C:\Users\HP\.m2\repository\org\apache\logging\log4j\log4j-api\2.11.2\log4j-api-2.11.2.jar;C:\Users\HP\.m2\repository\org\slf4j\jul-to-slf4j\1.7.26\jul-to-slf4j-1.7.26.jar;C:\Users\HP\.m2\repository\javax\annotation\javax.annotation-api\1.3.2\javax.annotation-api-1.3.2.jar;C:\Users\HP\.m2\repository\org\springframework\spring-core\5.1.6.RELEASE\spring-core-5.1.6.RELEASE.jar;C:\Users\HP\.m2\repository\org\springframework\spring-jcl\5.1.6.RELEASE\spring-jcl-5.1.6.RELEASE.jar;C:\Users\HP\.m2\repository\org\yaml\snakeyaml\1.23\snakeyaml-1.23.jar;C:\Users\HP\.m2\repository\org\springframework\boot\spring-boot-starter-json\2.1.4.RELEASE\spring-boot-starter-json-2.1.4.RELEASE.jar;C:\Users\HP\.m2\repository\com\fasterxml\jackson\core\jackson-databind\2.9.8\jackson-databind-2.9.8.jar;C:\Users\HP\.m2\repository\com\fasterxml\jackson\core\jackson-annotations\2.9.0\jackson-annotations-2.9.0.jar;C:\Users\HP\.m2\repository\com\fasterxml\jackson\core\jackson-core\2.9.8\jackson-core-2.9.8.jar;C:\Users\HP\.m2\repository\com\fasterxml\jackson\datatype\jackson-datatype-jdk8\2.9.8\jackson-datatype-jdk8-2.9.8.jar;C:\Users\HP\.m2\repository\com\fasterxml\jackson\datatype\jackson-datatype-jsr310\2.9.8\jackson-datatype-jsr310-2.9.8.jar;C:\Users\HP\.m2\repository\com\fasterxml\jackson\module\jackson-module-parameter-names\2.9.8\jackson-module-parameter-names-2.9.8.jar;C:\Users\HP\.m2\repository\org\springframework\boot\spring-boot-starter-tomcat\2.1.4.RELEASE\spring-boot-starter-tomcat-2.1.4.RELEASE.jar;C:\Users\HP\.m2\repository\org\apache\tomcat\embed\tomcat-embed-core\9.0.17\tomcat-embed-core-9.0.17.jar;C:\Users\HP\.m2\repository\org\apache\tomcat\embed\tomcat-embed-el\9.0.17\tomcat-embed-el-9.0.17.jar;C:\Users\HP\.m2\repository\org\apache\tomcat\embed\tomcat-embed-websocket\9.0.17\tomcat-embed-websocket-9.0.17.jar;C:\Users\HP\.m2\repository\org\hibernate\validator\hibernate-validator\6.0.16.Final\hibernate-validator-6.0.16.Final.jar;C:\Users\HP\.m2\repository\javax\validation\validation-api\2.0.1.Final\validation-api-2.0.1.Final.jar;C:\Users\HP\.m2\repository\org\jboss\logging\jboss-logging\3.3.2.Final\jboss-logging-3.3.2.Final.jar;C:\Users\HP\.m2\repository\com\fasterxml\classmate\1.4.0\classmate-1.4.0.jar;C:\Users\HP\.m2\repository\org\springframework\spring-web\5.1.6.RELEASE\spring-web-5.1.6.RELEASE.jar;C:\Users\HP\.m2\repository\org\springframework\spring-beans\5.1.6.RELEASE\spring-beans-5.1.6.RELEASE.jar;C:\Users\HP\.m2\repository\org\springframework\spring-webmvc\5.1.6.RELEASE\spring-webmvc-5.1.6.RELEASE.jar;C:\Users\HP\.m2\repository\org\springframework\spring-aop\5.1.6.RELEASE\spring-aop-5.1.6.RELEASE.jar;C:\Users\HP\.m2\repository\org\springframework\spring-context\5.1.6.RELEASE\spring-context-5.1.6.RELEASE.jar;C:\Users\HP\.m2\repository\org\springframework\spring-expression\5.1.6.RELEASE\spring-expression-5.1.6.RELEASE.jar;C:\Users\HP\.m2\repository\org\springframework\boot\spring-boot-starter-data-jpa\2.1.4.RELEASE\spring-boot-starter-data-jpa-2.1.4.RELEASE.jar;C:\Users\HP\.m2\repository\org\springframework\boot\spring-boot-starter-aop\2.1.4.RELEASE\spring-boot-starter-aop-2.1.4.RELEASE.jar;C:\Users\HP\.m2\repository\org\aspectj\aspectjweaver\1.9.2\aspectjweaver-1.9.2.jar;C:\Users\HP\.m2\repository\org\springframework\boot\spring-boot-starter-jdbc\2.1.4.RELEASE\spring-boot-starter-jdbc-2.1.4.RELEASE.jar;C:\Users\HP\.m2\repository\org\springframework\spring-jdbc\5.1.6.RELEASE\spring-jdbc-5.1.6.RELEASE.jar;C:\Users\HP\.m2\repository\javax\transaction\javax.transaction-api\1.3\javax.transaction-api-1.3.jar;C:\Users\HP\.m2\repository\javax\xml\bind\jaxb-api\2.3.1\jaxb-api-2.3.1.jar;C:\Users\HP\.m2\repository\javax\activation\javax.activation-api\1.2.0\javax.activation-api-1.2.0.jar;C:\Users\HP\.m2\repository\org\hibernate\hibernate-core\5.3.9.Final\hibernate-core-5.3.9.Final.jar;C:\Users\HP\.m2\repository\javax\persistence\javax.persistence-api\2.2\javax.persistence-api-2.2.jar;C:\Users\HP\.m2\repository\org\javassist\javassist\3.23.1-GA\javassist-3.23.1-GA.jar;C:\Users\HP\.m2\repository\net\bytebuddy\byte-buddy\1.9.12\byte-buddy-1.9.12.jar;C:\Users\HP\.m2\repository\antlr\antlr\2.7.7\antlr-2.7.7.jar;C:\Users\HP\.m2\repository\org\jboss\jandex\2.0.5.Final\jandex-2.0.5.Final.jar;C:\Users\HP\.m2\repository\org\dom4j\dom4j\2.1.1\dom4j-2.1.1.jar;C:\Users\HP\.m2\repository\org\hibernate\common\hibernate-commons-annotations\5.0.4.Final\hibernate-commons-annotations-5.0.4.Final.jar;C:\Users\HP\.m2\repository\org\springframework\data\spring-data-jpa\2.1.6.RELEASE\spring-data-jpa-2.1.6.RELEASE.jar;C:\Users\HP\.m2\repository\org\springframework\data\spring-data-commons\2.1.6.RELEASE\spring-data-commons-2.1.6.RELEASE.jar;C:\Users\HP\.m2\repository\org\springframework\spring-orm\5.1.6.RELEASE\spring-orm-5.1.6.RELEASE.jar;C:\Users\HP\.m2\repository\org\springframework\spring-tx\5.1.6.RELEASE\spring-tx-5.1.6.RELEASE.jar;C:\Users\HP\.m2\repository\org\springframework\spring-aspects\5.1.6.RELEASE\spring-aspects-5.1.6.RELEASE.jar;C:\Users\HP\.m2\repository\com\oracle\ojdbc6\11.2.0.3\ojdbc6-11.2.0.3.jar;C:\Users\HP\.m2\repository\com\zaxxer\HikariCP\3.2.0\HikariCP-3.2.0.jar;C:\Users\HP\.m2\repository\org\slf4j\slf4j-api\1.7.26\slf4j-api-1.7.26.jar;C:\Users\HP\.m2\repository\commons-codec\commons-codec\1.11\commons-codec-1.11.jar;C:\Program Files\JetBrains\IntelliJ IDEA 2018.2.3\lib\idea_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:\myproject\spring-boot-samples\spring-samples-impl\target\classes started by HP in D:\myproject\spring-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实现前后端分离项目作铺垫

最新回复(0)