IP address lookup application on BlueMix

These days I am trying to do a application on BlueMix,so it is less updated on the Blog.The aim is user put his ip address,we can quickly get his location on GoogleMap.

I did it on BlueMix,this application is based on SQLDB,java,Spring,google map api.Below are some technical details for my application.

1.create your IP Geolocation db.You can get some data from http://dev.maxmind.com/geoip/legacy/geolite/,if you need more strict location,you need pay the money to buy it.

2.Launch the SQLDB console to create your table and upload your data to these table.

Enter the below sql in the SQL command UI,

CREATE TABLE USER01."GeoLiteCity-Blocks" (       
        "startlpNum" BIGINT NOT NULL,
        "endlpNum" BIGINT NOT NULL,
        "locld" Integer
    );

CREATE TABLE USER01."GeoLiteCity-Location" (       
        "locld" Integer NOT NULL,
        "country" VARCHAR(256) ,
        "region" VARCHAR(256) ,
        "city"  VARCHAR(256),
        "postalCode"  VARCHAR(256) ,
        "latitude" DECIMAL(9 , 4),
        "longitude" DECIMAL(9 , 4)
    );
   

CREATE TABLE USER01."GeoLiteCity-Blocks" (		
		"startlpNum" BIGINT NOT NULL, 
		"endlpNum" BIGINT NOT NULL, 
		"locld" Integer
	);

CREATE TABLE USER01."GeoLiteCity-Location" (		
		"locld" Integer NOT NULL,
		"country" VARCHAR(256) , 
		"region" VARCHAR(256) ,
		"city"  VARCHAR(256),
		"postalCode"  VARCHAR(256) ,
		"latitude" DECIMAL(9 , 4),
		"longitude" DECIMAL(9 , 4)
	);
	

CREATE TABLE USER01."GeoLiteCity-Blocks" (		
		"startlpNum" BIGINT NOT NULL, 
		"endlpNum" BIGINT NOT NULL, 
		"locld" Integer
	);

CREATE TABLE USER01."GeoLiteCity-Location" (		
		"locld" Integer NOT NULL,
		"country" VARCHAR(256) , 
		"region" VARCHAR(256) ,
		"city"  VARCHAR(256),
		"postalCode"  VARCHAR(256) ,
		"latitude" DECIMAL(9 , 4),
		"longitude" DECIMAL(9 , 4)
	);
	

Using Load featur into an existing table to load data in created table

After finish you can check the sample data in these tables.I have inserted above 100000  records in the table,so if your application need lots of data ,I think it should not be a big problem for that.

If your csv file size  is  bigger ,I suggest you can split the csv samller to process faster.see my sample

3.Write java application to connect sqldb console.The most import part you need to write a db connection class to connect sqldb,see below sample code

 package com.ibm.web.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Set;

import com.ibm.db2.jcc.DB2SimpleDataSource;
import com.ibm.nosql.json.api.BasicDBList;
import com.ibm.nosql.json.api.BasicDBObject;
import com.ibm.nosql.json.util.JSON;
public class DBConnection {
  
    String databaseHost = "localhost";
    int port = 50000;
    String databaseName = "mydb";
    String user = "myuser";
    String password = "mypass";
    String myurl = "myurl";
  
  
    public Connection getDBConnection(){
        Connection con = null;
        try {
             String VCAP_SERVICES = System.getenv("VCAP_SERVICES");
             System.out.println("VCAP_SERVICES content: " + VCAP_SERVICES);

                if (VCAP_SERVICES != null) {
                    // parse the VCAP JSON structure
                    BasicDBObject obj = (BasicDBObject) JSON.parse(VCAP_SERVICES);
                    String thekey = null;
                    Set<String> keys = obj.keySet();              
                    // Look for the VCAP key that holds the SQLDB information
                    for (String eachkey : keys) {              
                        // Just in case the service name gets changed to lower case in the future, use toUpperCase
                    System.out.println("key"+eachkey.toUpperCase());
                        if (eachkey.toUpperCase().contains("SQLDB")) {
                            thekey = eachkey;
                        }
                    }
          
                    BasicDBList list = (BasicDBList) obj.get(thekey);
                    obj = (BasicDBObject) list.get("0");  
                    System.out.println("Service found: " + obj.get("name"));
                    obj = (BasicDBObject) obj.get("credentials");
                    databaseHost = (String) obj.get("host");
                    databaseName = (String) obj.get("db");
                    port = (Integer)obj.get("port");
                    user = (String) obj.get("username");
                    password = (String) obj.get("password");
                    myurl = (String) obj.get("jdbcurl");
                    System.out.println("databaseHost"+    databaseHost);
                    System.out.println("databaseName"+    databaseName);
                    System.out.println("username"+    user);
                    System.out.println("password"+    password);
                    System.out.println("port"+    port);
                    System.out.println("jdbc url"+    myurl);
                    String databaseUrl = "jdbc:db2://" + databaseHost + ":" + port + "/"
                            + databaseName;
                    System.out.println("Loading the Database Driver");
                    Class.forName("com.ibm.db2.jcc.DB2Driver");
                    System.out.println("connect db");
                    con = DriverManager.getConnection(databaseUrl, user, password);
                    con.setAutoCommit(false);
                }
                else {
                    System.out.println("VCAP_SERVICES is null");
                  
                }
                return con;
          
        }
        catch (SQLException e) {
            System.out.println("Error connecting to database");
            System.out.println("SQL Exception: " + e.toString());
           return null;
        }
      
        catch (Exception e) {
            System.out.println("cann;t get connection"+e.toString());
            return null;
        }
    }

}

 4.Aslo you need to write ip value convertion,

private static long ipValue(String ipAddress) {
		String[] ippart = ipAddress.split("\\.");
		long ipvalue = 256 * 256 * 256 * Integer.parseInt(ippart[0]) + 256
				* 256 * Integer.parseInt(ippart[1]) + 256
				* Integer.parseInt(ippart[2]) + Integer.parseInt(ippart[3]);
		return ipvalue;
	}

 private static long ipValue(String ipAddress) {

        String[] ippart = ipAddress.split("\\.");
        long ipvalue = 256 * 256 * 256 * Integer.parseInt(ippart[0]) + 256
                * 256 * Integer.parseInt(ippart[1]) + 256
                * Integer.parseInt(ippart[2]) + Integer.parseInt(ippart[3]);
        return ipvalue;
    }

So you can write the sql to query the actual result based on the ipValue,

String sqlStatement = "select a.\"country\",a.\"region\",a.\"city\",a.\"postalCode\",a.\"latitude\",a.\"longitude\"  from   USER01.\"GeoLiteCity-Location\" a, USER01.\"GeoLiteCity-Blocks\" b where b.\"startIpNum\"<="
					+ ipValue
					+ " and  b.\"endIpNum\">="
					+ ipValue
					+ " and a.\"locId\"=b.\"locId\"";

 String sqlStatement = "select a.\"country\",a.\"region\",a.\"city\",a.\"postalCode\",a.\"latitude\",a.\"longitude\"  from   USER01.\"GeoLiteCity-Location\" a, USER01.\"GeoLiteCity-Blocks\" b where b.\"startIpNum\"<="

                    + ipValue
                    + " and  b.\"endIpNum\">="
                    + ipValue
                    + " and a.\"locId\"=b.\"locId\"";

			String countryCode = "";
			String latitude = "";
			String longitude = "";
			String postalCode = "";
			String city = "";
			String region = "";			
			while (rs.next()) {
				countryCode =rs.getString(1);
				region=rs.getString(2);
				city=rs.getString(3);
				postalCode=rs.getString(4);
				latitude =String.valueOf(rs.getDouble(5));
				longitude=String.valueOf(rs.getDouble(6));
			}			
			rs.close();
			Locale l = new Locale("", countryCode);			
			serverLocation.setCountryCode(countryCode);
			serverLocation.setCountryName(l.getDisplayCountry());
			serverLocation.setRegion(region);
			serverLocation.setRegionName(null);
			serverLocation.setCity(city);
			serverLocation.setPostalCode(postalCode);
			serverLocation.setLatitude(latitude);
			serverLocation.setLongitude(longitude);

       
            while (rs.next()) {
                countryCode =rs.getString(1);
                region=rs.getString(2);
                city=rs.getString(3);
                postalCode=rs.getString(4);
                latitude =String.valueOf(rs.getDouble(5));
                longitude=String.valueOf(rs.getDouble(6));
            }          
            Locale l = new Locale("", countryCode);          
            serverLocation.setCountryCode(countryCode);
            serverLocation.setCountryName(l.getDisplayCountry());
            serverLocation.setRegion(region);
            serverLocation.setRegionName(null);
            serverLocation.setCity(city);
            serverLocation.setPostalCode(postalCode);
            serverLocation.setLatitude(latitude);
            serverLocation.setLongitude(longitude);

private static long ipValue(String ipAddress) {
		String[] ippart = ipAddress.split("\\.");
		long ipvalue = 256 * 256 * 256 * Integer.parseInt(ippart[0]) + 256
				* 256 * Integer.parseInt(ippart[1]) + 256
				* Integer.parseInt(ippart[2]) + Integer.parseInt(ippart[3]);
		return ipvalue;
	}

 5.For jsp part,we need to use jquery to parse the result json and use google api to make the Latitude and  longitude location on the google map.Sample code for your reference.

	var map;
		
		function showMap(latitude,longitude) { 
			
			var googleLatandLong = new google.maps.LatLng(latitude,longitude);
		
			var mapOptions = { 
				zoom: 5,
				center: googleLatandLong,
				mapTypeId: google.maps.MapTypeId.ROADMAP 
			};
		
			var mapDiv = document.getElementById("map");
			map = new google.maps.Map(mapDiv, mapOptions);
			
			var title = "Server Location"; 
			addMarker(map, googleLatandLong, title, "");
			
		}
		
		function addMarker(map, latlong, title, content) { 
			
			var markerOptions = {
				position: latlong, 
				map: map,
				title: title, 
				clickable: true
			};
			var marker = new google.maps.Marker(markerOptions); 
		}
		

    var map;

       
        function showMap(latitude,longitude) {
           
            var googleLatandLong = new google.maps.LatLng(latitude,longitude);
       
            var mapOptions = {
                zoom: 5,
                center: googleLatandLong,
                mapTypeId: google.maps.MapTypeId.ROADMAP
            };
       
            var mapDiv = document.getElementById("map");
            map = new google.maps.Map(mapDiv, mapOptions);
           
            var title = "Server Location";
            addMarker(map, googleLatandLong, title, "");
           
        }
       
        function addMarker(map, latlong, title, content) {
           
            var markerOptions = {
                position: latlong,
                map: map,
                title: title,
                clickable: true
            };
            var marker = new google.maps.Marker(markerOptions);
        }
       

 6.After finish your application,you can export your application to war format.Write a manifest.yml

 for deploy usage.

# this manifest deploys the IP Lookup Sample application
applications:
- name: IPLookup
  #host: IPLookup-${random-word}
  memory: 512M
  instances: 1
  # replace the service name below with appropriate SQLDB service you created
  services:
  - SQLDB-Sample-01

 # this manifest deploys the IP Lookup Sample application

applications:
- name: IPLookup
  #host: IPLookup-${random-word}
  memory: 512M
  instances: 1
  # replace the service name below with appropriate SQLDB service you created
  services:
  - SQLDB-Sample-01

So the main steps are completed,I will share my code at jazzhub,if you are interested in,you can have a look.

package com.ibm.web.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Set;

import com.ibm.db2.jcc.DB2SimpleDataSource;
import com.ibm.nosql.json.api.BasicDBList;
import com.ibm.nosql.json.api.BasicDBObject;
import com.ibm.nosql.json.util.JSON;
public class DBConnection {
	
	String databaseHost = "localhost";
	int port = 50000;
	String databaseName = "mydb";
	String user = "myuser";
	String password = "mypass";
	String myurl = "myurl";
	
	
	public Connection getDBConnection(){
		Connection con = null;
		try {
			 String VCAP_SERVICES = System.getenv("VCAP_SERVICES");
			 System.out.println("VCAP_SERVICES content: " + VCAP_SERVICES);

				if (VCAP_SERVICES != null) {
					// parse the VCAP JSON structure
					BasicDBObject obj = (BasicDBObject) JSON.parse(VCAP_SERVICES);
					String thekey = null;
					Set<String> keys = obj.keySet();				
					// Look for the VCAP key that holds the SQLDB information
					for (String eachkey : keys) {				
						// Just in case the service name gets changed to lower case in the future, use toUpperCase
					System.out.println("key"+eachkey.toUpperCase());
						if (eachkey.toUpperCase().contains("SQLDB")) {
							thekey = eachkey;
						}
					}
			
					BasicDBList list = (BasicDBList) obj.get(thekey);
					obj = (BasicDBObject) list.get("0");	
					System.out.println("Service found: " + obj.get("name"));
					obj = (BasicDBObject) obj.get("credentials");
					databaseHost = (String) obj.get("host");
					databaseName = (String) obj.get("db");
					port = (Integer)obj.get("port");
					user = (String) obj.get("username");
					password = (String) obj.get("password");
					myurl = (String) obj.get("jdbcurl");
					System.out.println("databaseHost"+	databaseHost);
					System.out.println("databaseName"+	databaseName);
					System.out.println("username"+	user);
					System.out.println("password"+	password);
					System.out.println("port"+	port);
					System.out.println("jdbc url"+	myurl);
					String databaseUrl = "jdbc:db2://" + databaseHost + ":" + port + "/"
							+ databaseName;
					System.out.println("Loading the Database Driver");
					Class.forName("com.ibm.db2.jcc.DB2Driver");
					System.out.println("connect db");
					con = DriverManager.getConnection(databaseUrl, user, password);
					con.setAutoCommit(false);
				}
				else {
					System.out.println("VCAP_SERVICES is null");
					
				}
				return con;
			
		} 
		catch (SQLException e) {
			System.out.println("Error connecting to database");
			System.out.println("SQL Exception: " + e.toString());
		   return null;
		}
		
		catch (Exception e) {
			System.out.println("cann;t get connection"+e.toString());
			return null;
		} 
	}

}

package com.ibm.web.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Set;

import com.ibm.db2.jcc.DB2SimpleDataSource;
import com.ibm.nosql.json.api.BasicDBList;
import com.ibm.nosql.json.api.BasicDBObject;
import com.ibm.nosql.json.util.JSON;
public class DBConnection {
	
	String databaseHost = "localhost";
	int port = 50000;
	String databaseName = "mydb";
	String user = "myuser";
	String password = "mypass";
	String myurl = "myurl";
	
	
	public Connection getDBConnection(){
		Connection con = null;
		try {
			 String VCAP_SERVICES = System.getenv("VCAP_SERVICES");
			 System.out.println("VCAP_SERVICES content: " + VCAP_SERVICES);

				if (VCAP_SERVICES != null) {
					// parse the VCAP JSON structure
					BasicDBObject obj = (BasicDBObject) JSON.parse(VCAP_SERVICES);
					String thekey = null;
					Set<String> keys = obj.keySet();				
					// Look for the VCAP key that holds the SQLDB information
					for (String eachkey : keys) {				
						// Just in case the service name gets changed to lower case in the future, use toUpperCase
					System.out.println("key"+eachkey.toUpperCase());
						if (eachkey.toUpperCase().contains("SQLDB")) {
							thekey = eachkey;
						}
					}
			
					BasicDBList list = (BasicDBList) obj.get(thekey);
					obj = (BasicDBObject) list.get("0");	
					System.out.println("Service found: " + obj.get("name"));
					obj = (BasicDBObject) obj.get("credentials");
					databaseHost = (String) obj.get("host");
					databaseName = (String) obj.get("db");
					port = (Integer)obj.get("port");
					user = (String) obj.get("username");
					password = (String) obj.get("password");
					myurl = (String) obj.get("jdbcurl");
					System.out.println("databaseHost"+	databaseHost);
					System.out.println("databaseName"+	databaseName);
					System.out.println("username"+	user);
					System.out.println("password"+	password);
					System.out.println("port"+	port);
					System.out.println("jdbc url"+	myurl);
					String databaseUrl = "jdbc:db2://" + databaseHost + ":" + port + "/"
							+ databaseName;
					System.out.println("Loading the Database Driver");
					Class.forName("com.ibm.db2.jcc.DB2Driver");
					System.out.println("connect db");
					con = DriverManager.getConnection(databaseUrl, user, password);
					con.setAutoCommit(false);
				}
				else {
					System.out.println("VCAP_SERVICES is null");
					
				}
				return con;
			
		} 
		catch (SQLException e) {
			System.out.println("Error connecting to database");
			System.out.println("SQL Exception: " + e.toString());
		   return null;
		}
		
		catch (Exception e) {
			System.out.println("cann;t get connection"+e.toString());
			return null;
		} 
	}

}

package com.ibm.web.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Set;

import com.ibm.db2.jcc.DB2SimpleDataSource;
import com.ibm.nosql.json.api.BasicDBList;
import com.ibm.nosql.json.api.BasicDBObject;
import com.ibm.nosql.json.util.JSON;
public class DBConnection {
	
	String databaseHost = "localhost";
	int port = 50000;
	String databaseName = "mydb";
	String user = "myuser";
	String password = "mypass";
	String myurl = "myurl";
	
	
	public Connection getDBConnection(){
		Connection con = null;
		try {
			 String VCAP_SERVICES = System.getenv("VCAP_SERVICES");
			 System.out.println("VCAP_SERVICES content: " + VCAP_SERVICES);

				if (VCAP_SERVICES != null) {
					// parse the VCAP JSON structure
					BasicDBObject obj = (BasicDBObject) JSON.parse(VCAP_SERVICES);
					String thekey = null;
					Set<String> keys = obj.keySet();				
					// Look for the VCAP key that holds the SQLDB information
					for (String eachkey : keys) {				
						// Just in case the service name gets changed to lower case in the future, use toUpperCase
					System.out.println("key"+eachkey.toUpperCase());
						if (eachkey.toUpperCase().contains("SQLDB")) {
							thekey = eachkey;
						}
					}
			
					BasicDBList list = (BasicDBList) obj.get(thekey);
					obj = (BasicDBObject) list.get("0");	
					System.out.println("Service found: " + obj.get("name"));
					obj = (BasicDBObject) obj.get("credentials");
					databaseHost = (String) obj.get("host");
					databaseName = (String) obj.get("db");
					port = (Integer)obj.get("port");
					user = (String) obj.get("username");
					password = (String) obj.get("password");
					myurl = (String) obj.get("jdbcurl");
					System.out.println("databaseHost"+	databaseHost);
					System.out.println("databaseName"+	databaseName);
					System.out.println("username"+	user);
					System.out.println("password"+	password);
					System.out.println("port"+	port);
					System.out.println("jdbc url"+	myurl);
					String databaseUrl = "jdbc:db2://" + databaseHost + ":" + port + "/"
							+ databaseName;
					System.out.println("Loading the Database Driver");
					Class.forName("com.ibm.db2.jcc.DB2Driver");
					System.out.println("connect db");
					con = DriverManager.getConnection(databaseUrl, user, password);
					con.setAutoCommit(false);
				}
				else {
					System.out.println("VCAP_SERVICES is null");
					
				}
				return con;
			
		} 
		catch (SQLException e) {
			System.out.println("Error connecting to database");
			System.out.println("SQL Exception: " + e.toString());
		   return null;
		}
		
		catch (Exception e) {
			System.out.println("cann;t get connection"+e.toString());
			return null;
		} 
	}

}

package com.ibm.web.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Set;

import com.ibm.db2.jcc.DB2SimpleDataSource;
import com.ibm.nosql.json.api.BasicDBList;
import com.ibm.nosql.json.api.BasicDBObject;
import com.ibm.nosql.json.util.JSON;
public class DBConnection {
	
	String databaseHost = "localhost";
	int port = 50000;
	String databaseName = "mydb";
	String user = "myuser";
	String password = "mypass";
	String myurl = "myurl";
	
	
	public Connection getDBConnection(){
		Connection con = null;
		try {
			 String VCAP_SERVICES = System.getenv("VCAP_SERVICES");
			 System.out.println("VCAP_SERVICES content: " + VCAP_SERVICES);

				if (VCAP_SERVICES != null) {
					// parse the VCAP JSON structure
					BasicDBObject obj = (BasicDBObject) JSON.parse(VCAP_SERVICES);
					String thekey = null;
					Set<String> keys = obj.keySet();				
					// Look for the VCAP key that holds the SQLDB information
					for (String eachkey : keys) {				
						// Just in case the service name gets changed to lower case in the future, use toUpperCase
					System.out.println("key"+eachkey.toUpperCase());
						if (eachkey.toUpperCase().contains("SQLDB")) {
							thekey = eachkey;
						}
					}
			
					BasicDBList list = (BasicDBList) obj.get(thekey);
					obj = (BasicDBObject) list.get("0");	
					System.out.println("Service found: " + obj.get("name"));
					obj = (BasicDBObject) obj.get("credentials");
					databaseHost = (String) obj.get("host");
					databaseName = (String) obj.get("db");
					port = (Integer)obj.get("port");
					user = (String) obj.get("username");
					password = (String) obj.get("password");
					myurl = (String) obj.get("jdbcurl");
					System.out.println("databaseHost"+	databaseHost);
					System.out.println("databaseName"+	databaseName);
					System.out.println("username"+	user);
					System.out.println("password"+	password);
					System.out.println("port"+	port);
					System.out.println("jdbc url"+	myurl);
					String databaseUrl = "jdbc:db2://" + databaseHost + ":" + port + "/"
							+ databaseName;
					System.out.println("Loading the Database Driver");
					Class.forName("com.ibm.db2.jcc.DB2Driver");
					System.out.println("connect db");
					con = DriverManager.getConnection(databaseUrl, user, password);
					con.setAutoCommit(false);
				}
				else {
					System.out.println("VCAP_SERVICES is null");
					
				}
				return con;
			
		} 
		catch (SQLException e) {
			System.out.println("Error connecting to database");
			System.out.println("SQL Exception: " + e.toString());
		   return null;
		}
		
		catch (Exception e) {
			System.out.println("cann;t get connection"+e.toString());
			return null;
		} 
	}

}