public class StuDao {
/**
* 添加学生
* @param stu 要添加的学生对象
* @return 添加成功返回1,失败返回0
*/
public int addStu(Student stu) {
int i = 0;
Connection con = null;
PreparedStatement ps = null;
//连接数据库
try {
con = DBHelper.getCon();
String sql = "insert into Student values(?,?,?,?,?,?)";
ps = con.prepareStatement(sql);
//给占位符赋值
ps.setString(1, stu.getSname());
ps.setString(2, stu.getSsex());
ps.setInt(3, stu.getSage());
ps.setString(4, stu.getSlike());
ps.setString(5, stu.getSaddress());
ps.setString(6, stu.getSinfo());
//执行sql语句
i = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.closeDb(con, ps, null);
}
return i;
}
//删
/**
* 删除学生:根据学号删除学生
* @param sid 要删除的学生学号
* @return 成功返回1,失败返回0
*/
public int delById(int sid) {
//连接数据库
Connection con = null;
PreparedStatement ps = null;
int i = 0;
try {
//调用DBHelper类中的连接方法
con = DBHelper.getCon();
String sql = "delete Student where sid=?";
ps = con.prepareStatement(sql);
//给占位符赋值
ps.setInt(1, sid);
//执行sql语句 接收受影响行数
i = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
//关闭连接
DBHelper.closeDb(con, ps, null);
}
//返回受影响行数
return i;
}
//改
/**
* 修改学生信息
* @param sid 要修改的学生学号
* @param stu 修改后的学生信息对象
* @return 成功返回1,失败返回0
*/
public int updateById(int sid,Student stu) {
int i = 0;
Connection con = null;
PreparedStatement ps = null;
try {
//获取数据库连接
con = DBHelper.getCon();
//定义sql语句
String sql = "update Student set sname=?,ssex=?,sage=?,slike=?,saddress=?,sinfo=? where sid=?";
//获取PreparedStatement对象 用来执行sql语句
ps = con.prepareStatement(sql);
//给占位符赋值
ps.setString(1, stu.getSname());
ps.setString(2, stu.getSsex());
ps.setInt(3, stu.getSage());
ps.setString(4, stu.getSlike());
ps.setString(5, stu.getSaddress());
ps.setString(6, stu.getSinfo());
ps.setInt(7, sid);
//执行sql语句 接收受影响行数
i = ps.executeUpdate();
} catch (Exception e) {
//异常信息
e.printStackTrace();
}finally {
//关闭连接
DBHelper.closeDb(con, ps, null);
}
//返回受影响行数
return i;
}
//查所有
/**
* 查询所有
* @return 返回学生集合,如果没查询到就返回长度为0的集合
*/
public ArrayList<Student> getAll(){
//创建集合 用来存查询到的学生对象
ArrayList<Student> slist = new ArrayList<Student>();
ResultSet rs = null;
PreparedStatement ps = null;
Connection con = null;
try {
//连接数据库
con = DBHelper.getCon();
//定义sql语句
String sql = "select * from Student";
ps = con.prepareStatement(sql);
//执行sql语句
rs = ps.executeQuery();
//处理结果:遍历结果集
while(rs.next()) {
//每循环一次 实例化一个学生对象
Student stu = new Student(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7));
//把学生对象放到集合中
slist.add(stu);
}
} catch (Exception e) {
//异常信息
e.printStackTrace();
}finally {
//关闭连接
DBHelper.closeDb(con, ps, rs);
}
//返回学生集合
return slist;
}
//模糊查询
/**
* 模糊查询
* @param col 字段名
* @param str 字段的关键字
* @return 查询到的学生集合
*/
public ArrayList<Student> getByLike(String col,String str){
ArrayList<Student> slist = new ArrayList<>();
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DBHelper.getCon();
String sql = "select * from Student where "+col+" like '%"+str+"%'";
ps = con.prepareStatement(sql);
//给占位符赋值
//执行sql语句
rs = ps.executeQuery();
while(rs.next()) {
//每循环一次 实例化一个学生对象
Student stu = new Student(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7));
//把学生对象放到集合中
slist.add(stu);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.closeDb(con, ps, rs);
}
return slist;
}
//查询单个
/**
* 查询单个学生
* @param sid 要查询的学生学号
* @return 查询到返回学生对象,否则返回null
*/
public Student getById(int sid) {
Student stu = null;
ResultSet rs = null;//结果集
Connection con = null;
PreparedStatement ps = null;
try {
//连接数据库
con = DBHelper.getCon();
//执行sql语句那个对象
String sql = "select * from Student where sid=?";
ps = con.prepareStatement(sql);
//给占位符赋值
ps.setInt(1, sid);
//执行sql语句 接收结果集
rs = ps.executeQuery();
//处理结果:遍历结果集
if(rs.next()) {
//创建学生对象
stu = new Student();
//从结果集中获取数据 赋值给学生对象
stu.setSid(rs.getInt(1));//获取学号赋值给学生学号
stu.setSname(rs.getString(2));
stu.setSlike(rs.getString("slike"));
stu.setSage(rs.getInt("sage"));
stu.setSsex(rs.getString("ssex"));
stu.setSaddress(rs.getString("saddress"));
stu.setSinfo(rs.getString("sinfo"));
}
} catch (Exception e) {
//异常信息
e.printStackTrace();
}finally {
//关闭连接
DBHelper.closeDb(con, ps, rs);
}
//返回学生对象
return stu;
}
}