10到14.练习：学生管理系统的增删改查

10到14.练习：学生管理系统的增删改查

学习：第7遍

---

1.练习：学生管理系统

主要功能：
(1).查询所有学生
(2).根据学号查询学生
(3).根据姓名和年龄范围查询学生，模糊查询
(4).添加学生
(5).修改学生
(6)根据学号删除学生

JdbcUtil工具类：

package sms;

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

/**
 * JDBC工具类
 */
public class JdbcUtil {
	/**
	 * 获取数据库连接
	 */
	public static Connection getConnection() {
		String driverClassName = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true";
		String username = "root";
		String password = "123456";

		Connection conn = null;
		try {
			Class.forName(driverClassName);
			conn = DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 关闭资源
	 */
	public static void close(Connection conn, Statement stmt, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void close(Connection conn, Statement stmt) {
		close(conn, stmt, null);
	}
}

Student 类：

package sms;

import java.io.Serializable;
import java.util.Date;

/*
 * 学生类
 */
public class Student implements Serializable {

	private static final long serialVersionUID = -5984449679344766226L;

	private Integer id;
	private String name;
	private Integer age;
	private Double height;
	//import java.util.Date
	private Date bithday;
	private Clazz clazz; // 所在班级

	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 Integer getAge() {
		return age;
	}

	public void setAge(Integer age) {
		this.age = age;
	}

	public Double getHeight() {
		return height;
	}

	public void setHeight(Double height) {
		this.height = height;
	}

	public Date getBithday() {
		return bithday;
	}

	public void setBithday(Date bithday) {
		this.bithday = bithday;
	}

	public Clazz getClazz() {
		return clazz;
	}

	public void setClazz(Clazz clazz) {
		this.clazz = clazz;
	}

	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + ", age=" + age
				+ ", height=" + height + ", bithday=" + bithday + ", clazz="
				+ clazz + "]\n";
	}

	
}

Clazz类：

package sms;

import java.io.Serializable;

/*
 * 班级类
 */
public class Clazz implements Serializable {

	private static final long serialVersionUID = 4262995174037152678L;

	private Integer id;
	private String name;

	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;
	}

	@Override
	public String toString() {
		return "Clazz [id=" + id + ", name=" + name + "]";
	}

	
}

StudentTemplate 类：

package sms;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;



public class StudentTemplate {
	Connection conn=null;
	PreparedStatement ps=null;
	ResultSet rs=null;
	
	/**
	 * 查询操作
	 */
	public List<Student> query(String sql,Object... params){
		List<Student> list=new ArrayList<Student>();
		try {
			conn=JdbcUtil.getConnection();
			ps=conn.prepareStatement(sql);
			//问号有几个参数，数组就有几个值
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i+1, params[i]);
			}
			rs=ps.executeQuery();
			while(rs.next()){
				Student stu = new Student();
				stu.setId(rs.getInt("id"));
				stu.setName(rs.getString("name"));
				stu.setAge(rs.getInt("age"));
				stu.setHeight(rs.getDouble("height"));
				// getDate()返回的是java.sql.Date
				//  sql.Date会自动转换成util.Date
				stu.setBithday(rs.getDate("birthday")); 
				Clazz clazz = new Clazz();
				clazz.setId(rs.getInt("cid"));
				clazz.setName(rs.getString("cname"));
				stu.setClazz(clazz);
				
				list.add(stu);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.close(conn, ps, rs);
		}
		return list;
	}

	/**
	 * 更新操作
	 */
	public void update(String sql,Object... params){
		try {
			conn=JdbcUtil.getConnection();
			ps=conn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i+1,params[i]);
			}
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.close(conn, ps, rs);
		}
	}
}

Test 类：

package sms;

import java.util.Date;
import java.util.List;

public class Test {
	
	static StudentTemplate template=new StudentTemplate();
	
	public static void main(String[] args) {
		
		System.out.println(selectAll());
     	System.out.println(selectById(2));
		System.out.println(selectByCondition("三", 10, 29));
		
		Student stu = new Student();
		stu.setId(4);
		stu.setName("周杰伦 ");
		stu.setAge(28);
		stu.setHeight(170.5);
		stu.setBithday(new Date());
		Clazz clazz = new Clazz();
		clazz.setId(1);
		stu.setClazz(clazz);
//		insert(stu);
	    update(stu);
		
		deleteById(1);
		
		System.out.println("success");
	}
	
	// 1.查询所有学生
	public static List<Student> selectAll(){
 		String sql=new StringBuffer()
			.append(" select ")
			.append(" 	s.id,s.name,s.age,s.height,s.birthday,c.id cid,c.name cname ")
			.append(" from ")
			.append(" 	t_student s,t_class c")
			.append(" where ")
			.append(" 	s.cid=c.id ")
			.toString();
 		return template.query(sql);
	}
	
	// 2.根据学号查询学生
	public static Student selectById(int id){
 		String sql=new StringBuffer()
			.append(" select ")
			.append(" 	s.id,s.name,s.age,s.height,s.birthday,c.id cid,c.name cname ")
			.append(" from ")
			.append(" 	t_student s,t_class c")
			.append(" where ")
			.append(" 	s.cid=c.id and s.id=?")
			.toString();
		List<Student> list = template.query(sql, id);
		return list.isEmpty()?null:list.get(0);
	}

	// 3.根据姓名和年龄范围查询学生
	public static List<Student> selectByCondition(String name,int minAge,int maxAge){
		String sql=new StringBuffer()
		.append(" select ")
		.append(" 	s.id,s.name,s.age,s.height,s.birthday,c.id cid,c.name cname ")
		.append(" from ")
		.append(" 	t_student s,t_class c")
		.append(" where ")
		.append(" 	s.cid=c.id and s.name like ? and s.age between ? and ?")
		.toString();
		return template.query(sql, "%"+name+"%",minAge,maxAge);
	}
	
	// 4.添加学生
	public static void insert(Student stu){
		String sql=new StringBuffer()
			.append(" insert into ")
			.append(" 	t_student ")
			.append(" 		(name,age,height,birthday,cid) ")
			.append(" values ")
			.append("  		(?,?,?,?,?) ")
			.toString();
		template.update(sql, stu.getName(),stu.getAge(),stu.getHeight(),stu.getBithday(),stu.getClazz().getId());
	}
	
	// 5.修改学生
	public static void update(Student stu){
		String sql=new StringBuffer()
			.append(" update ")
			.append(" 	t_student ")
			.append(" set ")
			.append("  	name=?,age=?,height=?,birthday=?")
			.append(" where ")
			.append(" 	id=? ")
			.toString();
		template.update(sql, stu.getName(),stu.getAge(),stu.getHeight(),stu.getBithday(),stu.getId());
	}
	
	// 6.根据学号删除学生
	public static void deleteById(int id){
		String sql=new StringBuffer()
			.append(" delete from ")
			.append(" 	t_student ")
			.append(" where ")
			.append(" 	id=? ")
			.toString();
		template.update(sql, id);
	}
	
}

sql语句：

create table t_class
(
  id int auto_increment primary key,
  name varchar(200)
  
)charset utf8;

create table t_student
(
   id int auto_increment primary key,
   name varchar(50) not null unique,
   age int,
   height double,
   birthday date,
   cid int,
   foreign key(cid) references t_class(id)
)charset utf8;

insert into t_class (name) values("Java一班");
insert into t_class (name) values("Java二班");
insert into t_class (name) values("Python一班");
insert into t_class (name) values("Python二班");

insert into t_student() values(null,'汤小洋',20,180.5,'1990-12-4',1);
insert into t_student() values(null,'唐伯虎',25,173.5,curdate(),3);
insert into t_student() values(null,'张三',27,177,'1992-10-14',2);