package?com.java1234.dao;

import?java.sql.Connection;
import?java.sql.PreparedStatement;
import?java.sql.ResultSet;
import?java.util.ArrayList;
import?java.util.List;

import?com.java1234.model.Diary;
import?com.java1234.model.PageBean;
import?com.java1234.util.DateUtil;
import?com.java1234.util.StringUtil;

public?class?DiaryDao?{

	public?List?diaryList（Connection?conPageBean?pageBeanDiary?s_diary）throws?Exception{
		List?diaryList=new?ArrayList（）;
		StringBuffer?sb=new?StringBuffer（“select?*?from?t_diary?t1t_diaryType?t2?where?t1.typeId=t2.diaryTypeId?“）;
		if（StringUtil.isNotEmpty（s_diary.gettitle（）））{
			sb.append（“?and?t1.title?like?‘%“+s_diary.gettitle（）+“%‘“）;
		}
		if（s_diary.getTypeId（）!=-1）{
			sb.append（“?and?t1.typeId=“+s_diary.getTypeId（））;
		}
		if（StringUtil.isNotEmpty（s_diary.getReleaseDateStr（）））{
			sb.append（“?and?DATE_FORMAT（t1.releaseDate‘%Y年%m月‘）=‘“+s_diary.getReleaseDateStr（）+“‘“）;
		}
		sb.append（“?order?by?t1.releaseDate?desc“）;
		if（pageBean!=null）{
			sb.append（“?limit?“+pageBean.getStart（）+““+pageBean.getPageSize（））;
		}
		PreparedStatement?pstmt=con.prepareStatement（sb.toString（））;
		ResultSet?rs=pstmt.executeQuery（）;
		while（rs.next（））{
			Diary?diary=new?Diary（）;
			diary.setDiaryId（rs.getInt（“diaryId“））;
			diary.settitle（rs.getString（“title“））;
			diary.setContent（rs.getString（“content“））;
			diary.setReleaseDate（DateUtil.formatString（rs.getString（“releaseDate“）?“yyyy-MM-dd?HH:mm:ss“））;
			diaryList.add（diary）;
		}
		return?diaryList;
	}
	
	public?int?diaryCount（Connection?conDiary?s_diary）throws?Exception{
		StringBuffer?sb=new?StringBuffer（“select?count（*）?as?total?from?t_diary?t1t_diaryType?t2?where?t1.typeId=t2.diaryTypeId?“）;
		if（StringUtil.isNotEmpty（s_diary.gettitle（）））{
			sb.append（“?and?t1.title?like?‘%“+s_diary.gettitle（）+“%‘“）;
		}
		if（s_diary.getTypeId（）!=-1）{
			sb.append（“?and?t1.typeId=“+s_diary.getTypeId（））;
		}
		if（StringUtil.isNotEmpty（s_diary.getReleaseDateStr（）））{
			sb.append（“?and?DATE_FORMAT（t1.releaseDate‘%Y年%m月‘）=‘“+s_diary.getReleaseDateStr（）+“‘“）;
		}
		PreparedStatement?pstmt=con.prepareStatement（sb.toString（））;
		ResultSet?rs=pstmt.executeQuery（）;
		if（rs.next（））{
			return?rs.getInt（“total“）;
		}else{
			return?0;
		}
	}
	
	public?List?diaryCountList（Connection?con）throws?Exception{
		List?diaryCountList=new?ArrayList（）;
		String?sql=“SELECT?DATE_FORMAT（releaseDate‘%Y年%m月‘）?as?releaseDateStr?COUNT（*）?AS?diaryCount??FROM?t_diary?GROUP?BY?DATE_FORMAT（releaseDate‘%Y年%m月‘）?ORDER?BY?DATE_FORMAT（releaseDate‘%Y年%m月‘）?DESC;“;
		PreparedStatement?pstmt=con.prepareStatement（sql）;
		ResultSet?rs=pstmt.executeQuery（）;
		while（rs.next（））{
			Diary?diary=new?Diary（）;
			diary.setReleaseDateStr（rs.getString（“releaseDateStr“））;
			diary.setDiaryCount（rs.getInt（“diaryCount“））;
			diaryCountList.add（diary）;
		}
		return?diaryCountList;
	}
	
	public?Diary?diaryShow（C