数据库SQL的练习

已知考生表tdb_ks包含：考号?,姓名,成绩字段

表结构：
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| number | int(11)     | NO   |     | NULL    |                |
| name   | varchar(50) | NO   |     | NULL    |                |
| grade  | int(11)     | NO   |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

表记录:

+----+--------+------+-------+
| id | number | name | grade |
+----+--------+------+-------+
|  1 |   1001 | 小明    |    89 |
|  2 |   1002 | 小丽    |    79 |
|  3 |   1003 | 张三     |   85 |
|  4 |   1004 | 李四     |   99 |
+----+--------+------+-------+

程序实现如下要求:

   1>通过可视化工具创建数据库和表,要求定义id为主键并且自动增长.

   2>定义方法addStudent(Student student)
       功能:向考生表添加记录

   3>方法printMessageAllStudent()
       功能:打印所有考生信息

   4>方法 updateStudentByName(Student student,String name)
       功能:根据姓名更改考生信息

   5>方法 deleteStudentById(int number)
       功能:根据考号删除考生

   6>方法 int getSum()
       功能:查询所有学生总成绩

   7>方法 List<Student> getAllStudent()
       功能：查询高于平均分的所有学生

 

先建立学生类对象

public class Student {
 private int number;
 private String name;
 private int grade;

 public Student(int number, String name, int grade) {

  this.number = number;
  this.name = name;
  this.grade = grade;
 }

 public int getNumber() {
  return number;
 }

 public void setNumber(int number) {
  this.number = number;
 }

 public String getName() {
  return name;
 }

 public void setName(String name) {
  this.name = name;
 }

 public int getGrade() {
  return grade;
 }

 public void setGrade(int grade) {
  this.grade = grade;
 }

 @Override
 public String toString() {
  return "Student [number=" + number + ", name=" + name + ", grade=" + grade + "]";
 }
 
}

在创建连接 （用封装方法）

public class CouneDB {
 private static final String url =（统一资源定位符） 

 private static final String user = "root";（连接端口）
 private static final String password = "154331";（密码）

 public void CouneDB() {
  try {
   Class.forName("com.mysql.jpbc.Driver"); （加载相对应的mysql驱动）
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  }
 }

 public Connection getConnection() {
  Connection connect = null;
  try {
   connect = DriverManager.getConnection(url, user, password);
  } catch (SQLException e) {
   e.printStackTrace();
  }
  return connect;
 }

 public void closeConnection(Connection connect) {
  try {
   if (connect != null)
    connect.close();
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }
}

 

3.内容

public class StudentDB extends CouneDB {
 private static StudentDB DB = null;

 public static StudentDB getInstance() {
  if (DB == null) {
   DB = new StudentDB();
  }
  return DB;
 }

 private StudentDB() {

 }

 public void addStudent(Student student) {
  String sql = "insert into tdb_ks (number,name,grade) values(?,?,?)";
  Connection connect = null;
  PreparedStatement ps = null;
  try {
   connect = getConnection();
   ps = connect.prepareStatement(sql);

   ps.setInt(1, student.getNumber());
   ps.setString(2, student.getName());
   ps.setInt(3, student.getGrade());
   ps.execute();
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   if (ps != null) {
    try {
     ps.close();
    } catch (SQLException e) {
    }
   }
   closeConnection(connect);
  }
 }

 public ArrayList<Student> printMessageAllStudent() {
  String sql = "select * from tdb_ks";
  Connection connect = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  ArrayList<Student> studentList = null;
  try {
   connect = getConnection();
   ps = connect.prepareStatement(sql);
   rs = ps.executeQuery();

   studentList = new ArrayList<>();
   while (rs.next()) {
    int number = rs.getInt("number");
    String name = rs.getString("name");
    int grade = rs.getInt("grade");
    Student student = new Student(number, name, grade);
    studentList.add(student);
   }

  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   if (ps != null) {
    try {
     ps.close();
    } catch (SQLException e) {
    }
   }
  }
  return studentList;
 }

 public void updateStudentByName(Student student, String name) {
  String sql = "update tdb_ks set grade = ? where name = ?";
  Connection connect = null;
  PreparedStatement ps = null;
  try {
   connect = getConnection();
   ps = connect.prepareStatement(sql);
   ps.setInt(1, student.getGrade());
   ps.setString(2, name);
   ps.execute();
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   if (ps != null) {
    try {
     ps.close();
    } catch (SQLException e) {
    }
   }
  }
 }

 public void deleteStudentById(int number) {
  String sql = "delete from tdb_ks where number=?";
  Connection connect = null;
  PreparedStatement ps = null;
  try {
   connect = getConnection();
   ps = connect.prepareStatement(sql);
   ps.setInt(1, number);
   ps.execute();
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   if (ps != null) {
    try {
     ps.close();
    } catch (SQLException e) {
    }
   }
   closeConnection(connect);
  }
 }

 public int getSum() {
  String sql = "select sum(grade)   from tdb_ks;";
  Connection connect = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  int sum=-1;
  try {
   connect = getConnection();
   ps = connect.prepareStatement(sql);
   rs = ps.executeQuery();
   System.out.println("姓名" + "\t" + "学号" + "\t" + "成绩");
   if(rs.next()){
    sum = rs.getInt(1);
   }
  } catch (SQLException e) {
   e.printStackTrace();
  }finally {
   try {
   if(ps!= null)
     ps.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
   closeConnection(connect);
  }
  return sum;

 }
public void  printAllStudent(ArrayList<Student>studentList){
 System.err.println("学号" + "\t" + "姓名" + "\t" + "成绩" );
 for (Student student : studentList) {
  System.out.println(student.getNumber()+ "\t" + student.getName()
    + "\t" + student.getGrade());
 }
}
 public List<Student> getAllStudent(){
  String sql = "SELECT * FROM tdb_ks WHERE grade > (SELECT AVG(grade) FROM tdb_ks)";
  Connection connect =null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  List<Student> list = null;
  try {
   connect = getConnection();
   ps = connect.prepareStatement(sql);
   rs = ps.executeQuery();

   list = new ArrayList<>();
   while (rs.next()) {
    int number = rs.getInt("number");
    String name = rs.getString("name");
    int grade = rs.getInt("grade");
    Student student = new Student(number, name, grade);
    list.add(student);
   }

  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   if (ps != null) {
    try {
     ps.close();
    } catch (SQLException e) {
    }
   }
  }
  return list;
 }
 public static void main(String[] args) {
  StudentDB db = StudentDB.getInstance();

添加数据
    db.addStudent(new Student(1001, "小明", 89));

 

    db.addStudent(new Student(1002, "小丽", 79));

  db.addStudent(new Student(1003, "张三", 85));

   db.addStudent(new Student(1004, "李四", 89));
显示数据

   db.printAllStudent(db.printMessageAllStudent())；

删除  

db.deleteStudentById(1001);
总和 

 System.out.println(db.getSum());
修改

 db.updateStudentByName(new Student(1004, "李四", 99), "李四");
比平均值大  

ArrayList<Student> list = (ArrayList<Student>) db.getAllStudent();
  for (Student student : list) {
   System.out.println(student);
  }
 }

}