摘要:
启动项目后,使用select 1 from dual连接数据库成功,但是访问表operator数据报错如下,
从以上报错可以看出,居然在H2的数据库中找operator数据,但我明明配置的是oracle 的驱动,而且表数据在我oracle数据库也存在
导致该文这种错误的主要原因为为Oracle驱动配置错误,因为本人的Oracle驱动配置信息如下
# 数据源、视图配置
datasource:
name: oracle
continue-on-error: true
driver-class-name: com.oracle.jdbc.Driver
url: jdbc:oracle:thin:@localhost:1521:XE
username: system
password: 1234
#连接池的配置信息
initialSize: 10
minIdle: 10
maxActive: 100
maxWait: 60000
这种oracle驱动配置方式为使用 Druid数据库连接池才是这么配置的,而我项目中并没有使用Druid数据库连接池,所以这么配置肯定找不到Oracle驱动,正确的配置如下
# 数据源、视图配置
spring:
datasource:
name: oracle
continue-on-error: true
driver-class-name: oracle.jdbc.OracleDriver
url: jdbc:oracle:thin:@localhost:1521:XE
username: system
password: 1234
ConnectionTestQuery: SELECT 1
#连接池的配置信息
initialSize: 10
minIdle: 10
maxActive: 100
maxWait: 60000
1 场景还原:
1.1 为了连接数据库,所以以下引入依赖
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.3</version>
</dependency>
<!-- spring-boot整合mybatis -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.0</version>
</dependency>
其中ojdbc6的驱动是按照本文的正规操作安装到maven本地仓库的,操作步骤如下文
1.2 application.yml 配置
server:
address: 0.0.0.0
port: 10001
sessionTimeout: 3600
# 数据源、视图配置
datasource:
name: oracle
continue-on-error: true
driver-class-name: com.oracle.jdbc.Driver
url: jdbc:oracle:thin:@localhost:1521:XE
username: system
password: 1234
#连接池的配置信息
initialSize: 10
minIdle: 10
maxActive: 100
maxWait: 60000
注意上图的数据源配置以及datasource.driver-class-name的配置路径和配置值,细心的同学应该发现了,这个驱动配置的路径和值好像和以前的不一样【这里就是导致错误的原始位置】
1.3 添加vo和表
package com.mon.project.model.welcomeDBDemo.vo;
public class Operator {
private Long id;
private String dates;
private String equpment;
private String operators;
...
CREATE TABLE operator(
ID NUMBER(4),
dates VARCHAR2(10),
equpment varchar2(10),
operators varchar2(10)
)
INSERT INTO OPERATOR VALUES(1,'301', 'd', 'jemy');
1.4 添加dao层,xml
package com.mon.project.model.welcomeDBDemo.dao;
import org.springframework.stereotype.Repository;
@Repository
public interface WelcomeDBDao {
public int findWelcomeDB();
public Operator findOperator();
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mon.project.model.welcomeDBDemo.dao.WelcomeDBDao">
<select id="findWelcomeDB" resultType="java.lang.Integer" >
select 1 from dual
</select>
<select id="findOperator" resultType="com.mon.project.model.welcomeDBDemo.vo.Operator">
select id,dates,equpment,operators from operator where id =1
</select>
</mapper>
1.5 添加controller层
package com.mon.project.model.welcomeDBDemo.contoller;
import com.mon.project.model.welcomeDBDemo.dao.WelcomeDBDao;
import com.mon.project.model.welcomeDBDemo.vo.Operator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("/welcomeDB")
public class WelcomeDBController {
@Autowired
WelcomeDBDao welcomeDBDao;
@RequestMapping(value = "/getWelcomeDB",method = RequestMethod.GET)
public int getWelcomeDB(){
return welcomeDBDao.findWelcomeDB();
}
@RequestMapping(value = "/findOperator",method = RequestMethod.GET)
public Operator findOperator(){
return welcomeDBDao.findOperator();
}
}
1.6启动项目
package com.mon.project;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan(basePackages = {"com.mon.project.**.dao"})
public class ProjectApplication {
public static void main(String[] args) {
SpringApplication.run(ProjectApplication.class, args);
}
}
1.7 启动就会报错,报错如下:
***************************
APPLICATION FAILED TO START
***************************
Description:
Failed to configure a DataSource: 'url' attribute is not specified and no embedded datasource could be configured.
Reason: Failed to determine a suitable driver class
Action:
Consider the following:
If you want an embedded database (H2, HSQL or Derby), please put it on the classpath.
If you have database settings to be loaded from a particular profile you may need to activate it (no profiles are currently active).
看这个错误,好像是缺少H2驱动,因为之前就有报这方面的错误,所以直接就把下面的 pom依赖加入
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
1.8此时启动非常欢快,而且启动成功了
访问getWelcomeDB成功,说明数据库连接成功
http://localhost:10001/welcomeDB/getWelcomeDB
访问findOperator报错
http://localhost:10001/welcomeDB/findOperator
注意看上面的红框中文字,居然是用的驱动是H2,然后报错没有找到表OPERATOR。
哈,我在oracle中查数据都有啊
2 问题分析
从1.8最后访问findOperator报错信息看,数据库虽然连接成功了,但是确是使用H2的数据库,而 H2是一个内置的内存数据库 依赖这个后 默认你的数据库就有配置了,而之前 1.7提示没有H2数据库其实不是主要原因,因为主要原因就是没有Oracle的驱动,所以才推荐配置H2的数据库驱动。
那么问题来了,为什么我application.yml 配置了datasource.driver-class-name=com.oracle.jdbc.Driver,但还是找不到驱动呢?后面查Oracle驱动的配置方式一般有两种
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
#spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
spring.datasource.username=scott
spring.datasource.password=dfasd
而datasource.driver-class-name=com.oracle.jdbc.Driver这种配置方式是使用阿里的 Druid数据库连接池才是这么配置的,而我项目中并没有使用Druid数据库连接池,所以这么配置肯定找不到Oracle驱动
解决方式:
1.去掉H2的pom依赖
2.修改application.yml中连接数据库的配置
# 数据源、视图配置
spring:
datasource:
name: oracle
# continue-on-error: true
driver-class-name: oracle.jdbc.OracleDriver
url: jdbc:oracle:thin:@localhost:1521:XE
username: system
password: 1234
ConnectionTestQuery: SELECT 1
#连接池的配置信息
initialSize: 10
minIdle: 10
maxActive: 100
maxWait: 60000