1、 实验目的
(1) 掌握基本的DBMS使用
(2) 理解JDBC的概念
(3) 运用JDBC-ODBC桥开发数据库
2、 基本要求
(1) 注意自始至终贯彻课程中所介绍程序设计风格,养成良好的编程习惯
(2) 独立完成所布置习题
(3) 为保证尽量在统一安排的上机时间内编译运行通过程序,应事先设计好程序
(4) 认真完成每次试验,并写出试验报告
3、 实验内容和步骤
完成一个具体的数据库系统的开发,如图书管理系统、人事管理系统、学生信息管理系统等等。
源代码
package studentManagement;
public class Student {
String number;
String classNumber;
String name;
String sex;
int age;
String hometown;
}
package studentManagement;
import java.sql.*;
import java.util.Scanner;
public class Main {
static Connection con = null;
static Statement sql;
public static void insert() throws SQLException {
Scanner cin = new Scanner(System.in);
Student stu1 = new Student();
System.out.println("请输入插入学生的信息:");
System.out.println("请输入学生学号:");
stu1.number = cin.next();
System.out.println("请输入学生班级:");
stu1.classNumber = cin.next();
System.out.println("请输入学生姓名:");
stu1.name = cin.next();
System.out.println("请输入学生性别:");
stu1.sex = cin.next();
System.out.println("请输入学生年龄:");
stu1.age = cin.nextInt();
System.out.println("请输入学生籍贯:");
stu1.hometown = cin.next();
write(stu1);
System.out.println("插入学生信息成功!");
}
public static void connectSql(){
String uri = "jdbc:mysql://127.0.0.1:3306/students?user=root&password=123456&characterEncoding=GB2312&useSSL=false&serverTimezone=UTC";
try {
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection(uri);
if(con == null)
System.out.println("连接数据库失败");
else if(con.isClosed())
System.out.println("连接数据库失败");
else
System.out.println("数据库连接成功");
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
public static void write(Student st){
String str = "('"+st.number+"','"+st.classNumber+"','"+st.name+"','"+st.sex+"',"+st.age+",'"+st.hometown+"')";
String sqlstr = "insert into student values" + str;
try {
sql = con.createStatement();
sql.executeUpdate(sqlstr);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
public static Student find(String number1){
Student stu = new Student();
ResultSet rs;
try {
sql = con.createStatement();
rs = sql.executeQuery("SELECT * FROM student");
while(rs.next()){
String number = rs.getString(1);
String classNumber = rs.getString(2);
String name = rs.getString(3);
String sex = rs.getString(4);
int age = rs.getInt(5);
String homeTown = rs.getString(6);
if(number.equals(number1)){
stu.number = number;
stu.classNumber = classNumber;
stu.name = name;
stu.sex = sex;
stu.age = age;
stu.hometown = homeTown;
break;
}
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return stu;
}
public static void findStudent(){
Scanner cin = new Scanner(System.in);
System.out.println("请输入查询学生的学号:");
String number = cin.next();
Student stu = find(number);
if(stu.number == null){
System.out.println("查无此人!");
}
else{
System.out.println("学生信息如下;");
System.out.println("学号:" + stu.number);
System.out.println("班级:" + stu.classNumber);
System.out.println("姓名:" + stu.name);
System.out.println("性别:" + stu.sex);
System.out.println("年龄:" + stu.age);
System.out.println("籍贯:" + stu.hometown);
}
}
public static void delete() throws SQLException {
Scanner cin = new Scanner(System.in);
System.out.println("请输入删除学生的学号:");
String number = cin.next();
Student stu = find(number);
if(stu.number == null)
System.out.println("没有该学生的记录!");
else {
String sqlstr = "delete from student "+" where 学号 = '"+ stu.number +"'; ";
sql.executeUpdate(sqlstr);
System.out.println("删除成功!");
}
}
public static void main(String[] args) throws SQLException {
Scanner cin = new Scanner(System.in);
connectSql();
while(true){
System.out.println("1、查询学生信息");
System.out.println("2、增加学生信息");
System.out.println("3、删除学生信息");
System.out.println("4、退出");
System.out.print("请输入进行的操作:");
int x = cin.nextInt();
if(x == 1)
findStudent();
if(x == 2)
insert();
if(x == 3)
delete();
if(x == 4){
con.close();
sql.close();
System.exit(0);
}
}
}
}