Jstl、el、jdbc、dbcp、servlet的CRUD示例

来源：互联网发布：数据库性能编辑：程序博客网时间：2024/06/11 01:18

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>

<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>

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>

<c:forEach var="listes" items="${alllist}">

<tr>

<td> ${listes.szz_id}</td>

<td>${listes.szz_name}</td>

<td>

</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>

<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>

</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>

<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>

</form>

</body>

</html>

五 web.xml

<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.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)
)