全站资源开放下载,感谢广大网友的支持
链接失效请移步职涯宝平台的学习路线|资源下载分类
支持用户留言评论_客服实时在线_问题解决更快
非盈利平台

非盈利平台

只为分享一些优质内容

Java帮帮-微信公众号

Java帮帮-微信公众号

将分享做到极致

微信小程序

微信小程序

更方便的阅读

百度小程序

百度小程序

搜索便捷阅读

支付宝小程序

支付宝小程序

支付也能阅读

程序员生活志-公众号

程序员生活志-公众号

程序员生活学习圈,互联网八卦黑料

支付宝赞助-Java帮帮社区
微信赞助-Java帮帮社区

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

83
发表时间:2018-09-07 17:22

第三十天-加强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("删除失败,请重试");

}

}