69期-Java SE-053_AJAX-3

location.sql

-- MySQL dump 10.13  Distrib 8.0.11, for macos10.13 (x86_64)
--
-- Host: 127.0.0.1    Database: test
-- ------------------------------------------------------
-- Server version    8.0.11

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
 SET NAMES utf8mb4 ;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `city`
--

DROP TABLE IF EXISTS `city`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 SET character_set_client = utf8mb4 ;
CREATE TABLE `city` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(11) DEFAULT NULL,
  `pid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `pro` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `city`
--

LOCK TABLES `city` WRITE;
/*!40000 ALTER TABLE `city` DISABLE KEYS */;
INSERT INTO `city` VALUES (1,'西安市',1),(2,'渭南市',1),(3,'宝鸡市',1),(4,'郑州市',2),(5,'洛阳市',2),(6,'南京市',3),(7,'南通市',3),(8,'苏州市',3),(9,'榆林市',1);
/*!40000 ALTER TABLE `city` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `pro`
--

DROP TABLE IF EXISTS `pro`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 SET character_set_client = utf8mb4 ;
CREATE TABLE `pro` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `pro`
--

LOCK TABLES `pro` WRITE;
/*!40000 ALTER TABLE `pro` DISABLE KEYS */;
INSERT INTO `pro` VALUES (1,'陕西省'),(2,'河南省'),(3,'江苏省');
/*!40000 ALTER TABLE `pro` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `area`
--

DROP TABLE IF EXISTS `area`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 SET character_set_client = utf8mb4 ;
CREATE TABLE `area` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(11) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cid` (`cid`),
  CONSTRAINT `area_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `city` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `area`
--

LOCK TABLES `area` WRITE;
/*!40000 ALTER TABLE `area` DISABLE KEYS */;
INSERT INTO `area` VALUES (1,'新城区',1),(2,'莲湖区',1),(3,'高新区',2),(4,'东湖区',3),(5,'福田区',4),(6,'雁塔区',1);
/*!40000 ALTER TABLE `area` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2019-07-25 22:04:42

 index.jsp

<%--
  Created by IntelliJ IDEA.
  User: southwind
  Date: 2019-07-25
  Time: 20:10
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<script type="text/javascript" src="js/jquery-1.8.3.min.js"></script>
<script type="text/javascript">
  $(function(){
    $("#pro").change(function () {
      var id = $("#pro").val();
      $.ajax({
          url:"/location.do",
          type:"POST",
          data:{
              method:"findCityByPid",
              pid:id
          },
          dataType:"JSON",
          success:function(data){
            var content = "";
              for(var i=0;i<data.length;i++){
                content += "<option value='"+data[i].id+"'>"+data[i].name+"</option>";
              }
              $("#city").html(content);
          }
      });
    });
  });
</script>
<html>
  <head>
    <title>$Title$</title>
  </head>
  <body>
  <select id="pro">
    <c:forEach items="${requestScope.list}" var="pro">
      <option value="${pro.id}">${pro.name}</option>
    </c:forEach>
  </select>
  <select id="city">
    <c:forEach items="${requestScope.cities}" var="city">
      <option value="${city.id}">${city.name}</option>
    </c:forEach>
  </select>
  <select id="area">
    <c:forEach items="${requestScope.areas}" var="area">
      <option value="${area.id}">${area.name}</option>
    </c:forEach>
  </select>
  </body>
</html>

 web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
         version="4.0">
</web-app>

 c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>

    <named-config name="testc3p0">

        <!-- 指定连接数据源的基本属性 -->
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=UTF-8</property>

        <!-- 若数据库中连接数不足时, 一次向数据库服务器申请多少个连接 -->
        <property name="acquireIncrement">5</property>
        <!-- 初始化数据库连接池时连接的数量 -->
        <property name="initialPoolSize">5</property>
        <!-- 数据库连接池中的最小的数据库连接数 -->
        <property name="minPoolSize">5</property>
        <!-- 数据库连接池中的最大的数据库连接数 -->
        <property name="maxPoolSize">10</property>

    </named-config>

</c3p0-config>

 LocationServlet.java

package com.southwind.controller;

import com.southwind.entity.City;
import com.southwind.entity.Province;
import com.southwind.service.LocationService;
import com.southwind.service.impl.LocationServiceImpl;
import net.sf.json.JSONArray;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

@WebServlet("/location.do")
public class LocationServlet extends HttpServlet {

    private LocationService locationService = new LocationServiceImpl();

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        this.doPost(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setCharacterEncoding("UTF-8");
        String method = req.getParameter("method");
        if(method == null){
            method = "index";
        }
        switch (method){
            case "index":
                List<Province> list = locationService.findAll();
                req.setAttribute("list",list);
                req.setAttribute("cities", locationService.findCityByPid(1L));
                req.setAttribute("areas", locationService.findAreaByCid(1L));
                req.getRequestDispatcher("index.jsp").forward(req,resp);
                break;
            case "findCityByPid":
                String pidStr = req.getParameter("pid");
                Long pid = Long.parseLong(pidStr);
                List<City> cities = locationService.findCityByPid(pid);
                JSONArray jsonArray = JSONArray.fromObject(cities);
                resp.getWriter().write(jsonArray.toString());
                break;
        }

    }
}

 Area.java

package com.southwind.entity;

public class Area {
    private Long id;
    private String name;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

City.java

package com.southwind.entity;

import java.util.List;

public class City {
    private Long id;
    private String name;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

Province.java

package com.southwind.entity;

import java.util.List;

public class Province {
    private Long id;
    private String name;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

 AreaRepository.java

package com.southwind.repository;

import com.southwind.entity.Area;

import java.util.List;

public interface AreaRepository {
    public List<Area> findByCid(Long id);
}

CityRepository.java

package com.southwind.repository;

import com.southwind.entity.City;

import java.util.List;

public interface CityRepository {
    public List<City> findByPid(Long id);
}

ProvinceRepository.java

package com.southwind.repository;

import com.southwind.entity.Province;

import java.util.List;

public interface ProvinceRepository {
    public List<Province> findAll();
}

 AreaRepositoryImpl.java

package com.southwind.repository.impl;

import com.southwind.entity.Area;
import com.southwind.repository.AreaRepository;
import com.southwind.util.JDBCTools;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

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

public class AreaRepositoryImpl implements AreaRepository {
    @Override
    public List<Area> findByCid(Long id) {
        Connection connection = JDBCTools.getConnection();
        String sql = "select * from area where cid = ?";
        QueryRunner queryRunner = new QueryRunner();
        List<Area> list = null;
        try {
            list = queryRunner.query(connection,sql,new BeanListHandler<>(Area.class),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}

CityRepositoryImpl.java

package com.southwind.repository.impl;

import com.southwind.entity.City;
import com.southwind.repository.CityRepository;
import com.southwind.util.JDBCTools;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

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

public class CityRepositoryImpl implements CityRepository {
    @Override
    public List<City> findByPid(Long id) {
        Connection connection = JDBCTools.getConnection();
        String sql = "select * from city where pid = ?";
        QueryRunner queryRunner = new QueryRunner();
        List<City> list = null;
        try {
            list = queryRunner.query(connection,sql,new BeanListHandler<>(City.class),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}

ProvinceRepositoryImpl.java

package com.southwind.repository.impl;

import com.southwind.entity.Province;
import com.southwind.repository.ProvinceRepository;
import com.southwind.util.JDBCTools;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

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

public class ProvinceRepositoryImpl implements ProvinceRepository {
    @Override
    public List<Province> findAll() {
        Connection connection = JDBCTools.getConnection();
        String sql = "select * from pro";
        QueryRunner queryRunner = new QueryRunner();
        List<Province> list = null;
        try {
            list = queryRunner.query(connection,sql,new BeanListHandler<>(Province.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}

 LocationService.java

package com.southwind.service;

import com.southwind.entity.Area;
import com.southwind.entity.City;
import com.southwind.entity.Province;

import java.util.List;

public interface LocationService {
    public List<Province> findAll();
    public List<City> findCityByPid(Long id);
    public List<Area> findAreaByCid(Long id);
}

 LocationServiceImpl.java

package com.southwind.service.impl;

import com.southwind.entity.Area;
import com.southwind.entity.City;
import com.southwind.entity.Province;
import com.southwind.repository.AreaRepository;
import com.southwind.repository.CityRepository;
import com.southwind.repository.ProvinceRepository;
import com.southwind.repository.impl.AreaRepositoryImpl;
import com.southwind.repository.impl.CityRepositoryImpl;
import com.southwind.repository.impl.ProvinceRepositoryImpl;
import com.southwind.service.LocationService;

import java.util.List;

public class LocationServiceImpl implements LocationService {

    private ProvinceRepository provinceRepository = new ProvinceRepositoryImpl();
    private CityRepository cityRepository = new CityRepositoryImpl();
    private AreaRepository areaRepository = new AreaRepositoryImpl();

    @Override
    public List<Province> findAll() {
        return provinceRepository.findAll();
    }

    @Override
    public List<City> findCityByPid(Long id) {
        return cityRepository.findByPid(id);
    }

    @Override
    public List<Area> findAreaByCid(Long id) {
        return areaRepository.findByCid(id);
    }
}

 JDBCTools.java

package com.southwind.util;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class JDBCTools {
    private static DataSource dataSource;
    static{
        dataSource = new ComboPooledDataSource("testc3p0");
    }
    public static Connection getConnection(){
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
    public static void release(Connection connection){
        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}