columndaoimpl.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.ColumnDAO;
import com.mycompany.news.dto.Column;
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 ColumnDAOImpl implements ColumnDAO{
	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 addColumn(Column column) throws SQLException{
		String sql="insert into News_Column (" +
				"Column_Name," +
				"Column_Order," +
				"Column_Status," +
				"Channel_id," +
				"Parent_Column," +
				"Top_Column," +
				"current_level," +
				"leaf_Node) values(?,?,?,?,?,?,?,?)";			
		PreparedStatement ps = connection.prepareStatement(sql);
		int i=1;
		ps.setString(i++,column.getColumnName());
		ps.setInt(i++,column.getColumnOrder().intValue());
		ps.setInt(i++,column.getColumnStatus().intValue());
		ps.setLong(i++,column.getChannelId().longValue());
		ps.setLong(i++,column.getParentColumn().longValue());
		ps.setLong(i++,column.getTopColumn().longValue());
		ps.setInt(i++,column.getCurrentLevel().intValue());
		ps.setInt(i++,column.getLeafNode().intValue());
		
		ps.executeUpdate();
		ps.close();		
	}
	
	public void updateColumn(Column Column)throws Exception{
		List columns = new ArrayList();
		columns.add("column_Name");
		columns.add("column_Order");
		columns.add("column_Status");
		columns.add("parent_Column");
		columns.add("top_Column");
		columns.add("channel_Id");
		columns.add("current_Level");
		columns.add("leaf_Node");
		StringBuffer sqlString=new StringBuffer();
		sqlString.append("update News_Column 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 Column_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(Column,((String)columns.get(i)).replaceAll("_","")));
		}
		ps.setLong(columns.size()+1,Column.getColumnId().longValue());
		ps.executeUpdate();
		ps.close();
	}
	public void deleteColumn(Column Column)throws Exception{
		String sqlString="update News_Column set Column_Status=-1 where Column_id=?";
		PreparedStatement ps = connection.prepareStatement(sqlString);
		ps.setLong(1,Column.getColumnId().longValue());
		ps.executeUpdate();
		ps.close();
	}
	public List listAllColumns() throws Exception{
		PreparedStatement ps = connection.prepareStatement("select * from News_Column where Column_Status>=0 order by column_order");
		ResultSet rs = ps.executeQuery();
		List list = DTOPopulator.populate(rs,Column.class);
		for(int i=0;i<list.size();i++){
			Column c = (Column) list.get(i);
		}
		rs.close();
		ps.close();
		return list;
	}
	public List listColumns(Column columnCondition,int pageNo,int pageSize)throws Exception{

		boolean nameCondition=false;
		boolean parentCondition=false;
		boolean topCondition=false;
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append("select * from News_Column where Column_Status>=0 ");
		if(columnCondition.getColumnName()!=null&&!columnCondition.getColumnName().trim().equals("")){
			stringBuffer.append(" and Column_name like ?");
			nameCondition = true;
		}
		if(columnCondition.getParentColumn()!=null){
			stringBuffer.append(" and Parent_Column =?");
			parentCondition= true;
		}

		if(columnCondition.getTopColumn()!=null){
			stringBuffer.append(" and Top_Column =?");
			topCondition= true;
		}
		stringBuffer.append(" order by column_order limit ?,? ");
		PreparedStatement ps = connection.prepareStatement(stringBuffer.toString());
		int i=1;
		if(nameCondition)
			ps.setString(i++,"%"+columnCondition.getColumnName().trim()+"%");
		if(parentCondition)
			ps.setLong(i++,columnCondition.getParentColumn().longValue());
		if(topCondition)
			ps.setLong(i++,columnCondition.getTopColumn().longValue());
		ps.setInt(i++,(pageNo-1)*pageSize);
		ps.setInt(i++,pageSize);		
		ResultSet rs = ps.executeQuery();
		return DTOPopulator.populate(rs,Column.class);
	}
	public Column getByID(long id)throws Exception{
		PreparedStatement ps = connection.prepareStatement("select * from News_Column where  column_id=?");
		ps.setLong(1,id);
		ResultSet rs = ps.executeQuery();
		return (Column) DTOPopulator.populate(rs,Column.class).get(0);
	}
	public static void main(String[] args) {
		ColumnDAO dao = new ColumnDAOImpl();
		Connection conn =null;
		try {
			conn = Database.getConnection();
			dao.setConnection(conn);
			Column Column=new Column();
			Column.setColumnId(new Long(2));
			Column.setColumnName("国际新闻1");
			Column.setColumnStatus(new Integer(1));
//			dao.addColumn(Column);
			dao.updateColumn(Column);
			Database.commit();
			dao.listAllColumns();
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			Database.releaseConnection(conn);
		}
	}
}