jdbc练习题

简介: 连接代码import com.mchange.v2.c3p0.ComboPooledDataSource;import javax.sql.DataSource;import java.beans.
连接代码
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("卡号错误");
                }
            }
        }
    }
}



目录
相关文章
|
1月前
|
前端开发 Java 数据库连接
JDBC是什么,2024年前端笔试题总结
JDBC是什么,2024年前端笔试题总结
|
11月前
|
SQL druid Java
JDBC BasicDAO详解(通俗易懂)
JDBC 第七节 详解BasicDAO 通俗易懂!
82 0
|
11月前
|
Java 数据库连接 数据库
JavaWeb第二章课后题 JDBC进阶
JavaWeb第二章课后题 JDBC进阶
62 0
|
SQL Ubuntu Java
一文搞懂JDBC应该怎么用,附代码实例
说到Java的常见框架,JDBC一定是榜上有名。 虽然大多数人会使用Mybatis来做Java应用和MySQL之间的数据交互,但了解JDBC依然是一个Java工程师的基本功。 本篇文章,就来介绍一下JDBC这个框架,最后内会附上代码实例。
140 1
一文搞懂JDBC应该怎么用,附代码实例
|
SQL druid 安全
JDBC超详细笔记
JDBC超详细笔记
81 0
|
SQL Java 关系型数据库
JDBC 编程
JDBC 编程
87 0
JDBC 编程
|
SQL 监控 Oracle
|
SQL 存储 缓存
|
SQL 存储 安全
JDBC入门详解
JDBC入门详解
128 0
JDBC入门详解