4到8.练习:改写学生管理系统:使用连接池
学习:第7遍
1.学生相关的主要功能有哪些?
(1).查询所有学生
(2).根据学号查询学生
(3).根据姓名和年龄范围查询学生,模糊查询
(4).添加学生
(5).修改学生
(6)根据学号删除学生
2.班级管理功能有哪些?
(1).查询所有班级
(2).根据班级号查询某个班级
(3).添加班级
技术要求:
(1).使用druid连接池,部署druid.jar包
(2).使用实体类即Bean类或Entity类
entity:实体类对应数据库中的表,类的属性对应表的字段
POJO(Plain Ordinary Java Object)简单的Java对象,类中可以包含:属性/get/set/构造方法,不允许有业务逻辑代码,实际就是普通JavaBeans
VO:Value Object值对象,用于封装 值、数据的对象,可能和数据库中表的字段不对应
src下的datasource.properties属性文件:
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8
username=root
password=123456
initialSize=10
maxActive=50
minIdle=5
maxWait=6000
entity包下的Student类:
package NewSMS.entity;
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;
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";
}
}
entity包下的Clazz类:
package NewSMS.entity;
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 + "]\n";
}
}
dao包下的ClazzDao类:
package NewSMS.dao;
import java.util.List;
import NewSMS.entity.Clazz;
import NewSMS.mapper.ClazzMapper;
import NewSMS.util.JdbcTemplate;
import NewSMS.util.RowMapper;
public class ClazzDao {
JdbcTemplate<Clazz> template=new JdbcTemplate<Clazz>();
RowMapper<Clazz> rm=(RowMapper<Clazz>) new ClazzMapper();
public List<Clazz> selectAll(){
String sql=new StringBuffer()
.append(" select ")
.append(" id,name ")
.append(" from ")
.append(" t_class ")
.toString();
return template.query(sql, rm);
}
public Clazz selectById(int id){
String sql=new StringBuffer()
.append(" select ")
.append(" id,name ")
.append(" from ")
.append(" t_class ")
.append(" where ")
.append(" id=? ")
.toString();
return template.queryForObject(sql, rm, id);
}
public void insert(String name){
String sql=new StringBuffer()
.append(" insert into ")
.append(" t_class ")
.append(" (name) ")
.append(" values ")
.append(" (?) ")
.toString();
template.update(sql, name);
}
}
dao包下的StudentDao类:
package NewSMS.dao;
import java.util.List;
import NewSMS.entity.Student;
import NewSMS.mapper.StudentMapper;
import NewSMS.util.JdbcTemplate;
import NewSMS.util.RowMapper;
public class StudentDao {
JdbcTemplate<Student> template=new JdbcTemplate<>();
RowMapper<Student> rm = (RowMapper<Student>) new StudentMapper();
public 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, rm);
}
public 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, rm,id);
return list.isEmpty()?null:list.get(0);
}
public 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, rm, "%"+name+"%",minAge,maxAge);
}
public 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());
}
public 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());
}
public void deleteById(int id){
String sql=new StringBuffer()
.append(" delete from ")
.append(" t_student ")
.append(" where ")
.append(" id=? ")
.toString();
template.update(sql, id);
}
}
util包下的JdbcUtil类:
package NewSMS.util;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
public class JdbcUtil {
private static DataSource dataSource;
static{
Properties p = new Properties();
try {
p.load(JdbcUtil.class.getClassLoader().getResourceAsStream("datasource.properties"));
dataSource=DruidDataSourceFactory.createDataSource(p);
} catch (Exception e) {
throw new ExceptionInInitializerError("JdbcUtil初始化失败:"+e);
}
}
public static Connection getConnection() {
try {
return dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
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);
}
}
util包下的JdbcTemplate类:
package NewSMS.util;
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 JdbcTemplate<T> {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
public List<T> query(String sql, RowMapper<T> rm, Object... params) {
List<T> list = new ArrayList<T>();
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()) {
list.add(rm.mapRow(rs));
}
} 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);
}
}
public T queryForObject(String sql, RowMapper<T> rm, Object... params) {
List<T> list = query(sql, rm, params);
return list.isEmpty() ? null : list.get(0);
}
public Integer save(String sql, Object... params) {
try {
conn = JdbcUtil.getConnection();
ps = conn.prepareStatement(sql,
PreparedStatement.RETURN_GENERATED_KEYS);
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
ps.executeUpdate();
rs = ps.getGeneratedKeys();
if (rs.next()) {
return rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(conn, ps, rs);
}
return null;
}
}
util包下的RowMapper类:
package NewSMS.util;
import java.sql.ResultSet;
import java.sql.SQLException;
public interface RowMapper<T> {
public T mapRow(ResultSet rs) throws SQLException;
}
view包下的Start类:
package NewSMS.view ;
import java.util.Scanner;
public class Start {
public static void main(String[] args) {
new Start().showSystemMenu();
}
public void showSystemMenu(){
System.out.println("=================欢迎使用学生管理系统=======================");
System.out.println("1.学生管理 2.班级管理");
System.out.print("请选择:");
Scanner input = new Scanner(System.in);
int choice = input.nextInt();
if(choice==1){
new StudentView().showMenu();
}else if(choice==2){
new ClazzView().showMenu();
}
}
}
view包下的StudentView类:
package NewSMS.view;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Scanner;
import NewSMS.dao.StudentDao;
import NewSMS.entity.Clazz;
import NewSMS.entity.Student;
public class StudentView {
Scanner input = new Scanner(System.in);
StudentDao studentDao = new StudentDao();
public void showMenu() {
System.out.println("1.查询所有学生");
System.out.println("2.根据学号查询学生");
System.out.println("3.根据姓名和年龄范围查询学生");
System.out.println("4.添加学生");
System.out.println("5.修改学生");
System.out.println("6.根据学号删除学生");
System.out.print("请选择:");
int choice = input.nextInt();
switch(choice){
case 1:
findAll();
break;
case 2:
findById();
break;
case 3:
findByCondition();
break;
case 4:
add();
break;
case 5:
modify();
break;
case 6:
removeById();
break;
}
showMenu();
}
private void findAll() {
List<Student> list = studentDao.selectAll();
System.out.println(list);
}
private void findById() {
System.out.print("请输入学号:");
Student student = studentDao.selectById(input.nextInt());
System.out.println(student);
}
private void findByCondition() {
System.out.print("请输入姓名:");
String name=input.next();
System.out.print("请输入最小年龄:");
int minAge = input.nextInt();
System.out.print("请输入最大年龄:");
int maxAge = input.nextInt();
List<Student> list = studentDao.selectByCondition(name, minAge, maxAge);
System.out.println(list);
}
private void add() {
Student stu = new Student();
System.out.print("请输入姓名:");
stu.setName(input.next());
System.out.print("请输入年龄:");
stu.setAge(input.nextInt());
System.out.print("请输入身高:");
stu.setHeight(input.nextDouble());
System.out.print("请输入出生日期:");
try {
stu.setBithday(new SimpleDateFormat("yyyy-MM-dd").parse(input.next()));
} catch (ParseException e) {
e.printStackTrace();
}
System.out.print("请输入班级号:");
Clazz clazz = new Clazz();
clazz.setId(input.nextInt());
stu.setClazz(clazz);
studentDao.insert(stu);
System.out.println("添加成功");
}
private void modify() {
}
private void removeById() {
}
}
view包下的ClazzView类:
package NewSMS.view;
import java.util.List;
import java.util.Scanner;
import NewSMS.dao.ClazzDao;
import NewSMS.entity.Clazz;
public class ClazzView {
Scanner input = new Scanner(System.in);
ClazzDao clazzDao=new ClazzDao();
public void showMenu(){
System.out.println("1.查询所有班级");
System.out.println("2.根据班级号查询班级");
System.out.println("3.添加班级");
System.out.print("请选择:");
int choice = input.nextInt();
switch(choice){
case 1:
findAll();
break;
case 2:
findById();
break;
case 3:
add();
break;
}
showMenu();
}
private void findAll() {
List<Clazz> list = clazzDao.selectAll();
System.out.println(list);
}
private void findById() {
System.out.print("请输入班级号:");
Clazz clazz = clazzDao.selectById(input.nextInt());
System.out.println(clazz);
}
private void add() {
System.out.print("请输入班级名称:");
clazzDao.insert(input.next());
System.out.println("添加成功");
}
}