数据库 JDBC使用 C3P0使用

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class JDBCUtil {

	private static String Dirver;
	private static String user;
	private static String password;
	private static String url;

	static {
		Properties prop = new Properties();

		try {
			prop.load(JDBCUtil.class.getClassLoader().getResourceAsStream("db.properties"));
			Dirver = prop.getProperty("driverClass");
			user = prop.getProperty("user");
			password = prop.getProperty("password");
			url = prop.getProperty("url");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static Connection getConnection() throws SQLException {

		try {
			Class.forName(Dirver);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return DriverManager.getConnection(url, user, password);

	}

	/**
	 * 
	 * @param rs
	 * @param pst
	 * @param conn
	 */

	public static void Release(ResultSet rs, PreparedStatement pst, Connection conn) {

		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (pst != null) {
			try {
				pst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}


}

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JDBCXUtil {
	// 确定所有dao ProductDao OrderDao UserDao 公用同一个连接池对象
	private static ComboPooledDataSource cpds = new ComboPooledDataSource();

	public static ComboPooledDataSource getDataSource() {
		return cpds;
	}

	// 当上述代码一旦构造对象 自动 加载 scr/c3p0-config.xml 获取数据库连接信息 ...
	// 提供给所有dao 获取Connection对象的方法!
	public static Connection getConnection() {
		try {

			return cpds.getConnection();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException("连接池获取失败...");
		}
	}

	// 方便dao调用连接池获取的连接 放回到连接池对象
	public static void close(Connection con) {
		try {
			if (con != null) {
				con.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void close(Connection con, Statement st) {
		if (st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		close(con);
	}

	public static void close(Connection con, Statement st, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		close(con, st);
	}
}

driverClass=com.mysql.jdbc.Driver
user=root
password=samsung
url=jdbc:mysql://localhost:3306/day01

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import cn.itcast.jdbc.JDBCUtil;
import cn.itcast.test_c3p0.JDBCXUtil;
import entity.employee;

public class TestJDBCUtil {

	
	@Test
	public void test3() {
		Connection conn = null;
		PreparedStatement pst = null;

		try {
			conn = JDBCXUtil.getConnection();
			// crud
			// employee_id=13050601, name=xiaoli, salary=10000, age=28,
			// job=engineer, passwd=123123, sex=male
			String sql = "insert into employee(name,salary) values(?,?)";
			pst = conn.prepareStatement(sql);
			pst.setString(1, "xiaogang");
			pst.setInt(2, 5000);
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 连接池获取connection 放回到连接池对象
			JDBCXUtil.close(conn, pst);
		}

	}

	@Test
	public void test() {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			conn = JDBCUtil.getConnection();
			String sql = "select * from employee where name = ?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, "xiaoli");
			rs = stmt.executeQuery();

			employee e = new employee();
			if (rs.next()) {
				e.setEmployee_id(rs.getInt(1));
				e.setName(rs.getString("name"));
				e.setSalary(rs.getInt(3));
				e.setAge(rs.getInt(4));
				e.setJob(rs.getString("job"));
				e.setPasswd(rs.getString("passwd"));
				e.setSex(rs.getString("sex"));
			}
			System.out.println(e);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.Release(rs, stmt, conn);
		}
	}

	@Test
	public void TestC3P0() throws PropertyVetoException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			ComboPooledDataSource cpds = new ComboPooledDataSource();
			cpds.setDriverClass("com.mysql.jdbc.Driver");
			cpds.setJdbcUrl("jdbc:mysql://localhost:3306/day01");
			cpds.setUser("root");
			cpds.setPassword("samsung");

			conn = cpds.getConnection();
			String sql = "select * from employee where name = ?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, "xiaoli");
			rs = stmt.executeQuery();

			employee e = new employee();
			if (rs.next()) {
				e.setEmployee_id(rs.getInt(1));
				e.setName(rs.getString("name"));
				e.setSalary(rs.getInt(3));
				e.setAge(rs.getInt(4));
				e.setJob(rs.getString("job"));
				e.setPasswd(rs.getString("passwd"));
				e.setSex(rs.getString("sex"));
			}
			System.out.println(e);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.Release(rs, stmt, conn);
		}
	}

}