使用Oracle数据的SpringBoot项目【原创】

2019-08-26 本文已影响0人 elijah777

使用Oracle数据的SpringBoot项目

一、在docker安装oraclexe

1、拉镜像

docker pull oracleinanutshell/oracle-xe-11g

2、启动镜像

docker run -d -p 49161:1521 -p 9090:8080 oracleinanutshell/oracle-xe-11g

3、端口设置

添加指定需要开放的端口： firewall-cmd --add-port=8761/tcp --permanent
重载入添加的端口： firewall-cmd --reload
查询指定端口是否开启成功： firewall-cmd --query-port=123/tcp
查询所有开发端口 firewall-cmd --list-ports

3、用户操作

管理员操作

For APEX user:

http://192.168.99.100:8080/apex/apex_admin

登录之后需要更新密码

创建工作区

创建工作区.png

使用datagrip连接

用户密码.png

4、进去容器查看是否正常

先查询到容器的id

docker ps

在终端中进入此容器

docker exec -it f4e5ad1e61c3 /bin/bash

运行 sqlplus

root@f4e5ad1e61c3:/# sqlplus

用户名与密码登陆

username: system
password: oracle

5、创建用户并分配权限

select username,password from dba_users;
-- 用户名为 boot；密码为：boot
create user boot identified by boot;
select * from all_users;
-- 给用户赋予connect 和 resource 角色（connect： 保证数据库可以链接；resource：该用户可以使用数据库的资源；create session：会话的权利）
grant connect,resource,create session to boot;
-- 创建表格添加，更改用户表空间配额
grant unlimited tablespace to boot;

二、项目上应用

pom 中jar引入

<parent>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-parent</artifactId>
 <version>2.1.6.RELEASE</version>
 <relativePath/> <!-- lookup parent from repository -->
 </parent>

 <dependencies>
 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-data-jpa</artifactId>
 </dependency>
 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-web</artifactId>
 </dependency>
<!--
 <dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 </dependency>-->
-->

 <dependency>
 <groupId>com.oracle</groupId>
 <artifactId>ojdbc6</artifactId>
 <version>11.2.0.4.0-atlassian-hosted</version>
 </dependency>

 </dependencies>

ojdbc6jar打包maven仓库

下载地址

https://www.oracle.com/database/technologies/jdbcdriver-ucp-downloads.html

mvn install:install-file -DgroupId=com.oracle "-DartifactId=ojdbc6" "-Dversion=11.2.0.2.0" "-Dpackaging=jar" "-D
file=D:\tmp\odbj6.jar"

yaml配置

spring:
 datasource:
 url: jdbc:oracle:thin:192.168.99.100:49161:xe
 username: boot
 password: boot
 driver-class-name: oracle.jdbc.driver.OracleDriver
 #spring:
#  datasource:
#    driver-class-name: com.mysql.jdbc.Driver
#    username: root
#    password: 123456
#    url: jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf-8&useSSL=false  

 jpa:
 show-sql: true

代码java代码说明

Repository类

public interface PersonRepository extends JpaRepository<Person, Long> {

 @Query("select p from Person p where p.address= :address")
 List<Person> findByAddress(@Param("address") String address);
 }

Servicice类

@Service
public class PersonServiceImpl implements PersonService {

 @Autowired
 private PersonRepository personRepository;

 @Override
 public List<Person> findByAddress(String address) {
 return personRepository.findByAddress(address);
 }

 @Override
 public Person save(Person person) {
 return personRepository.save(person);
 }
}

controller类

@RestController
public class DataController {

 @Autowired
 private PersonService personService;

 @RequestMapping("/save")
 public Person save(Long id,String name, String address, Integer age) {
 Person p = personService.save(new Person(id, name, age, address));
 return p;
 }

 @RequestMapping("/q1")
 public List<Person> q1(String address) {
 List<Person> p = personService.findByAddress(address);
 return p;
 }
}

DTO

@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
@NamedQuery(name = "Person.withName", query = "select p from Person p where p.name = ?1 and p.address=?2")
public class Person {

 @Id
 @GeneratedValue(strategy=GenerationType.IDENTITY)
 private Long id;
 private String name;
 private Integer age;
 private String address;
}