1、如是使用springboot,则首推jdbcTemplate
application.properties文件添加配置:
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.url=jdbc:oracle:thin:@192.168.1.184:1521/orcl
spring.datasource.username=xxx
spring.datasource.password=xxx
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.initialSize=10
spring.datasource.minIdle=10
spring.datasource.maxIdle=20
spring.datasource.maxActive=60
spring.datasource.maxWait=60000
spring.datasource.timeBetweenEvictionRunsMillis=60000
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
使用Autowired引入jdbcTemplate对象:
@Autowired
private JdbcTemplate jdbcTemplate;
查询获取数据:
List<Map<String,Object>> list = jdbcTemplate.queryForList(sql, new Object[]{xxx,xxx,xxx,xxx});
List<Map<String,Object>> resultList = hashMaps.stream().map(e ->{
String xxx1= e.get("xxx1").toString();
String xxx2= e.get("xxx2").toString();
if("xx".equals(xxx2)){
e.put("xx3","xxx4");
}
return e;
}).collect(Collectors.toList());
2、如不是用的springboot
(1)也可以使用JdbcTemplate
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
(2)可以使用DBUtils的QueryRunner
QueryRunner queryRunner = new QueryRunner(dataSource);
(3)这里提供两种dataSource:
c3p0的ComboPooledDataSource
package com.zhbr.util;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;
import java.beans.PropertyVetoException;
import java.io.*;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
/**
* 1. 获取数据源
* 2. 获取连接对象
* 3. 返回dbutils的QueryRunner对象
* @ClassName JDBCUtil
* @Description TODO
* @Autor yanni
* @Date 2020/5/5 12:17
* @Version 1.0
*/
public class JDBCUtil {
private static ComboPooledDataSource dataSource = null;
static {
//读取配置文件
String p_dir = System.getProperty("user.dir");
Properties properties = new Properties();
try {
// 使用InPutStream流读取properties文
BufferedReader bufferedReader = new BufferedReader(new FileReader(p_dir+"/app.properties"));
// 使用 properties 对象加载输入流
properties.load(bufferedReader);
} catch (IOException e) {
e.printStackTrace();
}
dataSource = new ComboPooledDataSource();
dataSource.setJdbcUrl(properties.getProperty("db.url"));
dataSource.setUser(properties.getProperty("db.userName"));
dataSource.setPassword(properties.getProperty("db.password"));
dataSource.setInitialPoolSize(2);
dataSource.setMaxIdleTime(30);
dataSource.setMaxPoolSize(10);
dataSource.setMinPoolSize(2);
dataSource.setMaxStatements(50);
try {
dataSource.setDriverClass(properties.getProperty("db.driver"));
} catch (PropertyVetoException e) {
e.printStackTrace();
}
}
/**
* 1.获取数据库连接对象 (事务),这个连接对象需要手动释放
* @return
*/
public static Connection getConnection() {
try {
return dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 2.获取dbutils的QueryRunner对象(非事务),资源会自动释放
* @return
*/
public static QueryRunner getQueryRunner() {
return new QueryRunner(dataSource);
}
}
alibaba的DruidDataSource
package com.zhbr.util;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.commons.dbutils.QueryRunner;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
public class JDBCUtil1 {
//声明连接池
private static DruidDataSource dataSource = null;
static {
//读取配置文件
String p_dir = System.getProperty("user.dir");
Properties properties = new Properties();
try {
// 使用InPutStream流读取properties文
BufferedReader bufferedReader = new BufferedReader(new FileReader(p_dir+"/app.properties"));
// 使用 properties 对象加载输入流
properties.load(bufferedReader);
} catch (IOException e) {
e.printStackTrace();
}
dataSource = new DruidDataSource();
dataSource.setUrl(properties.getProperty("db.url"));
dataSource.setUsername(properties.getProperty("db.userName"));
dataSource.setPassword(properties.getProperty("db.password"));
dataSource.setValidationQuery("SELECT 1");//用来检测连接是否有效
dataSource.setMaxActive(20);
dataSource.setInitialSize(10);
dataSource.setDriverClassName(properties.getProperty("db.driver"));
}
/**
* 1.获取数据库连接对象 (事务),这个连接对象需要手动释放
* @return
*/
public static Connection getConnection() {
try {
return dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 2.获取dbutils的QueryRunner对象(非事务),资源会自动释放
* @return
*/
public static QueryRunner getQueryRunner() {
return new QueryRunner(dataSource);
}
}