这是一个简单的学生信息管理,非常小非常简单,适合初学者,下载地址:http://download.csdn.net/detail/bq1073100909/7545941
数据库连接使用的jar文件:mysql-connector-java-5.1.30-bin.jar
项目目录结构
数据库文件:
create database test;
use test;
CREATE TABLE `test`.`student` (
`stuid` INT NOT NULL AUTO_INCREMENT,
`stuname` VARCHAR(45) NOT NULL,
PRIMARY KEY (`stuid`),
UNIQUE INDEX `id_UNIQUE` (`stuid` ASC));
CREATE TABLE `test`.`course` (
`courseid` INT NOT NULL AUTO_INCREMENT,
`coursename` VARCHAR(45) NOT NULL,
PRIMARY KEY (`courseid`),
UNIQUE INDEX `courseid_UNIQUE` (`courseid` ASC),
UNIQUE INDEX `coursename_UNIQUE` (`coursename` ASC));
CREATE TABLE `test`.`stucourse` (
`stuid` INT NOT NULL,
`courseid` INT NOT NULL,
`grade` FLOAT NOT NULL,
INDEX `f1_idx` (`stuid` ASC),
INDEX `f2_idx` (`courseid` ASC),
CONSTRAINT `f1`
FOREIGN KEY (`stuid`)
REFERENCES `test`.`student` (`stuid`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `f2`
FOREIGN KEY (`courseid`)
REFERENCES `test`.`course` (`courseid`)
ON DELETE CASCADE
ON UPDATE CASCADE);
INSERT INTO `test`.`student` (`stuid`, `stuname`) VALUES ('1', '张三');
INSERT INTO `test`.`student` (`stuid`, `stuname`) VALUES ('2', '李四');
INSERT INTO `test`.`course` (`courseid`, `coursename`) VALUES ('1', '语文');
INSERT INTO `test`.`course` (`courseid`, `coursename`) VALUES ('2', '数学');
INSERT INTO `test`.`stucourse`(`stuid`,`courseid`,`grade`) values(1,1,80);
INSERT INTO `test`.`stucourse`(`stuid`,`courseid`,`grade`) values(1,2,90);
INSERT INTO `test`.`stucourse`(`stuid`,`courseid`,`grade`) values(2,1,60);
INSERT INTO `test`.`stucourse`(`stuid`,`courseid`,`grade`) values(2,2,100);
select * from student;
select * from course;
select * from stucourse;
select stuname,coursename,grade from student,course,stucourse where student.stuid=stucourse.stuid and course.courseid=stucourse.courseid;
JDBCMySQL.java文件:
package dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBCMySQL {
// 驱动程序就是之前在classpath中配置的jdbc的驱动程序的jar包中
public static final String DBDRIVER = "org.gjt.mm.mysql.Driver";
// 连接地址是由各个数据库生产商单独提供的,所以需要单独记住
public static final String DBURL = "jdbc:mysql://localhost:3306/test";
// 连接数据库的用户名
public static final String DBUSER = "root";
// 连接数据库的密码
public static final String DBPASS = "root";
Connection conn = null; // 表示数据库的连接的对象
PreparedStatement pstmt = null; // 表示数据库的更新操作
ResultSet rs=null;//结果集
//数据库连接
public void connect() throws SQLException, ClassNotFoundException{
// 1、使用Class类加载驱动程序
Class.forName(DBDRIVER);
// 2、连接数据库
conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
}
//插入学生信息
public void insertdate(String name) throws Exception {
this.connect();//数据库连接
String sql = "INSERT INTO student(stuname) VALUES (?) ";
// 3、PreparedStatement接口需要通过Connection接口进行实例化操作
pstmt = conn.prepareStatement(sql) ;// 使用预处理的方式创建对象
pstmt.setString(1, name) ;// 第一个?号的内容
// 执行SQL语句,更新数据库
if(pstmt.executeUpdate()>0){
System.out.println("插入数据成功!学生姓名"+name);
}
this.closeDB();
}
//删除学生信息
public void deleteStuInfo(String name) throws ClassNotFoundException, SQLException{
this.connect();
String sql = "DELETE FROM `test`.`student` WHERE `stuname`=?; ";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, name);
if(pstmt.executeUpdate()>0){
System.out.println("删除"+name+"学生数据成功!");
}
this.closeDB();
}
//查询所有学生成绩信息
public void findStuInfo() throws Exception{
int i=0;
int j=0;
float yuwen_total=0;
float shuxue_total=0;
this.connect();
String sql ="select stuname,coursename,grade from student,course,stucourse where student.stuid=stucourse.stuid and course.courseid=stucourse.courseid;";
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
System.out.println("姓名 科目 成绩 等级");
while(rs.next()){
System.out.print(rs.getString(1)+" ");
System.out.print(rs.getString(2)+" ");
System.out.print(rs.getFloat(3)+" ");
System.out.print(rs.getFloat(3)>=60?"及格":"不及格");
System.out.println();
if("语文".equals(rs.getString(2))){
yuwen_total+=rs.getFloat(3);
i++;
}
if("数学".equals(rs.getString(2))){
shuxue_total+=rs.getFloat(3);
j++;
}
}
System.out.println();
System.out.println("语文总分是:"+yuwen_total+" 语文平均分是:"+yuwen_total/i);
System.out.println("数学总分是:"+shuxue_total+" 数学平均分是:"+shuxue_total/j);
this.closeDB();
}
//更新学生信息
public void updateStuInfo(String newName,String oldName) throws Exception{
this.connect();
String sql = "UPDATE `test`.`student` SET `stuname`=? WHERE `stuname`=?; ";
pstmt = conn.prepareStatement(sql) ;// 使用预处理的方式创建对象
pstmt.setString(1, newName) ;// 第一个?号的内容
pstmt.setString(2, oldName) ;
if(pstmt.executeUpdate()>0){
System.out.println("更新数据成功!学生姓名"+newName);
}
this.closeDB();
}
//查询所有学生的信息
public void findStu() throws ClassNotFoundException, SQLException{
this.connect();
String sql ="select stuid,stuname from student;";
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
System.out.println("ID 姓名");
while(rs.next()){
System.out.print(rs.getInt(1)+" ");
System.out.print(rs.getString(2)+" ");
System.out.println();
}
System.out.println();
this.closeDB();
}
//查询所有科目信息
public void findCourse() throws ClassNotFoundException, SQLException{
this.connect();
String sql ="select courseid,coursename from course;";
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
System.out.println("ID 名称");
while(rs.next()){
System.out.print(rs.getInt(1)+" ");
System.out.print(rs.getString(2)+" ");
System.out.println();
}
System.out.println();
this.closeDB();
}
//插入学生成绩
public void insertGrade(int stuid,int courseid,float grade) throws ClassNotFoundException, SQLException{
this.connect();//数据库连接
String sql = "INSERT INTO `test`.`stucourse`(`stuid`,`courseid`,`grade`) values(?,?,?); ";
// 3、PreparedStatement接口需要通过Connection接口进行实例化操作
pstmt = conn.prepareStatement(sql) ;// 使用预处理的方式创建对象
pstmt.setInt(1, stuid) ;// 第一个?号的内容
pstmt.setInt(2, courseid);
pstmt.setFloat(3, grade);
// 执行SQL语句,更新数据库
if(pstmt.executeUpdate()>0){
System.out.println("插入数据成功!");
}
this.closeDB();
}
// 4、关闭数据库
public void closeDB(){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(pstmt!=null){
try {
pstmt.close() ;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
Display.java文件:
package dao;
import java.sql.SQLException;
import java.util.Scanner;
public class Display {
JDBCMySQL jdbcMySQL=new JDBCMySQL();
public void display() throws Exception{
Display t=new Display();
String input=null;
Scanner in = new Scanner(System.in);
while(true){
System.out.println("输入:\n1、查询学生信息。\n2、插入学生\n3、修改学生姓名\n4、删除学生信息\n5、插入学生成绩\n0、退出");
input = in.nextLine();
if(input.equals("0")){
System.out.println("感谢使用!");
break;
}else if(input.equals("2")){
System.out.println("请输入学生姓名:");
input=in.nextLine();
t.insert(input);
}else if(input.equals("1")){
t.select();
}else if(input.equals("3")){
this.findStu();
System.out.println("请输入要修改的学生姓名:");
String oldName=in.nextLine();
System.out.println("请输入新的学生姓名:");
String newName=in.nextLine();
t.update(newName,oldName);
}else if(input.equals("4")){
System.out.println("请输入要删除的学生姓名:");
String name=in.nextLine();
t.delete(name);
}else if(input.equals("5")){
int courseid=1;
float grade=0;
System.out.println("下面是学生信息:");
this.findStu();
System.out.println("请输入学生ID: ");
int stuid=in.nextInt();
System.out.println("请输入语文成绩:");
courseid=1;
grade=in.nextFloat();
this.insertGrade(stuid, courseid, grade);
System.out.println("请输入数学成绩:");
courseid=2;
grade=in.nextFloat();
this.insertGrade(stuid, courseid, grade);
}
}
in.close();
}
public void insert(String name) throws Exception{
jdbcMySQL.insertdate(name);
}
public void select() throws Exception{
jdbcMySQL.findStuInfo();
}
public void update(String newName,String oldName) throws Exception{
jdbcMySQL.updateStuInfo(newName, oldName);
}
public void delete(String name) throws ClassNotFoundException, SQLException{
jdbcMySQL.deleteStuInfo(name);
}
public void findStu() throws ClassNotFoundException, SQLException{
jdbcMySQL.findStu();
}
public void findCourse() throws ClassNotFoundException, SQLException{
jdbcMySQL.findCourse();
}
public void insertGrade(int stuid,int courseid,float grade) throws ClassNotFoundException, SQLException{
jdbcMySQL.insertGrade(stuid, courseid, grade);
}
}
Test.java文件:
package dao;
public class Test {
public static void main(String[] args) throws Exception {
Display d=new Display();
d.display();
}
}