JDBC终章- 使用 DBUtils实现增删查改- C3P0Utils数据源/QueryRunner runner连接数据源并执行sql

<?xml version="1.0" encoding="UTF-8"?><c3p0-config> <named-config name="szs"> <property name="user">root</property>
<property name="password">123456</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc</property>
<property name="driverClass">com.mysql.jdbc.Driver</property> <property name="acquireIncrement">2</property>
<property name="initialPoolSize">5</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">10</property> <property name="maxStatements">20</property>
<property name="maxStatementsPerConnection">5</property> </named-config>
</c3p0-config>

package day_23;import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;import javax.sql.DataSource;
import java.sql.SQLException;public class C3P0Utils {
private static DataSource ds;
static {
ds=new ComboPooledDataSource("szs"); //这里因为配置文件中没有default,故需要自定义的
}
public static DataSource getDataSource() throws SQLException {
return ds;
}
}

package day_23;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;import java.sql.SQLException;
import java.util.List;//创建DBUtilsDao类
public class DBUtilsDao {
//查询所有,返回List集合
public List findAll() throws SQLException{
//创建QueryRunner 对象
QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource()) ;
//写SQL语句
String sql="select * from user";
//调用方法
List list=(List)runner.query(sql,new BeanListHandler(User.class));
return list;
}
//查询单个对象的id,返回List集合中的第一个对象
public User find(int id) throws SQLException{
//创建QueryRunner 对象
QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource()) ;
//写SQL语句
String sql="select * from user where id= ?";
//调用方法
List list=(List)runner.query(sql,new BeanListHandler(User.class),id);
return (User)list.get(0);
}
//添加用户的操作
public Boolean insert(User user) throws SQLException{
//创建QueryRunner 对象
QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource()) ;
//写SQL语句
String sql="insert into user (name,password) values (?,?)";
//调用方法
int num=runner.update(sql,new Object[]{user.getName(),user.getPassword()});
if(num>0){
System.out.println("添加用户成功!");
return true;
}
else
return false;
}
//修改用户的操作
public Boolean update(User user) throws SQLException{
//创建QueryRunner 对象
QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource()) ;
//写SQL语句
String sql="update user set name=?,password=? where id=?";
//调用方法
int num=runner.update(sql,new Object[]{user.getName(),user.getPassword(),user.getId()});
if(num>0){
System.out.println("更新用户成功!");
find(user.getId()).toString();
return true;
}
else
return false;
}
//删除用户的操作 ,根据用户的id
public Boolean delete(int id) throws SQLException{
//创建QueryRunner 对象
QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource()) ;
//写SQL语句
String sql="delete from user where id =?";
//调用方法
int num=runner.update(sql,id);
if(num>0){
System.out.println("删除用户成功!");
return true;
}
else
return false;
}}

package day_23;import java.sql.SQLException;
import java.util.List;//测试增(insert)删查改,四个功能
public class DBUtilsDaoTest {
private static DBUtilsDao dao=new DBUtilsDao(); public static void testInsert() throws SQLException{
User user=new User();
user.setId(1);
user.setName("赵六");
user.setPassword("666666");
boolean b=dao.insert(user);
System.out.println(b);
}
public static void testDelete() throws SQLException{
System.out.println(dao.delete(1));
}
public static void testfind() throws SQLException {
System.out.println(dao.find(2));
}
public static void testfindAll() throws SQLException{
List<User> users= dao.findAll();
for(int i=0;i<users.size();i++)
System.out.println(users.get(i));
}
public static void testUpdate() throws SQLException{
User user=new User();
user.setId(5);
user.setName("赵六66");
user.setPassword("666666");
boolean b=dao.insert(user);
System.out.println(b);
}
public static void main(String[] args) throws SQLException {
testInsert();
testDelete();
testUpdate();
testfind();
testfindAll(); }
}