需要导入包
hikari配置properties文件
jdbcUrl=jdbc:postgresql://localhost:5432/jspdb?useUnicode=true&characterEncoding=UTF-8
username=postgres
password=null
maximumPoolSize=30
minimumIdle=5
connectionTestQuery=SELECT 1
autoCommit=true
dataSource.cachePrepStmts=true
dataSource.prepStmtCacheSize=250
dataSource.prepStmtCacheSqlLimit=2048
dataSource.useServerPrepStmts=true
dataSource.useLocalSessionState=true
dataSource.useLocalTransactionState=true
dataSource.rewriteBatchedStatements=true
dataSource.cacheResultSetMetadata=true
dataSource.cacheServerConfiguration=true
dataSource.elideSetAutoCommits=true
dataSource.maintainTimeStats=false
hikari连接池的使用
public class DBService {
private static Logger logger = LoggerFactory.getLogger(DBService.class);//使用指定类初始化日志对象,该类不必要
private final static String driver = "org.postgresql.Driver";
private static HikariDataSource dataSource;
public static DBService INSTANCE;
static {
INSTANCE = new DBService();
}
private DBService() { super(); }
private static HikariDataSource setupDataSource() { //建立连接池
HikariConfig config = new HikariConfig("src/resource/hikari.properties");
config.getJdbcUrl();
config.getConnectionTimeout();
config.getMaximumPoolSize();
config.getMinimumIdle();
config.getUsername();
config.getPassword();
HikariDataSource ds = new HikariDataSource(config);
return ds;
}
public static DBService getInstance() {
if (INSTANCE == null) {
try {
INSTANCE = new DBService();
} catch (Exception e) {
e.printStackTrace();
}
}
return INSTANCE;
}
//从连接池中获取一个连接
public Connection getConnection() {
Connection con = null;
if (dataSource != null) {
try {
con = dataSource.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
}
return con;
利用连接池插入一百万随机生成数据
public class ConnectionDemo {
public static void insertStu() {
//public List<Student> getAllStunames() {
System.out.println("使用连接池................................");
//开启总计时
long beginTime = System.currentTimeMillis();
Connection conn = HikariManager.getInstance().getConnection();
String sql = new String("INSERT INTO student(sno,sname,sex,tel,addr,sgrade,cid) VALUES(?,?,?,?,?,?,?) ");
try {
PreparedStatement ps = conn.prepareStatement(sql);
conn.setAutoCommit(false);
//默认为自动提交,每执行一个update ,delete或者insert的时候都会自动提交到数据库,无法回滚事务。
//设置connection.setautocommit(false);只有程序调用connection.commit()的时候才会将先前执行的语句一起提交到数据库,这样就实现了数据库的事务。
// true:sql命令的提交(commit)由驱动程序负责
// false:sql命令的提交由应用程序负责,程序必须调用commit或者rollback方法
//总之就是把数据插入完毕之后程序里的commit才会将其一起提交至数据库,否则会有错误数据残余
//一次插入一万条数据,插入十次
for (int n = 1;n <= 100;n++){
//开启分段计时
// long bTime = System.currentTimeMillis();
Random rand = new Random();
for (int i = 1;i <=10000;i++){
int a = rand.nextInt(100);
ps.setString(1, RandomValue.getSno());
ps.setString(2, RandomValue.getChineseName());
ps.setString(3, RandomValue.getSex());
ps.setString(4, RandomValue.getTel());
ps.setString(5, RandomValue.getRoad());
ps.setInt(6, a);
ps.setString(7, RandomValue.getCid());
ps.addBatch();
}
ps.executeBatch();
conn.commit();
}
Long endTime = System.currentTimeMillis();
long time = endTime-beginTime;
System.out.println("插入用时"+time);
}catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
insertStu();
}
}
//默认为自动提交,每执行一个update ,delete或者insert的时候都会自动提交到数据库,无法回滚事务。
//设置connection.setautocommit(false);只有程序调用connection.commit()的时候才会将先前执行的语句一起提交到数据库,这样就实现了数据库的事务。
// true:sql命令的提交(commit)由驱动程序负责
// false:sql命令的提交由应用程序负责,程序必须调用commit或者rollback方法
//总之就是把数据插入完毕之后程序里的commit才会将其一起提交至数据库,否则会有错误数据残余
//一次插入一万条数据,插入十次
for (int n = 1;n <= 100;n++){
//开启分段计时
// long bTime = System.currentTimeMillis();
Random rand = new Random();
for (int i = 1;i <=10000;i++){
int a = rand.nextInt(100);
ps.setString(1, RandomValue.getSno());
ps.setString(2, RandomValue.getChineseName());
ps.setString(3, RandomValue.getSex());
ps.setString(4, RandomValue.getTel());
ps.setString(5, RandomValue.getRoad());
ps.setInt(6, a);
ps.setString(7, RandomValue.getCid());
ps.addBatch();
}
ps.executeBatch();
conn.commit();
}
Long endTime = System.currentTimeMillis();
long time = endTime-beginTime;
System.out.println("插入用时"+time);
}catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
insertStu();
}
}