java入门小案例：连接mysql实现一个简单的账户管理系统（涉及知识点：循环 / java类的基本使用 / jdbc / driud连接池）
实现图例(通过一段简单的java代码实现对mysql读写，实现注册、登录、交易明细记录等功能)
代码结构：整体分为四个类
- jdbcUtil类：通过druid连接池自定义的一个创建连接和释放连接的工具类(嫌麻烦也可以不用druid连接池，使用普通的jdbc获取连接方式)
package com.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
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 DataSource dataSource=null;
private static ThreadLocal<Connection> tl=new ThreadLocal<>();
static {
Properties pro = new Properties();
InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("Druid.Properties");
try {
pro.load(is);
} catch (IOException e) {
throw new RuntimeException(e);
}
try {
dataSource = DruidDataSourceFactory.createDataSource(pro);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static Connection getConnection() throws SQLException {
Connection con = tl.get();
if (con==null){
con = dataSource.getConnection();
System.out.println("获取连接"+con);
tl.set(con);
}
return con;
}
public static void freeCon() throws SQLException {
Connection con = tl.get();
if (con!=null){
System.out.println("释放连接"+con);
tl.remove();
con.setAutoCommit(true);
con.close();
}
}
}
- BankUser类：属性名对应mysql字段名，通过对象属性控制mysql读写
package com.utils;
public class BankUser {
private String username;
private Double changeMoney;
private Double balance;
private String createTime;
public BankUser() {
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Double getChangeMoney() {
return changeMoney;
}
public void setChangeMoney(Double changeMoney) {
this.changeMoney = changeMoney;
}
public Double getBalance() {
return balance;
}
public void setBalance(Double balance) {
this.balance = balance;
}
public String getCreateTime() {
return createTime;
}
public void setCreateTime(String createTime) {
this.createTime = createTime;
}
@Override
public String toString() {
return "username:"+username+",changeMoney:"+changeMoney+",balance:"+balance+",createTime:"+createTime;
}
}
- Bank类：包含BankUser属性/Connection属性以及注册，登录，查询，修改的一些方法
package com.utils;
import org.apache.commons.lang.text.StrBuilder;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import static java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE;
public class Bank {
private BankUser bankUser = new BankUser();
private Connection con = JDBCUtils.getConnection();
public Bank() throws SQLException {
}
/*验证用户名：验证注册用户名在数据库中是否已经存在*/
public boolean verifyRegisterUsername(String username) throws SQLException {
String sql = "select * from bankuser where user=?";
PreparedStatement preparedStatement = con.prepareStatement(sql);
preparedStatement.setString(1, username);
ResultSet resultSet = preparedStatement.executeQuery();
boolean result = resultSet.next();
return result;
}
/*注册：向数据库中写入一条注册信息*/
public boolean registerUser(String username, String password) throws SQLException {
if (verifyRegisterUsername(username) == true) {
return false;
} else {
String sql = "insert into bankuser values(?,?)";
PreparedStatement preparedStatement = con.prepareStatement(sql);
preparedStatement.setString(1, username);
preparedStatement.setString(2, password);
int result = preparedStatement.executeUpdate();
System.out.println(result);
return result > 0;
}
}
/*登录：查询数据库中是否有账号和密码对的上的数据*/
public boolean login(String username, String password) throws SQLException {
String sql = "select * from bankuser where user=? and password=?";
PreparedStatement preparedStatement = con.prepareStatement(sql);
preparedStatement.setString(1, username);
preparedStatement.setString(2, password);
ResultSet resultSet = preparedStatement.executeQuery();
boolean result = resultSet.next();
return result;
}
/*初始化：新注册用户在业务表里还没有数据，生成一条初始数据并写入业务表中*/
public void initUser(String username) throws SQLException {
String initSql = "insert into bank(user,changeMoney,balance,createTime) values(?,?,?,?)";
PreparedStatement initStatement = con.prepareStatement(initSql);
initStatement.setString(1, username);
initStatement.setDouble(2, 0);
initStatement.setDouble(3, 0);
initStatement.setString(4, new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(new Date()));
initStatement.executeUpdate();
}
public void resultSetToUser(ResultSet resultSet) throws SQLException {
if (resultSet.next()) {
String user = resultSet.getString("user");
double changeMoney = resultSet.getDouble("changeMoney");
double balance = resultSet.getDouble("balance");
String createTime = resultSet.getString("createTime");
bankUser.setUsername(user);
bankUser.setChangeMoney(changeMoney);
bankUser.setBalance(balance);
bankUser.setCreateTime(createTime);
}
}
/*获取用户最新信息：用户每一条操作是基于最新一条数据进行，先获取最新信息并写入BankUser对象中*/
public BankUser getLatestUser(String username) throws SQLException {
String sql = "select * from bank where user=? order by transactionId desc limit 1";
PreparedStatement preparedStatement = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
preparedStatement.setString(1, username);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next() == false) {
initUser(username);
PreparedStatement preparedStatement1 = con.prepareStatement(sql);
preparedStatement1.setString(1, username);
ResultSet resultSet1 = preparedStatement.executeQuery();
resultSetToUser(resultSet1);
} else {
resultSet.beforeFirst();
resultSetToUser(resultSet);
}
return bankUser;
}
/*存取钱：在最新一条数据基础上进行insert操作*/
public boolean changeMoney(String user, double money) throws SQLException {
BankUser latestUser = getLatestUser(user);
latestUser.setChangeMoney(money);
latestUser.setBalance(latestUser.getBalance() + money);
latestUser.setCreateTime(new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(new Date()));
String sql = "insert into bank(user,changeMoney,balance,createTime) values(?,?,?,?)";
PreparedStatement preparedStatement = con.prepareStatement(sql);
preparedStatement.setString(1, latestUser.getUsername());
preparedStatement.setDouble(2, latestUser.getChangeMoney());
preparedStatement.setDouble(3, latestUser.getBalance());
preparedStatement.setString(4, latestUser.getCreateTime());
int i = preparedStatement.executeUpdate();
return i > 0;
}
public String resultSetToString(ResultSet resultSet) throws SQLException {
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
ArrayList<HashMap<String, String>> list = new ArrayList<>();
while (resultSet.next()) {
HashMap<String, String> map = new HashMap<>();
for (int i = 1; i <= columnCount; i++) {
String columnLabel = metaData.getColumnLabel(i);
String value = resultSet.getObject(i).toString();
map.put(columnLabel, value);
}
list.add(map);
}
StrBuilder sb = new StrBuilder();
sb.append("Id====user====changeMoney====balance====createTime\n");
for (int i = 0; i < list.size(); i++) {
HashMap<String, String> map = list.get(i);
String transactionId = map.get("transactionId");
String user1 = map.get("user");
String changeMoney = map.get("changeMoney");
String balance = map.get("balance");
String createTime = map.get("createTime");
sb.append(transactionId + "\t" + user1 + " \t" + changeMoney + "\t\t\t" + balance + "\t\t" + createTime + "\n");
}
return sb.toString();
}
/*展示交易明细：查询某一用户所有业务数据，遍历拉取写成string*/
public String showDetail(String user) throws SQLException {
String sql = "select * from bank where user=?";
PreparedStatement preparedStatement = con.prepareStatement(sql, TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
preparedStatement.setString(1, user);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next() == false) {
initUser(user);
PreparedStatement preparedStatement1 = con.prepareStatement(sql);
preparedStatement1.setString(1, user);
ResultSet resultSet1 = preparedStatement1.executeQuery();
return resultSetToString(resultSet1);
} else {
resultSet.beforeFirst();
return resultSetToString(resultSet);
}
}
}
- UserBankLogin:main方法主要写一些循环和页面提示信息
package com.utils;
import java.sql.SQLException;
import java.util.Scanner;
public class UserBankLogin {
public static void main(String[] args) throws SQLException {
Scanner scan = new Scanner(System.in);
scan.useDelimiter("\n");
Bank bank = new Bank();
while (true) {
System.out.println("===== 1.register 2.login 3.quit======");
System.out.println("输入指令进行注册或者登录：");
String next = scan.next();
if (next.equals("3")) {
System.out.println("即将退出系统...");
JDBCUtils.freeCon();
break;
}
if (next.equals("1")) {
while (true) {
System.out.println("=====是否继续注册 yes no =====");
String registerOrNot = scan.next();
if (registerOrNot.equals("no")) {
System.out.println("退出注册...");
break;
}
if (registerOrNot.equals("yes")) {
System.out.println("欢迎进入注册页面!请输入用户名和密码：");
System.out.println("请输入用户名：");
String registerUsername = scan.next();
System.out.println("请输入密码：");
String registerPassword = scan.next();
boolean registerResult = bank.registerUser(registerUsername, registerPassword);
if (registerResult == true) {
System.out.println("注册成功，用户名：" + registerUsername + ",密码：" + registerPassword);
System.out.println("++++++++++++++++++++++++++++++++++++++");
} else {
System.out.println("用户名已存在");
System.out.println("++++++++++++++++++++++++++++++++++++++");
}
}
}
} else if (next.equals("2")) {
System.out.println("欢迎进入登录页面！请输入用户名和密码：");
System.out.println("请输入用户名：");
String loginUsername = scan.next();
System.out.println("请输入密码：");
String loginPassword = scan.next();
boolean loginResult = bank.login(loginUsername, loginPassword);
if (loginResult == true) {
while (true) {
System.out.println("请选择需要进行的操作：1.查询余额 2.存钱 3.取钱 4.查询交易明细 5.放回上一级");
String type = scan.next();
if (type.equals("5")) {
System.out.println("返回上一级");
break;
}
switch (type) {
case "1":
BankUser latestUser = bank.getLatestUser(loginUsername);
System.out.println(latestUser);
break;
case "2":
System.out.println("请输入存钱金额：");
String addMoney = scan.next();
boolean addResult = bank.changeMoney(loginUsername, Double.parseDouble(addMoney));
if (addResult == true) {
System.out.println("存钱成功！");
} else {
System.out.println("存钱失败！");
}
break;
case "3":
System.out.println("请输入取钱金额：");
String subMoney = scan.next();
boolean subResult = bank.changeMoney(loginUsername, -Double.parseDouble(subMoney));
if (subResult == true) {
System.out.println("取钱成功！");
} else {
System.out.println("取钱失败！");
}
break;
case "4":
String s = bank.showDetail(loginUsername);
System.out.println(s);
break;
default:
System.out.println("输入错误，请重兴输入");
break;
}
}
} else {
System.out.println("登录失败");
System.out.println("+++++++++++++++++++++++++++++++++++++++");
}
} else {
System.out.println("输入有误,请重新输入!");
System.out.println("+++++++++++++++++++++++++++++++++++++++");
}
}
}
}
mysql中对应的表
-
bankuser表：用来存储用户注册信息，和验证登录信息用
建表语句：create table if not exists bankuser( user varchar(10), password varchar(10) ); -
bank表：用来存储业务信息
建表语句：create table if not exists bank( transactionId int primary key auto_increment not null, user varchar(10), changeMoney double, balance double, createTime datetime );
总结：随手写的，有些地方不是很严谨，能跑，供参考
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
