aspose file tools*
The moose likes JDBC and the fly likes PrepareStatement to build query with parameters based on spreadsheet data Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "PrepareStatement to build query with parameters based on spreadsheet data " Watch "PrepareStatement to build query with parameters based on spreadsheet data " New topic
Author

PrepareStatement to build query with parameters based on spreadsheet data

sai rama krishna
Ranch Hand

Joined: May 29, 2009
Posts: 271
Hi,

I would like to use PrepareStatement to build my query by supplying the parameters based on spreadsheet data on mysql database. How can i achieve it.


I wrote my application like




I am getting console output like

rs1-->com.mysql.jdbc.JDBC4ResultSet@1006d75
rec1 is--jim---rec2 is---yyy
e-->java.lang.NullPointerException
rs1-->com.mysql.jdbc.JDBC4ResultSet@1ccce3c
rec1 is--bill---rec2 is---zzz
e-->java.lang.NullPointerException



since those excel(File.xls) records are already there in login table as well. Now I would like to I would like extend program to use PrepareStatement to build my query by supplying the parameters based on spreadsheet data.

Any ideas, suggestions, sample code, links, source code highly appreciated. Thanks in advance



[Thumbnail for LoginTable.JPG]


[Thumbnail for FileExcel.JPG]

R. Jain
Ranch Hand

Joined: Aug 11, 2012
Posts: 375
    
    1

See PreparedStatement at JdbcFaq
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39436
    
  28
Moving to our databases fora.

You should divide that task into smaller pieces. Look for POI which I have never used, but people say it can access spreadsheets.
sai rama krishna
Ranch Hand

Joined: May 29, 2009
Posts: 271





I modified my program as below.

If there are missing records in excel compared to database it is sending to hashmap and printing to console. Instead I want to generate output excel report and email to user. Please advise on how can i do it.
Winston Gutkowski
Bartender

Joined: Mar 17, 2011
Posts: 8008
    
  22

sai rama krishna wrote:I would like to use PrepareStatement to build my query by supplying the parameters based on spreadsheet data on mysql database. How can i achieve it.

I think the others have covered the semantic part, but I'd question the design. Seems to me that storing a spreadsheet in a database is the height of redundancy; and I suspect it'll cause you problems in the future (if it hasn't already).

Winston


Isn't it funny how there's always time and money enough to do it WRONG?
Articles by Winston can be found here
sai rama krishna
Ranch Hand

Joined: May 29, 2009
Posts: 271
Sorry for not communicating properly. I am not storing excel in database. I am reading username and password informationf from File.xls and then check if the database has those username password information. If any username and password is there in excel but not in the database, then i need to generate missaingRecord excel report. Please advise
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: PrepareStatement to build query with parameters based on spreadsheet data