使用 ThreadLocal绑定orderService,提交或回滚事务

使用 ThreadLocal 来确保所有 dao 操作都在同一个 Connection 连接对象中完成 

  BaseDao中一定要用throw new RuntimeException(e) 抛出异常，不能关闭连接，否则在servlet中是不能回滚事务的

package dao;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import utils.JdbcUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;


public abstract class BaseDao {
    //导入jar包 commons-dbutils-1.3.jar
    //使用DBUtils操作数据库
    private QueryRunner queryRunner=new QueryRunner();

    /*
      update()方法用来执行insert，delete，update SQL语句
      return 的返回值表明执行操作影响的行数，返回-1表示执行失败
     */
    public int update(String sql, Object...args){
          Connection connection=null;
        try {
            connection= JdbcUtils.getConnection();
            return queryRunner.update(connection,sql,args);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException(throwables);//一定要抛出异常，才能回滚事务
        }
//        finally{
//            JdbcUtils.close(connection);
//        }
        //return -1;
    }

    /*
    * 查询返回一个javaBean的sql语句
    * type  返回的对象类型
    * sql   执行的sql语句
    * args  sql对应的参数值（可变长参数）
    * <T>   返回类型的泛型*/
    public <T> T queryForOne (Class<T> type , String sql, Object...args){
      Connection connection=JdbcUtils.getConnection();
        try {
            return queryRunner.query(connection,sql,new BeanHandler<T>(type),args);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);//一定要抛出异常，才能回滚事务
        }
//        finally {
//            JdbcUtils.close(connection);
//        }
        //return null;
    }

    //查询返回一个带有javaBean的list链表的sql语句
    public <T> List<T> queryForList (Class<T> type , String sql, Object...args){
        Connection connection=JdbcUtils.getConnection();
        try {
            return queryRunner.query(connection,sql,new BeanListHandler<T>(type),args);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);//一定要抛出异常，才能回滚事务
        }
//        finally {
//            JdbcUtils.close(connection);
//        }
        //return null;
    }

    /*
    * 执行返回一行一列的sql语句*/
    public Object queryForSingleValue(String sql,Object...args){
        Connection connection=JdbcUtils.getConnection();
        try {
            return queryRunner.query(connection,sql,new ScalarHandler(),args);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new RuntimeException(throwables);//一定要抛出异常，才能回滚事务
        }
        //return null;
    }
}

连接数据库时设为手动提交，无异常时提交事务并关闭连接，有异常时回滚事务并关闭连接 

package utils;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;


public class JdbcUtils {
    private  static DruidDataSource dataSource;
    public static ThreadLocal<Connection> conns=new ThreadLocal<Connection>();
    static {
        Properties properties=new Properties();
        InputStream resourceAsStream = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
        //读取jdbc.properties属性配置文件
        try {
            properties.load(resourceAsStream);//从流中加载数据
            try {
                dataSource= (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);//创建数据库连接池
            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){//获得数据库连接池中的连接
        Connection conn=conns.get();
        if(conn==null){
            try {
                conn=dataSource.getConnection();
                conn.setAutoCommit(false);//设置为手动管理事务
                conns.set(conn);//把conn设置为当前线程关联的数据
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return conn;
    }

    public static void commitAndClose(){//提交事务，关闭连接
           Connection conn=conns.get();
           if(conn!=null){
               try {
                   conn.commit();
               } catch (SQLException throwables) {
                   throwables.printStackTrace();
               }finally {
                   try {
                       conn.close();
                   } catch (SQLException throwables) {
                       throwables.printStackTrace();
                   }
               }
           }
           conns.remove();//因为tomcat底层使用的是线程池技术，否则会出错
    }

    public static void rollbackAndClose(){//回滚事务，关闭连接
        Connection conn=conns.get();
        if(conn!=null){
            try {
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        conns.remove();//因为tomcat底层使用的是线程池技术，否则会出错
    }
//    /*
//    获取数据库连接池中的连接
//     */
//    public  static Connection getConnection(){
//        Connection conn=null;
//
//        try {
//            conn=dataSource.getConnection();
//        } catch (SQLException throwable) {
//            throwable.printStackTrace();
//        }
//
//        return conn;
//    }
//
//    /*
//    关闭连接，放回数据库连接池
//
//     */
//    public static void close(Connection conn){
//          if(conn!=null){
//              try {
//                  conn.close();
//              } catch (SQLException throwable) {
//                  throwable.printStackTrace();
//              }
//          }
//    }
}

 

  try {
            orderId = orderService.createOrder(cart,userId);
            JdbcUtils.commitAndClose();
        } catch (Exception e) {
            e.printStackTrace();
            JdbcUtils.rollbackAndClose();
        } 

 

package web;

import pojo.Car;
import pojo.OrderItem;
import pojo.User;
import service.OrderService;
import service.OrderServiceImpl;
import utils.JdbcUtils;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

public class OrderServlet extends BaseServlet {
    private OrderService orderService=new OrderServiceImpl();

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            doPost(request,response);
    }

    protected void createOrder(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
           Car cart=(Car)request.getSession().getAttribute("cart");//获取购物车对象
           User loginUser=(User)request.getSession().getAttribute("user");
           if(loginUser==null){//如果未登录，跳到登录页面
               request.getRequestDispatcher("/pages/user/login.jsp").forward(request,response);
           }

           Integer userId=loginUser.getId();//获得用户的id

        String orderId= null;//生成订单
        try {
            orderId = orderService.createOrder(cart,userId);
            JdbcUtils.commitAndClose();
        } catch (Exception e) {
            e.printStackTrace();
            JdbcUtils.rollbackAndClose();
        }
        
        request.getSession().setAttribute("orderId",orderId);

           response.sendRedirect(request.getContextPath()+"/pages/cart/checkout.jsp");//请求重定向

    }

    protected void queryOrderItem(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        List<OrderItem> orderItems = orderService.queryOrderItem();
        request.getSession().setAttribute("orderItems",orderItems);
        request.getRequestDispatcher("/pages/order/order.jsp").forward(request,response);
    }



}