cms(Content Manage System) 模型框架搭建

关于构建CMS网页

本文内容是关于用CMS的思想，设计web页面和数据库内容。其中web页面的页面设计储存在模板中，页面内容储存在数据库中，我这里用的数据库连接时用的是数据库缓存池来缓存。这种方式设计的网页能够适应大部分的门户网站和小型私有化企业网站。

一、项目结构层设计目录

1、web结构目录

基本结构目录包括：dao包；entity包；servlet包；filter包；db包（数据库连接）；

2、web页面目录

基本页面目录包括：index.jsp（首页索引页面）；list.jsp（列表内容页面模板）；image.jsp（图文内容页面模板）；detail.jsp（自由内容页面模板）；

二、数据库表设计

注释：type字段中1、2、3分别对应“列表内容页面模板”、“图文内容页面模板”、“自由内容页面模板”

1、目录表

新建一个表，表名为cms_channel。

设计表结构，基本字段包括：channel_id，channel_name，type（看注释），parent_id(上级目录的id)，visit_path（访问路径），template_path（模板路径），default_path（默认路径）

2、内容表

 新建一个表，表名为cms_posts。

设计表结构，基本字段包括：id，channel_id（对应的目录id），title（标题），cover（图片），content_info（详细内容），clicks（点击量），creator（发布人），create_time（创建时间）

都设计好后就可以开始写代码了

三、web结构目录代码

1、db包：首先在db包中新建DBManager.java文件，编写获取数据库连接的方法和关闭数据库连接的方法。

package com.ponderingtech.db;import java.sql.Connection;import java.sql.DatabaseMetaData;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties;import javax.sql.DataSource;import org.apache.commons.dbcp.BasicDataSourceFactory;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;public class DBManager {private static final Log log = LogFactory.getLog(DBManager.class);private static final String configFile = "dbcp.properties";private static DataSource dataSource;static {Properties dbProperties = new Properties();try {dbProperties.load(DBManager.class.getClassLoader().getResourceAsStream(configFile));dataSource = BasicDataSourceFactory.createDataSource(dbProperties);Connection conn = getConnection();DatabaseMetaData mdm = conn.getMetaData();log.info("Connected to " + mdm.getDatabaseProductName() + " "+ mdm.getDatabaseProductVersion());if (conn != null) {conn.close();}} catch (Exception e) {log.error("初始化连接池失败：" + e);}}private DBManager() {}/** * 获取链接，用完后记得关闭 *  * @see {@link DBManager#closeConn(Connection)} * @return */public static final Connection getConnection() {Connection conn = null;try {conn = dataSource.getConnection();} catch (SQLException e) {log.error("获取数据库连接失败：" + e);}return conn;}/** * 关闭连接 *  * @param conn *            需要关闭的连接 */public static void closeConn(Connection conn) {try {if (conn != null && !conn.isClosed()) {conn.setAutoCommit(true);conn.close();}} catch (SQLException e) {log.error("关闭数据库连接失败：" + e);}}public static void Close(ResultSet rs, Statement st, Connection conn) {if (rs != null)try {rs.close();// System.out.println("查询结果返回正常！!");} catch (SQLException e1) {System.out.println("查询结果返回出现异常！!");e1.printStackTrace();}if (st != null)try {st.close();// System.out.println("sql语句的连接正常关闭!!");} catch (SQLException e) {System.out.println("sql语句关闭时出现异常!!");e.printStackTrace();}if (conn != null)try {conn.close();// System.out.println("数据库连接正常关闭!!");} catch (SQLException e) {System.out.println("数据库关闭时出现异常!!");e.printStackTrace();}}}

2、entity包：在entity包中新建Channel.java和Posts.java文件，编写目录实体类的构造方法。

package com.ponderingtech.entity;import java.util.List;public class Channel {private int channelId;private String channelName;private int type;private int parentId;private String visitPath;private String templatePath;private String defaultPath;private List<Channel> childChannelList;public Channel() {super();}public Channel(int channelId, String channelName, int type, int parentId,String visitPath, String templatePath, String defaultPath) {super();this.channelId = channelId;this.channelName = channelName;this.type = type;this.parentId = parentId;this.visitPath = visitPath;this.templatePath = templatePath;this.defaultPath = defaultPath;}public int getChannelId() {return channelId;}public void setChannelId(int channelId) {this.channelId = channelId;}public String getChannelName() {return channelName;}public void setChannelName(String channelName) {this.channelName = channelName;}public int getType() {return type;}public void setType(int type) {this.type = type;}public int getParentId() {return parentId;}public void setParentId(int parentId) {this.parentId = parentId;}public String getVisitPath() {return visitPath;}public void setVisitPath(String visitPath) {this.visitPath = visitPath;}public String getTemplatePath() {return templatePath;}public void setTemplatePath(String templatePath) {this.templatePath = templatePath;}public String getDefaultPath() {return defaultPath;}public void setDefaultPath(String defaultPath) {this.defaultPath = defaultPath;}public List<Channel> getChildChannelList() {return childChannelList;}public void setChildChannelList(List<Channel> childChannelList) {this.childChannelList = childChannelList;}}

package com.ponderingtech.entity;import java.util.Date;public class Posts {private int id;private int channelId;private String title;private String conver;private String contentInfo;private int clicks;private String creator;private Date createTime;public Posts() {super();}public Posts(int id, int channelId, String title, String conver,String contentInfo, int clicks, String creator, Date createTime) {super();this.id = id;this.channelId = channelId;this.title = title;this.conver = conver;this.contentInfo = contentInfo;this.clicks = clicks;this.creator = creator;this.createTime = createTime;}public int getId() {return id;}public void setId(int id) {this.id = id;}public int getChannelId() {return channelId;}public void setChannelId(int channelId) {this.channelId = channelId;}public String getTitle() {return title;}public void setTitle(String title) {this.title = title;}public String getConver() {return conver;}public void setConver(String conver) {this.conver = conver;}public String getContentInfo() {return contentInfo;}public void setContentInfo(String contentInfo) {this.contentInfo = contentInfo;}public int getClicks() {return clicks;}public void setClicks(int clicks) {this.clicks = clicks;}public String getCreator() {return creator;}public void setCreator(String creator) {this.creator = creator;}public Date getCreateTime() {return createTime;}public void setCreateTime(Date createTime) {this.createTime = createTime;}}

3、dao包：在dao包中新建ChannelDao.java和PostsDao.java文件。ChannelDao.java文件中编写获取数据库中cms_channel表数据的方法，PostsDao.java文件中编写获取数据库中cms_PostsDao表数据的方法。

package com.ponderingtech.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;import com.ponderingtech.db.DBManager;import com.ponderingtech.entity.Channel;public class ChannelDao {public List<Channel> getTopChannelList() {String sql = "select * from cms_channel where parent_id is null";// 获取连接Connection conn = DBManager.getConnection();Statement st = null;ResultSet rs = null;List<Channel> list = new ArrayList<Channel>();try {st = conn.prepareStatement(sql);rs = st.executeQuery(sql);while (rs.next()) {Channel c = parse(rs);list.add(c);}} catch (SQLException e) {System.out.println("sql语句连接异常！");e.printStackTrace();} finally {DBManager.Close(rs, st, conn);}List<Channel> allChannelList = getAllChannelList();for(Channel topChannel:list) {List<Channel> childChannelList = new ArrayList<Channel>();for(Channel c:allChannelList) {if(c.getParentId() >0 && c.getParentId() == topChannel.getChannelId()) {childChannelList.add(c);}}topChannel.setChildChannelList(childChannelList);}return list;}public List<Channel> getAllChannelList() {String sql = "select * from cms_channel";// 获取连接Connection conn = DBManager.getConnection();Statement st = null;ResultSet rs = null;List<Channel> list = new ArrayList<Channel>();try {st = conn.prepareStatement(sql);rs = st.executeQuery(sql);while (rs.next()) {Channel c = parse(rs);list.add(c);}} catch (SQLException e) {System.out.println("sql语句连接异常！");e.printStackTrace();} finally {DBManager.Close(rs, st, conn);}return list;}public Channel getChannel(String visitPath) {String sql = "select * from cms_channel where visit_path=?";// 获取连接Connection conn = DBManager.getConnection();PreparedStatement st = null;ResultSet rs = null;Channel c = null;try {st = conn.prepareStatement(sql);st.setString(1, visitPath);rs = st.executeQuery(sql);if (rs.next()) {c = parse(rs);}} catch (SQLException e) {System.out.println("sql语句连接异常！");e.printStackTrace();} finally {DBManager.Close(rs, st, conn);}return c;}private Channel parse(ResultSet rs) throws SQLException {Channel c = new Channel(rs.getInt("channel_id"),rs.getString("channel_name"), rs.getInt("type"),rs.getInt("parent_id"), rs.getString("visit_path"),rs.getString("template_path"),rs.getString("default_path"));return c;}}

package com.ponderingtech.dao;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.ponderingtech.db.DBManager;import com.ponderingtech.entity.Posts;public class PostsDao {public List<Posts> getPostsList(int channelId) {String sql = "select * from cms_posts where channel_id=?";// 获取连接Connection conn = DBManager.getConnection();PreparedStatement st = null;ResultSet rs = null;List<Posts> list = new ArrayList<Posts>();try {st = conn.prepareStatement(sql);st.setInt(1, channelId);rs = st.executeQuery();while (rs.next()) {Posts c = parse(rs);list.add(c);}} catch (SQLException e) {System.out.println("sql语句连接异常！");e.printStackTrace();} finally {DBManager.Close(rs, st, conn);}return list;}public Posts getPosts(String postsId) {String sql = "select * from cms_posts where id=?";// 获取连接Connection conn = DBManager.getConnection();PreparedStatement st = null;ResultSet rs = null;Posts c = new Posts();try {st = conn.prepareStatement(sql);st.setString(1, postsId);rs = st.executeQuery();if (rs.next()) {c = parse(rs);}} catch (SQLException e) {System.out.println("sql语句连接异常！");e.printStackTrace();} finally {DBManager.Close(rs, st, conn);}return c;}private Posts parse(ResultSet rs) throws SQLException {Posts p = new Posts(rs.getInt("id"), rs.getInt("channel_id"), rs.getString("title"), rs.getString("cover"), rs.getString("content_info"), rs.getInt("clicks"), rs.getString("creator"), rs.getTimestamp("create_time"));return p;}}

未完待续。。。。。。。。。。。。。。。