notedaoimpl.java

DAO实现留言板

package com.commit.dao.daoimpl;

import java.sql.*;
import java.util.*;
import java.sql.*;

import com.commit.dao.dao.*;
import com.commit.dao.dbc.*;
import com.commit.dao.vo.*;

public class NoteDAOImpl implements NoteDAO 
{

	public void addNotes(NoteVo nv) throws Exception 
	{
		// TODO Auto-generated method stub
		PreparedStatement pstmt = null ;
		String sql = null ;
		DataBaseConnection dbc = null ;
		//connect to database
		dbc = new DataBaseConnection() ;
		//init sql
		sql = "insert into note (author,title,content) values (?,?,?)" ;
		try
		{
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			pstmt.setString(1, nv.getAuthor()) ;
			pstmt.setString(2, nv.getTitle()) ;
			pstmt.setString(3, nv.getContent()) ;
			pstmt.executeUpdate() ;
			
			pstmt.close() ;
		}
		catch(Exception e)
		{}
		finally
		{
			dbc.close() ;
		}
	}

	public void deleteNotes(int id) throws Exception 
	{
		// TODO Auto-generated method stub
		PreparedStatement pstmt = null ;
		String sql = null ;
		DataBaseConnection dbc = null ;
		//connect to database
		dbc = new DataBaseConnection() ;
		//init sql
		sql = "delete from note where id=?" ;
		try
		{
			pstmt = dbc.getConnection().prepareStatement(sql) ;	
			pstmt.setInt(1,id) ;
			pstmt.executeUpdate() ;
			pstmt.close() ;
		}
		catch(Exception e)
		{}
		finally
		{
			dbc.close() ;
		}
	}

	public List listAll() throws Exception 
	{
		// TODO Auto-generated method stub
		List all = new ArrayList() ;
		NoteVo nv = null ;
		PreparedStatement pstmt = null ;
		ResultSet rs = null ;
		String sql = null ;
		DataBaseConnection dbc = null ;
		//connect to database
		dbc = new DataBaseConnection() ;
		//init sql
		sql = "select id,author,title,content from note" ;
		try
		{
			pstmt = dbc.getConnection().prepareStatement(sql) ;	
			rs = pstmt.executeQuery() ;
			while(rs.next())
			{
				nv = new NoteVo() ;
				nv.setId(rs.getInt(1)) ;
				nv.setAuthor(rs.getString(2)) ;
				nv.setTitle(rs.getString(3)) ;
				nv.setContent(rs.getString(4)) ;
				
				all.add(nv) ;
			}
			rs.close() ;
			pstmt.close() ;
		}
		catch(Exception e)
		{}
		finally
		{
			dbc.close() ;
		}
		return all ;
	}

	public NoteVo listById(int id) throws Exception 
	{
		// TODO Auto-generated method stub
		NoteVo nv = null ;
		PreparedStatement pstmt = null ;
		ResultSet rs = null ;
		String sql = null ;
		DataBaseConnection dbc = null ;
		//connect to database
		dbc = new DataBaseConnection() ;
		//init sql
		sql = "select id,author,title,content from note where id=?" ;
		try
		{
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			pstmt.setInt(1, id) ;
			rs = pstmt.executeQuery() ;
			while(rs.next())
			{
				nv = new NoteVo() ;
				nv.setId(rs.getInt(1)) ;
				nv.setAuthor(rs.getString(2)) ;
				nv.setTitle(rs.getString(3)) ;
				nv.setContent(rs.getString(4)) ;
			}
			rs.close();
			pstmt.close() ;
		}
		catch(Exception e)
		{}
		finally
		{
			dbc.close() ;
		}
		return nv ;
	}

	public List listByLike(String keywords) throws Exception 
	{
		// TODO Auto-generated method stub
		List all = new ArrayList() ;
		NoteVo nv = null ;
		PreparedStatement pstmt = null ;
		ResultSet rs = null ;
		String sql = null ;
		DataBaseConnection dbc = null ;
		//connect to database
		dbc = new DataBaseConnection() ;
		//init sql
		sql = "select id,author,title,content from note where author like ? or title like ? or content like ?" ;
		try
		{
			pstmt = dbc.getConnection().prepareStatement(sql) ;	
			pstmt.setString(1, "%"+keywords+"%") ;
			pstmt.setString(2,"%"+keywords+"%") ;
			pstmt.setString(3,"%"+keywords+"%") ;
			rs = pstmt.executeQuery() ;
			while(rs.next())
			{
				nv = new NoteVo() ;
				nv.setId(rs.getInt(1)) ;
				nv.setAuthor(rs.getString(2)) ;
				nv.setTitle(rs.getString(3)) ;
				nv.setContent(rs.getString(4)) ;
				
				all.add(nv) ;
			}
			rs.close() ;
			pstmt.close() ;
		}
		catch(Exception e)
		{}
		finally
		{
			dbc.close() ;
		}
		return all ;
	}

	public void updateNotes(NoteVo nv) throws Exception 
	{
		// TODO Auto-generated method stub
		PreparedStatement pstmt = null ;
		String sql = null ;
		DataBaseConnection dbc = null ;
		//connect to database
		dbc = new DataBaseConnection() ;
		//init sql
		sql = "update note set author=?,title=?,content=? where id=?" ;
		try
		{
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			pstmt.setString(1, nv.getAuthor()) ;
			pstmt.setString(2, nv.getTitle()) ;
			pstmt.setString(3, nv.getContent()) ;
			pstmt.setInt(4, nv.getId()) ;
			pstmt.executeUpdate() ;
			
			pstmt.close() ;
		}
		catch(Exception e)
		{}
		finally
		{
			dbc.close() ;
		}
	}
}