(1.實現(xiàn)學(xué)生信息和課程信息的添加。
要求:
1. 創(chuàng)建數(shù)據(jù)庫名字為scores。
2. 創(chuàng)建學(xué)生表(student)广匙,包括學(xué)號平斩,姓名,性別瞭稼,出生日期忽洛。學(xué)號作為自增主鍵,姓名不可為空环肘。
3. 創(chuàng)建課程表(course)欲虚,包括課程編號,課程名稱悔雹,學(xué)分复哆。課程編號作為自增主鍵,課程名稱和學(xué)分均不可為空腌零。???程序首先要求輸入多少個學(xué)生信息梯找,然后依次輸入學(xué)生信息。
5. 編寫程序?qū)崿F(xiàn)課程信息的添加益涧。程序詢問需要輸入多少門課程锈锤,然后依次輸入課程信息。
提示:
1. 課程的學(xué)分可以為0闲询。
2. 要規(guī)定出生日期的格式久免。
3. 數(shù)據(jù)要正確插入數(shù)據(jù)庫中。
MySQLDemo.java
package shiyan8_1;
import java.sql.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Locale;
import java.util.Scanner;
public class MySQLDemo {
??? //JDBC 驅(qū)動名及數(shù)據(jù)庫 URL
??? static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";?
??? static final String DB_URL = "jdbc:mysql://localhost:3306/scores?useSSL=false&serverTimezone=UTC";
??? //數(shù)據(jù)庫的用戶名與密碼扭弧,需要根據(jù)自己的設(shè)置
??? static final String USER = "root";
??? static final String PASS = "123456";
??? public static void main(String[] args) {
???? Scannerinput = new Scanner(System.in);
??????? Connectionconn = null;
??????? Statementstmt = null;
??????? try{
??????????? // 注冊 JDBC 驅(qū)動
??????????? Class.forName("com.mysql.jdbc.Driver");
??????????? // 打開鏈接
??????????? conn=DriverManager.getConnection(DB_URL,USER,PASS);
??????????? System.out.println("連接數(shù)據(jù)庫 成功...");
????????? ??stmt = conn.createStatement();//獲取一個可以執(zhí)行sql語句的對象阎姥。
??????????? Stringsql;
??????????? System.out.println("輸入多少個學(xué)生信息?");
??????????? int num = input.nextInt();
??????????? System.out.println("依次輸入:學(xué)號-姓名-性別-出生日期:(如:2016110 張三 男 1999-12-12");
???????????for(int i=0;i<num;i++){
??????????? ?? int xuehao = input.nextInt();
??????????? ?? Stringxingming = input.next();
??????????? ?? Stringxingbie = input.next();
??????????? ?? Stringriqi = input.next();
??????????? ?? Timestampt=string2Time(riqi);
? ???????????? //System.out.println(xuehao+""+xingming+" "+xingbie+" "+riqi);
??????????? ?? sql="insert
into student values('"+xuehao +"','"+xingming +"','"+xingbie+"','"+t+"')";//'"+xuehao+"'雙引號里面套變量
??????????? ?? //System.out.println(sql);
??????????? ??stmt.executeUpdate(sql);?
????????????? System.out.println("第"+(i+1)+"條插入成功!");
??????????? }?????
??????????? sql = "SELECT * from student";
??????????? ResultSetrs = stmt.executeQuery(sql);
??????????? // 展開結(jié)果集數(shù)據(jù)庫
??????????? while(rs.next()){
???? ???????????// 通過字段檢索
??????????????? int id? =rs.getInt("學(xué)號");
??????????????? Stringxm = rs.getString("姓名");
??????????????? Stringxb = rs.getString("性別");
??????????????? // 輸出數(shù)據(jù)
??????????????? System.out.print("學(xué)號: " + id);
??????????????? System.out.print("? 姓名: " + xm);
??????????????? System.out.print("? 性別: " + xb);
??????????????? System.out.println("");
??????????? }
??????????? System.out.println("輸入多少個課程信息鸽捻?");
??????????? num = input.nextInt();
??????????? System.out.println("依次輸入:課程編號-課程名稱-課程學(xué)分:(如:001 Java5");
??????????? for(int i=0;i<num;i++){
??????????? ?? int id = input.nextInt();
??????????? ?? Stringmingcheng = input.next();
??????????? ?? int xuefen = input.nextInt();
??????????? ?? sql="insert into course values('"+id +"','"+mingcheng +"','"+xuefen+"')";
????????????? stmt.executeUpdate(sql);?
????????????? System.out.println("第"+(i+1)+"條插入成功!");
??????????? }
??????????? sql = "SELECT * from course";
??????????? rs = stmt.executeQuery(sql);
??????????? while(rs.next()){
????????????? // 通過字段檢索
????????????? int id? =rs.getInt("課程編號");
????????????? Stringmc = rs.getString("課程名稱");
????????????? int xf = rs.getInt("學(xué)分");
????????????? // 輸出數(shù)據(jù)
????????????? System.out.print("課程編號: " + id);
????????????? System.out.print("? 課程名稱: " + mc);
?????????????System.out.print("? 課程學(xué)分: " + xf);
????????????? System.out.println("");
????????? }
??????????? // 完成后關(guān)閉
??????????? rs.close();
??????????? stmt.close();
??????????? conn.close();
??????????? input.close();
??????? }catch(SQLExceptionse){
???????????// 處理 JDBC 錯誤
??????????? se.printStackTrace();
??????? }catch(Exceptione){
??????????? // 處理 Class.forName 錯誤
??????????? e.printStackTrace();
??????? }finally{
??????????? // 關(guān)閉資源
??????????? try{
??????????????? if(stmt!=null) stmt.close();
??????????? }catch(SQLExceptionse2){
??????????? }// 什么都不做
??????????? try{
??????????????? if(conn!=null) conn.close();
??????????? }catch(SQLExceptionse){
??????????????? se.printStackTrace();
??????????? }
??????? }
??????? System.out.println("Goodbye!");
??? }
??? public static Timestamp string2Time(String dateString) throws ParseException { //字符串轉(zhuǎn)換成固定格式
????? DateFormatdateFormat;
????? dateFormat = newSimpleDateFormat("yyyy-MM-dd", Locale.ENGLISH); //固定格式
????? dateFormat.setLenient(false);
??? ??java.util.DatetimeDate = dateFormat.parse(dateString);//util類型
????? TimestampdateTime = new Timestamp(timeDate.getTime());//Timestamp類型,timeDate.getTime()返回一個long型
????? return dateTime;
? }
}
(2.實現(xiàn)成績登錄功能呼巴。
要求:
1. 輸入學(xué)生學(xué)號氨淌,如果學(xué)生存在,則開始登錄學(xué)生成績伊磺。否則重新輸入學(xué)生學(xué)號盛正。
2. 開始錄入成績時,列出所有可選課程屑埋,并讓用戶選擇錄入哪門課程豪筝。
3. 用戶選擇課程后,如果成績已經(jīng)存在摘能,則提示用戶已經(jīng)存在的成績续崖,并提示是否輸入新成績(y/n)。如果用戶選擇n則退出团搞。
4. 如果用戶選擇y严望,則重新錄入成績,并更新原來的成績逻恐。
4. 如果用戶選擇的課程成績不存在像吻,則提示用戶錄入成績。成績必須在0~100分之間复隆。
提示:
1. 數(shù)據(jù)庫需要第三個表來管理學(xué)生拨匆、課程和成績的關(guān)系。
2. 新錄入的成績和原來的成績必須正確插入數(shù)據(jù)庫挽拂。
SignOn.java
package shiyan8_2;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class SignOn {
?????? ?// JDBC 驅(qū)動名及數(shù)據(jù)庫 URL
? static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";?
? static final String DB_URL = "jdbc:mysql://localhost:3306/scores?useSSL=false&serverTimezone=UTC";
? //數(shù)據(jù)庫的用戶名與密碼惭每,需要根據(jù)自己的設(shè)置
? static final String USER = "root";
? static final String PASS = "123456";
?????? public static void main(String[] args) {
????????????? Scannerinput = new Scanner(System.in);
??? Connectionconn = null;
??? Statementstmt = null;
??? try{
????? //注冊 JDBC 驅(qū)動
????? Class.forName("com.mysql.jdbc.Driver");
????? //打開鏈接
????? conn= DriverManager.getConnection(DB_URL,USER,PASS);
????? System.out.println("連接數(shù)據(jù)庫成功...");
????? stmt = conn.createStatement();//獲取一個可以執(zhí)行sql語句的對象。
????? Stringsql;
????? int num=0;
????? System.out.println("輸入登陸的學(xué)生學(xué)號亏栈。");
????? ???? while(true){
????? ??????????? num = input.nextInt();
????? ??????????? sql = "select
* from student where 學(xué)號 = '"+num+"'";
????????? ResultSetre = stmt.executeQuery(sql);
????? ??????????? if(re.next()){
????? ?????????????????? break;
????? ??????????? }else{
????? ?????????????????? System.out.println("沒有該學(xué)生台腥,請重新輸入學(xué)生學(xué)號");
????? ??????????? }
????? ???? }
????? ???? sql = "select
id,課程名稱 from chengji where 學(xué)號 = '"+num+"'";
????? ???? ResultSetre = stmt.executeQuery(sql);
????? ???? while(re.next()){
????? ??????????? System.out.println("ID:="+re.getInt("id")+" "+re.getString("課程名稱"));
????? ???? }
????? ???? System.out.println("選擇錄入id為?的課程成績~");
????? ???? num=input.nextInt();
?? ??????? sql = "select成績 from chengji where id =
'"+num+"'";
????? ???? re = stmt.executeQuery(sql);
????? ???? if(re.next()){
????? ??????????? int c = re.getInt("成績");//這里值為0和為空無法判斷绒北,要解決就用Integer
????? ??????????? if(c==0){
????? ?????????????????? System.out.println("該課程成績不存在黎侈,請錄入成績:");
????? ?????????????????? c = input.nextInt();
????? ?????????????????? sql = "UPDATE
chengji SET 成績= '"+c+"'
WHERE id='"+num+"'";
????? ?????????????????? stmt.executeUpdate(sql);
????? ?????????????????? System.out.println("錄入成功!");
????? ??????????? }else{
????? ?????????????????? System.out.println("該課程成績已經(jīng)存在為:="+c+"是否更改成績~y/n:");
????? ?????????????????? char e = 'a';
? ??????????Stringstr1 = input.next();
??????????? e =str1.charAt(0);
??????????? if(e=='Y'||e=='y'){
??????????? ?? System.out.println("請輸入更正的成績:");
??????????? ?? c = input.nextInt();
??????????? ?? sql = "UPDATE
chengji SET 成績= '"+c+"'
WHERE id='"+num+"'";
?? ???????????????????? stmt.executeUpdate(sql);
??????? ??????????????? System.out.println("更正成功!");
??????????? }else{
??????????? ?? System.out.println("退出成功.");
??????????? }
????? ??????????? }
????? ??????????? System.out.println(c);
????? ???? }else{
????? ??????????? System.out.println("沒有找到該課程,Id有誤!!!");
????? ???? }
????? stmt.close();
????? conn.close();
????? input.close();
? }catch(SQLException se){
????? //處理 JDBC 錯誤
????? se.printStackTrace();
? }catch(Exception e){
????? //處理 Class.forName 錯誤
????? e.printStackTrace();
? }finally{
????? //關(guān)閉資源
????? try{
?????????if(stmt!=null) stmt.close();
????? }catch(SQLExceptionse2){
????? }// 什么都不做
????? try{
????????? if(conn!=null) conn.close();
????? }catch(SQLExceptionse){
????????? se.printStackTrace();
????? }
? }
? System.out.println("Goodbye!");
}
}
(3.實現(xiàn)計算加權(quán)平均分功能镇饮。
要求:輸入學(xué)生的學(xué)號蜓竹,列出學(xué)生所修課程的成績和加權(quán)平均分〈⒚辏或者輸入all俱济,列出所有學(xué)生的加權(quán)平均分。
提示:
1. 學(xué)分為0的課程不計入加權(quán)平均分钙勃。
2. 輸入的學(xué)號可能不存在蛛碌。
3. 若學(xué)生沒有選修任何課程,則輸出相應(yīng)提示辖源。
PrintStudent.java
package shiyan8_2;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class PrintStudent {
????????????? static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";?
?????? ? static final String DB_URL = "jdbc:mysql://localhost:3306/scores?useSSL=false&serverTimezone=UTC";
?????? ? // 數(shù)據(jù)庫的用戶名與密碼蔚携,需要根據(jù)自己的設(shè)置
?????? ? static final String USER = "root";
?????? ? static final String PASS = "123456";
????????????? public static void main(String[] args) {
???????????????????? Scannerinput = new Scanner(System.in);
?????? ???Connectionconn = null;
?????? ???Statementstmt = null;
?????? ???Statementstmt1 = null;
?????? ???Statementstmt2 = null;
?????? ???try{
?????? ?????// 注冊 JDBC 驅(qū)動
?????? ?????Class.forName("com.mysql.jdbc.Driver");
?????? ?????// 打開鏈接
?????? ?????conn= DriverManager.getConnection(DB_URL,USER,PASS);
?????? ?????System.out.println("連接數(shù)據(jù)庫成功...");
?????? ?????stmt = conn.createStatement();//獲取一個可以執(zhí)行sql語句的對象希太。
?????? ?????stmt1 = conn.createStatement();//一個Statement對象最好對應(yīng)一個ResultSet對象,否則可能會出現(xiàn)上述錯誤
?????? ?????stmt2 = conn.createStatement();
?????? ?????Stringsql;
?????? ?????Stringstr1="";
?????? ?????System.out.println("輸入打印的學(xué)生學(xué)號或者all酝蜒。");
?????? ????????? while(true){
?????? ???????????????? str1 = input.next();
?????? ???????????????? if(str1.equals("all")){
?????? ??????????????????????? System.out.println("打印所有學(xué)生的成績和平均分");
?????? ??????????????????????? sql = "select
* from student";
?????? ??????????????????????? ResultSetr1 = stmt.executeQuery(sql);
?????? ???????????????? ?????? while(r1.next()){
?????? ???????????????? ????????????? int xuehao = r1.getInt("學(xué)號");
?????? ???????????????? ????????????? System.out.print(xuehao);
?????? ???????????????? ????????????? Stringxingming = r1.getString("姓名");
?????? ???????????????? ????????????? System.out.println("? "+xingming);????????????
?????? ???????????????? ????????????? sql = "select課程名稱,成績 from chengji where 學(xué)號 = '"+xuehao+"'";
?????? ???????????????? ????????????? ResultSetr3 = stmt2.executeQuery(sql);
?????? ???????????????? ????????????? if(!r3.next()){
?????? ???????????????? ???????????????????? System.out.println("該學(xué)生未選修任何課程~~");
?????? ???????????????? ???????????????????? continue;
?????? ???????????????? ????????????? }
?????? ???????????????? ????????????? double sum=0;
?????? ???????????????? ????????????? int ant = 0;
?????? ???????????????? ????????????? while(r3.next()){
?????? ???????????????? ???????????????????? Stringke = r3.getString("課程名稱");
?????? ???????????????? ???????????????????? int cj = r3.getInt("成績");
?????? ???????????????? ???????????????????? System.out.println("->"+r3.getString("課程名稱")+" "+r3.getInt("成績"));
?????? ???????????????? ???????????????????? sql = "select學(xué)分 from course where 課程名稱 = '"+ke+"'";
?????? ???????????????? ???????????????????? ResultSetr2 = stmt1.executeQuery(sql);
?????? ???????????????? ???????????????????? int xuefen = 0;
?????? ???????????????? ???????????????????? while(r2.next()){//使用rs.getstring();前一定要加上rs.next();
?????? ???????????????? ??????????????????????????? xuefen = r2.getInt("學(xué)分");
?????? ???????????????? ???????????????????? }
?????? ???????????????? ???????????????????? r2.close();
?????? ???????????????? ???????????????????? sum+=(xuefen*cj*1.0);
?????? ???????????????? ???????????????????? if(xuefen!=0)
?????? ???????????????? ???????????????????? ant+=xuefen;
?????? ???????????????? ????????????? }
?????? ???????????????? ????????????? if(sum==0){
?????? ???????????????? ???????????????????? System.out.println("加權(quán)平均分為:0");
?????? ???????????????? ????????????? }else{
?????? ???????????????? ???????????????????? System.out.println("加權(quán)平均分為:"+(double)Math.round(sum/ant*100.0)/100);
?????? ???????????????? ????????????? }
?????? ???????????????? ?????? r3.close();
?????? ?????????????? }
?????? ???????????????? ?????? r1.close();
?????? ??????????????????????? break;
?????? ???????????????? }
?????? ???????????????? sql = "select
* from student where 學(xué)號 = '"+str1+"'";
?????? ?????????ResultSetre = stmt.executeQuery(sql);
?????? ???????????????? if(re.next()){
?????? ??????????????????????? int xuehao = re.getInt("學(xué)號");
?? ?????????????? ????????????? System.out.print(xuehao);
????? ??????????? ????????????? Stringxingming = re.getString("姓名");
????? ??????????? ????????????? System.out.println("? "+xingming);????????????
????? ??????????? ????????????? sql = "select課程名稱,成績 from chengji where 學(xué)號 = '"+xuehao+"'";
????? ??????????? ????????????? ResultSetr3 = stmt2.executeQuery(sql);
? ??????????????? ????????????? if(!r3.next()){
????? ??????????? ???????????????????? System.out.println("該學(xué)生未選修任何課程~~");
????? ??????????? ???????????????????? continue;
????? ??????????? ????????????? }
????? ??????????? ????????????? double sum=0;
????? ??????????? ????????????? int ant = 0;
????? ??????????? ????????????? while(r3.next()){
????? ??????????? ???????????????????? Stringke = r3.getString("課程名稱");
????? ??????????? ???????????????????? int cj = r3.getInt("成績");
????? ??????????? ???????????????????? System.out.println("->"+r3.getString("課程名稱")+" "+r3.getInt("成績"));
????? ??????????? ???????????????????? sql = "select學(xué)分 from course where 課程名稱 = '"+ke+"'";
????? ??????????? ???????????????????? ResultSetr2 = stmt1.executeQuery(sql);
????? ??????????? ???????????????????? int xuefen = 0;
????? ??????????? ???????????????????? while(r2.next()){//使用rs.getstring();前一定要加上rs.next();
????? ??????????? ??????????????????????????? xuefen = r2.getInt("學(xué)分");
????? ??????????? ???????????????????? }
????? ??????????? ???????????????????? r2.close();
????? ??????????? ???????????????????? sum+=(xuefen*cj*1.0);
????? ??????????? ???????????????????? if(xuefen!=0)
????? ??????????? ???????????????????? ant+=xuefen;
????? ??????????? ????????????? }
????? ??????????? ????????????? if(sum==0){
????? ??????????? ????????????? ?????? System.out.println("加權(quán)平均分為:0");
????? ??????????? ????????????? }else{
????? ??????????? ???????????????????? System.out.println("加權(quán)平均分為:"+(double)Math.round(sum/ant*100.0)/100);
????? ??????????? ????????????? }
????? ??????????? ?????? r3.close();
?????? ??????????????????????? break;
?????? ???????????????? }else{
?????? ??????????????????????? System.out.println("沒有該學(xué)生誊辉,請重新輸入學(xué)生學(xué)號");
?????? ???????????????? }
?????? ????????? }
?????? ?????stmt.close();
?????? ?????conn.close();
?????? ?????input.close();
?????? ? }catch(SQLException se){
?????? ?????// 處理 JDBC 錯誤
?????? ?????se.printStackTrace();
?????? ? }catch(Exception e){
?????? ?????// 處理 Class.forName 錯誤
?????? ?????e.printStackTrace();
?????? ? }finally{
?????? ?????// 關(guān)閉資源
?????? ?????try{
?????? ?????????if(stmt!=null) stmt.close();
?????? ?????}catch(SQLException se2){
?????? ?????}// 什么都不做
?????? ?????try{
?????? ?????????if(conn!=null) conn.close();
?????? ?????}catch(SQLException se){
?????? ?????????se.printStackTrace();
?????? ?????}
?????? ? }
?????? ?System.out.println("Goodbye!");
?????? }
?????? }