基于mysql，servlet，jsp的学生信息管理系统,dao层

/**
 * 学生dao层
 */
package com.qingmang.dao;


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


import com.qingmang.dao.IStudentDao.IStudentDao;
import com.qingmang.domain.Student;
import com.qingmang.utils.JdbcUtil;


/**
 * @author administrator
 * 
 */
public class StudentDao implements IStudentDao {


private Connection conn = null;
private PreparedStatement pstmt = null;
private Statement stmt = null;
private ResultSet rs = null;


/*
* (non-Javadoc) 
* 根据 名字 得到学生对象
* @see com.qingmang.dao.inter.IUserDao#getByName(java.lang.String)
*/
public Student getByName(String name) {
Student student = null;


try {
conn = JdbcUtil.getConnection();
String sql = "select * from student where name=?";
pstmt = conn.prepareStatement(sql);
// 设置参数
pstmt.setString(1, name);


rs = pstmt.executeQuery();
if (rs.next()) {
Student stu = new Student();
stu.setName(rs.getString("name"));
stu.setSex(rs.getString("sex"));
stu.setAge(rs.getInt("age"));
stu.setGrade(rs.getInt("grade"));


}
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
} finally {
try {
JdbcUtil.close(conn, pstmt, rs);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
return student;
}


/*
* (non-Javadoc) 
* 得到所有学生
* @see com.qingmang.dao.inter.IStudentDao#getAll()
*/
public List<Student> getAll() {
List<Student> list = new ArrayList<Student>();


try {
conn = JdbcUtil.getConnection();
String sql = "select * from student";
pstmt = conn.prepareStatement(sql);


rs = pstmt.executeQuery();
while (rs.next()) {
Student stu = new Student();


stu.setId(rs.getInt("id"));
stu.setName(rs.getString("name"));
stu.setSex(rs.getString("sex"));
stu.setAge(rs.getInt("age"));
stu.setGrade(rs.getInt("grade"));


list.add(stu);
}
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
} finally {
try {
JdbcUtil.close(conn, pstmt, rs);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}


return list;
}


/*
* (non-Javadoc)
* 添加学生信息
* @see com.qingmang.dao.IStudentDao#addStudent(com.qingmang.domain.Student)
*/
@Override
public boolean addStudent(Student stu) {
boolean flag = false;


try {
conn = JdbcUtil.getConnection();
String sql = "INSERT INTO student " + "(id,name,sex,"
+ "age,grade) " + "VALUES (?,?,?,?,?)";
pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, stu.getId());
pstmt.setString(2, stu.getName());
pstmt.setString(3, stu.getSex());
pstmt.setInt(4, stu.getAge());
pstmt.setInt(5, stu.getGrade());


int count = pstmt.executeUpdate();
if (count > 0)
flag = true;


} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException();
} finally {
try {
JdbcUtil.close(conn, pstmt);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException();
}
}
return flag;
}


/*
* (non-Javadoc)
* 根据id删除学生信息
* @see com.qingmang.dao.IStudentDao#delStudent(int)
*/
@Override
public boolean delStudent(String id) {
boolean flag = false;
try {
// 连接数据库
conn = JdbcUtil.getConnection();


// 静态的sql语句
String sql = "delete from student where id='" + id + "'";
// 得到Statement对象
pstmt = conn.prepareStatement(sql);


// 执行sql语句(结果和数据库一样，)
int count = pstmt.executeUpdate();
if (count >= 1) {
flag = true;
}
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
} finally {
try {
JdbcUtil.close(conn, pstmt);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
return flag;
}


/*
* (non-Javadoc)
* 修改学生信息
* @see
* com.qingmang.dao.IStudentDao#updateStudent(com.qingmang.domain.Student)
*/
@Override
public boolean updateStudent(Student stu) {
boolean falg = false;
try {
conn = JdbcUtil.getConnection();
String sql = "update student set name=?,sex=?,age=?,grade=? where id=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, stu.getName());
pstmt.setString(2, stu.getSex());
pstmt.setInt(3, stu.getAge());
pstmt.setInt(4, stu.getGrade());
pstmt.setInt(5, stu.getId());


pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
} finally {
try {
JdbcUtil.close(conn, pstmt);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}


}
return falg;


}


/*
* (non-Javadoc) 
* 根据id查询学生信息，并返回学生对象
* @see com.qingmang.dao.IStudentDao#findStudentById(int)
*/
@Override
public Student findStudentById(int id) {
// 创建user的null
Student stu = null;
try {
conn = JdbcUtil.getConnection();
String sql = "select * from student where id=?";
pstmt = conn.prepareStatement(sql);
// 设置参数
pstmt.setInt(1, id);


rs = pstmt.executeQuery();
if (rs.next()) {
stu = new Student();
stu.setId(rs.getInt("id"));
stu.setName(rs.getString("name"));
stu.setSex(rs.getString("sex"));
stu.setAge(rs.getInt("age"));
stu.setGrade(rs.getInt("grade"));


}


} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException();
} finally {
try {
JdbcUtil.close(conn, pstmt);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException();
}
}


return stu;
}


/*
* (non-Javadoc) 
* 根据关键字查询学生信息，并返回学生集合
* @see com.qingmang.dao.IStudentDao#getByKey(java.lang.String)
*/
@Override
public List<Student> getByKey(String key) {
List<Student> list = new ArrayList<Student>();
try {
conn = JdbcUtil.getConnection();
String sql = "";
sql = "select * from student where 1=1";


/* 此处where 1=1 妙处在于在之后加条件时直接用and并列条件即可；也有一个在jsp页面多条件查询的方法，但需用到没学过的知识 */


if (key != null && !"".equals(key)) {
sql += " and name like '" + key + "%'";
}


stmt = conn.createStatement();


rs = stmt.executeQuery(sql);
while (rs.next()) {
Student stu = new Student();
stu.setId(rs.getInt("id"));
stu.setName(rs.getString("name"));
stu.setSex(rs.getString("sex"));
stu.setAge(rs.getInt("age"));
stu.setGrade(rs.getInt("grade"));


list.add(stu);
}
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
} finally {
try {
JdbcUtil.close(conn, pstmt, rs);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
return list;
}


}