newsattributedaoimpl.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.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.mycompany.database.Database;
import com.mycompany.news.dao.NewsAttributeDAO;
import com.mycompany.news.dto.News;
import com.mycompany.news.dto.NewsAttribute;
import com.mycompany.tools.DTOPopulator;

/**
 * @author Administrator
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class NewsAttributeDAOImpl implements NewsAttributeDAO{
	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 News getByID(long id)throws Exception{
		PreparedStatement ps = connection.prepareStatement("select * from news_attribute where entity_id=?");
		ps.setLong(1,id);
		ResultSet rs = ps.executeQuery();
		return (News) DTOPopulator.populate(rs,NewsAttribute.class).get(0);
	}
	public String getAttributeValue(long entityid,String attributeName,int attrType) throws SQLException{
		PreparedStatement ps = connection.prepareStatement("select news_attr_value from news_attribute where entity_id=? and news_attr_name=? and news_attr_type=?");
		int i=1;
		ps.setLong(i++,entityid);
		ps.setString(i++,attributeName);
		ps.setInt(i++,attrType);
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
			return rs.getString("news_attr_value");
		}
		return null;
		
	}


	public List getRecommendedNews(long columnid) throws Exception {
		// TODO Auto-generated method stub
		String sqlStr=" select ni.* from News_Info ni left join news_attribute na on ni.news_id=na.entity_id where na.news_attr_name=? and na.news_attr_value=? and ni.column_id=?";
		PreparedStatement ps = connection.prepareStatement(sqlStr);
		ps.setString(1,"is_recommend");
		ps.setString(2,"true");
		ps.setLong(3,columnid);
		ResultSet rs = ps.executeQuery();
		return DTOPopulator.populate(rs,News.class);
	}

	public synchronized Long getCurrentID() throws SQLException {
		PreparedStatement preparedStatement = connection.prepareStatement("select max(ni.news_attribute_id) as m from news_attribute ni");
		ResultSet resultSet = preparedStatement.executeQuery();
		if(resultSet.next()){
		long currentID = resultSet.getLong("m");
		return new Long(currentID);
		}
		return null;
	}


	public void recommendNews(long news) throws Exception {
		String sqlStr="insert into news_attribute(news_attr_type,news_attr_name, news_attr_value,entity_id) values(?,?,?,?)";		
		PreparedStatement ps = connection.prepareStatement(sqlStr);
		int i=1;
		ps.setInt(i++,0);
		ps.setString(i++,"is_recommend");
		ps.setString(i++,"true");
		ps.setLong(i++,news);
		ps.executeUpdate();				
	}


	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();				
	}
	public static void main(String[] args) {
		NewsAttributeDAO dao = new NewsAttributeDAOImpl();
		Connection conn =null;
		try{
			conn = Database.getConnection();
			dao.setConnection(conn);
			String attributeValue = dao.getAttributeValue(13,"visit_count",0);
			System.out.println("attributeValue = "+attributeValue);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Database.releaseConnection(conn);
		}
	}
	protected void deleteAttribute(Long entityId,int entityType) throws SQLException{
		String sqlString="delete from News_Attribute where entity_id=? and news_attr_type=?";
		PreparedStatement ps = connection.prepareStatement(sqlString);
		ps.setLong(1,entityId.longValue());
		ps.setInt(2,entityType);
		ps.executeUpdate();
		ps.close();				

	}
	/* (non-Javadoc)
	 * @see com.mycompany.news.dao.NewsAttributeDAO#deleteByNews(com.mycompany.news.dto.News)
	 */
	public void deleteByNews(News news)throws Exception {
		// TODO Auto-generated method stub
		deleteAttribute(news.getNewsId(),0);
	}
}