通过dbutil操作数据库
dbutil操作数据库工具类
步骤
- 导入jar包
c3p0-0.9.1.2.jar commons-dbutils-1.6.jar mysql-connector-java-5.1.12-bin.jar
- 把c3p0配置文件放到src目录下（src下一级目录，否则出错），
c3p0-config.xml （连接数据库的驱动，url,名字，密码，，，，等）
- 创建连接池
ComboPooledDataSource ds = new ComboPooledDataSource();
- 拿到执行sql的关键对象QueryRunner
QueryRunner qr = new QueryRunner(ds);
- 写sql语句
增：String sql=” insert into user(name,age) values(?,?)”;
删：String sql="delete from user where id="+u.getId();
改：String sql="update user set name=?,age=? where id=?";
查：
查一个：String sql="select * from user where id=?";
查一批：String sql="select * from user";
- 将sql语句放入Query对象，调用相应方法增删改查
不要疑惑，增加、修改、删除都可以调用update方法，内部会通过判断有无id而具体实行增加还是修改，只有sql时删除。
增：int num = qr.update(sql, u.getName(),u.getAge());
改：int num = qr.update(sql, u.getName(),u.getAge()，u.getId());
删：int num=qr.update(sql);
查：
查一个
User user = qr.query(sql, new BeanHandler<User>(User.class), id);
查一批
List<User> users = qr.query(sql, new BeanListHandler<User>(User.class));
注：增删改都可用Query对象的update方法，查询的同时将查到的数据封装成对应的对象或者集合。
示例代码：
c3p0-config.xml
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost/test</property>
<property name="user">root</property>
<property name="password"></property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">10</property>
</default-config>
</c3p0-config>
DbUtil.java
package util;
import org.apache.commons.dbutils.QueryRunner;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class DbUtil {
//构造数据源
private static ComboPooledDataSource ds=new ComboPooledDataSource();
public static QueryRunner getRuertyRunner(){
//拿到操作数据库的关键对象QueryRunner,把数据源放进去
QueryRunner qr = new QueryRunner(ds);
return qr;
}
}
User.java
package entity;
public class User {
private int id;
private int age;
private String name;
public User() {
super();
}
public User(int id, int age, String name) {
super();
this.id = id;
this.age = age;
this.name = name;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
UserDao.java
package dao;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import util.DbUtil;
import entity.User;
public class UserDao {
/*public int add(User u){
Connection conn = JdbcUtil.getConnection();
String sql="insert into user(name,age) values(?,?)";
PreparedStatement ps=null;
int num=0;
try {
ps = conn.prepareStatement(sql);
ps.setObject(1, u.getName());
ps.setObject(2, u.getAge());
num=ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return num;
}*/
//dbutil
QueryRunner qr = DbUtil.getRuertyRunner();
public int add(User u){
String sql="insert into user(name,age) values(?,?)";
int num=0;
try {
num = qr.update(sql, u.getName(),u.getAge());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return num;
}
public int delete(User u){
String sql="delete from user where id="+u.getId();
int num=0;
try {
num = qr.update(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return num;
}
public int update(User u){
String sql="update user set name=?,age=? where id=?";
int num =0;
try {
num= qr.update(sql, u.getName(),u.getAge(),u.getId());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return num;
}
public User selectOne(int id){
String sql="select * from user where id=?";
User user=null;
try {
user = qr.query(sql, new BeanHandler<User>(User.class), id);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return user;
}
public List<User> selectAll(){
String sql="select * from user";
List<User> users=null;
try {
users = qr.query(sql, new BeanListHandler<User>(User.class));
} catch (SQLException e) {
e.printStackTrace();
}
return users;
}
}
