Convert IP String to numeric representation and numeric representation to IP String in SQL Server

In the post Convert IP String to numeric representation and numeric representation to IP String in Java we dealt with IP conversions in Java. In this post we will do just the same, but instead using Java we will use SQL Server. Converting the Java code to MS SQL is very simple. Not much knowledge in T-SQL is needed, because we are mostly dealing with mathematical operations. We will simply create 2 functions. Here is the first function for converting bigint to IP:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[bigint_to_ip]
(
        @ip bigint
)
RETURNS varchar(15)
AS
BEGIN
    DECLARE @octet0 varchar(3)
    DECLARE @octet1 varchar(3)
    DECLARE @oct
et2 varchar(3)
    DECLARE @octet3 varchar(3)
    SET @octet3 = (@ip / power(2, 24)) % 256
    SET @octet2 = (@ip / power(2, 16)) % 256
    SET @octet1 = (@ip / power(2, 8)) % 256
    SET @octet0 = @ip % 256
    RETURN @octet3 + '.' + @octet2 + '.' + @octet1 + '.' + @octet0
END

The second function which converts IP to bigint is a bit more tricky, since unlike java “split” function, SQL Server T-SQL doesn’t present any comfortable way of handling strings in the manner Java does. Luckily, SQL Server does have a function called “PARSENAME” which is used for getting a part of an object. Assuming the parts of an object are separated with dots, it is possible to get any part of the object. for example:

DECLARE @myObject varchar(100)
SET @myObject = 'this.is.my.object'
PARSENAME(@ObjectName, 4) -- Return: this
PARSENAME(@ObjectName, 3) -- Return: is
PARSENAME(@ObjectName, 2) -- Return: my
PARSENAME(@ObjectName, 1) -- Return: object

Note, that the position is reversed to the intuitive logic, and that position “1” means the last part of the string and not the first.

We can use the “PARSENAME” function in order to easily split the IP string to its components. Here is the second function that converts IP to bigint:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ip_to_bigint]
(
        @ip varchar(15)
)
RETURNS bigint
AS
BEGIN
    DECLARE @octet0 bigint
    DECLARE @octet1 bigint
    DECLARE @octet2 bigint
    DECLARE @octet3 bigint
    SET @octet3 = ParseName(@ip, 4)
    SET @octet2 = ParseName(@ip, 3)
    SET @octet1 = ParseName(@ip, 2)
    SET @octet0 = ParseName(@ip, 1)
    RETURN @octet3 * power(2, 24) + @octet2 * power(2, 16) + @octet1 * power(2, 8) + @octet0
END

Convert IP String to numeric representation and numeric representation to IP String in Java

There are times we would like to convert IP string to its numeric representation. For example, if we would like to store in our database ranges of IPs and for each IP its Geo location or organization or ISP or any other IP based service. Storing the IP as a String doesn’t have much meaning, since we won’t be able to do range comparisons. For example if we had a database table named “country_ip” with the following columns:

• begin_ip – Beginning of the IP range (long/bigint)
• end_ip – End of IP range (long/bigint)
• country_code – Country code corresponding to the IP range (2 or 3 characters depending on the standard used)

Note that the “being_ip” and “end_ip” are in their numeric representation and are stored as long/bigint values in the database.

Now, suppose we have an IP that we would like to find its country code.

First we will have to convert the IP to its numeric representation and then we would find the country code by writing a simple SQL query:

select country_code from country_ip where :param_ip >= begin_ip and :param_ip <= end_ip

The IP conversion functions, which convert an IP to its numeric representation and vise versa, are quite simple. A string IP is constructed from 4 octets separated by “.” (of course, this is going to change gradually with the entrance of IPv6, but as it is for now all services are still in IPv4). Each octet is a byte value from 0 to 255. In order to convert a string IP to its numeric representation all we have to do is split the IP string to its 4 octets and then put each octet into a long variable on its correct position. For example, the first octet from the right is entered as it is, from the “0” bit to the “7” bit, the second (from the right), should be placed on the “8” bit to the “15” bit and so on. Placing an octet on a specific location in the long variable can be done simply by multiplying it by 256 and adding its value (to the long variable), which is actually 2 power 8 (8 bits). The third octet which should be put on the 16th bit and therefore should be multiplied by 2 power 16 (65536) and so on. Since we are multiplying by numbers which are base of 2, it is more efficient to use shit left operation. This operation simply moves all the bits of a number to the left “n” times. This is how this operation is written in Java:

x << n

which means: move the bits in “x” “n” times to the left, or in other words: multiply “x” by 2 power “n”. or shortly:

x << n = x * 2 ^ n

where “^” is used as power operator.

For example:

10 << 1 = 10 * 2 ^ 1 = 10 * 2 = 20
10 << 3 = 10 * 2 ^ 3 = 10 * 8 = 80

Converting a numeric IP to a string IP can easily by done by doing the reverse operation, which means dividing and taking the reminder. Of course that instead of using straight division shift right operation can be used, which move all the bits of a number to the right “n” times.

Let’s have a look at the code of the IP conversion functions. It is very simple and pretty much speaks for itself:

package com.bashan.blog;
public class IpUtils {
  public static String longToIp(long longIp) {
    int octet3 = (int) ((longIp >> 24) % 256);
    int octet2 = (int) ((longIp >> 16) % 256);
    int octet1 = (int) ((longIp >> 8) % 256);
    int octet0 = (int) ((longIp) % 256);
    return octet3 + "." + octet2 + "." + octet1 + "." + octet0;
  }
  public static long ipToLong(String ip) {
    String[] octets = ip.split("\\.");
    return (Long.parseLong(octets[0]) << 24) + (Integer.parseInt(octets[1]) << 16) +
        (Integer.parseInt(octets[2]) << 8) + Integer.parseInt(octets[3]);
  }
  public static void main(String[] args) {
    System.out.println("IP 34.56.78.34 as a number: " + ipToLong("34.56.78.34"));
    System.out.println("and converting back to ip: " + longToIp(574115362));
    System.out.println("IP 0.0.0.0 as a number: " + ipToLong("0.0.0.0"));
    System.out.println("and converting back to ip: " + longToIp(0));
    System.out.println("IP 255.255.255.255 as a number: " + ipToLong("255.255.255.255"));
    System.out.println("and converting back to ip: " + longToIp(4294967295L));
  }
}

and this is the test program output:

IP 34.56.78.34 as a number: 574115362
and converting back to ip: 34.56.78.34
IP 0.0.0.0 as a number: 0
and converting back to ip: 0.0.0.0
IP 255.255.255.255 as a number: 4294967295
and converting back to ip: 255.255.255.255

Getting all time zones of a Country in Java

In this post I wrote about how to get a complete time zone list in java. In terms of UI, it is not always the best thing to show a complete list of time zones. The list is quite long and the data is ordered by continent. Sometimes it makes it harder to find a time zone. A nice solution is allowing to filter time zones according to a selected country. Unfortunately, there is no built-in way to know the time zones of a given locale. But, there is a very nice open source project called ICU4J (International Components for Unicode) that allows very easily to get all time zones of a specific Locale. In order to that, we just have to call the getAvailableIDs method of the com.ibm.icu.util.TimeZone class. Here is an example:

String[] timeZones = com.ibm.icu.util.TimeZone.getAvailableIDs(countryCode);

This example will return all the available time zones in Israel. Note that the input to this function is country code. If we had a locale we could have written:

String[] timeZones = com.ibm.icu.util.TimeZone.getAvailableIDs(locale.getCountry());

Note that the result of this function is array of time zone ids. This result can be easily converted to an array of time zones:

String[] timeZones = com.ibm.icu.util.TimeZone.getAvailableIDs(countryCode);
List<TimeZone> timeZoneList = new ArrayList<TimeZone>();
for (String timeZone : timeZones)
{
  timeZoneList.add(TimeZone.getTimeZone(timeZone));
}

Programmatically Delete a database in SQL server

Sometimes we would like to programmatically delete SQL server database. Simply writing:

DROP DATABASE 'database_name'

is not always a good solution, for 2 main reasons:

1. The database may be already deleted, therefore we will get exception telling there is no such database to delete.
2. There may still be existing connections to the database, preventing from the database being deleted.

The first problem can be easily solved by checking the existence of the database before deleting it:

IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = 'database_name') BEGIN
DROP DATABASE 'database_name'
END
GO

Solving the second problem is a bit harder. In order to delete the database, all existing connections needed to be closed. This should be done carefully since we do not want to close our own connection. This script, first closes all existing connections and then drops the database:

DECLARE @spid INT,
@cnt INT,
@sql VARCHAR(255)
IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = 'database_name') BEGIN
SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE dbid = DB_ID('database_name')
AND spid != @@SPID
PRINT 'Starting to KILL '+RTRIM(@cnt)+' processes.'
WHILE @spid IS NOT NULL
BEGIN
PRINT 'About to KILL '+RTRIM(@spid)
SET @sql = 'KILL '+RTRIM(@spid)
EXEC(@sql)
SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE dbid = DB_ID('database_name')
AND spid != @@SPID
PRINT RTRIM(@cnt)+' processes remain.'
END
DROP DATABASE 'database_name'
END
GO

Note that the name of the database (“database_name” in this example) can be replaced with SQL parameter.

Hibernate Bi-Directional Many to Many mapping

There are times we have many to many relation between 2 objects (tables). The many to many relationship between 2 tables is defined by a third table. For example, suppose we 2 tables: “customer” and “service” and we say that a customer can be related to more than one service, we add a third table: “user_customer” that will hold the relations between customers and services.

Let’s summarize the structure of these 3 tables:

• customer: This table holds customers. The fields in this table are:
• customer_id (primary key)
• first_name
• last_name
• service: This table holds list of services that a customer can receive. For example, cables TV customer can get the following services: Sports, Drama, News etc’. A customer can get more than one service. The fields in this table are:
• service_id (primary key)
• name
• customer_service: This table defines the relation between customers and services. It connected users to services. It contains only the key from each table:
• user_id
• service_id

Note that “customer” and “service” tables used in this example doesn’t contain all the business data usually would have been used in the real world. This is to make things simpler.

Hibernate has a special annotation that helps us to define this relation: @ManyToMany. This is how this annotation is being used:

This is the Customer class representing the “customer” table:

package com.bashan.blog.hibernate;
import javax.persistence.*;
import java.util.Set;
@Entity
@Table(name = "customer")
public class Customer {
  @Id
  @Column(name = "customer_id")
  private Integer customerId;
  @Column(name = "first_name")
  private String firstName;
  @Column(name = "last_name")
  private String lastName;
  @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
  @JoinTable(name = "customer_service",
      joinColumns = {@JoinColumn(name = "customer_id")},
      inverseJoinColumns = @JoinColumn(name = "service_id"))
  private Set<Service> services;
  public Integer getCustomerId() {
    return customerId;
  }
  public void setCustomerId(Integer customerId) {
    this.customerId = customerId;
  }
  public String getFirstName() {
    return firstName;
  }
  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }
  public String getLastName() {
    return lastName;
  }
  public void setLastName(String lastName) {
    this.lastName = lastName;
  }
}

This is the Service class representing the “service” table:

package com.bashan.blog.hibernate;
import javax.persistence.*;
import java.util.Set;
@Entity
@Table(name = "service")
public class Service {
  @Id
  @Column(name = "service_id")
  private Integer serviceId;
  @Column(name = "name")
  private String name;
  @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
  @JoinTable(name = "customer_service",
      joinColumns = {@JoinColumn(name = "service_id")},
      inverseJoinColumns = @JoinColumn(name = "customer_id"))
  private Set<Customer> customers;
  public Integer getServiceId() {
    return serviceId;
  }
  public void setServiceId(Integer serviceId) {
    this.serviceId = serviceId;
  }
  public String getName() {
    return name;
  }
  public void setName(String name) {
    this.name = name;
  }
}

Not that there is no need to define any CustomerService object since the @ManyToMany annotation already contains this information in the “name” property of the @JoinTable.

The code for these 2 classes can be found in this link.

Hibernate Annotations CollectionOfElements to create a Map

There are times is it very comfortable using Map data structure to easily get elements by some key. For example, suppose we have in our database a “user” table, and we would like easily store and retrieve user properties.

Hibernate gives a nice solution to this issue, by using the annotation @CollectionOfElements. We will see a demonstration of using this annotation with our “user” table. Suppose we have 2 tables in out database:

• user: Contains users information. The table contains the following fields:
  • user_id
  • first_name
  • last_name
  • password
• user_property: Stores user properties. The tables contains the following fields:
  • user_id
  • key
  • value

The structure of these 2 tables is quite simple and straight forward, therefore, we will not broaden on it.

We would like that “user_property” will be a Map containing key and value pairs, rather than being a collection in “user”. We will use @CollectionOfElements annotation in order to achieve that. This is how our User class looks like:

package com.bashan.blog.hibernate;
import org.hibernate.annotations.CollectionOfElements;
import javax.persistence.*;
import java.io.Serializable;
import java.util.Map;
@Entity
@Table(name = "user")
public class User implements Serializable {
  @Id
  @Column(name = "user_id")
  private Integer userId;
  @Column(name = "first_name")
  private String firstName;
  @Column(name = "last_name")
  private String last_name;
  @Column(name = "password")
  private String password;
  @CollectionOfElements
  @JoinTable(name = "user_property", joinColumns = @JoinColumn(name = "user_id"))
  @org.hibernate.annotations.MapKey(columns = {@Column(name = "key")})
  @Column(name = "value")
  private Map<String, String> properties;
  public Integer getUserId() {
    return userId;
  }
  public void setUserId(Integer userId) {
    this.userId = userId;
  }
  public String getFirstName() {
    return firstName;
  }
  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }
  public String getLast_name() {
    return last_name;
  }
  public void setLast_name(String last_name) {
    this.last_name = last_name;
  }
  public String getPassword() {
    return password;
  }
  public void setPassword(String password) {
    this.password = password;
  }
  public Map<String, String> getProperties() {
    return properties;
  }
  public void setProperties(Map<String, String> properties) {
    this.properties = properties;
  }
}

Note that no class exists for “user_properties” table. In this case the @CollectionOfElements mapping acts like in @ManyToMany mapping. The link to “user_properties” table is defined by the “name” property of @JoinTable. Also pay attention to the fact the @MapKey annotation that is used here belongs to “org.hibernate.annotations” and not to the “javax.persistence” package.

You can also download this class by pressing this link.

Programtically compile Java source code

There are times we need to compile Java code from our application. For example, if we are dynamically generating some specific Java code to be packaged and sent to some customer.
Of course we can use "javac.exe" in order to achieve our goal. But executing "javac" is less programmatic way of doing the job.
Ever since Java 1.6 there are set of tools allowing us to compile Java sources directly from Java code. We will make use these tools in order to build a neat Java source code compiler, that will allow us to compile java files as well as java strings representing source code.
We will make use of the following classes supplied by the Java language:

• StandardJavaFileManager: This class is used for adding classes and jars to the compiler as well as determining the output of the compiled classes.
  
• JavaCompiler: This class is the actual compiler responsible of compiling all the the sources and other resources added by StandardJavaFileManager.
• JavaCompiler.CompilationTask: This is the actual compilation task. The compilation takes place by calling it method: call.
• SimpleJavaFileObject: We will extend this class in order to introduce the ability to add directly a String as a Java source code.

Out main class is called: JavaCodeCompiler. This is a wrapper to the Java compiler classes. This class introduces the following important methods:

• addSource: This method adds all the Java classes under a given directory. It knows to handle directories and subdirectories as well. In addition, there is another overloaded addSource method, that allows adding a string represting a Java class. This is sometimes useful, when we would like to dynamically generate a Java source code.
• compile: This methods does the actual compilation. It takes all the resources we added, compiles them and put the result in the output directory we defined in the class constructor.

This is how the JavaCodeCompiler looks like:

package com.bashan.blog.compile;
import javax.tools.*;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class JavaCodeCompiler {
  private static final Pattern PATTERN_CLASS_NAME =
        Pattern.compile(".*?class\\s*?([a-zA-Z_][a-zA-Z0-9_]*)\\s*?", Pattern.DOTALL);
  private JavaCompiler compiler;
  private File dirOutClasses;
  private StandardJavaFileManager filemanager;
  protected List<File> fileSources;
  protected List<JavaFileObject> sources;
  public JavaCodeCompiler(File dirOutClasses) throws IOException {
    compiler = ToolProvider.getSystemJavaCompiler();
    filemanager = compiler.getStandardFileManager(null, null, null);
    this.dirOutClasses = dirOutClasses;
    fileSources = new ArrayList<File>();
    sources = new ArrayList<JavaFileObject>();
  }
  public void addSource(File dir) throws Exception {
    fileSources.clear();
    addDir(dir);
    sources = (List<JavaFileObject>) filemanager.
        getJavaFileObjects(fileSources.toArray(new File[fileSources.size()]));
  }
  private void addDir(File dir) {
    File[] files = dir.listFiles();
    for (File file : files) {
      if (file.isDirectory()) {
        addDir(file);
      } else if (file.getPath().endsWith(JavaFileObject.Kind.SOURCE.extension)) {
        fileSources.add(file);
      }
    }
  }
  private String getClassName(String code)
  {
    Matcher matcher = PATTERN_CLASS_NAME.matcher(code);
    if (matcher.find())
    {
      return matcher.group(1);
    }
    return null;
  }
  public void addSource(String code) {
    String className = getClassName(code);
    sources.add(new JavaSourceString(className, code));
  }
  public void compile() throws IOException, CompileException {
    try {
      dirOutClasses.mkdirs();
      filemanager.setLocation(StandardLocation.CLASS_OUTPUT, Collections.singleton(dirOutClasses));
      JavaCompiler.CompilationTask task = compiler.getTask(null, filemanager, null, null, null, sources);
      if (!task.call()) {
        throw new CompileException("Failed compiling classes");
      }
    }
    finally {
      filemanager.close();
    }
  }
}

In addition to this class, there are 2 more classes used. One is: CompileException. This class is defined since the Java compilation task returns “true” or “false” as success/failure indication, an we would like to generate a more proper mechanism that will force user to deal with compilation failures. This is how this class looks:

package com.bashan.blog.compile;
public class CompileException extends Exception {
  public CompileException() {
  }
  public CompileException(String message) {
    super(message);
  }
  public CompileException(String message, Throwable cause) {
    super(message, cause);
  }
  public CompileException(Throwable cause) {
    super(cause);
  }
}

And the final class named: JavaSourceString extends the class SimpleJavaFileObject which supplied by Java. It gives the ability to add String Java source code. This is how it looks:

package com.bashan.blog.compile;
import javax.tools.SimpleJavaFileObject;
import java.net.URI;
public class JavaSourceString extends SimpleJavaFileObject {
  final String code;
  JavaSourceString(String name, String code) {
    super(URI.create("string:///" + name.replace('.', '/') + Kind.SOURCE.extension), Kind.SOURCE);
    this.code = code;
  }
  @Override
  public CharSequence getCharContent(boolean ignoreEncodingErrors) {
    return code;
  }
}

This is a basic Java Source code compiler. You can extend its capabilities further more, for example: allowing to add directly a Velocity template representing a dynamically created Java class.

You can download the classes in this post directly by using this link.