|
前言：本项目主要用eclipse和orcl数据库进行搭建。重点是代码封装！
基本上是“换汤不换药”，代码大都“神似且形似”。
一、项目功能：
1-1：用户登录
1-2：用户注册
1-3：新闻页面的数据维护（添加、修改、删除、查询）
1-4：新闻主题页面的数据维护（添加、修改、删除）
1-5：阅读新闻页面的数据维护（添加评论、删除评论）
二、准备工作
1、在orcl数据库中创建 新闻表（news）、新闻主题表（subject）、用户表（User）、评论表（ptext）
2、打开eclipse新建一个web项目
3、在webroot下的WEB-INF下的lib中导入数据库的驱动jar包
4、创建对应的包结构
优化新闻管理系统(分层) entity：新闻、主题、评论、用户 dao：新闻dao、主题dao、评论dao、用户dao
com.china.dao ——数据操作类
com.china.entity ——实体类（实体即抽象出来的用户对象，对应数据库中的User表，表中的每个字段在实体中为一个属性）
com.china.util ——帮助类
三、进入主题
帮助类：DBHelper
package com.china.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class DBHelper {
//OracleDriver alt+?
private static String cname = "oracle.jdbc.driver.OracleDriver";
private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
private static String user = "scott";
private static String upwd = "tiger";
//注册驱动类
static {
try {
Class.forName(cname);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 连接数据库
* @return
*/
public static Connection getCon() {
Connection con = null;
try {
con = DriverManager.getConnection(url, user, upwd);
} catch (Exception e) {
e.printStackTrace();
}
return con;
}
/**
* 关闭连接
* @param con
* @param ps
* @param rs
*/
public static void closeDB(Connection con,PreparedStatement ps,ResultSet rs) {
try {
if(con!=null) {
con.close();
}
if(ps!=null) {
ps.close();
}
if(rs!=null) {
rs.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 下一个获取下一个编号的方法
* @return 下一个编号
*/
public static int getNextId(String tableName,String col) {
int id = 1;
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//连接数据库
con = DBHelper.getCon();
ps = con.prepareStatement("select max("+col+") from "+tableName);
//执行sql语句
rs = ps.executeQuery();
if(rs.next()) {
id = rs.getInt(1)+1;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.closeDB(con, ps, rs);
}
return id;
}
}
实体类(仅展示一个，其他类以此类推)：News
package com.china.entity;
import java.sql.Date;
public class News {
private int nid;
private int tid;
private String ntitle;
private String nzz;
private String nnr;
private Date ndate;
private String nzy;
private int nlook;
private String nimg;
public News() {
super();
}
public News(int nid, int tid, String ntitle, String nzz, String nnr, Date ndate, String nzy, String nimg) {
super();
this.nid = nid;
this.tid = tid;
this.ntitle = ntitle;
this.nzz = nzz;
this.nnr = nnr;
this.ndate = ndate;
this.nzy = nzy;
this.nimg = nimg;
}
public News(int tid, String ntitle, String nzz, String nnr, Date ndate, String nzy, int nlook, String nimg) {
super();
this.tid = tid;
this.ntitle = ntitle;
this.nzz = nzz;
this.nnr = nnr;
this.ndate = ndate;
this.nzy = nzy;
this.nlook = nlook;
this.nimg = nimg;
}
public News(int tid, String ntitle, String nzz, String nnr, String nzy, String nimg) {
super();
this.tid = tid;
this.ntitle = ntitle;
this.nzz = nzz;
this.nnr = nnr;
this.nzy = nzy;
this.nimg = nimg;
}
public News(String ntitle, String nzz, String nnr, String nzy, String nimg) {
super();
this.ntitle = ntitle;
this.nzz = nzz;
this.nnr = nnr;
this.nzy = nzy;
this.nimg = nimg;
}
public News(int nid, int tid, String ntitle, String nzz, String nnr, String nzy, String nimg) {
super();
this.nid = nid;
this.tid = tid;
this.ntitle = ntitle;
this.nzz = nzz;
this.nnr = nnr;
this.nzy = nzy;
this.nimg = nimg;
}
public News(int nid, int tid, String ntitle, String nzz, String nnr, Date ndate, String nzy, int nlook,
String nimg) {
super();
this.nid = nid;
this.tid = tid;
this.ntitle = ntitle;
this.nzz = nzz;
this.nnr = nnr;
this.ndate = ndate;
this.nzy = nzy;
this.nlook = nlook;
this.nimg = nimg;
}
public int getNid() {
return nid;
}
public void setNid(int nid) {
this.nid = nid;
}
public int getTid() {
return tid;
}
public void setTid(int tid) {
this.tid = tid;
}
public String getNtitle() {
return ntitle;
}
public void setNtitle(String ntitle) {
this.ntitle = ntitle;
}
public String getNzz() {
return nzz;
}
public void setNzz(String nzz) {
this.nzz = nzz;
}
public String getNnr() {
return nnr;
}
public void setNnr(String nnr) {
this.nnr = nnr;
}
public Date getNdate() {
return ndate;
}
public void setNdate(Date ndate) {
this.ndate = ndate;
}
public String getNzy() {
return nzy;
}
public void setNzy(String nzy) {
this.nzy = nzy;
}
public int getNlook() {
return nlook;
}
public void setNlook(int nlook) {
this.nlook = nlook;
}
public String getNimg() {
return nimg;
}
public void setNimg(String nimg) {
this.nimg = nimg;
}
@Override
public String toString() {
return "News [nid=" + nid + ", tid=" + tid + ", ntitle=" + ntitle + ", nzz=" + nzz + ", nnr=" + nnr + ", ndate="
+ ndate + ", nzy=" + nzy + ", nlook=" + nlook + ", nimg=" + nimg + "]";
}
}
数据操作类：NewsDao
package com.china.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.sql.Date;
import com.china.entity.News;
import com.china.util.DBHelper;
public class NewsDao {
// 添加新闻
/**
* 添加新闻
* @param news 要添加的新闻对象
* @return 成功返回1，失败返回0
*/
public int addNews(News news) {
// 声明对象
int i = 0;
Connection con = null;
PreparedStatement ps = null;
try {
// 连接数据库
con = DBHelper.getCon();
ps = con.prepareStatement(
"insert into news(nid,tid,ntitle,nzz,ncontent,nzy,ndate,nlook) values(?,?,?,?,?,?,sysdate,0)");
// 给占位符赋值
ps.setInt(1, DBHelper.getNextId("news", "nid"));
ps.setInt(2, news.getTid());
ps.setString(3, news.getNtitle());
ps.setString(4, news.getNzz());
ps.setString(5, news.getNnr());
ps.setString(6, news.getNzy());
// 执行sql语句
i = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.closeDB(con, ps, null);
}
return i;
}
// 删除新闻
/**
* 删除新闻
* @param nid 要删除的新闻编号
* @return 成功返回1，失败返回0
*/
public int delete(int nid) {
int i = 0;
Connection con = null;
PreparedStatement ps = null;
try {
//连接数据库
con = DBHelper.getCon();
ps = con.prepareStatement("delete news where nid="+nid);
//执行sql语句
i = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.closeDB(con, ps, null);
}
return i;
}
// 修改新闻
/**
* 修改新闻
* @param nid 要修改的 新闻编号
* @param news 修改后的新闻
* @return 成功返回1，失败返回0
*/
public int upNews(int nid,News news) {
int i = 0;
Connection con = null;
PreparedStatement ps = null;
try {
//连接数据库
con = DBHelper.getCon();
ps = con.prepareStatement("update news set tid=?, ntitle=?,nzz=?,ncontent=?,nzy=?,nimg=? where nid="+nid);
//给占位符赋值
ps.setInt(1, news.getTid());
ps.setString(2, news.getNtitle());
ps.setString(3, news.getNzz());
ps.setString(4, news.getNnr());
ps.setString(5, news.getNzy());
ps.setString(6, news.getNimg());
//给占位符赋值
i = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.closeDB(con, ps, null);
}
return i;
}
//查询单个
public News getByid(int xy) {
News n = null;
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//连接数据库
con = DBHelper.getCon();
ps = con.prepareStatement("select * from news where nid="+xy);
//执行sql语句
rs = ps.executeQuery();
//处理结果
if(rs.next()) {
int nid = rs.getInt(1);
int tid = rs.getInt(2);
String ntitle = rs.getString(3);
String nzz = rs.getString(4);
String nnr = rs.getString(5);
Date ndate = rs.getDate(6);
int nlook = rs.getInt(7);
String nzy = rs.getString(8);
String nimg = rs.getString(9);
//创建对象
n = new News(nid, tid, ntitle, nzz, nnr, ndate, nzy, nlook, nimg);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.closeDB(con, ps, rs);
}
return n;
}
//分页查询：
/**
* 分页查询
* @param pageIndex 页码
* @param pageSize 每页数据条数
* @return 查询到的集合
*/
public ArrayList pageNews(int pageIndex,int pageSize){
//声明对象
ArrayList nlist = new ArrayList();
int start = (pageIndex-1)*pageSize+1;
int end = pageIndex*pageSize;
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//连接数据库
con = DBHelper.getCon();
String sql = "select * from(select a.*,rownum mid from news a)b where mid>=? and mid=? and mid=? and mid |