第三十天-加强2-多表查询&JDBC&连接池&DBUtils&综合案例【悟空教程】

第三十天-加强2-多表查询&JDBC&连接池&DBUtils&综合案例【悟空教程】

第6天多表关系实战&多表查询

第19章 多表关系实战

19.1 实战1：省和市

• 方案1：多张表，一对多

• 方案2：一张表，自关联一对多

19.2 实战2：用户和角色

• 多对多关系

19.3 实战3：角色和权限

• 多对多关系

19.4 实战4：客户和联系人(可选)

• 一对多：一个客户服务于多个联系人

第20章 多表查询

CREATE TABLE category (

cid VARCHAR(32) PRIMARY KEY ,

cname VARCHAR(50)

);

CREATE TABLE products(

pid VARCHAR(32) PRIMARY KEY ,

pname VARCHAR(50),

price INT,

flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架

category_id VARCHAR(32),

CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid)

);

20.1 初始化数据

#分类

INSERT INTO category(cid,cname) VALUES('c001','家电');

INSERT INTO category(cid,cname) VALUES('c002','服饰');

INSERT INTO category(cid,cname) VALUES('c003','化妆品');

#商品

INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');

INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');

INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');

20.2 多表查询

1. 交叉连接查询(基本不会使用-得到的是两个表的乘积) [了解]

• 语法：select * from A,B;

2. 内连接查询(使用的关键字 inner join -- inner可以省略)

• 隐式内连接：select * from A,B where 条件;
• 显示内连接：select * from A inner join B on 条件;

3. 外连接查询(使用的关键字 outer join -- outer可以省略)

• 左外连接：left outer join
  • select * from A left outer join B on 条件;
• 右外连接：right outer join
  • select * from A right outer join B on 条件;

#1.查询哪些分类的商品已经上架

#隐式内连接

SELECT DISTINCT c.cname FROM category c , products p

WHERE c.cid = p.category_id AND p.flag = '1';

#内连接

SELECT DISTINCT c.cname FROM category c

INNER JOIN products p ON c.cid = p.category_id

WHERE p.flag = '1';

#2.查询所有分类商品的个数

#左外连接

INSERT INTO category(cid,cname) VALUES('c004','奢侈品');

SELECT cname,COUNT(category_id) FROM category c

LEFT OUTER JOIN products p

ON c.cid = p.category_id

GROUP BY cname;

20.3 子查询

子查询：一条select语句结果作为另一条select语法一部分（查询条件，查询结果，表等）。

select ....查询字段 ... from ... 表.. where ... 查询条件

#3 子查询, 查询“化妆品”分类上架商品详情

#隐式内连接

SELECT p.* FROM products p , category c

WHERE p.category_id=c.cid AND c.cname = '化妆品';

#子查询

##作为查询条件

SELECT * FROM products p

WHERE p.category_id =

(

SELECT c.cid FROM category c

WHERE c.cname='化妆品'

);

##作为另一张表

SELECT * FROM products p ,

(SELECT * FROM category WHERE cname='化妆品') c

WHERE p.category_id = c.cid;

#查询“化妆品”和“家电”两个分类上架商品详情

SELECT * FROM products p

WHERE p.category_id in

(

SELECT c.cid FROM category c

WHERE c.cname='化妆品' or c.name='家电'

);

第7天JDBC回顾

第21章 JDBC

21.1 JDBC概述

JDBC（Java Data Base Connectivity,java数据库连接）是一种用于执行SQL语句的Java API。JDBC是Java访问数据库的标准规范，可以为不同的关系型数据库提供统一访问，它由一组用Java语言编写的接口和类组成。

JDBC需要连接驱动，驱动是两个设备要进行通信，满足一定通信数据格式，数据格式由设备提供商规定，设备提供商为设备提供驱动软件，通过软件可以与该设备进行通信。

今天我们使用的是mysql的驱动mysql-connector-java-5.1.37-bin.jar

21.2 JDBC原理

Java提供访问数据库规范称为JDBC，而生产厂商提供规范的实现类称为驱动。

JDBC是接口，驱动是接口的实现，没有驱动将无法完成数据库连接，从而不能操作数据库！每个数据库厂商都需要提供自己的驱动，用来连接自己公司的数据库，也就是说驱动一般都由数据库生成厂商提供。

21.3 JDBC入门案例

21.3.1 准备数据

之前我们学习了sql语句的使用，并创建的分类表category，今天我们将使用JDBC对分类表进行增删改查操作。

#创建数据库

create database mydb;

#使用数据库

use mydb;

###创建分类表

create table category(

cid int PRIMARY KEY AUTO_INCREMENT ,

cname varchar(100)

);

#初始化数据

insert into category (cname) values('家电');

insert into category (cname) values('服饰');

insert into category (cname) values('化妆品');

21.3.2 导入驱动jar包

创建lib目录，用于存放当前项目需要的所有jar包

选择jar包，右键执行build path / Add to Build Path

21.3.3 开发步骤

1. 注册驱动.

2. 获得连接.

3. 获得语句执行平台

4. 执行sql语句

5. 处理结果

6. 释放资源.

21.3.4 案例实现

21.4 API详解

21.4.1 API详解：注册驱动

代码：Class.forName("com.mysql.jdbc.Driver");

JDBC规范定义驱动接口：java.sql.Driver，MySql驱动包提供了实现类：com.mysql.jdbc.Driver

DriverManager工具类，提供注册驱动的方法 registerDriver()，方法的参数是java.sql.Driver，所以我们可以通过如下语句进行注册:

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

以上代码不推荐使用，存在两方面不足

1. 硬编码，后期不易于程序扩展和维护

2. 驱动被注册两次。

通常开发我们使用Class.forName() 加载一个使用字符串描述的驱动类。

如果使用Class.forName()将类加载到内存，该类的静态代码将自动执行。

通过查询com.mysql.jdbc.Driver源码，我们发现Driver类“主动”将自己进行注册

public class Driver extends NonRegisteringDriver implements java.sql.Driver {

static {

try {

java.sql.DriverManager.registerDriver(new Driver());

} catch (SQLException E) {

throw new RuntimeException("Can't register driver!");

}

}

……

}

21.4.2 API详解：获得链接

代码：Connection con = DriverManager.getConnection (“jdbc:mysql://localhost:3306/mydb”,”root”,”root”);

获取连接需要方法 DriverManager.getConnection(url,username,password)，三个参数分别表示，url 需要连接数据库的位置（网址）user用户名 password 密码

url比较复杂，下面是mysql的url：

jdbc:mysql://localhost:3306/mydb

JDBC规定url的格式由三部分组成，每个部分中间使用冒号分隔。

• 第一部分是jdbc，这是固定的；
• 第二部分是数据库名称，那么连接mysql数据库，第二部分当然是mysql了；
• 第三部分是由数据库厂商规定的，我们需要了解每个数据库厂商的要求，mysql的第三部分分别由数据库服务器的IP地址（localhost）、端口号（3306），以及DATABASE名称(mydb)组成。

21.4.3 API详解：获得语句执行平台

String sql = "某SQL语句";

获取Statement语句执行平台：Statement stmt = con.createStatement();

常用方法：

• int executeUpdate(String sql); --执行insert update delete语句.
• ResultSet executeQuery(String sql); --执行select语句.
• boolean execute(String sql); --执行select返回true 执行其他的语句返回false.

21.4.4 API详解：处理结果集（执行insert、update、delete无需处理）

ResultSet实际上就是一张二维的表格，我们可以调用其boolean next()方法指向某行记录，当第一次调用next()方法时，便指向第一行记录的位置，这时就可以使用ResultSet提供的getXXX(int col)方法(与索引从0开始不同个，列从1开始)来获取指定列的数据：

rs.next();//指向第一行

rs.getInt(1);//获取第一行第一列的数据

常用方法：

• Object getObject(int index) / Object getObject(String name) 获得任意对象
• String getString(int index) / Object getObject(String name) 获得字符串
• int getInt(int index) / Object getObject(String name) 获得整形
• double getDouble(int index) / Object getObject(String name) 获得双精度浮点型

21.4.5 API详解：释放资源

与IO流一样，使用后的东西都需要关闭！关闭的顺序是先得到的后关闭，后得到的先关闭。

rs.close();

stmt.close();

con.close();

21.5 JDBC工具类

“获得数据库连接”操作，将在以后的增删改查所有功能中都存在，可以封装工具类JDBCUtils。提供获取连接对象的方法，从而达到代码的重复利用。

该工具类提供方法：public static Connection getConn ()。代码如下：

21.6 JDBC增删改查操作

21.6.1 插入

21.6.2 修改

21.6.3 删除

21.6.4 通过id查询详情

21.6.5 查询所有

21.7 预处理对象

21.7.1 SQL注入问题

SQL注入：用户输入的内容作为了SQL语句语法的一部分，改变了原有SQL真正的意义。

假设有登录案例SQL语句如下:

SELECT * FROM 用户表 WHERE NAME = 用户输入的用户名 AND PASSWORD = 用户输的密码;

此时，当用户输入正确的账号与密码后，查询到了信息则让用户登录。但是当用户输入的账号为XXX 密码为：XXX’ OR ‘a’=’a时，则真正执行的代码变为：

SELECT * FROM 用户表 WHERE NAME = ‘XXX’ AND PASSWORD =’ XXX’ OR ’a’=’a’;

此时，上述查询语句时永远可以查询出结果的。那么用户就直接登录成功了，显然我们不希望看到这样的结果，这便是SQL注入问题。

为此，我们使用PreparedStatement来解决对应的问题。

21.7.2 API详解：预处理对象

PreparedStatement预处理对象，处理的每条sql语句中所有的实际参数，都必须使用占位符?替换。

String sql = "select * from user where username = ? and password = ?";

PreparedStatement使用，需要通过以下3步骤完成：

1. PreparedStatement预处理对象代码：

#获得预处理对象，需要提供已经使用占位符处理后的SQL语句

PreparedStatement psmt = conn.prepareStatement(sql)

2. 设置实际参数

void setXxx(int index, Xxx xx) 将指定参数设置指定类型的值

参数1：index 实际参数序列号，从1开始。

参数2：xxx 实际参数值，xxx表示具体的类型。

例如：

setString(2, "1234") 把SQL语句中第2个位置的占位符?替换成实际参数 "1234"

3. 执行SQL语句:

int executeUpdate(); --执行insert update delete语句.

ResultSet executeQuery(); --执行select语句.

boolean execute(); --执行select返回true 执行其他的语句返回false.

21.7.3 插入

21.7.4 更新

21.7.5 删除

21.7.6 查询所有

21.7.7 通过id查询详情

第8天连接池&DButils回顾与加强

第22章 连接池

实际开发中“获得连接”或“释放资源”是非常消耗系统资源的两个过程，为了解决此类性能问题，通常情况我们采用连接池技术，来共享连接Connection。这样我们就不需要每次都创建连接、释放连接了，这些操作都交给了连接池

22.1 连接池概述

• 概念

用池来管理Connection，这样可以重复使用Connection。有了池，所以我们就不用自己来创建Connection，而是通过池来获取Connection对象。当使用完Connection后，调用Connection的close()方法也不会真的关闭Connection，而是把Connection“归还”给池。池就可以再利用这个Connection对象了。

• 规范

Java为数据库连接池提供了公共的接口：javax.sql.DataSource，各个厂商需要让自己的连接池实现这个接口。这样应用程序可以方便的切换不同厂商的连接池！

常见的连接池：DBCP、C3P0。

接下来，我们就详细的学习连接池。

22.2 C3P0连接池

C3P0开源免费的连接池！目前使用它的开源项目有：Spring、Hibernate等。使用第三方工具需要导入jar包，c3p0使用时还需要添加配置文件 c3p0-config.xml

22.2.1 导入jar包

我们使用的0.9.2版本，需要导入2个jar包

22.2.2 核心类

22.2.3 配置文件

• 配置文件名称：c3p0-config.xml （固定）
• 配置文件位置：src （类路径）
• 配置文件内容：命名配置

<c3p0-config>

<!-- 命名的配置 -->

<named-config name="javahelp">

<!-- 连接数据库的4项基本参数 -->

<property name="driverClass">com.mysql.jdbc.Driver</property>

<property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/webdb</property>

<property name="user">root</property>

<property name="password">root</property>

<!-- 如果池中数据连接不够时一次增长多少个 -->

<property name="acquireIncrement">5</property>

<!-- 初始化连接数 -->

<property name="initialPoolSize">20</property>

<!-- 最小连接受 -->

<property name="minPoolSize">10</property>

<!-- 最大连接数 -->

<property name="maxPoolSize">40</property>

<!-- -JDBC的标准参数，用以控制数据源内加载的PreparedStatements数量 -->

<property name="maxStatements">0</property>

<!-- 连接池内单个连接所拥有的最大缓存statements数 -->

<property name="maxStatementsPerConnection">5</property>

</named-config>

</c3p0-config>

• 配置文件内容：默认配置

<c3p0-config>

<!-- 默认配置，如果没有指定则使用这个配置 -->

<default-config>

<property name="driverClass">com.mysql.jdbc.Driver</property>

<property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/webdb</property>

<property name="user">root</property>

<property name="password">root</property>

<property name="checkoutTimeout">30000</property>

<property name="idleConnectionTestPeriod">30</property>

<property name="initialPoolSize">10</property>

<property name="maxIdleTime">30</property>

<property name="maxPoolSize">100</property>

<property name="minPoolSize">10</property>

<property name="maxStatements">200</property>

<user-overrides user="test-user">

<property name="maxPoolSize">10</property>

<property name="minPoolSize">1</property>

<property name="maxStatements">0</property>

</user-overrides>

</default-config>

22.2.4 常见配置项

22.2.5 编写工具类

C3P0提供核心工具类：ComboPooledDataSource，如果要使用连接池，必须创建该类的实例对象。

• new ComboPooledDataSource(“名称”); 使用配置文件“命名配置”
  • <named-config name="javahelp">
• new ComboPooledDataSource(); 使用配置文件“默认配置”
  • <default-config>

22.3 DBCP连接池

DBCP也是一个开源的连接池，是Apache Common成员之一，在企业开发中也比较常见，tomcat内置的连接池。

22.3.1 导入jar包

22.3.2 核心类

22.3.3 配置文件

• 配置文件名称：*.properties
• 配置文件位置：任意，建议src（classpath/类路径）
• 配置文件内容：properties不能编写中文，不支持在STS中修改，必须使用记事本修改内容，否则中文注释就乱码了

#连接设置

driverClassName=com.mysql.jdbc.Driver

url=jdbc:mysql://localhost:3306/webdb

username=root

password=root

#<!-- 初始化连接 -->

initialSize=10

#最大连接数量

maxActive=50

#<!-- 最大空闲连接 -->

maxIdle=20

#<!-- 最小空闲连接 -->

minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->

maxWait=60000

#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;]

#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。

connectionProperties=useUnicode=true;characterEncoding=gbk

#指定由连接池所创建的连接的自动提交（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

22.3.4 常见配置项

参考文档：http://commons.apache.org/proper/commons-dbcp/configuration.html

22.3.5 编写工具类

第23章 DBUtils

如果只使用JDBC进行开发，我们会发现冗余代码过多，为了简化JDBC开发，本案例我们讲采用apache commons组件一个成员：DBUtils。

DBUtils就是JDBC的简化开发工具包。需要项目导入commons-dbutils-1.6.jar才能够正常使用DBUtils工具。

23.1 概述

DBUtils是java编程中的数据库操作实用工具，小巧简单实用。

DBUtils封装了对JDBC的操作，简化了JDBC操作，可以少写代码。

Dbutils三个核心功能介绍

• QueryRunner中提供对sql语句操作的API.
• ResultSetHandler接口，用于定义select操作后，怎样封装结果集.
• DbUtils类，它就是一个工具类,定义了关闭资源与事务处理的方法

23.2 准备数据

• 创建表：

create table product(

pid int primary key,

pname varchar(20),

price double,

category_id varchar(32)

);

• 插入表记录：

INSERT INTO product(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');

INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');

INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');

INSERT INTO product(pid,pname,price,category_id) VALUES(4,'JACK JONES',800,'c002');

INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');

INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');

INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');

INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');

INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');

INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');

INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');

INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005');

INSERT INTO product(pid,pname,price,category_id) VALUES(13,'果9',1,NULL);

23.3 QueryRunner核心类介绍

23.3.1 提供数据源

• 构造方法
  • QueryRunner(DataSource) 创建核心类，并提供数据源，内部自己维护Connection
• 普通方法
  • update(String sql , Object ... params) 执行DML语句
  • query(String sql , ResultSetHandler , Object ... params) 执行DQL语句，并将查询结果封装到对象中。

23.3.2 提供连接

• 构造方法
  • QueryRunner() 创建核心类，没有提供数据源，在进行具体操作时，需要手动提供Connection
• 普通方法
  • update(Connection conn , String sql , Object ... params) 使用提供的Connection，完成DML语句
  • query(Connection conn , String sql , ResultSetHandler , Object ... params) 使用提供的Connection，执行DQL语句，并将查询结果封装到对象中。

23.4 QueryRunner实现添加、更新、删除操作

• update(String sql, Object... params) ，用来完成表数据的增加、删除、更新操作

23.4.1 添加

23.4.2 更新

23.4.3 删除

23.5 QueryRunner实现查询操作

• query(String sql, ResultSetHandler<T> rsh, Object... params) ，用来完成表数据的查询操作

23.5.1 ResultSetHandler结果集处理类

23.5.2 JavaBean

JavaBean就是一个类，在开发中常用语封装数据。具有如下特性

1. 需要实现接口：java.io.Serializable ，通常实现接口这步骤省略了，不会影响程序。

2. 提供私有字段：private 类型 字段名;

3. 提供getter/setter方法：

4. 提供无参构造

23.5.3 BeanHandler

23.5.4 BeanListHandler

23.5.5 ScalarHander

23.5.6 MapHandler

23.5.7 MapListHandler

23.5.8 ArrayHandler

23.5.9 ArrayListHandler

23.5.10 KeyedHandler

23.5.11 ColumnListHandler

23.6 总结

• DBUtils工具
• 作用：简化JDBC的操作
• 常用类与方法
  • QueryRunner 用来执行SQL语句对象
    • update(Connection conn, String sql, Object… params) 插入表记录、更新表记录、删除表记录
    • query(Connection conn, String sql, ResultSetHandler handler, Object… params) 查询表记录
    • ResultSetHandler 处理结果集的对象

第9天JDBC高级开发事务

第24章 JDBC高级开发事务

24.1 事务管理

24.1.1 事务概述

• 事务指的是逻辑上的一组操作,组成这组操作的各个单元要么全都成功,要么全都失败.
• 事务作用：保证在一个事务中多次操作要么全都成功,要么全都失败.

24.1.2 mysql事务操作

• 准备数据

# 创建一个表：账户表.

create database webdb;

# 使用数据库

use webdb;

# 创建账号表

create table account(

id int primary key auto_increment,

name varchar(20),

money double

);

# 初始化数据

insert into account values (null,'jack',10000);

insert into account values (null,'rose',10000);

insert into account values (null,'tom',10000);

• 操作：
  • MYSQL中可以有两种方式进行事务的管理：
    • 自动提交：MySql默认自动提交。及执行一条sql语句提交一次事务。
    • 手动提交：先开启，再提交
  • 方式1：手动提交

start transaction;

update account set money=money-1000 where name='守义';

update account set money=money+1000 where name='凤儿';

commit;

#或者

rollback;

• 方式2：自动提交，通过修改mysql全局变量“autocommit”进行控制

show variables like '%commit%';

* 设置自动提交的参数为OFF:

set autocommit = 0; -- 0:OFF 1:ON

• 扩展：Oracle数据库事务不自动提交

24.1.3 JDBC事务操作

//事务模板代码

public void demo01() throws SQLException{

// 获得连接

Connection conn = null

try {

//#1 开始事务

conn.setAutoCommit(false);

//.... 加钱 ,减钱

//#2 提交事务

conn.commit();

} catch (Exception e) {

//#3 回滚事务

conn.rollback();

} finally{

// 释放资源

conn.close();

}

}

24.1.4 DBUtils事务操作

24.2 案例分析

• 开发中，常使用分层思想
  • 不同的层次结构分配不同的解决过程，各个层次间组成严密的封闭系统
  • 不同层级结构彼此平等
  • 分层的目的是：
    • 解耦
    • 可维护性
    • 可扩展性
    • 可重用性
• 不同层次，使用不同的包表示
  • cn.com.javahelp 公司域名倒写
  • cn.com.javahelp.dao dao层
  • cn.com.javahelp.service service层
  • cn.com.javahelp.domain javabean
  • cn.com.javahelp.utils 工具

24.3 代码实现

• 步骤1：编写入口程序

• 步骤2：编写AccountService

• 步骤3：编写AccountDao

public class AccountDao {

/**

* 付款的方法

* @param name

* @param money

* @throws SQLException

*/

public void outMoney(String name,double money) throws SQLException{

Connection conn = null;

PreparedStatement pstmt = null;

try{

// 获得连接:

conn = JDBCUtils.getConnection();

// 编写一个SQL:

String sql = "update account set money = money-? where name=?";

// 预编译SQL:

pstmt = conn.prepareStatement(sql);

// 设置参数:

pstmt.setDouble(1, money);

pstmt.setString(2, name);

// 执行SQL：

pstmt.executeUpdate();

}catch(Exception e){

e.printStackTrace();

}finally{

pstmt.close();

conn.close();

}

}

/**

* 收款的方法

* @param name

* @param money

* @throws SQLException

*/

public void inMoney(String name,double money) throws SQLException{

Connection conn = null;

PreparedStatement pstmt = null;

try{

// 获得连接:

conn = JDBCUtils.getConnection();

// 编写一个SQL:

String sql = "update account set money = money+? where name=?";

// 预编译SQL:

pstmt = conn.prepareStatement(sql);

// 设置参数:

pstmt.setDouble(1, money);

pstmt.setString(2, name);

// 执行SQL：

pstmt.executeUpdate();

}catch(Exception e){

e.printStackTrace();

}finally{

pstmt.close();

conn.close();

}

}

}

24.4 事务管理：传递Connection

• 修改service和dao，service将connection传递给dao，dao不需要自己获得连接

24.4.1 service层

24.4.2 dao层

/**

* 汇款

* @param outUser 汇款人

* @param money -

*/

public void outMoney(Connection conn, String outUser , int money){

//Connection conn = null;

PreparedStatement psmt = null;

ResultSet rs = null;

try {

//1 获得连接

//conn = JdbcUtils.getConnection();

//2 准备sql语句

String sql = "update account set money = money - ? where username = ?";

//3预处理

psmt = conn.prepareStatement(sql);

//4设置实际参数

psmt.setInt(1, money);

psmt.setString(2, outUser);

//5执行

int r = psmt.executeUpdate();

System.out.println(r);

} catch (Exception e) {

throw new RuntimeException(e);

} finally{

//6释放资源

JdbcUtils.closeResource(null, psmt, rs);

}

}

/**

* 收款

* @param inUser 收款人

* @param money +

*/

public void inMoney(Connection conn,String inUser , int money){

//Connection conn = null;

PreparedStatement psmt = null;

ResultSet rs = null;

try {

//1 获得连接

//conn = JdbcUtils.getConnection();

//2 准备sql语句

String sql = "update account set money = money + ? where username = ?";

//3预处理

psmt = conn.prepareStatement(sql);

//4设置实际参数

psmt.setInt(1, money);

psmt.setString(2, inUser);

//5执行

int r = psmt.executeUpdate();

System.out.println(r);

} catch (Exception e) {

throw new RuntimeException(e);

} finally{

//6释放资源

JdbcUtils.closeResource(null, psmt, rs);

}

}

24.5 提高：ThreadLocal

24.5.1 案例介绍

在“事务传递参数版”中，我们必须修改方法的参数个数，传递链接，才可以完成整个事务操作。如果不传递参数，是否可以完成？在JDK中给我们提供了一个工具类：ThreadLocal，此类可以在一个线程中共享数据。

24.5.2 相关知识：ThreadLocal

java.lang.ThreadLocal 该类提供了线程局部 (thread-local) 变量，用于在当前线程中共享数据。ThreadLocal工具类底层就是一个Map，key存放的当前线程，value存放需要共享的数据。

24.5.3 分析

24.5.4 实现

24.5.4.1 工具类JDBCUtils

//连接池

private static ComboPooledDataSource dataSource = new ComboPooledDataSource("javahelp");

//给当前线程绑定 连接

private static ThreadLocal<Connection> local = new ThreadLocal<Connection>();

/**

* 获得连接

* @return

*/

public static Connection getConnection(){

try {

//#1从当前线程中， 获得已经绑定的连接

Connection conn = local.get();

if(conn == null){

//#2 第一次获得，绑定内容 – 从连接池获得

conn = dataSource.getConnection();

//#3 将连接存 ThreadLocal

local.set(conn);

}

return conn; //获得连接

} catch (Exception e) {

//将编译时异常 转换 运行时 ， 以后开发中 运行时异常使用比较多的。

// * 此处可以编写自定义异常。

throw new RuntimeException(e);

// * 类与类之间 进行数据交换时，可以使用return返回值。也可以自定义异常返回值，调用者try{} catch(e){ e.getMessage() 获得需要的数据}

//throw new MyConnectionException(e);

}

}

24.5.4.2 service层

public void transfer(String outUser,String inUser,int money){

Connection conn =null;

try{

//1 获得连接

conn = JdbcUtils.getConnection();

//2 开启事务

conn.setAutoCommit(false);

accountDao.out(outUser, money);

//断电

//int i = 1 / 0;

accountDao.in(inUser, money);

//3 提交事务

conn.commit();

} catch (Exception e) {

try {

//回顾

if (conn != null) {

conn.rollback();

}

} catch (Exception e2) {

}

throw new RuntimeException(e);

} finally{

JdbcUtils.closeResource(conn, null, null);

}

}

24.5.4.3 dao层

/**

* 汇款

* @param outUser 汇款人

* @param money -

*/

public void out(String outUser , int money){

Connection conn = null;

PreparedStatement psmt = null;

ResultSet rs = null;

try {

//1 获得连接

conn = JdbcUtils.getConnection();

//2 准备sql语句

String sql = "update account set money = money - ? where username = ?";

//3预处理

psmt = conn.prepareStatement(sql);

//4设置实际参数

psmt.setInt(1, money);

psmt.setString(2, outUser);

//5执行

int r = psmt.executeUpdate();

System.out.println(r);

} catch (Exception e) {

throw new RuntimeException(e);

} finally{

//6释放资源--不能关闭连接

JdbcUtils.closeResource(null, psmt, rs);

}

}

/**

* 收款

* @param inUser 收款人

* @param money +

*/

public void in(String inUser , int money){

Connection conn = null;

PreparedStatement psmt = null;

ResultSet rs = null;

try {

//1 获得连接

conn = JdbcUtils.getConnection();

//2 准备sql语句

String sql = "update account set money = money + ? where username = ?";

//3预处理

psmt = conn.prepareStatement(sql);

//4设置实际参数

psmt.setInt(1, money);

psmt.setString(2, inUser);

//5执行

int r = psmt.executeUpdate();

System.out.println(r);

} catch (Exception e) {

throw new RuntimeException(e);

} finally{

//6释放资源--注意：不能关闭链接

JdbcUtils.closeResource(null, psmt, rs);

}

}

24.6 案例总结：事务总结

24.6.1 事务特性：ACID

• 原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
• 一致性（Consistency）事务前后数据的完整性必须保持一致。
• 隔离性（Isolation）事务的隔离性是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。
• 持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

24.6.2 并发访问问题

如果不考虑隔离性，事务存在3中并发访问问题。

1. 脏读：一个事务读到了另一个事务未提交的数据.

2. 不可重复读：一个事务读到了另一个事务已经提交(update)的数据。引发另一个事务，在事务中的多次查询结果不一致。

3. 虚读 /幻读：一个事务读到了另一个事务已经提交(insert)的数据。导致另一个事务，在事务中多次查询的结果不一致。

24.6.3 隔离级别：解决问题

• 数据库规范规定了4种隔离级别，分别用于描述两个事务并发的所有情况。

1. read uncommitted 读未提交，一个事务读到另一个事务没有提交的数据。

a) 存放：3个问题（脏读、不可重复读、虚读）。

b) 解决：0个问题

2. read committed 读已提交，一个事务读到另一个事务已经提交的数据。

a) 存放：2个问题（不可重复读、虚读）。

b) 解决：1个问题（脏读）

3. repeatable read :可重复读，在一个事务中读到的数据始终保持一致，无论另一个事务是否提交。

a) 存放：1个问题（虚读）。

b) 解决：2个问题（脏读、不可重复读）

4. serializable 串行化，同时只能执行一个事务，相当于事务中的单线程。

a) 存放：0个问题。

b) 解决：3个问题（脏读、不可重复读、虚读）

• 安全和性能对比
  • 安全性：serializable > repeatable read > read committed > read uncommitted
  • 性能 ： serializable < repeatable read < read committed < read uncommitted
• 常见数据库的默认隔离级别：
  • MySql：repeatable read
  • Oracle：read committed

24.6.4 演示

• 隔离级别演示参考：资料/隔离级别操作过程.doc【增强内容,了解】
• 查询数据库的隔离级别

show variables like '%isolation%';

或

select @@tx_isolation;

• 设置数据库的隔离级别
  • set session transaction isolation level 级别字符串
    • 级别字符串：read uncommitted、read committed、repeatable read、serializable
  • 例如：set session transaction isolation level read uncommitted;
• 读未提交：read uncommitted
  • A窗口设置隔离级别
  • AB同时开始事务
  • A 查询
  • B 更新，但不提交
  • A 再查询？-- 查询到了未提交的数据
  • B 回滚
  • A 再查询？-- 查询到事务开始前数据
• 读已提交：read committed
  • A窗口设置隔离级别
  • AB同时开启事务
  • A查询
  • B更新、但不提交
  • A再查询？--数据不变，解决问题【脏读】
  • B提交
  • A再查询？--数据改变，存在问题【不可重复读】
• 可重复读：repeatable read
  • A窗口设置隔离级别
  • AB 同时开启事务
  • A查询
  • B更新， 但不提交
  • A再查询？--数据不变，解决问题【脏读】
  • B提交
  • A再查询？--数据不变，解决问题【不可重复读】
  • A提交或回滚
  • A再查询？--数据改变，另一个事务
• 串行化：serializable
  • A窗口设置隔离级别
  • AB同时开启事务
  • A查询
  • B更新？--等待(如果A没有进一步操作，B将等待超时)
  • A回滚
  • B 窗口？--等待结束，可以进行操作

第10天综合案例&阶段总结

第25章 综合案例&阶段总结

25.1 案例需求

• 运行

• 查询所有

• 通过id查询详情

• 添加

• 修改

• 通过id删除

• 删除所有

25.2 案例分析

• 程序将划分层次
  • cn.com.javahelp.domain javaBean
  • cn.com.javahelp.utils 工具类
  • cn.com.javahelp.dao dao层
  • cn.com.javahelp.service service层（业务层，一般情况内容非常少，直接调用dao，只有与业务挂钩时才能体现出来的）

25.3 代码实现

25.3.1 准备数据

create database webdb;

use webdb;

CREATE TABLE products(

pid int PRIMARY KEY auto_increment ,

pname VARCHAR(50),

price INT,

flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架

category_id VARCHAR(32)

);

#商品

INSERT INTO products(pname,price,flag,category_id) VALUES('联想',5000,'1','c001');

INSERT INTO products(pname,price,flag,category_id) VALUES('海尔',3000,'1','c001');

INSERT INTO products(pname,price,flag,category_id) VALUES('雷神',5000,'1','c001');

INSERT INTO products(pname,price,flag,category_id) VALUES('JACK JONES',800,'1','c002');

INSERT INTO products(pname,price,flag,category_id) VALUES('真维斯',200,'1','c002');

INSERT INTO products(pname,price,flag,category_id) VALUES('花花公子',440,'1','c002');

INSERT INTO products(pname,price,flag,category_id) VALUES('劲霸',2000,'1','c002');

INSERT INTO products(pname,price,flag,category_id) VALUES('香奈儿',800,'1','c003');

INSERT INTO products(pname,price,flag,category_id) VALUES('相宜本草',200,'1','c003');

25.3.2 控制台输入

BufferedReader reader = new BufferedReader(new InputStreamReader(System.in));

while(true){

System.out.println("输入以下命令进行操作：");

System.out.println("C：创建、U：修改、D：删除、DA：删除所有、I：通过id查询、FA：查询所有、Q：退出");

String line = reader.readLine();

switch (line.toUpperCase()) {

case "C":

add(reader);

break;

case "U":

break;

case "D":

break;

case "DA":

break;

case "I":

break;

case "FA":

break;

case "Q":

System.out.println("欢迎下次使用");

System.exit(-1);

break;

default:

break;

}

}

25.3.3 查询所有

25.3.3.1 dao层

/**

* 查询所有

* @return

* @throws SQLException

*/

public List<Product> findAll() throws SQLException{

QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());

String sql = "SELECT * FROM products";

Object[] params = {};

return queryRunner.query(sql, new BeanListHandler<Product>(Product.class), params);

}

25.3.3.2 service层

/**

* 查询所有

* @return

* @throws SQLException

*/

public List<Product> findAll() throws SQLException{

return productDao.findAll();

}

25.3.3.3 入口

private static void findAll(BufferedReader reader) {

try {

ProductService productService = new ProductService();

List<Product> list = productService.findAll();

System.out.println("查询结果：");

for (Product product : list) {

System.out.println(product);

}

} catch (Exception e) {

System.out.println("查询异常，请稍后重试");

}

}

25.3.4 通过id查询详情

25.3.4.1 dao层

/**

* 查询详情

* @return

* @throws SQLException

*/

public Product findById(Integer pid) throws SQLException{

QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());

String sql = "SELECT * FROM products where pid = ?";

Object[] params = {pid};

return queryRunner.query(sql, new BeanHandler<Product>(Product.class), params);

}

25.3.4.2 service层

/**

* 通过id查询

* @param pid

* @return

* @throws SQLException

*/

public Product findById(Integer pid) throws SQLException{

return productDao.findById(pid);

}

25.3.4.3 入口

private static void findById(BufferedReader reader) {

try {

System.out.println("请输入查询商品编号：");

String pidStr = reader.readLine();

Integer pid = Integer.parseInt(pidStr);

ProductService productService = new ProductService();

Product product = productService.findById(pid);

System.out.println("查询结果：" + product);

} catch (Exception e) {

System.out.println("查询异常，请稍后重试");

}

}

25.3.5 添加

25.3.5.1 dao层

/**

* 添加

* @return

* @throws SQLException

*/

public void save(Product product) throws SQLException{

QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());

String sql = "INSERT INTO products(pname,price,flag,category_id) VALUES(?,?,?,?)";

Object[] params = {product.getPname(),product.getPrice(),product.getFlag(),product.getCategory_id()};

queryRunner.update(sql, params);

}

25.3.5.2 service层

/**

* 添加商品

* @param product

* @throws SQLException

*/

public void addProduct(Product product) throws SQLException{

productDao.save(product);

}

25.3.5.3 入口

private static void add(BufferedReader reader) {

try {

System.out.println("请输入商品名：");

String pname = reader.readLine();

System.out.println("请输入价格：");

String priceStr = reader.readLine();

int price = Integer.parseInt(priceStr);

String flag = "1";

String category_id = "c001";

Product product = new Product(pname, price, flag, category_id);

ProductService productService = new ProductService();

productService.addProduct(product);

System.out.println("添加成功");

} catch (Exception e) {

System.out.println("添加失败，请重试");

}

}

25.3.6 修改

25.3.6.1 dao层

/**

* 修改

* @return

* @throws SQLException

*/

public void update(Product product) throws SQLException{

QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());

String sql = "update products set pname=?,price=?,flag=?,category_id=? where pid=?";

Object[] params = {product.getPname(),product.getPrice(),product.getFlag(),

product.getCategory_id(),product.getPid()};

queryRunner.update(sql, params);

}

25.3.6.2 service层

/**

* 修改商品

* @param product

* @throws SQLException

*/

public void editProduct(Product product) throws SQLException{

productDao.update(product);

}

25.3.6.3 入口

private static void update(BufferedReader reader) {

try {

System.out.println("请输入编辑商品编号：");

String pidStr = reader.readLine();

Integer pid = Integer.parseInt(pidStr);

ProductService productService = new ProductService();

Product product = productService.findById(pid);

if(product == null){

System.out.println("修改商品已不存在");

return;

}

System.out.println("查询结果：" + product);

System.out.println("请输入修改的商品名：");

String pname = reader.readLine();

product.setPname(pname);

System.out.println("请输入修改的价格：");

String priceStr = reader.readLine();

int price = Integer.parseInt(priceStr);

product.setPrice(price);

productService.editProduct(product);

System.out.println("修改成功");

} catch (Exception e) {

System.out.println("修改失败，请重试");

}

}

25.3.7 通过id删除

25.3.7.1 dao层

/**

* 删除

* @return

* @throws SQLException

*/

public void delete(Integer pid) throws SQLException{

QueryRunner queryRunner = new QueryRunner();

String sql = "delete from products where pid=?";

Object[] params = {pid};

queryRunner.update(C3P0Utils.getConnection() , sql, params);

}

25.3.7.2 service层

public void delete(Integer pid) {

Connection conn = null;

try {

conn = JdbcUtils.getConnection();

//开启

conn.setAutoCommit(false);

productDao.delete(pid);

//提交

DbUtils.commitAndClose(conn);

} catch (Exception e) {

//回滚

DbUtils.rollbackAndCloseQuietly(conn);

//通知调用者

throw new RuntimeException(e);

}

}

25.3.7.3 入口

private static void delete(BufferedReader reader) {

try {

System.out.println("请输入删除商品编号：");

String pidStr = reader.readLine();

Integer pid = Integer.parseInt(pidStr);

ProductService productService = new ProductService();

Product product = productService.findById(pid);

System.out.println("将要删除的商品：" + product);

System.out.println("您确定要删除吗？请输入y：");

String yes = reader.readLine();

if("y".equalsIgnoreCase(yes)){

productService.delete(pid);

System.out.println("删除成功");

} else {

System.out.println("操作取消");

}

} catch (Exception e) {

System.out.println("删除失败，请重试");

}

}

25.3.8 批量删除

25.3.8.1 service层

/**

* 删除所有商品

* @param product

* @throws SQLException

*/

public void deleteAll(List<Integer> ids) throws SQLException{

Connection conn = null;

try {

conn = C3P0Utils.getConnection();

conn.setAutoCommit(false);

for(Integer id : ids) {

productDao.delete(id);

}

DbUtils.commitAndClose(conn);

} catch (Exception e) {

DbUtils.rollbackAndCloseQuietly(conn);

throw new RuntimeException("批量删除失败");

}

}

25.3.8.2 入口

private static void deleteAll(BufferedReader reader) {

try {

List<Integer> idList = new ArrayList<Integer>();

System.out.println("进入批量删除模式：(输入-1退出)");

ProductService productService = new ProductService();

while(true){

System.out.println("请输入删除商品编号：");

String pidStr = reader.readLine();

Integer pid = Integer.parseInt(pidStr);

if(pid == -1){

break;

}

Product product = productService.findById(pid);

if(product != null){

System.out.println("已标记要删除的商品：" + product);

idList.add(pid);

} else {

System.out.println("删除商品不存在");

}

}

System.out.println("您确定要删除被标记"+idList.size()+"个商品吗？请输入y：");

String yes = reader.readLine();

if("y".equalsIgnoreCase(yes)){

productService.deleteAll(idList);

System.out.println("删除成功");

} else {

System.out.println("操作取消");

}

} catch (Exception e) {

System.out.println("删除失败，请重试");

}

}