2020-06-08-JDBC操作数据库

第一章 JDBC的简介

1.1 JDBC是什么

JDBC (Java DataBase Connectivity) 是一组用于执行SQL语句的Java API,它由一组用Java语言编写的类和接口组成,可以为多种关系型数据库提供统一访问。他是一种标准,是由oracle公司制定的一套使用Java语言访问数据库的标准.

1.2 JDBC原理图

</center>

1.3 JDBC提供的常用接口和类

1. DriverManager: 管理数据库驱动程序,用于建立数据库连接

2. Driver: 提供给各个数据库厂商的接口，每一个数据库厂商要想可以使用Java语言来与他们的数据库进行通信,必须实现此接口.(主要处理与数据库服务器之间的通信)

3. Connection: 此接口具有用于联系数据库的所有方法

4. Statement: 从此接口创建的对象将SQL语句提交到数据库

5. ResultSet: 在使用Statement对象执行SQL查询后，这个对象保存从数据库检索的数据

6. SQLException: 处理数据库应用程序中发生的异常

第二章 JDBC入门

以一个用户表为例来使用JDBC进行增删该查的操作

数据库环境搭建

// 1)、创建数据库

CREATE DATABASE jdbc DEFAULT CHARACTER SET UTF8;

// 2)、切换数据库

USE jdbc;

// 3)、创建数据库表

CREATE TABLE user(

`user_id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',

`user_name` VARCHAR(20) NOT NULL COMMENT '用户名',

`price` double(10,2) DEFAULT 0.0 COMMENT '价格',

`create_time` DATETIME DEFAULT NULL COMMENT '创建时间'

)ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=UTF8 COMMENT="用户表";

添加用户

//①加载数据库驱动

Class.forName("com.mysql.jdbc.Driver");

//②创建数据库连接

Connection conn = DriverManager.getConnection("jdbc:mysql:///jdbc?characterEncoding=utf-8","rooe","root");

//③创建Statement对象

Statement statement = conn.createStatement();

//④创建添加SQL语句

String sql="INSERT INTO user(user_name,price,create_time) VALUES('admin',100.12,now())";

//⑤执行sql语句,返回影响行数,如果需要获取主键，要配置Statement.RETURN_GENERATED_KEYS参数

int row = statement.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);

//⑥打印影响行数

System.out.println("row:"+row);

//如果想获取插入用户的主键可以使用statement.getGeneratedKeys()获取

ResultSet resultSet=statement.getGeneratedKeys();

if(resultSet.next()){

int id = resultSet.getInt(1);

}

更新用户

//①加载数据库驱动

Class.forName("com.mysql.jdbc.Driver");

//②创建数据库连接

Connection conn = DriverManager.getConnection("jdbc:mysql:///jdbc?characterEncoding=utf-8","rooe","root");

//③创建Statement对象

Statement statement = conn.createStatement();

//④创建更新SQL语句

String sql="UPDATE user SET user_name='admin1',price=12.12,create_time=now() WHERE user_id=100";

//⑤执行sql语句,返回影响行数

int row = statement.executeUpdate(sql);

//⑥打印影响行数

System.out.println("row:"+row);

删除用户

//①加载数据库驱动

Class.forName("com.mysql.jdbc.Driver");

//②创建数据库连接

Connection conn = DriverManager.getConnection("jdbc:mysql:///jdbc?characterEncoding=utf-8","rooe","root");

//③创建Statement对象

Statement statement = conn.createStatement();

//④创建更新SQL语句

String sql="DELETE FROM user WHERE user_id=100";

//⑤执行sql语句,返回影响行数

int row = statement.executeUpdate(sql);

//⑥打印影响行数

System.out.println("row:"+row);

查询用户列表

//创建一个集合对象,将从数据库查询出来的数据保存到集合中

List<User> users = new ArrayList<>();

//①加载数据库驱动

Class.forName("com.mysql.jdbc.Driver");

//②创建数据库连接

Connection conn = DriverManager.getConnection("jdbc:mysql:///jdbc?characterEncoding=utf-8","rooe","root");

//③创建Statement对象

Statement statement = conn.createStatement();

//④创建SQL语句

String sql="SELECT * FROM user";

//⑤执行sql语句,返回结果集对象

ResultSet result = statement.executeQuery(sql);

while(result.next()){

User user = new User();

int userId = result.getInt("user_id");

user.setUserId(userId);

String userName = result.getString("user_name");

user.setUserName(userName);

double price = result.getDouble("price");

user.setPrice(price);

Date createTime = result.getDate("create_time");

user.setCreateTime(createTime);

users.add(user);

}

//打印从数据库查询出来的对象

System.out.println(users);

查询一个用户(因为JDBC没有提供一个查询单个对象的结果集,所以查询单个对象时也是使用ResultSet对象,取集合中的一个对象即可)

//创建一个集合对象,将从数据库查询出来的数据保存到集合中

List<User> users = new ArrayList<>();

//①加载数据库驱动

Class.forName("com.mysql.jdbc.Driver");

//②创建数据库连接

Connection conn = DriverManager.getConnection("jdbc:mysql:///jdbc?characterEncoding=utf-8","root","root");

//③创建Statement对象

Statement statement = conn.createStatement();

//④创建SQL语句

String sql="SELECT * FROM user";

//⑤执行sql语句,返回结果集对象

ResultSet result = statement.executeQuery(sql);

while(result.next()){

User user = new User();

int userId = result.getInt("user_id");

user.setUserId(userId);

String userName = result.getString("user_name");

user.setUserName(userName);

double price = result.getDouble("price");

user.setPrice(price);

Date createTime = result.getDate("create_time");

user.setCreateTime(createTime);

users.add(user);

}

//打印从数据库查询出来的对象

System.out.println(users);

查询数据库总记录数

//①加载数据库驱动

Class.forName("com.mysql.jdbc.Driver");

//②创建数据库连接

Connection conn = DriverManager.getConnection("jdbc:mysql:///jdbc?characterEncoding=utf-8","root","root");

//③创建Statement对象

Statement statement = conn.createStatement();

//④创建SQL语句

String sql="SELECT COUNT(*) AS count FROM user";

//⑤执行sql语句,返回结果集对象

ResultSet result = statement.executeQuery(sql);

if(result.next()){

int count = result.getInt("count");

System.out.println(count);

}

分页查询

MYSQL数据库分页的语法

SELECT * FROM user LIMIT [offset],[max];

MYSQL的SQL语句的语法格式带有两个参数分别是offset和max,而我们前端或者是客户端给后台发送过来的数据为pageNo(当前页)和pageSize(每页显示多少条数),而SQL语句里面的max和我们前端传送过来的pageSize是相同的都是为每页显示的条数。但是offset和pageNo不是相同的所以需要转换。

转换的公式:

offset=(pageNo-1)*pageSize

中文乱码

url：jdbc:mysql:///jdbc?characterEncoding=utf-8&useUnicode=true

第三章 PreparedStatement接口

1. 它是扩展了Statement的接口(PreparedStatement本身也是接口)

2. 功能比Statement更强大

3. 可以动态地提供/接受参数

4. 创建PreparedStatement对象 --> conn.prepareStatement(通过Connection对象获取)

5. 可以使用占位符(?)来进行数据绑定

6. 通过stmt.setInt(1, 35);绑定参数

添加用户

//①加载数据库驱动

Class.forName("com.mysql.jdbc.Driver");

//②创建数据库连接

Connection conn = DriverManager.getConnection("jdbc:mysql:///jdbc?characterEncoding=utf-8","rooe","root");

//③准备sql，带有（?）占位符

String sql="INSERT INTO user(user_name,price,create_time) VALUES(?,?,?)";

//④预编译

PreparedStatement prepareStatement = conn.prepareStatement(sql);

//⑤绑定参数(参数的下标从1开始而不是像数组或者集合从0开始)

prepareStatement.setString(1, "admin");

prepareStatement.setDouble(2, 100.12);

prepareStatement.setDate(3, new Date(new java.util.Date().getTime()));

//⑥执行进行绑定参数之后的sql语句

int row = prepareStatement.executeUpdate();

System.out.println("row:"+row);

其余(删改查)操作自己完成

第四章 JDBC的事务处理

jdbc默认情况下每执行完一条SQL就会提交到数据库一次,因为jdbc默认提交事务的方式是自动提交,但是在正常的业务逻辑下,一个业务正常要执行多条sql语句,如果每执行完一条sql都进行事务提交就很容易出现前后数据不一致的问题,所以要保证这一组的sql语句操作都在一个事务管理中,所以我们要取消JDBC的默认事务提交

1. 使用Connection对象的setAutoCommit()方法关闭他的自动提交

2. 手动提交事务调用connection的commit()方法

3. 事务回滚调用connection的rollback()方法

第五章 JDBC批量处理

使用JDBC进行批处理的对象有两个:

Statement

PreparedStatement

Statement

介绍

①使用Statement对象批量添加要执行的SQL语句,事例如下:

statement.addBatch(sql1);

statement.addBatch(sql2);

statement.addBatch(sql3);

statement.addBatch(sql4);

②执行批处理SQL语句：statement.executeBatch();

③清除批处理命令：statement.clearBatch();

代码

Statement statement = connection.createStatement();

//批量准备SQL

String sql1="INSERT INTO user(user_name,price,create_time) VALUES('AA',12.12,NOW())";

String sql2="INSERT INTO user(user_name,price,create_time) VALUES('BB',13.12,NOW())";

String sql3="INSERT INTO user(user_name,price,create_time) VALUES('CC',14.12,NOW())";

String sql4="DELETE FROM user WHERE user_id=100";

//批量添加SQL

statement.addBatch(sql1);

statement.addBatch(sql2);

statement.addBatch(sql3);

statement.addBatch(sql4);

//批量执行

statement.executeBatch();

PreparedStatement

代码

String sql="INSERT INTO user(user_name,price,create_time) VALUES(?,?,?)";

PreparedStatement prepareStatement = connection.prepareStatement(sql);

//绑定参数1

prepareStatement.setString(1, "AA");

prepareStatement.setDouble(2, 12.12);

prepareStatement.setDate(2, new Date(new java.util.Date().getTime()));

prepareStatement.addBatch();

//绑定参数2

prepareStatement.setString(1, "BB");

prepareStatement.setDouble(2, 12.12);

prepareStatement.setDate(2, new Date(new java.util.Date().getTime()));

prepareStatement.addBatch();

//执行

prepareStatement.executeBatch();

Statement和PrepareStatement的优缺点:

1.Statement可以添加不同的SQL语句INSERT UPDATE DELETE可以同时进行批处理,但是效率相对较差

2.PrepareStatement执行同一条SQL不同参数的SQL语句，但是效率相对较高

第六章 JDBC存储大字段到MYSQL(了解)

6.1 大字段介绍

对于字段长度要求超过 255 个的情况下，MySQL 提供了 TEXT 和 BLOB 两种类型。根据存储数据的大小，它们都有不同的子类型。这些大型的数据用于存储文本块或图像、声音文件等二进制数据类型

6.2 大字段类型

4种文本: TINYTEXT(0-255字节)、TEXT(0-65535字节)、MEDIUMTEXT(0-16777215字节) 和 LONGTEXT(0-4294967295字节)

4种二进制: TINYBLOB(0-255字节)、BLOB(0-65535字节)、MEDIUMBLOB(0-16777215字节) 和 LONGBLOB(0-4294967295字节)

6.3 具体使用图示

</center>

6.4 大字段的操作

创建保存大字段的表

CREATE TABLE user(

`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',

`image` MEDIUMBLOB COMMENT '图片',

`article` TEXT NOT NULL COMMENT '大字段文本'

)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

存储图片/文本的例子

* 保存大字段数据

@Test

public void test01() throws ClassNotFoundException, SQLException, IOException {

//加载数据库驱动

Class.forName("com.mysql.jdbc.Driver");

//获取数据库连接

Connection conn = DriverManager.getConnection("jdbc:mysql:///bigdata?characterEncoding=utf8", "root", "root");

//准备sql语句

String sql="INSERT INTO user(image,article) VALUES(?,?)";

//获取preparedStatement对象

PreparedStatement ps = conn.prepareStatement(sql);

//绑定参数

//获取二进制流

InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("mysql.jpg");

ps.setBinaryStream(1, inputStream, inputStream.available());

//获取文本流

File file = new File("mysql.sql");

FileReader reader = new FileReader(file);

ps.setCharacterStream(2, reader, file.length());

ps.executeUpdate();

ps.close();

conn.close();

}

读取图片/文本的例子

* 查询大字段数据

@Test

public void test02() throws SQLException, ClassNotFoundException, IOException {

//加载数据库驱动

Class.forName("com.mysql.jdbc.Driver");

//获取数据库连接

Connection conn = DriverManager.getConnection("jdbc:mysql:///bigdata?characterEncoding=utf8", "root", "root");

//准备sql语句

String sql="SELECT id,image,article FROM user WHERE id=?";

//获取preparedStatement对象

PreparedStatement ps = conn.prepareStatement(sql);

ps.setInt(1, 1);

ResultSet resultSet = ps.executeQuery();

if(resultSet.next()) {

InputStream binaryStream = resultSet.getBinaryStream("image");

Reader characterStream = resultSet.getCharacterStream("article");

FileOutputStream outputStream = new FileOutputStream(new File("1.jpg"));

byte[] b = new byte[1024];

int len_=0;

while((len_=binaryStream.read(b))!=-1) {

outputStream.write(b, 0, len_);

}

System.out.println("=============");

char[] c = new char[512];

int len=0;

while((len=characterStream.read(c))!=-1) {

System.out.println(new String(c, 0,len));

}

第七章封装JDBC工具类

封装获取和关闭数据库连接的工具类

①获取数据库连接

--加载数据库驱动

--获取数据库连接

②关闭数据库连接

--关闭ResultSet

--关闭PreparedStatement

--关闭Connection

代码

public class JdbcUtils {

private static final String URL="jdbc:mysql:///test";

private static final String USER="root";

private static final String PASSWORD="root";

private static Connection connection=null;

static {

try {

connection = DriverManager.getConnection(URL, USER, PASSWORD);

} catch (SQLException e) {

e.printStackTrace();

}

/**

* 获取数据库连接

public static Connection getConnection() {

return connection;

}

/**

* 关闭数据库连接

public static void close(Connection conn) {

if(conn!=null) {

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

/**

* 关闭连接

* @param conn

* @param ps

public static void close(Connection conn,PreparedStatement ps) {

if(ps!=null) {

try {

ps.close();

} catch (SQLException e) {

e.printStackTrace();

}

if(conn!=null) {

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

/**

* 关闭连接

* @param conn

* @param ps

* @param rs

public static void close(Connection conn,PreparedStatement ps,ResultSet rs) {

if(rs!=null) {

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

if(ps!=null) {

try {

ps.close();

} catch (SQLException e) {

e.printStackTrace();

}

if(conn!=null) {

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

封装操作数据库Dao层的通用类

查询单个对象

* 查询单个对象（采用JDK原生的API方式调用反射接口进行返回对象的封装）

public T get(Connection conn,String sql,Class<T> clazz,Object ...args) throws SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException, SecurityException {

//声明返回值

T t=null;

//将SQL进行预编译

PreparedStatement ps = conn.prepareStatement(sql);

ResultSet rs = null;

if(args!=null && args.length>0) {

//进行参数绑定

for(int i=0;i

ps.setObject(i+1, args[i]);

}

rs=ps.executeQuery();

}else {

//执行绑定参数之后的SQL语句返回结果集对象

rs=ps.executeQuery();

}

//获取描述结果集对象的元数据信息

ResultSetMetaData rsmd = rs.getMetaData();

//获取总列数

int count = rsmd.getColumnCount();

//如果存在结果集那么开始进行封装

if(rs.next()) {

//通过反射创建对象

t = clazz.newInstance();

//循环获取列对象

for(int i=0;i

//获取列名(如果有别名获取别名，没有别名获取列名)

String columnName = rsmd.getColumnLabel(i+1);

//获取列值

Object columnValue = rs.getObject(columnName);

//通过反射获取clazz对象里描述属性的Field对象

Field field = clazz.getDeclaredField(columnName);

//开启权限控制

field.setAccessible(true);

//给T对象属性赋值

field.set(t, columnValue);

}

return t;

}

查询对象列表

* 查询对象列表

* 不采用原生反射API进行对象封装而是采用第三方提供的工具类进行封装

* commons-beanutils-1.8.0.jar

* commons-logging-1.1.1.jar

* 需要这两个jar包,进行对象封装

public List<T> getList(Connection conn,String sql,Class<T> clazz,Object ...args) throws SQLException, InstantiationException, IllegalAccessException, InvocationTargetException, NoSuchMethodException {

//声明返回值

List list = new ArrayList<>();

//将SQL进行预编译

PreparedStatement ps = conn.prepareStatement(sql);

ResultSet rs = null;

if(args!=null && args.length>0) {

//进行参数绑定

for(int i=0;i

ps.setObject(i+1, args[i]);

}

//执行绑定参数之后的SQL语句返回结果集对象

rs=ps.executeQuery();

}else {

rs=ps.executeQuery();

}

//获取描述结果集对象的元数据信息

ResultSetMetaData rsmd = rs.getMetaData();

//获取总列数

int count = rsmd.getColumnCount();

//如果存在结果集那么开始进行封装

while(rs.next()) {

T t = clazz.newInstance();

for(int i=0;i

//获取列名

String columnName = rsmd.getColumnLabel(i+1);

//获取列值

Object columnValue = rs.getObject(columnName);

//封装数据到对象中

PropertyUtils.setProperty(t, columnName, columnValue);

}

list.add(t);

}

return list;

}

第八章数据库连接池

8.1 连接池介绍

数据库连接池的概念：负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个连接来操作数据库.

8.2 常见连接池

C3P0:比较古老的数据库连接池

官网地址: https://www.mchange.com/projects/c3p0/

C3P0提供了多种创建数据库连接的方式,根据官网任选其一即可

Druid:alibaba的基于Java的高效的数据库连接池

官网地址: https://github.com/alibaba/druid/wiki/%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98

8.3 C3P0连接池使用

ComboPooledDataSource pool = new ComboPooledDataSource();

pool.setDriverClass("com.mysql.jdbc.Driver");

pool.setJdbcUrl("jdbc:mysql://localhost:3306/jdbc");

pool.setUser("root");

pool.setPassword("root");

第九章 DBUtils工具类

9.1 DBUtils介绍

DBUtils是apache的一个组件,算是一个轻量级的JDBC框架,官网地址

https://commons.apache.org/proper/commons-dbutils/

DbUtils是一个非常小的类库，不需要花很长的时间去看他的API类或者是接口，只需要知道QueryRunner和ReulthSthand两个/接口类即可

9.2 DBUtils使用

通过BeanHandler查询一个ResultSet返回一个JavaBean对象(查询单个对象)

QueryRunner run = new QueryRunner(dataSource);

ResultSetHandler<Person> h = new BeanHandler<Person>(Person.class);

Person p = run.query("SELECT * FROM Person WHERE name=?", h, "John Doe");

通过BeanListHandler查询所有的ResultSet返回一组JavaBean列表

QueryRunner run = new QueryRunner(dataSource);

ResultSetHandler<List<Person>> h = new BeanListHandler<Person>(Person.class);

List<Person> persons = run.query("SELECT * FROM Person", h);

第十章多线程安全问题

当前端多个用户发送多个请求的时候,每一个请求都会建立一个数据库连接,而一个用户请求可能执行多条SQL语句,多次操作数据库,在执行多条SQL语句时怎么保证一个用户请求在一个事务管理内，如果不能保证在一个事务管理中就会出现线程安全问题，造成前后数据的不一致。保证线程安全的主要任务就是保证一个用户请求中执行的多条SQL语句在一个数据库连接中即可.还有一种情况实在使用数据库连接池时候，由于数据库连接池的链接是可以共用的，在一个用户请求过来的时候由于有异常产生而需要事物回滚操作，但是在事务还没有来得及回滚的时候其他的请求使用了这个连接池里的连接，进行了提交，这个时候在回滚就会没有效果，造成的数据出错

解决方案

public class JdbcUtil {

private static ThreadLocal pool = new ThreadLocal<>();

/**

* 获取当前请求线程上的Connection

* @return

public static Connection getConnection() {

Connection conn = pool.get();

if(conn==null) {

try {

Class.forName("com.mysql.jdbc.Driver");

conn = DriverManager.getConnection("", "", "");

pool.set(conn);

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

return conn;

}

/**

* 关闭当前请求线程上的Connection

public static void close(){

Connection conn = pool.get();

conn.close();//关闭连接

pool.remove();//移除连接

}