Spring Boot对接Oracle数据库
最近学习了Oracle数据库,那么如何使用Spring Boot和MyBatis Plus对接Oracle数据库呢?
这就有了这篇随记,具体流程如下
1、创建Maven工程
创建一个空的Maven工程,导入如下依赖:
<?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 http://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.3.7.RELEASE</version><relativePath/></parent><groupId>org.example</groupId><artifactId>oracle-init</artifactId><version>1.0-SNAPSHOT</version><properties><maven.compiler.source>8</maven.compiler.source><maven.compiler.target>8</maven.compiler.target><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter</artifactId><version>2.3.7.RELEASE</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><version>2.3.7.RELEASE</version></dependency><!-- Oracel11g与ojdbc5/ojdbc6版本匹配 --><dependency><groupId>com.oracle.database.jdbc</groupId><artifactId>ojdbc6</artifactId><version>11.2.0.4</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.16.10</version><scope>provided</scope></dependency><!-- <dependency>-->
<!-- <groupId>com.oracle.database.jdbc</groupId>-->
<!-- <artifactId>ojdbc8</artifactId>-->
<!-- <version>19.8.0.0</version>-->
<!-- </dependency>--><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.3.2</version></dependency><dependency><groupId>org.junit.jupiter</groupId><artifactId>junit-jupiter</artifactId><version>RELEASE</version><scope>compile</scope></dependency></dependencies>
</project>
tips:这里碰到一个坑,我本机适用的Oracle数据库版本是11g XE,所以要使用的驱动为ojdbc5/ojdbc6,不然连接老会失败。
2、application.yml
在配置文件中填写数据库连接的参数
spring:datasource:username: pppassword: 123456url: jdbc:oracle:thin:@localhost:1521:XEdriver-class-name: oracle.jdbc.OracleDriver
参数说明:
- username:数据库连接用户名
- password:数据库连接密码
- url:连接地址
- driver-class-name:数据库驱动
3、创建实例数据表
为了演示本次对接Oracle数据库,我们需要一张测试数据表
3.1创建Users数据表,SQL如下:
CREATE TABLE users (id INT PRIMARY KEY,username VARCHAR(50) NOT NULL,password VARCHAR(50) NOT NULL,email VARCHAR(100) NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
什么?没有测试数据!!!Orz…
这里可以使用Oracle数据库的PL/SQL编程来批量生成测试数据,真香!!
3.2PL/SQL批量生产测试数据
sql如下:
DECLARETYPE user_type IS RECORD (id NUMBER,username VARCHAR2(50),email VARCHAR2(100),password VARCHAR2(100),created_at DATE);TYPE user_list IS TABLE OF user_type;l_users user_list := user_list();
BEGINFOR i IN 1..100 LOOPl_users.extend;l_users(i).id := i;l_users(i).username := 'user' || i;l_users(i).email := 'user' || i || '@example.com';l_users(i).password := 'password' || i;l_users(i).created_at := SYSDATE; -- 使用当前时间作为创建时间END LOOP;FORALL i IN 1..l_users.COUNTINSERT INTO users (id, username, email, password, created_at)VALUES (l_users(i).id, l_users(i).username, l_users(i).email, l_users(i).password, l_users(i).created_at);COMMIT;DBMS_OUTPUT.PUT_LINE('Data inserted successfully.');
EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);ROLLBACK;
END;
执行之后查看数据表中的数据:
OK!准备工作完成,接下来就可以进行对接了。
4、使用MP对Oracle进行CRUD
4.1创建实体类
User:
/*** TODO User实体类* @version 1.0* @author ss_419* @date 2023/8/11 14:49*/
@Data
@AllArgsConstructor
@NoArgsConstructor
// 指定Oracle数据库中的表名
@TableName("users")
public class User {private Long id;private String username;private String password;private String email;private Date created_at;
}
4.2、创建Mapper
/*** @author ss_419*/
@Mapper
public interface UserRepository extends BaseMapper<User> {// 这里可以自定义一些数据库操作方法
}
4.3、创建Service
/*** @author ss_419*/
@Service
public class UserServiceextends ServiceImpl<UserRepository, User> {// 这里可以编写一些业务逻辑方法private final UserRepository userRepository;@Autowiredpublic UserService(UserRepository userRepository) {this.userRepository = userRepository;}public List<User> getAllUsers() {List<User> users = userRepository.selectList(null);return users;}public User getUserById(Long id) {return userRepository.selectById(id);}public void saveUser(User user) {userRepository.insert(user);}public void updateUser(User user) {userRepository.updateById(user);}public void deleteUser(Long id) {userRepository.deleteById(id);}
}
4.4、创建启动器
@SpringBootApplication
// Mapper包扫描
@MapperScan("org.example.mapper")
public class OracleDBApplication {public static void main(String[] args) {SpringApplication.run(OracleDBApplication.class,args);}
}
5、测试
万事俱备,只欠东风,对CRUD进行测试。
@SpringBootTest
public class OraCTest {@Autowiredprivate UserService service;/*** 获取所有用户信息*/@Testpublic void testGetAllUsers() {List<User> users = service.getAllUsers();users.forEach(System.out::println);}/*** 根据id获取用户*/@Testpublic void testGetUserById() {User userById = service.getUserById(1L);System.out.println("userById = " + userById);}/*** 保存用户*/@Testpublic void testSaveUser() {User user = new User();user.setId(1000L);user.setUsername("测试新增User");user.setPassword("00101010");user.setEmail("test@example.com");user.setCreated_at(new Date());service.saveUser(user);}/*** 更新用户*/@Testpublic void testUpdateUser() {// 先查询User user = service.getUserById(1000L);System.out.println("userById = " + user);// 后更新user.setUsername("update_username");service.updateUser(user);}/*** 根据id删除用户*/@Testpublic void testDeleteUser() {service.deleteUser(1000L);}
}