62,615
社区成员
发帖
与我相关
我的任务
分享
package com.yanzhen.september;
/**
* 学生类信息
* 包含:姓名;年龄;学号;JAVA成绩,C语言成绩,OC成绩
* @围脖:皖西程序猿
* @Email:walkman_28@163.com
*/
public class Student {
private String name;
private int age;
private String number;
private double javaLanguageScore;
private double cLanguageScore;
private double ocLanguageScore;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public double getJavaLanguageScore() {
return javaLanguageScore;
}
public void setJavaLanguageScore(double javaLanguageScore) {
this.javaLanguageScore = javaLanguageScore;
}
public double getcLanguageScore() {
return cLanguageScore;
}
public void setcLanguageScore(double cLanguageScore) {
this.cLanguageScore = cLanguageScore;
}
public double getOcLanguageScore() {
return ocLanguageScore;
}
public void setOcLanguageScore(double ocLanguageScore) {
this.ocLanguageScore = ocLanguageScore;
}
}
第二步,在MYSQL中建立数据库
DROP TABLE IF EXISTS STUDENT;
CREATE TABLE student (
name varchar(20) NOT NULL,
age int(3) NOT NULL,
num varchar(20) NOT NULL,
javaScore double(4,2) NOT NULL,
cScore double(4,2) NOT NULL,
ocScore double(4,2) NOT NULL,
PRIMARY KEY (num)
);
第三步,主函数
package com.yanzhen.september;
import java.util.Scanner;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.PrintStream;
import java.sql.*;
/**
* 由控制台控制学生信息的输入,查询及保存的简单小系统。
* @围脖:皖西程序猿
* @Email:walkman_28@163.com
*/
public class StudentScoreQuery {
private final static String TIP_MESSAGE = "学生成绩查询系统(新浪微博:皖西程序猿)";
private final static String OPTION_ONE = "1 将学生信息录入数据库中";
private final static String OPTION_TWO = "2 显示所有学生信息";
private final static String OPTION_THREE = "3 根据学生学号查询该学生的总分";
private final static String OPTION_FOUR = "4 导出所有学生的相关信息";
private final static String OPTION_FIVE = "5 退出";
// 数据库操作部分
//驱动程序名
private final static String driver = "com.mysql.jdbc.Driver";
//URL指向要访问的数据库名mydata
private final static String url = "jdbc:mysql://localhost:3306/student";
//MySQL配置时的用户名
private final static String user = "root";
//MySQL配置时的密码
private final static String password = "test";
public static void main(String[] args) {
// (1)控制台上显示主菜单信息
// 1 将学生信息录入数据库中 2 显示所有学生信息 3 根据学生学号查询该学生的总分 4 导出所有学生的相关信息 5 退出
System.out.print(TIP_MESSAGE + "\t\n");
System.out.print(OPTION_ONE);
System.out.print(OPTION_TWO);
System.out.print(OPTION_THREE);
System.out.print(OPTION_FOUR);
System.out.print(OPTION_FIVE + "\n");
// 判断输入的数字,决定操作
Scanner scan = new Scanner(System.in);
int inputNumber = scan.nextInt();
if (inputNumber == 1) {
// 具体功能实现:1 将学生信息录入数据库中
Student stu = getScannerInputInformations();
saveStudentInfomationsIntoDatabase(stu);
} else if (inputNumber == 2 ) {
// 具体功能实现:2 显示所有学生信息
showAllStudentInformations();
} else if (inputNumber == 3 ) {
// 具体功能实现:3 根据学生学号查询该学生的总分
String num = scan.next();
System.out.println("总分为:" + getSumScoreByNumber(num));
} else if (inputNumber == 4 ) {
// 4 导出所有学生的相关信息
writeAllStudentInformationIntoFile();
} else if (inputNumber == 5 ) {
// 具体功能实现:5 直接退出程序
System.exit(0);
}
}
// 由控制台输入信息并生成Student类
public static Student getScannerInputInformations() {
Student stu = new Student();
// 1 接收姓名
System.out.println("请输入姓名");
Scanner scan = new Scanner(System.in);
String getScanName = scan.nextLine();
// 2 接收年龄
System.out.println("请输入年龄");
scan = new Scanner(System.in);
int getScanAge = scan.nextInt();
// 3 接收学号
System.out.println("请输入学号");
scan = new Scanner(System.in);
String getScanNumber = scan.nextLine();
// 4 接收JAVA成绩
System.out.println("请输入JAVA成绩");
scan = new Scanner(System.in);
double getScanJavaLanuageScore = scan.nextDouble();
// 5 接收C语言成绩
System.out.println("请输入C语言成绩");
scan = new Scanner(System.in);
double getScanCLanuageScore = scan.nextDouble();
// 6 接收OC成绩
System.out.println("请输入OC语言成绩");
scan = new Scanner(System.in);
double getScanOCLanuageScore = scan.nextDouble();
// 7 组合Student对象
stu.setName(getScanName);
stu.setAge(getScanAge);
stu.setNumber(getScanNumber);
stu.setJavaLanguageScore(getScanJavaLanuageScore);
stu.setcLanguageScore(getScanCLanuageScore);;
stu.setOcLanguageScore(getScanOCLanuageScore);
return stu;
}
// 具体功能实现:1 将学生信息录入数据库中
public static void saveStudentInfomationsIntoDatabase (Student stu) {
//声明Connection对象
Connection con;
try {
Class.forName(driver);
//1.getConnection()方法,连接MySQL数据库
con = DriverManager.getConnection(url,user,password);
//2.创建PreparedStatement类对象,用来执行SQL语句
con.setAutoCommit(false);
String sql = "insert into student values(?,?,?,?,?,?) ";
PreparedStatement pst = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
pst.setString(1, stu.getName());
pst.setInt(2, stu.getAge());
pst.setString(3, stu.getNumber());
pst.setDouble(4, stu.getJavaLanguageScore());
pst.setDouble(5, stu.getcLanguageScore());
pst.setDouble(6, stu.getOcLanguageScore());
pst.executeUpdate();
con.commit();
pst.close();
con.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 具体功能实现:2 显示所有学生信息
public static void showAllStudentInformations() {
//声明Connection对象
Connection con;
try {
Class.forName(driver);
//1.getConnection()方法,连接MySQL数据库
con = DriverManager.getConnection(url,user,password);
//2.创建PreparedStatement类对象,用来执行SQL语句
con.setAutoCommit(false);
String sql = "select * from student ";
PreparedStatement pst = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
ResultSet rs = pst.executeQuery(sql);
// 3.遍历查找数据库中数据
while(rs.next()) {
System.out.println(rs.getString(1) + "|" + rs.getInt(2) +"|"+rs.getString(3)
+ rs.getDouble(4) + "|" + rs.getDouble(5) + "|" + rs.getDouble(6));
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
// 具体功能实现:3 根据学生学号查询该学生的总分
public static double getSumScoreByNumber(String num) {
//声明Connection对象
Connection con;
double sumScore = 0.0;
try {
Class.forName(driver);
//1.getConnection()方法,连接MySQL数据库
con = DriverManager.getConnection(url,user,password);
//2.创建PreparedStatement类对象,用来执行SQL语句
con.setAutoCommit(false);
String sql = "select * from student where num="+ num;
PreparedStatement pst = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
ResultSet rs = pst.executeQuery(sql);
// 3.遍历查找数据库中数据
while(rs.next()) {
sumScore = rs.getDouble(4) + rs.getDouble(5) + rs.getDouble(6);
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return sumScore;
}
// 具体功能实现:4 导出所有学生的相关信息存放到studentInfo.txt中
public static void writeAllStudentInformationIntoFile() {
//声明Connection对象
Connection con;
String infomationForFile = "";
File file = new File("d:\\student.txt");
try {
Class.forName(driver);
// 1.getConnection()方法,连接MySQL数据库
con = DriverManager.getConnection(url, user, password);
// 2.创建PreparedStatement类对象,用来执行SQL语句
con.setAutoCommit(false);
String sql = "select * from student ";
PreparedStatement pst = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ResultSet rs = pst.executeQuery(sql);
// 3.遍历查找数据库中数据
while (rs.next()) {
String tempStr = (rs.getString(1) + "|" + rs.getInt(2) + "|" + rs.getString(3) + rs.getDouble(4) + "|"
+ rs.getDouble(5) + "|" + rs.getDouble(6)) + "\n";
infomationForFile = infomationForFile + tempStr;
}
PrintStream ps = new PrintStream(new FileOutputStream(file));
ps.println(infomationForFile);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
}