?
Solved

enhance my java application with by forming a query

Posted on 2012-08-22
8
Medium Priority
?
560 Views
Last Modified: 2012-08-31
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
0
Comment
Question by:gudii9
  • 5
  • 2
8 Comments
 
LVL 22

Accepted Solution

by:
Helena Marková earned 2000 total points
ID: 38323547
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
 
LVL 21

Expert Comment

by:Amitkumar P
ID: 38323648
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
 
LVL 7

Author Comment

by:gudii9
ID: 38325555
sorry my excel looks like this
Reportt.xls
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Comment

by:gudii9
ID: 38331005
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
 
LVL 7

Author Comment

by:gudii9
ID: 38331027
what is FIELD2 here. Please advise
0
 
LVL 7

Author Comment

by:gudii9
ID: 38341735
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
 
LVL 7

Author Comment

by:gudii9
ID: 38350906
>>>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
 
LVL 22

Expert Comment

by:Helena Marková
ID: 38353089
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses
Course of the Month15 days, 16 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question