*.数据库连接池c3p0，连接mysql数据库；
*.Jquery使用，删除时跳出框，确定是否要删除；
*.使用EL和JSTL，简化在jsp页面中插入的java语言
1.连接数据库
（1）导入连接数据库连接池的开源架包和EL和JSTL的架包（在lib目录下导入）
c3p0-0.9.1.2jar
commons-dbutils-1.3jar
mysql-connector-java-5.1jar
jstl.jar
standard.jar
（2）在src目录下建立一个c3p0-config.xml文件，存放数据库连接池的变量：
<?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <named-config name="mvcapp"> <!-- 指定连接数据源的基本属性 --> <property name="user">root</property> <property name="password">lxn123</property> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql:///test</property> <!-- 若数据库中连接数不足时, 一次向数据库服务器申请多少个连接 --> <property name="acquireIncrement">5</property> <!-- 初始化数据库连接池时连接的数量 --> <property name="initialPoolSize">10</property> <!-- 数据库连接池中的最小的数据库连接数 --> <property name="minPoolSize">10</property> <!-- 数据库连接池中的最大的数据库连接数 --> <property name="maxPoolSize">50</property> <!-- C3P0 数据库连接池可以维护的 Statement 的个数 --> <property name="maxStatements">20</property> <!-- 每个连接同时可以使用的 Statement 对象的个数 --> <property name="maxStatementsPerConnection">5</property> </named-config> </c3p0-config>
(2)连接数据库连接池的类：JdbcUtils
package com.lanqiao.javatest; import java.sql.Connection; import java.sql.SQLException; import org.junit.Test; import com.mchange.v2.c3p0.ComboPooledDataSource; public class JdbcUtils { //实现数据库连接池的方法类 //该静态代码块只执行一次 private static ComboPooledDataSource dataSource=null; static{ dataSource=new ComboPooledDataSource("mvcapp"); } public static Connection getConnection() throws SQLException{ return dataSource.getConnection(); } @Test //测试数据库连接池是否连接成功 public void testT() throws SQLException{ System.out.println(getConnection()); } //释放connection连接 public static void releaseConnection(Connection connection){ try { if(connection!=null){ connection.close(); } } catch (Exception e) { e.printStackTrace(); } } }
2.建立customer类，对数据库中的属性进行封装；
package com.lanqiao.javatest; public class Customer { private Integer id; private String name; private String address; private String phone; public Customer() { super(); // TODO Auto-generated constructor stub } public Customer(String name, String address, String phone) { this.name = name; this.address = address; this.phone = phone; } public Customer(Integer id, String name, String address, String phone) { super(); this.id = id; this.name = name; this.address = address; this.phone = phone; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } @Override public String toString() { return "Person [id=" + id + ", name=" + name + ", address=" + address + ", phone=" + phone + "]"; } }
3.建立CriteriaCustomer类，对数据库中的部分数据进行封装，便于模糊查询使用
package com.lanqiao.javatest; //模糊查询的类 public class CriteriaCustomer { private String name; private String address; private String phone; public CriteriaCustomer() { super(); } public CriteriaCustomer(String name, String address, String phone) { super(); this.name = name; this.address = address; this.phone = phone; } public String getName() { if (name==null) { name="%%"; } else{ name="%"+name+"%"; } return name; } public void setName(String name) { this.name = name; } public String getAddress() { if (address==null) { address="%%"; } else{ address="%"+address+"%"; } return address; } public void setAddress(String address) { this.address = address; } public String getPhone() { if (phone==null) { phone="%%"; } else{ phone="%"+phone+"%"; } return phone; } public void setPhone(String phone) { this.phone = phone; } }
4.在src目录下建立一个接口类：CustomerDAO，对一些方法进行了封装；
package com.lanqiao.javatest; import java.util.List; //创建以CustomerDAO接口，可以实现其他类的调用 public interface CustomerDAO { //模糊查询方法 public List<Customer> getForListWithCriteriaCustomer(CriteriaCustomer cc); //查询的方法，将数据库的所有数据放在list集合中，并实现输出的查询 public List<Customer> getAll(); // public void save(Customer customer); //通过jsp超链接里面？后面的id,获取该id下的各属性的值 public Customer get(Integer id); //通过jsp超链接里面？后面的id,并且实现删除的功能 public void delete(Integer id); //返回和该name相等的个数 public long getCountWithName(String name); void update(Customer customer); }
5.建一个DAO类，有得到某一个类方法明的反射方法，实现对数据的增删改查的方法
package com.lanqiao.javatest; import java.lang.reflect.ParameterizedType; import java.lang.reflect.Type; import java.sql.Connection; import java.util.List; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; /* * 封装了基本的增删改查方法，以供子类继承使用； * 当前dao没有事务，直接在方法中获取数据库的链接 * */ public class DAO <T>{ //这个是线程安全的 private QueryRunner queryRunner=new QueryRunner(); private Class<T> clazz; //类的构造方法 public DAO() { //得到父类带泛型的类型 //type类型导包为import java.lang.reflect.Type;反射类型里面的 //反射。。。。。。Type所有超级类接口，ParameterizedType表示参数化类型,参数化类型在反射方法首次需要时创建（在此包中指定）。 //当创建参数化类型 p 时，p 实例化的一般类型声明会被解析，并且按递归方式创建 p 的所有类型参数。 Type superClass=getClass().getGenericSuperclass(); if (superClass instanceof ParameterizedType) { ParameterizedType parameterizedType=(ParameterizedType) superClass; // getActualTypeArguments():返回表示此类型实际类型参数的 Type 对象的数组。 Type[] typeArgs=parameterizedType.getActualTypeArguments(); if (typeArgs!=null && typeArgs.length>0) { if (typeArgs[0] instanceof Class) { clazz=(Class<T>) typeArgs[0]; } } } } //返回某一字段的值：比如返回某一条记录的customerName,或返回有多少条记录 public <E> E getForValue(String sql,Object...args){ Connection connection=null; try { connection=JdbcUtils.getConnection(); return (E) queryRunner.query(connection,sql,new ScalarHandler(),args); } catch (Exception e) { e.printStackTrace(); }finally { JdbcUtils.releaseConnection(connection); } return null; } //返回所对应的list集合,获得的是一组的 public List<T> getForList(String sql,Object...args){ Connection connection=null; try { connection=JdbcUtils.getConnection(); return queryRunner.query(connection,sql,new BeanListHandler<>(clazz),args); } catch (Exception e) { e.printStackTrace(); }finally { JdbcUtils.releaseConnection(connection); } return null; } //返回对应的T的一个实体类的对象 public T get(String sql,Object...args){ Connection connection=null; try { connection=JdbcUtils.getConnection(); return queryRunner.query(connection,sql,new BeanHandler<>(clazz),args); } catch (Exception e) { e.printStackTrace(); }finally { JdbcUtils.releaseConnection(connection); } return null; } //该方法封装了增删改操作 public void update(String sql,Object...args){ Connection connection=null; try { connection=JdbcUtils.getConnection(); queryRunner.update(connection, sql, args); } catch (Exception e) { e.printStackTrace(); }finally { JdbcUtils.releaseConnection(connection); } } }
6.建立一个类：CustomerDAOJdbcImpl 继承于父类 DAO<Customer> 和继承于接口 CustomerDAO，增删改查方法和模糊查询的方法；
package com.lanqiao.javatest; import java.util.List; import com.lanqiao.javatest.CriteriaCustomer; import com.lanqiao.javatest.CustomerDAO; import com.lanqiao.javatest.DAO; //实现各个功能的类 public class CustomerDAOJdbcImpl extends DAO<Customer> implements CustomerDAO{ @Override //模糊查询的方法 public List<Customer> getForListWithCriteriaCustomer(CriteriaCustomer cc) { String sql="select id,name,address,phone from customer " + "where name like ? and address like ? and phone like ?"; //修改了CriteriaCustomer的get方法，使其返回的字符串中含有%% return getForList(sql,cc.getName(),cc.getAddress(),cc.getPhone()); } @Override //获取整个数据库中的所有数据 public List<Customer> getAll() { String sql1="select id,name,address,phone from customer"; return getForList(sql1); } @Override //插入数据到数据库的方法 public void save(Customer customer) { String sql2="insert into customer(name,address,phone) values(?,?,?)"; update(sql2, customer.getName(),customer.getAddress(),customer.getPhone()); } @Override //获取某个数据 public Customer get(Integer id) { String sql3="select id,name,address,phone from customer where id=?"; return get(sql3,id); } @Override //删除id=?的数据 public void delete(Integer id) { String sql4="delete from customer where id=?"; update(sql4, id); } @Override //用名字获取count(name)，即获取用户的数量 public long getCountWithName(String name) { String sql5="select count(name) from customer where name=?"; return getForValue(sql5, name); } @Override public void update(Customer customer) { String sql = "UPDATE customer SET name = ?, address = ?, phone = ? " + "WHERE id = ?"; update(sql, customer.getName(), customer.getAddress(), customer.getPhone(), customer.getId()); } }
7.建立一个Servlet类：doGet和doPost方法；
package com.lanqiao.javatest1; import java.io.IOException; import java.lang.reflect.Method; import java.util.List; import javax.management.Query; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import com.lanqiao.javatest.CriteriaCustomer; import com.lanqiao.javatest.Customer; import com.lanqiao.javatest.CustomerDAO; import com.lanqiao.javatest.CustomerDAOJdbcImpl; public class CustomerServlet extends HttpServlet { private static final long serialVersionUID = 1L; private CustomerDAO customerDAO=new CustomerDAOJdbcImpl(); protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request, response); } protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //利用反射，接受多个以.do结尾的请求 //1.变量 servletPath获取所有后边带.do的方法名和.do ,例如这个/pass.do String servletPath=request.getServletPath(); //2.在字符串后面除去.do这个字符 String methodName=servletPath.substring(1); methodName=methodName.substring(0,methodName.length()-3); //System.out.println(methodName); try { //反射获取该类的该方法 Method method=getClass().getDeclaredMethod (methodName, HttpServletRequest.class,HttpServletResponse.class); //这里面的this指通过这个方法的到方法名字，并且输出，及this指methodName的到的方法名字 method.invoke(this,request,response); } catch (Exception e) { //如果出现错误，去error页面 response.sendRedirect("error.jsp"); } } //查询方法 private void query(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //获取模糊查询的请求参数 String name=request.getParameter("name"); String address=request.getParameter("address"); String phone=request.getParameter("phone"); //把请求参数封装为一个CriteriaCustomer对象 CriteriaCustomer cc=new CriteriaCustomer(name,address,phone); //1.调用CustomerDAO的getForListWithCriteriaCustomer(cc)方法得到lists的集合 List<Customer> lists=customerDAO.getForListWithCriteriaCustomer(cc); //2.把list集合放到request中 request.setAttribute("list", lists); //3.转发页面到index.jsp(不能使用重定向) /代表的是根目录下的jsp文件； request.getRequestDispatcher("/index.jsp").forward(request, response); } //插入数据的方法： private void addCustomer(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{ //1.获取表单参数，name,address,phone String name=request.getParameter("name"); String address=request.getParameter("address"); String phone=request.getParameter("phone"); //检验名字是否已经被占用了:调用CustomerDAO的getCountWithName方法，获取name参数是否大于0，如果大于.... //并且消息可以回显:value="<%=request.getParameter("name")==null ? "" :%>" long count=customerDAO.getCountWithName(name); if (count>0) { request.setAttribute("message","用户名"+name+"已经被占用了，请重新选择！！！"); //名字重复了，请求的转发到/newcustomer.jsp request.getRequestDispatcher("/newcustomer.jsp").forward(request, response); //结束方法 return ; } //2.若验证通过的话，把表单参数封装为一个customer的对象 Customer customer=new Customer(name,address,phone); //3.调用CustomerDAO的save方法执行保存 customerDAO.save(customer); //数据插入成功后，请求的转发到newcustomer.jsp //request.getRequestDispatcher("/newcustomer.jsp").forward(request, response); //4.数据插入成功后，重定向到success.jsp页面：使用重定向可以避免出现表单的重复提交问题. response.sendRedirect("success.jsp"); } private void delete(HttpServletRequest request, HttpServletResponse response) throws IOException { //1.获取请求的id String idStr=request.getParameter("id"); int id=0; try { id=Integer.parseInt(idStr); //2.调用CustomerDAO的getId()方法执行删除 customerDAO.delete(id); } catch (Exception e) { response.sendRedirect("query.do"); } //重定向的页面jsp,其前面不用加"/" response.sendRedirect("success.jsp"); } //修改数据表里的数据 private void edit(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //出现了所查询的数据不存在的错误显示到error.jsp页面 String forwardPath="/error.jsp"; //1.获取请求的id String idStr=request.getParameter("id"); //2. 调用 CustomerDAO 的 customerDAO.get(id) 获取和 id 对应的 Customer 对象 customer try { Customer customer=customerDAO.get(Integer.parseInt(idStr)); //如果数据存在的跳转到updatecustomer.jsp页面，进行修改数据 if(customer!=null){ forwardPath="/updatecustomer.jsp"; //将数据放到request请求的转发的里面 request.setAttribute("customer", customer); } } catch (Exception e) { System.out.println("fsdlkf"); } //4. 响应 updatecustomer.jsp 页面: 转发. request.getRequestDispatcher(forwardPath).forward(request, response); } private void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //1. 获取表单参数: id, name, address, phone, oldName String id=request.getParameter("id"); String name=request.getParameter("name"); String address=request.getParameter("address"); String phone=request.getParameter("phone"); String oldName=request.getParameter("oldName"); //2. 检验 name 是否已经被占用: //2.1 比较 name 和 oldName 是否相同, 若相同说明 name 可用. //2.1 若不相同, 则调用 CustomerDAO 的 getCountWithName(String name) 获取 name 在数据库中是否存在 if(!oldName.equalsIgnoreCase(name)){ long count=customerDAO.getCountWithName(name); //2.2 若返回值大于 0, 则响应 updatecustomer.jsp 页面: 通过转发的方式来响应 newcustomer.jsp if (count>0) { //2.2.1 在 updatecustomer.jsp 页面显示一个错误消息: 用户名 name 已经被占用, 请重新选择! //在 request 中放入一个属性 message: 用户名 name 已经被占用, 请重新选择!, //在页面上通过 request.getAttribute("message") 的方式来显示 request.setAttribute("message", "用户名"+name+"已经被占用，请重现选择！！！"); //2.2.2 newcustomer.jsp 的表单值可以回显. //address, phone 显示提交表单的新的值, 而 name 显示 oldName, 而不是新提交的 name //2.2.3 结束方法: return request.getRequestDispatcher("updatecustomer.jsp").forward(request, response); return ; } } //3. 若验证通过, 则把表单参数封装为一个 Customer 对象 customer Customer customer=new Customer(name,address,phone); customer.setId(Integer.parseInt(id)); //4. 调用 CustomerDAO 的 update(Customer customer) 执行更新操作 customerDAO.update(customer); //5. 重定向到 query.do response.sendRedirect("query.do"); } }
8.在WEB-INF下的web.xml文件；
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
<display-name>day-11</display-name>
<servlet>
<description></description>
<display-name>CustomerServlet1</display-name>
<servlet-name>CustomerServlet1</servlet-name>
<servlet-class>com.lanqiao.javatest1.CustomerServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>CustomerServlet1</servlet-name>
<url-pattern>*.do</url-pattern>
</servlet-mapping>
</web-app>
9.几个jsp页面；
(1)index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>Insert title here</title> <script type="text/javascript" src="scripts/jquery-1.7.2.js"></script> <script type="text/javascript"> $(function(){ $(".delete12").click(function(){ var content = $(this).parent().parent().find("td:eq(1)").text(); var flag = confirm("确定要是删除" + content + "的信息吗?"); return flag; }); }); </script> </head> <body> <form action="query.do" method="post"> <table> <tr> <td>Name:</td> <td><input type="text" name="name"/></td> </tr> <tr> <td>Address:</td> <td><input type="text" name="address"/></td> </tr> <tr> <td>Phone:</td> <td><input type="text" name="phone"/></td> </tr> <tr> <td><input type="submit" value="Query"/></td> <td><a href="newcustomer.jsp">Add New Customer</a></td> </tr> </table> </form> <br><br> <c:if test="${!empty requestScope.list }"> <hr> <br><br> <table border="1" cellpadding="10" cellspacing="0"> <tr> <th>id</th> <th>name</th> <th>address</th> <th>phone</th> <th>update</th> <th>delete</th> </tr> <c:forEach items="${requestScope.list }" var="cust"> <tr> <td>${cust.id }</td> <td>${cust.name }</td> <td>${cust.address }</td> <td>${cust.phone }</td> <td> <c:url value="/edit.do" var="editurl"> <c:param name="id" value="${cust.id }"></c:param> </c:url> <a href="${editurl }">UPDATE</a> <td/> <c:url value="/delete.do" var="deleteurl"> <c:param name="id" value="${cust.id }"></c:param> </c:url> <a href="${deleteurl }" class="delete12">DELETE</a> </tr> </c:forEach> </table> </c:if> </body> </html>
(2)newcustomer.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>Insert title here</title> </head> <body> <c:if test="${requestScope.message != null }"> <br> <font color="red">${requestScope.message }</font> <br> <br> </c:if> <form action="addCustomer.do" method="post"> <table> <tr> <td>Name:</td> <td><input type="text" name="name" value="${param.name }"/></td> </tr> <tr> <td>Address:</td> <td><input type="text" name="address" value="${param.address }"/></td> </tr> <tr> <td>Phone:</td> <td><input type="text" name="phone" value="${param.phone }"/></td> </tr> <tr> <td colspan="2"><input type="submit" value="Submit"/></td> </tr> </table> </form> </body> </html>
(3)updatecustomer.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>Insert title here</title> </head> <body> <c:if test="${requestScope.message != null }"> <br> <font color="red">${requestScope.message }</font> <br> <br> </c:if> <c:set var="id" value="${customer != null ? customer.id : param.id }"></c:set> <c:set var="oldName" value="${customer != null ? customer.name : param.oldName }"></c:set> <c:set var="name" value="${customer != null ? customer.name : param.oldName }"></c:set> <c:set var="address" value="${customer != null ? customer.address : param.address }"></c:set> <c:set var="phone" value="${customer != null ? customer.phone : param.phone }"></c:set> <form action="update.do" method="post"> <input type="hidden" name="id" value="${id }"/> <input type="hidden" name="oldName" value="${oldName }"/> <table> <tr> <td>Name:</td> <td><input type="text" name="name" value="${name }"/></td> </tr> <tr> <td>Address:</td> <td><input type="text" name="address" value="${address }"/></td> </tr> <tr> <td>Phone:</td> <td><input type="text" name="phone" value="${phone }"/></td> </tr> <tr> <td colspan="2"><input type="submit" value="Submit"/></td> </tr> </table> </form> </body> </html>
(4)success.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<h2>操作成功！！！</h2>
<a href="index.jsp">Return....</a>
</body>
</html>
(5)error.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<h3>不好意思页面无法打开！！！</h3>
</body>
</html>
1018
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
