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[] { });
}
}
Sspring相关的数据库操作
最新推荐文章于 2022-07-29 15:11:20 发布