向tb_city表添加全国城市数据
时间: 2024-09-13 12:02:18 浏览: 41
向tb_city表添加全国城市数据通常涉及数据库操作,需要先确定tb_city表的结构,比如它包含哪些字段,例如城市ID、城市名称、省/直辖市、区县等。确保你有适当的权限向表中插入数据。以下是向tb_city表添加数据的基本步骤:
1. 首先,确定tb_city表的字段。一般情况下,它可能包含如下字段:
- city_id(城市ID,通常是主键)
- city_name(城市名称)
- province(省份)
- district(区县)
- population(人口,可选)
- area_code(区号,可选)
- etc(其他可能的附加信息)
2. 接下来,创建一个INSERT语句,用以向表中添加数据。例如:
```sql
INSERT INTO tb_city (city_id, city_name, province, district) VALUES (1, '北京', '北京市', '无');
INSERT INTO tb_city (city_id, city_name, province, district) VALUES (2, '上海', '上海市', '无');
-- 继续为其他城市添加数据...
```
3. 执行INSERT语句,向数据库中添加数据。
请注意,实际操作时,你可能需要考虑到数据完整性,例如:
- 确保城市ID是唯一的。
- 确保插入的数据符合字段的约束条件,比如字段长度、数据类型等。
- 如果表中有外键约束,需要确保关联的其他表中有相应记录。
相关问题
用javeEE实现淘宝收获地址,单表存储,具有翻页功能,增删改功能
首先,我们需要设计数据库表结构。本例中,我们只使用一张表来存储收货地址信息,表名为`tb_address`,包含以下字段:
- `id`:主键,自增长;
- `user_id`:用户 ID,用于关联用户表;
- `name`:收货人姓名;
- `phone`:收货人电话;
- `province`:省份;
- `city`:城市;
- `district`:区县;
- `address`:详细地址;
- `is_default`:是否默认地址,0 表示不是默认地址,1 表示是默认地址。
接下来,我们可以创建一个 `Address` 类来封装地址信息,并为其编写相应的 Getter 和 Setter 方法。
```java
public class Address {
private int id;
private int userId;
private String name;
private String phone;
private String province;
private String city;
private String district;
private String address;
private int isDefault;
// Getter 和 Setter 方法省略
}
```
接着,我们可以创建一个 `AddressDao` 类来实现对地址表的增删改查操作。在本例中,我们使用 JDBC 连接数据库来实现数据访问。
```java
public class AddressDao {
// 数据库连接信息
private String driver = "com.mysql.jdbc.Driver";
private String url = "jdbc:mysql://localhost:3306/test";
private String username = "root";
private String password = "123456";
// 查询地址列表
public List<Address> getAddressList(int userId, int page, int pageSize) throws Exception {
// 计算分页参数
int start = (page - 1) * pageSize;
int end = start + pageSize;
// 创建数据库连接
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
// 执行 SQL 查询
String sql = "SELECT * FROM tb_address WHERE user_id = ? LIMIT ?, ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, userId);
pstmt.setInt(2, start);
pstmt.setInt(3, pageSize);
ResultSet rs = pstmt.executeQuery();
// 封装查询结果
List<Address> list = new ArrayList<>();
while (rs.next()) {
Address address = new Address();
address.setId(rs.getInt("id"));
address.setUserId(rs.getInt("user_id"));
address.setName(rs.getString("name"));
address.setPhone(rs.getString("phone"));
address.setProvince(rs.getString("province"));
address.setCity(rs.getString("city"));
address.setDistrict(rs.getString("district"));
address.setAddress(rs.getString("address"));
address.setIsDefault(rs.getInt("is_default"));
list.add(address);
}
// 关闭连接
rs.close();
pstmt.close();
conn.close();
return list;
}
// 查询地址总数
public int getAddressCount(int userId) throws Exception {
// 创建数据库连接
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
// 执行 SQL 查询
String sql = "SELECT COUNT(*) FROM tb_address WHERE user_id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, userId);
ResultSet rs = pstmt.executeQuery();
// 获取查询结果
int count = 0;
if (rs.next()) {
count = rs.getInt(1);
}
// 关闭连接
rs.close();
pstmt.close();
conn.close();
return count;
}
// 添加地址
public void addAddress(Address address) throws Exception {
// 创建数据库连接
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
// 执行 SQL 插入语句
String sql = "INSERT INTO tb_address(user_id, name, phone, province, city, district, address, is_default) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, address.getUserId());
pstmt.setString(2, address.getName());
pstmt.setString(3, address.getPhone());
pstmt.setString(4, address.getProvince());
pstmt.setString(5, address.getCity());
pstmt.setString(6, address.getDistrict());
pstmt.setString(7, address.getAddress());
pstmt.setInt(8, address.getIsDefault());
pstmt.executeUpdate();
// 关闭连接
pstmt.close();
conn.close();
}
// 更新地址
public void updateAddress(Address address) throws Exception {
// 创建数据库连接
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
// 执行 SQL 更新语句
String sql = "UPDATE tb_address SET name = ?, phone = ?, province = ?, city = ?, district = ?, " +
"address = ?, is_default = ? WHERE id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, address.getName());
pstmt.setString(2, address.getPhone());
pstmt.setString(3, address.getProvince());
pstmt.setString(4, address.getCity());
pstmt.setString(5, address.getDistrict());
pstmt.setString(6, address.getAddress());
pstmt.setInt(7, address.getIsDefault());
pstmt.setInt(8, address.getId());
pstmt.executeUpdate();
// 关闭连接
pstmt.close();
conn.close();
}
// 删除地址
public void deleteAddress(int id) throws Exception {
// 创建数据库连接
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
// 执行 SQL 删除语句
String sql = "DELETE FROM tb_address WHERE id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
pstmt.executeUpdate();
// 关闭连接
pstmt.close();
conn.close();
}
}
```
最后,我们可以创建一个 `AddressServlet` 类来处理收货地址相关的 HTTP 请求。
```java
public class AddressServlet extends HttpServlet {
private AddressDao addressDao = new AddressDao();
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// 获取用户 ID 参数
int userId = Integer.parseInt(req.getParameter("userId"));
// 获取分页参数
int page = req.getParameter("page") != null ? Integer.parseInt(req.getParameter("page")) : 1;
int pageSize = req.getParameter("pageSize") != null ? Integer.parseInt(req.getParameter("pageSize")) : 10;
try {
// 查询地址列表
List<Address> list = addressDao.getAddressList(userId, page, pageSize);
// 查询地址总数
int count = addressDao.getAddressCount(userId);
// 计算总页数
int pageCount = count % pageSize == 0 ? count / pageSize : count / pageSize + 1;
// 封装查询结果
Map<String, Object> result = new HashMap<>();
result.put("code", 200);
result.put("message", "success");
result.put("data", list);
result.put("count", count);
result.put("pageCount", pageCount);
// 将查询结果转换成 JSON 格式并输出到客户端
Gson gson = new Gson();
String json = gson.toJson(result);
resp.getWriter().write(json);
} catch (Exception e) {
e.printStackTrace();
// 封装错误信息并输出到客户端
Map<String, Object> result = new HashMap<>();
result.put("code", 500);
result.put("message", "server error: " + e.getMessage());
Gson gson = new Gson();
String json = gson.toJson(result);
resp.getWriter().write(json);
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// 获取操作参数
String action = req.getParameter("action");
try {
if ("add".equals(action)) {
// 添加地址
Address address = new Address();
address.setUserId(Integer.parseInt(req.getParameter("userId")));
address.setName(req.getParameter("name"));
address.setPhone(req.getParameter("phone"));
address.setProvince(req.getParameter("province"));
address.setCity(req.getParameter("city"));
address.setDistrict(req.getParameter("district"));
address.setAddress(req.getParameter("address"));
address.setIsDefault(Integer.parseInt(req.getParameter("isDefault")));
addressDao.addAddress(address);
// 封装添加结果并输出到客户端
Map<String, Object> result = new HashMap<>();
result.put("code", 200);
result.put("message", "success");
Gson gson = new Gson();
String json = gson.toJson(result);
resp.getWriter().write(json);
} else if ("update".equals(action)) {
// 更新地址
Address address = new Address();
address.setId(Integer.parseInt(req.getParameter("id")));
address.setName(req.getParameter("name"));
address.setPhone(req.getParameter("phone"));
address.setProvince(req.getParameter("province"));
address.setCity(req.getParameter("city"));
address.setDistrict(req.getParameter("district"));
address.setAddress(req.getParameter("address"));
address.setIsDefault(Integer.parseInt(req.getParameter("isDefault")));
addressDao.updateAddress(address);
// 封装更新结果并输出到客户端
Map<String, Object> result = new HashMap<>();
result.put("code", 200);
result.put("message", "success");
Gson gson = new Gson();
String json = gson.toJson(result);
resp.getWriter().write(json);
} else if ("delete".equals(action)) {
// 删除地址
int id = Integer.parseInt(req.getParameter("id"));
addressDao.deleteAddress(id);
// 封装删除结果并输出到客户端
Map<String, Object> result = new HashMap<>();
result.put("code", 200);
result.put("message", "success");
Gson gson = new Gson();
String json = gson.toJson(result);
resp.getWriter().write(json);
}
} catch (Exception e) {
e.printStackTrace();
// 封装错误信息并输出到客户端
Map<String, Object> result = new HashMap<>();
result.put("code", 500);
result.put("message", "server error: " + e.getMessage());
Gson gson = new Gson();
String json = gson.toJson(result);
resp.getWriter().write(json);
}
}
}
```
最后,我们需要在 `web.xml` 文件中配置 `AddressServlet` 类的映射关系。
```xml
<servlet>
<servlet-name>AddressServlet</servlet-name>
<servlet-class>com.example.AddressServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>AddressServlet</servlet-name>
<url-pattern>/address/*</url-pattern>
</servlet-mapping>
```
现在,我们可以在浏览器中访问以下地址来测试代码:
- 查询地址列表:`http://localhost:8080/address?userId=1&page=1&pageSize=10`
- 添加地址:`http://localhost:8080/address?action=add&userId=1&name=张三&phone=13800138000&province=广东省&city=深圳市&district=福田区&address=华强北&isDefault=0`
- 更新地址:`http://localhost:8080/address?action=update&id=1&name=李四&phone=13800138111&province=广东省&city=深圳市&district=南山区&address=科技园&isDefault=1`
- 删除地址:`http://localhost:8080/address?action=delete&id=1`
阅读全文