1、DBUtils对jdbc的操作,进行了封装.简化了jdbc的操作
2、在实现的MyDBUtils中使用了C3P0数据库,C3P0的配置文件如下
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config name="mysql">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">
jdbc:mysql://localhost:3306/testdatabase
</property>
<property name="user">root</property>
<property name="password">1234</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
</c3p0-config>
3、实体类User的代码
package com.jdbc.entity;
public class User {
private int id;
private String uname;
private int age;
public User() {
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "User [id=" + id + ", uname=" + uname + ", age=" + age + "]";
}
}
4、简化的MyDBUtils的代码如下
package com.jdbc.dbutils;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class MyDBUtils {
/**
* 使用C3P0数据库连接池,用于获取数据库的连接,提高效率
*/
private static ComboPooledDataSource dataSource = null;
/**
* 在第一次加载GenericityJDBCUtils类的时候,利用C3P0,初始化dataSource
* 然后利用dataSource,可以方便的去获取数据库的连接
*/
static {
try {
dataSource = new ComboPooledDataSource("mysql");
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* 返回关联数据库连接池的dataSource
*/
public static DataSource getDataSource() {
return dataSource;
}
/**
* 获取一个数据库的连接
*
* @return
*/
public static Connection getConnection() {
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
5、利用UserDao去操作数据库的代码,实现如下
package com.jdbc.dbutils.userdao;
import java.util.ArrayList;
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 com.jdbc.dbutils.MyDBUtils;
import com.jdbc.entity.User;
/**
* 用于操作user表
*
*/
public class UserDao {
/**
* 根据用户id来查询用户
*
* @param id
* @return
*/
public User findById(int id) {
QueryRunner qr = null;
User user = null;
try {
qr = new QueryRunner(MyDBUtils.getDataSource());
String sql = "select * from user where id = ?";
user = (User) qr.query(sql, id, new BeanHandler(User.class));
return user;
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* 查询所有的用户
*
* @return
*/
public List<User> findAll() {
QueryRunner qr = null;
List<User> users = new ArrayList<User>();
try {
qr = new QueryRunner(MyDBUtils.getDataSource());
String sql = "select * from user ";
users = (List<User>) qr.query(sql, new BeanListHandler(User.class));
return users;
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* 根据分页条件来查询用户
*
* @param start
* @param size
* @return
*/
public List<User> findFenYe(int start, int size) {
QueryRunner qr = null;
List<User> users = new ArrayList<User>();
try {
qr = new QueryRunner(MyDBUtils.getDataSource());
String sql = "select * from user limit ? , ? ";
users = (List<User>) qr.query(sql, new Object[] { start, size },
new BeanListHandler(User.class));
return users;
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* 增加一个用户到数据库
*
* @param user
*/
public void add(User user) {
QueryRunner qr = null;
try {
qr = new QueryRunner(MyDBUtils.getDataSource());
String sql = "insert into user( uname , age ) values( ? , ? ) ";
qr.update(sql, new Object[] { user.getUname(), user.getAge() });
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* 根据sql和paras 更新数据库
*
* @param sql
* @param paras
*/
public void update(String sql, Object[] paras) {
QueryRunner qr = null;
try {
qr = new QueryRunner(MyDBUtils.getDataSource());
qr.update(sql, paras);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
6、测试代码如下
package com.jdbc.dbutils.userdao;
import java.util.List;
import org.junit.Test;
import com.jdbc.entity.User;
public class UserDaoTest {
@Test
public void testFindById() {
UserDao userDao = new UserDao();
User user = userDao.findById(1);
System.out.println(user);
}
@Test
public void testFindAll() {
UserDao userDao = new UserDao();
List<User> users = userDao.findAll();
for (User user : users)
System.out.println(user);
}
@Test
public void testFindFenYe() {
UserDao userDao = new UserDao();
List<User> users = userDao.findFenYe(0, 2);
for (User user : users)
System.out.println(user);
}
@Test
public void testAdd() {
UserDao userDao = new UserDao();
User user = new User();
user.setAge(18);
user.setUname("哈哈");
userDao.add(user);
}
@Test
public void testDelete() {
UserDao userDao = new UserDao();
String sql = "delete from user where id = ? ";
Object[] paras = { 4 };
userDao.update(sql, paras);
}
@Test
public void testUpdate() {
UserDao userDao = new UserDao();
String sql = "update user set uname = ? where id = ? ";
Object[] paras = { "haha", 5 };
userDao.update(sql, paras);
}
}