jdbc新闻系统
1.动态的网页项目布局
2.在java中连接SQL数据库(在util层)
import java.sql.Connection; //导import'Connection'(java.sql)包
import java.sql.DriverManager;
public class DbUtil {
public Connection getConn() throws Exception {
Class.forName("com.mysql.jdbc.Driver"); //驱动
//ip:prot/database
Connection conn=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/news_system", "root", "123456");
return conn;
} //url=jdbc:mysql://127.0.0.1:3306/数据库名称,user,password
public void closeConn(Connection conn) throws Exception {
if(conn!=null){
conn.close();
}
}
public static void main(String[] args) throws Exception {
DbUtil dbUtil=new DbUtil();
Connection conn=dbUtil.getConn();
if(conn!=null){
System.out.println("数据库连接成功");
}else{
System.out.println("数据库连接失败");
}
dbUtil.closeConn(conn);
}
}
3.在Dao层(使用了Hibernate连接数据库,操作数据库增删改查)
package com.oracle.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.LinkedList; import com.oracle.model.News; public class NewsDao { public LinkedList<News> findAll(Connection conn) throws Exception{ String sql="select news_id,title,author,source,content,create_time from news";
//select查看表数据语句(select 表中字段名=?,,,……)from 表名; PreparedStatement pst=conn.prepareStatement(sql); ResultSet rs=pst.executeQuery(); LinkedList<News> link=new LinkedList<News>(); while(rs.next()) { News news=new News(); news.setNewsId(rs.getInt("news_id")); news.setTitle(rs.getString("title")); news.setAuthor(rs.getString("author")); news.setSource(rs.getString("source")); news.setContent(rs.getString("content")); news.setCreateTime(rs.getDate("create_time")); link.add(news); } return link; } public int add (Connection conn,News news) throws Exception { String sql="insert into news values(null,?,?,?,?,now())";
//向表中添加数据 insert into 表名 values(值,值,……); PreparedStatement pst=conn.prepareStatement(sql); pst.setString(1,news.getTitle()); pst.setString(2, news.getAuthor()); pst.setString(2, news.getSource()); pst.setString(2, news.getContent()); int n=pst.executeUpdate(); return n; } public int del(Connection conn,int newsId) throws Exception { String sql="delete from news where news_id=?";
//删除表中数据 delete from 表名 where 主键名=?; PreparedStatement pst=conn.prepareStatement(sql); pst.setInt(1, newsId); int n=pst.executeUpdate(); return n; } public int modify(Connection conn,News news) throws Exception { String sql="update news title=?,author=?,source=?,content=? where news_id=?";
//修改表中数据 update 表名 表中字段名=?,,, where 主键名=?; PreparedStatement pst=conn.prepareStatement(sql); pst.setString(1, news.getTitle()); pst.setString(2, news.getAuthor()); pst.setString(3, news.getSource()); pst.setString(4, news.getContent()); int ret=pst.executeUpdate(); return ret; } public News findById(Connection conn,int news_id) throws Exception { String sql="select title,author,source,content from news where news_id=?";
//查询表中的某一条数据 select 字段名,,,from 表名 where 主键=?; PreparedStatement pst=conn.prepareStatement(sql); pst.setInt(1, news_id); ResultSet rs=pst.executeQuery(); News news=null; if(rs.next()) { news=new News(); news.setTitle(rs.getString("title")); news.setAuthor(rs.getString("author")); news.setSource(rs.getString("source")); news.setContent(rs.getString("content")); } return news; } }
4.在model层(对应的数据库表的实体类)
package com.oracle.model; import java.sql.Date; public class News { private Integer newsId; private String title; private String author; private String source; private String content; private Date createTime; public News() { super(); } public Integer getNewsId() { return newsId; } public void setNewsId(Integer newsId) { this.newsId = newsId; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public String getAuthor() { return author; } public void setAuthor(String author) { this.author = author; } public String getSource() { return source; } public void setSource(String source) { this.source = source; } public String getContent() { return content; } public void setContent(String content) { this.content = content; } public Date getCreateTime() { return createTime; } public void setCreateTime(Date createTime) { this.createTime = createTime; } }
5.web即Service层:引用对应的Dao数据库操作
package com.oracle.web;
import java.io.IOException;
import java.sql.Connection;
import java.util.LinkedList;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.oracle.dao.NewsDao;
import com.oracle.model.News;
import com.oracle.util.DbUtil;
public class NewsServlet extends HttpServlet {
/**
*
*/
private static final long serialVersionUID = 1L;
DbUtil dbUtil=new DbUtil();
NewsDao newsDao=new NewsDao();
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String action=req.getParameter("action");
if(action.equals("list")){
newsList(req,resp);
}else if(action.equals("show")){
newsShow(req,resp);
}else if(action.equals("pre_modify")){
newsPreModify(req,resp);
}else if(action.equals("remove")){
newsRemove(req,resp);
}else if(action.equals("modify")){
newsModify(req,resp);
}else if(action.equals("add")){
newsAdd(req,resp);
}
}
private void newsList(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8"); //设置字符集
Connection conn=null;
try {
conn=dbUtil.getConn();
//从Dao层返回一个链表
LinkedList<News> newsList=newsDao.findAll(conn);
//jsp页面显示
req.setAttribute("newsList", newsList);
req.getRequestDispatcher("news_list.jsp").forward(req, resp);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
dbUtil.closeConn(conn);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
private void newsShow(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String newsId=req.getParameter("newsId");
Connection conn=null;
try {
conn=dbUtil.getConn();
News news=newsDao.findById(conn, Integer.parseInt(newsId));
req.setAttribute("news", news);
req.getRequestDispatcher("news_show.jsp").forward(req, resp);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
dbUtil.closeConn(conn);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
private void newsPreModify(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String newsId=req.getParameter("newsId");
}
private void newsRemove(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String newsId=req.getParameter("newsId");
Connection conn=null;
try {
conn=dbUtil.getConn();
int count=newsDao.del(conn, Integer.parseInt(newsId));
req.setAttribute("count", count);
req.getRequestDispatcher("news_del_success.jsp").forward(req, resp);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
dbUtil.closeConn(conn);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
private void newsModify(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String newsId=req.getParameter("newsId");
String xxx=req.getParameter("...");
}
private void newsAdd(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String title=req.getParameter("title");
String author=req.getParameter("author");
String source=req.getParameter("source");
String content=req.getParameter("content");
News news=new News();
news.setTitle(title);
news.setAuthor(author);
news.setSource(source);
news.setContent(content);
Connection conn=null;
try {
conn=dbUtil.getConn();
int count=newsDao.add(conn, news);
req.setAttribute("count", count);
req.getRequestDispatcher("news_add_success.jsp").forward(req, resp);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
dbUtil.closeConn(conn);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}