通过dbutil操作数据库

dbutil操作数据库工具类

步骤

1. 导入jar包

c3p0-0.9.1.2.jar         commons-dbutils-1.6.jar         mysql-connector-java-5.1.12-bin.jar

1. 把c3p0配置文件放到src目录下（src下一级目录，否则出错），

c3p0-config.xml  （连接数据库的驱动，url,名字，密码，，，，等）

1. 创建连接池

ComboPooledDataSource  ds = new ComboPooledDataSource();

1. 拿到执行sql的关键对象QueryRunner

QueryRunner  qr = new QueryRunner(ds);

1. 写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";

1. 将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;
}
}