项目需要连接不同版本的数据库,大多数情况下,最新版本的驱动包会兼容老版本的数据库,但仍存在新版的驱动连接老版本数据库出现不兼容的情况。
需要在同一个运行环境中同时加载多个不同版本的驱动对象。
注意类加载器的parent需要设为null,否则它会将加载类通过双亲委派机制委派给AppClassLoader,如果AppClassLoader中已经存在同限定名的类(比如pom文件依赖),这里UrlClassLoader中加载的类会被忽略。
传统方式
String url = "jdbc:oracle:thin:@//192.168.111.201:1521/xxx";
Class.forName("oracle.jdbc.OracleDriver");
connection = DriverManager.getConnection(url, "用户名", "密码");
mysql动态加载驱动
public void driverTest() throws Exception {
String userName = "username";
String password = "password";
String url = "jdbc:mysql://localhost:3306/schema";
String className = "com.mysql.jdbc.Driver";
String jarFilePath = "jar:file:/home/xxx/libs/mysql-connector-java-8.0.29.jar!/";
URLClassLoader loader = new URLClassLoader(new URL[]{new URL(jarFilePath)}, null);
Driver driver = (Driver) Class.forName(className, true, loader).newInstance();
Connection connection = null;
Properties info = new Properties();
info.put("user", userName);
info.put("password", password);
connection = driver.connect(url, info);
System.out.println(connection.getMetaData().getDatabaseProductVersion());
System.out.println(connection.getMetaData().getDriverVersion());
}
oracle动态加载驱动
方式一:绝对路径,/home/oracle/osyn/ojdbc8.jar
String url = "jdbc:oracle:thin:@//192.168.10.201:1521/xxx";
String className = "oracle.jdbc.OracleDriver";
String jarFilePath = "jar:file:/home/oracle/osyn/ojdbc8.jar!/";
System.out.println("jarFilePath=" + jarFilePath);
//注意类加载器的parent需要设为null,否则它会将加载类通过双亲委派机制委派给AppClassLoader,如果AppClassLoader中已经存在同限定名的类(比如pom文件依赖),这里UrlClassLoader中加载的类会被忽略。
URLClassLoader loader = new URLClassLoader(new URL[]{new URL(jarFilePath)}, null);
Driver driver = (Driver) Class.forName(className, true, loader).newInstance();
Properties info = new Properties();
info.put("user", "用户名");
info.put("password", "密码");
connection = driver.connect(url, info);
System.out.println(connection.getMetaData().getDatabaseProductVersion());
System.out.println(connection.getMetaData().getDriverVersion());
方式二:相对路径
将ojdbc8.jar放到 resources/jdbc/ojdbc8.jar
String url = "jdbc:oracle:thin:@//192.168.10.201:1521/xxx";
String className = "oracle.jdbc.OracleDriver";
ClassPathResource resource = new ClassPathResource("jdbc/ojdbc8.jar");
String jarFilePath = resource.getURL() + "!/";
System.out.println(jarFilePath);
System.out.println("jarFilePath=" + jarFilePath);
//注意类加载器的parent需要设为null,否则它会将加载类通过双亲委派机制委派给AppClassLoader,如果AppClassLoader中已经存在同限定名的类(比如pom文件依赖),这里UrlClassLoader中加载的类会被忽略。
URLClassLoader loader = new URLClassLoader(new URL[]{new URL(jarFilePath)}, null);
Driver driver = (Driver) Class.forName(className, true, loader).newInstance();
Properties info = new Properties();
info.put("user", "用户名");
info.put("password", "密码");
connection = driver.connect(url, info);
System.out.println(connection.getMetaData().getDatabaseProductVersion());
System.out.println(connection.getMetaData().getDriverVersion());
使用了一段时间,报错:Caused by: java.lang.OutOfMemoryError: Compressed class space
原因是获取连接,我封装成了一个方法,但是最后并没有 loader.close(); 每次获取连接都加载了一次jdbc的驱动,所以驱动加载最好放在静态代码块中。
将ojdbc8.jar放到 resources/jdbc/ojdbc8.jar
package com.study.service;
@Service
public class PullTaskService {
private static final Logger log = LoggerFactory.getLogger(PullTaskService.class);
private static Driver driver = null;
@Value("${o.url}")
private String url;
@Value("${o.username}")
private String username;
@Value("${o.password}")
private String password;
/**
* 加载驱动
* @author lhs
* @date 2024/3/20 16:49
*/
static {
try {
// 对方数据库版本为Oracle 12.2 需要使用ojdbc8版本驱动
ClassPathResource resource = new ClassPathResource("jdbc/ojdbc8.jar");
String jarFilePath = resource.getURL() + "!/";
// 注意类加载器的parent需要设为null,否则它会将加载类通过双亲委派机制委派给AppClassLoader,
// 如果AppClassLoader中已经存在同限定名的类(比如pom文件依赖),这里UrlClassLoader中加载的类会被忽略。
URLClassLoader loader = new URLClassLoader(new URL[]{new URL(jarFilePath)}, null);
driver = (Driver) Class.forName("oracle.jdbc.OracleDriver", true, loader).newInstance();
} catch (Exception e) {
log.error(e.getMessage(), e);
}
}
/**
* 获取jdbc连接
* 对方数据库版本为Oracle 12.2 需要使用ojdbc8版本驱动
* @author lhs
* @date 2023/12/6 15:15
*/
public Connection getConnection() {
Connection connection = null;
try {
Properties info = new Properties();
info.put("user", username);
info.put("password", password);
connection = driver.connect(url, info);
log.info("JDBC驱动版本:" + connection.getMetaData().getDriverVersion());
return connection;
} catch (Exception e) {
log.error(e.getMessage(), e);
// 出现异常时关闭连接
if (connection != null) {
try {
connection.close();
} catch (SQLException e1) {
log.error(e1.getMessage(), e1);
}
}
}
return null;
}
private List<Map<String, Object>> get() {
Connection connection = null;
try {
connection = getConnection();
// 查询语句
String sql = "SELECT * FROM tableName";
// 查询
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet set = statement.executeQuery();
set.close();
statement.close();
return list;
} catch (Exception e) {
log.error(e.getMessage(), e);
} finally {
try {
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
log.error(e.getMessage(), e);
}
}
return null;
}
}
参考: