mysql+dbcp（idea），使用事务管理实现‘注册’功能的详细步骤

1.java搭建一个项目，命名为qq_manager

2.在项目下添加lib包

（1）首先在新建的java项目中添加四个jar包

链接：https://pan.baidu.com/s/10fdi5bYDuBgNe5qqsKZWDQ
提取码：2022

（2）实现lib

   lib右键

3.在项目文件的src下添加配置文件

dbcp.properties

#配置驱动类
driverClassName=com.mysql.cj.jdbc.Driver
#配置连接字符串,qqmanager为数据库名
url=jdbc:mysql://localhost:3308/qqmanager?useSSL=false&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
#登录数据库的用户名
username=root
#登录数据库的密码
password=root
#最大活动连接数，设为0为没有限制
maxActive=50
#最大空闲连接数，设为0为没有限制
maxIdle=20

4.搭建分层package，分为实体类层bean，视图层client,menu,数据访问层dao，服务层service，工具包util,common

• （1）util包中，添加类ConnUtil，方便使用ThreaLocal通过Key关闭和创建连接，以及回滚事务

public class ConnUtil {
    //每个线程使用自己的ThreadLocal对象
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();
    //数据源对象
    private static DataSource ds;

    //私有构造方法，外部不能new
    private ConnUtil() {

    }

    static {
        //加载数据源ds对象
        InputStream is = ConnUtil.class.getResourceAsStream("/dbcp.properties");
        Properties properties = new Properties();
        try {
            properties.load(is);
            ConnUtil.ds = BasicDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConn() throws SQLException {
        //从threadLocal中取出
        Connection conn = threadLocal.get();

            if (conn == null || conn.isClosed()) {
                //创建数据库连接
                conn = ds.getConnection();
                threadLocal.set(conn);//将连接放入当前线程
            }

        return conn;
    }

    public static void closeConn()  {
        Connection conn = threadLocal.get();

        try {
            if (conn != null && !conn.isClosed()) {
                try {
                    conn.close();//在使用DBCP时，释放连接对象
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }

                threadLocal.set(null);//清空当前线程中的连接

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
/**
 * 回滚dbcp连接对象
 */
public static void rollback() {
    Connection conn = threadLocal.get();

    try {
        if (conn != null && !conn.isClosed()) {

            conn.rollback();//执行事务回滚
        }
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }

}

}

• （2）创建bean包，在bean包中创建实体类，在这之前需要在数据库(qqmanager)中创建表

account表，存储用户账号信息

users表，存储用户的基本信息

在users表中创建外键fk_users_accountId，在users表插入数据前，首先要在account表中插入数据，将accountId自增主键的值返回到users表中的accountId字段。

• 创建实体类，实体类的类名为表名单数，users->user,实体类中的属性、类型与表中的字段一一对应。
• 在设置属性后，加入setter,getter方法，适当的添加构造方法。

User

import java.util.Date;
public class User {
    private int userId;
    private String userName;
    private boolean gender;
    private Date bearthDate;
    private String address;
    private int accountId;

    public User() {
    }

    public User(int userId, String userName, boolean gender, Date bearthDate, String address, int accountId) {
        this.userId = userId;
        this.userName = userName;
        this.gender = gender;
        this.bearthDate = bearthDate;
        this.address = address;
        this.accountId = accountId;
    }

    public int getUserId() {
        return userId;
    }

    public void setUserId(int userId) {
        this.userId = userId;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public boolean isGender() {
        return gender;
    }

    public void setGender(boolean gender) {
        this.gender = gender;
    }

    public Date getBearthDate() {
        return bearthDate;
    }

    public void setBearthDate(Date bearthDate) {
        this.bearthDate = bearthDate;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public int getAccountId() {
        return accountId;
    }

    public void setAccountId(int accountId) {
        this.accountId = accountId;
    }
}

Account 用户账号实体类

public class Account {
    private int accountId;
    private String accountNo;
    private String pwd;
    private String nickName;
    private int state;
    private int grade;

    public Account() {
    }

    public Account(int accountId, String accountNo, String pwd, String nickName, int state, int grade) {
        this.accountId = accountId;
        this.accountNo = accountNo;
        this.pwd = pwd;
        this.nickName = nickName;
        this.state = state;
        this.grade = grade;
    }

    public int getAccountId() {
        return accountId;
    }

    public void setAccountId(int accountId) {
        this.accountId = accountId;
    }

    public String getAccountNo() {
        return accountNo;
    }

    public void setAccountNo(String accountNo) {
        this.accountNo = accountNo;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    public String getNickName() {
        return nickName;
    }

    public void setNickName(String nickName) {
        this.nickName = nickName;
    }

    public int getState() {
        return state;
    }

    public void setState(int state) {
        this.state = state;
    }

    public int getGrade() {
        return grade;
    }

    public void setGrade(int grade) {
        this.grade = grade;
    }
}

• (3)menu菜单，该菜单可以通过在用户界面通过对象进行调用，显示内容
• 后续添加功能，可以在此类添加内容进行说明。

public class SystemMenu {
    public void showMenu(){
        System.out.println("*****************");
        System.out.println("1.注册");
        System.out.println("2.登录");
        System.out.println("9.退出");
        System.out.println("*****************");
    }
}

• (4)client 用户界面层，通过页面选择功能进行了判断，选择执行的方法，仅有注册的功能，registerUser().

public class Client {

    private static SystemMenu systemMenu = new SystemMenu();
    private static Scanner scan = new Scanner(System.in);
    private static UserService userService = new UserService();
    public static void main(String[] args) {
        while(true){
            systemMenu.showMenu();
            int item = scan.nextInt();
            switch (item){
                case 1:
                    //注册
                    registerUser();
                    break;
                case 2:
                    //登录
                    //login();
                    break;
                case 9:
                    System.out.println("退出");
                    System.exit(1);
            }

        }
    }

    /**
     * 注册
     */
    private static void registerUser() {
        System.out.println("当前位置：注册");
        Account account = new Account();

        account.setAccountNo(Generator.generator());
        System.out.print("密码：");
        account.setPwd(scan.next());
        System.out.print("昵称：");
        account.setNickName(scan.next());

        User user = new User();
        System.out.println("姓名：");
        user.setUserName(scan.next());
        System.out.println("性别：");
        user.setGender("t".equals(scan.next().toLowerCase()));
        System.out.println("出生日期(格式1900-8-09)：");
        String date = scan.next();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date birthday = null;
        try {
            birthday = sdf.parse(date);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        user.setBearthDate(birthday);
        System.out.println("籍贯：");
        user.setAddress(scan.next());

        userService.register(account,user);
        System.out.println("注册成功！");

    }
}

• （5）dao层，数据访问层，实现插入用户和账户信息的功能 ，AccountDao在ps预处理sql语句时，将自增主键accountId的值。

public class AccountDao {
    private static Connection conn = null;
    private static PreparedStatement ps = null;
    private static ResultSet rs = null;
    /*
    增加账户信息，返回账号Id
     */
    public int addAccount(Account account){
        String sql = "insert into account values(default,?,?,?,?,?)";
        int accountId = 0;
        try {
            //1.获取连接对象
            conn = ConnUtil.getConn();
            ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);            ps.setString(1,account.getAccountNo());
            ps.setString(2,account.getPwd());
            ps.setString(3, account.getNickName());
            ps.setInt(4,account.getState());
            ps.setInt(5,account.getGrade());
            ps.executeUpdate();
            rs = ps.getGeneratedKeys();
            if(rs.next()){
                accountId = rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return accountId;
    }
}

• UserDao将界面输入的值，以及返回的accountId,插入user表，作为user表的外键。

public class UserDao {
    private static Connection conn = null;
    private static PreparedStatement ps = null;
    private static ResultSet rs = null;
    /*
    增加用户信息
     */
    public int addUser(User user){
        String sql = "insert into users values(default,?,?,?,?,?)";
        int count = 0;
        try {
            //1.获取连接对象
            conn = ConnUtil.getConn();
            ps = conn.prepareStatement(sql);
            ps.setString(1,user.getUserName());
            ps.setBoolean(2,user.isGender());
            ps.setTimestamp(3,new java.sql.Timestamp(user.getBearthDate().getTime()));
            ps.setString(4,user.getAddress());
            ps.setInt(5,user.getAccountId());
            count = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return count;
    }
}

• （6）service层，对注册的两个事务做原子性操作，要么都执行，要么都不执行。

ConnUtil.getConn().setAutoCommit(false);//设置提交方式为手动提交
ConnUtil.getConn().commit();//提交 连接
ConnUtil.rollback();//回滚

public class UserService {
   private UserDao userDao = new UserDao();
   private AccountDao accountDao = new AccountDao();
    public boolean register(Account acc, User user){
        try {
            ConnUtil.getConn().setAutoCommit(false);
            int accountId = accountDao.addAccount(acc);
            if(accountId>0){
                user.setAccountId(accountId);
                userDao.addUser(user);

                ConnUtil.getConn().commit();
                return true;
            }else {
                ConnUtil.rollback();
            }

        } catch (SQLException throwables) {
                ConnUtil.rollback();
        }catch (Exception exception){
                ConnUtil.rollback();
        } finally
        {
                ConnUtil.closeConn();//关闭连接
        }
        return false;
    }
}