一、JDBC概述
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/45ed9a8b2e914e0eb589dc669ce264ee.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/73dd6cee7de54121bf8723bc1c332112.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/b7232cf74fe3498a9908d0d956339bac.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/cd6bbaf44bb94a999f3d546bdf6fd1e5.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/64cbae7b01ed4ccea241cbbb68ac0c99.png)
二、JDBC快速入门
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/9c124952415844e98f6fc7d0aa5822b2.png)
2.1 添加驱动
- 新建 libs 文件夹 --> 复制驱动 --> 右键 .jar 文件 Add as Library …
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/527d4bbb938444d989f18ebe8c737a01.png)
2.2 执行sql
package com.gyhedu.jdbc.myjdbc;
import com.mysql.jdbc.Driver;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBC01 {
public static void main(String[] args) throws SQLException {
Driver driver = new Driver();
String url = "jdbc:mysql://localhost:13306/dbtest1";
Properties properties = new Properties();
properties.setProperty("user","root");
properties.setProperty("password","gyhgzf");
Connection connect = driver.connect(url, properties);
String sql = "INSERT INTO actor(name, sex, borndate, phone) VALUES('刘德华', '男', '1970-11-11', '110')";
Statement statement = connect.createStatement();
int rows = statement.executeUpdate(sql);
System.out.println(rows > 0?"成功":"失败");
statement.close();
connect.close();
}
}
2.3 获取数据库连接5种方式
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/5b2792bdcaec4da4be362c68719e6091.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/bdf4c4b514a1449fbc32189f57a35799.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/1219a908b0c1441fa6c6afe74657fa6e.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/16c55ba43b7d49c488460d9f17550370.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/c8626f128d07412e95e01d2c482ab977.png)
2.4 ResultSet[结果集]
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/352d651cdadb4a678af0f644037b603d.png)
package com.gyhedu.jdbc.myjdbc;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class JDBC03 {
public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
String user = properties.getProperty("user");
String password = properties.getProperty("password");
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
String sName = "刘德华";
String sql = "SELECT id, `name`, sex, borndate, phone FROM actor WHERE `name` = '" + sName + "';";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String sex = resultSet.getString(3);
String bornDate = resultSet.getString(4);
String phone = resultSet.getString(5);
System.out.println(id + '\t' + name + '\t' + sex + '\t' + bornDate + '\t' + phone);
}
resultSet.close();
statement.close();
connection.close();
}
}
2.5 statement(SQL注入)
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/dfd28eca7b2a4d94a25909e652bd0ef8.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/f941efba78394cbaa5d59d711c116753.png)
2.6 PreparedStatement
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/644fd7b8d7ab49e5a4bb999e59201acf.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/c8f316092e9b44d8bfb3ac4131288d84.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/54c48f7d86974ec599ec4b8d9e456046.png)
package com.gyhedu.jdbc.myjdbc;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class PreparedStatement01 {
public static void main(String[] args) throws ClassNotFoundException, IOException, SQLException {
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
String user = properties.getProperty("user");
String password = properties.getProperty("password");
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
String sql = "SELECT COUNT(*) FROM actor WHERE id > ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 2);
ResultSet resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
System.out.println(resultSet.getInt(1));
}
preparedStatement.close();
connection.close();
}
}
三、JDBC API
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/68aa81b4a3fc4c068fd0eb8cb7eccab4.png)
四、JDBCUtils
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/65632ad2bc914088b4fac97814833a28.png)
package com.gyhedu.jdbc.jdbcutils;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
private static String url;
private static String user;
private static String password;
private static String driver;
static {
Properties properties = new Properties();
try {
properties.load(new FileInputStream("src\\mysql.properties"));
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
driver = properties.getProperty("driver");
Class.forName(driver);
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
public static void close(ResultSet resultSet, Statement statement, Connection connection) {
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();
}
}
}
}
package com.gyhedu.jdbc.jdbcutils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBCUtilsTest {
public static void main(String[] args) throws SQLException {
Connection connection = JDBCUtils.getConnection();
String sql = "SELECT id, `name`, sex, borndate, phone FROM actor WHERE id > ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 1);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String sex = resultSet.getString(3);
String bornDate = resultSet.getString(4);
String phone = resultSet.getString(5);
System.out.println(id + "\t" + name + "\t" + sex + "\t" + bornDate + "\t" + phone);
}
JDBCUtils.close(resultSet, preparedStatement, connection);
}
}
五、事务
5.1 基本介绍
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/caea227edd0641298eda224d1b313f45.png)
5.2 应用实例
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/e349be6b1a3b4cfea7f22ffc25088646.png)
package com.gyhedu.jdbc.event;
import com.gyhedu.jdbc.jdbcutils.JDBCUtils;
import jdk.nashorn.internal.scripts.JD;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Event {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
PreparedStatement preparedStatement1 = null;
try {
connection = JDBCUtils.getConnection();
connection.setAutoCommit(false);
String sql1 = "UPDATE account SET balance = balance - ? WHERE name = ?";
preparedStatement = connection.prepareStatement(sql1);
preparedStatement.setInt(1, 100);
preparedStatement.setString(2, "马云");
String sql2 = "UPDATE account SET balance = balance + ? WHERE name = ?";
preparedStatement1 = connection.prepareStatement(sql2);
preparedStatement1.setInt(1, 100);
preparedStatement1.setString(2, "马化腾");
preparedStatement.executeUpdate();
int n = 1 / 0;
preparedStatement1.executeUpdate();
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}
JDBCUtils.close(null, preparedStatement, connection);
JDBCUtils.close(null, preparedStatement1, null);
}
}
}
六、批处理
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/54d97e1fa8ae4068a2ce70c11f3896c0.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/81b549660e19447d8fc679def789fe1d.png)
七、连接池
7.1 基本介绍
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/f94a815091db44759928bfff95785d7e.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/ebe8701f5a4d44d6a8e17ebf9a96dee5.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/2b39ba81e39b473a84d433d638903ba2.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/501929991aba431c9c26f1c3629ac1e4.png)
7.2 数据库连接池种类
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/3f1ed66f5ef44e5492d5776f138b6dee.png)
7.3 C3P0应用实例
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/ba8802e9d90e446fb9581c4de8314b68.png)
package com.gyhedu.jdbc.dbpool;
import com.gyhedu.jdbc.jdbcutils.JDBCUtils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
public class C3P0Test {
public static void main(String[] args) throws SQLException {
c3p0Test_02();
}
private static void testC3P0_01() throws Exception {
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
String url = properties.getProperty("url");
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
comboPooledDataSource.setDriverClass(driver);
comboPooledDataSource.setJdbcUrl(url);
comboPooledDataSource.setUser(user);
comboPooledDataSource.setPassword(password);
comboPooledDataSource.setInitialPoolSize(10);
comboPooledDataSource.setMaxPoolSize(50);
Connection connection = comboPooledDataSource.getConnection();
connection.close();
}
private static void c3p0Test_02() throws SQLException {
ComboPooledDataSource myc3p0 = new ComboPooledDataSource("Myc3p0");
Connection connection = myc3p0.getConnection();
System.out.println("连接成功");
connection.close();
}
}
<?xml version="1.0" encoding="utf-8"?>
<c3p0-config>
<named-config name="Myc3p0">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:13306/dbtest1</property>
<property name="user">root</property>
<property name="password">gyhgzf</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">5</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">10</property>
<property name="maxStatements">20</property>
<property name="maxStatementsPerConnection">5</property>
</named-config>
</c3p0-config>
7.4 Druid应用实例
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/58f465946eac47cab270f8540f862ef5.png)
package com.gyhedu.jdbc.dbpool;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.util.Properties;
public class DruidTest {
public static void main(String[] args) throws Exception {
Properties properties = new Properties();
properties.load(new FileInputStream("src\\druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
System.out.println("连接成功");
connection.close();
}
}
#key=value
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:13306/dbtest1?rewriteBatchStatements=true
#url=jdbc:mysql://localhost:13306/dbtest1
username=root
password=gyhgzf
#initial connection Size
initialSize=10
#min idle connection size
minIdle=5
#max active connection size
maxActive=50
#max wait time(5000 mil seconds)
maxWait=5000
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/f2bbcd2e76634f4084cc8d5d98900fe2.png)
package com.gyhedu.jdbc.jdbcutils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCUtilsByDruid {
private static DataSource dataSource;
static {
try {
Properties properties = new Properties();
properties.load(new FileInputStream("src\\druid.properties"));
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public static void close(ResultSet resultSet, Statement statement, Connection connection){
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();
}
}
}
}
八、Apache — DBUtils
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/7ab264db00444fcabb0f99666e3745db.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/39bf1184cd434f5aab453978e56ff994.png)
8.1 自定义的解决方法
package com.gyhedu.jdbc.jdbcutils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
public class JDBCUtilsByDruidTest {
public static void main(String[] args) throws SQLException, InstantiationException, IllegalAccessException {
Connection connection = JDBCUtilsByDruid.getConnection();
String sql = "SELECT id, name, sex, borndate, phone FROM actor WHERE id >= ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 1);
ResultSet resultSet = preparedStatement.executeQuery();
List<Actor> result = JDBCUtilsByDruid.getResult(resultSet, Actor.class);
result.forEach(System.out::println);
JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);
}
}
package com.gyhedu.jdbc.jdbcutils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;
public class JDBCUtilsByDruid {
private static DataSource dataSource;
static {
try {
Properties properties = new Properties();
properties.load(new FileInputStream("src\\druid.properties"));
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public static <T> List<T> getResult(ResultSet resultSet, Class<T> beanClass) throws SQLException, IllegalAccessException, InstantiationException {
List<T> list = new ArrayList<>();
Field[] declaredFields = beanClass.getDeclaredFields();
while(resultSet.next()){
T entity = null;
entity = beanClass.newInstance();
for(Field f:declaredFields){
f.setAccessible(true);
f.set(entity,resultSet.getObject(f.getName()));
}
list.add(entity);
}
return list;
}
public static void close(ResultSet resultSet, Statement statement, Connection connection){
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();
}
}
}
}
class Actor{
private Integer id;
private String name;
private String sex;
private Date borndate;
private String phone;
public Actor() {
}
public Actor(Integer id, String name, String sex, Date borndate, String phone) {
this.id = id;
this.name = name;
this.sex = sex;
this.borndate = borndate;
this.phone = phone;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBorndate() {
return borndate;
}
public void setBorndate(Date borndate) {
this.borndate = borndate;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Actor{" +
"id=" + id +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", borndate=" + borndate +
", phone='" + phone + '\'' +
'}';
}
}
8.2 Apache — DBUtils
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/d6966ba67c8d423395fb32456c6db60f.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/f2c25a19bfbf4a57963bc05429cabbd6.png)
package com.gyhedu.jdbc.jdbcutils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
public class DBUtilsUse {
public static void main(String[] args) throws SQLException {
Connection connection = JDBCUtilsByDruid.getConnection();
QueryRunner queryRunner = new QueryRunner();
String sql = "SELECT * FROM actor WHERE id >= ?";
List<Actor> list =
queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);
System.out.println("输出集合的信息");
for (Actor actor : list) {
System.out.println(actor);
}
JDBCUtilsByDruid.close(null, null, connection);
}
}
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/a6adab62ac214c2b956075a7045b0430.png)
九、DAO增删改查 - BasicDao
- DAO:数据访问对象
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/ec55b24856244366a77bbc2db620289a.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/40633e5af887455e8cdcf9a310c51b29.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/b62ff3e5670f4adf8b7814eeeb6e8778.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/5e75179072b244eba93d7071ba4b1d24.png)
package com.gyhedu.dao;
import com.gyhedu.jdbc.jdbcutils.JDBCUtils;
import com.gyhedu.utils.JDBCUtilsByDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
public class BasicDAO<T> {
private QueryRunner queryRunner = new QueryRunner();
public int update(String sql, Object... parameters) {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
int update = queryRunner.update(connection, sql, parameters);
return update;
} catch (SQLException e) {
throw new RuntimeException();
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
public List<T> queryMulti(String sql, Class<T> clazz, Object... parameters) {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
List<T> query =
queryRunner.query(connection, sql, new BeanListHandler<>(clazz), parameters);
return query;
} catch (SQLException e) {
throw new RuntimeException();
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
public T querySingle(String sql, Class<T> clazz, Object... parameters) {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
T query =
queryRunner.query(connection, sql, new BeanHandler<>(clazz), parameters);
return query;
} catch (SQLException e) {
throw new RuntimeException();
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
public Object queryScalar(String sql, Object... parameters) {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
Object query =
queryRunner.query(connection, sql, new ScalarHandler<>(), parameters);
return query;
} catch (SQLException e) {
throw new RuntimeException();
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
}
package com.gyhedu.dao;
import com.gyhedu.jdbc.jdbcutils.Actor;
public class ActorDAO extends BasicDAO<Actor>{
}