一、省份信息获取
1.ProvinceDao
(1)查询所有省份信息
(2)根据省份code查询某省份信息
package com.xzt.prictice.dao;
import com.xzt.prictice.entity.Province;
import com.xzt.prictice.utils.DataSourceUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* @Author xzt
* @Date 2022/4/29 23:35
* @Version 1.0
* @Introduce
*/
public class ProvinceDao {
/**
* 查询所有的省份信息
* @return
*/
public List<Province> findAll() {
try {
Connection connection = DataSourceUtils.getConnection();
PreparedStatement ps = connection.prepareStatement("select * from province;");
ResultSet rs = ps.executeQuery();
List<Province> provinceList = new ArrayList<>();
while (rs.next()) {
Province province = new Province(rs.getInt("id"),rs.getString("code"),rs.getString("name"));
provinceList.add(province);
}
DataSourceUtils.close(connection,ps,rs);
return provinceList;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
/**
* 根据省份code获取该省份详细信息
* @param provinceCode
* @return
*/
public Province findByCode(String provinceCode) {
try {
Connection connection = DataSourceUtils.getConnection();
PreparedStatement ps = connection.prepareStatement("select * from province where code = ?");
ps.setString(1,provinceCode);
ResultSet rs = ps.executeQuery();
Province province = new Province();
if (rs.next()){
province.setId(rs.getInt(1));
province.setCode(rs.getString(2));
province.setName(rs.getString(3));
DataSourceUtils.close(connection,ps,rs);
return province;
}
return null;
}catch (SQLException throwables){
throwables.printStackTrace();
}
return null;
}
}
2.ProvinceService
package com.xzt.prictice.service;
import com.xzt.prictice.dao.ProvinceDao;
import com.xzt.prictice.entity.Province;
import java.util.List;
/**
* @Author xzt
* @Date 2022/4/29 23:35
* @Version 1.0
* @Introduce
*/
public class ProvinceService {
private ProvinceDao provinceDao=new ProvinceDao();
/**
* 查询全部省份信息
* @return
*/
public List<Province> findAll() {
List<Province> provinceList=provinceDao.findAll();
return provinceList;
}
public Province findByCode(String provinceCode) {
Province province = provinceDao.findByCode(provinceCode);
return province;
}
}
3.QueryProvinceServlet
package com.xzt.prictice.controller;
import com.alibaba.fastjson.JSON;
import com.xzt.prictice.entity.Province;
import com.xzt.prictice.service.ProvinceService;
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;
/**
* @Author xzt
* @Date 2022/4/29 23:33
* @Version 1.0
* @Introduce 查询全国所有的省份
*/
@WebServlet("/queryProvince")
public class QueryProvinceServlet extends HttpServlet {
private ProvinceService provinceService = new ProvinceService();
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// 查询所有的省份信息
List<Province> provinceList=provinceService.findAll();
// 设置响应类型为json
resp.setContentType("application/json;charset=utf8");
// 将集合转换为json
String provinceListJson = JSON.toJSONString(provinceList);
// 写出到前端
resp.getWriter().write(provinceListJson);
}
}
二、城市信息的获取
1.CityDao
(1)查询所有城市信息
(2)根据城市code查询某城市信息
package com.xzt.prictice.dao;
import com.xzt.prictice.entity.City;
import com.xzt.prictice.utils.DataSourceUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* @Author xzt
* @Date 2022/4/29 23:40
* @Version 1.0
* @Introduce
*/
public class CityDao {
/**
* 根据省份code查询所属城市
* @return
*/
public List<City> findByProvinceCode(String provinceCode) {
try {
Connection connection = DataSourceUtils.getConnection();
PreparedStatement ps = connection.prepareStatement("select * from city where provincecode=?;");
ps.setString(1,provinceCode);
// 执行查询
ResultSet rs = ps.executeQuery();
// 准备一个cityList集合
List<City> cityList = new ArrayList<>();
while (rs.next()) {
City city = new City(
rs.getInt("id"),
rs.getString("code"),
rs.getString("name"),
rs.getString("provincecode")
);
// 添加到cityList集合
cityList.add(city);
}
DataSourceUtils.close(connection,ps,rs);
return cityList;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
/**
* 根据城市code获取该城市的详细信息
* @param cityCode
* @return
*/
public City findByCode(String cityCode) {
try{
Connection connection = DataSourceUtils.getConnection();
PreparedStatement ps = connection.prepareStatement("select * from city where code=?;");
ps.setString(1,cityCode);
ResultSet rs = ps.executeQuery();
City city = new City();
if (rs.next()){
city.setId(rs.getInt(1));
city.setCode(rs.getString(2));
city.setName(rs.getString(3));
city.setProvinceCode(rs.getString(4));
DataSourceUtils.close(connection,ps,rs);
return city;
}
return null;
}catch (SQLException throwables){
throwables.printStackTrace();
}
return null;
}
}
2.CityService
package com.xzt.prictice.service;
import com.xzt.prictice.dao.CityDao;
import com.xzt.prictice.entity.City;
import java.util.List;
/**
* @Author xzt
* @Date 2022/4/29 23:39
* @Version 1.0
* @Introduce
*/
public class CityService {
private CityDao cityDao=new CityDao();
/**
* 根据省份code查询所属城市
* @return
*/
public List<City> findByProvinceCode(String provinceCode) {
List<City> cityList=cityDao.findByProvinceCode(provinceCode);
return cityList;
}
public City findByCode(String cityCode) {
City city = cityDao.findByCode(cityCode);
return city;
}
}
3.QueryCityServlet
package com.xzt.prictice.controller;
import com.alibaba.fastjson.JSON;
import com.xzt.prictice.entity.City;
import com.xzt.prictice.service.CityService;
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;
/**
* @Author xzt
* @Date 2022/4/29 23:39
* @Version 1.0
* @Introduce 查询某省份的所有城市
*/
@WebServlet("/queryCity")
public class QueryCityServlet extends HttpServlet {
private CityService cityService = new CityService();
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// 接收前端传递的省份Code
String provinceCode = req.getParameter("provinceCode");
// 查询所有的省份信息
List<City> cityList=cityService.findByProvinceCode(provinceCode);
// 设置响应类型为json
resp.setContentType("application/json;charset=utf8");
// 将集合转换为json
String cityListJson = JSON.toJSONString(cityList);
// 写出到前端
resp.getWriter().write(cityListJson);
}
}
三、县区信息的获取
1.AreaDao
(1)查询所有县区信息
(2)根据县区code查询某县区信息
package com.xzt.prictice.dao;
import com.xzt.prictice.entity.Area;
import com.xzt.prictice.utils.DataSourceUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* @Author xzt
* @Date 2022/4/29 23:43
* @Version 1.0
* @Introduce
*/
public class AreaDao {
/**
* 根据城市code查询所属县区
*
* @return
*/
public List<Area> findByCityCode(String cityCode) {
try {
Connection connection = DataSourceUtils.getConnection();
PreparedStatement ps = connection.prepareStatement("select * from area where cityCode=?;");
ps.setString(1, cityCode);
// 执行查询
ResultSet rs = ps.executeQuery();
// 准备一个areaList集合
List<Area> areaList = new ArrayList<>();
while (rs.next()) {
Area area = new Area(
rs.getInt("id"),
rs.getString("code"),
rs.getString("name"),
rs.getString("citycode")
);
// 添加到cityList集合
areaList.add(area);
}
DataSourceUtils.close(connection,ps,rs);
return areaList;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
/**
* 根据地区code查询该地区具体信息
* @param areaCode
* @return
*/
public Area findByCode(String areaCode) {
try {
Connection connection = DataSourceUtils.getConnection();
PreparedStatement ps = connection.prepareStatement("select * from area where code=?;");
ps.setString(1, areaCode);
// 执行查询
ResultSet rs = ps.executeQuery();
Area area = new Area();
if (rs.next()){
area.setId(rs.getInt(1));
area.setCode(rs.getString(2));
area.setName(rs.getString(3));
area.setCityCode(rs.getString(4));
DataSourceUtils.close(connection,ps,rs);
return area;
}
return null;
}catch (SQLException throwables){
throwables.printStackTrace();
}
return null;
}
}
2.AreaService
package com.xzt.prictice.service;
import com.xzt.prictice.dao.AreaDao;
import com.xzt.prictice.entity.Area;
import java.util.List;
/**
* @Author xzt
* @Date 2022/4/29 23:43
* @Version 1.0
* @Introduce
*/
public class AreaService {
private AreaDao areaDao=new AreaDao();
/**
* 根据城市code查询所属县区
* @return
*/
public List<Area> findByCityCode(String cityCode) {
List<Area> areaList=areaDao.findByCityCode(cityCode);
return areaList;
}
public Area findBuCode(String areaCode) {
Area area = areaDao.findByCode(areaCode);
return area;
}
}
3.QueryAreaServlet
package com.xzt.prictice.controller;
import com.alibaba.fastjson.JSON;
import com.xzt.prictice.entity.Area;
import com.xzt.prictice.service.AreaService;
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;
/**
* @Author xzt
* @Date 2022/4/29 23:41
* @Version 1.0
* @Introduce 查询某城市的所有县区
*/
@WebServlet("/queryArea")
public class QueryAreaServlet extends HttpServlet {
private AreaService areaService = new AreaService();
@Override
protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 接收前端传递的城市Code
String cityCode = request.getParameter("cityCode");
// 查询所有的省份信息
List<Area> areaList=areaService.findByCityCode(cityCode);
// 设置响应类型为json
response.setContentType("application/json;charset=utf8");
// 将集合转换为json
String areaListJson = JSON.toJSONString(areaList);
// 写出到前端
response.getWriter().write(areaListJson);
}
}
四、判断用户名是否存在
(1)查询该用户名的记录条数
(2)如果大于0说明已存在,否则不存在
1.UserDao
package com.xzt.prictice.dao;
import com.xzt.prictice.entity.User;
import com.xzt.prictice.utils.DataSourceUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @Author xzt
* @Date 2022/4/30 9:33
* @Version 1.0
* @Introduce
*/
public class UserDao {
/**
* 判断该用户名是否存在
* @param username
* @return
*/
public Integer countByUsername(String username){
try{
Connection connection = DataSourceUtils.getConnection();
PreparedStatement ps = connection.prepareStatement("select count(*) from user where username = ?");
ps.setString(1,username);
ResultSet rs = ps.executeQuery();
rs.next();
int count = rs.getInt(1);
DataSourceUtils.close(connection,ps,rs);
return count;
}catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
}
2.UserService
package com.xzt.prictice.service;
import com.xzt.prictice.dao.UserDao;
import com.xzt.prictice.entity.PageEntity;
import com.xzt.prictice.entity.User;
import java.util.List;
/**
* @Author xzt
* @Date 2022/4/30 9:33
* @Version 1.0
* @Introduce
*/
public class UserService {
private UserDao userDao = new UserDao();
public Integer countByUsername(String name){
Integer count = userDao.countByUsername(name);
return count;
}
}
3.CheckUsernameServlet
package com.xzt.prictice.controller;
import com.xzt.prictice.service.UserService;
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;
/**
* @Author xzt
* @Date 2022/4/30 9:29
* @Version 1.0
* @Introduce 根据用户输入的用户查询数据库是否存在
*/
@WebServlet("/checkUsername")
public class CheckUsernameServlet extends HttpServlet {
private UserService userService = new UserService();
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String username = req.getParameter("username");
Integer count = userService.countByUsername(username);
if (count>0){
resp.getWriter().write("1");
}else {
resp.getWriter().write("0");
}
}
}
五、判断邮箱是否存在
(1)查询该邮箱的记录条数
(2)如果大于0说明已存在,否则不存在
1.UserDao
package com.xzt.prictice.dao;
import com.xzt.prictice.entity.User;
import com.xzt.prictice.utils.DataSourceUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @Author xzt
* @Date 2022/4/30 9:33
* @Version 1.0
* @Introduce
*/
public class UserDao {
/**
* 判断该邮箱是否存在
* @param email
* @return
*/
public Integer countByEmail(String email) {
try{
Connection connection = DataSourceUtils.getConnection();
PreparedStatement ps = connection.prepareStatement("select count(*) from user where email = ?");
ps.setString(1,email);
ResultSet rs = ps.executeQuery();
rs.next();
int count = rs.getInt(1);
DataSourceUtils.close(connection,ps,rs);
return count;
}catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
}
2.UserService
public Integer countByEmail(String email) {
Integer count = userDao.countByEmail(email);
return count;
}
3.CheckEmailServlet
package com.xzt.prictice.controller;
import com.xzt.prictice.service.UserService;
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;
/**
* @Author xzt
* @Date 2022/4/30 9:29
* @Version 1.0
* @Introduce 根据用户输入的邮箱查询数据库是否存在
*/
@WebServlet("/checkEmail")
public class CheckEmailServlet extends HttpServlet {
private UserService userService = new UserService();
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String email = req.getParameter("email");
Integer count = userService.countByEmail(email);
if (count>0){
resp.getWriter().write("1");
}else {
resp.getWriter().write("0");
}
}
}
六、注册(添加用户信息)
1.UserDao
/**
* 注册新用户,添加用户
* @param user
*/
public void saveUser(User user) {
try{
Connection connection = DataSourceUtils.getConnection();
PreparedStatement ps = connection.prepareStatement("insert into user(username,password,name,pic,email,gender,birthday,address) values (?,?,?,?,?,?,?,?)");
ps.setString(1, user.getUsername());
ps.setString(2, user.getPassword());
ps.setString(3, user.getName());
ps.setString(4, user.getPic());
ps.setString(5, user.getEmail());
ps.setInt(6, user.getGender());
ps.setDate(7, new Date(user.getBirthday().getTime()));
ps.setString(8, user.getAddress());
ps.executeUpdate();
DataSourceUtils.close(connection,ps);
}catch (SQLException throwables) {
throwables.printStackTrace();
}
}
2.UserService
public void addUser(User user) {
userDao.saveUser(user);
}
3.RegisterServlet
package com.xzt.prictice.controller;
import com.xzt.prictice.entity.Area;
import com.xzt.prictice.entity.City;
import com.xzt.prictice.entity.Province;
import com.xzt.prictice.entity.User;
import com.xzt.prictice.service.AreaService;
import com.xzt.prictice.service.CityService;
import com.xzt.prictice.service.ProvinceService;
import com.xzt.prictice.service.UserService;
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.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
* @Author xzt
* @Date 2022/4/30 10:45
* @Version 1.0
* @Introduce 用户注册
*/
@WebServlet("/register")
public class RegisterServlet extends HttpServlet {
private UserService userService = new UserService();
private ProvinceService provinceService = new ProvinceService();
private CityService cityService = new CityService();
private AreaService areaService = new AreaService();
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//解决表单提交乱码问题
req.setCharacterEncoding("utf-8");
String username = req.getParameter("username");
String password = req.getParameter("password");
String realname = req.getParameter("name");
String email = req.getParameter("email");
int gender = Integer.parseInt(req.getParameter("gender"));
Date birthday = null;
try {
birthday = new SimpleDateFormat("yyyy-MM-dd").parse(req.getParameter("birthday"));
} catch (ParseException e) {
e.printStackTrace();
}
String provinceCode = req.getParameter("province");
String cityCode = req.getParameter("city");
String areaCode = req.getParameter("area");
Province province = provinceService.findByCode(provinceCode);
City city = cityService.findByCode(cityCode);
Area area = areaService.findBuCode(areaCode);
String address = province.getName()+city.getName()+area.getName();
User user = new User(null,username,password,realname,email,req.getContextPath()+"/images/user.png",gender,birthday,address);
userService.addUser(user);
resp.sendRedirect(req.getContextPath()+"/login.jsp");
}
}
七、前端
1.js
$(function () {
// 页面一加载就去查询全部的省份信息
$.get("/queryProvince",function (res) {
var html="";
for(var i=0;i<res.length;i++){
html+="<option value=\""+res[i].code+"\">"+res[i].name+"</option>";
}
// console.log(html)
$("#province").html(html);
// 刷新UI组件
$('.selectpicker').selectpicker('refresh');
})
});
// 当有省份被选择时去查询所属的城市
$("#province").change(function () {
$.get("/queryCity",{provinceCode:$(this).val()},function (res) {
var html="";
for(var i=0;i<res.length;i++){
html+="<option value=\""+res[i].code+"\">"+res[i].name+"</option>";
}
// 修改城市下拉框
$("#city").html(html);
// 刷新UI组件
$('.selectpicker').selectpicker('refresh');
})
});
// 当有城市被选择时去查询所属的县区
$("#city").change(function () {
$.get("/queryArea",{cityCode:$(this).val()},function (res) {
var html="";
for(var i=0;i<res.length;i++){
html+="<option value=\""+res[i].code+"\">"+res[i].name+"</option>";
}
// 修改城市下拉框
$("#area").html(html);
// 刷新UI组件
$('.selectpicker').selectpicker('refresh');
})
});
// 失去焦点时发送请求来到后端查询
$("#username").blur(function () {
$.get("/checkUsername", {username: $(this).val()}, function (res) {
if(res=='1'){
// 说明用户名已经被注册
$("#username").addClass("input-error");
}else{
$("#username").removeClass("input-error");
}
})
});
// 失去焦点时发送请求来到后端查询
$("#email").blur(function () {
$.get("/checkEmail", {email: $(this).val()}, function (res) {
if(res=='1'){
// 说明用户名已经被注册
$("#email").addClass("input-error");
}else{
$("#email").removeClass("input-error");
}
})
});
$("#regisForm").submit(function (){
var flag = true;
$(this).find("input").each(function (index,domElement) {
if ($(this).hasClass("input-error")){
flag = false;
return;
}
});
return flag;
});
</script>
2.register.jsp页面
<form class="form-horizontal" action="/register" id="regisForm" method="post">
<div class="form-group">
<label for="province" class="col-sm-2 control-label">省份:</label>
<div class="col-sm-10">
<select name="province" id="province" required class="form-control selectpicker" data-live-search="true">
</select>
</div>
</div>
<div class="form-group">
<label for="city" class="col-sm-2 control-label">城市:</label>
<div class="col-sm-10">
<select name="city" id="city" required class="form-control selectpicker" data-live-search="true">
</select>
</div>
</div>
<div class="form-group">
<label for="area" class="col-sm-2 control-label">县/区:</label>
<div class="col-sm-10">
<select name="area" id="area" required class="form-control selectpicker" data-live-search="true">
</select>
</div>
</div>
下一章介绍实现用户登录