Saturday 5 March 2011

Find occurance of SearchString in Files - Recursively for all sub-folders too.

Hi Friends ,

This is bit of small utility program that can used to search items & record its counts for each file in a folder &  recursively all files in it's subfolder too ...

Suppose I have my project source at folder D:\project\src.
This src folder lots of sub folders/packages which in urn have lots of java files/ jsp / properties / xml etc ...

Now suppose, I want to find the occurance of "Connection", "Statement","ResultSet" , in each files of the project's java source  & get it neatly to an xls file .. how do you do it ??

The below program tries to achieve the same but in crude manner ... it  uses core Java , file handling & Regex functionality to achieve this ...


 File Name ConnectionStatementResultSettrycatchfataldebugthrow
D:\project\src\com\xyz\cash\dao\AAbstractDAO.java52461028491310899
D:project\src\com\xyz\cash\dao\AEntity.java40012250044
D:\project\src\com\xyz\cash\dao\Batch.java780330515


Thanks

Rakesh

/**
 *
 * @author rakesh.praphullan
 */
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Pattern;
import java.util.regex.Matcher;

public class RegexTest {
    static String fileExtn = ".java";
    static String checkDir = "D:\\project\\src";
    static String logFile = "D:\\project\\Result_log.xls";
   
    static String[] searchStrings = {"Connection", "Statement","ResultSet","try","catch","fatal","debug","throw"};  
    private static StringBuffer logBuffer = new StringBuffer();
    private static StringBuffer csvBuffer = new StringBuffer();
    public static void logMessage(String s) {
        //logBuffer.append("" + s + "\n");
       
        System.out.println(s);
    }
   
    public static void csvMessage(ArrayList list) {
        //csvBuffer.append("");
        int size = list.size();
        for (int i = 0; i < size; i++) {
       
            String value = (String)list.get(i);
            //System.out.println("value is "+value);
            csvBuffer.append( value+ "\t");
        }
        //logBuffer.append("" + s + "\n");
        csvBuffer.append("");
        csvBuffer.append("\n");
        //System.out.println(s);
    }
    public static void csvHeader(String[] s) {
        csvBuffer.append(" File Name "+ "\t");
        for (int i = 0; i < s.length; i++) {
            csvBuffer.append(s[i] + "\t");
        }
        //logBuffer.append("" + s + "\n");
        csvBuffer.append("");
        csvBuffer.append("\n");
        //System.out.println(s);
    }
    public static void main(String[] args) {
        try{
            csvHeader(searchStrings);
        File dir = new File(checkDir);
        if (dir.isDirectory()) {
            File[] files = dir.listFiles();
            for (int i = 0; i < files.length; i++) {
                File file = files[i];
                if (file.isDirectory()) {
                    handleDir(file);
                } else {
                    handleFile(file);
                }
            }
        }
        }finally{
            try {
                writeFile(logFile, csvBuffer);
            } catch (Exception ex) {
                Logger.getLogger(RegexTest.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
    private static void handleDir(File dir) {
       
        File[] files = dir.listFiles();
            for (int i = 0; i < files.length; i++) {
                File file = files[i];
                if (file.isDirectory()) {
                    handleDir(file);
                } else {
                    handleFile(file);
                }
            }
    }
    private static void handleFile(File fileName) {
       
        if ( fileName != null)
        {
            int index = -1;
            index = fileName.getName().indexOf(fileExtn);
            if( index == -1){ return ; }
        }
        //fileName.
        StringBuffer buff = readFile(fileName);
        String path = fileName.getAbsolutePath();
        String name = path ;
        //logMessage("Inside handleFile "+name);
        int count[] = new int[searchStrings.length];
        for (int i = 0; i < searchStrings.length; i++) {
            count[i] = 0;
            String data = searchStrings[i];
            Pattern pattern = Pattern.compile(data);
            Matcher matcher =
                    pattern.matcher(buff.toString());
            boolean found = false;
            while (matcher.find()) {
                /*System.out.println("Found in File ["+name+"] the text " + matcher.group() + " starting at " +
                        "index " + matcher.start() + " and ending at index " + matcher.end());*/
                found = true;
                count[i]++;
            }
            if (!found) {
                //System.out.println("No match found for " + data+" in File "+name);
            }
        }
        // Final Analysis
        ArrayList list = new ArrayList();
        list.add(name);
        logMessage("Analysis of  File :["+name+"]");
        for (int i = 0; i < searchStrings.length; i++) {
            list.add(""+count[i]);
            if(count[i] != 0)
            {
                logMessage(" Seaarch item :["+searchStrings[i]+"] found ["+count[i]+"] times");
            }
        }
        csvMessage(list);
    }
    private static StringBuffer readFile(File filename) {
        StringBuffer buff = new StringBuffer();
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(filename);
            char current;
            while (fis.available() > 0) {
                current = (char) fis.read();
                buff.append(current);
            }
        } catch (IOException ex) {
            Logger.getLogger(ClassPathPrinter.class.getName()).log(Level.SEVERE, null, ex);
            logMessage(ex.toString());
        } finally {
            try {
                fis.close();
            } catch (IOException ex) {
                Logger.getLogger(ClassPathPrinter.class.getName()).log(Level.SEVERE, null, ex);
                logMessage(ex.toString());
            }
        }
        //System.out.println("Filecontent is  :" +buff.toString());
        return buff;
    }
    private static boolean writeFile(String filename, StringBuffer buff) throws Exception {
        boolean writeDone = false;
        FileOutputStream fout = null;
        try {
            fout = new FileOutputStream(filename);
            String data = buff.toString();
            char current;
            for(int i=0 ; i< data.length(); i++)
            {
                fout.write(data.charAt(i));
            }
            writeDone = true;
        } catch (IOException ex) {
            Logger.getLogger(RegexTest.class.getName()).log(Level.SEVERE, null, ex);
            logMessage("IOException"+ex+"");
            throw ex;
        } finally {
            try {
                fout.close();
            } catch (IOException ex) {
                Logger.getLogger(RegexTest.class.getName()).log(Level.SEVERE, null, ex);
                logMessage("IOException"+ex+"");
                throw ex;
            }
        }
        return writeDone;
    }
}

 

My ways of doing Database Growth Analysis

Hi Guys,

I was having an unusual scenario in one of my projects. The data was growing by leaps and bounds- day by day. One year after application went live now .. we have problem running reports ... CPU utilization is becoming very high .. resulting in freezing of Application on Weblogic Servers .... Load on database server is also getting huge ... . Unfortunately initial designers of application didn't have hindsight of putting Archiving-Purging logic in application . By the time I took over, we were into other trouble shooting that this was put to back burner till now ...

Things have got hot now .. we have to work on this Database growth analysis:

This is how we did it ...
1. We decided that we will capture the table record counts & table size of all Tables daily at a pre-defined time for a month.
2. We started getting these table records counts & table size in form of xls files.
3. Now we know that there will almost 30 days xls , each with details of 500 odd tables.
4. How do we analyze this data - will putting in a database table help us ?
5. Or the xls itself can be used for data manipulation. But this will be highly manual task. Can't waste time to do comparsion of each table for all 30 days , find growth every day .. then find average growth ..
6. Then we struck upon a idea - use xls , jdbc & sql-query from Java ...
7. We know SQL's are very easy to build & execute ... so why not use SQL to do this manipulation in xls.
8. There is way to connect to xls using Jdbc-Odbc bridge connection in windows.
9. Assume the xls we have is of name"qa.xls". Each worksheets is having name "qa1" , "qa2" , "qa3" ... "qa30" & have data for 30 days respectively.
10 . Each worksheet will have header & data  as :
SCHEMA    TABLENAME    ROWCOUNT   SPACEUSED

11. Now we need to create ODBC data source through Control Panel , use "Microsoft Excel Driver".
12. Give name of "Data Source Name"  as "qa-list". And select the xls file "qa.xls".
13.Odbc-Jdbc microsoft excel setup is complete now.
14.  Now we can programatically call the xls worksheet as database tables . Using joins we are able to achieve the daily growth , average growth etc ... for any specific time period.

15. Refer my other post "You can use JDBC/SQL Query in Java for Excel files" to see the compelete program listing.

16. Output from this an xls sheet with all required data growth statistics.
17. Utimately from daily data for a period of time we are able to derive a equation from xls .. which will predict what data will be available at time in future.

18. This analysis has helped us to plan for our server disc space capacity planning. Think on Archiving strategy on out growing tables ..

You can use JDBC/SQL Query in Java for Excel files

Hi Freinds,

This is one java program which will use JDBC-ODBC driver to read Excel files.
We can connect to microsoft xls , same way as we do connect to our databases.
Then we can query these xls using select query , use joins , where clause , having , group by ASC .. and all.

This is very powerful way to handle xls from Java.
Every worksheet of xls is taken as seperate Table.
In this case , i wanted to compare growth of tables in my database for a period of 7 days. Hence each worksheet for me constituted same Table -  but data with different days.

First worksheet "qas1" having table records & size for 7th Feb'11.
Second worksheet "qas2" having table records & size for 8th Feb'11. etc ..

I tried to build query dynamically form provided parameters of no. of days  we want to do data analysis. In here it was for 8 days. Hence query was generated for comparison of 8 days.

This is very crude way of doing things .. however will give idea for Java developers to proceed if needed on similiar lines ...

Thanks

Rakesh

/**
 *
 * @author rakesh.praphullan
 */
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.util.logging.Level;
import java.util.logging.Logger;
public class ExcelReader {
    private static final int DATA_COUNT = 8;
    private static final String output_file = "D:\\D\\Rakesh\\test\\SqlUtils\\src\\Output.xls";
    private static final String TableAlias = "A";
    private static final String TableNamePrefix = "qas";
    private static final int pivotTable = 1;
    private static final String pivotTableAlias = TableAlias + pivotTable;
    private static final String tab = "\t";
    public static void main(String[] args) {
        Connection c = null;
        Statement stmnt = null;
        String generatedQuery = generateQuery();
        System.out.println(" Generated Query is :[" + generatedQuery + "]");
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            c = DriverManager.getConnection("jdbc:odbc:Java-Excel", "", "");
            stmnt = c.createStatement();
            String query = "select A.SCHEMA as Schema , A.TABLENAME as TableName," + " A.ROWCOUNT as Count1, B.ROWCOUNT as Count2, C.ROWCOUNT as Count3 ,D.ROWCOUNT as Count4," + " (B.ROWCOUNT-A.ROWCOUNT) as Increment1 , (C.ROWCOUNT-B.ROWCOUNT) as Increment2 , (D.ROWCOUNT-C.ROWCOUNT) as Increment3" + " from [qas1$] A , [qas2$] B , [qas3$] C , [qas4$] D " + " where A.TABLENAME =+ B.TABLENAME and A.TABLENAME =+ C.TABLENAME and A.TABLENAME =+ D.TABLENAME and A.SCHEMA='PINDBO' order by A.ROWCOUNT DESC"; // and Year=2001;";
            System.out.println(" Current Query is :[" + query + "]");
            ResultSet rs = stmnt.executeQuery(generatedQuery);
           
            StringBuffer outputBuffer = new StringBuffer();
            outputBuffer.append(getTableHeader()); //"Schema" + tab + "TableName" + tab + "Count1" + tab + "Count2" + tab + "Count3" + tab + "Count4" + tab + "Increment1" + tab + "Increment2" + tab + "Increment3\n");
            retrieveResults( rs , outputBuffer);
            /*while (rs.next()) {
                outputBuffer.append("" + rs.getString(1) + tab);
                outputBuffer.append("" + rs.getString(2) + tab);
                outputBuffer.append("" + rs.getInt(3) + tab);
                outputBuffer.append("" + rs.getInt(4) + tab);
                outputBuffer.append("" + rs.getInt(5) + tab);
                outputBuffer.append("" + rs.getInt(6) + tab);
                outputBuffer.append("" + rs.getInt(7) + tab);
                outputBuffer.append("" + rs.getInt(8) + tab);
                outputBuffer.append("" + rs.getInt(9) + tab);
                outputBuffer.append("\n");
            } */
            System.out.println(outputBuffer.toString());
            writeFile(output_file, outputBuffer);
        } catch (Exception e) {
            System.err.println(e);
        } finally {
            try {
                stmnt.close();
                c.close();
            } catch (Exception e) {
                System.err.println(e);
            }
        }
    }
    public static boolean retrieveResults(ResultSet rs ,StringBuffer outputBuffer) throws Exception
    {
        boolean okay = true;
        while (rs.next()) {
                outputBuffer.append("" + rs.getString(1) + tab);
                outputBuffer.append("" + rs.getString(2) + tab);
                // next the counts
                for (int i = 1; i <= DATA_COUNT; i++) {
                    outputBuffer.append("" + rs.getInt(2+i) + tab);
                }
                // next the increment counts
                for (int i = 1; i < DATA_COUNT; i++) {
                    outputBuffer.append("" + rs.getInt(2+DATA_COUNT+i) + tab);
                }
               
                outputBuffer.append("\n");
            }
        return okay;
    }
    public static String getTableHeader() {
        StringBuffer headerBuffer = new StringBuffer();
        StringBuffer queryHdr = new StringBuffer();
        queryHdr.append("Schema" + tab + "TableName" + tab + "");
//outputBuffer.append("Schema" + tab + "TableName" + tab + "Count1" + tab + "Count2" + tab + "Count3" + tab + "Count4" + tab + "Increment1" + tab + "Increment2" + tab + "Increment3\n");
        StringBuffer rowCountDetails = new StringBuffer();
        StringBuffer rowIncrementDetails = new StringBuffer();
        for (int i = 1; i <= DATA_COUNT; i++) {
            rowCountDetails.append(" Day_" + i + "_Count"+tab);
            if (i != 1) {
                // skipping first row
                //(B.ROWCOUNT-A.ROWCOUNT) as Increment1 ,
                rowIncrementDetails.append("Day_" + (i - 1) + "_Increment"+tab);
                if (i == DATA_COUNT) {
                    rowIncrementDetails.append("\n");
                }
            }           
        }
        headerBuffer.append(queryHdr.toString());
        headerBuffer.append(rowCountDetails.toString());
        headerBuffer.append(rowIncrementDetails.toString());
        return headerBuffer.toString();
    }
    public static String generateQuery() {
        StringBuffer queryBuffer = new StringBuffer();

        StringBuffer queryHdr = new StringBuffer();
        queryHdr.append("Select " + pivotTableAlias + ".SCHEMA as Schema ," + pivotTableAlias + ".TABLENAME as TableName,");
        StringBuffer rowCountDetails = new StringBuffer();
        StringBuffer rowIncrementDetails = new StringBuffer();
        StringBuffer fromTableDetails = new StringBuffer(" From ");
        StringBuffer whereClauseDetails = new StringBuffer(" Where ");
        StringBuffer postwhereClauseDetails = new StringBuffer(" " + pivotTableAlias + ".SCHEMA='PINDBO' order by " + pivotTableAlias + ".ROWCOUNT DESC");
        for (int i = 1; i <= DATA_COUNT; i++) {
            rowCountDetails.append(" " + TableAlias + i + ".ROWCOUNT as Day_" + i + "Count,");
            if (i != 1) {
                // skipping first row
                //(B.ROWCOUNT-A.ROWCOUNT) as Increment1 ,
                rowIncrementDetails.append(" (" + TableAlias + i + ".ROWCOUNT - " + TableAlias + (i - 1) + ".ROWCOUNT ) as Day_" + (i - 1) + "Increment");
                if (i != DATA_COUNT) {
                    rowIncrementDetails.append(",");
                }
            }
            // from [qas1$] A ,
            fromTableDetails.append(" [" + TableNamePrefix + i + "$] " + TableAlias + i + " ");
            if (i != DATA_COUNT) {
                fromTableDetails.append(",");
            }
            if (i != pivotTable) {
                // A.TABLENAME =+ B.TABLENAME and
                whereClauseDetails.append(pivotTableAlias + ".TABLENAME =+ " + TableAlias + i + ".TABLENAME and ");
            }
        }
        queryBuffer.append(queryHdr.toString());
        queryBuffer.append(rowCountDetails.toString());
        queryBuffer.append(rowIncrementDetails.toString());
        queryBuffer.append(fromTableDetails.toString());
        queryBuffer.append(whereClauseDetails.toString());
        queryBuffer.append(postwhereClauseDetails.toString());
        return queryBuffer.toString();
    }
    private static boolean writeFile(String filename, StringBuffer buff) {
        boolean writeDone = false;
        FileOutputStream fout = null;
        try {
            fout = new FileOutputStream(filename);
            String data = buff.toString();
            char current;
            for (int i = 0; i < data.length(); i++) {
                fout.write(data.charAt(i));
            }
            writeDone = true;
        } catch (IOException ex) {
            Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                fout.close();
            } catch (IOException ex) {
                Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return writeDone;
    }
}