<%@ page contentType="text/plain; charset=UTF-8"%>
<%@ page language="java"%>
<%@ page import="java.sql.*,ajax.db.DBUtils,org.json.simple.JSONObject,org.json.simple.JSONArray"%>
<%!
    //根據(jù)索引標記獲取名片列表
    String getAddressList(String indexKey) {
        JSONArray array = new JSONArray();          //生成一個JSONArray對象

        //根據(jù)參數(shù)不同選擇不同的SQL語句
        String sql = null;
        if ("".equals(indexKey)) {
            //獲取全部名片
            sql = "select id, name from address order by name asc";
        } else {
            //獲取與索引匹配的名片
            sql = "select id, name from address where firstpy(name) = ? order by name asc";
        }
        Connection conn = null;                     //聲明Connection對象
        PreparedStatement pstmt = null;             //聲明PreparedStatement對象
        ResultSet rs = null;                        //聲明ResultSet對象
        try {
            conn = DBUtils.getConnection();         //獲取數(shù)據(jù)庫連接
            pstmt = conn.prepareStatement(sql);     //根據(jù)sql創(chuàng)建PreparedStatement
            if (!"".equals(indexKey)) {
                pstmt.setString(1, indexKey);
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {                     //遍歷結(jié)果集
                JSONObject obj = new JSONObject();  //創(chuàng)建一個JSONObject對象
                obj.put("id", rs.getString(1));
                obj.put("name", rs.getString(2));
                array.add(obj);                     //將JSONObject對象放入JSONArray對象中
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
        } finally {
            DBUtils.close(rs);                      //關(guān)閉結(jié)果集
            DBUtils.close(pstmt);                   //關(guān)閉PreparedStatement
            DBUtils.close(conn);                    //關(guān)閉連接
        }
        return array.toString();                    //以JSON格式返回信息
    }

    //獲取名片詳細信息
    String getAddress(String id) {
        JSONObject obj = new JSONObject();      //創(chuàng)建一個JSONObject對象
        String sql = "select id, name, tel, email from address where id = ?";//定義SQL語句
        Connection conn = null;                 //聲明Connection對象
        PreparedStatement pstmt = null;         //聲明PreparedStatement對象
        ResultSet rs = null;                    //聲明ResultSet對象
        try {
            conn = DBUtils.getConnection();     //獲取數(shù)據(jù)庫連接
            pstmt = conn.prepareStatement(sql); //根據(jù)sql創(chuàng)建PreparedStatement
            pstmt.setString(1, id);             //設(shè)置參數(shù)
            rs = pstmt.executeQuery();
            if (rs.next()) {
                obj.put("id", rs.getString(1));
                obj.put("name", rs.getString(2));
                obj.put("tel", rs.getString(3));
                obj.put("email", rs.getString(4));
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
        } finally {
            DBUtils.close(rs);                  //關(guān)閉結(jié)果集
            DBUtils.close(pstmt);               //關(guān)閉PreparedStatement
            DBUtils.close(conn);                //關(guān)閉連接
        }
        return obj.toString();                  //以JSON格式返回信息
    }

    //添加名片信息
    void addAddress(String name, String tel, String email) {
        String sql = "insert into address(name, tel, email) values(?,?,?)";//定義SQL語句
        Connection conn = null;                 //聲明Connection對象
        PreparedStatement pstmt = null;         //聲明PreparedStatement對象
        try {
            conn = DBUtils.getConnection();     //獲取數(shù)據(jù)庫連接
            pstmt = conn.prepareStatement(sql); //根據(jù)sql創(chuàng)建PreparedStatement
            pstmt.setString(1, name);
            pstmt.setString(2, tel);
            pstmt.setString(3, email);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.toString());
        } finally {
            DBUtils.close(pstmt);               //關(guān)閉PreparedStatement
            DBUtils.close(conn);                //關(guān)閉連接
        }
    }

    //刪除名片信息
    void delAddress(String id) {
        String sql = "delete from address where id = ?";//定義SQL語句
        Connection conn = null;                 //聲明Connection對象
        PreparedStatement pstmt = null;         //聲明PreparedStatement對象
        try {
            conn = DBUtils.getConnection();     //獲取數(shù)據(jù)庫連接
            pstmt = conn.prepareStatement(sql); //根據(jù)sql創(chuàng)建PreparedStatement
            pstmt.setString(1, id);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.toString());
        } finally {
            DBUtils.close(pstmt);               //關(guān)閉PreparedStatement
            DBUtils.close(conn);                //關(guān)閉連接
        }
    }

    //更新名片信息
    void updateAddress(String id, String name, String tel, String email) {
        String sql = "update address set name = ?, tel = ?, email = ? where id = ?";//定義SQL語句
        Connection conn = null;                 //聲明Connection對象
        PreparedStatement pstmt = null;         //聲明PreparedStatement對象
        try {
            conn = DBUtils.getConnection();     //獲取數(shù)據(jù)庫連接
            pstmt = conn.prepareStatement(sql); //根據(jù)sql創(chuàng)建PreparedStatement
            pstmt.setString(1, name);
            pstmt.setString(2, tel);
            pstmt.setString(3, email);
            pstmt.setString(4, id);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.toString());
        } finally {
            DBUtils.close(pstmt);               //關(guān)閉PreparedStatement
            DBUtils.close(conn);                //關(guān)閉連接
        }
    }
%>
<%
    out.clear();                                            //清空當前的輸出內(nèi)容（空格和換行符）
    request.setCharacterEncoding("UTF-8");                  //設(shè)置請求字符集為UTF-8

    String action = request.getParameter("action");         //獲取action參數(shù)

    //根據(jù)action參數(shù)不同執(zhí)行不同的數(shù)據(jù)庫操作
    if ("getAddressList".equals(action)) {                  //執(zhí)行根據(jù)索引標記獲取名片列表操作
        String indexKey = request.getParameter("indexKey");
        out.print(getAddressList(indexKey));
    } else if ("getAddress".equals(action)) {               //執(zhí)行獲取名片詳細信息操作
        String id = request.getParameter("id");
        out.print(getAddress(id));
    } else if ("addAddress".equals(action)) {               //執(zhí)行添加名片信息操作
        String name = request.getParameter("name");
        String tel = request.getParameter("tel");
        String email = request.getParameter("email");
        addAddress(name, tel, email);
        out.print("用戶信息保存成功。");
    } else if ("delAddress".equals(action)) {               //執(zhí)行刪除名片信息操作
        String id = request.getParameter("id");
        delAddress(id);
        out.print("用戶信息刪除成功。");
    } else if ("updateAddress".equals(action)) {            //執(zhí)行更新名片信息操作
        String id = request.getParameter("id");
        String name = request.getParameter("name");
        String tel = request.getParameter("tel");
        String email = request.getParameter("email");
        updateAddress(id, name, tel, email);
        out.print("用戶信息保存成功。");
    }
%>