系列文章

数据库 —— MySQL 01
数据库 —— MySQL 02
数据库 —— Java操作MySQL

文章目录

系列文章
十、JDBC
- 10.1 什么是JDBC
- 10.2、JDBC程序
- - 10.2.1、封装工具类
  - 10.2.2、SQL注入问题及解决
- 10.3、使用IDEA连接数据库
- 10.4、JDBC操作事务
- 10.5、数据库连接池

十、JDBC

10.1 什么是JDBC

JDBC是SUN公司为了简化开发人员对数据库的操作，提供了一个Java操作数据库的规范，俗称JDBC。

这些规范的实现由具体数据库的厂商去做，即驱动。对于开发人员来说，我们只需要掌握JDBC接口即可。

10.2、JDBC程序

1、先用SQLyog创建数据库、表以及插入数据

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')

2、IDEA创建普通Java项目

3、导入MySQL的驱动jar包，这里用的是5.1.47。

官网链接：https://downloads.mysql.com/archives/c-j/

Java基本使用SQL

package lessen10_JDBC;import java.sql.*;public class jdbcFirstDemo {public static void main(String[] args) throws ClassNotFoundException, SQLException {//1. 加载驱动Class.forName("com.mysql.jdbc.Driver");//通过反射的方式//2. 用户信息和URL//localhost 主机，端口 3306，jdbcStudy 数据库名，使用unicode编码并设置字符编码uft8String url = "jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=false";String userName = "root";String password = "123456";//3. 连接数据库，获得数据库对象 Connection代表数据库Connection connection = DriverManager.getConnection(url, userName, password);//4. 获得执行SQL命令的对象Statement statement = connection.createStatement();//5. 执行SQL命令，若有结果，则查看结果String sql = "SELECT * FROM users";//返回结果集，结果集中封装了全部的查询结果ResultSet resultSet = statement.executeQuery(sql);while (resultSet.next()){System.out.println("id = "+resultSet.getObject("id"));System.out.println("name = "+resultSet.getObject("name"));System.out.println("pwd = "+resultSet.getObject("pwd"));System.out.println("email = "+resultSet.getObject("email"));System.out.println("birthday = "+resultSet.getObject("birthday"));System.out.println("--------------------------------------------------");}//6. 释放数据库resultSet.close();statement.close();connection.close();}
}

下面对上面代码进行解释：

为什么通过class.forName()加载？查看Dirver源码可以发现，Dirver构造方法中通过DriverManage注册，而我们只需要通过class.forName()便会执行到Driver构造方法，从而激活驱动。

public class Driver extends NonRegisteringDriver implements java.sql.Driver {public Driver() throws SQLException {}static {try {DriverManager.registerDriver(new Driver());} catch (SQLException var1) {throw new RuntimeException("Can't register driver!");}}
}

Statement对象

statement.executeQuery("SQL语句");//查询，返回Result结果集
statement.executeUpdate("SQL语句");//更新、插入、删除，返回受影响的行数
statement.execute("SQL语句");//执行任何SQL，但效率要低些，因为要判断类型

ResultSet 查询的结果集，封装了所有结果。

resultSet.getObject("列名");//在不知道具体类型时使用
resultSet.getInt("列名");//知道列名具体类型时使用，效率更高
resultSet.getString("列名");
resultSet.getFloat("列名");
....

遍历：resultSet结果集就像一个表，我们一行一行读取其数据。

resultSet.next();//结果集光标移动到下一行
resultSet.privious();//结果集移动到上一行
resultSet.absolute(行);//移动到指定行
resultSet.beforeFirst();//移动到第一行

10.2.1、封装工具类

这里先只写连接和释放部分，下面学了PreparedStatement再写完整版。

在src目录下新建一个db.properties文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=false
userName=root
password=123456

JdbcUtils.java

package lessen02.utils;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{try {InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");Properties properties = new Properties();properties.load(in);driver = properties.getProperty("driver");url = properties.getProperty("url");userName = properties.getProperty("userName");password = properties.getProperty("password");//驱动加载Class.forName(driver);}catch (Exception 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 e){e.printStackTrace();}}if (statement != null){try {statement.close();}catch (SQLException e){e.printStackTrace();}}if (connection != null){try {connection.close();}catch (SQLException e){e.printStackTrace();}}}
}

后续可以更加方便连接、释放数据库。

10.2.2、SQL注入问题及解决

前面使用的Statement存在一种问题，在用户登录时我们可以绕过密码，考虑如下情况：

package lessen02;import lessen02.utils.JdbcUtils;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class SqlQuestion {public static void main(String[] args) throws SQLException {System.out.println("正常登录时：");login("lisi", "123456");System.out.println("绕开密码：");login("'or'1=1", "'or'1=1");//因为此时的SQL是：SELECT * FROM `users` WHERE `name`=''or'' AND `pwd`=''or'';//通过 or 来作为SQL命令，从而绕开密码，同时还拿到所有用户密码}public static void login(String userName, String password) throws SQLException {Connection connection = null;Statement st = null;ResultSet rt = null;connection = JdbcUtils.getConnection();st = connection.createStatement();String sql = "SELECT * FROM `users` WHERE `name`='"+userName+"' AND `pwd`='"+password+"';";rt = st.executeQuery(sql);while (rt.next()){System.out.println("用户："+rt.getString("name"));System.out.println("密码："+rt.getString("pwd"));System.out.println("---------------------------------------");}JdbcUtils.release(connection, st, rt);}
}

结果：

正常登录时：
用户：lisi
密码：123456
---------------------------------------
绕开密码：
用户：zhansan
密码：123456
---------------------------------------
用户：lisi
密码：123456
---------------------------------------
用户：wangwu
密码：123456
---------------------------------------

下面是解决方案：通过PreparedStatement对象防止注入，因为PreparedStatement会把传递进来的参数当作字符。假如里面存在转义字符，例如引号 ’ ，就会被直接转义！

package lessen02;import lessen02.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) {System.out.print("正常登录时：");login("lisi", "123456");System.out.print("绕开密码：");login("'or'1=1", "'or'1=1");}public static void login(String userName, String password) {Connection connection = null;PreparedStatement st = null;ResultSet rt = null;try{connection = JdbcUtils.getConnection();//1. 写SQL，用?表示占位符，后面用参数替代String sql = "select * from `users` where `id`=? and `pwd`=?";//2. 预处理SQLst = connection.prepareStatement(sql);//3.填写参数，第一个参数表示是第几个问号，第二个是值st.setString(1, userName);st.setString(2,password);//4.执行SQL，返回结果集rt = st.executeQuery();if(rt.next())System.out.println("登录成功");elseSystem.out.println("登录失败");}catch (SQLException e){e.printStackTrace();}finally {JdbcUtils.release(connection, st, rt);}}
}

结果：

正常登录时：登录失败
绕开密码：登录失败

下面通过PreparedStatement插入：

package lessen02;import lessen02.utils.JdbcUtils;import java.util.*;import java.sql.*;import java.util.Date;/*** 向users表插入用户*/
public class TestInsert {public static void main(String[] args) throws SQLException {Connection connection = null;PreparedStatement st = null;ResultSet rt = null;try{connection = JdbcUtils.getConnection();//1. 写SQL，用?表示占位符，后面用参数替代String sql = "INSERT INTO `users` (`id`,`name`,`pwd`,`email`, `birthday`) VALUES(?,?,?,?,?)";//2. 预处理SQLst = connection.prepareStatement(sql);//3.填写参数，第一个参数表示是第几个问号，第二个是值st.setInt(1,4);st.setString(2, "狗蛋");st.setString(3, "123456");st.setString(4,"123456789@qq.com");//数据库的Date和Java的Date不同，java是Utils下的。// 数据库的Date需要传入一个时间戳，通过java的Date().getTime()获取Date javaDate = new Date();java.sql.Date sqlDate = new java.sql.Date(javaDate.getTime());st.setDate(5,sqlDate);//4.执行SQL，返回受影响行数if(st.executeUpdate() > 0)System.out.println("插入成功！");}catch (SQLException e){e.printStackTrace();}finally {JdbcUtils.release(connection, st, rt);}}
}

10.3、使用IDEA连接数据库

1、侧边栏点击DataBase

侧边栏

2、选择数据源，输入用户和密码连接。

注意：如果连接失败，可能因为MySQL版本问题，有的版本要求URL里有时区。

3、选择自己数据库的表，之后双击右侧表就能可视化查看。

4、如果修改表格，一定要点击提交。
在这里插入图片描述

5、如果要写SQL或者切换数据库。

10.4、JDBC操作事务

ACID原则

原子性：要么全部完成，要么都不完成

一致性：总数不变

隔离性：多个进程互不干扰

持久性：一旦提交不可逆，持久化到数据库了

隔离性的问题：

脏读：一个事务读取了另一个事务没有提交的数据。
不可重复读：在同一个事务内，重复读取表中的数据，表数据发生了变化。
虚读：在一个事务内，读取了别人插入的数据，导致前后读出来的结果不一致。

代码实现：

//先创建表，并插入数据
create table `account`(`name` int(4) not null,`money` float(4) not null,primary key (`name`)
);insert into `account`
values (1,100),(2,50);

Java事务模拟转账：

package lessen03;import lessen02.utils.JdbcUtils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;public class TestTransaction {public static void main(String[] args) throws SQLException {Connection connection = null;PreparedStatement st = null;try {connection = JdbcUtils.getConnection();//关闭自动提交，同时系统会自动开启事务connection.setAutoCommit(false);String sql1 = "update `account` set `money` = `money` - 20 where `name`=1";st = connection.prepareStatement(sql1);st.executeUpdate();//这里会报错，模拟业务被打断，然后进行回滚int x = 1/0;String sql2 = "update `account` set `money` = `money` + 20 where `name`=2";st = connection.prepareStatement(sql2);st.executeUpdate();//事务提交connection.commit();System.out.println("提交成功");} catch (Exception e) {e.printStackTrace();//如果失败，则回滚connection.rollback();System.out.println("回滚");}finally {JdbcUtils.release(connection, st, null);}}
}

10.5、数据库连接池

由于我们使用数据库要经过连接——执行——释放，而连接释放十分资源，因此有了池化技术，即提前准备一些资源，当有连接需要时直接连接，使用完后在一定时间内不会释放。

连接池开源数据源实现：DBCP、C3P0、Druid，使用它们的连接池后，我们项中就不需要再写连接出数据库的代码。

1、DBCP：需要导入两个JAR包，这里用的 commons-dbcp-1.4-bin.tar.gz、commons-pool-1.6-src.tar.gz

官网链接：http://commons.apache.org/proper/commons-dbcp/download_dbcp.cgi

官网链接：http://commons.apache.org/proper/commons-pool/download_pool.cgi

在使用方法上，我们加入了连接池和之前的没什么区别，但是在性能上有很大提高。

同样的，也需要创建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

用DBCP写工具类

package TestPool;import org.apache.commons.dbcp.BasicDataSourceFactory;import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;public class JdbcUtils_DBCP {private static DataSource dataSource = null;static{try {InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");Properties properties = new Properties();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 e){e.printStackTrace();}}if (statement != null){try {statement.close();}catch (SQLException e){e.printStackTrace();}}if (connection != null){try {connection.close();}catch (SQLException e){e.printStackTrace();}}}
}

用DBCP测试向数据库插入数据

package TestPool;import lessen02.utils.JdbcUtils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;public class TestDBCP {public static void main(String[] args) throws SQLException {Connection connection = null;PreparedStatement st = null;try{//connection = JdbcUtils.getConnection();connection = JdbcUtils_DBCP.getConnection();//1. 写SQL，用?表示占位符，后面用参数替代String sql = "INSERT INTO `users` (`id`,`name`,`pwd`,`email`, `birthday`) VALUES(?,?,?,?,?)";//2. 预处理SQLst = connection.prepareStatement(sql);//3.填写参数，第一个参数表示是第几个问号，第二个是值st.setInt(1,5);st.setString(2, "狗蛋2");st.setString(3, "111111");st.setString(4,"123456780@qq.com");//数据库的Date和Java的Date不同，java是Utils下的。// 数据库的Date需要传入一个时间戳，通过java的Date().getTime()获取Date javaDate = new Date();java.sql.Date sqlDate = new java.sql.Date(javaDate.getTime());st.setDate(5,sqlDate);//4.执行SQL，返回受影响行数if(st.executeUpdate() > 0)System.out.println("插入成功！");}catch (SQLException e){e.printStackTrace();}finally {JdbcUtils_DBCP.release(connection, st, null);}}
}

2、C3P0：需要导入jar包，链接 /c3p0-bin/c3p0-0.9.5.5/c3p0-0.9.5.5.bin.zip 。

c3p0-0.9.5.5.jar、mchange-commons-java-0.2.19.jar。它们都在压缩包中的lib目录下。

配置文件：必须命名为 c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config><!--c3p0的缺省（默认）配置如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource();"这样写就表示使用的是c3p0的缺省（默认）--><default-config><proerty name="driverClass">com.mysql.jdbc.Driver</proerty><property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy?userUnicode=true&amp;characterEncoding=utf8&amp;uesSSL=false&amp;serverTimezone=UTC</property><property name="user">root</property><property name="password">123456</property><property name="acquireIncrement">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");"这样写就表示使用的是name为MySQL的配置--><name-config name="MySQL"><proerty name="driverClass">com.mysql.jdbc.Driver</proerty><property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy?userUnicode=true&amp;characterEncoding=utf8&amp;uesSSL=false&amp;serverTimezone = UTC</property><property name="user">root</property><property name="password">123456</property><property name="acquireIncrement">5</property><property name="initialPoolSize">10</property><property name="minPoolSize">5</property><property name="maxPoolSize">20</property></name-config>
</c3p0-config>

封装的工具类：

package TestPool;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{try {//创建数据源 用配置文件的方式dataSource = new ComboPooledDataSource("MySQL");//代码版配置/*dataSource = new ComboPooledDataSource();dataSource.setDriverClass();dataSource.setUser();dataSource.setPassword();dataSource.setJdbcUrl();dataSource.setMaxPoolSize();dataSource.setMinPoolSize();*/}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 e){e.printStackTrace();}}if (statement != null){try {statement.close();}catch (SQLException e){e.printStackTrace();}}if (connection != null){try {connection.close();}catch (SQLException e){e.printStackTrace();}}}
}