MySQL-DBCP
使用DBCP连接MySQL数据库
1.导入maven坐标
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.1.1</version>
</dependency>
2.建立实体类
建立pojo/userinfo实体类,与数据库中userinfo表对应
package pojo;
public class userinfo {
private Integer id;
private String college;
private String name;
private Integer picId;
public userinfo(Integer id, String college, String name, Integer picId) {
this.id = id;
this.college = college;
this.name = name;
this.picId = picId;
}
public userinfo() {
}
@Override
public String toString() {
return "userinfo{" +
"id=" + id +
", college='" + college + '\'' +
", name='" + name + '\'' +
", picId=" + picId +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getCollege() {
return college;
}
public void setCollege(String college) {
this.college = college;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getPicId() {
return picId;
}
public void setPicId(Integer picId) {
this.picId = picId;
}
}
3.建立数据库配置文件dbcp.properties
在resource文件夹下建立dbcp.properties配置文件
driverClassName = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://127.0.0.1:3306/zzu?serverTimeZone=UTC&characterEncoding=utf-8
username = root
password = XXXXXX
#初试连接数
initialSize=30
#最大活跃数
maxTotal=30
#最大idle数
maxIdle=10
#最小idle数
minIdle=5
#最长等待时间(毫秒)
maxWaitMillis=1000
#程序中的连接不使用后是否被连接池回收(该版本要使用removeAbandonedOnMaintenance和removeAbandonedOnBorrow)
#removeAbandoned=true
removeAbandonedOnMaintenance=true
removeAbandonedOnBorrow=true
#连接在所指定的秒数内未使用才会被删除(秒)(为配合测试程序才配置为1秒)
removeAbandonedTimeout=1
4.建立utils工具类
建立utils/dbcputils工具类
package util;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
public class DBCPUtils {
private static Properties props = new Properties();
static{
try{
props.load(DBCPUtils.class.getClassLoader().
getResourceAsStream("dbcp.properties"));
}catch(Exception e){
System.out.println("读取配置文件出错");
}
}
public static DataSource getDataSource() {
try{
return BasicDataSourceFactory.createDataSource(props);
}catch(Exception ex){
System.out.println("获取数据源出错");
return null;
}
}
public static Connection getConnection() {
try {
return getDataSource().getConnection();
} catch (SQLException e) {
System.out.println("获取连接失败");
return null;
}
}
public static void close(Connection conn, ResultSet rs, PreparedStatement prep){
if(rs != null){
try{
rs.close();
rs = null;
}catch(Exception e){
System.out.println("关闭失败");
}
}
if(prep != null){
try{
prep.close();
prep = null;
}catch(Exception e){
System.out.println("关闭失败");
}
}
if(conn != null){
try{
conn.close();
conn = null;
}catch(Exception e){
System.out.println("关闭失败");
}
}
}
}
5.测试
建立测试类,获取数据库连接,进行数据获取测试
import util.DBCPUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class DBCPTest {
public static void main(String[] args) {
Connection conn = null;
try {
conn = DBCPUtils.getConnection();
PreparedStatement prepStmt = conn.prepareStatement("select * from userinfo");
ResultSet rs = prepStmt.executeQuery();
while (rs.next()) {
int id = rs.getInt("id");
String college = rs.getString("college");
String name = rs.getString("name");
int picId = rs.getInt("pic_id");
System.out.println(id + " " + college + " " + name + " " + picId);
}
}catch(Exception e){
System.out.println("出错了");
}
}
}
通过以上步骤,可以利用dbcp来进行数据库数据的获取