notedaoim.java

前台:文章浏览、发表留言、Game、Music 后台:文章相关:发表文章、修改文章、删除文章、批量移动文章 栏目相关:增加栏目、修改栏目、删除栏目、栏目链接、栏目排序系统栏目分为系统内部栏目和外部栏目

package com.yhcms.note.dao;

import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.yhcms.db.DBConnException;
import com.yhcms.db.DBConnect;
import com.yhcms.note.bean.Note;
import com.yhcms.note.bean.NoteDto;
import com.yhcms.note.itface.NoteDao;
import com.yhcms.utils.StringUtils;
/**
 * <p>Title:系统留言的相关操作</p>
 * <li>系统留言与数据库相关的各项操作</li>
 * <b>CopyRight: yyhweb[由由华网]</b>
 * @author stephen
 * @version YH-2.0
 */
public class NoteDaoIm implements NoteDao {
	
	private static Logger yhlog = Logger.getLogger(NoteDaoIm.class.getName());
	
	private DBConnect dbconn = null;
	
	private static NoteDaoIm notedao = new NoteDaoIm();
	
	/**
	 * @return 取得一个系统留言操作对象
	 */
	public static NoteDaoIm getInstance(){
		return notedao;
	}

	public boolean addNote(Note note) throws DBConnException {
		int i = 0;
		// 取得系统留言最大Id+1 作为新录入文章的Id
		int maxid = getNoteMaxId()+1;
		Note curNote = note;
		String author = curNote.getAuthor();
		String weburl = curNote.getWeburl();
		String comefrom = curNote.getComefrom();
		String email = curNote.getEmail();
		String qq = curNote.getQq();
		String title = curNote.getTitle();
		String content = curNote.getContent();
		String ptime = curNote.getPtime();
		String ip = curNote.getIp();
		String image = curNote.getImage();
		String answer = curNote.getAnswer();
		
		String sql = "insert into note(id,author,comefrom,weburl,email,qq,title,content,ptime,ip,image,answer)" +
					" values (?,?,?,?,?,?,?,?,?,?,?,?)";
//		String sql = "insert into note(id,author,comefrom,weburl,email,qq,title,content,ptime,ip,image,answer)" +
//		" values ("+ maxid+",'"+author+"','"+comefrom+"','"+weburl+"','"+email+"','"+qq+"','"+title+
//		"','"+content+"','"+ptime+"','"+ip+"','"+image+"','"+answer+"')";
		try{
			dbconn = new DBConnect(sql);
			dbconn.setInt(1, maxid);
			dbconn.setString(2, author);
			dbconn.setString(3, comefrom);
			dbconn.setString(4, weburl);
			dbconn.setString(5, email);
			dbconn.setString(6, qq);
			dbconn.setString(7, title);
			dbconn.setString(8, content);
			dbconn.setString(9, ptime);
			dbconn.setString(10, ip);
			dbconn.setString(11, image);
			dbconn.setString(12, answer);
			i = dbconn.executeUpdate();
		}catch(Exception e){
			yhlog.warn("When add a note,throw an Exception!");
		}finally{
			dbconn.close();
		}
		if(i>0){
			yhlog.info("Add a note successfully.the note id is:"+maxid+".");
			return true;
		}else{
			yhlog.info("Add a note unsuccessfully.");
			return false;
		}
	}


	public boolean answerNote(Note note) throws DBConnException {
		int i = 0;
		Note curNote = note;
		int id = curNote.getId();
		String author = curNote.getAuthor();
		String weburl = curNote.getWeburl();
		String comefrom = curNote.getComefrom();
		String email = curNote.getEmail();
		String qq = curNote.getQq();
		String title = curNote.getTitle();
		String content = curNote.getContent();
		String answer = curNote.getAnswer();
		
		String sql = "update note set author=?,weburl=?,comefrom=?,email=?,qq=?,title=?,content=?,answer=? " +
					 "where id="+id;
//		String sql = "update note set author='"+author+"',weburl='"+weburl+"',comefrom='"+comefrom+"',email='"+email+
//		"',qq='"+qq+"',title='"+title+"',content='"+content+"',answer='"+answer+"' where id="+id;
		try{
			dbconn = new DBConnect(sql);
			dbconn.setString(1, author);
			dbconn.setString(2, weburl);
			dbconn.setString(3, comefrom);
			dbconn.setString(4, email);
			dbconn.setString(5, qq);
			dbconn.setString(6, title);
			dbconn.setString(7, content);
			dbconn.setString(8, answer);
			
			i = dbconn.executeUpdate();
		}catch(Exception e){
			yhlog.warn("When answer a note,throw an Exception!The Note id is:"+id+".");
		}finally{
			dbconn.close();
		}
		if(i>0){
			yhlog.info("Answer a note successfully,the note id is:"+id+".");
			return true;
		}else{
			yhlog.info("Answer a note unsuccessfully!the note id is:"+id+".");
			return false;
		}
	}
	
	public boolean delNote(int id) throws DBConnException {
		int i = 0;
		String sql = "delete from note where id="+id;
		try{
			dbconn = new DBConnect(sql);
			i = dbconn.executeUpdate();
		}catch(Exception e){
			yhlog.warn("When delete a note,throw an Exception!The note id is:"+id+".");
		}finally{
			dbconn.close();
		}
		if(i>0){
			yhlog.info("Delete a note successfully,the note id is:"+id+".");
			return true;
		}else{
			yhlog.info("Delete a note unsuccessfully,the note id is:"+id+".");
			return false;
		}
	}

	public List getAllNotes(int begin, int size) throws DBConnException {
		ArrayList notelist = new ArrayList();
		Note curNote = null;
		String sql = "select n.id,n.author,n.comefrom,n.weburl,n.email,n.qq,n.title,n.content,n.ptime,n.ip," +
					"n.image,n.answer from note as n order by n.id desc limit "+begin+","+size;
		try{
			dbconn = new DBConnect(sql);
			dbconn.executeQuery();
			while(dbconn.next()){
				curNote = new Note();
				curNote.setId(dbconn.getInt(1));
				curNote.setAuthor(dbconn.getString(2));
				curNote.setComefrom(dbconn.getString(3));
				curNote.setWeburl(dbconn.getString(4));
				curNote.setEmail(dbconn.getString(5));
				curNote.setQq(dbconn.getString(6));
				curNote.setTitle(dbconn.getString(7));
				curNote.setContent(StringUtils.ubbEncode(dbconn.getString(8)));
				curNote.setPtime(dbconn.getString(9));
				curNote.setIp(dbconn.getString(10));
				curNote.setImage(dbconn.getString(11));
				curNote.setAnswer(dbconn.getString(12));
				
				notelist.add(curNote);
			}
		}catch(Exception e){
			e.printStackTrace();
			yhlog.warn("When get all notes of system,throw an Exception!");
		}finally{
			dbconn.close();
		}
		return notelist;
	}
	
	public List getAllNoteDto(int begin, int size) throws DBConnException {
		ArrayList notelist = new ArrayList();
		NoteDto curNote = null;
		String temp = "";
		String sql = "select n.id,n.author,n.title,n.content,n.answer,n.ptime from note as n order by n.id desc limit "+begin+","+size;
		try{
			dbconn = new DBConnect(sql);
			dbconn.executeQuery();
			while(dbconn.next()){
				curNote = new NoteDto();
				curNote.setId(dbconn.getInt(1));
				curNote.setAuthor(dbconn.getString(2));
				temp = dbconn.getString(3);
				if(temp.length()>15){
					curNote.setTitle(temp.substring(0,15)+"......");
				}else{
					curNote.setTitle(temp);
				}
				curNote.setContent(dbconn.getString(4));
				curNote.setAnswer(dbconn.getString(5));
				curNote.setPtime(dbconn.getString(6));
				
				notelist.add(curNote);
			}
		}catch(Exception e){
			e.printStackTrace();
			yhlog.warn("When get all notes of system,throw an Exception!");
		}finally{
			dbconn.close();
		}
		return notelist;
	}

	public Note getNoteById(int id) throws DBConnException {
		Note curNote = null;
		String sql = "select n.id,n.author,n.comefrom,n.weburl,n.email,n.qq,n.title,n.content,n.ptime,n.ip," +
					 "n.image,n.answer from note as n where id = "+id;
		try{
			dbconn = new DBConnect(sql);
			dbconn.executeQuery();
			if(dbconn.next()){
				curNote = new Note();
				curNote.setId(dbconn.getInt(1));
				curNote.setAuthor(dbconn.getString(2));
				curNote.setComefrom(dbconn.getString(3));
				curNote.setWeburl(dbconn.getString(4));
				curNote.setEmail(dbconn.getString(5));
				curNote.setQq(dbconn.getString(6));
				curNote.setTitle(dbconn.getString(7));
				curNote.setContent(StringUtils.ubbEncode(dbconn.getString(8)));
				curNote.setPtime(dbconn.getString(9));
				curNote.setIp(dbconn.getString(10));
				curNote.setImage(dbconn.getString(11));
				curNote.setAnswer(dbconn.getString(12));
			}
		}catch(Exception e){
			yhlog.warn("When get a note,throw an Exception!The note id is:"+id+".");
		}finally{
			dbconn.close();
		}
		return curNote;
	}

	public int getNoteMaxId() throws DBConnException {
		int maxId = 0;
		String sql = "select max(id) from note";
		try{
			dbconn = new DBConnect(sql);
			dbconn.executeQuery();
			if(dbconn.next()){
				maxId = dbconn.getInt(1);
			}
		}catch(Exception e){
			yhlog.warn("When get the max id of system note,throw an Exception!");
		}finally{
			dbconn.close();
		}
		return maxId;
	}

	public int getNoteNum() throws DBConnException {
		int num = 0;
		String sql = "select count(id) from note ";
		try{
			dbconn = new DBConnect(sql);
			dbconn.executeQuery();
			if(dbconn.next()){
				num = dbconn.getInt(1);
			}
		}catch(Exception e){
			yhlog.warn("When get note count of system note,throw an Exception!");
		}finally{
			dbconn.close();
		}
		return num;
	}

	public List getNewNotes(int length) throws DBConnException {
		ArrayList notelist = new ArrayList();
		NoteDto curNote = null;
		String sql = "select n.id,n.author,n.title,n.ptime from note as n " +
					 " order by n.id desc limit "+length;
		try{
			dbconn = new DBConnect(sql);
			dbconn.executeQuery();
			while(dbconn.next()){
				curNote = new NoteDto();
				curNote.setId(dbconn.getInt(1));
				curNote.setAuthor(dbconn.getString(2));
				curNote.setTitle(dbconn.getString(3));
				curNote.setPtime(dbconn.getString(4));
				
				notelist.add(curNote);
			}
		}catch(Exception e){
			e.printStackTrace();
			yhlog.warn("When get new notes of system,throw an Exception!");
		}finally{
			dbconn.close();
		}
		return notelist;
	}

}