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;
}
}
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();
}
}
}
}