2018-12-29作业

接昨天作业
(6)定义查询所有学生的方法public List getAllStudent(){}
(7)定义根据id查询学生的方法public Student getStudentById(int id){}
(8)定义根据id删除学生的方法public int deleteStudentById(int id){}
(9)定义添加学员的方法public int addStudent(Student stu){}//注意只有数据库中没有有才能添，有无法添加
(10)定义根据id修改学员的信息public int updateStudentById(Student stu){}//注意只有数据库中有才能修改，没有无法修改

//学生的实体类
public class Student {
	private Integer id;
	private String name;
	private String sex;
	private Integer age;
	private Integer score;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
	public Integer getScore() {
		return score;
	}
	public void setScore(Integer score) {
		this.score = score;
	}
	public Student() {
		super();
		// TODO Auto-generated constructor stub
	}
	public Student(Integer id, String name, String sex, Integer age, Integer score) {
		super();
		this.id = id;
		this.name = name;
		this.sex = sex;
		this.age = age;
		this.score = score;
	}
	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + ", sex=" + sex + ", age=" + age + ", score=" + score + "]";
	}
	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + ((age == null) ? 0 : age.hashCode());
		result = prime * result + ((id == null) ? 0 : id.hashCode());
		result = prime * result + ((name == null) ? 0 : name.hashCode());
		result = prime * result + ((score == null) ? 0 : score.hashCode());
		result = prime * result + ((sex == null) ? 0 : sex.hashCode());
		return result;
	}
	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		Student other = (Student) obj;
		if (age == null) {
			if (other.age != null)
				return false;
		} else if (!age.equals(other.age))
			return false;
		if (id == null) {
			if (other.id != null)
				return false;
		} else if (!id.equals(other.id))
			return false;
		if (name == null) {
			if (other.name != null)
				return false;
		} else if (!name.equals(other.name))
			return false;
		if (score == null) {
			if (other.score != null)
				return false;
		} else if (!score.equals(other.score))
			return false;
		if (sex == null) {
			if (other.sex != null)
				return false;
		} else if (!sex.equals(other.sex))
			return false;
		return true;
	}
	
}

//与数据库连接的工具类
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCUtil {
	private static String driverClassName = "com.mysql.jdbc.Driver";
	private static String url = "jdbc:mysql://localhost:3306/mydb";
	private static String username = "root";
	private static String password = "root";
	//加载驱动
	static {
		try {
			Class.forName(driverClassName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	//得到连接
	public static Connection getConnection() throws SQLException {
		Connection connection = DriverManager.getConnection(url, username, password);
		
		return connection;
	}
	//关闭连接
	public static void closeConnection(Connection connection) throws SQLException {
		connection.close();
	}
	//执行增删改操作
	public static int executeUpdate(String sql,Object[] params) throws SQLException {
		Connection connection = getConnection();
		
		PreparedStatement s = connection.prepareStatement(sql);
		if(params != null){
			for(int i = 0; i < params.length ; i++) {
				s.setObject(i+1, params[i]);
			}
		}
		
		int n = s.executeUpdate();
		
		s.close();
		
		connection.close();
		
		return n;
	}
	//执行查询操作
	public static ResultSet executeQuery(Connection connection,String sql,Object[] params) throws SQLException {
		PreparedStatement s = connection.prepareStatement(sql);
		if(params != null){
			for(int i = 0; i < params.length ; i++) {
				s.setObject(i+1, params[i]);
			}
		}
		
		ResultSet rs = s.executeQuery();
		
		return rs;
	}
}

//方法实现类
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StuDaoImpl {
	//定义查询所有学生的方法
	public List<Student> getAllStudent() throws SQLException{
		String sql = "select * from student order by id";
		Connection connection = JDBCUtil.getConnection();
		ResultSet rs = JDBCUtil.executeQuery(connection, sql, null);
		List<Student> list = new ArrayList<>();
		Student stu = null;
		Integer id;
		String name;
		String sex;
		Integer age;
		Integer score;
		while(rs.next()){
			id = rs.getInt("id");
			name = rs.getString("name");
			sex = rs.getString("sex");
			age = rs.getInt("age");
			score = rs.getInt("score");
			stu = new Student(id, name, sex, age, score);
			list.add(stu);
		}
		return list;
	}
	
	//定义根据id查询学生的方法
	public Student getStudentById(int id) throws SQLException{
		String sql = "select * from student where id = ?";
		Connection connection = JDBCUtil.getConnection();
		ResultSet rs = JDBCUtil.executeQuery(connection, sql, new Object[]{id});
		Student stu = null;
		String name;
		String sex;
		Integer age;
		Integer score;
		if(rs.next()){
			id = rs.getInt("id");
			name = rs.getString("name");
			sex = rs.getString("sex");
			age = rs.getInt("age");
			score = rs.getInt("score");
			stu = new Student(id, name, sex, age, score);
		}
		return stu;
	}
	//定义根据id删除学生的方法
	public int deleteStudentById(int id) throws SQLException{
		String sql = "delete from student where id = ?";
		int n = JDBCUtil.executeUpdate(sql, new Object[]{id});
		return n;
	}
	
	//定义添加学员的方法,注意只有数据库中没有有才能添，有无法添加
	public int addStudent(Student stu) throws SQLException{
		Student s = getStudentById(stu.getId());
		int n;
		if(s == null){
			String sql = "insert into student values(?,?,?,?,?)";
			Object[] params = new Object[]{
					stu.getId(),
					stu.getName(),
					stu.getSex(),
					stu.getAge(),
					stu.getScore()
			};
			n = JDBCUtil.executeUpdate(sql, params);
		}else{
			n = 0;
		}
		return n;
	}
	
	//定义根据id修改学员的信息,注意只有数据库中有才能修改，没有无法修改
	public int updateStudentById(Student stu) throws SQLException{
		Student s = getStudentById(stu.getId());
		int n;
		if(s != null){
			StringBuilder stb = new StringBuilder();
			stb.append("update student");
			stb.append(" set name = ?,sex = ?,age = ?,score = ?");
			stb.append(" where id = ?");
			String sql = stb.toString();
			Object[] params = new Object[]{
					stu.getName(),
					stu.getSex(),
					stu.getAge(),
					stu.getScore(),
					stu.getId()
			};
			n = JDBCUtil.executeUpdate(sql, params);
		}else{
			n = 0;
		}
		
		return n;
	}
}

//测试类
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import com.neu.dao.DeptDaoImpl;
import com.neu.dao.EmpDaoImpl;
import com.neu.dao.JDBCUtil;
import com.neu.entity.Dept;
import com.neu.entity.Emp;

public class StuTest {

	public static void main(String[] args) throws SQLException {
		while(true) {
			System.out.println("欢迎来到学生系统！");
			System.out.println("请选择：");
			System.out.println("1.查询所有学生");
			System.out.println("2.根据id查询学生");
			System.out.println("3.根据id删除学生");
			System.out.println("4.添加学员");
			System.out.println("5.根据id修改学员信息");
			System.out.println("6.退出系统！");
			
			Scanner input = new Scanner(System.in);
			int n = input.nextInt();
			StuDaoImpl stuDao = new StuDaoImpl();
			List<Student> list = new ArrayList<>();
			
			switch(n){
			case 1:
				//查询所有学生
				list = stuDao.getAllStudent();
				System.out.println("学号\t姓名\t性别\t年龄\t成绩");
				for(Student stu : list) {
					System.out.print(stu.getId());
					System.out.print("\t"+stu.getName());
					System.out.print("\t"+stu.getSex());
					System.out.print("\t"+stu.getAge());
					System.out.print("\t"+stu.getScore());
					System.out.println();
				}
				continue;
			case 2:
				//根据id查询学生
				System.out.print("请输入学生学号：");
				Integer id = input.nextInt();
				Student stu = stuDao.getStudentById(id);
				System.out.println("学号\t姓名\t性别\t年龄\t成绩");
				System.out.print(stu.getId());
				System.out.print("\t"+stu.getName());
				System.out.print("\t"+stu.getSex());
				System.out.print("\t"+stu.getAge());
				System.out.print("\t"+stu.getScore());
				System.out.println();
				continue;
			case 3:
				//根据id删除学生
				System.out.print("请输入学生学号：");
				int id1 = input.nextInt();
				int x = stuDao.deleteStudentById(id1);
				if(x == 1){
					System.out.println("删除成功！");
				}else{
					System.out.println("删除失败！");
				}
				continue;
			case 4:
				//添加学员
				System.out.print("请输入学生学号：");
				int id2 = input.nextInt();
				System.out.print("请输入学生姓名：");
				String name = input.next();
				System.out.print("请输入学生性别：");
				String sex = input.next();
				System.out.print("请输入学生年龄：");
				Integer age = input.nextInt();
				System.out.print("请输入学生成绩：");
				Integer score = input.nextInt();
				Student stu1 = new Student(id2, name, sex, age, score);
				int m = stuDao.addStudent(stu1);
				if(m == 1){
					System.out.println("添加成功！");
				}else{
					System.out.println("添加失败！");
				}
				continue;
			case 5:
				//根据id修改学员信息
				System.out.print("请输入学生学号：");
				int id3 = input.nextInt();
				System.out.print("请输入修改后的学生姓名：");
				String name1 = input.next();
				System.out.print("请输入修改后的学生性别：");
				String sex1 = input.next();
				System.out.print("请输入修改后的学生年龄：");
				Integer age1 = input.nextInt();
				System.out.print("请输入修改后的学生成绩：");
				Integer score1 = input.nextInt();
				Student stu2 = new Student(id3, name1, sex1, age1, score1);
				int y = stuDao.updateStudentById(stu2);
				if(y == 1){
					System.out.println("修改成功！");
				}else{
					System.out.println("修改失败！");
				}
				continue;
			case 6:
				//退出系统！
				System.out.println("谢谢访问！");
				break;
			default:
				//输入错误
				System.out.println("输入错误，请重新输入！");
				continue;
			}
			
			break;
		}


	}

}