notedaoimpl.java

一个简单的留言网上留言系统

package cn.li.note.dao.impl;

import java.util.*;
import java.sql.*;
import cn.li.note.vo.*;
import cn.li.note.dao.*;
import cn.li.note.dbc.*;

public class NoteDAOImpl implements NoteDAO
{
	public void insert(Note note)throws Exception
	{
		String sql="INSERT INTO note(id,title,author,content) VALUES(null,?,?,?)";
		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);	
		}
		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();	
		}
		
	}
	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("根据id查询失败");	
		}
		finally
		{
		 dbc.close();	
		}
		return note;
	}
	public List queryAll()throws Exception
	{
			List all=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));
			 	 all.add(note);
			 	}
			 	rs.close();
				pstmt.close();
			}
		catch(Exception e)
		{
			throw new Exception("查询全部失败");	
		}
		finally
		{
		 dbc.close();	
		}
		return all;
		
	}
	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)
		{
			throw new Exception("模糊查询失败");	
		}
		finally
		{
		 dbc.close();	
		}
		return all;
		
		}
}