Meaningless Drivel is fun!*
The moose likes JDBC and the fly likes upload file and into database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "upload file and into database" Watch "upload file and into database" New topic
Author

upload file and into database

Tom Reese
Greenhorn

Joined: Apr 24, 2011
Posts: 12
Hi JavaRanch,
My question is how would I upload a file, say a text file and insert it into my database. Firstly, I am aware that I need to use the Commons FileUpload from Apache. The only thing I could not find a example of was to use the Commons FileUpload in conjunction with a database. The code shown here... is what i have so far, I just don't understand where I should go on from here. I know that I need to convert the file into a Blob data type but how do I do that? Any help is greatly appreciated!
Pat Farrell
Rancher

Joined: Aug 11, 2007
Posts: 4646
    
    5

Its really simple if you separate the actions logically.

Inside your doGet() method, do two steps:

1) upload the file. If its small, you can just read it into memory. If its too large for memory, write it out to a temporary place.
2) read the contents back and call the appropriate DBMS code to store each row.

This also lets you debug it in steps. First get the upload to work. Then make the process to work going from the contents to the DBMS. Finally, glue them together.
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2339
    
  28

To create the blob, call Connection.createBlob to get a blob object. You can then either set the bytes directly into the blob, or get an output stream that you can stream the bytes to.
Pat Farrell
Rancher

Joined: Aug 11, 2007
Posts: 4646
    
    5

Also, all your fooling around with Calendar, Date and Timestamp creation is unnecessary and probably won't work the way you really want. If you are happy using the JVM's default locale and timezone, you can simply create a Date and then use its getTime() as the argument to the Timestamp constructor. It would, of course, be cleaner if Java let you simply call an empty constructor on the Timestamp and have it do the same thing, but Date is a four letter word in Java.

If you think you really need a Calendar, you should really pass in a TimeZone so that the Calendar's date has real meaning. I run all my servers on UTC and then apply the user's local timezone and locale for formatting output.
Tom Reese
Greenhorn

Joined: Apr 24, 2011
Posts: 12
Thank you for the suggestions, I think I am on the right track. I will debug and get a sense of whats happening. I will post my solution once i get it.
Tom Reese
Greenhorn

Joined: Apr 24, 2011
Posts: 12
I have done some head scratching and have ran into some issues. I can't use the method Connection.createBlob() because the Postgresql jdbc4 drivers do not support this, that is literally the error I am receiving. Here is my code currently...I am up for any other suggestions on how I can upload a file into my database. What other methods are out there? Thanks for any assistance you can provide.
Pat Farrell
Rancher

Joined: Aug 11, 2007
Posts: 4646
    
    5

Tom Reese wrote:I can't use the method Connection.createBlob() because the Postgresql jdbc4 drivers do not support this


You need to read the JDBC driver's documentation and see how it describes supporting Blob fields. Perhaps there is another approach that is documented.

Folks here who work with Posttresql will know, but you will learn more if you dive into it yourself. Or perhaps you can google for postgres and blob.
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2339
    
  28

I'm moving this thread to the JDBC forum, because the question primarily is how to save a BLOB in PostgreSQL
Tom Reese
Greenhorn

Joined: Apr 24, 2011
Posts: 12
Ok after some very emotionally trying times I have arrived at a good place in my current code. I figured out the fix for getting around the Connection.createBlob() function. I found the solution here, http://www.xinotes.org/notes/note/943/ I also had to fix some other things revolving around getting the field values along with the file. The problem I am having now is simple, I am not reading in the file bytes correctly and I could some suggestions. I may be doing something really bogus because I have not messed with bytes a whole lot. To test if the bytes are being read I export the test file (small image) to a place on my disk. Currenty the file I export is empty. Sorry for the lengthy code...
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2339
    
  28

Tom,

In all of your code I don't see where you are saving the blob. Also, the code reads like a big wall of code.. The official term that I would use to describe here is holy moley.. Yes that's an official term.

You might find it easier to debug and understand your own code if you modularize it a bit. Also it will help you create a SSCCE which will help you test your problem isolation and also help us help you better

And please don't think of this as piling on, but I don't like the way you comment your code. You have lot of comments which is good, but most of the comments just describe what the next line of code is doing. Comments are supposed to explain what the code is doing from a functionality point of view. I'd don't need the comment to tell me that the result set is moving to the last row; I know what rs.last does, or can look it up. I need to know why you are moving to the last row
Tom Reese
Greenhorn

Joined: Apr 24, 2011
Posts: 12
Well for a moment there I thought I would have to sacrifice a goat to get this working but I finally found a guide that told me exactly what I needed, the guide found here http://jdbc.postgresql.org/documentation/80/binary-data.html uses deprecated methods but simply find the most recent ones which simply use a long type variable instead of a int. Sometimes you just need the right documentation and examples in which my case I did. I understand how file uploading works and it doesn't seem so complicated. Thanks for the suggestions to modularize the code, it made it more readable and easier to understand. Here is my working code... and thanks to everyone who replied, sometimes you just need a little criticism and suggestions. Thread solved, case closed! Code is still lengthy but is much more readable I think. This will be going up on my website!

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

There is still at least one problem - the getLastRowResultSet method. Rows in most (for all practical purposes "all") relational databases do not have any intrinsic order. In simple cases it might appear that SELECT * returns rows in the order in which they were created, but in real life conditions that is almost never true (see our FAQ page regarding the database ordering). Another problem is that ResultSet.last() is expensive operation, it fetches all rows from the query, which gets worse as the table grows. Again, this is easy to miss when testing with a low volume of data.

You need to find another way of locating the row which was just inserted. I don't know PostgreSQL, so I cannot really advice what works best there. One of possible approaches should be to generate the primary key value in a separate call (usually using a sequence) and use it then to insert the new row, which can then be obtained by its primary key. By the way, it looks like your table doesn't have a primary key, so you should perhaps add one.
Tom Reese
Greenhorn

Joined: Apr 24, 2011
Posts: 12
Yeah I agree, my table has a sequence as a primary key, PostgreSQL increments it automatically for me. I will try to figure out something else instead of using rs.last(). Thanks for your input!
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

You'll notice in the API documentation that Connection has a method "prepareStatement(String sql, int autoGeneratedKeys)" which gives you a PreparedStatement with the ability to retrieve auto-generated keys. You may find that PostgreSQL doesn't support this feature properly, but if it does then that would be the way to go.

Although Martin says your table doesn't have a primary key? In that case, like he says, you should add one and let the database auto-generate its value when you insert a row.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Paul Clapham wrote:Although Martin says your table doesn't have a primary key? In that case, like he says, you should add one and let the database auto-generate its value when you insert a row.

Tom says the table has a primary key. I just wasn't able to identify it in the code.
vipin raj
Greenhorn

Joined: Jun 16, 2013
Posts: 1

Nice. Is there anyway I can upload files using Ajax?


http://technobytz.com
 
Don't get me started about those stupid light bulbs.
 
subject: upload file and into database