dbaddress.java

基于struct结构的jsp

package com.ntsky.bbs.service.db;

import java.sql.*;
import java.io.*;
import java.util.*;
import org.dom4j.*;
import org.dom4j.io.*;
import org.dom4j.dom.*;
import org.apache.log4j.Logger ;

import com.ntsky.xml.bbs.AddressXML;
import com.ntsky.xml.bbs.XMLCreator;
import com.ntsky.xml.bbs.dto.AddressDTO;
import com.ntsky.datasource.DBConnectionManager;
/**
 * <p>Title: Ntsky OpenSource BBS</p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2004</p>
 * <p>Company: WWW.FM880.COM</p>
 * @author 姚君林
 * @version 1.0
 */
public class DBAddress {

    private final static Logger logger = Logger.getLogger(DBAddress.class);

    private final static String EXPORT_MYSQL = "INSERT INTO t_address(addrIp1,addrIp2,addrCountry,addrCity) VALUES(?,?,?,?);";
    private final static String ISEXIST_ADDRESS = "SELECT addrIp1 FROM t_address LIMIT 0,1;";
    private final static String ADDRESS_NAME = "SELECT addrCountry,addrCity FROM t_address WHERE addrIp1<=? and addrIp2>=?;";

    /**
     * 获取该IP所在地
     * @return boolean
     */
    public static String[] getAddressName(String ip){
        Connection conn = DBConnectionManager.getInstance().getConnection();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        String[] string = new String[2];
        double doubleIp = getIpValue(ip);
        try {
            pstm = conn.prepareStatement(ADDRESS_NAME);
            pstm.setDouble(1,doubleIp);
            pstm.setDouble(2,doubleIp);
            rs = pstm.executeQuery();
            if(rs.next()){
                string[0] = rs.getString("addrCountry");
                string[1] = rs.getString("addrCity");
            }
            else{
                //logger.info("无此ip对应的地名"+ip);
                string[0] = "";
                string[1] = "";
            }
        }
        catch (Exception ex) {
            logger.error("查询ip地址发生错误" + ex.getMessage());
        }
        finally{
            try {
                rs.close();
            }
            catch (Exception ex) {
                logger.info("关闭指针错误 error : " + ex.getMessage());
            }
            try {
                pstm.close();
            }
            catch (Exception ex) {
                logger.error("关闭 pstm 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭数据库连接失败 error : " +ex.getMessage());
            }
        }
        return string;
    }

    /**
     * IP换算
     * @return boolean
     */
    private static double getIpValue(String ip){
        int i = ip.indexOf(".");
        String ip1 = ip.substring(0, i);
        ip = ip.substring(i + 1, ip.length());
        i = ip.indexOf(".");
        String ip2 = ip.substring(0, i);
        ip = ip.substring(i + 1, ip.length());
        i = ip.indexOf(".");
        String ip3 = ip.substring(0, i);
        String ip4 = ip.substring(i + 1, ip.length());
        double dobuleIp;
        dobuleIp = (double) Integer.valueOf(ip1).intValue() * 256 * 256 * 256 +
            Integer.valueOf(ip2).intValue() * 256 * 256 +
            Integer.valueOf(ip3).intValue() * 256 +
            Integer.valueOf(ip4).intValue();
        //logger.info("该ip对应的值为 : "+dobuleIp);
        return dobuleIp;

   }

    /**
     * 判断有无纪录
     * @param addressDTO AddressDTO
     */
    public static boolean isExistAddress(){
        boolean isExist = false;
        Connection conn = DBConnectionManager.getInstance().getConnection();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            pstm = conn.prepareStatement(ISEXIST_ADDRESS);
            rs = pstm.executeQuery();
            if(rs.next()){
                isExist = true;
            }
        }
        catch (Exception ex) {
            logger.info("检索数据库发生错误" + ex.getMessage());
        }
        finally{
            try {
                rs.close();
            }
            catch (Exception ex) {
                logger.info("关闭指针错误 error : " + ex.getMessage());
            }
            try {
                pstm.close();
            }
            catch (Exception ex) {
                logger.error("关闭 pstm 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭数据库连接失败 error : " +ex.getMessage());
            }
        }
        return isExist;
    }

    /**
     * 从xml导入到mysql内存表
     * @param rs Iterator
     */
    private static void exportXMLToMysql(AddressDTO addressDTO){
        Connection conn = DBConnectionManager.getInstance().getConnection();
        PreparedStatement pstm = null;
        try {
            pstm = conn.prepareStatement(EXPORT_MYSQL);
            pstm.setString(1,addressDTO.getAddrIp1());
            pstm.setString(2,addressDTO.getAddrIp2());
            pstm.setString(3,addressDTO.getAddrCountry());
            pstm.setString(4,addressDTO.getAddrCity());
            pstm.executeUpdate();
        }
        catch (Exception ex) {
            logger.info("插入到内存表出错" + ex.getMessage());
        }
        finally{
            try {
                pstm.close();
            }
            catch (Exception ex) {
                logger.error("关闭 pstm 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭数据库连接失败 error : " +ex.getMessage());
            }
        }
    }
    /**
     * 写入数据的方法
     */
    public synchronized static void setExportMYSQL(Iterator addressList){
       // logger.info("addressList.hasNext() = " + addressList.hasNext());
        while(addressList.hasNext()){
            AddressDTO addressDTO = (AddressDTO)addressList.next();
            exportXMLToMysql(addressDTO);
        }
    }
}