com.us.test.H2Helper.java Source code

Introduction

Here is the source code for com.us.test.H2Helper.java

Source

/*
 * @(#)IpToH2.java 2011-12-13
 * ?us??  2008-2011, Inc. All rights reserved.
 * s.server. Use is subject to license terms.
 */
package com.us.test;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.io.FileUtils;
import org.h2.jdbc.JdbcSQLException;
import org.h2.jdbcx.JdbcConnectionPool;

import com.us.util.StringUtil;

/**
 * : IPH2??F
 * 
 * @author <a href="mailto:monlyu.hong@gmail.com">monlyu</a>
 * @version --0.0.1--, 2011-12-13
 * @since JDK6.0
 */
public class H2Helper {

    /**
     * H2?
     * @param url ?Uri?
     * @throws SQLException
     */
    public static void createOrUpdate(String url, String uname, String upasswd) throws SQLException {
        JdbcConnectionPool cp = JdbcConnectionPool.create(url, uname, upasswd);
        Connection conn = cp.getConnection();
        boolean hasIpTabel = true;
        try {
            hasIpTabel = conn.createStatement().execute("select count(start) from ips");
        } catch (JdbcSQLException e) {
            hasIpTabel = false;
        }
        if (!hasIpTabel) {
            String sql = "create table ips(start BIGINT,end BIGINT,startip varchar(32),endip varchar(32),"
                    + " country varchar(255),reurl varchar(255));";
            conn.createStatement().execute(sql);
        }
        conn.close();
        cp.dispose();
    }

    /**
     * IP
     */
    private static String ip2Long(String str) {
        List<Long> ary = StringUtil.splitKillNull(str, "\\.");
        long l = 0;
        l += ary.get(0) * 256l * 256l * 256l;
        l += ary.get(1) * 256l * 256l;
        l += ary.get(2) * 256l;
        l += ary.get(3);
        return Long.toString(l);
    }

    public static void laodData2H2Db(File ips, String url, String uname, String upasswd)
            throws IOException, SQLException {
        List<String> ipsegma = FileUtils.readLines(ips, "GBK");

        JdbcConnectionPool cp = JdbcConnectionPool.create(url, uname, upasswd);
        Connection conn = cp.getConnection();
        String sql = "insert into ips values(?,?,?,?,?,?)";
        int i = 0;
        for (String ip : ipsegma) {
            PreparedStatement statement = conn.prepareStatement(sql);
            String[] ipary = ip.split("\\s+");
            if (ipary.length < 2 || ipary[0].indexOf(".") < 0)
                continue;
            statement.setString(1, ip2Long(ipary[0].trim()));
            statement.setString(2, ip2Long(ipary[1].trim()));
            statement.setString(3, ipary[1].trim());
            statement.setString(4, ipary[1].trim());
            statement.setString(5, ipary.length <= 2 ? "" : ipary[2].trim());
            statement.setString(6, ipary.length <= 3 ? "" : ipary[3].trim());
            System.out.println(i++ + ":" + statement.execute());

        }
        conn.close();
        cp.dispose();
    }

    public static void main2(String... args) throws Exception {
        createOrUpdate("jdbc:h2:~/data/iph2db", "sa", "sa");
        laodData2H2Db(new File("C:\\Documents and Settings\\Administrator\\data\\ips.txt"), "jdbc:h2:~/data/iph2db",
                "sa", "sa");
        JdbcConnectionPool cp = JdbcConnectionPool.create("jdbc:h2:~/data/iph2db", "sa", "sa");
        Connection conn = cp.getConnection();
        ResultSet rs = conn.createStatement().executeQuery("select * from ips");
        while (rs.next()) {
            System.out.println(
                    rs.getString(1) + ":" + rs.getString(2) + ":" + rs.getString(3) + ":" + rs.getString(4));
        }

    }

    public static void main(String[] args) throws Exception {
        System.out.println(ip2Long("192.168.1.1"));
        //select * from ips where 3232235777 between start  and end ;
        //        main2(args);
    }
}