CODEWITHSUNDEEP
javasqlstringfile-io
JAVA Beginner
JAVA Beginner

Reputation: 481

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

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 !!!

Upvotes: 1

Views: 2310

Answers (5)

JAVA Beginner
JAVA Beginner

Reputation: 481

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());
            }
        }
        }
    }
}

Upvotes: 1

Santosh
Santosh

Reputation: 17893

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).

Upvotes: 0

Java Man
Java Man

Reputation: 1860

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"});

Upvotes: 0

mvp
mvp

Reputation: 116068

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.

Upvotes: 0

Noman ali abbasi
Noman ali abbasi

Reputation: 539

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;
    }
}

Upvotes: 2

Related Questions