aspose file tools*
The moose likes JDBC and the fly likes SQL Database Structure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Database Structure" Watch "SQL Database Structure" New topic
Author

SQL Database Structure

Joe Lemmer
Ranch Hand

Joined: Oct 24, 2008
Posts: 171
Hi there,

I hope it's OK to ask a purely SQL question here (I am using Java in the application as a whole). I want to create lots of lists of strings in various different categories and store them in a mySQL database.

I thought the way I'd do it is have just one table, and then have a column for each different category of strings. But each of my categories will have different numbers of strings to store, so maybe thats not such a good idea after all.

Do you think I should have a different table for each category of strings?

Is there an easy SQL command whereby I could add a string to the bottom of one single column of a table, without putting anything in the others?

Thanks

Joe


OCPJP 85%
Jimmy Clark
Ranch Hand

Joined: Apr 16, 2008
Posts: 2187
I want to create lots of lists of strings in various different categories and store them in a mySQL database.
...
Do you think I should have a different table for each category of strings?

Sure.

Is there an easy SQL command whereby I could add a string to the bottom of one single column of a table, without putting anything in the others?


Yes, in general. An INSERT or UPDATE command will handle this. There are different dialects of SQL. I am sure that Transact-SQL will do this. Not sure about the others.
Joe Lemmer
Ranch Hand

Joined: Oct 24, 2008
Posts: 171
Hi James,

Thanks very much for your reply!

I'd just like to ask a couple more questions:

I have created a new little table with a couple of columns and used a few insert statements to add data to just one column at a time, as I would if I were just adding a single string to a category (column) in my table via my GUI.

I added two strings (using two separate insert statements) to the first column, then I added a string (using a third insert statement) to the second. When I checked to see how the table was arranged using the 'SELECT * FROM my_table;' command it showed three rows with a string in one column and NULL in the other. What I was hoping for was that was that I wouldn't have any NULL's, and that I'd have just two rows because I wanted the last string to be inserted not on a new row, but at the top of it's column. Boo Hoo!

Is there a way to insert single pieces of data as I want to with mySQL?

If not, then I will just create a talbe for each category. No problem. Except... as part of my application, I want to get the names of the tables returned to a method in one of my classes. So I've had a trawl of the java.SQL class and think I need to get a DatabaseMetaData object and then query that to get the table names. But the parameters for the getTables methods look like it wants info that I don't know how to give. Catalog? schemaPattern? tableNamePattern? types[]?

I'd be really grateful if you let me know of another method or way I could get the table names of my database returned.

I can't believe you've read all that waffle!

Many thanks

Joe
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30537
    
150

Joe,
So you have


and wanted


Is this correct? It's certainly possible to do. You would want an update statement rather than an insert for the third time because you do not want a new row. Insert = new row, update = change existing rows


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Joe Lemmer
Ranch Hand

Joined: Oct 24, 2008
Posts: 171
Hi Jeanne

Thanks for your reply:-)

The table structure I had was:



and I wanted to be able to choose my column and for it to be automatically added at the end of my chosen column.

as in from:



to:



Would I have to do something like make a query that:
1. Finds the first null row in the column that I want to add data to.
2. Returns this row number to another piece of code
3. Makes an update to insert the data at that first null row.

?

This might be a bit complicated for me, because I would have to test all the other rows to check their length, so that I would know whether I could use update to add my data, or whether I should use insert because another row was needed.

My SQL is not very good, so I'm not sure if this is correct and would be grateful for some guidance.

Also, I'd really love to know how to get the table names of a DatabaseMetaData object. I don't know what catalogs and schemaPatterns or tableNamePatterns are though (the parameters of getTables method in the DatabaseMetaData class). This would mean that I have the option of just using different tables for each category of data.

Thanks again

Joe
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30537
    
150

"Finds the first null row in the column that I want to add data to."
How do you define "first". The first one alphabetically? The first one in the order the records happen to be stored in the database?

Your description shows you recognize you need two queries (insert vs update) for different cases. Since you are using JDBC, you can use Java to help you.
1) Do query that determines key of row to update (or no records if an insert is needed)
2) Do insert or update query as appropriate
Jimmy Clark
Ranch Hand

Joined: Apr 16, 2008
Posts: 2187
I'd be really grateful if you let me know of another method or way I could get the table names of my database returned.


Store a list of the table names in a table and give each table name an unique id.

DATABASE.META(ID, TABLE_NAME)
[ November 30, 2008: Message edited by: Jamees Clark ]
Joe Lemmer
Ranch Hand

Joined: Oct 24, 2008
Posts: 171
Thanks James. Thanks Jeanne. ;-)
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL Database Structure