基于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;

}

}