连接代码
实现功能代码：
选择功能代码：
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.beans.PropertyVetoException;
import java.sql.*;
public class Test {
private static Test ourInstance = new Test();
private DataSource dataSource = null;
public static Test getInstance() {
return ourInstance;
}
private Test() {
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
try {
comboPooledDataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
comboPooledDataSource.setJdbcUrl("");
comboPooledDataSource.setUser("root");
comboPooledDataSource.setPassword("");
comboPooledDataSource.setMaxPoolSize(3);
comboPooledDataSource.setMinPoolSize(1);
dataSource = comboPooledDataSource;
Connection connection = dataSource.getConnection();
System.out.println("初始化成功");
} catch (PropertyVetoException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public Connection getDataSource() throws SQLException {
Connection connection = dataSource.getConnection();
connection.getAutoCommit();
return connection;
}
public Connection getDataSource2() throws SQLException {
Connection connection = dataSource.getConnection();
connection.setAutoCommit(false);
return connection;
}
public static ResultSet getResultSet(Connection connection, String sql, Object... objects) throws SQLException {
PreparedStatement connection1 = connection.prepareStatement(sql);
for (int i = 0; i < objects.length; i++) {
connection1.setObject(i + 1, objects[i]);
}
ResultSet rs = connection1.executeQuery();
return rs;
}
public static int getUpdate(Connection connection, String sql, Object... objects) throws SQLException {
PreparedStatement pst = connection.prepareStatement(sql);
for (int i = 0; i < objects.length; i++) {
pst.setObject(i + 1, objects[i]);
}
int a = pst.executeUpdate();
return a;
}
}
实现功能代码：
import YeWu.*;
import YeWu.KaHaoException;
import java.sql.*;
public class Test2 {
/**
* @param CardNumber 卡号
* @param password 密码
* @throws SQLException sql语法错误
* @throws KaHaoException 卡号错误
* @throws DongJieException 账户被冻结
* @throws MimaErrorException 密码错误
*/
public static void YeWu(String CardNumber, String password) throws SQLException,
KaHaoException, DongJieException, MimaErrorException {
Connection connection = null;
try {
connection = Test.getInstance().getDataSource2();
ResultSet resultSet = Test.getResultSet(connection, "select*from Trading where CardNumber=?", CardNumber);
if (!resultSet.next()) {
throw new KaHaoException();
}
long ShiJian = resultSet.getTime("shijiantime").getTime();
long DangQian = System.currentTimeMillis();
if (resultSet.getInt("error") >= 3) {
if (DangQian > ShiJian) {
Test.getUpdate(connection, "update Trading set error=0 where CardNumber=?", CardNumber);
connection.commit();//写修改一定要提交不然事务就会自动回滚
} else {
throw new DongJieException();
}
}
ResultSet rs = Test.getResultSet(connection,
"select*from Trading where CardNumber=? and password=?",
CardNumber, password);
if (!rs.next()) {
int a = resultSet.getInt("error");
if (a >= 2) {
Test.getUpdate(connection,
"UPDATE Tradin set jiesuotime=DATE_ADD(SYSDATE(), INTERVAL '0 00:01:00' DAY_SECOND) where CardNumber=?",
CardNumber);
}
Test.getUpdate(connection,
"update Trading set error=error+1 where CardNumber=?",
CardNumber);
connection.commit();
throw new MimaErrorException();
}
connection.commit();
} finally {
connection.close();
}
}
/**
* @param CardNumber
* @return 查询
* @throws SQLException
* @throws KaHaoException
*/
public static float CaXun(String CardNumber) throws KaHaoException, SQLException {
Connection connection = null;
float jine = 0;
try {
connection = Test.getInstance().getDataSource2();
ResultSet resultSet = Test.getResultSet(connection, "select meoney from Trading where CardNumber=?", CardNumber);
if (!resultSet.next()) {
throw new KaHaoException();
} else {
jine = resultSet.getInt("meoney");
}
} finally {
connection.close();
}
return jine;
}
/**
* @param CardNumber 卡号
* @param je 取款金额
*/
public static void QuKuan(String CardNumber, float je) throws SQLException, YeBuZhuException, KaHaoException, WeiZhiException {
if (je <= 0) {
throw new YeBuZhuException();
}
Connection connection = null;
try {
connection = Test.getInstance().getDataSource2();
ResultSet resultSet = Test.getResultSet(connection,
"Select*from Trading where CardNumber=?",
CardNumber);
if (!resultSet.next()) {
throw new KaHaoException();
}
if (resultSet.getFloat("meoney") >= je) {
int a = Test.getUpdate(connection, "update Trading set meoney=meoney-? where CardNumber=?", je, CardNumber);
if (a >= 1) {
int b = Test.getUpdate(connection,
"INSERT INTO jinelog(uid,ctime,jin,type,info) VALUES(?,?,SYSDATE(),?,?,?)",
CardNumber, je, "出", "取款业务"
);
connection.commit();
} else {
throw new WeiZhiException();
}
} else {
throw new YeBuZhuException();
}
} finally {
connection.close();
}
}
/**
* 存款业务
*
* @throws SQLException
*/
public static void CunKuan(String CardNumber, float meoney, String MiMa) throws YeBuZhuException, KaHaoException, WeiZhiException, SQLException {
if (meoney <= 0) {
throw new YeBuZhuException();
}
Connection connection = null;
try {
connection = Test.getInstance().getDataSource2();
ResultSet resultSet = Test.getResultSet(connection,
"select*from Trading where CardNumber=?",
CardNumber);
if (!resultSet.next()) {
throw new KaHaoException();
} else {
int a = Test.getUpdate(connection,
"update Trading set meoney=meoney+? where CardNumber=?",
meoney, CardNumber);
if (a < 1) {
throw new WeiZhiException();
}
Test.getUpdate(connection,
"INSERT INTO jinelog(uid,ctime,jin,type,info) VALUES(?,?,SYSDATE(),?,?,?)",
CardNumber, meoney, "进", "存款业务"
);
connection.commit();
}
} finally {
connection.close();
}
}
public static void KaiHu(String CardNumber1, String password, String name) throws SQLException, KaHaoException {
Connection connection = null;
try {
connection = Test.getInstance().getDataSource2();
ResultSet resultSet = Test.getResultSet(connection,
"select*from Trading where CardNumber=?",
CardNumber1);
if (resultSet.next()) {
throw new KaHaoException();
}
int a = Test.getUpdate(connection,
"INSERT INTO Trading(CardNumber,password,name,meoney,error,shijiantime) values(?,?,?,0,0,sysdate())",
CardNumber1, password, name);
if (a < 1) {
throw new KaHaoException();
}
connection.commit();
} finally {
connection.close();
}
}
public static void ZhuanZhang(String CardNumber, float jine, String id, String name) throws KaHaoException, YeBuZhuException, ToKaHaoErrorException, WeiZhiException, SQLException {
if (jine <= 0) {
throw new YeBuZhuException();
}
Connection connection = null;
try {
connection = Test.getInstance().getDataSource2();
ResultSet rs2 = Test.getResultSet(connection,
"select*from Trading where CardNumber=? and name=?",
id, name);
if (!rs2.next()) {
throw new ToKaHaoErrorException();
}
ResultSet rs = Test.getResultSet(connection,
"select*from Trading where CardNumber=?",
CardNumber);
rs.next();
if (rs.getFloat("meoney") < jine) {
throw new YeBuZhuException();
}
int a = Test.getUpdate(connection,
"update Trading set meoney=meoney-? where CardNumber=?",
jine, CardNumber);
if (a <= 0) {
throw new KaHaoException();
}
int bb = Test.getUpdate(connection,
"INSERT INTO jinelog(uid,ctime,jin,type,info) values(?,?,sysdate(),?,?,?)",
CardNumber, jine, "出", "转账业务");
System.out.println(2);
if (bb <= 0) {
throw new WeiZhiException();
}
int tt = Test.getUpdate(connection,
"update Trading set meoney=meoney+? where CardNumber=?",
jine, id);
if (tt <= 0) {
throw new WeiZhiException();
}
int aa = Test.getUpdate(connection,
"INSERT INTO jinelog(uid,ctime,jin,type,info) values(?,?,sysdate(),?,?,?)",
id, jine, "进", "转账业务");
if (aa <= 0) {
throw new WeiZhiException();
}
connection.commit();
} finally {
connection.close();
}
}
}选择功能代码：
import YeWu.*;
import java.sql.SQLException;
import java.util.Scanner;
public class Test3 {
public static void main(String[] args) {
Scanner input = new Scanner(System.in);
while (true) {
System.out.println("---------------------");
System.out.println(" 欢迎来到渣渣辉银行 ");
System.out.println("---------------------");
System.out.println("请选择您要办理的业务");
System.out.println("1.登录");
System.out.println("2.退出");
System.out.println("3.开户");
int XuanZhe = input.nextInt();
if (XuanZhe == 2) {
break;
} else if (XuanZhe == 1) {
System.out.print("请输入您的账号:");
String ZhangHu = input.next();
System.out.print("请输入您的密码:");
String MiMa = input.next();
try {
Test2.YeWu(ZhangHu, MiMa);
System.out.println("登录成功");
while (true) {
System.out.println("1.查询");
System.out.println("2.取款");
System.out.println("3.存款");
System.out.println("4.转账");
System.out.println("5.退出");
int xuanzhe = input.nextInt();
if (xuanzhe == 5) {
break;
} else if (xuanzhe == 1) {
float j = Test2.CaXun(ZhangHu);
System.out.println(j);
} else if (xuanzhe == 2) {
System.out.print("请输入您的取款金额:");
float ji = input.nextFloat();
try {
Test2.QuKuan(ZhangHu, ji);
} catch (YeBuZhuException e) {
System.out.println("余额不足");
} catch (WeiZhiException e) {
System.out.println("未知错误");
}
} else if (xuanzhe == 3) {
System.out.print("请输入您的存款金额:");
float ji = input.nextFloat();
try {
Test2.CunKuan(ZhangHu, ji, MiMa);
} catch (YeBuZhuException e) {
System.out.println("余额不足");
} catch (WeiZhiException e) {
System.out.println("未知错误");
}
} else if (xuanzhe == 4) {
System.out.print("请输入您的转账金额:");
float je = input.nextFloat();
System.out.print("请输入您的转账账户:");
String id = input.next();
System.out.print("请输入您转账对象的姓名:");
String name = input.next();
try {
Test2.ZhuanZhang(ZhangHu, je, id, name);
} catch (ToKaHaoErrorException e) {
System.out.println("您转账的用户不存在");
} catch (WeiZhiException e) {
System.out.println("未知错误");
;
} catch (YeBuZhuException e) {
System.out.println("您的金额不足");
;
}
}
}
} catch (SQLException e) {
System.out.println("连接错误");
} catch (KaHaoException e) {
System.out.println("卡号错误");
} catch (DongJieException e) {
System.out.println("账号已被冻结");
} catch (MimaErrorException e) {
System.out.println("密码错误");
}
} else if (XuanZhe == 3) {
System.out.print("请输入您的账号:");
String ZhangHao = input.next();
System.out.print("请输入您的密码:");
String MiMa = input.next();
System.out.print("请输入您的真实姓名:");
String name = input.next();
try {
Test2.KaiHu(ZhangHao, MiMa, name);
} catch (SQLException e) {
System.out.println("连接错误");
} catch (KaHaoException e) {
System.out.println("卡号错误");
}
}
}
}
}
