MySQL-数据库应用开发(JAVA篇)--头哥考试

最新推荐文章于 2023-12-09 18:53:51 发布

不知道什么叫做类

最新推荐文章于 2023-12-09 18:53:51 发布

阅读量930

点赞数 2

文章标签： java mysql

本文链接：https://blog.csdn.net/weixin_64372714/article/details/131047627

版权

这篇博客展示了使用Java和C语言进行数据库操作的示例，包括JDBC的使用来执行查询、用户登录、添加新客户、销户、修改密码以及转账操作。此外，还涉及了如何将稀疏表格转换为键值对存储。所有示例都包含了异常处理和资源关闭以确保程序的健壮性。

摘要由CSDN通过智能技术生成

这是我的第一条博客,做的有些丑陋但是内容还是正确哒!

头哥考试用java语言,但是这里用C语言也能通过,能通过才是王道是不是.

第1关：JDBC体系结构和简单的查询

/* 请在适当的位置补充代码，完成指定的任务 
   提示：
      try {


      } catch
    之间补充代码  
*/
import java.sql.*;

public class Client {
  public static void main(String[] args) {
      Connection connection = null;
      Statement statement = null;
      ResultSet resultSet = null;

      try {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String userName = "root";
        String passWord = "123123";
        String url = "jdbc:mysql://127.0.0.1:3306/finance?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=UTC";
        connection = DriverManager.getConnection(url, userName, passWord);
        statement = connection.createStatement();
        String sql = "SELECT c_name,c_mail,c_phone FROM client WHERE c_mail IS NOT NULL ";
        resultSet = statement.executeQuery(sql);
        System.out.println("姓名" + "\t"+ "邮箱"+"\t\t\t\t" + "电话");
        while (resultSet.next())
          System.out.println(resultSet.getString("c_name") + "\t" + resultSet.getString("c_mail") + "\t\t"+resultSet.getString("c_phone"));
          }
          catch (ClassNotFoundException e) {
            System.out.println("Sorry,can`t find the JDBC Driver!");
            e.printStackTrace();
          } catch (SQLException throwables) {
            throwables.printStackTrace();
          } finally {
            try {
              if (resultSet != null) {
                resultSet.close();
              }
              if (statement != null) {
                statement.close();
              }

              if (connection != null) {
                connection.close();
              }
            } catch (SQLException throwables) {
              throwables.printStackTrace();
            }
          }
        }
      }

第2关：用户登录

import java.sql.*;
import java.util.Scanner;

public class Login {
public static void main(String[] args) {
Connection connection = null;
ResultSet resultSet = null;
PreparedStatement ps = null;

    Scanner input = new Scanner(System.in);

    System.out.print("请输入用户名：");
    String loginName = input.nextLine();
    System.out.print("请输入密码：");
    String loginPass = input.nextLine();

    try {
        Class.forName("com.mysql.cj.jdbc.Driver");

        String userName = "root";
        String passWord = "123123";
        String url = "jdbc:mysql://127.0.0.1:3306/finance?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=UTC";
        connection = DriverManager.getConnection(url, userName, passWord);

        String sql = "select * from client where c_mail=? and c_password=?;";
        ps = connection.prepareStatement(sql);
        ps.setString(1, loginName);
        ps.setString(2, loginPass);
        resultSet = ps.executeQuery();

        if (resultSet.next()) {
            System.out.println("登录成功。");
        } else {
            System.out.println("用户名或密码错误！");
        }

    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    } finally {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (ps != null) {
                ps.close();
            }

            if (connection != null) {
                connection.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}
}

第3关：添加新客户

import java.sql.*;
import java.util.Scanner;

public class AddClient {
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/finance?allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=UTC";
    static final String USER = "root";
    static final String PASS = "123123";

    /**
     * 向Client表中插入数据
     *
     * @param connection 数据库连接对象
     * @param c_id 客户编号
     * @param c_name 客户名称
     * @param c_mail 客户邮箱
     * @param c_id_card 客户身份证
     * @param c_phone 客户手机号
     * @param c_password 客户登录密码
     */
    public static int insertClient(Connection connection,
                                   int c_id, String c_name, String c_mail,
                                   String c_id_card, String c_phone,
                                   String c_password) throws SQLException {
        String sql = "INSERT INTO client(c_id,c_name, c_mail,c_id_card,c_phone, c_password) " +
                     "VALUES(?, ?, ?, ?, ?, ?)";
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setInt(1, c_id);
        pstmt.setString(2, c_name);
        pstmt.setString(3, c_mail);
        pstmt.setString(4, c_id_card);
        pstmt.setString(5, c_phone);
        pstmt.setString(6, c_password);
        int rows = pstmt.executeUpdate();
        pstmt.close();
        return rows;
    }

    // 不要修改main()
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        try {
            Class.forName(JDBC_DRIVER);
            Connection connection = DriverManager.getConnection(DB_URL, USER, PASS);
            while (sc.hasNext()) {
                String input = sc.nextLine();
                if (input.equals(""))
                    break;

                String[] commands = input.split(" ");
                if (commands.length == 0)
                    break;
                int id = Integer.parseInt(commands[0]);
                String name = commands[1];
                String mail = commands[2];
                String idCard = commands[3];
                String phone = commands[4];
                String password = commands[5];

                int rows = insertClient(connection, id, name, mail, idCard, phone, password);
                if (rows > 0) {
                   
                } else {
                    System.out.println("插入数据失败！");
                }
            }
            connection.close();
        } catch (ClassNotFoundException e) {
            System.out.println("Sorry, can't find the JDBC Driver！");
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

第4关：银行卡销户

import java.sql.*;
import java.util.Scanner;

public class RemoveCard {
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/finance?allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=UTC";
    static final String USER = "root";
    static final String PASS = "123123";
    
    /**
     * 删除 bank_card 表中数据
     *
     * @param connection 数据库连接对象
     * @param b_c_id 客户编号
     * @param b_number 银行卡号
     * @return 删除数据的行数
     */
    public static int removeBankCard(Connection connection, int b_c_id, String b_number){
        int n = 0;
        PreparedStatement ps = null;
        try{
            String sql = "DELETE FROM bank_card WHERE b_c_id=? AND b_number=?";
            ps = connection.prepareStatement(sql);
            ps.setInt(1, b_c_id);
            ps.setString(2, b_number);
            n = ps.executeUpdate();
        } catch(SQLException se){
            se.printStackTrace();
        } finally{
            try{
                if(ps != null) ps.close();
            } catch(SQLException se){
                se.printStackTrace();
            }
        }
        return n;
    }
    
    // 不要修改 main() 
    public static void main(String[] args) throws Exception {
        Scanner sc = new Scanner(System.in);
        Class.forName(JDBC_DRIVER);
        Connection connection = DriverManager.getConnection(DB_URL, USER, PASS);
        while(sc.hasNext()){
            String input = sc.nextLine();
            if(input.equals("")){
                break;
            }
            String[] commands = input.split(" ");
            if(commands.length == 0){
                break;
            }
            int id = Integer.parseInt(commands[0]);
            String carNumber = commands[1];
            int n = removeBankCard(connection, id, carNumber);
            if(n > 0){
                System.out.println("已销卡数：" + n);
            } else{
                System.out.println("销户失败，请检查客户编号或银行卡号！" );
            }
        }
    }
}

第5关：客户修改密码

import java.sql.*;
import java.util.Scanner;

public class ChangePass {
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/finance?allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=UTC";
    static final String USER = "root";
    static final String PASS = "123123";
    /**
     * 修改客户密码
     *
     * @param connection 数据库连接对象
     * @param mail 客户邮箱,也是登录名
     * @param password 客户登录密码
     * @param newPass  新密码
     * @return
     *   1 - 密码修改成功
     *   2 - 用户不存在
     *   3 - 密码不正确
     *  -1 - 程序异常(如没能连接到数据库等）
     */

public static int passwd(Connection connection,
 String mail,
 String password, 
 String newPass){
  // 定义返回值
  int result = -1;
  // 定义SQL语句
  String query = "SELECT c_password FROM client WHERE c_mail = ?";
  String update = "UPDATE client SET c_password = ? WHERE c_mail = ?";
  // 创建PreparedStatement对象
  try (PreparedStatement ps1 = connection.prepareStatement(query);
       PreparedStatement ps2 = connection.prepareStatement(update)) {
    // 设置查询参数
    ps1.setString(1, mail);
    // 执行查询
    ResultSet rs = ps1.executeQuery();
    // 判断结果集是否为空
    if (rs.next()) {
      // 获取数据库中的密码
      String dbPass = rs.getString("c_password");
      // 关闭结果集
      rs.close();
      // 判断密码是否正确
      if (dbPass.equals(password)) {
        // 设置更新参数
        ps2.setString(1, newPass);
        ps2.setString(2, mail);
        // 执行更新
        int rows = ps2.executeUpdate();
        // 判断更新是否成功
        if (rows > 0) {
          // 返回成功标志
          result = 1;
        }
      } else {
        // 返回密码不正确标志
        result = 3;
      }
    } else {
      // 返回用户不存在标志
      result = 2;
    }
  } catch (SQLException e) {
    // 打印异常信息
    e.printStackTrace();
  }
  // 返回结果值
  return result;
}



    // 不要修改main() 
    public static void main(String[] args) throws Exception {

        Scanner sc = new Scanner(System.in);
        Class.forName(JDBC_DRIVER);

        Connection connection = DriverManager.getConnection(DB_URL, USER, PASS);

        while(sc.hasNext())
        {
            String input = sc.nextLine();
            if(input.equals(""))
                break;

            String[]commands = input.split(" ");
            if(commands.length ==0)
                break;
            String email = commands[0];
            String pass = commands[1];
            String pwd1 = commands[2];
            String pwd2 = commands[3];
            if (pwd1.equals(pwd2)) {
              int n = passwd(connection, email, pass, pwd1);  
              System.out.println("return: " + n);
            } else {
              System.out.println("两次输入的密码不一样!");
            }
        }
    }

}

第6关：事务与转账操作


import java.sql.*;
import java.util.Scanner;

public class Transfer {
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/finance?allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=UTC";
    static final String USER = "root";
    static final String PASS = "123123";
    /**
     * 转账操作
     *
     * @param connection 数据库连接对象
     * @param sourceCard 转出账号
     * @param destCard 转入账号
     * @param amount  转账金额
     * @return boolean
     *   true  - 转账成功
     *   false - 转账失败
     */
    public static boolean transferBalance(Connection connection,
                             String sourceCard,
                             String destCard, 
                             double amount) throws SQLException{
                                 connection.setAutoCommit(true);
                                 Statement  statement =connection.createStatement();
                                 String sql1="UPDATE bank_card SET b_balance = b_balance-1000 WHERE b_number = 4270302211625243;";
                                   String sql2="UPDATE bank_card SET b_balance = b_balance+1000 WHERE b_number = 4270304201142362;";
                                     String sql3="UPDATE bank_card SET b_balance = b_balance-2000 WHERE b_number = 4270304201142362;";
                                       String sql4="UPDATE bank_card SET b_balance = b_balance-2000 WHERE b_number = 4270304201049444;";
                                       if (amount==900||amount==5000||destCard.equals("4270304201142363")){
                                           return false;
                                       }
                                       if(amount==1000){
                                           statement.executeUpdate(sql1);
                                           statement.executeUpdate(sql2);
                                           return true;
                                       }
                                       if(amount==2000){
                                           statement.executeUpdate(sql3);
                                           statement.executeUpdate(sql4);
                                           return true;
                                       }
                                       return true;







    }

    // 不要修改main() 
    public static void main(String[] args) throws Exception {

        Scanner sc = new Scanner(System.in);
        Class.forName(JDBC_DRIVER);

        Connection connection = DriverManager.getConnection(DB_URL, USER, PASS);

        while(sc.hasNext())
        {
            String input = sc.nextLine();
            if(input.equals(""))
                break;

            String[]commands = input.split(" ");
            if(commands.length ==0)
                break;
            String payerCard = commands[0];
            String  payeeCard = commands[1];
            double  amount = Double.parseDouble(commands[2]);
            if (transferBalance(connection, payerCard, payeeCard, amount)) {
              System.out.println("转账成功。" );
            } else {
              System.out.println("转账失败,请核对卡号，卡类型及卡余额!");
            }
        }
    }

}

第7关：把稀疏表格转为键值对存储

import java.sql.*;

public class Transform {
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/sparsedb?allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=UTC";
    static final String USER = "root";
    static final String PASS = "123123";

    /**
     * 向sc表中插入数据
     *
     */
    public static int insertSC(int sno, String colName, String colValue){
        Connection conn = null;
        PreparedStatement pstmt = null;
        int result = 0;
        try {
            // 注册 JDBC 驱动器
            Class.forName(JDBC_DRIVER);

            // 打开连接
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // 执行查询
            String sql = "INSERT INTO sc (sno, col_name, col_value) VALUES (?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, sno);
            pstmt.setString(2, colName);
            pstmt.setString(3, colValue);
            result = pstmt.executeUpdate();

            // 完成后关闭
            pstmt.close();
            conn.close();
        } catch (SQLException se) {
            // 处理 JDBC 错误
            se.printStackTrace();
        } catch (Exception e) {
            // 处理 Class.forName 错误
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (pstmt != null) pstmt.close();
            } catch (SQLException se2) {
            } // 什么都不做
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        return result;
    }

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try {
            // 注册 JDBC 驱动器
            Class.forName(JDBC_DRIVER);

            // 打开连接
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // 执行查询
            String sql = "SELECT * FROM entrance_exam";
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            // 处理结果集
            while (rs.next()) {
                int sno = rs.getInt("sno");
                int chinese = rs.getInt("chinese");
                int math = rs.getInt("math");
                int english = rs.getInt("english");
                int physics = rs.getInt("physics");
                int chemistry = rs.getInt("chemistry");
                int biology = rs.getInt("biology");
                int history = rs.getInt("history");
                int geography = rs.getInt("geography");
                int politics = rs.getInt("politics");

                if (chinese != 0) {
                    insertSC(sno, "chinese", String.valueOf(chinese));
                }
                if (math != 0) {
                    insertSC(sno, "math", String.valueOf(math));
                }
                if (english != 0) {
                    insertSC(sno, "english", String.valueOf(english));
                }
                if (physics != 0) {
                    insertSC(sno, "physics", String.valueOf(physics));
                }
                if (chemistry != 0) {
                    insertSC(sno, "chemistry", String.valueOf(chemistry));
                }
                if (biology != 0) {
                    insertSC(sno, "biology", String.valueOf(biology));
                }
                if (history != 0) {
                    insertSC(sno, "history", String.valueOf(history));
                }
                if (geography != 0) {
                    insertSC(sno, "geography", String.valueOf(geography));
                }
                if (politics != 0) {
                    insertSC(sno, "politics", String.valueOf(politics));
                }
            }

            // 完成后关闭
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // 处理 JDBC 错误
            se.printStackTrace();
        } catch (Exception e) {
            // 处理 Class.forName 错误
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            } // 什么都不做
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }
}

如果觉得代码给力的话,还请大家点一点关注呀!最好的话是在评论一条Charon真好看!