手机号码归属地查询代码

package com.rupeng.test;

import java.io.Closeable;
import java.io.IOException;

public class IOUtils
{
	public static void closeQuietly(Closeable closeable)
	{
		if(closeable!=null)
		{
			try
			{
				closeable.close();
			} catch (IOException e)
			{
				//
			}
		}
	}
}
<pre name="code" class="java">package com.rupeng.test;

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

public class JdbcUtils
{
	private final static String dbdriver = ConfigReader.dbdriver;
	private final static String dburl = ConfigReader.dburl;
	private final static String dbusername = ConfigReader.dbusername;
	private final static String dbpassword = ConfigReader.dbpassword;
	static
	{
		try
		{
			Class.forName(dbdriver);
		} catch (ClassNotFoundException e)
		{
			throw new ExceptionInInitializerError(e);
		}
	}

	public static Connection createConnection() throws SQLException
	{
		return DriverManager.getConnection(dburl, dbusername, dbpassword);
	}

	public static void close(Connection conn)
	{
		if (conn != null)
		{
			try
			{
				conn.close();
			} catch (SQLException e)
			{

			}
		}
	}

	public static void close(Statement stmt)
	{
		if (stmt != null)
		{
			try
			{
				stmt.close();
			} catch (SQLException e)
			{

			}
		}
	}

	public static void close(ResultSet rs)
	{
		if (rs != null)
		{
			try
			{
				rs.close();
			} catch (SQLException e)
			{

			}
		}
	}

	public static int executeUpdate(String sql, Object... parameters)
			throws SQLException
	{
		Connection conn = null;
		try
		{
			conn = createConnection();
			return executeUpdate(conn, sql, parameters);
		} finally
		{
			close(conn);
		}
	}

	public static int executeUpdate(Connection conn, String sql,
			Object... parameters) throws SQLException
	{
		PreparedStatement ps = null;
		try
		{
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < parameters.length; i++)
			{
				ps.setObject(i + 1, parameters[i]);
			}
			return ps.executeUpdate();
		} finally
		{
			close(ps);
		}
	}

	public static ResultSet executeQuery(String sql, Object... parameters)
			throws SQLException
	{
		Connection conn = null;
		try
		{
			conn = createConnection();
			return executeQuery(conn, sql, parameters);
		} catch (SQLException ex)
		{
			close(conn);
			throw ex;
		}
	}

	public static ResultSet executeQuery(Connection conn, String sql,
			Object... parameters) throws SQLException
	{
		PreparedStatement ps = null;
		try
		{
			ResultSet rs = null;
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < parameters.length; i++)
			{
				ps.setObject(i + 1, parameters[i]);
			}
			rs = ps.executeQuery();
			return rs;
		} catch (SQLException ex)
		{
			close(ps);
			throw ex;
		}
	}

	public static void closeAll(ResultSet rs)
	{
		if (rs == null)
		{
			return;
		}
		try
		{
			close(rs.getStatement().getConnection());
			close(rs.getStatement());
			close(rs);
		} catch (SQLException e)
		{

		}
	}
	
	public static void rollback(Connection conn)
	{
		try
		{
			conn.rollback();
		} catch (SQLException e)
		{
			//
		}
	}
}




package com.rupeng.test;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

public class ConfigReader
{

	public final static String dbdriver;
	public final static String dburl;
	public final static String dbusername;
	public final static String dbpassword;

	static
	{
		Properties prop =new Properties();
		InputStream instream = JdbcUtils.class.getClassLoader()
				.getResourceAsStream("com/rupeng/test/config.properties");
		try
		{
			prop.load(instream);
		    instream.close();
		} catch (IOException e)
		{
			throw new ExceptionInInitializerError(e);
		}
		dbdriver = prop.getProperty("dbdriver");
		dburl = prop.getProperty("dburl");
		dbusername = prop.getProperty("dbusername");
		dbpassword = prop.getProperty("dbpassword");
	}

}

dbdriver=com.mysql.jdbc.Driver
dburl=jdbc:mysql://localhost:3306/rupengcrm?seUnicode=true&characterEncoding=UTF8
dbusername=root
dbpassword=root

package com.rupeng.test;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class PhonePos
{

	public static void main(String[] args)
	{
		/*
		//csv导入数据库
		FileInputStream fis = null;
		InputStreamReader reader = null;
		BufferedReader buffReader  =null;
		Connection conn = null;
		PreparedStatement ps = null;
		try
		{
			fis = new FileInputStream("F:\\快盘\\NextBig\\java课程\\10_Java这么学不枯燥(JDBC基础)\\2014年7月份手机号码归属地数据库.csv");
			reader = new InputStreamReader(fis);
			buffReader = new BufferedReader(reader);
			
			buffReader.readLine();//丢弃第一行,表头
			
			conn = JdbcUtils.createConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement("insert into t_phonepos(PhonePrefix,Area,MobileType) values(?,?,?)");
			
			long startMS =  System.currentTimeMillis();
			String line;
			int count=0;
			while((line=buffReader.readLine())!=null)
			{
				//System.out.println(line);
				String[] segments = line.split(",");
				String phonePrefix = segments[1].replace("\"", "");//去掉双引号
				String area = segments[2].replace("\"", "");
				String yys = segments[3].replace("\"", "");
				
				ps.clearParameters();
				ps.setString(1, phonePrefix);
				ps.setString(2, area);
				ps.setString(3, yys);
				ps.addBatch();
				count++;
				if(count%2000==0)
				{
					ps.executeBatch();
				}
				//System.out.println("手机前缀"+phonePrefix+";"+area+yys);
			}
			ps.executeBatch();
			conn.commit();
			System.out.println("导入成功"+(System.currentTimeMillis()-startMS));
		}
		catch(SQLException sqlex)
		{
			JdbcUtils.rollback(conn);
			sqlex.printStackTrace();
		}
		catch(IOException ex)
		{
			JdbcUtils.rollback(conn);
			System.out.println("出错"+ex);
		}
		finally
		{
			JdbcUtils.close(conn);
			//正好和创建的顺序相反
			IOUtils.closeQuietly(buffReader);
			IOUtils.closeQuietly(reader);
			IOUtils.closeQuietly(fis);
		}*/
		System.out.println("请输入手机号");
		Scanner sc = new Scanner(System.in);
		String phoneNum = sc.nextLine();		
		sc.close();
		
		String prefix = phoneNum.substring(0,7);
		System.out.println(prefix);
		ResultSet rs = null;
		try
		{
			rs = JdbcUtils.executeQuery("select * from T_PhonePos where PhonePrefix=?", prefix);
			if(!rs.next())
			{
				System.out.println("查不到您的运营商信息");
				return;
			}
			String area = rs.getString("Area");
			String mobileType = rs.getString("MobileType");
			System.out.println(area+mobileType);
			
		} catch (SQLException e)
		{
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		finally{
			JdbcUtils.closeAll(rs);
		}
		
	}

}



package com.rupeng.test;

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


public class Test1
{

	public static void main(String[] args)
	{
		/*
		try
		{
			JdbcUtils.executeUpdate("insert into t_persons2(Id,Name,Age) 

values(?,?,?)",
					3,"yzk",17);
		} catch (SQLException e)
		{
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}*/
		
		/*
		ResultSet rs = null;
		try
		{
			JdbcUtils.executeUpdate("insert into t_persons2(Name,Age) values

(?,?)",
					"yzk",17);
			
			rs = JdbcUtils.executeQuery("select max(id) maxid from 

t_persons2");
			rs.next();
			long id = rs.getLong("maxid");
			System.out.println(id);
		} catch (SQLException e)
		{
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		finally
		{
			JdbcUtils.closeAll(rs);
		}*/
		
		ResultSet rs = null;
		try
		{
			Connection conn = JdbcUtils.createConnection();
			JdbcUtils.executeUpdate(conn,"insert into t_persons2(Name,Age) 

values(?,?)",
					"yzk",17);
			
			//last_insert_id()获得当前连接中最后一次生成的自动递增字段的值
			rs = JdbcUtils.executeQuery(conn,"select last_insert_id() lastId");
			rs.next();
			long id = rs.getLong("lastId");
			System.out.println(id);
		} catch (SQLException e)
		{
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		finally
		{
			JdbcUtils.closeAll(rs);
		}
	}

}



展开阅读全文

没有更多推荐了,返回首页