数据库程序实现增删改查的实际应用

package cn.sxb.day1107;

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

	public Student(){
		
	}

	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 + "]";
	}

}

package cn.sxb.day1102;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class UtilDB {
	private static final String URL = "jdbc:mysql://192.168.5.17:3306/db1606";
	private static final String USER = "test";
	private static final String PASSWORD = "123321";

	/**
	 * 构造方法加载驱动
	 */
	public UtilDB() {
		try {
			Class.forName("com.mysql.jdbc.Driver");// 加载mysql驱动
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 建立连接数据库
	 */
	public Connection getConnection() {
		Connection connect = null;
		try {
			connect = DriverManager.getConnection(URL, USER, PASSWORD);//试图建立到给定数据库URL的连接
			if (connect == null) {
				System.out.println("数据库连接失败");
			} else {
				System.out.println("数据库连接成功");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connect;
	}

	/**
	 * 关闭数据库连接
	 */
	public void closeConnection(Connection connect) {
		try {
			if (connect != null)
				connect.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

package cn.sxb.day1107;
/**
已知考生表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 | 小丽    |    90 |
|  3 |   1003 | 张三     |   95 |
|  4 |   1004 | 李四     |   89 |
+----+--------+------+-------+

程序实现如下要求:

   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()
       功能：查询高于平均分的所有学生
	   */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.sxb.day1102.UtilDB;

public class DBDemo extends UtilDB {
	
	public DBDemo() {

	}

	/**
	 * 添加学生 + "(1001,'小明',89)," + "(1002,'小丽',90)," + "(1003,'张三',95)," +
	 * "(1004,'李四',89)";
	 */
	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 {
			try {
				if (ps != null)
					ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			closeConnection(connect);
		}
	}
	/**
	 * 打印所有考生信息
	 */
	public void printMessageAllStudent(){
		String sql = "SELECT * FROM tdb_ks";
		Connection connect = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			connect = getConnection();
			ps = connect.prepareStatement(sql);
			rs = ps.executeQuery();
			
			while(rs.next()){
				int number = rs.getInt("number");
				String name = rs.getString("name");
				int grade = rs.getInt("grade");
				System.out.println(number+"\t"+name+"\t"+grade);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(rs != null)
					rs.close();
				if (ps != null)
					ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			closeConnection(connect);
		}
	}
	/**
	 * 根据姓名更改考生信息
	 * @param student
	 * @param name
	 */
	public void updateStudentByName(Student student,String name){
		String sql = "UPDATE tdb_ks SET number = ? WHERE name = ?";
		Connection connect = null;
		PreparedStatement ps = null;
		try {
			connect = getConnection();
			ps = connect.prepareStatement(sql);
			ps.setInt(1, student.getNumber());
			ps.setString(2, name);
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if (ps != null)
					ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			closeConnection(connect);
		}
		
	}
	/**
	 * 根据考号删除考生
	 * @param num
	 */
	public void deleteStudentById(int num){
		Connection connect = null;
		PreparedStatement ps = null;
		try {
			String sql = "DELETE FROM tdb_ks WHERE number = ?";
			connect = getConnection();
			ps = connect.prepareStatement(sql);
			ps.setInt(1, num);
			ps.execute();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if (ps != null)
					ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			closeConnection(connect);
		}
	}
	/**
	 * 查询所有学生总成绩
	 * @return
	 */
	public int getSum(){
		String sql = "SELECT sum(grade) FROM tdb_ks";
		Connection connect = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int sumGrade = 0;
		try {
			connect = getConnection();
			ps = connect.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()){
			sumGrade = rs.getInt("sum(grade)");//"sum(grade)"可以写成1
//			System.out.println(sumGrade);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if (ps != null)
					ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			closeConnection(connect);
		}
		return sumGrade;
	}
	
	/**
	 * 查询高于平均分的所有学生
	 * @param args
	 */
	public  List<Student> getAllStudent(){
		ArrayList<Student> lists = new ArrayList<Student>();
		String sql = " SELECT * FROM tdb_ks WHERE grade > (SELECT AVG(grade) FROM tdb_ks)";
		Connection connect = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			connect = getConnection();
			ps = connect.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				int number = rs.getInt("number");
				String name = rs.getString("name");
				int grade = rs.getInt("grade");
				
				Student stu = new Student(number,name,grade);
				lists.add(stu);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return lists;
	}
	
	public static void main(String[] args){
		DBDemo db = new DBDemo();
//		Student stu = new Student(1001,"小明",89);
		//db.addStudent(stu);
//		db.addStudent(new Student(1002,"小丽",90));
//		db.addStudent(new Student(1003,"张三",95));
//		db.addStudent(new Student(1004,"李四",89));
		
//		db.printMessageAllStudent();
		
//		db.deleteStudentById(1001);
		
//		Student student = new Student(1008,"张三",95);
//		db.updateStudentByName(student, "张三");
		
		int sum = db.getSum();
		System.out.println(sum);
//		List<Student> studentList = db.getAllStudent();
//		for(Student stu : studentList){
//			System.out.println(stu);
//		}
	}
}