Web jsp开发学习——连接数据库，数据的增加和删除
2024-10-06 21:58:14
1.首先在newlist界面增加三个图表，带上事件
newlist.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
import="java.util.*,java.text.*,com.xx17.cys.entity.*"
import="java.sql.*,org.apache.commons.dbcp2.*"
import="javax.naming.*,javax.sql.*" pageEncoding="UTF-8"%>
<%
//List<News> newslist = new ArrayList<News>();
List newslist = new ArrayList();
News news;
/*= new News(1,"今天是星期四","gfsggfsg",201,"2019-3-13",0);
newslist.add(news);
news = new News(2,"又可以上web课啦！","gfsggfshgffdhg",104,"2019-3-14",0);
newslist.add(news);
String newstitle[]={"今天是星期四！","又可以上web课啦！","下午公休，嘿嘿！","人生啊，如此艰难！","现在是上午9点20分"};
String newsdate[]={"2019-3-13","2019-3-14","2019-3-15","2019-3-16","2019-3-17"};
*/
/*数据库连接池*/
/*BasicDataSource ds = new BasicDataSource();
String url="jdbc:mysql://localhost:3306/bookstore?user=root&password=caiyishuai";
url += "&useUnicode=true&characterEncoding=utf8";
ds.setDriverClassName(url);
ds.setUsername("root");
ds.setPassword("caiyishuai");
ds.setMaxTotal(30);
ds.setMinIdle(5);
ds.setMaxWaitMillis(10000);
ds.setRemoveAbandonedTimeout(100);
ds.setRemoveAbandonedOnBorrow(true);
ds.setRemoveAbandonedOnMaintenance(true);
*/ /*使用静态数据库连接池*/
Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/bookstore"); //建立连接池连接
Connection conn = ds.getConnection(); /*Class.forName("com.mysql.jdbc.Driver"); ////驱动程序名
String url = "jdbc:mysql://localhost:3306/bookstore"; //数据库名
String username = "root"; //数据库用户名
String password = "caiyishuai"; //数据库用户密码
Connection conn = DriverManager.getConnection(url, username, password); //连接状态*/ if(conn != null){
out.print("数据库连接成功！");
Statement stmt = conn.createStatement();
//创建结果集合，集合与表的结构类似
ResultSet rs = stmt.executeQuery("select * from t_news");
//rs.last
//out.println("记录数："+rs.getRow());
while(rs.next()){
//System.out.println("rs"+rs);
news = new News(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getInt(5),rs.getString(4),1);
newslist.add(news);
}
} /*建立连接
Connection conn = DriverManager.getConnection(url);
创建语句环境
Statement stmt = conn.createStatement();
创建结果集合，集合与表的结构类似
ResultSet rs = stmt.executeQuery("select * from t_news");
rs.last
out.println("记录数："+rs.getRow());
Class.forName("com.mysql.jdbc.Driver").newInstance();
com.mysql.jdbc.Driver d = new com.mysql.jdbc.Driver
String url="jdbc:mysql://localhost:3306/bookstore?"
+"user=root&password=caiyishuai&useUnicode=true&characterEncoding=utf8";
List<News> newslist = new ArrayList<News>();
News news;
while(rs.next()){
news = new News(1,rs.getString(1),rs.getString(2),rs.getInt(4),rs.getString(3),1);
newslist.add(news);
}*/
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>新闻列表</title>
</head>
<body>
<link href="css/style.css" rel="stylesheet" type="text/css"/>
<jsp:include page="head.jsp?col=1"></jsp:include>
<div id="main" class="layout">
<%
SimpleDateFormat formatter = new SimpleDateFormat("yyyy年MM月dd日");
java.util.Date today = new java.util.Date();
out.print(formatter.format(today));
//out.print(today.toLocaleString());
//int id =Integer.parseInt(str);
%>
<table width="1000" border="1" cellpadding="0" cellspacing="0" style="border-collapse:collapse;">
<tr>
<th>序号</th>
<th>标题</th>
<th>日期</th>
<th>点击</th>
<th></th>
</tr>
<% String[] color={"red","orange","yellow","green","blue","purple","pink","black","brown"};%>
<% for(int font_size=1;font_size<7;font_size++) {%>
<br>
<font color="<%= color[font_size-1] %>" size="<%= font_size %>">
智慧的帅帅，么么哒！
</font>
<%}
News n;
%> <% for(int i=0;i<newslist.size();i++){
n=(News)newslist.get(i);%>
<tr>
<td><%=i+1%></td>
<td><a href="news.jsp?nid=<%=n.getNews_id()%>">
<%=n.getNews_title()%></a></td>
<td><%=n.getNews_date()%></td>
<td><%=n.getNews_read()%></td>
<td>
<a href="addnews.do?action=mod&nid=<%=n.getNews_id()%>">
<img src="data:images/mod.png" width=40 >
</a>
<a href="addnews.do?action=del&nid=<%=n.getNews_id()%>">
<img src="data:images/del.png" width=40>
</a>
</td>
</tr>
<%} %>
</table>
<a href="addnews.do?action=add"><img src="data:images/new.png" width=40 ></a>
</div>
<%@ include file="foot.jsp" %>
</body>
</html>
2.在NewsServlet,java里写判断条件等等
package com.xx17.cys.servlet; import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date; import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; import com.xx17.cys.entity.News;
import com.xx17.cys.javabean.NewsBean; /**
* Servlet implementation class NewsServlet
*/
@WebServlet("/addnews.do")
public class NewsServlet extends HttpServlet {
private static final long serialVersionUID = 1L; /**
* @see HttpServlet#HttpServlet()
*/
public NewsServlet() {
super();
// TODO Auto-generated constructor stub
} protected void addNews(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { RequestDispatcher rd;
rd = request.getRequestDispatcher("addNews.jsp");
rd.forward(request, response); } protected void modNews(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String nid = request.getParameter("nid");
NewsBean nb = new NewsBean();
News news = nb.getNewsById(nid); request.setAttribute("news", news);
RequestDispatcher rd;
rd = request.getRequestDispatcher("modnews.jsp");
rd.forward(request, response);
} protected void delNews(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String nid = request.getParameter("nid");
NewsBean nb = new NewsBean();
nb.del(nid); RequestDispatcher rd;
rd = request.getRequestDispatcher("newslist.jsp");
rd.forward(request, response);
} protected void getNews(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { } protected void insNews(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String nt = request.getParameter("ntitle");
String nc = request.getParameter("ncontent");
String ntop = request.getParameter("ntop");
Date now = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String nd = sdf.format(now);
int nr = 100;
NewsBean nb = new NewsBean();
nb.add(nt,nc,nd,nr,ntop); response.sendRedirect("newslist.jsp");
/*RequestDispatcher rd;
rd = request.getRequestDispatcher("newslist.jsp");
rd.forward(request, response);*/ }
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
String act = request.getParameter("action");
if(act.equals("add")) {
addNews(request, response);
}else if(act.equals("mod")) {
modNews(request, response);
}else if(act.equals("del")) {
delNews(request, response);
}else if(act.equals("show")) {
getNews(request, response);
}else if(act.equals("save")) {
insNews(request, response);
}
} /**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
} }
3.增加新闻，点击后跳转到addNews.jsp界面
addNews.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<link href="css/style.css" rel="stylesheet" type="text/css"/>
<jsp:include page="head.jsp?col=1"></jsp:include>
<div style="width:1000px; margin:auto; heigth:500px;">
<form action="addnews.do?action=save" method="post">
<table width="1000" border="1" cellpadding="5">
<tr>
<td width="200">标题：</td>
<td width="800"><input name="ntitle" size="80"></td>
</tr>
<tr>
<td>内容：</td>
<td><textarea name="ncontent" cols="80" rows="10"></textarea></td>
</tr>
<tr>
<td></td>
<td><input type="checkbox" name="ntop" value="1">置顶</td>
</tr>
<tr>
<td align="center" colspan="2">
<input type="submit" value="保存">
<input type="button" value="返回" onclick="location.href='newslist.jsp'">
</td>
</tr>
</table>
</form>
</div>
</body>
<%@ include file="foot.jsp" %>
</html>
NewsBean.java
package com.xx17.cys.javabean;
import java.sql.ResultSet;
import com.xx17.cys.entity.News;
public class NewsBean {
public void add(String nt, String nc, String nd, int nr, String ntop) {
String sql = "insert into t_news(ntitle,ncontent,ndate,";
sql += "nread,ntop) values('"+nt+"','"+nc+"','"+nd;
sql += "',"+nr+","+ntop+")";
System.out.println("here: "+sql);
DBBean db = new DBBean();
db.getConnection();
db.executeUpdata(sql);
}
public News getNewsById(String nid) {
News n = new News();
String sql = "select * from t_news where nid="+nid;
DBBean db = new DBBean();
db.getConnection();
ResultSet rs = db.executeQuery(sql);
try {
if(rs.next()) {
n.setNews_id(rs.getInt(1));
n.setNews_title(rs.getString(2));
n.setNews_content(rs.getString(3));
n.setNews_top(rs.getInt(6));
}
}catch(Exception e) {
e.printStackTrace();
}
return n;
}
public void del(String nid) {
String sql = "delete from t_news where nid="+nid;
System.out.println("here: "+sql);
DBBean db = new DBBean();
db.getConnection();
db.executeUpdata(sql);
}
}
DBBean.java
package com.xx17.cys.javabean; import java.sql.*;
import javax.naming.*;
import javax.sql.*; public final class DBBean { private Connection conn;
private Statement stmt;
private ResultSet rs; public DBBean() { } //建立数据库连接
public Connection getConnection() { try {
/*使用静态数据库连接池*/
Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/bookstore");
//建立连接池连接
conn = ds.getConnection();
}catch(Exception e) {
e.printStackTrace();
} return conn;
} // 提取SQL生成记录集
public ResultSet executeQuery(String sql) {
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
System.out.println("返回结果集");
}catch(Exception e) {
e.printStackTrace();
} return rs;
} // 提取SQL添加记录
public int executeUpdata(String sql){
int result = 0; try{
stmt = conn.createStatement();
System.out.println(sql);
result = stmt.executeUpdate(sql);
}catch(Exception e){
e.printStackTrace();
} return result; }
}
3.删除数据
最新文章
- 深入理解java中的ArrayList和LinkedList
- 使用C#在CEF中拦截并响应请求
- f(n) hdu 2582
- 【NodeJS 学习笔记03】先运行起来再说
- 淘宝首页源码藏美女彩蛋(上)（UED新作2013egg）
- meclipse中project facet问题
- unity button
- java中的if-Switch选择结构
- The Material Sourcing Process Failed To Create Picking Suggestions in INVTOTRX (文档 ID 2003806.1)
- QT creator中使用opencv采集摄像头信息
- Oracle12c 在windonServer2012中安装的步骤
- word 2013 粘贴的图片自适应大小
- 用软件工程分析开源项目octave的移植
- python循环字符转换
- Google Protocol Buffer在vs2010下配置
- linux上jar包的运行
- js dom学习
- Fort.js – 时尚、现代的进度提示效果
- 外汇EA(LRY_FX_Robot_V5)
- 角色用户权限RBAC