ACCPS2-03使用java實現資料庫程式設計考試試題參照

MySQL資料庫SQL語句

CREATE DATABASE `Apply`;

USE `Apply`;

DROP TABLE IF EXISTS `Apply_Info`;
CREATE TABLE `Apply_Info`(
   `applyId` INT AUTO_INCREMENT PRIMARY KEY COMMENT '報名編號',
   `name` CHAR(10) NOT NULL COMMENT '名字',
   `age` INT(4) NOT NULL COMMENT '年齡',
   `class` VARCHAR(50) NOT NULL COMMENT '年級',
   `game` VARCHAR(50) NOT NULL COMMENT '報名專案'
);
#插入測試語句
INSERT INTO `apply_Info`(`name`,`age`,`class`,`game`)VALUES('小明','17','一班','跳遠');
INSERT INTO `apply_Info`(`name`,`age`,`class`,`game`)VALUES('小紅','18','二班','接力跑');
INSERT INTO `apply_Info`(`name`,`age`,`class`,`game`)VALUES('小華','19','三班','跳繩');
#插入資料
INSERT INTO `apply_Info`(`name`,`age`,`class`,`game`)VALUES(?,?,?,?);
#按科目查詢
SELECT `applyId`,`game`,`class`,`name`,`age`FROM`apply_info` WHERE `game` = ?;
#按年級查詢
SELECT `applyId`,`game`,`class`,`name`,`age`FROM`apply_info` WHERE `class` = ?;
#刪除語句
DELETE FROM `apply_info` WHERE `name` = ?;

SELECT * FROM `apply_Info`;

JAVA實現類和表示層

實現類

ApplyInfoDaoImpl 實現類

package com.xinshi.dao.impl;

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

import com.xinshi.dao.ApplyInfoDao;
import com.xinshi.entity.ApplyInfo;
import com.xinshi.utils.BaseDao;

public class ApplyInfoDaoImpl extends BaseDao implements ApplyInfoDao{

   @Override
   public int insertStudent(ApplyInfo apply) {
       String sql = "INSERT INTO `apply_info`(`applyId`,`name`,`age`, `class`, `game`)VALUES(DEFAULT,?,?,?,?)";
       List<Object> obj = new ArrayList<>();
       obj.add(apply.getName());
       obj.add(apply.getAge());
       obj.add(apply.getClasses());
       obj.add(apply.getGame());
       int result = executeUpdate(sql, obj);;
       return result;
   }

   @Override
   public List<ApplyInfo> classQuery(String classes) {
       String sql = "SELECT `applyId`,`game`,`class`,`name`,`age` FROM `apply_info` WHERE `class` = ?";
       List<Object> obj = new ArrayList<>();
       obj.add(classes);
       executeQuery(sql, obj);
       List<ApplyInfo> list = new ArrayList<>();
       try {
           while(rs.next()) {
               ApplyInfo apply = new ApplyInfo();
               apply.setGame(rs.getString("game"));
               apply.setClasses(rs.getString("class"));
               apply.setAge(rs.getInt("age"));
               apply.setName(rs.getString("name"));
               apply.setApplyId(rs.getInt("applyId"));
               list.add(apply);
           }
       } catch (SQLException e) {
           e.printStackTrace();
       }
       return list;
   }

   @Override
   public List<ApplyInfo> gameQuery(String game) {
       String sql = "SELECT `applyId`,`game`,`class`,`name`,`age` FROM `apply_info` WHERE `game` = ? ";
       List<Object> obj = new ArrayList<>();
       obj.add(game);
       executeQuery(sql, obj);
       List<ApplyInfo> list = new ArrayList<>();
       try {
           while(rs.next()) {
               ApplyInfo apply = new ApplyInfo();
               apply.setGame(rs.getString("game"));
               apply.setClasses(rs.getString("class"));
               apply.setAge(rs.getInt("age"));
               apply.setName(rs.getString("name"));
               apply.setApplyId(rs.getInt("applyId"));
               list.add(apply);
           }
       } catch (SQLException e) {
           e.printStackTrace();
       }
       return list;
   }

   @Override
   public int delApply(String name) {
       String sql = "DELETE FROM `apply_info` WHERE `name` = ? ";
       List<Object> obj = new ArrayList<>();
       obj.add(name);
       int result = executeUpdate(sql, obj);
       return result;
   }
}

表示層程式碼

package com.xinshi.ui;

import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import com.xinshi.entity.ApplyInfo;
import com.xinshi.service.ApplyInfoService;
import com.xinshi.service.impl.ApplyInfoServiceImpl;

public class GameMgr {
ApplyInfoService app = new ApplyInfoServiceImpl();
Scanner input = new Scanner(System.in);

   public void mgr() {
       System.out.println("******歡迎使用運動會報名系統******");
       do {
           System.out.println("1.學生報名2.按比賽專案查詢3.按班級查詢4.取消報名5.退出系統");
           System.out.print("請選擇(1~5):");
           int choice = input.nextInt();
           switch (choice) {
           case 1:
               insertStudent();
               continue;
           case 2:
               gameQuery();
               continue;
           case 3:
               classQuery();
               continue;
           case 4:
               delApply();
               continue;
           case 5:
               System.out.println("謝謝使用!");
               System.exit(0);
               break;
           default:
               System.out.println("輸入錯誤,請重新輸入!");
               continue;
           }
           break;
       } while (true);
   }

   /**
   * 學生報名
   */
   public void insertStudent() {
       ApplyInfo apply = new ApplyInfo();
       System.out.print("請輸入姓名:");
       apply.setName(input.next());
       System.out.print("請輸入年齡:");
       apply.setAge(input.nextInt());
       System.out.print("請選擇班級:(1.一班 2.二班 3.三班)");
       int classes = input.nextInt();
       if (classes == 1) {
           apply.setClasses("一班");
       } else if (classes == 2) {
           apply.setClasses("二班");
       } else if (classes == 3) {
           apply.setClasses("三班");
       }
       System.out.print("請選擇要比賽專案(1.跳遠 2.接力跑 3.跳繩)");
       int game = input.nextInt();
       if (game == 1) {
           apply.setGame("跳遠");
       } else if (game == 2) {
           apply.setGame("接力跑");
       } else if (game == 3) {
           apply.setGame("跳繩");
       }
       int result = app.insertStudent(apply);
       if (result > 0) {
           System.out.println("錄入成功!");
       } else {
           System.out.println("錄入失敗!");
       }
   }

   /**
   * 按比賽專案查詢
   */
   public void gameQuery() {
       System.out.print("請選擇要比賽專案(1.跳遠 2.接力跑 3.跳繩)");
       int game = input.nextInt();
       ApplyInfo apply = new ApplyInfo();
       if (game == 1) {
           apply.setGame("跳遠");
       } else if (game == 2) {
           apply.setGame("接力跑");
       } else if (game == 3) {
           apply.setGame("跳繩");
       }
       List<ApplyInfo> list = app.gameQuery(apply.getGame());
       if (list.size()==0) {
           System.out.println("沒有此專案的報名資訊!");
       } else {
           System.out.println("專案\t班級\t姓名\t年齡");
           for (int i = 0; i < list.size(); i++) {
               System.out.println(list.get(i).getGame() + "\t" + list.get(i).getClasses() + "\t"
                       + list.get(i).getName() + "\t" + list.get(i).getAge());
           }
       }
   }

   /**
   * 按班級查詢
   */
   public void classQuery() {
       System.out.print("請選擇要查詢的班級(1.一班2.二班3.三班)");
       int classes = input.nextInt();
       ApplyInfo apply = new ApplyInfo();
       if (classes == 1) {
           apply.setClasses("一班");
       } else if (classes == 2) {
           apply.setClasses("二班");
       } else if (classes == 3) {
           apply.setClasses("三班");
       }
       List<ApplyInfo> list = app.classQuery(apply.getClasses());
       if (list.size()==0) {
           System.out.println("沒有此班級的報名資訊!");
       } else {
           System.out.println("專案\t班級\t姓名\t年齡");
           for (int i = 0; i < list.size(); i++) {
               System.out.println(list.get(i).getGame() + "\t" + list.get(i).getClasses() + "\t"
                       + list.get(i).getName() + "\t" + list.get(i).getAge());
           }
       }
   }

   /**
   * 取消報名方法
   */
   public void delApply() {
       System.out.print("請輸入要取消報名的學生名字:");
       String name = input.next();
       int result = app.delApply(name);
       if (result > 0) {
           System.out.println("取消報名成功!");
       } else {
           System.out.println("沒有找到該報名資料!");
       }
   }

}

ACCPS2-03使用java實現資料庫程式設計考試試題參照

相關文章