channeldaoimpl.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.ChannelDAO;
import com.mycompany.news.dto.Channel;
import com.mycompany.news.dto.Column;
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 ChannelDAOImpl implements ChannelDAO{
	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 addChannel(Channel channel) throws SQLException{
		String sql="insert into Channel(channel_Name,channel_Order,channel_Status) values(?,?,?)";			
		PreparedStatement ps = connection.prepareStatement(sql);
		int i=1;
		ps.setString(i++,channel.getChannelName());
		ps.setInt(i++,channel.getChannelOrder().intValue());
		ps.setInt(i++,channel.getChannelStatus().intValue());
		ps.executeUpdate();
		ps.close();		
	}
	
	public void updateChannel(Channel channel)throws Exception{
		List columns = new ArrayList();
		columns.add("channel_Name");
		columns.add("channel_Order");
		columns.add("channel_Status");
		StringBuffer sqlString=new StringBuffer();
		sqlString.append("update Channel set ");
		for(int i=0;i<columns.size();i++){
			if(columns.size()-1==i)
				sqlString.append(columns.get(i)+"=? ");
			else
				sqlString.append(columns.get(i)+"=?, ");
		}
		sqlString.append(" where channel_id=?");
		System.out.println(sqlString);
		PreparedStatement ps = connection.prepareStatement(sqlString.toString());
		for(int i=0;i<columns.size();i++){
			ps.setObject(i+1,BeanUtils.getValue(channel,((String)columns.get(i)).replaceAll("_","")));
		}
		ps.setLong(columns.size()+1,channel.getChannelID().longValue());
		ps.executeUpdate();
		ps.close();
	}
	public void deleteChannel(Channel channel)throws Exception{
		String sqlString="update Channel set channel_status=? where channel_id=?";
		PreparedStatement ps = connection.prepareStatement(sqlString);
		ps.setInt(1,-1);
		System.out.println("channel.getChannelID() = "+channel.getChannelID());
		ps.setLong(2,channel.getChannelID().longValue());
		ps.executeUpdate();
		ps.close();
	}
	public List listAllChannels() throws Exception{
		PreparedStatement ps = connection.prepareStatement("select * from channel where channel_status>=0 order by channel_order");
		ResultSet rs = ps.executeQuery();
		List list = DTOPopulator.populate(rs,Channel.class);
		rs.close();
		ps.close();
		return list;
	}
	public List listChannels(Channel channelCondition)throws Exception{

		boolean nameCondition=false;
		boolean statusCondition=false;
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append("select * from channel where channel_status>=0");
		if(channelCondition.getChannelName()!=null&&!channelCondition.getChannelName().trim().equals("")){
			stringBuffer.append("and channel_name like ?");
			nameCondition = true;
		}
		if(channelCondition.getChannelStatus()!=null){
			stringBuffer.append("and channel_status =?");
			statusCondition= true;
		}
		PreparedStatement ps = connection.prepareStatement(channelCondition.toString());
		int i=1;
		if(nameCondition)
			ps.setString(i++,"%"+channelCondition.getChannelName().trim()+"%");
		if(statusCondition)
			ps.setInt(i++,channelCondition.getChannelStatus().intValue());
		ResultSet rs = ps.executeQuery();
		return DTOPopulator.populate(rs,Channel.class);
	}
	public Channel getByID(long id)throws Exception{
		PreparedStatement ps = connection.prepareStatement("select * from channel where channel_id=?");
		ps.setLong(1,id);
		ResultSet rs = ps.executeQuery();
		return (Channel) DTOPopulator.populate(rs,Channel.class).get(0);
	}
	public List getChildren(Long channelID) throws Exception {
		PreparedStatement ps = connection.prepareStatement("select * from news_column n where n.channel_Id=? and n.current_level=0 order by n.column_order");
		ps.setLong(1,channelID.longValue());
		ResultSet rs = ps.executeQuery();
		return DTOPopulator.populate(rs,Column.class);
		
	}
	public static void main(String[] args) {
		ChannelDAO dao = new ChannelDAOImpl();
		Connection conn =null;
		try {
			conn = Database.getConnection();
			dao.setConnection(conn);
			Channel channel=new Channel();
			channel.setChannelID(new Long(2));
			channel.setChannelName("国际新闻1");
			channel.setChannelOrder(new Integer(13));
			channel.setChannelStatus(new Integer(1));
//			dao.addChannel(channel);
			dao.updateChannel(channel);
			Database.commit();
			dao.listAllChannels();
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			Database.releaseConnection(conn);
		}
	}
	/* (non-Javadoc)
	 * @see com.mycompany.news.dao.ChannelDAO#getChildren(java.lang.Long)
	 */

}