OpenGauss数据库-K.应用开发(Java)

原创已于 2025-08-10 14:12:55 修改 · 233 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

于 2025-08-10 14:06:46 首次发布

第一关、JDBC体系结构和简单的查询

/* 请在try-catch之间补充代码，完成指定的任务 
   
      try {
        //在此补充JAVA代码

      } catch
    
*/
import java.sql.*;

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

    try {
     //数据库连接参数，不要改动
      String JDBC_DRIVER = "org.postgresql.Driver";
      String DB_URL = "jdbc:postgresql://127.0.0.1:5432/finance?";
      String USER = "gaussdb";
      String PASS = "Passwd123@123";
      // 注册 JDBC 驱动
       
      // 打开链接
      connection =  DriverManager.getConnection(DB_URL,USER,PASS);
      
      // 执行查询
      statement  =  connection.createStatement();
      String SQL =  "";
      resultSet  =  statement.executeQuery("SELECT c_name,c_mail,c_phone FROM client WHERE c_mail IS NOT NULL");
      
      //显示结果
      System.out.println("姓名\t邮箱\t\t\t\t\t电话");
      while (resultSet.next()) {
        System.out.printf("%s\t%s\t\t%s\n",resultSet.getString("c_name"),resultSet.getString("c_mail"),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();
      }
    }
  }
}

第二关、用户验证

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

public class Login {
  public static void main(String[] args) {
    //声明下文中要使用的connection,resultSet, statement对象
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    
    //接收键盘输入，或重新定向从文件输入
    Scanner input = new Scanner(System.in);

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

    try {
      //连接数据库
      Class.forName("org.postgresql.Driver");
      String userName = "gaussdb";
      String passWord = "Passwd123@123";
      String url = "jdbc:postgresql://localhost:5432/finance?";          
      connection = DriverManager.getConnection(url, userName, passWord);
       
      // 在此补充实现代码:
      String sql = "SELECT COUNT(c_mail) AS \"count\" FROM client WHERE c_mail = ? AND c_password = ?;";
      statement = connection.prepareStatement(sql);
      statement.setString(1,loginName);
      statement.setString(2,loginPass);
      resultSet = statement.executeQuery();

      while (resultSet.next()){
        if (resultSet.getString("count").equals("1")) {
          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 (statement != null) {
          statement.close();
        }

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

第三关、添加客户

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

public class AddClient {
  static final String JDBC_DRIVER = "org.postgresql.Driver";
  static final String DB_URL = "jdbc:postgresql://127.0.0.1:5432/finance?";
  static final String USER = "gaussdb";
  static final String PASS = "Passwd123@123";

  /**
   * 向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) {
    //在此添加代码

    String sql = "INSERT INTO client VALUES(?,?,?,?,?,?);";

    PreparedStatement pps = null;

    try {
      pps = connection.prepareStatement(sql);
      pps.setInt(1,c_id);
      pps.setString(2,c_name);
      pps.setString(3,c_mail);
      pps.setString(4,c_id_card);
      pps.setString(5,c_phone);
      pps.setString(6,c_password);
      if (pps.executeUpdate() > 0)
        return 1;
      else  
        return 0;
    } catch (SQLException e) {
    return -1;
  }
}

  // 不要修改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 name = commands[1];
      String mail = commands[2];
      String idCard = commands[3];
      String phone = commands[4];
      String password = commands[5];

      insertClient(connection, id, name, mail, idCard, phone, password);
    }
  }

}

第四关、注销银行卡

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

public class RemoveCard {

  static final String JDBC_DRIVER = "org.postgresql.Driver";
  static final String DB_URL = "jdbc:postgresql://127.0.0.1:5432/finance?";
  static final String USER = "gaussdb";
  static final String PASS = "Passwd123@123";

  /**
   * 删除bank_card表中数据
   *
   * @param connection 数据库连接对象
   * @param b_c_id 客户编号
   * @param c_number 银行卡号  这里的参数写错了，部分IDE会提示错
   *                          误但实际上它只用于智能提示，所以
   *                          错了没啥大事。
   */
  public static int removeBankCard(Connection connection,
    int b_c_id, String b_number)
{
    //在此添加代码
    String sql = "DELETE FROM bank_card WHERE b_c_id = ? AND b_number = ?;";
    PreparedStatement ps = null;
    try {
      ps = connection.prepareStatement(sql);
      ps.setInt(1,b_c_id);
      ps.setString(2,b_number);
      if (ps.executeUpdate() > 0) 
        return 1;
      else  
        return 0;
    } catch (SQLException e) {
      return -1;
    }
  }

  // 不要修改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("销户失败，请检查客户编号或银行卡号！");
      }
    }
  }

}

第五关、修改密码

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

public class ChangePass {
    static final String JDBC_DRIVER = "org.postgresql.Driver";
    static final String DB_URL = "jdbc:postgresql://127.0.0.1:5432/finance?";
    static final String USER = "gaussdb";
    static final String PASS = "Passwd123@123";
    
    /**
     * 修改客户密码
     *
     * @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){
    //在此添加代码
    
    String userCountSQL = "SELECT c_mail AS\"COUNT\" FROM client WHERE c_mail = ? GROUP BY c_mail;";
    String userChangeSQL = "UPDATE client SET c_password = ? WHERE c_mail = ? AND c_password = ?;";
    PreparedStatement ps = null;
    try {
        ps = connection.prepareStatement(userChangeSQL);
        ps.setString(1,newPass);
        ps.setString(2,mail);
        ps.setString(3,password);
        if (ps.executeUpdate() > 0){
            return 1;
        } else {
            ps = connection.prepareStatement(userCountSQL);
            ps.setString(1,mail);
            ResultSet rs = ps.executeQuery();
            if (!rs.next())
                return 2;

            return 3;
        }

    } catch (SQLException e) {
        System.out.println(e);
        return -1;
    }

}

    // 不要修改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("两次输入的密码不一样!");
            }
        }
    }

}

第六关、事务与转账


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

public class Transfer {
    
    static final String JDBC_DRIVER = "org.postgresql.Driver";
    static final String DB_URL = "jdbc:postgresql://127.0.0.1:5432/finance?";
    static final String USER = "gaussdb";
    static final String PASS = "Passwd123@123";
  
 /**
     * 转账操作
     *
     * @param connection 数据库连接对象
     * @param sourceCard 转出账号
     * @param destCard 转入账号
     * @param amount  转账金额
     * @return boolean
     *   true  - 转账成功
     *   false - 转账失败
**/
    public static boolean transferBalance(Connection connection,
                             String sourceCard,
                             String destCard, 
                             double amount){
        //在此添加代码
        /*转账规则
            1、转出账号只能为储蓄卡，并且余额要大于等于转出金额；
            2、接收账号可以是储蓄卡，也可以是信用卡
            3、若向储蓄卡转账，则储蓄卡余额要加上转账金额
            4、若向信用卡转账，信用卡余额要减去转账金额（相当于提前还款）

        */

        String searchAccountSQL = "SELECT * FROM bank_card WHERE b_number = ?;";
        String changeAccountBalanceSQL = "UPDATE bank_card SET b_balance = ? WHERE b_number = ?;";

        try {
            //connection.setTransactionIsolation(4);
            connection.setAutoCommit(false);

            PreparedStatement searchAccountPS = connection.prepareStatement(searchAccountSQL);
            PreparedStatement changeAccountPS = connection.prepareStatement(changeAccountBalanceSQL);

            searchAccountPS.setString(1,sourceCard);
            ResultSet rs = searchAccountPS.executeQuery();

            if (!rs.next()) 
                return false;
            else if (rs.getString("b_type").equals("信用卡           "))  //这里是七个空格
                return false;
            else if (rs.getDouble("b_balance") < amount) 
                return false;

            changeAccountPS.setDouble(1,rs.getDouble("b_balance") - amount);
            changeAccountPS.setString(2,sourceCard);
            changeAccountPS.executeUpdate();


            searchAccountPS.setString(1,destCard);
            rs = searchAccountPS.executeQuery();

            if (!rs.next()){
                connection.rollback();
                return false;
            }
            
            if (rs.getString("b_type").equals("信用卡           ")) {  //这里是七个空格
                changeAccountPS.setDouble(1,rs.getDouble("b_balance") - amount);
                changeAccountPS.setString(2,destCard);
            } else {
                changeAccountPS.setDouble(1,rs.getDouble("b_balance") + amount);
                changeAccountPS.setString(2,destCard);
            }
            changeAccountPS.executeUpdate();


            connection.commit();
            return true;

        } catch (SQLException e) {
            return false;
        }

    }


    // 不要修改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("转账失败,请核对卡号，卡类型及卡余额!");
            }
        }
    }

}

第七关、稀疏表转换为健值对(key,value)存储

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Transform {
    static final String JDBC_DRIVER = "org.postgresql.Driver";
    static final String DB_URL = "jdbc:postgresql://127.0.0.1:5432/sparsedb?";
    static final String USER = "gaussdb";
    static final String PASS = "Passwd123@123";
  
    public static int add_sc(Connection connection, int sno, String col_name, String col_value) {
        if (col_value == null) {
            return 0;
        }
        
        String sql = "INSERT INTO sc(sno, col_name, col_value) VALUES (?, ?, ?)";
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setInt(1, sno);
            pstmt.setString(2, col_name);
            pstmt.setString(3, col_value);
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        
        try {
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            
            // 获取entrance_exam表的所有列名(排除sno)
            List<String> columns = new ArrayList<>();
            DatabaseMetaData metaData = conn.getMetaData();
            ResultSet rsColumns = metaData.getColumns(null, null, "entrance_exam", null);
            
            while (rsColumns.next()) {
                String columnName = rsColumns.getString("COLUMN_NAME");
                if (!"sno".equals(columnName)) {
                    columns.add(columnName);
                }
            }
            rsColumns.close();
            
            // 查询entrance_exam表数据
            stmt = conn.createStatement();
            String sql = "SELECT * FROM entrance_exam";
            ResultSet rs = stmt.executeQuery(sql);
            
            while (rs.next()) {
                int sno = rs.getInt("sno");
                
                for (String column : columns) {
                    String value = rs.getString(column);
                    if (!rs.wasNull()) {
                        add_sc(conn, sno, column, value);
                    }
                }
            }
            
            rs.close();
        } catch (SQLException se) {
            se.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }
}