步骤分析:
1.数据库和表
2.新建一个项目
3.导入jar包和工具类
驱动 jdbcUtils
c3p0及其配置文件和工具类
dbutils
4.新建一个account.jsp 表单
5.accountservlet:
接受三个参数
调用accountservice.account方法完成转账操作
打印信息
6.account方法中:
使用jdbc不考虑事务
调用dao完成转出操作
调用dao完成转入操作
7.dao中
一旦出现异常,钱飞了.
要想避免这事情,必须添加事务,在service添加事务.
为了保证所有的操作在一个事务中,必须保证使用的是同一个连接
在service层我们获取了连接,开启了事务.如何dao层使用此连接呢????
方法1:
向下传递参数.注意连接应该在service释放
方法2:
可以将connection对象绑定当前线程上
jdk中有一个ThreadLocal类,
ThreadLocal 实例通常是类中的 private static 字段,
它们希望将状态与某一个线程(例如,用户 ID 或事务 ID)相关联
代码:
account.jsp:
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<form method="post" action="${pageContext.request.contextPath }/account">
<table>
<tr>
<td>转出方</td>
<td><input type="text" name="fromuser"></td>
</tr>
<tr>
<td>转入方</td>
<td><input type="text" name="touser"></td>
</tr>
<tr>
<td>转账金额</td>
<td><input type="text" name="money"></td>
</tr>
<tr>
<td><input type="submit" value="转账"></td>
<td></td>
</tr>
</table>
</form>
</body>
</html>
servlet:
package com.feizhu.web.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.feizhu.service.AccountService;
import com.feizhu.service.AccountService4DB;
import com.feizhu.service.AccountService4tl;
/**
* 转账
*/
public class AccountServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//设置编码
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
PrintWriter w=response.getWriter();
//接受三个参数
String fromUser=request.getParameter("fromuser");
String toUser=request.getParameter("touser");
String money=request.getParameter("money");
//调用accountservice.account(fromuser,touser,money)
try {
//new AccountService4tl().account(fromUser,toUser,money);
new AccountService4DB().account(fromUser, toUser, money);
//打印信息
w.print("转账成功");
} catch (Exception e) {
e.printStackTrace();
w.print("转账失败");
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
service:
package com.feizhu.service;
import java.sql.Connection;
import com.feizhu.dao.AccountDao;
import com.feizhu.utils.JdbcUtils;
public class AccountService {
/**
*
* @param fromUser 转出方
* @param toUser 转入方
* @param money 金额
* @throws Exception
*/
public void account(String fromUser, String toUser, String money) throws Exception {
AccountDao dao = new AccountDao();
//开启事物
Connection conn=null;
try {
conn = JdbcUtils.getConnection();
conn.setAutoCommit(false);
//转出
int i=1/0;
dao.accountOut(conn,fromUser,money);
//转入
dao.accountIn(conn,toUser,money);
//事物提交
conn.commit();
JdbcUtils.closeConnect(conn);
} catch (Exception e) {
e.printStackTrace();
//事物回滚
conn.rollback();
JdbcUtils.closeConnect(conn);
throw e;
}
}
}
dao
package com.feizhu.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.feizhu.utils.JdbcUtils;
public class AccountDao {
/**
* 转出钱
* @param fromUser
* @param money
* @throws Exception
*/
public void accountOut(Connection conn, String fromUser, String money) throws Exception {
PreparedStatement st=null;
try {
//编写sql
String sql="update account set money=money-? where user=?";
//创建语句执行者
st=conn.prepareStatement(sql);
//设置参数
st.setString(1, money);
st.setString(2, fromUser);
//执行sql
int i=st.executeUpdate();
//处理
System.out.println("出:"+i);
} catch (Exception e) {
e.printStackTrace();
throw e;
}finally {
//JdbcUtils.closeResource(conn, st, rs);
JdbcUtils.closeStatement(st);
}
}
/**
* 转入钱
* @param toUser
* @param money
* @throws Exception
*/
public void accountIn(Connection conn,String toUser, String money) throws Exception {
PreparedStatement st=null;
ResultSet rs=null;
try {
//编写sql
String sql="update account set money=money+? where user=?";
//创建语句执行者
st=conn.prepareStatement(sql);
//设置参数
st.setString(1, money);
st.setString(2, toUser);
//执行sql
int i=st.executeUpdate();
//处理
System.out.println("进:"+i);
} catch (Exception e) {
e.printStackTrace();
throw e;
}finally {
//JdbcUtils.closeResource(conn, st, rs);
JdbcUtils.closeStatement(st);
}
}
public void accountOut_(String fromUser, String money) throws Exception {
Connection conn=null;
PreparedStatement st=null;
ResultSet rs=null;
try {
conn=JdbcUtils.getConnection();
//编写sql
String sql="update account set money=money-? where user=?";
//创建语句执行者
st=conn.prepareStatement(sql);
//设置参数
st.setString(1, money);
st.setString(2, fromUser);
//执行sql
int i=st.executeUpdate();
//处理
System.out.println("出:"+i);
} catch (Exception e) {
e.printStackTrace();
throw e;
}finally {
JdbcUtils.closeResource(conn, st, rs);
}
}
/**
* 转入钱
* @param toUser
* @param money
* @throws Exception
*/
public void accountIn_(String toUser, String money) throws Exception {
Connection conn=null;
PreparedStatement st=null;
ResultSet rs=null;
try {
conn=JdbcUtils.getConnection();
//编写sql
String sql="update account set money=money+? where user=?";
//创建语句执行者
st=conn.prepareStatement(sql);
//设置参数
st.setString(1, money);
st.setString(2, toUser);
//执行sql
int i=st.executeUpdate();
//处理
System.out.println("进:"+i);
} catch (Exception e) {
e.printStackTrace();
throw e;
}finally {
JdbcUtils.closeResource(conn, st, rs);
}
}
}
工具类:
package com.feizhu.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ResourceBundle;
public class JdbcUtils {
static final String DRIVERCLASS;
static final String URL;
static final String USER;
static final String PASSWORD;
static {
//获取ResourceBundle ctrl+2 l
ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
//获取指定的内容
DRIVERCLASS=bundle.getString("driverClass");
URL=bundle.getString("url");
USER=bundle.getString("user");
PASSWORD=bundle.getString("password");
}
static {
//注册驱动
try {
Class.forName(DRIVERCLASS);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws Exception {
//获取连接
Connection conn=DriverManager.getConnection(URL,USER,PASSWORD);
return conn;
}
/**
* 释放资源
*
* @param conn
* 连接
* @param st
* 语句执行者
* @param rs
* 结果集
*/
public static void closeResource(Connection conn, PreparedStatement st, ResultSet rs) {
closeConnect(conn);
closeStatement(st);
closeResult(rs);
}
/**
* 释放连接
*
* @param conn
* 连接
*/
public static void closeConnect(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
/**
* 释放语句执行者
*
* @param st
* 语句执行者
*/
public static void closeStatement(PreparedStatement st) {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
st = null;
}
}
/**
* 释放结果集
*
* @param rs
* 结果集
*/
public static void closeResult(ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
}
}
--------------------------------------------------------------------------------------------
package com.feizhu.utils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class DataSourceUtils {
private static ComboPooledDataSource ds=new ComboPooledDataSource();
private static ThreadLocal<Connection> tl=new ThreadLocal<>();
/**
*获取数据源
* @return 连接
*/
public static DataSource getDataSource() {
return ds;
}
/**
* 从 当前线程上获取连接
* @return
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
Connection conn=tl.get();
if(conn==null) {
//第一次获取 创建一个连接和当前的线程绑定
conn=ds.getConnection();
//绑定
tl.set(conn);
}
return conn;
}
/**
* 释放连接
* @param conn 连接
*/
public static void closeConn(Connection conn) {
if(conn!=null) {
try {
conn.close();
//和当前的线程解绑
} catch (SQLException e) {
e.printStackTrace();
}
conn=null;
}
}
/**
*释放语句执行者
* @param st 执行者
*/
public static void closeStatement(PreparedStatement st) {
if(st!=null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
st=null;
}
}
/**
*释放结果集
* @param rs 结果集
*/
public static void closeResultSet(ResultSet rs) {
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs=null;
}
}
/**
*
* @param conn
* @param st
* @param rs
*/
public static void closeRouse(Connection conn,PreparedStatement st,ResultSet rs) {
closeRouse(st,rs);
closeConn(conn);
}
public static void closeRouse(PreparedStatement st,ResultSet rs) {
closeResultSet(rs);
closeStatement(st);
}
/**
* 开启事物
* @throws SQLException
*/
public static void startTransaction() throws SQLException {
//获取连接//开启事物
getConnection().setAutoCommit(false);
}
//事物提交
public static void commitAndClose() {
//获取连接
try {
Connection conn=getConnection();
//提交事物
conn.commit();
//释放资源
conn.close();
//解除绑定
tl.remove();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 事物回滚
* @throws SQLException
*/
//事物提交
public static void rollbackAndClose() {
//获取连接
try {
Connection conn=getConnection();
//事物回滚
conn.rollback();
//释放资源
conn.close();
//解除绑定
tl.remove();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
备注:由于个人原因,本博客暂停更新。如有问题可联系本人,本人提供技术指导、学习方向、学习路线。本人微信wlp1156107728(添加注明来意) QQ1156107728(添加注明来意)