【笔记】c3p0+dbUtils+Ajax+Json实现【增删改查】Demo

不解释，下方有效果图！支持源码下载哦！

【c3p0-config.xml】

<c3p0-config>
    <default-config>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/athl_ajax</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="initialPoolSize">3</property>  
        <!-- 连接的最大空闲时间  单位秒 默认是0-代表永远不会断开连接  超过设定时间的空闲连接将会断开 -->  
        <property name="maxIdleTime">30</property>  
        <!-- 连接池中拥有的最大连接数 默认值为15个 -->  
        <property name="maxPoolSize">20</property>  
        <!-- 连接池中保持的最小连接数  默认值为3个-->  
        <property name="minPoolSize">3</property>  
        <!-- 将连接池的连接数保持在minpoolsize 必须小于maxIdleTime设置  默认值为0代表不处理  单位秒 -->  
        <property name="maxIdleTimeExcessConnections">15</property>
    </default-config>
</c3p0-config>

【Bean】

package com.athl.bean;

public class Person {
    private int id;
    private String name;
    private int age;
    略
}

【Dao 】

package com.athl.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.athl.bean.Person;
import com.athl.utils.JdbcUtils;

public class AjaxDao {

    private QueryRunner qr=JdbcUtils.getQuerrRunner();
    public void add(Person p){
        String sql="insert into person(name,age) values(?,?)";
        try {
            qr.update(sql,p.getName(),p.getAge());
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    public void updata(Person p){
        String sql="update person set name=?,age=? where id=?";
        try {
            qr.update(sql,p.getName(),p.getAge(),p.getId());
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    public void del(int id){
        String sql="delete from person where id=?";
        try {
            qr.update(sql,id);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }
    public List<Person> query(){
        String sql="select * from person";
        try {
            return qr.query(sql,new BeanListHandler<Person>(Person.class));
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }
    public List<Person> queryLike(String search){
        String sql="select * from person where name like ? or age like ?";
        try {
            return qr.query(sql,new BeanListHandler<Person>(Person.class),"%"+search+"%","%"+search+"%");
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

}

【Servlet 】

package com.athl.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.JSONArray;

import com.athl.bean.Person;
import com.athl.dao.AjaxDao;
import com.sun.org.apache.commons.beanutils.BeanUtils;

public class AjaxServlet extends HttpServlet {

    private static final long serialVersionUID = 1L;
    private static AjaxDao dao=new AjaxDao();
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        doGet(request, response);
    }
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        String m=request.getParameter("m");
        if("ajaxAdd".equals(m)){
            ajaxAdd(request, response);
        }else if("ajaxDel".equals(m)){
            ajaxDel(request, response);
        }else if("ajaxQuery".equals(m)){
            ajaxQuery(request, response);
        }else if("ajaxUpdata".equals(m)){
            ajaxUpdata(request, response);
        }else if("ajaxQueryLike".equals(m)){
            ajaxQueryLike(request, response);
        }
    }

    public void ajaxQueryLike(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=utf-8");
        String s = request.getParameter("search");
        if(s!=null&&!"".equals(s)){
            JSONArray arr=new JSONArray(dao.queryLike(s));
            response.getWriter().write(arr.toString());
        }else{
            ajaxQuery(request, response);
        }
    }

    public void ajaxQuery(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=utf-8");
        JSONArray arr=new JSONArray(dao.query());
        response.getWriter().write(arr.toString());
    }


    public void ajaxAdd(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        Person p = new Person();
        String name =request.getParameter("name");
        String ageStr=request.getParameter("age");
        int age=0;
        if(!"".equals(ageStr)||ageStr!=null){
            age=Integer.valueOf(ageStr);
        }
        try {
            BeanUtils.setProperty(p, "name", name);
            BeanUtils.setProperty(p, "age", age);
            dao.add(p);
        } catch (Exception e) {
            e.printStackTrace();
        } 
    }

    public void ajaxUpdata(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        String idStr =request.getParameter("id");
        String name =request.getParameter("name");
        String ageStr=request.getParameter("age");
        int id=0;
        if(!"".equals(idStr)||idStr!=null){
            id=Integer.valueOf(idStr);
        }
        int age=0;
        if(!"".equals(ageStr)||ageStr!=null){
            age=Integer.valueOf(ageStr);
        }
        try {
            Person p = new Person(id,name,age);
            dao.updata(p);
        } catch (Exception e) {
            e.printStackTrace();
        } 
    }

    public void ajaxDel(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=utf-8");
        String idStr =request.getParameter("id");
        int id=0;
        if(!"".equals(idStr)||idStr!=null){
            id=Integer.valueOf(idStr);
        }
        try {
            dao.del(id);
        } catch (Exception e) {
            e.printStackTrace();
        } 
    }

}

【Filter】

package com.athl.filter;

import java.io.IOException;

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;

public class Myfilter implements Filter{

    public void destroy() {
    }

    public void doFilter(ServletRequest request, ServletResponse response,
            FilterChain chain) throws IOException, ServletException {
        request.setCharacterEncoding("utf-8");

        HttpServletRequest req = (HttpServletRequest) request;
        // 如果是get请求,交给EncodingRequest类处理
        if (req.getMethod().equals("GET")) {
            EncodingRequest er = new EncodingRequest(req);
            chain.doFilter(er, response);
        } else if (req.getMethod().equals("POST")) {
            chain.doFilter(request, response);
        }
    }

    public void init(FilterConfig arg0) throws ServletException {
    }

}

【index.jsp】

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">

    <title>My JSP 'index.jsp' starting page</title>
    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0">    
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="This is my page">
    <script type="text/javascript" src="jquery-1.9.1.js"></script>
</head>
<style type="text/css">
    *{
        margin: 0;
        padding: 0;
    }
    body{
        overflow-y : scroll;
    }
    #main{
        background-color: #00EE76;
        width: 622px;
        min-height: 300px;
        padding: 20px 5px 45px 5px;
        border-radius: 9px;
        box-shadow: 3px 3px 5px rgba(0, 0, 0, 0.5);
    }
    table{
        width:612px;
        text-align:center;
        border-collapse:collapse;
        color: #4F4F4F;
        font-size: 16px;
    }
    tr{
        height:35px;
        border:3px solid #9C9C9C;
    }
    #title th{

        height:45px;
        border:3px solid #9C9C9C;
        background: #FFDEAD;
        /* width: 200px */
    }

    td{
        height:25px;
        border:3px solid #9C9C9C;
    }
    table td input{
        width: 96%;
        height: 96%;
    }
    a{
        cursor: pointer;
    }
</style>
<body>
    <center>
    <div id="main">
        <table>
            <caption><h2>花 名 册</h2><input type="button" id="btn" value="查询"><input id="search" name="search" placeholder="姓名、年龄" type="text"></caption>
            <tr id="title">
                <th width="10%">ID</th>
                <th width="25%">姓名</th>
                <th width="25%">年龄</th>
                <th>操作/<a id="tj">添加</a></th>
            </tr>
        </table>
    </div>
    </center>
    <script type="text/javascript">
        $(function(){
            query();
            $('#btn').css({'height':'35px','width':'50px','cursor':'pointer','float':'right'});
            $('#search').css({'height':'35px','width':'150px','float':'right'});
        });
        $('#tj').click(function() {
            $('table').append($("<tr><td></td><td class='name'><input name='name' id='name' type='text'/></td><td class='age'><input name='age' id='age' type='text'/></td><td><a onclick='sc(this);'>删除</a><a onclick='bc(this);'> | 保存</a></td></tr>"));
        });

        $('#btn').click(function(){
            var search = $('#search').val();
            $.ajax({
                url:"Ajax?m=ajaxQueryLike",
                data:{search:search},
                dataType:'json',
                type:'post',
                success:function(msg){
                    $('table tr:not(:first)').remove();
                    var str;
                    for(var i=0;i<msg.length;i++){
                        str=$("<tr><td class='id'>"+msg[i].id+"</td><td class='name'>"+msg[i].name+"</td><td class='age'>"+msg[i].age+"</td><td class='action'><a onclick='xg(this);'>修改</a><a onclick='sc("+msg[i].id+");'> | 删除</a></td></tr>");
                        $('table').append(str);        
                    }
                }
            });

        });

        function query(){
            $.get("Ajax?m=ajaxQuery",function(msg){
                msg=eval(msg);
                $('table tr:not(:first)').remove();
                var str;
                for(var i=0;i<msg.length;i++){
                    str=$("<tr><td class='id'>"+msg[i].id+"</td><td class='name'>"+msg[i].name+"</td><td class='age'>"+msg[i].age+"</td><td class='action'><a onclick='xg(this);'>修改</a><a onclick='sc("+msg[i].id+");'> | 删除</a></td></tr>");
                    $('table').append(str);        
                }
            });
        }

        function xg(obj){
                var trf=$(obj).parent().parent();
                var value1=trf.children('.name').text();
                trf.children('.name').text("");
                var value2=trf.children('.age').text();
                trf.children('.age').text("");
                trf.children('.name').append($("<input type='text'/>"));
                trf.children('.age').append($("<input type='text'/>"));
                trf.children('.name').children().val(value1);
                trf.children('.age').children().val(value2);
                trf.children('.action').children('a:first').attr("onclick","xg2(this)");
                trf.children('.action').children('a:first').text("保存");
            };
            function xg2(obj){
                var trf=$(obj).parent().parent();
                var id=trf.children('.id').text();
                var name=trf.children('.name').children().val();
                var age=trf.children('.age').children().val();
                if(name!=""&&age!=""){
                    $.get("Ajax?m=ajaxUpdata&id="+id+"&name="+name+"&age="+age+"");
                    query();
                }else{
                    alert("不能为空！");
                }
            }
            function sc(id){
                if(id!=""&&id!=null){
                    $.get("Ajax?m=ajaxDel&id="+id);
                }
                query();
            };
            function bc(obj) {
                var trf=$(obj).parent().parent();
                var name=trf.children('.name').children().val();
                var age=trf.children('.age').children().val();
                trf.remove();
                if(name!=""&&age!=""){
                    $.get("Ajax?m=ajaxAdd&name="+name+"&age="+age);
                    query();
                }else{
                    alert("不能为空！");
                }
            };

    </script>
</body>
</html>

【效果图】

源码下载：http://download.csdn.net/detail/jul_11th/9734103

谢谢支持！