OpenLayers学习笔记8——使用servlet从mysql获取数据并标注

你的名字 2022-08-01 06:07 111阅读 0赞

1、服务器端Servlet代码：

package edu.whu.vge.servlet;
    
    import java.io.IOException;
    import java.io.PrintWriter;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import net.sf.json.JSONArray;
    import net.sf.json.JSONObject;
    
    import edu.whu.vge.dbUtil.PoiDBBean;
    
    public class SchoolQueryServlet extends HttpServlet {
    
    	private static final long serialVersionUID = 1L;
    
    	/*
    	 * (non-Javadoc)
    	 * 
    	 * @see
    	 * javax.servlet.http.HttpServlet#doPost(javax.servlet.http.HttpServletRequest
    	 * , javax.servlet.http.HttpServletResponse)
    	 */
    	@Override
    	protected void doPost(HttpServletRequest request,
    			HttpServletResponse response) throws ServletException, IOException {
    		// 设置输出内容格式和编码
    		response.setContentType("text/html;charset=utf-8");
    		PrintWriter out = response.getWriter();
    		// 设置接收参数编码格式
    		response.setCharacterEncoding("utf-8");
    		String schoolID = request.getParameter("schoolID");
    		String schoolName = request.getParameter("schoolName");
    		String schoolAddress = request.getParameter("schoolAddress");
    		String sql = "select * from school where ID like " + "\'" + "%"
    				+ schoolID + "%" + "\'" + " and name like " + "\'" + "%"
    				+ schoolName + "%" + "\'" + " and address like " + "\'" + "%"
    				+ schoolAddress + "%" + "\'";
    		PoiDBBean poiDBBean = new PoiDBBean();
    
    		//
    		JSONArray array = new JSONArray();
    		try {
    			ResultSet resultSet = poiDBBean.query(sql);
    			while (resultSet.next()) {
    				JSONObject object = new JSONObject()
    						.element("schoolName", resultSet.getString(4))
    						.element("schoolAddress", resultSet.getString(5))
    						.element("schoolTel", resultSet.getString(6))
    						.element("schoolKind", resultSet.getString(7))
    						.element("schoolLat", resultSet.getDouble(3))
    						.element("schoolLon", resultSet.getDouble(2));
    				array.add(object);
    
    			}
    			out.println(array.toString());
    			System.out.println(array.toString());
    
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} catch (ClassNotFoundException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} finally {
    			try {
    				poiDBBean.close();
    			} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    		}
    
    	}
    
    	public void destroy() {
    		super.destroy(); // Just puts "destroy" string in log
    		// Put your code here
    	}
    
    	/**
    	 * Initialization of the servlet. <br>
    	 * 
    	 * @throws ServletException
    	 *             if an error occurs
    	 */
    	public void init() throws ServletException {
    		// Put your code here
    	}
    
    }

这里看一下servlet的知识就可以了。

## 2、客户端请求代码： ##

这里采用jquery  ajax方法异步请求服务器端servlet，jquery及ajax见： [w3cschool][]。代码如下：

$.ajax({
            
                url: "http://127.0.0.1:8080/taxGIS/servlet/SchoolQueryServlet",
                type: 'post',
                dataType: 'json',
                data: {
                    schoolID: $("#schoolID").val(),
                    schoolName: $("#schoolName").val(),
                    schoolAddress: $("#schoolAddress").val()
                },
                success: function(jsonData){
                    //查询返回数据后标注在地图上并启用查询结果显示对话框
                    patchAddMarker(jsonData);
                    showQueryRes(jsonData);
                }
                
            });

## 3、解析json并标注在地图上： ##

以一次查询为例，得到的json数据如下：

[
        {
            "schoolName": "城阳区第二实验中学", 
            "schoolAddress": "山东省青岛市城阳区礼阳路107号", 
            "schoolTel": "0532-81156666", 
            "schoolKind": "教育学校:中学", 
            "schoolLat": 36.28491, 
            "schoolLon": 120.40238
        }, 
        {
            "schoolName": "小寨子幼儿园", 
            "schoolAddress": "山东省青岛市城阳区 ", 
            "schoolTel": " ", 
            "schoolKind": "教育学校:幼儿园", 
            "schoolLat": 36.29791, 
            "schoolLon": 120.39172
        }, 
        {
            "schoolName": "新太阳托管中心", 
            "schoolAddress": "山东省青岛市市北区嘉兴路11-2", 
            "schoolTel": " ", 
            "schoolKind": "教育学校:幼儿园", 
            "schoolLat": 36.10921, 
            "schoolLon": 120.35659
        }, 
        {
            "schoolName": "金苹果幼儿园(香江医院西)", 
            "schoolAddress": "山东省青岛市黄岛区香江路311号(香江医院西)", 
            "schoolTel": " ", 
            "schoolKind": "教育学校:幼儿园", 
            "schoolLat": 35.97422, 
            "schoolLon": 120.16204
        }, 
        {
            "schoolName": "锦桥社区托辅中心", 
            "schoolAddress": "山东省青岛市黄岛区王家石桥村锦桥社区", 
            "schoolTel": " ", 
            "schoolKind": "教育学校:幼儿园", 
            "schoolLat": 35.87418, 
            "schoolLon": 120.00619
        }, 
        {
            "schoolName": "艺星幼儿园", 
            "schoolAddress": "山东省青岛市城阳区岙东北路541", 
            "schoolTel": " ", 
            "schoolKind": "教育学校:幼儿园", 
            "schoolLat": 36.33419, 
            "schoolLon": 120.2701
        }, 
        {
            "schoolName": "隐珠街道办事处中心幼儿园", 
            "schoolAddress": "山东省青岛市胶南市灵海路126号", 
            "schoolTel": " ", 
            "schoolKind": "教育学校:幼儿园", 
            "schoolLat": 35.89869, 
            "schoolLon": 120.0444
        }, 
        {
            "schoolName": "慧诺托管家园", 
            "schoolAddress": "山东省青岛市黄岛区虹桥大街83-5", 
            "schoolTel": " ", 
            "schoolKind": "教育学校:幼儿园", 
            "schoolLat": 35.87653, 
            "schoolLon": 120.00338
        }, 
        {
            "schoolName": "四方区尚志幼儿园", 
            "schoolAddress": "山东省青岛市市北区尚志路4", 
            "schoolTel": " ", 
            "schoolKind": "教育学校:幼儿园", 
            "schoolLat": 36.11456, 
            "schoolLon": 120.35691
        }, 
        {
            "schoolName": "宝贝之家亲子园", 
            "schoolAddress": "山东省青岛市崂山区松岭路58-1", 
            "schoolTel": "(0532)88891918,(0532)88893227", 
            "schoolKind": "教育学校:幼儿园", 
            "schoolLat": 36.10406, 
            "schoolLon": 120.47963
        }, 
        {
            "schoolName": "天真幼儿园(灵山卫街道办事处人大工作办公室北)", 
            "schoolAddress": "山东省青岛市黄岛区329省道(灵山卫街道办事处人大工作办公室北)", 
            "schoolTel": " ", 
            "schoolKind": "教育学校:幼儿园", 
            "schoolLat": 35.93806, 
            "schoolLon": 120.15021
        }, 
        {
            "schoolName": "城阳区城阳街道皂户幼儿园", 
            "schoolAddress": "山东省青岛市城阳区正阳中路", 
            "schoolTel": " ", 
            "schoolKind": "教育学校:幼儿园", 
            "schoolLat": 36.30772, 
            "schoolLon": 120.35847
        }, 
        {
            "schoolName": "胶南市王台镇石梁小学", 
            "schoolAddress": "山东省青岛市黄岛区 ", 
            "schoolTel": "0532-83116752", 
            "schoolKind": "教育学校:小学", 
            "schoolLat": 36.07053, 
            "schoolLon": 120.03613
        }
    ]

json解析可以采用json2.js也可以使用js的eval函数。这里采用后者，代码如下，很简单就不做解释了。

## 4、表格显示： ##

5、总结

1、遇到跨域问题，客户端请求不到访问结果。解决办法：访问地址为：http://127.0.0.1:8080/taxGIS/index.jsp；而不是http://localhost:8080/taxGIS/index.jsp

2、jquery-ui和jquery easyui冲突问题。这两个都是基于jquery，有些方法和属性名称都是相同的，导致冲突，就看引入其js的先后顺序，后引入的覆盖先引入的。我这里主要用到jquery-ui的对话框和按钮以及折叠栏，而jquery easyui只用到了datagrid，所以我的引用是这样的：jquery--->jquery-ui--->jquery easyui

3、前端开发用到的知识比较多而且杂，不像C\#、java开发那般只用一种语言。刚开始可能会觉得手足无措，但是只要用心，入了门后面就会轻松些了。

4、注重开发部署的便宜性、用户体验度，不能做出来的东西部署非常复杂而又不实用。

[w3cschool]: http://www.w3school.com.cn/jquery/jquery_ref_ajax.asp