newsdaoimpl.java

jsp+servlet实现的标准MVC系统 jsp-servlet-service-DAO-DBMS

/*
 * Created on 2005-11-10
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package com.mycompany.news.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mycompany.database.Database;
import com.mycompany.news.dao.NewsAttributeDAO;
import com.mycompany.news.dao.NewsDAO;
import com.mycompany.news.dto.News;
import com.mycompany.tools.DTOPopulator;
import com.opensymphony.util.BeanUtils;

/**
 * @author Administrator
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class NewsDAOImpl implements NewsDAO{
	Connection connection = null;
	/**
	 * @return Returns the connection.
	 */
	public Connection getConnection() {
		return connection;
	}
	/**
	 * @param connection The connection to set.
	 */
	public void setConnection(Connection connection) {
		this.connection = connection;
	}
	public void addNews(News news) throws SQLException{
		String sql="insert into News_Info(column_id,subject,create_time,show_time,content,author,news_Status) values(?,?,?,?,?,?,?)";			
		PreparedStatement ps = connection.prepareStatement(sql);
		int i=1;
		ps.setLong(i++,news.getColumnId().longValue());
		ps.setString(i++,news.getSubject());
		ps.setDate(i++,new Date(new java.util.Date().getTime()));
		ps.setDate(i++,new Date(news.getShowTime().getTime()));
		ps.setString(i++,news.getContent());
		ps.setString(i++,news.getAuthor());
		ps.setInt(i++,news.getNewsStatus().intValue());
		ps.executeUpdate();
		ps.close();		
	}
	
	public void updateNews(News news)throws Exception{
		List Newss = new ArrayList();
		Newss.add("subject");
		Newss.add("show_Time");
		Newss.add("content");
		Newss.add("author");
		StringBuffer sqlString=new StringBuffer();
		sqlString.append("update News_Info set ");
		for(int i=0;i<Newss.size();i++){
			if(Newss.size()-1==i)
				sqlString.append(Newss.get(i)+"=? ");
			else
				sqlString.append(Newss.get(i)+"=?, ");
		}
		sqlString.append(" where News_id=?");
		System.out.println(sqlString);
		PreparedStatement ps = connection.prepareStatement(sqlString.toString());
		for(int i=0;i<Newss.size();i++){
			Object value = BeanUtils.getValue(news,((String)Newss.get(i)).replaceAll("_",""));
//			if(value instanceof java.util.Date){
//				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//				value = sdf.format(value);
//				System.out.println("value = "+value);
//			}
			
			ps.setObject(i+1,value);
		}
		ps.setLong(Newss.size()+1,news.getNewsId().longValue());
		ps.executeUpdate();
		ps.close();
	}
	public void deleteNews(News news)throws Exception{
		String sqlString="delete from News_Info where News_id=?";
		PreparedStatement ps = connection.prepareStatement(sqlString);
		ps.setLong(1,news.getNewsId().longValue());
		ps.executeUpdate();
		ps.close();
	}
	public List listAllNews() throws Exception{
		PreparedStatement ps = connection.prepareStatement("select * from News_Info order by show_time desc");
		ResultSet rs = ps.executeQuery();
		List list = DTOPopulator.populate(rs,News.class);
		rs.close();
		ps.close();
		return list;
	}
	public List listNews(News newsCondition,int curPage,int perpage)throws Exception{
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append("select * from news_info where 1=1 ");
		if(newsCondition.getColumnId()!=null){
			stringBuffer.append(" and column_id=? ");
		}
		if(newsCondition.getSubject()!=null){
			stringBuffer.append(" and subject like ? ");
		}
		stringBuffer.append(" order by show_time desc limit ?,?");
		PreparedStatement ps = connection.prepareStatement(stringBuffer.toString());
		int i=1;
		if(newsCondition.getColumnId()!=null){
			ps.setLong(i++,newsCondition.getColumnId().longValue());
		}
		if(newsCondition.getSubject()!=null){
			ps.setString(i++,"%"+newsCondition.getSubject()+"%");
		}			
		ps.setInt(i++,(curPage-1)*perpage);
		ps.setInt(i++,perpage);
		ResultSet rs = ps.executeQuery();
		return DTOPopulator.populate(rs,News.class);
	}
	public News getByID(long id)throws Exception{
		PreparedStatement ps = connection.prepareStatement("select * from news_info where news_id=?");
		ps.setLong(1,id);
		ResultSet rs = ps.executeQuery();
		return (News) DTOPopulator.populate(rs,News.class).get(0);
	}
	/* (non-Javadoc)
	 * @see com.mycompany.news.dao.NewsDAO#getRecommendedNews(int)
	 */
	public List getRecommendedNews(long channelid,int curPage,int max) throws Exception {
		// TODO Auto-generated method stub
		String sqlStr=" select ni.* from News_Info ni " +
				" join news_attribute na on ni.news_id=na.entity_id " +
				" join news_column nc on ni.column_id=nc.column_id" +
				" join channel ch on nc.channel_id=ch.channel_id" +
				" where na.news_attr_type=0 and na.news_attr_name=? and na.news_attr_value=?  and na.news_attr_type=? and ch.channel_id=? order by ni.show_time desc limit ?,?";
		PreparedStatement ps = connection.prepareStatement(sqlStr);
		int i=1;
		ps.setString(i++,"is_recommend");
		ps.setString(i++,"true");
		ps.setInt(i++,0);
		ps.setLong(i++,channelid);
		ps.setInt(i++,(curPage-1)*max);
		ps.setInt(i++,max);
		ResultSet rs = ps.executeQuery();
		return DTOPopulator.populate(rs,News.class);
	}
	public int getRecommendedNewsCount(long channelid) throws Exception {
		// TODO Auto-generated method stub
		String sqlStr=" select count(*) as count from News_Info ni " +
				" join news_attribute na on ni.news_id=na.entity_id " +
				" join news_column nc on ni.column_id=nc.column_id" +
				" join channel ch on nc.channel_id=ch.channel_id" +
				" where na.news_attr_type=0 and na.news_attr_name=? and na.news_attr_value=?  and na.news_attr_type=? and ch.channel_id=?";
		PreparedStatement ps = connection.prepareStatement(sqlStr);
		int i=1;
		ps.setString(i++,"is_recommend");
		ps.setString(i++,"true");
		ps.setInt(i++,1);
		ps.setLong(i++,channelid);
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
			return rs.getInt("count");
		}
		return 0;
	}
	/* (non-Javadoc)
	 * @see com.mycompany.news.dao.NewsDAO#getCurrentID()
	 */
	public synchronized Long getCurrentID() throws SQLException {
		PreparedStatement preparedStatement = connection.prepareStatement("select max(ni.news_id) as m from news_info ni");
		ResultSet resultSet = preparedStatement.executeQuery();
		if(resultSet.next()){
		long currentID = resultSet.getLong("m");
		return new Long(currentID);
		}
		return null;
	}
	/* (non-Javadoc)
	 * @see com.mycompany.news.dao.NewsDAO#recommendNews(long)
	 */
	public void recommendNews(long news) throws Exception {
		String sqlStr="insert into news_attribute(news_attr_name, news_attr_value,entity_id,news_attr_type) values(?,?,?,?)";		
		PreparedStatement ps = connection.prepareStatement(sqlStr);
		int i=1;
		ps.setString(i++,"is_recommend");
		ps.setString(i++,"true");
		ps.setLong(i++,news);
		ps.setInt(i++,0);
		ps.executeUpdate();				
	}
	/* (non-Javadoc)
	 * @see com.mycompany.news.dao.NewsDAO#cancelRecommend(long)
	 */
	public void cancelRecommend(long news) throws Exception {
		String sqlStr="delete from news_attribute where news_attr_name=? and news_attr_value=? and entity_id=?";		
		PreparedStatement ps = connection.prepareStatement(sqlStr);
		int i=1;
		ps.setString(i++,"is_recommend");
		ps.setString(i++,"true");
		ps.setLong(i++,news);
		ps.executeUpdate();				
	}
	/* (non-Javadoc)
	 * @see com.mycompany.news.dao.NewsDAO#updateVisitCount()
	 */
	public void updateVisitCount(long newsid) throws Exception {
		NewsAttributeDAO attrDAO = new NewsAttributeDAOImpl();
		String updateSQL="update news_attribute set news_attr_value=news_attr_value+'1' where news_attr_name=? and entity_id=? and news_attr_type=?";
		String insertSQL="insert into news_attribute(news_attr_name,news_attr_value,entity_id,news_attr_type) values(?,?,?,?)";
		attrDAO.setConnection(connection);
		String visitCount = attrDAO.getAttributeValue(newsid,"visit_count",0);
		PreparedStatement ps =null;
		if(visitCount==null){
			ps= connection.prepareStatement(insertSQL);
			int i=1;
			ps.setString(i++,"visit_count");
			ps.setString(i++,"1");
			ps.setLong(i++,newsid);
			ps.setInt(i++,0);
		}else{
			ps= connection.prepareStatement(updateSQL);
			int i=1;
			ps.setString(i++,"visit_count");
			ps.setLong(i++,newsid);
			ps.setInt(i++,0);
		}
		int i = ps.executeUpdate();
		System.out.println(i);
	}
	public static void main(String[] args) {
		NewsDAO dao = new NewsDAOImpl();
		Connection conn=null;
		try{
			conn = Database.getConnection();
		dao.setConnection(conn);
		dao.updateVisitCount(13);
		Database.commit();
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			Database.releaseConnection(conn);
		}
	}

}