概述
为什么要使用数据库连接池?数据库频繁的连接和关闭连接会占用不少资源,为了减少对资源的消耗,采用数据库连接池,可以重复的使用一个连接,而不是再建立新的连接。
以DBCP连接池为例。
准备
这四个是要导入项目的jar包,画红框的是DBCP连接池技术要用的jar包
DBCP下载链接
POOL下载链接
代码
- 测试类
public class Test {
public static void main(String[] args) {
insert(); //插入的操作
query(); //查询的操作
}
private static void insert() {
try {
QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
String sql = "insert into user(username,userpassword) values(?,?)";
Object[] params = {"郭小四", "123456"};
int line = qr.update(sql, params);
System.out.println("line="+line);
} catch (SQLException e) {
System.out.println("插入失败");
}
}
private static void query() {
try {
QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
String sql = "select * from user";
Object[] params = {};
List<User> user = qr.query(sql, new BeanListHandler<User>(User.class), params);
for (User u : user) {
System.out.println(u.toString());
}
} catch (Exception e) {
System.out.println("查询失败");
}
}
}
- 数据库连接工具类
public class JDBCUtils {
private static String DRIVER = "com.mysql.jdbc.Driver";
private static String URL = "jdbc:mysql://localhost:3306/mydb";
private static String USERNAME = "root";
private static String PASSWORD = "root";
//创建一个连接池
public static BasicDataSource dataSource = new BasicDataSource();
static {
dataSource.setDriverClassName(DRIVER);
dataSource.setUrl(URL);
dataSource.setUsername(USERNAME);
dataSource.setPassword(PASSWORD);
}
public static DataSource getDataSource() {
return dataSource;
}
}
- 封装数据类
查询操作获取的数据,用这个类的对象存放。这个类定义的时候要注意成员变量的名称要和数据库的字段名一致,类型也要一致,并且要提供打印的方法。
public class User {
private int id;
private String username;
private String userpassword;
public User() {
super();
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getuserpassword() {
return userpassword;
}
public void setuserpassword(String userpassword) {
this.userpassword = userpassword;
}
public String toString() {
return "User [id="+id+", username=" + username + ", userpassword=" + userpassword + "]";
}
}