文章目录

Jar包下载

蓝奏盘下载 | 闪电盘下载

sqlyog新建库

/*在MySQL中创建一个名称为chapter01的数据库,
  然后在该数据库中创建一个users表,SQL语句如下所示*/
CREATE DATABASE jdbc;
USE jdbc;
CREATE TABLE users(
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(40),
        password VARCHAR(40),
        email VARCHAR(60),
        birthday DATE 
)CHARACTER SET utf8 COLLATE utf8_general_ci;
 
/*数据库和表创建成功后,再向users表中插入3条数据,SQL语句如下所示*/
INSERT INTO users(NAME,PASSWORD,email,birthday) 
VALUES('zs','123456','zs@sina.com','1980-12-04');
INSERT INTO users(NAME,PASSWORD,email,birthday) 
VALUES('lisi','123456','lisi@sina.com','1981-12-04');
INSERT INTO users(NAME,PASSWORD,email,birthday) 
VALUES('wangwu','123456','wangwu@sina.com','1979-12-04');
/*为了查看数据是否添加成功,使用SELECT语句查询users表,SQL语句如下所示*/
package utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class C3P0Utils {
//    public static Connection getConnection() throws SQLException {
//        DataSource ds=new ComboPooledDataSource();//创建数据源(数据库连接池)
//        Connection connection = ds.getConnection();//从中取出一个空闲连接
//        return connection;//返回连接
//    }
//
//
//    //无需释放连接

//    为QueryRunner提供一个数据源对象
    static DataSource ds=null;
    static {
        ds=new ComboPooledDataSource();
    }
    public static DataSource getDataSource(){
        return ds;
    }
}

测试类

package Test;

import Bean.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import utils.C3P0Utils;

import javax.jws.soap.SOAPBinding;
import java.sql.SQLException;
import java.util.List;

public class C3P0Test1 {
    public static void main(String[] args) {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        //单个查询
//        //执行SQL语句
//        String sql="select * from users where id=?";
//        User user= null;
//        try {
//            user = runner.query(sql,new BeanHandler<>(User.class),1);
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        System.out.println(user.getUsername()+":"+user.getEmail());
//    }
        //查询所有
        String sql = "select * from users";
        List<User> list = null;
        try {
            list = runner.query(sql, new BeanListHandler<>(User.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // 遍历集合
        for (User user : list) {
            System.out.println(user.getName() + ":" + user.getEmail());
        }
    }
}