package cn.mldn.lxh.note.dao.impl ;

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

public class NoteDAOImpl implements NoteDAO
{
	// 增加操作
	public void insert(Note note) throws Exception
	{
		String sql = "INSERT INTO note(id,title,author,content) VALUES(note_sequ.nextVal,?,?,?)" ;
		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("操作中出現(xiàn)錯誤！！！") ;
		}
		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("操作中出現(xiàn)錯誤！！！") ;
		}
		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("操作中出現(xiàn)錯誤！！！") ;
		}
		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("操作中出現(xiàn)錯誤！！！") ;
		}
		finally
		{
			dbc.close() ;
		}
		return note ;
	}
	// 查詢?nèi)?	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)
		{
			System.out.println(e) ;
			throw new Exception("操作中出現(xiàn)錯誤！！！") ;
		}
		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)
		{
			System.out.println(e) ;
			throw new Exception("操作中出現(xiàn)錯誤！！！") ;
		}
		finally
		{
			dbc.close() ;
		}
		return all ;
	}
};