10、JDBC
10、JDBC
10.1、数据库驱动
驱动：声卡、显卡、数据库都需要驱动才可以使用
我们的程序会通过 数据库驱动，和数据库打交道
10.2、JDBC
sun公司未来简化开发人员的（对数据库的统一）操作，提供了一个（java操作数据库的）规范，俗称JDBC
这些规范的实现有具体的厂商去做
对于开发人员来说，我们只需要掌握JDBC接口的操作即可
需要java自带的两个包 java.sql javax.sql
还需要导入一个数据库驱动包 mysql-connector-java-5.1.47（maven工厂可下载）
10.3、第一个JDBC程序
创建测试数据库
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
CREATE TABLE `users`(
id INT PRIMARY KEY,
NAME VARCHAR(40),
PASSWORD VARCHAR(40),
email VARCHAR(60),
birthday DATE
);
INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')
1.创建一个普通项目
2.导入数据库驱动mysql-connector-java-5.1.47.jar（在项目下自创lib文件夹，将mysql-connector-java-5.1.47.jar拖到改文件夹，再设置 add as library）
3.编写测试代码
package com.test1;
import java.sql.*;
// 我的第一个JDBC程序
public class JdbcDemo01 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载数据驱动
Class.forName("com.mysql.jdbc.Driver");//固定写法，加载驱动
//2.用户信息和URL
//useUnicode=true 支持中文编码 characterEncoding=utf8 设置字符集为utf8，useSSL=true使用安全的连接,
// 这儿设置useSSL=true会报错,设置为SSLv3=true也报错，存在疑问
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSLv3=true";
String userName = "root";
String password = "123456";
//3.连接成功，获取数据库对象 connection代表数据库
Connection connection = DriverManager.getConnection(url, userName, password);
//4.执行SQL的对象 statement是执行sql语句的对象
Statement statement = connection.createStatement();
//5.用可以执行sql语句的对象，去执行sql语句 可能存在结果，查看结果
String sql = "SELECT * FROM `users`";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println("id:"+resultSet.getInt("id"));
System.out.println("name:"+resultSet.getString("NAME"));
System.out.println("pwd:"+resultSet.getString("PASSWORD"));
System.out.println("email:"+resultSet.getString("email"));
System.out.println("birth:"+resultSet.getDate("birthday"));
System.out.println("======================");
}
//释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结
1.加载驱动
2.连接数据库 DriverManager
3.获取执行sql的对象 Statement
4.获得返回的结果集
5.释放连接
DriverManager
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());//在Driver类中有静态代码块执行该行，用映射执行比较方便
Class.forName("com.mysql.jdbc.Driver");//固定写法，加载驱动
Connection connection = DriverManager.getConnection(url, userName, password);
//connection 代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.setAutoCommit();
connection.commit();
connection.rollback();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
//mysql默认端口号为3306
//协议：//主机地址:端口号/数据库名?参数1&参数2&参数3...
//oracle默认端口号为：1521
// jdbc:oracle:thin:@localhost:1521:sid
Statement 执行sql的类对象 PreparedStatement 执行sql的对象
String sql = "SELECT * FROM `users`";//编写sql语句
//4.执行SQL的对象 statement是执行sql语句的对象
Statement statement = connection.createStatement();
statement.execute();//执行所有语句
statement.executeQuery();//执行查询语句，返回查询结果集ResultSet
statement.executeUpdate();//更新、插入、删除，都用这个，返回受影响的行数
ResultSet 查询的结果集
resultSet.getObject();//在不知道列类型的情况下使用
//在知道列类型的情况下使用
resultSet.getString();
resultSet.getInt();
resultSet.getDate();
.........
遍历，指针
resultSet.beforeFirst();//将指针移动到第一行之前
resultSet.afterLast();//将指针移动到最后一行之后
resultSet.next();//将指针移动到下一行
resultSet.previous();//将指针移动到前一行
resultSet.absolute(row);//将指针移动到指定行
释放资源
//释放连接
resultSet.close();
statement.close();
connection.close();//耗资源，用完关掉
10.4、statement对象
代码实现
1.提取工具类
# db.properties 配置文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456
package com.test2.Utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
try {
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//驱动只用加载一次
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
//释放连接
public static void release(Connection connection, Statement statement,ResultSet resultSet){
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (statement!=null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection!=null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
2.编写增删改的方法，用executeUpdate
package com.test2;
import com.test2.Utils.JdbcUtils;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
Connection connection=null;
Statement statement=null;
try {
connection = JdbcUtils.getConnection();//获取数据库的连接
statement = connection.createStatement();//获取sql的执行对象
String sql = "INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)\n" +
"VALUES(4,'hsx','123456','2027517804@qq.com','1997-06-19')";
int i = statement.executeUpdate(sql);
if (i>0){
System.out.println("插入成功！");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,null);
}
}
}
package com.test2;
import com.test2.Utils.JdbcUtils;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelect {
public static void main(String[] args) {
Connection connection=null;
Statement statement=null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "DELETE FROM `users` WHERE id = 4";//sql语句发生变化
int i = statement.executeUpdate(sql);
if (i>0){
System.out.println("删除成功！");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,null);
}
}
}
package com.test2;
import com.test2.Utils.JdbcUtils;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpdate {
public static void main(String[] args) {
Connection connection=null;
Statement statement=null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "UPDATE `users` SET `NAME`='hsx',`PASSWORD`='654321' WHERE id = 1";//sql语句发生变化
int i = statement.executeUpdate(sql);
if (i>0){
System.out.println("更新成功！");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,null);
}
}
}
3.查询，用executeQuery
package com.test2;
import com.test2.Utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSelect {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "SELECT * FROM `users`";
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println("id:"+resultSet.getInt("id"));
System.out.println("name:"+resultSet.getString("NAME"));
System.out.println("password:"+resultSet.getString("PASSWORD"));
System.out.println("email:"+resultSet.getString("email"));
System.out.println("birthday:"+resultSet.getDate("birthday"));
System.out.println("==========");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
SQL 注入问题
sql 存在漏洞，会被攻击导致数据泄露，SQL会被拼接 or
package com.test2;
import com.test2.Utils.JdbcUtils;
import java.sql.*;
//sql 注入问题演示,模拟登录
public class TestSqlPro {
public static void main(String[] args) {
//"SELECT * FROM `users` where `NAME` ="+username+", `PASSWORD`="+password
//比如将SELECT * FROM `users` WHERE `NAME`='hsx' AND `PASSWORD`='654321'
//通过sql注入，改为 SELECT * FROM `users` WHERE `NAME`=' ' or '1=1' AND `PASSWORD`=' ' or '1=1'
//这样会盗取数据库的所有用户的信息，十分不安全
//解决：用 PreparedStatement
login(" 'or' 1=1"," 'or' 1=1");//拼接字符串
}
public static void login(String username,String password){
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "SELECT * FROM `users` where `NAME` = '"+username+"'AND `PASSWORD`='"+password+"'";
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println("id:"+resultSet.getInt("id"));
System.out.println("name:"+resultSet.getString("NAME"));
System.out.println("password:"+resultSet.getString("PASSWORD"));
System.out.println("email:"+resultSet.getString("email"));
System.out.println("birthday:"+resultSet.getDate("birthday"));
System.out.println("==========");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
10.5、PreparedStatement对象
PreparedStatement 可以防止SQL注入，而且效率更高
1.新增
package com.test3;
import com.test2.Utils.JdbcUtils;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestInsert {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JdbcUtils.getConnection();
//区别
//使用?占位符来代替参数
String sql = "INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)" +
"VALUES(?,?,?,?,?) ";
preparedStatement = connection.prepareStatement(sql);//预编译sql,先写sql,然后不执行
//手动给sql赋值
preparedStatement.setInt(1,4);
preparedStatement.setString(2,"hhh");
preparedStatement.setString(3,"123456");
preparedStatement.setString(4,"2027517804@qq.com");
//注意，MySQL中的date和java中的date是不一样的
//sql.Date 数据库 java.sql.Date()
//util.Date java new Date().getTime()获得时间戳
preparedStatement.setDate(5,new Date(new java.util.Date().getTime()));
//执行,直接去执行，而Statement需要在executeUpdate()加入sql语句
int i = preparedStatement.executeUpdate();
if (i>0){
System.out.println("插入成功！");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
//关闭连接
JdbcUtils.release(connection,preparedStatement,null);
}
}
}
2.删除
package com.test3;
import com.test2.Utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestDelete {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JdbcUtils.getConnection();
//使用?占位符来代替参数
String sql = "DELETE FROM `users` WHERE id = ?";
preparedStatement = connection.prepareStatement(sql);//预编译sql,先写sql,然后不执行
//手动给sql赋值
preparedStatement.setInt(1,4);
//执行,直接去执行，而Statement需要在executeUpdate()加入sql语句
int i = preparedStatement.executeUpdate();
if (i>0){
System.out.println("删除成功！");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,preparedStatement,null);
}
}
}
3.更新
package com.test3;
import com.test2.Utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestUpdate {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JdbcUtils.getConnection();
//使用?占位符来代替参数
String sql = "UPDATE `users` SET `NAME`=?,`PASSWORD`=? WHERE id =?";
preparedStatement = connection.prepareStatement(sql);//预编译sql,先写sql,然后不执行
//手动给sql赋值
preparedStatement.setString(1,"hhh");
preparedStatement.setString(2,"11111");
preparedStatement.setInt(3,1);
//执行,直接去执行，而Statement需要在executeUpdate()加入sql语句
int i = preparedStatement.executeUpdate();
if (i>0){
System.out.println("更新成功！");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,preparedStatement,null);
}
}
}
4.查询
package com.test3;
import com.test2.Utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestSelect {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
String sql = "SELECT `email` FROM `users` WHERE `NAME`=? AND `PASSWORD`=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"hhh");
preparedStatement.setString(2,"11111");
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getString("email"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,preparedStatement,resultSet);
}
}
}
5.防止SQL注入
package com.test3;
import com.test2.Utils.JdbcUtils;
import java.sql.*;
//sql 注入问题演示,模拟登录,防止sql注入的解决办法
public class TestSqlPro {
public static void main(String[] args) {
//解决：用 PreparedStatement
login(" 'or' 1=1"," 'or' 1=1");//拼接字符串
//login("hhh","11111");
}
public static void login(String username,String password){
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
String sql = "SELECT * FROM `users` WHERE `NAME`=? AND `PASSWORD`=?";
//PreparedStatement 防止sql注入的本质，把传递进来的参数当数值
//假设其中存在转义字符，比如''会被直接转义
statement = connection.prepareStatement(sql);
statement.setString(1,username);
statement.setString(2,password);
resultSet = statement.executeQuery();
while (resultSet.next()){
System.out.println("id:"+resultSet.getInt("id"));
System.out.println("name:"+resultSet.getString("NAME"));
System.out.println("password:"+resultSet.getString("PASSWORD"));
System.out.println("email:"+resultSet.getString("email"));
System.out.println("birthday:"+resultSet.getDate("birthday"));
System.out.println("==========");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
10.7、使用IDEA连接MySQL数据库
10.8、事务
要么都成功，要么都失败
ACID原则
原子性：要么全部完成，要么都不完成
一致性：总数不变
隔离性：多个进程互不干扰
持久性：一旦提交不可逆，持久化到数据库了
隔离性的问题：
脏读：一个事务读取了另一个没有提交的事务
不可重复读：在同一个事务中，重复读取表中的数据，数据发生了改变
虚读（幻读）：在一个事务内，读取了别人插入的数据，导致前后读取出来的结果不一致
代码实现
package com.test4;
import com.test2.Utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
//关闭数据库的自动提交，会自动开启事务
connection.setAutoCommit(false);//开启事务
String sql1 ="update account set money = money - 100 where name = 'A'";
preparedStatement = connection.prepareStatement(sql1);
preparedStatement.executeUpdate();
int x = 1/0;//报错，会默认回滚
String sql2 ="update account set money = money + 100 where name = 'B'";
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
//业务完毕，提交事务
connection.commit();
System.out.println("提交成功");
} catch (SQLException throwables) {
/* try {
//失败，就回滚，默认失败会自动回滚，这块代码可以不写
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}*/
throwables.printStackTrace();
}finally {
JdbcUtils.release(connection,preparedStatement,null);
}
}
}
10.9、数据库连接池
数据库连接---执行完毕---释放
连接--释放十分浪费系统资源
池化技术：准备一些预先的资源，过来就连接预先准备好的
连接池有最小连接数，最大连接数，当连接超过最大连接数，等待，就有一个等待超时
编写连接池，实现DataSourse接口
有已经编好的数据连接池
开源数据源实现（拿来即用）
DBCP
C3P0
Druid:阿里巴巴
使用了这些数据库池之后，我们在项目开发中就不需要编写连接数据库的代码了。
DBCP
需要用到的jar包有：
commons-dbcp-1.4.jar 、commons-pool-1.6.jar
dbcpconfig.properties
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456
#<!-- 初始化连接 -->
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最大空闲连接 -->
maxIdle=20
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：【属性名=property;】
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8
#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true
#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=
#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED
package com.test05.Utils;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils_DBCP {
private static DataSource dataSource = null;
static {
InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties properties = new Properties();
try {
properties.load(in);
//创建数据源 工厂模式--》创建
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();//从数据源中获取连接
}
//释放连接
public static void release(Connection connection, Statement statement,ResultSet resultSet){
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (statement!=null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection!=null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
package com.test05;
import com.test05.Utils.JdbcUtils_DBCP;
import com.test2.Utils.JdbcUtils;
import java.sql.*;
public class TestDBCP {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils_DBCP.getConnection();
String sql = "SELECT `email` FROM `users` WHERE `NAME`=? AND `PASSWORD`=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"hhh");
preparedStatement.setString(2,"111111");
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getString("email"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils_DBCP.release(connection,preparedStatement,resultSet);
}
}
}
C3P0
需要用到的jar包有：
c3p0-0.9.5.5.jar、mchange-commons-java-0.2.19.jar
c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!--
c3p0的缺省（默认）配置
如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource();"这样写就表示使用的是c3p0的缺省（默认）-->
<default-config>
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&uesSSL=false&serverTimezone=UTC</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquiredIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
<!--
c3p0的命名配置
如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");"这样写就表示使用的是mysql的缺省（默认）-->
<named-config name="MySQL">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&uesSSL=false&serverTimezone=UTC</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquiredIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</named-config>
</c3p0-config>
package com.test05.Utils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JdbcUtils_C3P0 {
private static ComboPooledDataSource dataSource = null;
static {
//代码版配置
/*dataSource = new ComboPooledDataSource();
dataSource.setDriverClass();
dataSource.setJdbcUrl();
dataSource.setUser();
dataSource.setPassword();
dataSource.setMinPoolSize();
dataSource.setMaxPoolSize();*/
//创建数据源 工厂模式--》创建
dataSource = new ComboPooledDataSource("MySQL");//配置文件写法
}
//获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();//从数据源中获取连接
}
//释放连接
public static void release(Connection connection, Statement statement,ResultSet resultSet){
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (statement!=null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection!=null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
package com.test05;
import com.test05.Utils.JdbcUtils_C3P0;
import com.test05.Utils.JdbcUtils_DBCP;
import com.test2.Utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestC3P0 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils_C3P0.getConnection();
String sql = "SELECT `email` FROM `users` WHERE `NAME`=? AND `PASSWORD`=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"hhh");
preparedStatement.setString(2,"111111");
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getString("email"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils_C3P0.release(connection,preparedStatement,resultSet);
}
}
}
结论
无论使用什么数据源，本质是一样的，DataSourse接口不会变，方法就不会变
