5.14. Spring boot with Oracle

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

5.14.1. Maven

首先到oracle官网,根据你的Oracle数据库,下载ojdbc6.jar(Oracle 11) 或者 ojdbc7.jar (Oracle 12)

			
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc7</artifactId>
            <version>12.1.0.1</version>
        </dependency>
			
			
				mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0.3 -Dpackaging=jar -Dfile=ojdbc6.jar -DgeneratePom=true
				mvn install:install-file -Dfile=ojdbc7.jar -DgroupId=com.oracle -DartifactId=ojdbc7 -Dversion=12.1.0.1 -Dpackaging=jar
			

另一种方案是在项目根目录下创建一个/lib文件夹,将下载的驱动放入该文件夹中。然后pom.xml 加入下面代码

ojdbc6.jar 例子

			
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
            <scope>system</scope>
            <systemPath>${basedir}/lib/ojdbc6.jar</systemPath>
        </dependency>
			
			

ojdbc7.jar 例子

			
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc7</artifactId>
            <version>12.1.0.1</version>
            <scope>system</scope>
            <systemPath>${basedir}/lib/ojdbc7.jar</systemPath>
        </dependency>
			
			

例 5.1. Example Spring boot with Oracle

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

	<groupId>netkiller.cn</groupId>
	<artifactId>api.netkiller.cn</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>api.netkiller.cn</name>
	<url>http://maven.apache.org</url>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<java.version>1.8</java.version>
	</properties>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.3.0.RELEASE</version>
	</parent>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<!-- <dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-security</artifactId>
		</dependency> -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-redis</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-mongodb</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-amqp</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>org.springframework.data</groupId>
			<artifactId>spring-data-mongodb</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.data</groupId>
			<artifactId>spring-data-oracle</artifactId>
			<version>1.0.0.RELEASE</version>
		</dependency>

        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <!-- <version>12.1.0.1</version> -->
            <version>11.2.0.3</version>
            <scope>system</scope>
            <systemPath>${basedir}/lib/ojdbc6.jar</systemPath>
        </dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
		<dependency>
			<groupId>com.google.code.gson</groupId>
			<artifactId>gson</artifactId>
			<scope>compile</scope>
		</dependency>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<sourceDirectory>src</sourceDirectory>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
			<plugin>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.3</version>
				<configuration>
					<source />
					<target />
				</configuration>
			</plugin>
			<plugin>
				<artifactId>maven-war-plugin</artifactId>
				<version>2.6</version>
				<configuration>
					<warSourceDirectory>WebContent</warSourceDirectory>
					<failOnMissingWebXml>false</failOnMissingWebXml>
				</configuration>
			</plugin>
		</plugins>
	</build>

</project>

				
				

5.14.2. application.properties

			
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@//192.168.4.9:1521/orcl.example.com
spring.datasource.username=www
spring.datasource.password=123123
spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect

spring.jpa.show-sql=true
#spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.ddl-auto=create-drop
			
			

其他配置

				spring.datasource.connection-test-query="SELECT 1 FROM DUAL"
				spring.datasource.test-while-idle=true
				spring.datasource.test-on-borrow=true
			

Oracle RAC

				jdbc:oracle:thin@(DESCRIPTION=
				(LOAD_BALANCE=on)
				(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1) (PORT=1521))
				(ADDRESS=(PROTOCOL=TCP)(HOST=racnode2) (PORT=1521))
				(CONNECT_DATA=(SERVICE_NAME=service_name)))

				jdbc:oracle:thin:@(DESCRIPTION=
				(ADDRESS=(PROTOCOL=TCP)(HOST=125.22.42.68)(PORT=1521))
				(LOAD_BALANCE=on)
				(FAILOVER=ON)
				(CONNECT_DATA=
				(SERVER=DEDICATED)
				(SERVICE_NAME=service_name)
				(FAILOVER_MODE=(TYPE=SESSION)(METHOD=BASIC))
				)
				)
			

5.14.3. Application

			
package api;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.data.mongodb.repository.config.EnableMongoRepositories;
import org.springframework.web.servlet.config.annotation.CorsRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;

import api.ApplicationConfiguration;

@SpringBootApplication
@EnableConfigurationProperties(ApplicationConfiguration.class)
@EnableAutoConfiguration
@ComponentScan({ "api.web", "api.rest", "api.service" })
@EnableMongoRepositories
@EnableJpaRepositories
public class Application {

	public @Bean WebMvcConfigurer corsConfigurer() {
		return new WebMvcConfigurerAdapter() {
			@Override
			public void addCorsMappings(CorsRegistry registry) {
				registry.addMapping("/**");
			}
		};
	}

	public static void main(String[] args) {
		SpringApplication.run(Application.class, args);
	}

}
			
			

5.14.4. CrudRepository

			
package api.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import api.domain.Article;

@Repository
public interface ArticleRepository extends CrudRepository<Article, Long> {

    Page<Article> findAll(Pageable pageable);

    Article findByTitle(String title);
    
    //@Query("select id,title,content from Article where id > ?1")
    //public List<Article> findBySearch(@Param("id")long id); 

}
			
			
			
package api.domain;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Table;

@Entity
@Table(name = "article")
public class Article implements Serializable {
	private static final long serialVersionUID = 7998903421265538801L;

	@Id
	@Column(name = "ID")
	@GeneratedValue(strategy=GenerationType.SEQUENCE, generator = "id_Sequence")
	@SequenceGenerator(name = "id_Sequence", sequenceName = "ID_SEQ")
	private Long id;
	private String title;
	private String content;

	public Article(){
		
	}
	public Article(String title, String content) {
		this.title = title;
		this.content = content;
	}

	public Long getId() {
		return id;
	}
	
	public void setId(Long id) {
		this.id = id;
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public String getContent() {
		return content;
	}

	public void setContent(String content) {
		this.content = content;
	}

	@Override
	public String toString() {
		return "Article [id=" + id + ", title=" + title + ", content=" + content + "]";
	}

}
			
			

5.14.5. JdbcTemplate

			
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@RequestMapping(value = "/article")
	public @ResponseBody String dailyStats(@RequestParam Integer id) {
		String query = "SELECT id, title, content from article where id = " + id;

		return jdbcTemplate.queryForObject(query, (resultSet, i) -> {
			System.out.println(resultSet.getLong(1)+","+ resultSet.getString(2)+","+ resultSet.getString(3));
			return (resultSet.getLong(1)+","+ resultSet.getString(2)+","+ resultSet.getString(3));
		});
	}
			
			

5.14.6. Controller

			
package api.web;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import api.domain.Article;
import api.repository.ArticleRepository;

@Controller
public class IndexController {

	@Autowired
	private ArticleRepository articleRepository;
	
	@RequestMapping("/mysql")
	@ResponseBody
	public String mysql() {
		repository.deleteAll();
		return "Deleted"	
	}
	
	@RequestMapping("/mysql")
	@ResponseBody
	public String mysql() {
		articleRepository.save(new Article("Neo", "Chen"));
		for (Article article : articleRepository.findAll()) {
			System.out.println(article);
		}
		Article tmp = articleRepository.findByTitle("Neo");
		return tmp.getTitle();
	}
	
	/*
	@RequestMapping("/search")
	@ResponseBody
	public String search() {
		
		/*for (Article article : articleRepository.findBySearch(1)) {
			System.out.println(article);
		}*/
		List<Article> tmp = articleRepository.findBySearch(1L);
		
		tmp.forEach((temp) -> {
			System.out.println(temp.toString());
		});
		
		return tmp.get(0).getTitle();
	}
	*/
	
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@RequestMapping(value = "/article")
	public @ResponseBody String dailyStats(@RequestParam Integer id) {
		String query = "SELECT id, title, content from article where id = " + id;

		return jdbcTemplate.queryForObject(query, (resultSet, i) -> {
			System.out.println(resultSet.getLong(1)+","+ resultSet.getString(2)+","+ resultSet.getString(3));
			return (resultSet.getLong(1)+","+ resultSet.getString(2)+","+ resultSet.getString(3));
		});

	}
}
			
			
			




原文出处:Netkiller 系列 手札
本文作者:陈景峯
转载请与作者联系,同时请务必标明文章原始出处和作者信息及本声明。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
8月前
|
Java 应用服务中间件 Maven
SpringBoot 项目瘦身指南
SpringBoot 项目瘦身指南
163 0
|
8月前
SpringBoot+Mybatis-Plus+PageHelper分页+多条件查询
SpringBoot+Mybatis-Plus+PageHelper分页+多条件查询
196 0
|
2月前
|
存储 运维 安全
Spring运维之boot项目多环境(yaml 多文件 proerties)及分组管理与开发控制
通过以上措施,可以保证Spring Boot项目的配置管理在专业水准上,并且易于维护和管理,符合搜索引擎收录标准。
50 2
|
3月前
|
SQL JSON Java
mybatis使用三:springboot整合mybatis,使用PageHelper 进行分页操作,并整合swagger2。使用正规的开发模式:定义统一的数据返回格式和请求模块
这篇文章介绍了如何在Spring Boot项目中整合MyBatis和PageHelper进行分页操作,并且集成Swagger2来生成API文档,同时定义了统一的数据返回格式和请求模块。
91 1
mybatis使用三:springboot整合mybatis,使用PageHelper 进行分页操作,并整合swagger2。使用正规的开发模式:定义统一的数据返回格式和请求模块
|
3月前
|
缓存 NoSQL Java
Springboot自定义注解+aop实现redis自动清除缓存功能
通过上述步骤,我们不仅实现了一个高度灵活的缓存管理机制,还保证了代码的整洁与可维护性。自定义注解与AOP的结合,让缓存清除逻辑与业务逻辑分离,便于未来的扩展和修改。这种设计模式非常适合需要频繁更新缓存的应用场景,大大提高了开发效率和系统的响应速度。
90 2
|
7月前
|
运维 Java 关系型数据库
Spring运维之boot项目bean属性的绑定读取与校验
Spring运维之boot项目bean属性的绑定读取与校验
62 2
|
7月前
|
存储 运维 Java
Spring运维之boot项目开发关键之日志操作以及用文件记录日志
Spring运维之boot项目开发关键之日志操作以及用文件记录日志
75 2
|
7月前
|
Java Maven
springboot项目打jar包后,如何部署到服务器
springboot项目打jar包后,如何部署到服务器
455 1
|
7月前
|
XML 运维 Java
Spring运维之boot项目打包jar和插件运行并且设置启动时临时属性和自定义配置文件
Spring运维之boot项目打包jar和插件运行并且设置启动时临时属性和自定义配置文件
60 1
|
7月前
springboot2.4.5使用pagehelper分页插件
springboot2.4.5使用pagehelper分页插件
188 0

推荐镜像

更多