DButils:
1.创建queryrunner
2.编写sql
3.执行sql
QueryRunner:
构造:
new QueryRunner(DataSource ds):自动事务
new QueryRunner():手动事务
常用方法:
update(Connection conn,String sql,Object ... params):执行的cud操作
query(Connection conn....):执行查询操作
注意:
一旦使用手动事务,调用方法的时候都需要手动传入connection,并且需要手动关闭连接
代码
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.dao.AccountDao4DB;
import com.feizhu.dao.AccountDao4tl;
import com.feizhu.utils.DataSourceUtils;
import com.feizhu.utils.JdbcUtils;
public class AccountService4DB {
/**
*
* @param fromUser
* 转出方
* @param toUser
* 转入方
* @param money
* 金额
* @throws Exception
*/
public void account(String fromUser, String toUser, String money) throws Exception {
AccountDao4DB dao = new AccountDao4DB();
try {
//开启事物
DataSourceUtils.startTransaction();
// 转出
dao.accountOut(fromUser, money);
int i=1/0;
// 转入
dao.accountIn(toUser, money);
//事物提交
DataSourceUtils.commitAndClose();
} catch (Exception e) {
e.printStackTrace();
DataSourceUtils.rollbackAndClose();
throw e;
}
}
}
dao:
package com.feizhu.dao;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import com.feizhu.utils.DataSourceUtils;
public class AccountDao4DB {
public void accountOut(String fromUser, String money) throws Exception {
//创建queryrunner
QueryRunner qr= new QueryRunner();
//编写sql
String sql="update account set money=money-? where user=?";
//执行sql
qr.update(DataSourceUtils.getConnection(),sql,money,fromUser);
}
public void accountIn(String toUser, String money) throws SQLException {
QueryRunner qr=new QueryRunner();
String sql="update account set money=money+? where user=?";
qr.update(DataSourceUtils.getConnection(),sql,money,toUser);
}
}
工具类:
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(添加注明来意)