Generic SQL Select Statement Executer in Java

In this post I will show you how to write a Java program that can allow you to run any SQL select statement against SQL Server and write output to csv file. While this is a simple task to use ResultSet and write output to file but imagine you need to keep on modifying output columns based on changes in business requirements or you need to output large number of SQL tables as csv files. In that case task gets little tedious and every time user request a new field to be added or removed you have to modify your class. This is where this trick comes handy.

You can download full source code from here : Generic SQL Select Statement Executer in Java

1. Lets create a new project in eclipse called SQLSelector and add a class file called GenericSelecor.java.

2. Next add sqljdbc4.jar and sqljdbc_auth.dll to your solution. This is required to connect to Microsoft SQL Server.

3. Add sqljdbc4.jar as a reference to your project by right clicking on project and going to properties and select java build path and libraries tab and click on Add Jar

4. Lets add two Properties file. One that is common for all SQL statements like database name,output folder etc and other one specific for current sql statement that will include Select statement, output file name and other specific information. I have Northwind database on my local machine and I want to select all customers from that database.

I have PROD.Properties file which contains database connection specific information.
Relative.Output.Folder=C\:\\SQLOutput\\
Server=localhost;instanceName=SQL2008
ODBC.DataSource=Northwind

I have Generic.Properties file which contains specific select statement and flag to indicate if string outputs should have double quotes
Statement=SELECT CustomerID ,CompanyName ,ContactName ,ContactTitle ,Address ,City ,Region ,PostalCode ,Country ,Phone ,Fax FROM Customers
OutputFileName = Customers.csv
StringOutputInDoubleQuote=Y

5. Now in GenericSelector class read those two properties files as arguments and read all the values in corresponding variables.

  
propertyFile = args[0];
statementFile= args[1];

Properties dpr = new Properties();
Properties spr = new Properties();

//Read all common variables from properties file
try 
{ 
 FileInputStream is = new FileInputStream(propertyFile);
 dpr.load(is);
 is.close();
   
 Server=dpr.getProperty("Server");
 DataSource=dpr.getProperty("ODBC.DataSource");
 
 OutputFolder=dpr.getProperty("Relative.Output.Folder");
 
}
catch(Exception e)
{
 e.printStackTrace();
 System.exit(1); 
}
// Read all specific variables from properties file
try 
{ 
 FileInputStream is = new FileInputStream(statementFile);
 spr.load(is);
 is.close();
 
 selectSatement =spr.getProperty("Statement"); // This is the select statement from external file
 outputFileName =spr.getProperty("OutputFileName");
 stringOutputInDoubleQuote = spr.getProperty("StringOutputInDoubleQuote").toUpperCase();
}
catch(Exception e)
{
 e.printStackTrace();
 System.exit(1);
 
}  

6.Load appropriate class driver and create connection to database. In this case I am using MSSQL server and connecting to Northwind database. Get a ResultSet by executing select statement. Get ResultSetMetaData from result set. This will help in identifying type of columns that are returned and name of those columns which then can be used to get the records from ResultSet without actually hard coding column names in application. ResultSetMetaData provides methods getColumnTypeName() and getColumnName() which are used to retrieve specific column information.

 
// Here we create a connection to database and run the query and then use metadata to generate csv output   
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String sqlCmd = "";
con = DriverManager.getConnection("jdbc:sqlserver://"+Server+";databaseName="+DataSource+";integratedSecurity=true");

sqlCmd = selectSatement;
PreparedStatement stat = con.prepareStatement(sqlCmd);
ResultSet rs = stat.executeQuery(); // Actual Data
ResultSetMetaData rsmd = rs.getMetaData(); // Metadata from current result set.

int columnCount = rsmd.getColumnCount();

String[] columnTypes = new String[columnCount]; 
String[] columnNames = new String[columnCount];

StringBuilder sb = new  StringBuilder();
rows=0;
// The column count starts from 1
for (int i = 1; i < columnCount + 1; i++ ) 
{
 columnTypes[i-1] = rsmd.getColumnTypeName(i); // Get Column Type Names
 String name = rsmd.getColumnName(i);  // Get actual column names
 columnNames[i-1] = name;
 sb.append(name);
 sb.append( i==columnCount ? "\n" : ",");
}                       

7. Now Iterate through each records in ResultSet and for each record find value by using its column name and column type that was retrieved using ResultSetMetaData and write to output file. Here I have checked for type of the column so I can use specific get method. If you don't require any formatting then you can use getObject() method of result set without worrying about underlying record type.

 
String DATETIME_FORMAT_QRY = "dd/MM/yyyy";
java.text.SimpleDateFormat sdf2 = new java.text.SimpleDateFormat(DATETIME_FORMAT_QRY);
   
while(rs.next())
{
 
 rows++;
 // Based on column type call appropriate getXXX() method to get value from database
 // If you don't need specifc formatting based on data type you can use getObject() method instead of specific get method
 for (int i = 1; i < columnCount + 1; i++ ) 
 {
   // If output column type is string type then either output it in double quote or as is based on flag 
   if(columnTypes[i-1]== "nchar" || columnTypes[i-1]== "nvarchar")
    sb.append((rs.getString(columnNames[i-1]) == null ? "" : (stringOutputInDoubleQuote.equals("Y") ? "\""+ rs.getString(columnNames[i-1]).trim() + "\"" : rs.getString(columnNames[i-1]).trim())));
   else if(columnTypes[i-1]== "decimal")    
    sb.append(rs.getDouble(columnNames[i-1]));
   else if(columnTypes[i-1]== "int" || columnTypes[i-1]== "tinyint")   
    sb.append(rs.getInt(columnNames[i-1]));
   else if(columnTypes[i-1]== "datetime")   
    sb.append(rs.getDate(columnNames[i-1])==null ? "" : sdf2.format(rs.getDate(columnNames[i-1])));
   else  
    sb.append("ERROR IN INTERFACE : No check added for "+columnNames[i-1] );
  
  sb.append( i==columnCount ? "\n" : ",");
 }
 

 // Write to file
 if(rows>0)
 {
  if(rows==1) // Delete old file
  {
   File delfile = new File(OutputFolder+"/"+outputFileName);
   delfile.delete();
  }
  FileOutputStream outFile = new FileOutputStream(OutputFolder+"/"+outputFileName,true);
  outFile.write(sb.toString().getBytes());
  outFile.close();
  sb = new StringBuilder(); // Reset string builder for next iteration
  
 }
 
}                        

That is it!!! You have a fully functional java code that can create any table as csv output. All you need to do is change SQL in Generic.Properties file. Happy Coding !!!

Only allow digits in Console Application in C#

If you are writing a console application in C# and you want to restrict user to only enter digits for certain variable there is an option to use ConsoleKeyInfo struct to read each key user input and take action accordingly. This struct provides a way to find which key user has entered in console application and check if it is a number or not using Char.IsNumber() method.
Below is the complete source code that only allow user to enter digits for a field. If user type any other characters it simply ignores them.

 
Console.WriteLine("Enter Numeric Value : ");
ConsoleKeyInfo key;
string inputStr = "";
do
{
 key = Console.ReadKey(true);
 if (key.Key != ConsoleKey.Backspace && key.Key != ConsoleKey.Enter)
 {
  if (char.IsNumber(key.KeyChar))//Check if it is a number
  {
   inputStr += key.KeyChar;
   Console.Write(key.KeyChar);
  }
 }
 else
 {
  if (key.Key == ConsoleKey.Backspace && inputStr.Length > 0)
  {
   inputStr = inputStr.Substring(0, (inputStr.Length - 1));
   Console.Write("\b \b");
  }
 }

} while (key.Key != ConsoleKey.Enter);

Console.WriteLine("\nNumber you entered is {0}", inputStr);

Find Distinct Objects from List of Objects using LINQ

To find distinct values from list of values in C# is a one line task by using LINQ's Distinct() method. This works well with primitive types but if you run the same method on List of custom Objects you will not get distinct objects based on its properties. To achieve this you an option to implement IEqualityComparer interface and use it to find distinct objects based on its properties. In the implementation of  Equals method you can define which properties to check for equality.

Below code provides the complete solution to get distinct objects from list of objects.

 
 using System.Collections.Generic;
    public class Team
    {
        public string Name {get;set;}
        public int Score {get;set;}
    }
        //Create some dummy data with duplicates
        public List<Team> lstTeam = new List<Team>{
        new Team{Name="Brazil", Score=1},
        new Team{Name="Man U", Score=1},
        new Team{Name="Man U", Score=1},
        new Team{Name="Brazil", Score=2},
        new Team{Name="Man U", Score=2},
        new Team{Name="Brazil", Score=2}

        };   

    //This is where we use equality comparer implementation to find unique records
     List<Team> lstDistictTeams = lstTeam.Distinct<Team>(new DistinctComparer()).ToList();

     foreach(Team t in lstDistictTeams) // Output Distinct Objects
     {
         Console.WriteLine("Team {0} has Score {1}",t.Name,t.Score);
     }

    //This class provides a way to compare two objects are equal or not
     public class DistinctComparer : IEqualityComparer<Team>
        {
            public bool Equals(Team x, Team y)
            {
                return (x.Name == y.Name && x.Score == y.Score); // Here you compare properties for equality
            }
            public int GetHashCode(Team obj)
            {
                return (obj.Name.GetHashCode() + obj.score.GetHashCode());
            }
        }