文章目录
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;
}
}