使用DBUtiles对数据库进行增删改查

这里需要使用的文件

• C3P0连接配置文件
• C3P0连接类
• DBUtils操作工具类
• 测试类

C3P0连接配置文件

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">
             jdbc:mysql://localhost:3306/jdbc
         </property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="checkoutTimeout">30000</property>
        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">10</property>
        <property name="maxStatements">200</property>
    </default-config> 
    <!--  -->
    <named-config name="itcast">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">
               jdbc:mysql://localhost:3306/jdbc
        </property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="initialPoolSize">5</property>
        <property name="maxPoolSize">15</property>
    </named-config>
</c3p0-config>

C3P0连接类

package p02;

import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0_Utils {
    private static DataSource ds = null;
    static{
        //使用c3p0的默认配置
        ds = new ComboPooledDataSource();
    }
    //提供数据源对象
    public static DataSource getDataSource() {
        return ds ;
    }
    //提供连接对象
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
}

DBUtils操作工具类

package p02;

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 p01.User;

public class DBUtilsDao {
    public Boolean insert(User user) throws SQLException {
        //创建QueryRunner
        QueryRunner runner = new QueryRunner(C3P0_Utils.getDataSource());
        //SQL语句
        String sql = "insert into users(id,name,password) value (?,?,?)";
        //执行sql
        int num = runner.update(sql,user.getId(),user.getName(),user.getPassword());
        if(num > 0) {
            return true ;
        }
        return false;
    }
    public static boolean update(User user) throws SQLException {
        //创建QueryRunner对象
        QueryRunner runner = new QueryRunner(C3P0_Utils.getDataSource());
        //SQL语句
        String sql = "update users set name=?,password=? where id = ?";
        //执行SQL语句
        int num = runner.update(sql,user.getName(),user.getPassword(),user.getId());
        if(num > 0) {
            return true;
        }else {
            return false;
        }
    }
    public static boolean delete(int id) throws SQLException {
        //创建QueryRunner对象
        QueryRunner runner = new QueryRunner(C3P0_Utils.getDataSource());
        //SQL语句
        String sql = "delete from users where id = ?";
        //执行sql
        int num = runner.update(sql,id);
        if(num > 0 ) {
            return true;
        }else {
            return false;
        }
    }
    public static List QueryAll() throws SQLException{
        //创建QueryRunner
        QueryRunner runner = new QueryRunner(C3P0_Utils.getDataSource());
        //SQL
        String sql = "select * from users";
        List list = runner.query(sql, new BeanListHandler<User>(User.class));
        return list;
    }
    public static User QueryID(int id ) throws SQLException{
        //创建QueryRunner
        QueryRunner runner = new QueryRunner(C3P0_Utils.getDataSource());
        //SQL
        String sql = "select * from user where id = ?";
        User user = runner.query(sql, new BeanHandler<User>(User.class),id);

        return user;
    }
}

测试类

package p02;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import com.mysql.fabric.xmlrpc.base.Array;

import p01.User;

public class DBUtilsDaoTest {

    private static DBUtilsDao dao = new DBUtilsDao();
    public static void TestInsert() throws SQLException {
        User user = new User();
        //user.setId(3);
        user.setId(9);
        user.setName("zhangsan666");
        user.setPassword("123");
        boolean b = dao.insert(user);
        System.out.println(b);

    }
    public static void TestUpdate() throws SQLException {
        User user = new User();
        user.setName("123666");
        user.setPassword("12334546xiugai");
        user.setId(1);
        Boolean b = dao.update(user);
        System.out.println(b);
    }
    public static void TestDelete() throws SQLException {
        Boolean b = dao.delete(1);
        System.out.println(b);
    }
    public static void TestQueryAll() throws SQLException {
        List list = dao.QueryAll();
            System.out.println(list.size());
    }

    public static void main(String[] args) throws SQLException {
        //TestInsert();
        //TestUpdate();
        //TestDelete();
        //TestQueryAll();
    }

}