初学者之Java与MySQL结合初步完成ATM项目

1 前期工作

1.1 导入jar包

先下载与本机MySQL版本对应的jar包，并复制这个jar包。

1.2 复制到idea

打开IDEA，创建：项目的new->Directory，命名为lib，一般存放jar包的文件都会命名为lib。

右键lib,把之前打开jar包复制到lib里面。

点击jar包，选择添加成库。

2 idea与MySQL建立连接

步骤
1.注册驱动
2. 获取数据库连接对象 Connection
3. 定义sql
4. 获取执行sql语句的对象 Statement
5. 执行sql，接受返回结果
6. 处理结果
7. 释放资源

代码演示：

public class ConSqlIn {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/school";//或者 jdbc:mysql://127.0.0.1/school
        String user = "root";
        String  password = "root";
        String loc = "com.mysql.cj.jdbc.Driver";
        String sql = "select * from student";//查询学生表
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            Class.forName(loc);//注册驱动
            connection = DriverManager.getConnection(url, user, password);//获取连接
            statement = connection.createStatement();//获取传输器
            ResultSet resultSet = statement.executeQuery(sql);//执行SQL
            while(resultSet.next()){//遍历结果集
                String sno = resultSet.getString(1);//第一列的值
                String sname = resultSet.getString(2);
                String sage = resultSet.getString(3);
                String ssex = resultSet.getString(4);
                String s = String.format("%-10s%-12s%-10s%-4s", sno, sname, sage, ssex);
                System.out.println(s);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //释放资源
            resultSet .close();
            statement .close();
            connection.close();
        }
    }
}

3 ATM项目

3.1 项目内容

数据表的字段包括id主键、用户名、用户密码以及账户余额。
该项目内容包括创建数据表、插入数据、删除数据、增加字段、删除字段、显示数据表内容、以及登录数据表。
用户选择登录数据表之后可以选择查询余额、存款、取款以及转账等功能。

3.2 源码

package cn.tedu.consqltest;
import com.mysql.cj.protocol.Resultset;
import javax.xml.transform.Result;
import java.sql.*;
import java.util.Scanner;
/*
 * @Author 作者：曾帅锅
 * @Project 项目：ConSql
 * @Time 时间：2021/7/2 18:56
 */
public class HomeTest {
    static Scanner scanner = new Scanner(System.in);
    static String url = "jdbc:mysql://127.0.0.1/school";
    static String user = "root";
    static String password = "root";
    static String loc = "com.mysql.cj.jdbc.Driver";
    static String username = null;
    static String userpw;
    public static void main(String[] args) throws Exception {
        function();
//        connectSql();//建立连接
//        createUser();//创建用户表

//        addUser();//增加字段
//        queryUser();//查询用户表
//        insertUser();//注册新用户
//        login();//登录用户表
    }

    private static void function() throws SQLException, ClassNotFoundException, InterruptedException {
        System.out.println("===============================================================================================");
        System.out.println("1代表创建数据表,2代表登录数据表,3代表查询数据表,4代表注册用户,5代表删除用户,6代表增加字段,7代表删除字段!"+" ||");
        System.out.println("===============================================================================================");
        Thread.sleep(1000);
        System.out.println("请输入你要执行的操作选项！");
        int input = scanner.nextInt();
        switch (input){
            case 1 : createUser();break;
            case 2 : login();break;
            case 3 : queryUser();break;
            case 4 : insertUser();break;
            case 5 : delUser();break;
            case 6 : addField();break;
            case 7 : deleteField();break;
        }
    }

    //登录功能
    public static void login() throws SQLException, ClassNotFoundException, InterruptedException {
        Scanner scan = new Scanner(System.in);
        System.out.println("============");
        System.out.println("请输入用户名：");
        username = scan.nextLine();
        String sqlU = "select * from user where find_in_set('"+username+"',name)";
        ResultSet resultSet = connectSql().executeQuery(sqlU);
        if (resultSet.next()){
            System.out.println("=============");
//            System.out.println("用户名输入成功！");
//            System.out.println("=============");
            System.out.println("请输入用户密码：");
            userpw = scan.nextLine();
            String sqlP = "select * from (select * from user where name = '"+username+"') as u1 where find_in_set("+userpw+",u1.password)";
            ResultSet resultSet1 = connectSql().executeQuery(sqlP);
            if (resultSet1.next()){
//                System.out.println("=============");
//                System.out.println("密码输入正确！");
//                System.out.println("=============");
                Thread.sleep(500);
                option(resultSet);
            }
            else {
                System.out.println("===============================");
                System.out.println("用户密码输入错误！正在为你重新登录~");
                Thread.sleep(1000);
                login();
            }
        }
        else {
            System.out.println("==============================");
            System.out.println("用户名输入错误！正在为你重新登录~");
            Thread.sleep(1000);
            login();
        }
    }
    //选择操作选项功能
    public static void option(ResultSet res) throws SQLException, InterruptedException, ClassNotFoundException {
        Scanner scan = new Scanner(System.in);
        System.out.println("=========================================================");
        System.out.println("0代表重新登录;1代表查询余额;2代表存款;3代表取款;4代表转账");
        System.out.println("==========================================================");
        System.out.println("请输入你要选择的操作选项！");
        String input = scan.nextLine();
        switch (input){
            case "0" : login();break;//登录
            case "1" : queryBl(res);break;//查询
            case "2" : deposit(res);break;//存款
            case "3" : withdrawl(res);break;//取款
            case "4" : transfer(res);break;//转账
        }
    }
    //转账功能
    private static void transfer(ResultSet res) throws SQLException, ClassNotFoundException, InterruptedException {
        Scanner scanner1 = new Scanner(System.in);
        System.out.println("================");
        System.out.println("请输入转入用户名：");
        String userother = scanner1.nextLine();
        String sqluo = "select * from user where name = '"+userother+"'";//判断是否存在此用户
        ResultSet ro = connectSql().executeQuery(sqluo);
        if (ro.next()){
            System.out.println("===========");
            System.out.println("存在此用户！");
            Thread.sleep(200);
            System.out.println("请输入转账金额：");
//            System.out.println("===========");
            double num = scanner1.nextDouble();
            if(num<=res.getDouble(4)){//转账金额不能超过当前余额
                String sqlthis = "update user set balance = "+(res.getDouble(4)-num)+" where name = '"+res.getString(2)+"'";
                connectSql().executeUpdate(sqlthis);//本账户余额减去转账金额
                String sqlother = "update user set balance = "+(ro.getDouble(4)+num)+" where name = '"+ro.getString(2)+"'";
                connectSql().executeUpdate(sqlother);//转入账户余额加上转账金额
                System.out.println("===============");
                System.out.println("转账成功！");
                System.out.print("你的账户当前可用余额为：");
                Thread.sleep(500);
                String sqlbl = "select * from user where name = '"+res.getString(2)+"'";
                ResultSet rthis = connectSql().executeQuery(sqlbl);
                rthis.next();
                System.out.println(rthis.getDouble(4));//输出当前余额
                System.out.println("======================");
                Thread.sleep(500);
                option(rthis);
            }
            else {//转账金额大于当前余额
                System.out.println("==================================");
                System.out.println("转账金额超出当前余额！请退出重新转账！");
                System.out.println("==================================");
                Thread.sleep(500);
                transfer(res);
            }
            ro.close();
        }
        else {
            System.out.println("=======================");
            System.out.println("不存在此用户！请重新输入！");
            System.out.println("=======================");
            Thread.sleep(500);
            transfer(res);
        }
    }
    //取款功能
    private static void withdrawl(ResultSet res) throws SQLException, ClassNotFoundException, InterruptedException {
        Scanner scanner1 = new Scanner(System.in);
        System.out.println("==============");
        System.out.println("请输入取款金额：");
        double num = scanner1.nextDouble();
        if (num<res.getDouble(4)){
            String sqlwd = "update user set balance = "+(res.getDouble(4)-num)+" where name = '"+res.getString(2)+"'";
            connectSql().executeUpdate(sqlwd);
            Thread.sleep(500);
            System.out.println("取款成功！");
            System.out.print("你的账户当前可用余额为：");
            Thread.sleep(1000);
            String sqlUe = "select * from user where name = '"+res.getString(2)+"'";
            ResultSet res1 = connectSql().executeQuery(sqlUe);
            res1.next();
            System.out.println(res1.getDouble(4));
            System.out.println("========================");
            Thread.sleep(500);
            option(res1);
        }
        else {
            System.out.println("==================================");
            System.out.println("取款金额超出当前余额！请退出重新取款！");
            System.out.println("==================================");
            Thread.sleep(500);
            transfer(res);
        }
    }
    //存款功能
    private static void deposit(ResultSet res) throws SQLException, ClassNotFoundException, InterruptedException {
        Scanner scanner1 = new Scanner(System.in);
        Statement statement = connectSql();
        System.out.println("==============");
        System.out.println("请输入存款金额：");
        double num = scanner1.nextDouble();
        String sqlDe = "update user set balance = "+(res.getDouble(4)+num)+" where name = '"+res.getString(2)+"'";
        statement.executeUpdate(sqlDe);//更新当前余额
//        queryUser();
        System.out.println("=========");
        System.out.println("存款成功！");
        System.out.print("你的账户当前可用余额为：");
        Thread.sleep(500);
        String sqlUe = "select * from user where name = '"+res.getString(2)+"'";
        ResultSet res1 = statement.executeQuery(sqlUe);
        res1.next();
        System.out.println(res1.getDouble(4));//输出当前余额
        System.out.println("=========================");
        Thread.sleep(500);
        option(res1);
    }
    private static void queryBl(ResultSet res) throws InterruptedException, SQLException, ClassNotFoundException {

        System.out.println("==================");
        System.out.println("正在为你查询,请稍后~~");
        Thread.sleep(1000);
        System.out.print("你的账户当前可用余额为：");
        Thread.sleep(500);
        System.out.println(res.getString(4));
        System.out.println("==================");
        Thread.sleep(1000);
        option(res);
    }
    //创建用户表
    public static void createUser() throws SQLException, ClassNotFoundException, InterruptedException {//创建用户表
        String sql1 = "create table user( id int primary key auto_increment, name varchar(10), password int(6))";//创建用户表
        connectSql().executeUpdate(sql1);
        System.out.println("==============");
        System.out.println("成功创建数据表！");
        System.out.println("==============");
        Thread.sleep(1000);
        function();
    }
    //向表中注册新用户
    public static void insertUser() throws SQLException, ClassNotFoundException, InterruptedException {
        Scanner scanner1 = new Scanner(System.in);
        System.out.println("============");
        System.out.println("请输入用户名：");
        String newusername = scanner1.nextLine();
        String sqlcheck = "select count(name) from user where name = '"+newusername+"'";
        ResultSet rc = connectSql().executeQuery(sqlcheck);
        rc.next();
        if (rc.getInt(1) != 0){
            System.out.println(rc.getString(1));
            System.out.println("==========================");
            System.out.println("该用户已经存在！请重新输入！");
            System.out.println("==========================");
            Thread.sleep(500);
            insertUser();
        }
        System.out.println("================");
        System.out.println("请输入用户密码：");
        System.out.println("================");
        String newpassword = scanner1.nextLine();
        String sqlnew = "insert into user (name,password) values ('"+newusername+"',"+newpassword+")";//插入数据

//        String sql2 = "insert into user values (1,'曾帅锅',123),(2,'彭自立',234),(3,'刘万权',345)";//插入数据
//        connectSql().executeUpdate(sql2);
        connectSql().executeUpdate(sqlnew);
        Thread.sleep(1000);
        System.out.println("==============");
        System.out.println("成功注册新用户！");
        System.out.println("==============");
//        String sqllog = "select * from user where name = '"+newusername+"'";
//        ResultSet rlog = connectSql().executeQuery(sqllog);
        Thread.sleep(1000);
        function();
    }
    public static void delUser() throws SQLException, ClassNotFoundException, InterruptedException {
        Scanner scannerdeluser= new Scanner(System.in);
        System.out.println("===================");
        System.out.println("请输入要删除的用户名！");
        String username = scannerdeluser.nextLine();
        String sqlun = "select count(*) from user where name = '"+username+"'";
        ResultSet rs = connectSql().executeQuery(sqlun);
        rs.next();
        if (rs.getInt(1) != 0){
            System.out.println("==========================");
            System.out.println("存在该用户！可以执行删除操作！");
            String sqldu = "delete from user where name = '"+username+"'";
            connectSql().executeUpdate(sqldu);
            Thread.sleep(1000);
            System.out.println("成功删除该用户！");
            System.out.println("==============");
            Thread.sleep(1000);
            function();
        }
        else {
            System.out.println("================================");
            System.out.println("该用户不存在！请确定要删除的用户名！");
            System.out.println("================================");
            Thread.sleep(500);
            delUser();
        }
    }
    public static void addField() throws SQLException, ClassNotFoundException, InterruptedException {
        Scanner scanneradd = new Scanner(System.in);
        System.out.println("==============================");
        System.out.println("请输入你要执行增加字段的SQL语句！");
        String sqladd = scanneradd.nextLine();
        connectSql().executeUpdate(sqladd);
//        String sqlUp = "alter table user add column balance numeric(10,3) default 0.0";
//        connectSql().executeUpdate(sqlUp);
        System.out.println("====================");
        System.out.println("正在插入字段,请稍后！");
        Thread.sleep(1000);
        System.out.println("执行插入字段操作成功！");
        System.out.println("==============================");
        Thread.sleep(500);
        function();
    }
    public static void deleteField() throws SQLException, ClassNotFoundException, InterruptedException {
        Scanner scannerdel = new Scanner(System.in);
        System.out.println("=================================");
        System.out.println("请输入你要执行删除字段操作的SQL语句！");
        String sqldel = scannerdel.nextLine();
        connectSql().executeUpdate(sqldel);
        System.out.println("===================");
        System.out.println("正在删除字段,请稍后！");
        Thread.sleep(1000);
        System.out.println("执行删除字段操作成功！！！");
        System.out.println("=================================");
        Thread.sleep(500);
        function();
    }
    public static void queryUser() {
        ResultSet resultSet = null;
        try{
            System.out.println("==============================");
            String sql3 = "select * from user";//查询用户表
            resultSet = connectSql().executeQuery(sql3);
            System.out.println("正在查询数据表,请稍后！");
            Thread.sleep(1000);
            while (resultSet.next()){
                String id = resultSet.getString(1);
                String uname = resultSet.getString(2);
                String upassword = resultSet.getString(3);
                String ubalance = resultSet.getString(4);
//            String uh = resultSet.getString(5);
                System.out.println(id+"\t"+uname+"\t"+upassword+"\t\t"+ubalance+"\t\t");
            }
            System.out.println("==============================");
            System.out.println("数据表已成功显示！");
            Thread.sleep(500);
            function();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(resultSet,null,null);
        }

    }
    public static Statement connectSql() throws ClassNotFoundException, SQLException {//连接MySQL
        Class.forName(loc);//注册驱动
        Connection connection = DriverManager.getConnection(url, user, password);//创建连接
        Statement statement = connection.createStatement();//创建传输器
        return statement;
    }
}
class JDBCUtils{//创建工具类，实现每个功能都需要关闭资源的任务（由于这个工具类是整体代码完成后最后才添加的，所以这个工具类基本还没有用到，而且很多方法里面我也没有实现关闭资源的操作）
    public static void close(ResultSet rs,Statement st,Connection con){
        if (rs!=null){
            try {
                rs.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        if (st!=null){
            try {
                st.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }if (con!=null){
            try {
                con.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }
}

4 出现的问题

4.1 Scanner输入问题

如果定义了成员变量scanner输入方式，则不能在方法里一会儿使用scanner.nextLine()，一会儿又使用scanner.nextInt()之类的，不然自调用本方法时下一次会直接跳过输入。需保证方法里的scanner输入方法一致。

4.2 ResultSet获取结果集问题

在定义了resultset结果集的时候，不能直接使用resultset.getString(n)之类的方法获取第n列的内容，如果想直接获取内容，需要在getstring()前面先调用一下resultset.next()，使指针指向当前行，确保当前行有内容。