一、Studnet表:
属性:sno int(20)、cno int(4)、grade int(4)。即学号、课程号、成绩。
二、Java中的类
用到的类: DBUtil(连接、关闭数据库)、Student(学生信息)、Userdemo(接口:实现Student表的增删该查)、UserdemoImpl(实现Userdemo接口里的方法)、Test(测试台);
1、Student
//Student表
public class Student
{
private int sno;
private int cno;
private int grade;
public Student(int sno, int cno, int grade)
{
this.sno = sno;
this.cno = cno;
this.grade = grade;
}
public Student()
{
}
public String toString()
{
return this.sno+","+this.cno+","+this.grade;
}
public int getSno()
{
return sno;
}
public void setSno(int sno)
{
this.sno = sno;
}
public int getCno()
{
return cno;
}
public void setCno(int cno)
{
this.cno = cno;
}
public int getGrade()
{
return grade;
}
public void setGrade(int grade)
{
this.grade = grade;
}
}
2、DBUtil
import java.io.*;
import java.util.*;
import java.sql.*;
public class DBUtil
{
private static String driver = "com.mysql.cj.jdbc.Driver"; //用于加载驱动
private static String url = "jdbc:mysql://localhost:3306/sc3?serverTimezone=UTC"; //数据库地址
private static String username = "root"; //用户名
private static String password = "123456"; //密码
//连接数据库
public static Connection open()
{
try
{
Class.forName(driver); //加载驱动
return DriverManager.getConnection(url, username, password);
} catch (Exception e)
{
e.printStackTrace();
}
return null;
}
//关闭数据库
public static void close(Connection conn)
{
if(conn!=null)
{
try
{
conn.close();
} catch (SQLException e)
{
e.printStackTrace();
}
}
}
}
3、Userdemo
import java.util.ArrayList;
//实现对数据库中sc表的增、删、改、查
public interface Userdemo
{
public void Insert(Student s);
public void Delete(int sno,int cno);
public void Update(Student s);
public Student Get(int sno,int cno);
public ArrayList<Student> SelectAll();
}
4、UserdemoImpl
import java.util.*;
import java.sql.*;
public class UserdemoImpl implements Userdemo //实现Userdemo接口的方法
{
@Override
public void Insert(Student s) //增
{
//链接数据库
Connection conn = DBUtil.open();
//创建语句
String sql = "insert into sc values(?,?,?)";
try
{
//动态操作sql语句
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,s.getSno());
pstmt.setInt(2,s.getCno());
pstmt.setInt(3,s.getGrade());
pstmt.executeUpdate();
} catch (SQLException e)
{
e.printStackTrace();
}
finally
{
DBUtil.close(conn); //关闭数据库
}
}
@Override
public void Delete(int sno, int cno) //删
{
Connection conn = DBUtil.open();
String sql = "delete from sc where sno=? and cno=?";
try
{
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,sno);
pstmt.setInt(2,cno);
pstmt.executeUpdate();
} catch (SQLException e)
{
e.printStackTrace();
}
finally
{
DBUtil.close(conn);
}
}
@Override
public void Update(Student s) //改
{
Connection conn = DBUtil.open();
String sql = "update sc set grade=? where sno=? and cno=?";
try
{
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,s.getGrade());
pstmt.setInt(2,s.getSno());
pstmt.setInt(3,s.getCno());
pstmt.executeUpdate();
} catch (Exception e)
{
e.printStackTrace();
}
finally
{
DBUtil.close(conn);
}
}
@Override
public Student Get(int sno, int cno) //查
{
Connection conn = DBUtil.open();
String sql = "select grade from sc where sno=? and cno=?";
try
{
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,sno);
pstmt.setInt(2,cno);
ResultSet rs = pstmt.executeQuery();
if(rs.next())
{
int grade = rs.getInt(1);
Student s = new Student(sno,cno,grade);
return s;
}
} catch (Exception e)
{
e.printStackTrace();
}
finally
{
DBUtil.close(conn);
}
return null;
}
@Override
public ArrayList<Student> SelectAll() //遍历
{
ArrayList<Student> stu = new ArrayList<Student>();
Connection conn = DBUtil.open();
String sql = "select * from sc";
try
{
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while(rs.next())
{
int sno = rs.getInt(1);
int cno = rs.getInt(2);
int grade = rs.getInt(3);
Student s = new Student(sno,cno,grade);
stu.add(s);
}
return stu;
} catch (SQLException e)
{
e.printStackTrace();
}
finally
{
DBUtil.close(conn);
}
return null;
}
}
5、Test
import java.util.*;
import java.sql.*;
public class Test
{
public static void Create() //创建Student表
{
Connection conn = DBUtil.open(); //连接数据库
String sql = "create table student(sno int(20),cno int(4),grade int(4),primary key(sno,cno))";
try
{
Statement stmt = conn.createStatement(); //静态操作sql语句
stmt.execute(sql);
} catch (SQLException e)
{
e.printStackTrace();
}
finally
{
DBUtil.close(conn);
}
}
public static void show() //提示信息
{
System.out.println("***********成绩数据库管理***********");
System.out.println("insert: 插入成绩.");
System.out.println("delete: 删除成绩.");
System.out.println("update: 更新成绩.");
System.out.println("get: 查看成绩.");
System.out.println("list: 查看所有成绩.");
System.out.println("***********成绩数据库管理***********");
}
public static void main(String[] args)
{
Scanner in = new Scanner(System.in);
// Create();
Userdemo userdemo = new UserdemoImpl(); //多态
Student stu = new Student();
while(true)
{
show();
System.out.print("命令>");
String s = in.next();
if(s.equals("insert"))
{
System.out.println("请输入正确的信息,如果重复、不存在、信息错误,都会出现错误.");
System.out.print("请输入学号:");
int sno = in.nextInt();
System.out.print("请输入课程号:");
int cno = in.nextInt();
System.out.print("请输入成绩:");
int grade = in.nextInt();
stu.setSno(sno);
stu.setCno(cno);
stu.setGrade(grade);
userdemo.Insert(stu);
}
else if(s.equals("delete"))
{
System.out.println("请输入正确的信息,如果重复、不存在、信息错误,都会出现错误.");
System.out.print("请输入待删除的学号:");
int sno = in.nextInt();
System.out.print("请输入待删除的课程号:");
int cno = in.nextInt();
userdemo.Delete(sno, cno);
}
else if(s.equals("update"))
{
System.out.println("请输入正确的信息,如果重复、不存在、信息错误,都会出现错误.");
System.out.print("请输入待更新的学号:");
int sno = in.nextInt();
System.out.print("请输入待更新的课程号:");
int cno = in.nextInt();
System.out.print("请输入新成绩:");
int grade = in.nextInt();
stu.setSno(sno);
stu.setCno(cno);
stu.setGrade(grade);
userdemo.Update(stu);
}
else if(s.equals("get"))
{
System.out.println("请输入正确的信息,如果重复、不存在、信息错误,都会出现错误.");
System.out.print("请输入查询的学号:");
int sno = in.nextInt();
System.out.print("请输入查询的课程号:");
int cno = in.nextInt();
stu = userdemo.Get(sno, cno);
System.out.println("成绩:"+stu.getGrade());
}
else if(s.equals("list"))
{
ArrayList<Student> note = userdemo.SelectAll();
for(int i=0,t=note.size();i<t;i++)
{
System.out.println(note.get(i));
}
}
else if(s.equals("exit"))
{
System.out.println("再见!");
System.exit(0);
}
}
}
}