package test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Test {
/**
* 把192.168.0.2 这样的IP 格式化成192.168.000.002便于比较字符串
* @param aimstr 传入要比较的字符串
* @return 格式化好的字符串
*/
public String FormatString(String aimstr) {
String[] strarray = StringSplit.split(aimstr, ".");
if (strarray.length != 4)
return "error";
String returnstr = "";
for (int i = 0; i < strarray.length; i++) {
if (strarray[i].length() == 1)
returnstr += "00" + strarray[i];
else if (strarray[i].length() == 2)
returnstr += "0" + strarray[i];
else
returnstr += strarray[i];
if (i != 3)
returnstr += ".";
}
return returnstr;
}
/**
*
* @param database 格式化目标数据库,就是把原来数据库中的起始地址IP 都格式化成15位的
*/
public void FormatDatabase(String database) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
if (database == null || "".equals(database))
database = "ip";
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
String url = "jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=webdb";
String user = "sa";
String password = "sa";
Connection conn = DriverManager.getConnection(url, user, password);
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
Connection uconn = DriverManager.getConnection(url, user, password);
String querysql = "select * from " + database;
int count = 0;
System.out.println("------------begin--------");
try {
PreparedStatement ps = conn.prepareStatement(querysql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
count++;
System.out.println("==" + count);
if (count % 100 == 0) {
System.out.println("curnum:" + count);
}
String newstartip = "";
String newendip = "";
String startip = rs.getString("startip");
String endip = rs.getString("endip");
if (startip.length() == 15 && newendip.length() == 15)
continue;
if (startip.length() < 15)
newstartip = FormatString(startip);
else
newstartip = startip;
if (newendip.length() < 15)
newendip = FormatString(endip);
else
newendip = endip;
PreparedStatement ups = uconn.prepareStatement("update " + database + " set startip='" + newstartip + "',endip='" + newendip + "' where startip='" + startip + "'");
ups.executeUpdate();
}
} catch (Exception e) {
System.out.println("<BR><BR>Error:" + e.getMessage());
} finally {
if (conn != null) {
conn.close();
}
if (uconn != null) {
uconn.close();
}
}
System.out.println("------------end--------");
}
/**
*
* @param database 存放IP信息的数据库
* @param aimdatabase 目标数据库 用来存放需要查找域名的数据库
*/
public void ReadWriteDatabase(String database, String aimdatabase) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
String url = "jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=webdb";
String user = "sa";
String password = "sa";
Connection conn = DriverManager.getConnection(url, user, password);
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
Connection uconn = DriverManager.getConnection(url, user, password);
String querysql = "select * from " + aimdatabase;
int count = 0;
System.out.println("------------begin--------");
try {
PreparedStatement ps = conn.prepareStatement(querysql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
count++;
System.out.println("==" + count);
if (count % 100 == 0) {
System.out.println("curnum:" + count);
}
String newstartip = "";
String startip = rs.getString("ip");
if (startip.length() < 15)
newstartip = FormatString(startip);
else
newstartip = startip;
String name = QueryName(database, newstartip);
PreparedStatement ups = uconn.prepareStatement("update " + aimdatabase + " set name='" + name + "' where ip='" + startip + "'");
ups.executeUpdate();
}
} catch (Exception e) {
System.out.println("<BR><BR>ReadWriteDatabaseError:" + e.getMessage());
} finally {
if (conn != null) {
conn.close();
}
if (uconn != null) {
uconn.close();
}
}
System.out.println("------------end--------");
}
/**
*
* @param database 存放IP信息的数据库
* @param queryIp 要查询的IP
* @return 该IP对应的实际地址
*/
public String QueryName(String database, String queryIp) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
queryIp = FormatString(queryIp);
if (database == null || "".equals(database))
database = "ip";
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
String url = "jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=webdb";
String user = "sa";
String password = "sa";
Connection conn = DriverManager.getConnection(url, user, password);
String localname = "";
String querysql = "select * from " + database + " where startip<='" + queryIp + "' and endip>='" + queryIp + "'";
try {
PreparedStatement ps = conn.prepareStatement(querysql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
localname = rs.getString("localname");
}
} catch (Exception e) {
System.out.println("<BR><BR>QueryNameError:" + e.getMessage());
} finally {
if (conn != null) {
conn.close();
}
}
return localname;
}
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
System.out.println(new Test().FormatString("192.165.0.1"));
}
}
///
package test;
import java.util.StringTokenizer;
public class StringSplit
{
private StringSplit()
{
}
public static String[] split(String source, String delim)
{
String[] wordLists;
if (source == null)
{
wordLists = new String[1];
wordLists[0] = source;
return wordLists;
}
if (delim == null)
{
delim = ",";
}
StringTokenizer st = new StringTokenizer(source, delim);
int total = st.countTokens();
wordLists = new String[total];
for (int i = 0; i < total; i++)
{
wordLists[i] = st.nextToken();
}
return wordLists;
}
}
数据库名字是webdb
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ipadd]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ipadd]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ip]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ip]
GO
//存放要查询IP信息的表
CREATE TABLE [dbo].[ipadd] (
[ip] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[name] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
//存放IP对应地址信息的表
CREATE TABLE [dbo].[ip] (
[startip] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[endip] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[localname] [nvarchar] (60) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO