1.不使用连接池方式(Jdbc)
1.1 工具类（JdbcUtil.java)
package com.jdbc.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JdbcUtil {
private static String driver;
private static String url;
private static String username;
private static String password;
static {
try {
Properties props = new Properties();
InputStream is = JdbcUtil.class.getResourceAsStream("/jdbc.properties");
props.load(is);
driver = props.getProperty("driver");
url = props.getProperty("url");
username = props.getProperty("username");
password = props.getProperty("password");
is.close();
} catch (IOException e) {
throw new ExceptionInInitializerError("获取数据库配置文件信息失败");
}
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError("加载驱动失败");
}
}
public static Connection getConnection(){
try {
Connection conn = DriverManager.getConnection(url,username,password);
return conn;
} catch (Exception e) {
throw new ExceptionInInitializerError("连接数据库的url或用户名密码错误");
}
}
public static void release(ResultSet rs,PreparedStatement ps,Connection conn){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
ps = null;
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
public static void main(String[] args) {
// 以增加数据作为测试
Connection conn = null;
PreparedStatement ps = null;
int result = 0;
try {
conn = JdbcUtil.getConnection();
String sql = "insert into student(StudentNo,StudentName) values(1028,'袁华')";
ps = conn.prepareStatement(sql);
result = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally{
JdbcUtil.release(null, ps, conn);
}
if(result>0){
System.out.println("插入成功");
}else{
System.out.println("插入失败");
}
}
}
1.2 配置文件(jdbc.properties)
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/myschool
username=root
password=123456
2.Dbcp
2.1 工具类（DbcpUtil.java)
package com.jdbc.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import mons.dbcp2.BasicDataSourceFactory;
/*
* DBCP数据源的使用
*/
public class DbcpUtil {
static DataSource ds = null;
static {
// try shift+alt+z
try {
Properties props = new Properties();
InputStream is = DbcpUtil.class.getResourceAsStream("/dbcp.properties");
props.load(is);
// DBCP数据源
ds = BasicDataSourceFactory.createDataSource(props);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
return ds.getConnection();
} catch (SQLException e) {
throw new RuntimeException();
}
}
public static void release(Connection conn){
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) throws SQLException {
// 测试
Connection conn = getConnection();
String sql = "insert into student(StudentNo,StudentName,GradeId,Email) values('1209','Jingjing',3,'jingjing@')";
PreparedStatement ps = conn.prepareStatement(sql);
int result = ps.executeUpdate();
if(result>0){
System.out.println("插入成功");
}
}
}
1.2 配置文件(dbcp.properties)
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/myschool
username=root
password=123456
initialSize=10
maxIdle=30
minIdle=3
3.C3p0
3.1 工具类（C3p0Util.java)
package com.jdbc.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mchange.boPooledDataSource;
/*
* C3p0数据源的使用
*/
public class C3p0Util {
static ComboPooledDataSource ds = null;
static {
ds = new ComboPooledDataSource("jdbc-c3p0");
}
public static Connection getConnection(){
try {
return ds.getConnection();
} catch (SQLException e) {
throw new RuntimeException();
}
}
public static void release(ResultSet rs,PreparedStatement ps,Connection conn){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
ps = null;
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
public static void main(String[] args) throws SQLException {
// 测试
Connection conn = getConnection();
String sql = "insert into student(StudentNo,StudentName,GradeId,Email) values('1027','fdafd',3,'fdfdaf@')";
PreparedStatement ps = conn.prepareStatement(sql);
int result = ps.executeUpdate();
if(result>0){
System.out.println("插入成功");
}
}
}
3.2 配置文件(c3p0-config.properties)
<?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/myschool</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="initialPoolSize">10</property>
<property name="maxPoolSize">20</property>
<property name="minPoolSize">5</property>
</default-config>
<named-config name="jdbc-c3p0">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/myschool</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="initialPoolSize">10</property>
<property name="maxPoolSize">20</property>
<property name="minPoolSize">5</property>
</named-config>
</c3p0-config>
4.Jndi(依赖于服务器)
4.1 工具类（JndiUtil.java)package com.jdbc.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
/*
* jdbc数据源连接
*/
public class JndiUtil {
static DataSource ds= null;
static {
try {
Context initCtx = new InitialContext();
Context envCtx = (Context)initCtx.lookup("java:comp/env");
ds = (DataSource)envCtx .lookup("mysql/jdbc");
} catch (NamingException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
return ds.getConnection();
} catch (SQLException e) {
throw new RuntimeException();
}
}
public static void release(ResultSet rs,PreparedStatement ps,Connection conn){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
ps = null;
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
//要用到tomcat故放在jsp页面进行测试，开启tomcat，访问localhost:8080/jdbc/jndi.jsp进行测试
// public static void main(String[] args) throws SQLException {
//测试
// Connection conn = JndiUtil.getConnection();
// String sql = "insert into student(StudentNo,StudentName,GradeId,Email) values('1028','jndi',3,'jndif@')";
// PreparedStatement ps = conn.prepareStatement(sql);
// int result = ps.executeUpdate();
// if(result>0){
// System.out.println("插入成功");
// }
// }
}
4.2 配置文件（context.xml)
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource name="mysql/jdbc"
auth="Container"
type="javax.sql.DataSource"
username="root"
password="123456"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/myschool"
maxActive="8"
maxIdle="4"/>
</Context>
4.3 jndi.jsp(当然也可用servlet)
<%@page import="java.sql.PreparedStatement"%>
<%@page import="com.jdbc.util.JndiUtil"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>这个页面为测试JNDI连接池的</title>
</head>
<body>
<%
// 这个页面为测试JNDI连接池的
Connection conn = JndiUtil.getConnection();
String sql = "insert into student(StudentNo,StudentName,GradeId,Email) values('1032','哈哈',4,'hehef@')";
PreparedStatement ps = conn.prepareStatement(sql);
int result = ps.executeUpdate();
if(result>0){
out.println("插入成功");
}else {
out.println("插入失败");
}
JndiUtil.release(null, ps, conn);
%>
</body>
</html>
