create table course (

id int auto_increment key ,

name varchar (20),

category varchar (20),

desp varchar (30),

createTime DATETIME

); 

insert into course ( name, category, desp, createTime) values('Java零基础','Java','Java基础语法','2020-03-10 00:00:00');

insert into course (name, category, desp, createTime) values('Java Web','Java','Jsp和Servlet','2020-03-10 00:00:00');

insert into course (name, category, desp, createTime) values('前端小白','前端','HTML/CSS/JS','2020-03-10 00:00:00');

package com.imooc.jdbc.utils;

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Properties;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JDBCUtils2 {

public static final ComboPooledDataSource datasourse = new ComboPooledDataSource();

/**

* 建立连接

* @return

* @throws Exception

*/

public static Connection getConnection() throws Exception {

Connection conn = datasourse.getConnection();

return conn;

}

/**

* 释放资源

* @param stmt

* @param conn

*/

public static void release(Statement stmt,Connection conn) {

if(stmt != null) {

try {

stmt.close();

}catch(SQLException e) {

e.printStackTrace();

}

stmt = null;

}

if(conn != null) {

try {

conn.close();

}catch(SQLException e) {

e.printStackTrace();

}

conn = null;

}

}

/**

* 释放资源

* @param rs

* @param stmt

* @param conn

*/

public static void release(ResultSet rs,Statement stmt,Connection conn) {

if(rs != null) {

try {

rs.close();

}catch(SQLException e) {

e.printStackTrace();

}

rs = null;

}

if(stmt != null) {

try {

stmt.close();

}catch(SQLException e) {

e.printStackTrace();

}

stmt = null;

}

if(conn != null) {

try {

conn.close();

}catch(SQLException e) {

e.printStackTrace();

}

conn = null;

}

}

}

package com.imooc.jdbc.utils;

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Properties;

public class JDBCUtils {

private static final String driverClass;

private static final String url;

private static final String username;

private static final String password;

static {

Properties props = new Properties();

InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");

try {

props.load(is);

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

driverClass = props.getProperty("driverClass");

url = props.getProperty("url");

username = props.getProperty("username");

password = props.getProperty("password");

}

/**

* 注册驱动

* @throws ClassNotFoundException

*/

public static void loadDriver() throws ClassNotFoundException {

Class.forName(driverClass);

}

/**

* 建立连接

* @return

* @throws Exception

*/

public static Connection getConnection() throws Exception {

loadDriver();

Connection conn = DriverManager.getConnection(url, username, password);

return conn;

}

/**

* 释放资源

* @param stmt

* @param conn

*/

public static void release(Statement stmt,Connection conn) {

if(stmt != null) {

try {

stmt.close();

}catch(SQLException e) {

e.printStackTrace();

}

stmt = null;

}

if(conn != null) {

try {

conn.close();

}catch(SQLException e) {

e.printStackTrace();

}

conn = null;

}

}

/**

* 释放资源

* @param rs

* @param stmt

* @param conn

*/

public static void release(ResultSet rs,Statement stmt,Connection conn) {

if(rs != null) {

try {

rs.close();

}catch(SQLException e) {

e.printStackTrace();

}

rs = null;

}

if(stmt != null) {

try {

stmt.close();

}catch(SQLException e) {

e.printStackTrace();

}

stmt = null;

}

if(conn != null) {

try {

conn.close();

}catch(SQLException e) {

e.printStackTrace();

}

conn = null;

}

}

}

package com.imooc.jdbc.demo3;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import org.junit.Test;

import com.imooc.jdbc.utils.JDBCUtils;

import com.imooc.jdbc.utils.JDBCUtils2;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DataSourceDemo1 {

@Test

/**

* 使用配置文件的方式

*/

public void demo2() {

Connection conn = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

try {

//创建连接池：

//ComboPooledDataSource dataSourse = new ComboPooledDataSource();

//获得连接：

//conn = dataSourse.getConnection();

conn = JDBCUtils2.getConnection();

//编写sql：

String sql="select * from course";

//预编译SQL：

pstmt = conn.prepareStatement(sql);

//设置参数

//执行SQL：

rs = pstmt.executeQuery();

while(rs.next()) {

System.out.println(rs.getString("NAME")+"         "+rs.getString("category")+"         "+rs.getString("desp")+"         "+rs.getDate("createTime"));

}

}catch(Exception e) {

e.printStackTrace();

}finally {

JDBCUtils2.release(rs, pstmt, conn);

}

}

@Test

/**

* 手动设置连接池

*/

public void demo1() {

//获得连接：

Connection conn = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

try {

//创建连接池：

ComboPooledDataSource dataSourse = new ComboPooledDataSource();

//设置连接池相关参数

dataSourse.setDriverClass("com.mysql.jdbc.Driver");

dataSourse.setJdbcUrl("jdbc:mysql://localhost:3306/jdbctest");

dataSourse.setUser("root");

dataSourse.setPassword("123");

dataSourse.setMaxPoolSize(20);

dataSourse.setInitialPoolSize(3);

//获得连接：

conn = dataSourse.getConnection();

//编写sql：

String sql="select * from course";

//预编译SQL：

pstmt = conn.prepareStatement(sql);

//设置参数

//执行SQL：

rs = pstmt.executeQuery();

while(rs.next()) {

System.out.println(rs.getString("NAME")+"         "+rs.getString("category")+"         "+rs.getString("desp")+"         "+rs.getDate("createTime"));

}

}catch(Exception e) {

e.printStackTrace();

}finally {

JDBCUtils.release(rs, pstmt, conn);

}

}

}