notedaoimpl.java

Struts+DAO实现的留言板管理程序

/*
 *@author RenGuiju ,Creation date:Nov 18, 2008
 *
 *Email:[e-mail]
 */
package edu.bupt.struts.note.impl;

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

import edu.bupt.struts.note.dao.NoteDAO;
import edu.bupt.struts.note.dbc.DataBaseConnection;
import edu.bupt.struts.note.vo.Note;

public class NoteDAOImpl implements NoteDAO {
	// 增加操作
	public void insert(Note note) throws Exception {
		String sql = "INSERT INTO note(title,author,content) VALUES(?,?,?)";
		PreparedStatement pstmt = null;
		DataBaseConnection dbc = null;
		dbc = new DataBaseConnection();
		try {
			pstmt = dbc.getConnection().prepareStatement(sql);
			pstmt.setString(1, note.getTitle());
			pstmt.setString(2, note.getAuthor());
			pstmt.setString(3, note.getContent());
			pstmt.executeUpdate();
			pstmt.close();
		} catch (Exception e) {
			// System.out.println(e) ;
			throw new Exception("操作中出现错误！！！");
		} finally {
			dbc.close();
		}
	}

	// 修改操作
	public void update(Note note) throws Exception {
		String sql = "UPDATE note SET title=?,author=?,content=? WHERE id=?";
		PreparedStatement pstmt = null;
		DataBaseConnection dbc = null;
		dbc = new DataBaseConnection();
		try {
			pstmt = dbc.getConnection().prepareStatement(sql);
			pstmt.setString(1, note.getTitle());
			pstmt.setString(2, note.getAuthor());
			pstmt.setString(3, note.getContent());
			pstmt.setInt(4, note.getId());
			pstmt.executeUpdate();
			pstmt.close();
		} catch (Exception e) {
			throw new Exception("操作中出现错误！！！");
		} finally {
			dbc.close();
		}
	}

	// 删除操作
	public void delete(int id) throws Exception {
		String sql = "DELETE FROM note WHERE id=?";
		PreparedStatement pstmt = null;
		DataBaseConnection dbc = null;
		dbc = new DataBaseConnection();
		try {
			pstmt = dbc.getConnection().prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
			pstmt.close();
		} catch (Exception e) {
			throw new Exception("操作中出现错误！！！");
		} finally {
			dbc.close();
		}
	}

	// 按ID查询，主要为更新使用
	public Note queryById(int id) throws Exception {
		Note note = null;
		String sql = "SELECT id,title,author,content FROM note WHERE id=?";
		PreparedStatement pstmt = null;
		DataBaseConnection dbc = null;
		dbc = new DataBaseConnection();
		try {
			pstmt = dbc.getConnection().prepareStatement(sql);
			pstmt.setInt(1, id);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				note = new Note();
				note.setId(rs.getInt(1));
				note.setTitle(rs.getString(2));
				note.setAuthor(rs.getString(3));
				note.setContent(rs.getString(4));
			}
			rs.close();
			pstmt.close();
		} catch (Exception e) {
			throw new Exception("操作中出现错误！！！");
		} finally {
			dbc.close();
		}
		return note;
	}

	// 查询全部
	public List queryAll() throws Exception {
		List list = new ArrayList();
		String sql = "SELECT id,title,author,content FROM note";
		PreparedStatement pstmt = null;
		DataBaseConnection dbc = null;
		dbc = new DataBaseConnection();
		try {
			pstmt = dbc.getConnection().prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				Note note = new Note();
				note.setId(rs.getInt(1));
				note.setTitle(rs.getString(2));
				note.setAuthor(rs.getString(3));
				note.setContent(rs.getString(4));
				list.add(note);
			}
			rs.close();
			pstmt.close();
		} catch (Exception e) {
			System.out.println(e);
			throw new Exception("操作中出现错误！！！");
		} finally {
			dbc.close();
		}
		return list;
	}

	// 模糊查询
	public List queryByLike(String cond) throws Exception {
		List all = new ArrayList();
		String sql = "SELECT id,title,author,content FROM note WHERE title LIKE ? or AUTHOR LIKE ? or CONTENT LIKE ?";
		PreparedStatement pstmt = null;
		DataBaseConnection dbc = null;
		dbc = new DataBaseConnection();
		try {
			pstmt = dbc.getConnection().prepareStatement(sql);
			pstmt.setString(1, "%" + cond + "%");
			pstmt.setString(2, "%" + cond + "%");
			pstmt.setString(3, "%" + cond + "%");
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				Note note = new Note();
				note.setId(rs.getInt(1));
				note.setTitle(rs.getString(2));
				note.setAuthor(rs.getString(3));
				note.setContent(rs.getString(4));
				all.add(note);
			}
			rs.close();
			pstmt.close();
		} catch (Exception e) {
			System.out.println(e);
			throw new Exception("操作中出现错误！！！");
		} finally {
			dbc.close();
		}
		return all;
	}
};