Jstl、el、jdbc、dbcp、servlet的CRUD示例
1 目录结构及说明
1.1 src 下有website项目目录用于存放模块单元
1.2 action 目录用于存入servlet 接收与响应页面请求
1.3 service 目录用于处理业务逻辑
1.4 dao 目录用于执行操作数据库方法
1.5 demo用于存放页面
1.6 adddemo.jsp 增加页面
1.7 demoindex.jsp示例主页面
1.8 indexlist.jsp 显示与操作列表页面
1.9 updatedemo.jsp 修改页面
代码清单:
一demoindex.jsp
<html>
<head><title>test</title></head>
<body>
<a href="<%=request.getContextPath()%>/demoselect?op=select">demoselect</a>
</body>
</html>
二 indexlist.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
<html>
<head>
<title>jstl的jsp写法</title>
<script language="javascript">
function godel(switchvalue)
{
confirm("are you sure?");
var urls= "<%=request.getContextPath()%>/demoselect?op=del&switchvalue="+switchvalue;
location.href = urls;
}
function goupdate(switchvalue)
{
location.href = "<%=request.getContextPath()%>/demoselect?op=update&switchvalue="+switchvalue;
}
</script>
</head>
<body>
<!-- 循环标签以0开始10结束,每循环的增量是1,并把当前的值赋值给变量bl -->
<table border="1">
<tr><td>序号</td><td>名字</td><td>操作</td></tr>
<c:forEach var="listes" items="${alllist}">
<tr>
<td> ${listes.szz_id}</td>
<td>${listes.szz_name}</td>
<td>
<input type="button" nane="button1" value="修改" onClick="goupdate('${listes.szz_id}')">
<input type="button" name="button2" value="删除" onClick="godel('${listes.szz_id}')">
</td>
</tr>
</c:forEach>
<a href="<%=request.getContextPath()%>/demo/adddemo.jsp">增加</a>
</body>
</html>
三adddemo.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
<html>
<head><title>test</title></head>
<body>
<form action="<%=request.getContextPath()%>/demoselect?op=add" method="post" name="form1">
id:<input type="text" name="szz_id"><br>
name:<input type="text" name="szz_name"><br>
<input type="submit" value="add" name="submit1">
</form>
</body>
</html>
四updatedemo.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
<html>
<head><title>test</title></head>
<body>
<form action="<%=request.getContextPath()%>/demoselect?op=goupdate" method="post" name="form1">
id:<input type="text" name="szz_id" value="${onemap.szz_id}"><br>
name:<input type="text" name="szz_name" value="${onemap.szz_name}"><br>
<input type="submit" value="update" name="submit1">
</form>
</body>
</html>
五 web.xml
<servlet>
<servlet-name>demoselect</servlet-name>
<servlet-class>website.action.DemoSelectAction</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>demoselect</servlet-name>
<url-pattern>/demoselect</url-pattern>
</servlet-mapping>
六 DBConnector类
package common.db;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbcp.BasicDataSource;
public class DBConnector {
public static BasicDataSource ds = null;
static String DBUSER = "yygl";
static String DBPASSWORD = "yygl";
static String DBSIZE = "15";
static String DBURL = "jdbc:oracle:thin:@localhost:1521:oracle";
static String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
static {
try {
ds = new BasicDataSource();
ds.setDriverClassName(DBDRIVER);
ds.setUsername(DBUSER);
ds.setPassword(DBPASSWORD);
ds.setUrl(DBURL);
ds.setMaxActive(Integer.parseInt(DBSIZE));
} catch (Exception e) {
System.out.println("数据库连接错误!");
e.printStackTrace();
}
}
public static Connection getConnection() {
Connection con = null;
if (ds != null) {
try {
con = ds.getConnection();
} catch (Exception e) {
e.printStackTrace(System.err);
}
try {
con.setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
return con;
}
public static void main(String args[]) throws Exception {
String DBDRIVER = null;
String DBUSER = null;
String DBPASSWORD = null;
String DBURL = null;
String DBSIZE = null;
DBUSER = "root";
DBPASSWORD = "root";
DBSIZE = "5";
DBURL = "jdbc:mysql://127.0.0.1:3306/store?useUnicode=true&characterEncoding=gbk";
DBDRIVER = "org.gjt.mm.mysql.Driver";
// DBUSER="salvation";
// DBPASSWORD="salvation";
// DBSIZE="15";
// DBURL="jdbc:oracle:thin:@10.62.1.251:1521:ora";
// DBDRIVER="oracle.jdbc.driver.OracleDriver";
Connection con = null;
BasicDataSource ds = null;
ds = new BasicDataSource();
ds.setDriverClassName(DBDRIVER);//
ds.setUsername(DBUSER);
ds.setPassword(DBPASSWORD);
ds.setUrl(DBURL);
ds.setMaxActive(Integer.parseInt(DBSIZE));
con = ds.getConnection();
con.setAutoCommit(false);
java.sql.PreparedStatement pre = con
.prepareStatement(" select * from s_table");
pre.executeQuery();
con.rollback();
con.rollback();
con.rollback();
con.commit();
con.close();
ds.close();
}
}
七 DemoSelectAction类
package website.action;
import java.io.IOException;
import java.sql.Connection;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import website.service.GlobalBo;
import common.db.DBConnector;
public class DemoSelectAction extends HttpServlet {
GlobalBo globalBo = new GlobalBo();
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
Connection con = DBConnector.getConnection();
String methodSwith =String.valueOf(request.getParameter("op"));
// Process the request in method processRequest
if("select".equals(methodSwith))//查
{
List list = globalBo.getAll(con,"");
request.setAttribute("alllist",list);
this.getServletContext().getRequestDispatcher("/demo/indexlist.jsp").forward(request, response);
}else if("add".equals(methodSwith))//增
{
String id = request.getParameter("szz_id");
String name = request.getParameter("szz_name");
globalBo.Add(con,id,name);
List list = globalBo.getAll(con,"");
request.setAttribute("alllist",list);
this.getServletContext().getRequestDispatcher("/demo/indexlist.jsp").forward(request, response);
}else if("update".equals(methodSwith))//改1
{
String id = request.getParameter("switchvalue");
Map maps = globalBo.getOne(con,id);
request.setAttribute("onemap",maps);
this.getServletContext().getRequestDispatcher("/demo/updatedemo.jsp").forward(request, response);
}else if("goupdate".equals(methodSwith))//改2
{
String id = request.getParameter("szz_id");
String name = request.getParameter("szz_name");
globalBo.Update(con,id,name);
List list = globalBo.getAll(con,"");
request.setAttribute("alllist",list);
this.getServletContext().getRequestDispatcher("/demo/indexlist.jsp").forward(request, response);
}else if("del".equals(methodSwith))//删
{
String id = request.getParameter("switchvalue");
globalBo.Del(con,id);
List list = globalBo.getAll(con,"");
request.setAttribute("alllist",list);
this.getServletContext().getRequestDispatcher("/demo/indexlist.jsp").forward(request, response);
}
}
}
八GlobalBo类
package website.service;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletException;
import website.dao.GlobalDao;
/**
* 此类为业务逻辑类,用于处理业务方法
* @author Administrator
*
*/
public class GlobalBo {
GlobalDao globalDao = new GlobalDao();
public List getAll(Connection conn, String stirngs)
{
// 注:可以在此方法里面使用判断或者循环等实现特定业务功能
return globalDao.selectAll(conn, "select s.szz_id,s.szz_name from shazhenzhong s order by szz_id asc");
}
/**
* 增加方法
* @param con
* @param id
* @param name
*/
public void Add(Connection con, String id, String name) {
globalDao.Exec(con, "insert into shazhenzhong values('"+id+"','"+name+"')");
}
public void Del(Connection connection, String id) {
System.out.println("delete from shazhenzhong where szz_id ='"+id+"'");
globalDao.Exec(connection, "delete from shazhenzhong where szz_id ='"+id+"'");
}
public Map getOne(Connection con, String id) {
return globalDao.getOneData(con,"select s.szz_id,s.szz_name from shazhenzhong s where s.szz_id='"+id+"'");
}
public void Update(Connection con, String id, String name) {
String sql="update shazhenzhong set szz_id='"+id+"',szz_name='"+name+"' where szz_id ='"+id+"'";
System.out.println(sql);
globalDao.Exec(con, sql);
}
}
九GlobalDao类
package website.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class GlobalDao {
/**
* 测试方法返回集合
* @param conn 传入一个数据库连接
* @param sql 传入一个sql查询语句
* @return 返回一个结果集
*/
public List selectAll(Connection conn, String sql){
//select s.szz_id,s.szz_name from shazhenzhong s
PreparedStatement pstm = null;
ResultSet rs = null;
List list = new ArrayList();
try {
pstm = conn.prepareStatement(sql);
rs = pstm.executeQuery();
while(rs.next()){
Map map = new HashMap();
map.put("szz_id", rs.getString("szz_id"));
map.put("szz_name", rs.getString("szz_name"));
list.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
} finally{
close(pstm, rs);
}
return list;
}
/**
* 执行增改删方法
* @param con
* @param string
*/
public void Exec(Connection con, String sql) {
PreparedStatement pstm = null;
try {
con.prepareStatement(sql).execute();
} catch (SQLException e) {
e.printStackTrace();
} finally{
try {
con.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
close(pstm);
}
}
/**
* 关闭预处理和和结果集
* @param pstm
* @param rs
*/
public void close(PreparedStatement pstm, ResultSet rs) {
try {
if (pstm != null) {
pstm.close();
}
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 关闭预处理语句
* @param pstm
*/
public void close(PreparedStatement pstm) {
try {
if (pstm != null) {
pstm.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public Map getOneData(Connection con, String sql) {
Map map = new HashMap();
PreparedStatement pstm = null;
ResultSet rs = null;
try {
pstm = con.prepareStatement(sql);
rs = pstm.executeQuery();
while(rs.next()){
map.put("szz_id", rs.getString("szz_id"));
map.put("szz_name", rs.getString("szz_name"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally{
close(pstm, rs);
}
return map;
}
}
十 建立表语句
create table SHAZHENZHONG
(
SZZ_ID VARCHAR2(100),
SZZ_NAME VARCHAR2(100)
)