用数据库连接池C3P0来获得连接和释放资源
用DBUtils工具里的核心类库中的QueryRunner类和ResultSetHandler类来简化数据的增删改查操作
首先导入jar包
导入以下四个jar包,并配置环境
1.mysql-connector-java-8.0.22.jar是数据库驱动包
2.c3p0-0.9.2.1.jar包是用来连接、销毁数据库用的
3.mchange-commons-java-0.2.3.4.jar这是c3p0数据库连接池的辅助包,如果没有这个包系统启动时会报classnotfoundexception
4.commons-dbutils-1.7.jar是用于操作数据库的工具包
一、动手创建数据库
这里不在交代数据库怎么创建的,数据库的结构如下图
二、创建一个User类
User类用来封装用户
package com.abc.jdbc;
import java.sql.Date;
public class User {
private int id;
private String name;
private String password;
private String email;
private Date birthday;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public User(int id, String name, String password, String email, Date birthday) {
this.id = id;
this.name = name;
this.password = password;
this.email = email;
this.birthday = birthday;
}
public User() {
}
@Override
public String toString() {
return "User{ id="+id+", name="+name+", password="+password+", email="+
email+", birthday="+birthday+" }";
}
}
三、创建C3p0Utils工具类
先在src目录下创建一个c3p0-config.xml配置文件,其内容为
要修改里面的password里的内容,就是要把密码修改成自己数据库账户的密码
<?xml version="1.0" encoding="utf-8" ?>
<c3p0-config>
<!-- 默认的配置 -->
<default-config>
<!-- 连接数据库的4项基本参数 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc?serverTimezone=GMT%2B8</property>
<property name="user">root</property>
<property name="password">123456</property>
<!-- 如果池中数据连接不够时一次增长多少个 -->
<property name="acquireIncrement">5</property>
<!-- 初始化连接数 -->
<property name="initialPoolSize">5</property>
<!-- 最小连接数 -->
<property name="minPoolSize">5</property>
<!-- 最大连接数 -->
<property name="maxPoolSize">40</property>
<!-- JDBC的标准参数,用以控制数据源内加载的PreparedStatements数量 -->
<property name="maxStatements">200</property>
<!--获取数据库连接的最大等待时间,单位是毫秒,如果超过了这个时间还未获取到,就抛出异常-->
<property name="checkoutTimeout">30000</property>
<!--数据库连接的最大空闲时间,单位是秒,如果超过了这个时间,就会丢弃-->
<property name="maxIdleTime">30</property>
</default-config>
<named-config name="itcast">
<!-- 连接数据库的4项基本参数 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc?serverTimezone=GMT%2B8</property>
<property name="user">root</property>
<property name="password">123456</property>
<!-- 如果池中数据连接不够时一次增长多少个 -->
<property name="acquireIncrement">5</property>
<!-- 初始化连接数 -->
<property name="initialPoolSize">5</property>
<!-- 最小连接数 -->
<property name="minPoolSize">5</property>
<!-- 最大连接数 -->
<property name="maxPoolSize">40</property>
<!-- JDBC的标准参数,用以控制数据源内加载的PreparedStatements数量 -->
<property name="maxStatements">200</property>
</named-config>
</c3p0-config>
然后创建C3p0Utils工具类
package com.abc.dbutils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
public class C3p0Utils {
public static DataSource ds;
static {
ds = new ComboPooledDataSource();
}
public static DataSource getDataSource() {
return ds;
}
}
四、创建UsesDao类
UserDao类是用来完成增删改查操作的
package com.abc.dbutils;
import com.abc.jdbc.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import javax.management.Query;
import java.sql.SQLException;
import java.util.List;
public class UserDao {
// 插入操作
public boolean insert(User user) throws SQLException {
QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
String sql = "insert into users(name,password,email,birthday) " +
"values(?,?,?,?)";
int num = runner.update(sql, new Object[]{user.getName(), user.getPassword(),
user.getEmail(), user.getBirthday()});
if(num>0){
return true;
}else {
return false;
}
}
// 修改操作
public boolean update(User user) throws SQLException {
QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
String sql = "update users set name=?,password=?,email=?,birthday=? where id = ?";
int num = runner.update(sql, new Object[]{user.getName(), user.getPassword(),
user.getEmail(), user.getBirthday(),user.getId()});
if(num>0){
return true;
}else {
return false;
}
}
// 删除操作
public boolean delete(int id) throws SQLException {
QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
String sql = "delete from users where id=?";
int num = runner.update(sql, id);
if(num>0){
return true;
}else {
return false;
}
}
// 查询全部操作
public List selectAll() throws SQLException {
QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
String sql = "select * from users";
List<User> list = runner.query(sql, new BeanListHandler<User>(User.class));
return list;
}
// 根据id查询操作
public User selectById(int id) throws SQLException{
QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
String sql = "select * from users where id=?";
User user = runner.query(sql, new BeanHandler<User>(User.class),id);
return user;
}
}
五、创建DbutilsTest类
测试增删改查操作是否成功
注意:因为User类里面有带参的构造方法,所以创建用户的时候我直接使用了这个带参的构造方法
package com.abc.dbutils;
import com.abc.jdbc.User;
import java.sql.Date;
import java.sql.SQLException;
import java.util.List;
public class dbutilsTest {
public static void main(String[] args) throws SQLException {
// 测试插入操作
UserDao ud = new UserDao();
// boolean b = ud.insert(new User(4, "ww", "123", "ww@qq.com",
// Date.valueOf("1998-02-03")));
// System.out.println(b);
// 测试修改操作
// boolean b = ud.update(new User(5, "zl", "123", "zl@qq.com",
// Date.valueOf("1999-02-03")));
// System.out.println(b);
// 测试删除操作
// boolean b = ud.delete(5);
// System.out.println(b);
// 测试查询全部操作
// List<User> list = ud.selectAll();
// for(User user:list){
// System.out.println(user);
// }
// 测试根据id查询操作
// User user = ud.selectById(2);
// System.out.println(user);
}
}
总结就到这里了