1 存储过程
1)用当地数据库语言,写的一段业务逻辑算法,并该算法存储在客户端
2)使用存储过程需要用于CallableStatement接口,同时需要使如下SQL命令调用:{call add_pro(?,?,?)}
3)对于存储过程的输出参数,需要注册:
cstmt.registerOutParameter(3,Types.INTEGER);
4)取得返回值时,需要按照输出参数的位置来取
编写存储过程得到CallableStatement,并调用存储过程:
CallableStatement cstmt= conn.prepareCall("{call demoSp(?, ?)}");
设置参数,注册返回值,得到输出
cstmt.registerOutParameter(2, Types.VARCHAR);
cstmt.setString(1, "abcdefg");
cstmt.execute();
System.out.println(cStmt.getString(2));
package cn.itcast.web.jdbc.dao;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Types;
import cn.itcast.web.jdbc.util.JdbcUtil;
//演示JDBC操作MySQL存储过程
public class Demo1 {
public static void main(String[] args) {
Connection conn = null;
//调用存储过程专用的接口
CallableStatement cstmt = null;
ResultSet rs = null;
//存储过程特定的语法
String sql = "{call add_pro(?,?,?)}";
try {
conn = JdbcUtil.getMySqlConnection();
cstmt = conn.prepareCall(sql);
//绑三个参数(前二个是输入,后一个是输出)
cstmt.setInt(1,100);
cstmt.setInt(2,200);
//注册一个输出参数,
//其中Types.INTEGER表示SQL与JDBC之前的是映射类型
cstmt.registerOutParameter(3,Types.INTEGER);
//调用存储过程
boolean flag = cstmt.execute();
System.out.println("flag="+flag);
//取得执行结果
int sum = cstmt.getInt(3);
System.out.println("sum="+sum);
} catch (Exception e) {
e.printStackTrace();
}finally{
JdbcUtil.close(rs);
JdbcUtil.close(cstmt);
JdbcUtil.close(conn);
}
}
}
2 事务的概念
1)每种数据库都有事务的支持,但支持强度不同
2)以MySQL为例,
启动事务
start transaction;
提交事务
commit;
回滚事务
rollback;
3)在事务范围内回滚是允许的,但如果commit后再回滚,无效
4)其实每条SQL都有事务存在,只是显示还隐藏而言,默认都是隐藏事务
5)事务的操作,必须争对同一个Connection。
6)事务的操作,可以设置一个回滚点,便于回滚到最近的回滚点处。
当Jdbc程序向数据库获得一个Connection对象时,默认情况下这个Connection对象会自动向数据库提交在它上面发送的SQL语句。若想关闭这种默认提交方式,让多条SQL在一个事务中执行,可使用下列语句:
JDBC控制事务语句
•Connection.setAutoCommit(false);
•Connection.rollback();
•Connection.commit();
设置事务回滚点
•Savepoint sp = conn.setSavepoint();
•Conn.rollback(sp);
•Conn.commit(); //回滚后必须要提交
3 事务的特性
1)原子性(A)事务是的各个操作是一个不可分割的子操作。必须将其看成一个整体,即原子操作
2)一致性(C)事务前后,由一个一致状态转移到另一个一致状态
*3)隔离性(I)事务中,每个线程操作同张表同记录时,相互分割
4)持久性(D)事务一旦生效,在没有操作该记录时情况下,永远保持不变
*4 三个缺点(违背隔离性)
1)脏读:一个线程看到了另一个线程未提交的数据,叫脏读
2)不可重复读:一个线程多次做查询操作,多次结果都不一致,叫不可重复读
上述二项,强调的是查询,内容变,但数量不变
3)幻读/虚读:
上述一项,强调的是插入,数量变
*5 事务的隔离级别(解药)
*static int TRANSACTION_READ_COMMITTED
指示不可以发生脏读的常量;不可重复读和虚读可以发生。
*static int TRANSACTION_REPEATABLE_READ
指示不可以发生脏读和不可重复读的常量;虚读可以发生。
static int TRANSACTION_SERIALIZABLE
指示不可以发生脏读、不可重复读和虚读的常量。
不可重复读
| read uncommitted | read committed | repeatable read | serializable |
脏读 | | 解决 | 解决 | 解决 |
不可重复读 | | | 解决 | 解决 |
幻读/虚读 | | | | 解决 |
总结:
项目中,对于select操作不需要事务,对于其它操作(update/delete/insert)操作需要事务。
package cn.itcast.web.jdbc.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Savepoint;
import cn.itcast.web.jdbc.util.JdbcUtil;
//JDBC显示操作事务的API
public class Demo2 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sqlA = "update account set salary=salary-1000 where name='aaa'";
String sqlB = "update account set salary=salary+1000 where name='bbb'";
String sqlC = "insert into account(name,salary) values('ccc',3000)";
Savepoint sp = null;
try {
conn = JdbcUtil.getMySqlConnection();
//设置事务显示手工提交
conn.setAutoCommit(false);
//NO1
pstmt = conn.prepareStatement(sqlA);
pstmt.executeUpdate();
//NO2
pstmt = conn.prepareStatement(sqlB);
pstmt.executeUpdate();
//设置一个回滚点
sp = conn.setSavepoint();
Integer.parseInt("abc");
//NO3
pstmt = conn.prepareStatement(sqlC);
pstmt.executeUpdate();
//设置事务手工提交
conn.commit();
} catch (Exception e) {
e.printStackTrace();
try {
//事务回滚,默认情况下,回滚到事务开始之前的状态
conn.rollback(sp);
conn.commit();
} catch (Exception e1) {
}
}finally{
JdbcUtil.close(rs);
JdbcUtil.close(pstmt);
JdbcUtil.close(conn);
}
}
}
package cn.itcast.web.jdbc.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import cn.itcast.web.jdbc.util.JdbcUtil;
//JDBC设置事务的隔离级别
public class Demo3 {
//我(serializable)先执行
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "select * from account";
try {
conn = JdbcUtil.getMySqlConnection();
//设置事务的隔离级别
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
conn.setAutoCommit(false);
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
//休息
Thread.sleep(20*1000);
conn.commit();
} catch (Exception e) {
e.printStackTrace();
try {
conn.rollback();
conn.commit();
} catch (Exception e1) {
}
}finally{
JdbcUtil.close(rs);
JdbcUtil.close(pstmt);
JdbcUtil.close(conn);
}
}
}
2)项目中,事务可能在dao层,也可能在service层,不论在哪一层,都必须确保使用的都是同一个connection
3)为了确保在Service和Dao层中用到的Connection一致,你可以使用如下方案解决:
a)将Service中的Connection传入Dao中
设计缺点:
Service和Dao代码过分藕合
在Service中引用了非业务逻辑操作
b)将JdbcUtil类中的Connection作成单例/态
c)使用ThreadLocale<Connection>将每个线程和自已的Connection绑定在一起,每个线程修改自已的Connection,
不会影响其它线程的Connection
4)在分层结构中,关闭Connection会推迟到Service层,但一定要关闭Connection对象
<%@ page language="java" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<body>
<form action="/day14/TransferServlet" method="post">
<table border="1" align="center">
<caption>转帐</caption>
<tr>
<th>转出帐号</th>
<td><input type="text" name="sid"/></td>
</tr>
<tr>
<th>转入帐号</th>
<td><input type="text" name="tid"/></td>
</tr>
<tr>
<th>金额</th>
<td><input type="text" name="money"/></td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="转帐"/>
</td>
</tr>
</table>
</form>
</body>
</html>
package cn.itcast.web.jdbc.domain;
//帐户
public class Account {
private int id;//帐号
private String name;//用户名
private float salary;//薪水
public Account(){}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getSalary() {
return salary;
}
public void setSalary(float salary) {
this.salary = salary;
}
}
package cn.itcast.web.jdbc.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import cn.itcast.web.jdbc.domain.Account;
import cn.itcast.web.jdbc.util.JdbcUtil;
public class TransferDao {
//根据ID号查询帐户
public Account findAccountById(int id) throws SQLException{
Account account = null;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "select * from account where id = ?";
try {
conn = JdbcUtil.getMySqlConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,id);
rs = pstmt.executeQuery();
if(rs.next()){
account = new Account();
account.setId(id);
account.setName(rs.getString("name"));
account.setSalary(rs.getFloat("salary"));
}
} catch (Exception e) {
e.printStackTrace();
}finally{
JdbcUtil.close(rs);
JdbcUtil.close(pstmt);
//JdbcUtil.close(conn);
}
return account;
}
//根据ID号更新帐户
public void updateAccountById(Account newAccount) throws SQLException{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "update account set salary = ? where id = ?";
try {
conn = JdbcUtil.getMySqlConnection();//conn=123
pstmt = conn.prepareStatement(sql);
pstmt.setFloat(1,newAccount.getSalary());
pstmt.setInt(2,newAccount.getId());
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally{
JdbcUtil.close(rs);
JdbcUtil.close(pstmt);
//JdbcUtil.close(conn);
}
}
}
package cn.itcast.web.jdbc.service;
import cn.itcast.web.jdbc.dao.TransferDao;
import cn.itcast.web.jdbc.domain.Account;
import cn.itcast.web.jdbc.exception.NoAccountException;
import cn.itcast.web.jdbc.exception.NoMoneyException;
import cn.itcast.web.jdbc.util.JdbcUtil;
public class TransferService {
//转帐
public void transfer(int sid,int tid,float money) throws Exception{
//NO1:判段转入和转出帐号是否存在
TransferDao transferDao = new TransferDao();
Account sAccount = transferDao.findAccountById(sid);
Account tAccount = transferDao.findAccountById(tid);
if(sAccount!=null && tAccount!=null){
//NO2:判段转出帐号是否有足够的余额
if(sAccount.getSalary()-money >= 0){
//进行转帐操作
sAccount.setSalary(sAccount.getSalary() - money);
tAccount.setSalary(tAccount.getSalary() + money);
try {
//事务开始
JdbcUtil.begin();//conn=123
transferDao.updateAccountById(sAccount);
//int i = 10/0;
transferDao.updateAccountById(tAccount);
//事务提交
JdbcUtil.commit();
} catch (Exception e) {
e.printStackTrace();
try {
//事务回滚
JdbcUtil.rollback();
//事务提交
JdbcUtil.commit();
} catch (Exception e1) {
}
throw e;
}finally{
//关闭Connection对象
JdbcUtil.closeConnection();
}
}
}
}
//取款
public void withdraw(int sid, float money)throws Exception{
TransferDao transferDao = new TransferDao();
Account sAccount = transferDao.findAccountById(sid);
if(sAccount!=null){
if(sAccount.getSalary()-money >= 0){
sAccount.setSalary(sAccount.getSalary() - money);
try {
transferDao.updateAccountById(sAccount);
} catch (Exception e) {
e.printStackTrace();
}finally{
JdbcUtil.closeConnection();
}
}else{
throw new NoMoneyException();
}
}else{
throw new NoAccountException();
}
}
}
package cn.itcast.web.jdbc.web;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import cn.itcast.web.jdbc.exception.NoAccountException;
import cn.itcast.web.jdbc.exception.NoMoneyException;
import cn.itcast.web.jdbc.service.TransferService;
public class TransferServlet extends HttpServlet {
private void withdraw(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
try {
int sid = Integer.parseInt(request.getParameter("sid"));
float money = Float.parseFloat(request.getParameter("money"));
TransferService transferService = new TransferService();
transferService.withdraw(sid,money);
request.setAttribute("message","交易成功,请取款");
}catch(NoMoneyException e){
e.printStackTrace();
request.setAttribute("message","帐号余额不足,不能交易");
}catch(NoAccountException e){
e.printStackTrace();
request.setAttribute("message","帐号输入错误,请重试");
} catch (Exception e) {
e.printStackTrace();
request.setAttribute("message","交易失败,请重试");
}
request.getRequestDispatcher("/message.jsp").forward(request,response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
String method = request.getParameter("method");
if(method!=null && method.equals("withdraw")){
this.withdraw(request,response);
return;
}
try {
int sid = Integer.parseInt(request.getParameter("sid"));
int tid = Integer.parseInt(request.getParameter("tid"));
float money = Float.parseFloat(request.getParameter("money"));
TransferService transferService = new TransferService();
transferService.transfer(sid,tid,money);
//转帐成功
request.setAttribute("message","转帐成功");
} catch (Exception e) {
//转帐失败
request.setAttribute("message","转帐成功");
}
request.getRequestDispatcher("/message.jsp").forward(request,response);
}
}
7 关于异常的处理
1)关于分层结构中,处理异常的规则,参见<<关于异常的处理规则.JPG>>
2)异常在项目中,往往替代boolean值,作为成功与否的标志
*8 连接池
1)传统方式找DriverManager要连接,数目是有限的。
2)传统方式的close(),并没有将Connection重用,只是切断应用程序和数据库的桥梁,即无发送到SQL命令到数据库端执行
3)项目中,对于Connection不说,不会直接使用DriverManager取得,而使用连接池方式。
4)DBCP和C3P0,都是Java开源的,都必须直接或间接实现javax.sql.DataSource接口
5)DBCP连接池需要dbcp.properties文件,同时需加入3个对应的jar包
•commons-dbcp.jar:连接池的实现
•commons-pool.jar:连接池实现的依赖类
•commons-collections.jar :连接池实现的集合类
*6)C3P0连接池需要在/WEB-INF/classes/目录下存放c3p0-config.xml文件,该类ComboPooledDataSource在创建时
会自动在指定的目录下找xml文件,并加载默认设置
7)重构JdbcUtil类
package cn.itcast.web.jdbc.datasource;
import java.sql.Connection;
import java.sql.SQLException;
import cn.itcast.web.jdbc.util.JdbcUtil3;
//测试传统方式取得连接的时间和个数
public class Demo1 {
public static void main(String[] args) throws SQLException {
long begin = System.currentTimeMillis();
for(int i=1;i<=5000;i++){
Connection conn = JdbcUtil3.getMySqlConnection();
if(conn!=null){
System.out.println(i+":取得连接");
}
JdbcUtil3.close(conn);
}
long end = System.currentTimeMillis();
System.out.println("共用" + (end-begin)/1000+"秒");
}
}
package cn.itcast.web.jdbc.datasource;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
//测试连接池DBCP的用法
public class Demo2 {
public static void main(String[] args) throws Exception {
long begin = System.currentTimeMillis();
//加载属性文件
InputStream is = Demo2.class.getClassLoader().getResourceAsStream("cn/itcast/web/jdbc/config/dbcp.properties");
Properties props = new Properties();
props.load(is);
//创建DBCP连接池工厂
BasicDataSourceFactory factory = new BasicDataSourceFactory();
//创建数据源,即连接池
DataSource ds = factory.createDataSource(props);
for(int i=1;i<=50000;i++){
//从连接池中取得一个空闲的连接对象
Connection conn = ds.getConnection();
if(conn!=null){
System.out.println(i+":取得连接");
}
//将连接对象还回给连接池
conn.close();
}
long end = System.currentTimeMillis();
System.out.println("共用" + (end-begin)/1000+"秒");
}
}
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/mydb2
username=root
password=root
package cn.itcast.web.jdbc.datasource;
import java.sql.Connection;
import com.mchange.v2.c3p0.ComboPooledDataSource;
//测试连接池C3P0的用法
public class Demo3 {
public static void main(String[] args) throws Exception {
long begin = System.currentTimeMillis();
//创建C3P0连接池
ComboPooledDataSource dataSource = new ComboPooledDataSource();
for(int i=1;i<=100000;i++){
Connection conn = dataSource.getConnection();
if(conn!=null){
System.out.println(i+":取得连接");
conn.close();
}
}
long end = System.currentTimeMillis();
System.out.println("共用" + (end-begin)/1000+"秒");
}
}