enhance my java application with by forming a query

Hi,

I am reading Excel spread sheet as follows. I am getting the values back from Excel on to my console. Now I need to enhance my java application with by forming a query to check whether Oracle database table has those valued records or not and print result to console.


import java.io.File;
import java.io.IOException;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ReadExcel {

      private String inputFile;

      public void setInputFile(String inputFile) {
            this.inputFile = inputFile;
      }

      public void read() throws IOException  {
            File inputWorkbook = new File(inputFile);
            Workbook w;
            try {
                  w = Workbook.getWorkbook(inputWorkbook);
                  // Get the first sheet
                  Sheet sheet = w.getSheet(0);
                  // Loop over first 10 column and lines
                  System.out.println(sheet.getRows());
                for (int i = 0; i < sheet.getRows(); i++) {
          for (int j = 0; j < sheet.getColumns(); j++) {
            Cell cell = sheet.getCell(i, j);
            String x = cell.getContents();
            System.out.println(x);
          }
      }
                 
                 
                 
                 
     

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

      public static void main(String[] args) throws IOException {
            ReadExcel test = new ReadExcel();
            test.setInputFile("c:/gp/Report.xls");
            test.read();
      }

}


I got output like

1234.pdf  |  1234


 5678.pdf  |  5678

3333.pdf  |  3333



I found couple of links to check the oracle table

http://www.roseindia.net/answers/viewqa/JDBC/18935-JDBC-Oracle-program.html
http://www.roseindia.net/answers/viewqa/Java-Beginners/8962-Check-Whether-Record-is-already-Exists-or-Not-with-Database-Connectivity.html


I was not sure how to enhance my java application with incorporated query to check whether Oracle database table has those valued records or not and print result to console.

My dynamically generated queries should looks like

select * from XYX_Table where col1='1234.pdf' and col2='1234';
select * from XYX_Table where col1='5678.pdf' and col2='5678';
select * from XYX_Table where col1='3333.pdf' and col2='3333';

Any ideas, suggestions, sample code, links, source code highly appreciated. Thanks in advance
LVL 7
gudii9Asked:
Who is Participating?
 
Helena MarkováConnect With a Mentor programmer-analystCommented:
You can use this query
select count(*) from dual where exists (select null from XYX_Table where col1='1234.pdf' and col2='1234');
It returns 1 when such records exists or 0 if not.

I am not a java expert so you ought to modify the above query something like this:
select count(*) into <variable_1> from dual where exists (select null from XYX_Table where col1=<variable_2> and col2=<variable_3>);

I hope this will be a little help for you.
0
 
Amitkumar PSr. ConsultantCommented:
Please try below code... You will get the String generated for the query and stored in the list. Also, correct minor errors as I dont have jxl library with me, so not able to give to exact code.


import java.io.File;
import java.io.IOException;
import java.util.List;
import java.util.ArrayList;
import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ReadExcel {

	private String inputFile;

    public void setInputFile(String inputFile) {
		this.inputFile = inputFile;
	}

	public List read() throws IOException  {
		List list = new ArrayList();
		String query = "select * from XYX_Table where col1='{FIELD1}' and col2='{FIELD2}'";
		File inputWorkbook = new File(inputFile);
		Workbook w;
		try {
			w = Workbook.getWorkbook(inputWorkbook);
			// Get the first sheet
			Sheet sheet = w.getSheet(0);
			// Loop over first 10 column and lines
			System.out.println(sheet.getRows());
			for (int i = 0; i < sheet.getRows(); i++) {
				String newQuery = new String(query);
				
				for (int j = 0; j < sheet.getColumns(); j++) {
					Cell cell = sheet.getCell(i, j);
					String x = cell.getContents();
					newQuery = newQuery.replaceAll("{FIELD"+(j+1)+"}", x);
					System.out.println(x);
				}
				list.add(newQuery);
			}
		} catch (BiffException e) {
			e.printStackTrace();
		}
		return list;
	}

	public static void main(String[] args) throws IOException {
		ReadExcel test = new ReadExcel();
		test.setInputFile("c:/gp/Report.xls");
		List list = test.read();
		System.out.println(list);
	}
}

Open in new window

0
 
gudii9Author Commented:
sorry my excel looks like this
Reportt.xls
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
gudii9Author Commented:
I am getting following error

Exception in thread "main" java.util.regex.PatternSyntaxException: Illegal repetition
{FIELD2}
      at java.util.regex.Pattern.error(Pattern.java:1713)
      at java.util.regex.Pattern.closure(Pattern.java:2775)
      at java.util.regex.Pattern.sequence(Pattern.java:1889)
      at java.util.regex.Pattern.expr(Pattern.java:1752)
      at java.util.regex.Pattern.compile(Pattern.java:1460)
      at java.util.regex.Pattern.<init>(Pattern.java:1133)
      at java.util.regex.Pattern.compile(Pattern.java:823)
      at java.lang.String.replaceAll(String.java:2189)
      at com.phh.CopyOfReadExcelEnhanceDBConnPropFileFinal.read(CopyOfReadExcelEnhanceDBConnPropFileFinal.java:73)
      at com.phh.CopyOfReadExcelEnhanceDBConnPropFileFinal.main(CopyOfReadExcelEnhanceDBConnPropFileFinal.java:165)


I have not understood purpose of

String newQuery = new String(query);
also
why are we returning the
list.add(newQuery);

I need the query results on the console not the query. Please advise
0
 
gudii9Author Commented:
what is FIELD2 here. Please advise
0
 
gudii9Author Commented:
I am seeing error like

Exception in thread "main" java.util.regex.PatternSyntaxException: Illegal repetition
{FIELD1}
      at java.util.regex.Pattern.error(Pattern.java:1713)
      at java.util.regex.Pattern.closure(Pattern.java:2775)
      at java.util.regex.Pattern.sequence(Pattern.java:1889)
      at java.util.regex.Pattern.expr(Pattern.java:1752)
      at java.util.regex.Pattern.compile(Pattern.java:1460)
      at java.util.regex.Pattern.<init>(Pattern.java:1133)
      at java.util.regex.Pattern.compile(Pattern.java:823)
      at java.lang.String.replaceAll(String.java:2189)
      at com.phh.ReturnQueryList.read(ReturnQueryList.java:64)
      at com.phh.ReturnQueryList.main(ReturnQueryList.java:208)


Please advise
0
 
gudii9Author Commented:
>>>You can use this query
select count(*) from dual where exists (select null from XYX_Table where col1='1234.pdf' and col2='1234');
It returns 1 when such records exists or 0 if not.



I am getting 1 either case. Please advise
0
 
Helena Markováprogrammer-analystCommented:
The select
select count(*) from dual where exists (select null from XYX_Table where col1='1234.pdf' and col2='1234');

returns 1 if there is 1 record or more the same records. It does not return the number of records.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.