Sspring相关的数据库操作

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.lang.xwork.StringUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

public class NewsManageDaoImpl extends JdbcDaoSupport implements INewsManageDao {
   
    /**
     * 获取栏目
     * @return 栏目列表
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    public List<NewsColumnBean> getNewsColumnName() throws Exception {
        String sql = "select column_id, news_column from news_column";
        return (List<NewsColumnBean>)this.getJdbcTemplate().query(sql,
            new RowMapper(){
            public Object mapRow(ResultSet rs, int i) throws SQLException {
                NewsColumnBean newsColumnBean = new NewsColumnBean();
                newsColumnBean.setId(rs.getString("column_id"));
                newsColumnBean.setColumnName(rs.getString("news_column"));
                return newsColumnBean;
            }
        });
    }
   
    /**
     * 通过栏目名获取id
     * @param columnName
     * @return String
     * @throws Exception
     */
    public String getNewsColumnNameByName(String columnName) throws Exception {
        if (StringUtils.isNotEmpty(columnName)) {
            String sql = "select column_id from news_column where news_column=?";
            RowMapper mapper = new RowMapper() {
                public Object mapRow(ResultSet rs, int rowNum)
                    throws SQLException {
                    String columnID = rs.getString("column_id");
                    return columnID;
                }
            };
        return (String) getJdbcTemplate().queryForObject(sql,
                new Object[] { columnName }, mapper);
       }
       return null;
    }
   
    /**
     * 通过标题ID查找标题信息
     * @param columnName
     * @return NewsTopicBean
     * @throws Exception
     */
    public NewsTopicBean getNewsTopicById(String topicID) throws Exception {
        if (StringUtils.isNotEmpty(topicID)) {
            String sql = "select t.topicid, n.news_column, t.topicname, t.link_url, t.summary, "
                    + "t.pub_date, t.save_date, t.author, t.topic_url, t.body_url, t.status "
                    + "from  news_topic t left join news_column n on t.columnid = n.column_id "
                    + "where topicid=?";
            RowMapper mapper = new RowMapper() {
                public Object mapRow(ResultSet rs, int rowNum)
                    throws SQLException {
                    NewsTopicBean newsTopicBean = new NewsTopicBean();
                    newsTopicBean.setId(rs.getString("topicid"));
                    newsTopicBean.setColumnName(rs.getString("news_column"));
                    newsTopicBean.setTopicName(rs.getString("topicname"));
                    newsTopicBean.setLinkURL(rs.getString("link_url"));
                    newsTopicBean.setSummary(rs.getString("summary"));
                    newsTopicBean.setAuthor(rs.getString("author"));
                    newsTopicBean.setTopicURL(rs.getString("topic_url"));
                    newsTopicBean.setBodyURL(rs.getString("body_url"));
                    newsTopicBean.setStatus(rs.getString("status"));
                    return newsTopicBean;
                }
            };
        return (NewsTopicBean) getJdbcTemplate().queryForObject(sql,
                new Object[] { topicID }, mapper);
       }
       return null;
    }
   
    /**
     * 添加标题
     * @param newsTopicBean 标题信息
     * @param ID 标题ID
     * @param columnID 标题所属栏目
     * @throws Exception
     */
    public void addNewsTopic(NewsTopicBean newsTopicBean,
                             String ID,
                             String columnID) throws Exception {
        String sql = "insert into news_topic values(?, ?, ?, ?, ?, sysdate, sysdate, ?, ?, ?, ?)";
        this.getJdbcTemplate().update(sql,
              new Object[] {ID,
                            columnID,
                            newsTopicBean.getTopicName(),
                            newsTopicBean.getLinkURL(),
                            newsTopicBean.getSummary(),
                            newsTopicBean.getAuthor(),
                            newsTopicBean.getTopicURL(),
                            newsTopicBean.getBodyURL(),
                            newsTopicBean.getStatus()});
    }
   
    /**
     * 通过id更新标题信息
     * @param newsTopicBean
     * @param columnID 栏目ID
     * @throws Exception
     */
    public void updateNewsTopic(NewsTopicBean newsTopicBean, String columnID) throws Exception {
         String sql = "update news_topic set COLUMNID=?, topicname=?, "
                  + "link_url=?, summary=?, save_date=sysdate, "
                 + "author=?, topic_url=?, body_url=?, status=? where topicid=?";
         this.getJdbcTemplate().update(sql,
                 new Object[] {columnID,
                               newsTopicBean.getTopicName(),
                               newsTopicBean.getLinkURL(),
                               newsTopicBean.getSummary(),
                               newsTopicBean.getAuthor(),
                               newsTopicBean.getTopicURL(),
                               newsTopicBean.getBodyURL(),
                               newsTopicBean.getStatus(),
                               newsTopicBean.getId()});
       
    }
   
    /**
     * 统计所属标题的新闻
     * @param topicID 标题id
     * @return 统计个数
     * @throws Exception
     */
    public int countContentInTopic(String topicID) throws Exception {
        String sql = "select count(*) from news_content where topicid=? ";
        return getJdbcTemplate().queryForInt(sql, new Object[] {topicID });
    }
   
    /**
     * 通过标题id删除标题
     * @param topicID 标题id
     * @throws Exception
     */
    public void deleteNewsTopic(String topicID) throws Exception {
        String sql = "delete from news_topic where topicid=?";
        this.getJdbcTemplate().update(sql, new Object[] {topicID});
    }
   
    /**
     * 通过新闻ID查新闻题信息
     * @param content
     * @return
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    public List<String> getNewsContentById(String contentID) throws Exception {
        if (StringUtils.isNotEmpty(contentID)) {
            String sql = "select content from  news_content where topicid=?";
            RowMapper mapper = new RowMapper() {
                public Object mapRow(ResultSet rs, int rowNum)
                    throws SQLException {
                    String content = rs.getString("content");
                    return content;
                }
            };
            return (List<String>)getJdbcTemplate().query(sql,
                    new Object[] {contentID}, mapper);
       }
       return null;
    }
   
    /**
     * 通过id更新标题正文
     * @param topicID 标题ID
     * @param value 正文内容
     * @throws Exception
     */
    public void updateNewsContent(String topicID, String value) throws Exception {
         String sql = "update news_content set CONTENT=? where TOPICID=?";
         this.getJdbcTemplate().update(sql,
                 new Object[] {value, topicID});
    }
   
    /**
     * 添加标题正文
     * @param topicID 标题ID
     * @param value 正文内容
     * @throws Exception
     */
    public void addNewsContent(String topicID, String value) throws Exception {
         String sql = "insert into news_content values(?, ?)";
         this.getJdbcTemplate().update(sql,
                 new Object[] {topicID, value});
    }
   
    /**
     * 删除标题正文
     * @param topicID 标题ID
     * @throws Exception
     */
    public void deleteNewsContent(String topicID) throws Exception {
         String sql = "delete from news_content where topicid=? ";
         this.getJdbcTemplate().update(sql,
                 new Object[] { topicID });
    }
   
    /**
     * 获自增长ID
     * @return 自增长ID
     * @throws Exception
     */
    public String getNewsID() throws Exception {
        String sql = "select SEQ_NEWS_TOPIC.NEXTVAL from dual";
        RowMapper mapper = new RowMapper() {
            public Object mapRow(ResultSet rs, int rowNum)
                throws SQLException {
                String id = rs.getString("NEXTVAL");
                return id;
            }
        };
        return (String) getJdbcTemplate().queryForObject(sql,
            new Object[] {  }, mapper);
    }
   
   
    /**
     * 按照不同条件查询资讯
     * @param columnName 栏目名
     * @param keyValue  关键字
     * @param startDate 开始时间
     * @param endDate 结束时间
     * @param pageNo 显示页数
     * @param pagePerNum 每页显示数
     * @return 返回资讯List
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    public List<NewsTopicBean> showNewsTopic(String columnName,
                                             String keyValue,
                                             String startDate,
                                             String endDate,
                                             int pageNo,
                                             int pagePerNum) throws Exception {
        int startNum = (pageNo -1) * pagePerNum + 1;
        int endNum = pageNo * pagePerNum == 0 ? 1 : pageNo * pagePerNum;
       
        StringBuffer sql = new StringBuffer("");
        sql.append("select * from  ");
        sql.append("( select a.*, rownum rn from ");
        sql.append("(select t.topicid, n.news_column, t.topicname, t.link_url, t.summary, ");
        sql.append("t.pub_date, t.save_date, t.author, t.topic_url, t.body_url, t.status ");
        sql.append("from  news_topic t left join news_column n on t.columnid = n.column_id "
                + "where 1=1 ");
       
        //根据栏目名查询
        if(StringUtils.isNotEmpty(columnName)) {
            sql.append(" and n.news_column= '" + columnName + "' ");
        }
       
        //根据关键字查询
        if(StringUtils.isNotEmpty(keyValue)) {
            sql.append(" and t.topicname like '%"+keyValue+"%' ");
        }
       
        //开始时间
        if(StringUtils.isNotEmpty(startDate)) {
            sql.append(" and to_char(t.pub_date,'YYYY-MM-DD') >= '");
            sql.append(startDate);
            sql.append("' ");
        }
       
        //结束时间
        if(StringUtils.isNotEmpty(endDate)) {
            sql.append(" and to_char(t.pub_date,'YYYY-MM-DD') <= '");
            sql.append(endDate);
            sql.append("' ");
        }
        sql.append(" order by t.save_date desc) a ");
        sql.append(") where rn between ? and ? ");
        RowMapper mapper = new RowMapper() {
            public Object mapRow(ResultSet rs, int i) throws SQLException {
                NewsTopicBean newsTopicBean = new NewsTopicBean();
                newsTopicBean.setId(rs.getString("topicid"));
                newsTopicBean.setColumnName(rs.getString("news_column"));
                newsTopicBean.setTopicName(rs.getString("topicname"));
                newsTopicBean.setStatus(rs.getString("status"));
                return newsTopicBean;
            }
        };
        logger.info("查询资讯SQL：" + sql.toString());
        return (List<NewsTopicBean>)getJdbcTemplate().query(sql.toString(),
            new Object[] {startNum, endNum}, mapper);
    }
   
    /**
     * 按照不同条件统计资讯
     * @param columnName 栏目名
     * @param keyValue  关键字
     * @param startDate 开始时间
     * @param endDate 结束时间
     * @return 返回资讯List
     * @throws Exception
     */
    public int countNewsTopic(String columnName,
                              String keyValue,
                              String startDate,
                              String endDate ) throws Exception {
        StringBuffer sql = new StringBuffer("");
        sql.append("select count(*) from news_topic t left join news_column n on "
                + " t.columnid = n.column_id where 1=1 ");
       
        //根据栏目名查询
        if(StringUtils.isNotEmpty(columnName)) {
            sql.append(" and n.news_column= '" + columnName + "' ");
        }
       
        //根据关键字查询
        if(StringUtils.isNotEmpty(endDate)) {
            sql.append(" and t.topicname like '%"+keyValue+"%' ");
        }
       
        //开始时间
        if(StringUtils.isNotEmpty(startDate)) {
            sql.append("and to_char(t.pub_date,'YYYY-MM-DD') >= '");
            sql.append(startDate);
            sql.append("' ");
        }
       
        //结束时间
        if(StringUtils.isNotEmpty(endDate)) {
            sql.append("and to_char(t.pub_date,'YYYY-MM-DD') <= '");
            sql.append(endDate);
            sql.append("' ");
        }
        logger.info("统计资讯SQL：" + sql.toString());
        return getJdbcTemplate().queryForInt(sql.toString(), new Object[] { });
    }
}