newsattachmentdaoimpl.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.ArrayList;
import java.util.List;

import com.mycompany.database.Database;
import com.mycompany.news.dao.NewsAttachmentDAO;
import com.mycompany.news.dto.News;
import com.mycompany.news.dto.NewsAttachment;
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 NewsAttachmentDAOImpl implements NewsAttachmentDAO{
	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 addNewsAttachment(NewsAttachment newsAttachment) throws SQLException{
		String sql="insert into News_Attachment(news_id,attachment_name,attachment_content) values(?,?,?)";
		PreparedStatement ps = connection.prepareStatement(sql);
		int i=1;
		ps.setLong(i++,newsAttachment.getNewsId().longValue());
		ps.setString(i++,newsAttachment.getAttachmentName());
		ps.setBytes(i++,newsAttachment.getAttachmentContent());
		ps.executeUpdate();
		ps.close();		
	}
	
	public void updateNewsAttachment(NewsAttachment newsAttachment)throws Exception{
		List NewsAttachments = new ArrayList();
		NewsAttachments.add("attachment_Name");
		NewsAttachments.add("attachment_Order");
		NewsAttachments.add("attachment_Status");
		StringBuffer sqlString=new StringBuffer();
		sqlString.append("update NewsAttachment set ");
		for(int i=0;i<NewsAttachments.size();i++){
			if(NewsAttachments.size()-1==i)
				sqlString.append(NewsAttachments.get(i)+"=? ");
			else
				sqlString.append(NewsAttachments.get(i)+"=?, ");
		}
		sqlString.append(" where Attachment_id=?");
		System.out.println(sqlString);
		PreparedStatement ps = connection.prepareStatement(sqlString.toString());
		for(int i=0;i<NewsAttachments.size();i++){
			ps.setObject(i+1,BeanUtils.getValue(newsAttachment,((String)NewsAttachments.get(i)).replaceAll("_","")));
		}
		ps.setLong(NewsAttachments.size()+1,newsAttachment.getAttachmentId().longValue());
		ps.executeUpdate();
		ps.close();
	}
	public void deleteNewsAttachment(NewsAttachment newsAttachment)throws Exception{
		String sqlString="delete from  News_Attachment where Attachment_id=?";
		PreparedStatement ps = connection.prepareStatement(sqlString);
		ps.setLong(1,newsAttachment.getAttachmentId().longValue());
		ps.executeUpdate();
		ps.close();
	}
	public List listAllNewsAttachments() throws Exception{
		PreparedStatement ps = connection.prepareStatement("select * from News_Attachment");
		ResultSet rs = ps.executeQuery();
		List list = DTOPopulator.populate(rs,NewsAttachment.class);
		for(int i=0;i<list.size();i++){
			NewsAttachment c = (NewsAttachment) list.get(i);
		}
		rs.close();
		ps.close();
		return list;
	}
	public List listNewsAttachments(NewsAttachment newsAttachmentCondition)throws Exception{

		boolean newsCondition=false;
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append("select * from News_Attachment where 1=1");
		if(newsAttachmentCondition.getNewsId()!=null){
			stringBuffer.append(" and news_id=?");
			newsCondition=true;
		}
		
		PreparedStatement ps = connection.prepareStatement(stringBuffer.toString());
		int i=1;
		if(newsCondition){
			ps.setLong(i++,newsAttachmentCondition.getNewsId().longValue());
		}
		ResultSet rs = ps.executeQuery();
		List list = new ArrayList();
		while(rs.next()){
			NewsAttachment attachment = new NewsAttachment();
			attachment.setAttachmentId(new Long(rs.getLong("attachment_id")));
			attachment.setAttachmentName(rs.getString("attachment_name"));
//			attachment.setAttContent(rs.getBytes("attachment_conntent"));
			attachment.setNewsId(newsAttachmentCondition.getNewsId());
			list.add(attachment);
		}
		return list;
	}
	public NewsAttachment getByID(long id)throws Exception{
		PreparedStatement ps = connection.prepareStatement("select * from news_attachment where attachment_id=?");
		ps.setLong(1,id);
		ResultSet rs = ps.executeQuery();
//		while(rs.next()){
//			NewsAttachment att = new NewsAttachment();
//			att.setAttachmentName(rs.getString("attachment_name"));
//			att.setAttachmentId(new Long(id));
//			att.setAttachmentContent(rs.getBytes("attachment_content"));
//		}
		return (NewsAttachment) DTOPopulator.populate(rs,NewsAttachment.class).get(0);
	}
	public static void main(String[] args) {
		NewsAttachmentDAO dao = new NewsAttachmentDAOImpl();
		Connection conn =null;
		try {
			conn = Database.getConnection();
			dao.setConnection(conn);
			String s = "fffaaaaffffddddabcdefg";
			StringBuffer sb = new StringBuffer();
			for(int i=0;i<100000;i++){
				sb.append(s);
			}
			NewsAttachment newsAttachment=new NewsAttachment();
			newsAttachment.setAttachmentName("测试附件");
			newsAttachment.setAttachmentContent(sb.toString().getBytes());
			newsAttachment.setNewsId(new Long(22));
			dao.addNewsAttachment(newsAttachment);
//			dao.updateNewsAttachment(newsAttachment);
			Database.commit();
//			dao.listAllNewsAttachments();
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			Database.releaseConnection(conn);
		}
	}
	/* (non-Javadoc)
	 * @see com.mycompany.NewsAttachment.dao.NewsAttachmentDAO#getRecommendedNewsAttachment(int)
	 */
	public List getRecommendedNewsAttachment(long columnid) throws Exception {
		// TODO Auto-generated method stub
		String sqlStr=" select ni.* from News_Attachment_Info ni left join NewsAttachment_attribute na on ni.NewsAttachment_id=na.NewsAttachment_id where na.NewsAttachment_attr_name=? and na.NewsAttachment_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,NewsAttachment.class);
	}
	/* (non-Javadoc)
	 * @see com.mycompany.news.dao.NewsAttachmentDAO#deleteAttrByNews(com.mycompany.news.dto.News)
	 */
	public void deleteAttrByNews(News news) throws Exception {
		// TODO Auto-generated method stub
		String sqlString="delete from News_Attachment where news_id=?";
		PreparedStatement ps = connection.prepareStatement(sqlString);
		ps.setLong(1,news.getNewsId().longValue());
		ps.executeUpdate();
		ps.close();		
	}

}