Parsing sql database file to get a wanted column field in java

Question

I am working to get some column and table names from my .sql file.

The File is given below,which has my database data.Using this,i have to fetch the column and table names.

DROP TABLE ADRepHealthTest_TREND CASCADE CONSTRAINTS;
CREATE TABLE ADRepHealthTest_TREND(
TRGT_HOST       varchar2(32),
PORT_NO         varchar2(32),
SITE_NAME       varchar2(64),
INFO            varchar2(128),
MSMT_HOST       varchar2(32),
MSMT_TIME       date,
PERIOD          varchar2(32),
FAILS_COUNT_MIN Number(20,4) NOT NULL,
FAILS_COUNT_MAX Number(20,4) NOT NULL,
FAILS_COUNT_STCNT varchar2(32) NOT NULL,
TOTAL_COUNT_MIN Number(20,4) NOT NULL,
TOTAL_COUNT_MAX Number(20,4) NOT NULL,
TOTAL_COUNT_STCNT varchar2(32) NOT NULL,
PERCENT_COUNT_MIN Number(20,4) NOT NULL,
PERCENT_COUNT_MAX Number(20,4) NOT NULL,
PERCENT_COUNT_STCNT varchar2(32) NOT NULL);

I read the file perfectly using Scanner class.The entire data have been read perfectly.

Now i need the column and table names alone from that parsed data.I struggled lot to tokenize or get the column & table names perfectly.

Guide me to proceed ahead !!!

I parsed the entire text but couldnt able to identify the column names perfectly !! The parsing is normal way @ Alex — JAVA Beginner
– JAVA Beginner, Commented Dec 18, 2013 at 11:17
@SridharRamakrishnan: you should tell if you have original database available to you and what you are actually trying to do. Your question feels to me like XY problem — mvp
– mvp, Commented Dec 18, 2013 at 11:49

Noman ali abbasi · Accepted Answer · 2013-12-18 11:16:12Z

2

Please try with the following code:

public class Test {
    public static void main(String[] args) {        
        StringBuffer tableBuffer = new StringBuffer();
        StringBuffer columnBuffer = new StringBuffer();
        try 
        {
            Scanner scanner = new Scanner(new FileInputStream(new File("D:\\test.sql")));
            while(scanner.hasNextLine()){
                String token = scanner.nextLine().toUpperCase();
                if(token.contains("TABLE")){
                    tableBuffer.append(token).append("\n");
                }

                if(isContainDataType(token)){
                    columnBuffer.append(token).append("\n");
                }
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        System.out.println("table:\n" + tableBuffer.toString());
        System.out.println("column:\n" + columnBuffer.toString());
    }

    private static boolean isContainDataType(String token){     
        if(token.contains("VARCHAR")){
            return true;
        }else if(token.contains("NUMBER")){
            return true;
        }

        return false;
    }
}

answered Dec 18, 2013 at 11:16

Noman ali abbasi

5392 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Noman ali abbasi Over a year ago

you can tweak it accordingly to your problem :)

JAVA Beginner Over a year ago

I dont need the datatypes noman !! Im trying to remove that !!

Noman ali abbasi Over a year ago

Try with it, this will only return you colun name: if(isContainDataType(token)){ columnBuffer.append(token.split(" ")[0]).append("\n"); }

Santosh Over a year ago

This will append the table name twice to tableBuffer.

JAVA Beginner · Accepted Answer · 2013-12-19 08:03:13Z

1

I tried by getting logics from santhosh and noman , Finally got it right now !! thanks all :)

import java.io.*;
import java.util.*;
public class Test3 
{
    public static void main(String[] args) throws FileNotFoundException
    {
        File file = new File("test.sql");
        Scanner scanner = new Scanner(file);
        StringTokenizer st = null;
        while(scanner.hasNextLine())
        {
            String lines = scanner.nextLine();
            if(lines.startsWith("CREATE TABLE"))
        {
            StringTokenizer st1 = new StringTokenizer(lines,"(");
            System.out.println("*****************Table Name:"+st1.nextToken().substring(13)+"**********************");
        }
            st = new StringTokenizer(lines);

        if(!lines.equals("") && lines.indexOf("CREATE") == -1 &&  lines.indexOf("ALTER") == -1 && lines.indexOf("DROP") == -1 && !lines.equals("(") && !lines.equals(");"))
        {
            if(lines.startsWith(" ") || !lines.startsWith(" "))
            {
                System.out.println("Coloumn names :"+st.nextToken());
            }
        }
        }
    }
}

answered Dec 19, 2013 at 8:03

JAVA Beginner

4814 silver badges15 bronze badges

1 Comment

ahaaman Over a year ago

Thanks for the help, you just saved me hours and hours of work :) Was trying to use JBoss Forge to generate Entity classes from tables, but apparently it has issues with Oracle DB and customer's tables have like > 250 columns => Nightmare to manually create java classes

mvp · Accepted Answer · 2013-12-18 11:33:30Z

0

If you have full .sql file and it is not very large (less than few megabytes), instead of parsing it, it is quite possible that it will be much easier and faster to simply import it and create full database instead. If you want to do it locally, you can import it into SQLite, using command like this:

sqlite3 -init mydump.sql mydatabase.db ""

Then, you can get everything you want from SQLite tables.

answered Dec 18, 2013 at 11:33

mvp

118k15 gold badges132 silver badges155 bronze badges

1 Comment

mvp Over a year ago

If you need to validate full database, this should work perfectly for you (as long as your DB flavor does not use very fancy syntax for its SQL dump and SQLite will not choke on it).

Java Man · Accepted Answer · 2013-12-18 11:44:29Z

0

use this you can get whole data of sqlite file without use scanner class.

 Process p = rt.exec(new String[]{"/bin/sh", "-c", "sqlite3 /home/ubuntu/testingdb.sqlite .dump > /home/ubuntu/success11.sql"});

answered Dec 18, 2013 at 11:44

Java Man

1,8703 gold badges21 silver badges44 bronze badges

Comments

Santosh · Accepted Answer · 2013-12-18 12:05:55Z

Here is fully functional code. I assumed the Scanner part (taken from here ). At the end, the table names and columns are collected in two Lists.

public class DataParser{


    public static void main(String args[]) throws FileNotFoundException {

        List<String> tables = new ArrayList<String>();
        List<String> columns = new ArrayList<String>();

        //creating File instance to reference text file in Java
        File text = new File("C:\\data.sql");      
        //Creating Scanner instnace to read File in Java
        Scanner scnr = new Scanner(text);      
        //Reading each line of file using Scanner class
        int lineNumber = 1;
        while(scnr.hasNextLine()){
            String line = scnr.nextLine();

            if(line.indexOf("DROP") != -1){
                tables.add(parseTable(line));
            }else{
                if(line.indexOf("CREATE") == -1)
                columns.add(parseColumn(line));
            }

            lineNumber++;
        }       

        System.out.println("Tables : "+tables);
        System.out.println("Columns : "+columns);
    }   

    public static String parseTable(String line){

        String[] arr = line.split(" ");
        return arr[2];
    }
    public static String parseColumn(String line){

        String[] arr = line.split(" ");
        return arr[0];

        }

 }

I assumed the path of the data file (data.sql).

Collectives™ on Stack Overflow

Parsing sql database file to get a wanted column field in java

5 Answers 5

4 Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related