使用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;
}
测试地址
http://127.0.0.1:8888/q1?address=zz
http://127.0.0.1:8888/save?name=hand&age=9&address=bj
该项目切换mysql同样有效
作者在开发过程中遇到的坑 如下，是jar的冲突与缺少，Google出来的方法也是千奇百怪，又让添加jar有让启动类上写注释，还好花费一些时间完成了
equired a bean named 'entityManagerFactory' that could not be found.
