Creating A Database Table In Java

Lister

I'm curently creating a program that, as part of its funtion, has to read in the data stored in a large database (64,000 rows x 15 columns approx).

I have no problems reading this data into a ResultSet.
What I want to do is read in say a quarter of the database and use that ResultSet to create a new table in the same database.

Is there anybody who can tell me if this is possible and if so how to go about doing it?

Thanks.

Karoma

snippet:

  //needless to say: create ResultSet resultSet, connection,etc...



   Vector cols = new Vector();
   Vector rows = new Vector();

   try
   {
	   rsmd = rs.getMetaData(); //result meta-data

                   //get all cols:
	   for (int i = 1; i <= rsmd.getColumnCount(); ++i)
	   cols.addElement( rsmd.getColumnName(i));


	   // get all row data **** add constraint to get only what you want
	   do
	   {
	      rows.addElement( getNextRow( rs, rsmd ) );
	   }while ( rs.next() );
   }

simply add a restraint to stop wherever you want...

parse RS, adding each result ( through SQL: .createStatement(); & .executeQuery( SQL ); )


may i ask why you're doing this (this way). really you should fix up (normalise/'split up') the database...

Related URL:
API for ResultSet , check for .ength() or similar to make life easier.
I'll look/explain better later (when the room stops swaying)

Lister

What I'm attempting to do is to create a program that counts votes, using the Irish Electorial counting rules.
I have a database with all the votes cast from the electronic voting trial that was held in Meath a while back.
What I was origionaly attempting to do was to read in the votes (rows) form the database, one by one, and add them to a Vector of Vectors.
This produced an OutOf MemoryException. I assume because the combined size of the ResultSet and all those Vectors was to much.
Unfortuntely inceaseing the Heap size had no effect.
So what I hoped to do was to read in all the votes for each candidate in order of preference and then save each ResultSet back to a new table (one table for each candidate) in the database, which I could then use only when needed.
If this was to work I would be splitting up the database.
Unfortunately I needed to do this through the code, as in the real system (that is being introduced in the summer), the database would remain intouched for security reasons.
Doing it this way was my lecturers idea, so I said that I would give it a go!

What I was thiking of doing was to read in the candidate data in order of preference (about 9,000 max rows per candidate), and then save that to a Vector of Vectors.
So I would end up with approx 14 to 17 Vector of Vectors, but I havent tried this yet and it may produce the same OutOf MemoryException that I got before.

Hope all that makes sense!

124124

I am not sure if I understand the requirement correctly, however, I am curious why you have to keep all the vote information in the memory, once you are done (counting) it?

Also, if you wouldn't have permissions to modify the source database (for security reasons, as you said) where are you going to create this new tables into?

Well, if you have 'create' permission in the actual database, I would suggest using couple of views (filter for candidate).

Now, I am not sure if this would help, but just to let you know that vectors are not the most memory efficient type in Java - may be an ArrayList be more efficient, what ever the size of the data you are hanlding.

Just some thoughts.

Lister

Changing to arrays is something that I'm considering, just havent got round to doing it yet. I decided to use Vectors for convenience more than anything.

The voting information has to be split into seperate tables, as once you have calculated all the first preference votes you have to transfer elected and eliminated candidate supluses and then count second and third prefernces and so on.
Its a very complicated counting algorithm.

I do have create permission in the database. But in the real system, I would not, as the databases used would have to be completely tamper proof.

I'm trying to find a middle ground. So what I hoped to do was to import the voting data of each candidate to a ResultSet and then use this data to create a new table in the database, one table for each candidate and then one to store the overall results of the count.

So basically what I'm wondering is if you can use the data in a ResultSet to create a new table in a database.

I'm not sure if this is possible, but I think that its worth finding out.

Karoma

1. yeah,it should be possible.
2. arrays != arrayList .. (124124 suggested arrayList, then you mentioned array: just making sure you're aware)
3. i don't fully understand the brief: but what about using different ResultSets? one from SELECT candidate1 details .... SELECT candidate2 details... etc... (different angle)

desc the table please.

124124

Again, ignore my comments if its not exactly fitting your requirements, however from a general idea that I am getting now, what you are trying do is to do some very data specific operations (counting, sorting, grouping, re-grouping etc) based on a very huge database.

Assuming that what you are trying to do is mostly data manipulation based on a set of conditions ( first iteration for candidate x, second iteration based on first iteration results, third calculation based on the result of first and second iterations etc), IMHO, trying to load everything in to memory hungry Java collections and then process them, doesn't look like the right thing to do.

Have you looked into generating (based on your conditions and requirements) various SQLs and fire it off against the database? Its just that when you are talking about handling large number of records etc, its better to employ the tool thats designed to do that, which in this case, the database. And your Java classes can provide nice user interfacing, and retrieval of complex search results and reporting it to the user.

Just some thoughts, feel free to ignore some or all of it if it doesn't make sense to your requirment - all I am doing is some handwaving in the general direction since I dont exactly know the kind of things that you want to do with the data.

On the otherhand, its very possible to get table information from the original database (use *Metadata class of java.sql) and to create your own table, and insert rows into it. You shoud be careful with Connection Commit settings and also have some way of reading and inserting a set of records (jdbc 2.0 onwards they support getting x many rows from a resultset), and most imporantly, removing the reference to processed records from the memory so that it will be garbage collected - if implemented properly, this shouldn't give you OUTOFMEMMORY Exceptions.