Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In most database application environments, you already know the database structure, the names of the tables, the number, and types of the columns, and so on, but there are times when you have to discover this information dynamically, and JDBC gives you that ability through a function called metadata. The term metadata in the context of databases describes exactly the sort of information, and you can get the metadata from a database connection quite easily. I'll do an initial demonstration of this functionality in a project named Table List.
I'm using a Singleton Connection Manager which I have described how to build in a previous exercise. I have gotten a reference to my connection, and then within the try catch block, I'm going to add some data to get a list of tables that's in my database. Within the try block, I'll first declare an object instantiated as DatabaseMetaData. As with many other objects in JDBC, you might see more than one version. Always be sure to choose the version from java.sql for maximum portability. I'll name this object metadata.
Then I'll get its reference from the connection object's getMetaData method. You'll see a couple of versions listed, but in fact, there is only one. The getMetaData method takes no arguments. I'm going to interrogate this object to get a list of tables. There are actually many different types of tables in a database. But the only ones I'm interested in are the ones that contain my data. And there's a series of database types that I can select from. To control which database table names are returned, I'm going to create an array of strings, and I'll name it tableTypes, and I'll declare the array as a pair of braces, and I will only have it contain one item.
All database management systems should support this table type, but the number and names of the different table types will then vary from one database management system to the next. Now, I'm going to get the list of tables. It's going to come back as a result set. I'm going to name the result set RS Tables, and to properly manage it, first I will declare the result set outside of the try catch block. I'll declare it with a data type of ResultSet, then rsTables as the object name, and I'll initially set it to null.
Then within the try block, I'll instantiate it with rsTables=metadata.getTables. The getTables method takes four arguments, the catalog which is the name of the database which I have already selected in my connection string, so I don't need to pass it in again. I'll set that to null, the schemaPattern which I'll set as a wildcard of a percent sign, the tableNamePattern which I'll also set with a percent sign wildcard, and the tableTypes which will be my array of strings that I already created.
The result set will come back with a column named table_name. So now, to print out the list of tables, I'll create a while loop, and I will iterate with a condition. The condition will be rsTables.next. So I am going to be moving forward through the ResultSet and working with each row one at a time. Then within the while loop, I'll use system output, and I'll output the value of rsTables.getString, and I'll pass in the name of the column TABLE_NAME. When I'm done, I'll need to close the result set.
As with all result sets, you will typically do this in a finally block. So I'll move the cursor there and call rsTables.close. I'll save my changes and run the code, and there is my list of tables in my backend database. You're able to do a lot more than this with the database metadata, including finding out about columns, their names, and their datatypes. I'll show you how to do some of these other tasks in another video.
Get unlimited access to all courses for just $25/month.Become a member