【大数据开发】JDBC编程练习——实现登录、注册、转账day27

账户表脚本

-- MySQL dump 10.13  Distrib 5.7.17, for Win64 (x86_64)
--
-- Host: localhost    Database: db1
-- ------------------------------------------------------
-- Server version	5.7.17-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `bank_account`
--

DROP TABLE IF EXISTS `bank_account`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `bank_account` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `account_id` varchar(18) NOT NULL COMMENT '用户账号',
  `account_balance` double(10,2) DEFAULT NULL COMMENT '账户余额',
  `user_name` varchar(20) NOT NULL COMMENT '用户名称',
  `user_pwd` varchar(18) DEFAULT NULL COMMENT '用户密码',
  `user_idcard` varchar(18) DEFAULT NULL COMMENT '身份证',
  `oper_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '操作日期',
  `gender` enum('f','m') DEFAULT NULL COMMENT 'f 表示女性，m表示男性',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_pwd` (`user_pwd`),
  UNIQUE KEY `user_idcard` (`user_idcard`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `bank_account`
--

LOCK TABLES `bank_account` WRITE;
/*!40000 ALTER TABLE `bank_account` DISABLE KEYS */;
INSERT INTO `bank_account` VALUES (1,'6225113088436225',180000.00,'zhugeliang','zgl123456','100000100010101000','2020-06-05 05:57:31','m'),(2,'6225113088436226',11000.00,'zhouyu','zy123456','100000100010101001','2020-06-05 07:21:43','f'),(3,'6225113088436227',209000.00,'caocao','cc123456','100000100010101002','2020-06-05 06:22:21','m'),(4,'6225113088436228',500.00,'niumo','nm123456','100000100010101003','2019-03-01 02:10:10','m');
/*!40000 ALTER TABLE `bank_account` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2020-08-03 17:52:10

前面已经将JDBC连接封装成工具类

1.使用账户表实现转账

package transfer;
//1.使用账户表实现转账

import util.DBUtil;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.*;

public class transferTask {
    public static void main(String[] args) throws SQLException, IOException {
        //驱动加载器获取连接
        Connection connection = DBUtil.getConnection();

        BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
        System.out.print("请输入你的账户：");
        String transAcc = br.readLine();
        System.out.print("请输入你的密码：");
        String transAccPwd = br.readLine();

        System.out.print("请输入转入账户：");
        String acceptAcc = br.readLine();
        System.out.print("请输入转账金额：");
        String money = br.readLine();


        //查询用户账户是否存在
        String transAccSql = "select * from bank_account where account_id=? and user_pwd=?";
        PreparedStatement pst = connection.prepareStatement(transAccSql);
        pst.setString(1,transAcc);
        pst.setString(2,transAccPwd);
        ResultSet rs = pst.executeQuery();
        if (!rs.next())
            throw new RuntimeException("用户名或密码错误!");

        //查询转账账户是否存在
        String acceptAccSql = "select account_balance from bank_account where account_id=?";
        pst = connection.prepareStatement(acceptAccSql);
        pst.setString(1,acceptAcc);
        rs = pst.executeQuery();
        if(!rs.next())
            throw new RuntimeException("转入账户不存在!");

        //如果账户存在，则发生转账
        //用户账户的余额应当大于转账金额
        String restMoneySql = "select * from bank_account where account_id=? and account_balance>=?";
        pst = connection.prepareStatement(restMoneySql);
        pst.setString(1,transAcc);
        pst.setString(2,money);
        rs = pst.executeQuery();
        if(!rs.next())
        {
            System.out.println("账号余额不足！");
        }
        else {

            String transSql = "update bank_account set account_balance=account_balance-? where account_id=?";
            pst = connection.prepareStatement(transSql);
            pst.setString(1, money);
            pst.setString(2, transAcc);
            int k = pst.executeUpdate();
            if (k > 0)
                System.out.println("你的账户扣除" + money + "元");

            //更改接收方金额
            String acceptSql = "update bank_account set account_balance=account_balance+? where account_id=?";
            pst = connection.prepareStatement(acceptSql);
            pst.setString(1, money);
            pst.setString(2, acceptAcc);
            int j = pst.executeUpdate();
            if (j > 0)
                System.out.println(acceptAcc + "账户增加" + money + "元");
        }

        DBUtil.close(rs,null,pst,connection);
    }
}

2.使用账户表实现登录

package user;

import util.DBUtil;

import java.io.*;
import java.sql.*;


public class Login {
    public static void main(String[] args) throws SQLException, IOException
    {
        BufferedReader bf = new BufferedReader(new InputStreamReader(System.in));
        System.out.print("请输入你的账号：");
        String account_id = bf.readLine();
        System.out.print("请输入你的密码：");
        String user_pwd= bf.readLine();

        Connection conn = DBUtil.getConnection();
        String sql = "select * from bank_account where account_id=? and user_pwd=?";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1,account_id);
        pst.setString(2,user_pwd);

        ResultSet rs = pst.executeQuery();
        if (rs.next())
        {
            System.out.println("登录成功！");
        }
        else
        {
            System.out.println("用户名或密码错误！");
        }


    }
}

3.使用账户表实现注册

package user;

import util.DBUtil;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


//使用账户表实现注册
public class Register {
    public static void main(String[] args) throws SQLException, IOException {
        Connection conn = DBUtil.getConnection();

        BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
        System.out.print("请输入账号id：");
        String account_id = br.readLine();
        System.out.print("请输入用户名：");
        String user_name = br.readLine();
        System.out.print("请输入密码：");
        String user_pwd = br.readLine();
        System.out.print("请输入银行卡号：");
        String user_idCard = br.readLine();
        System.out.print("请输入性别：");
        String gender = br.readLine();



        //判断账号是否在表中
        String userNaSql = "select * from bank_account where account_id=?";
        PreparedStatement pst = conn.prepareStatement(userNaSql);
        pst.setString(1,user_name);
        ResultSet rs = pst.executeQuery();
        if(rs.next())
            System.out.println("账号名已存在！");
        else{
            String regSql = "insert into bank_account(account_id,account_balance,user_name,user_pwd,user_idCard,gender) values(?,0,?,?,?,?)";
            pst = conn.prepareStatement(regSql);
            pst.setString(1,account_id);
            pst.setString(2,user_name);
            pst.setString(3,user_pwd);
            pst.setString(4,user_idCard);
            pst.setString(5,gender);
            int i = pst.executeUpdate();
            if(i>0)
            {
                System.out.println("注册成功");
            }
            else
                System.out.println("注册失败");
        }

        DBUtil.close(rs,null,pst,conn);
    }
}