city.java

计算机技术的快速发展

package com.suninformation.tools;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.suninformation.database.DBManager;
import com.suninformation.user.UnacceptableException;

/**
 * 城市信息处理
 * 
 * @author 刘镇
 * @version 1.0
 */
public class City {

	private static final String SEARCH_CITY_BY_CITYID = "SELECT id,cityname FROM city WHERE cityid=?";

	private static final String SEARCH_CITY_BY_CITYNAME = "SELECT id,cityid FROM city WHERE cityname=?";

	private static final String SEARCH_PROV_BY_CITYID = "SELECT id,provid FROM city WHERE cityid=?";

	/**
	 * 通过城市ID，返回城市名称
	 * 
	 * @param cityid
	 * @return
	 * @throws UnacceptableException
	 */
	public static String getCityName(int cityid) throws UnacceptableException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String CityName = "";
		try {
			conn = DBManager.getConnection();
			pstmt = conn.prepareStatement(SEARCH_CITY_BY_CITYID);
			pstmt.setInt(1, cityid);
			rs = pstmt.executeQuery();
			if (!rs.next()) {
				return "";
			}
			CityName = rs.getString(2);
		} catch (SQLException sqle) {
			throw new UnacceptableException("读取城市数据失败。", sqle);
		} finally {
			DBManager.closeObject(conn, pstmt, rs);
		}
		return CityName;
	}

	/**
	 * 通过城市名称，返回城市ID
	 * 
	 * @param cityname
	 * @return
	 * @throws UnacceptableException
	 */
	public static int getCityId(String cityname) throws UnacceptableException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int CityId = 0;
		try {
			conn = DBManager.getConnection();
			pstmt = conn.prepareStatement(SEARCH_CITY_BY_CITYNAME);
			pstmt.setString(1, cityname);
			rs = pstmt.executeQuery();
			if (!rs.next()) {
				return 0;
			}
			CityId = rs.getInt(2);
		} catch (SQLException sqle) {
			throw new UnacceptableException("读取城市数据失败。", sqle);
		} finally {
			DBManager.closeObject(conn, pstmt, rs);
		}
		return CityId;
	}

	/**
	 * 通过城市ID，返回省份ID
	 * 
	 * @param CityId
	 * @return
	 * @throws UnacceptableException
	 */
	public static int getProvId(int cityid) throws UnacceptableException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int ProvId = 0;
		try {
			conn = DBManager.getConnection();
			pstmt = conn.prepareStatement(SEARCH_PROV_BY_CITYID);
			pstmt.setInt(1, cityid);
			rs = pstmt.executeQuery();
			if (!rs.next()) {
				return 0;
			}
			ProvId = rs.getInt(2);
		} catch (SQLException sqle) {
			throw new UnacceptableException("读取城市数据失败。", sqle);
		} finally {
			DBManager.closeObject(conn, pstmt, rs);
		}
		return ProvId;
	}
}