webnotedaoex.java

本人做的个人留言本

package com.dao;

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

import com.connection.DataBaseConnection;
import com.vo.WebNote;

public class WebNoteDaoEx implements WebNoteDao {

	// 增加操作
	public void insert(WebNote note) throws Exception {
		String sql = "INSERT INTO webnote(title,name,qq,email,sex,image,content,addtime) 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.getName());
			pstmt.setString(3, note.getQq());
			pstmt.setString(4, note.getEmail());
			pstmt.setString(5, note.getSex());
			pstmt.setString(6, note.getImage());
			pstmt.setString(7, note.getContent());
			pstmt.setString(8, note.getAddtime());
			pstmt.executeUpdate();
			pstmt.close();
		} catch (Exception e) {
			throw new Exception("操作中出现错误！！！");
		} finally {
			dbc.close();
		}
	}

	// 修改操作
	public void update(WebNote note) throws Exception {
		String sql = "UPDATE webnote SET title=?,name=?,qq=?,email=?,sex=?,image=?,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.getName());
			pstmt.setString(3, note.getQq());
			pstmt.setString(4, note.getEmail());
			pstmt.setString(5, note.getSex());
			pstmt.setString(6, note.getImage());
			pstmt.setString(7, note.getContent());
			pstmt.setInt(8, 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 webnote 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 WebNote queryById(int id) throws Exception {
		WebNote note = null;
		String sql = "SELECT * FROM webnote 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();
			while (rs.next()) {
				note = new WebNote();
				note.setId(rs.getInt(1));
				note.setTitle(rs.getString(2));
				note.setName(rs.getString(3));
				note.setQq(rs.getString(4));
				note.setEmail(rs.getString(5));
				note.setSex(rs.getString(6));
				note.setImage(rs.getString(7));
				note.setContent(rs.getString(8));
				note.setAddtime(rs.getString(9));
			}
			rs.close();
			pstmt.close();
		} catch (Exception e) {
			throw new Exception("操作中出现错误！！！");
		} finally {
			dbc.close();
		}

		return note;
	}

	// 查询全部
	@SuppressWarnings("unchecked")
	public List queryAll() throws Exception {
		List all = new ArrayList();
		String sql = "SELECT * FROM webnote order by addtime desc";
		PreparedStatement pstmt = null;
		DataBaseConnection dbc = null;
		dbc = new DataBaseConnection();
		try {
			pstmt = dbc.getConnection().prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				WebNote note = new WebNote();
				note = new WebNote();
				note.setId(rs.getInt(1));
				note.setTitle(rs.getString(2));
				note.setName(rs.getString(3));
				note.setQq((rs.getString(4)).trim());
				note.setEmail(rs.getString(5));
				note.setSex(rs.getString(6));
				note.setImage(rs.getString(7));
				note.setContent(rs.getString(8));
				note.setAddtime(rs.getString(9));
				all.add(note);
			}
			rs.close();
			pstmt.close();
		} catch (Exception e) {
			System.out.println(e);
			throw new Exception("操作中出现错误！！！");
		} finally {
			dbc.close();
		}
		return all;
	}

}