Java JDBC Tutorial – Part 8: JDBC Database MetaData with MySQL

---

---

In this video tutorial we will learn how to retrieve JDBC database metadata with MySQL.

---

Transcript

Time - 00:00

Time - 00:30

Time - 00:48
There are a lot of methods available, you can go online and you can Google JDBC Metadata, there you'll find the actual JavaDoc for this class and you'll see all the methods that are available for it.

Time - 01:00
Let's switch over to Eclipse and see this in action. I have a very simple program called Metadata Basic Info. Let's walk through the code. The first thing we'll do in this application is we will get a connection to the database. Then using this database connection I can get the Metadata, it returns a special Database Metadata object. Now with this object I can retrieve information about the database, I can get the actual database product name, I could also get the product version and then from there I can move forward and get information about the JDBC driver. I can get the driver's name and also the driver version.

Time - 01:49
Let's go ahead and run this application. Once we run it, we see the output. It shows us the product name and the actual version, the actual JDBC driver name, MySQL Connector and also the JDBC driver version that we're using. This is very good information that we retrieved by accessing the Database Metadata.

Time - 02:13
We can also get information about the database schema. For example, we can get a list of all tables and also get a list of column names for each table along with our types.

Time - 02:29
All right so let's switch over to Eclipse and is this in action. I have a very simple program called Schema Info and let's walk through the code. In this main method I have some variables set up for catalog and all the various patterns. When you make calls to some of these Database Metadata methods, you can actually pass in patterns but for right now just keep it null just to keep it simple. Let's step through the code, the first thing we do is we get a connection to a database and we get the Metadata, we've seen all this before, then I like to get a list of tables so I say, "Database Metadata get tables" and I pass in those various patterns.

Time - 03:05
This will give me a list of all of the tables for this demo schema, it's going to return it as a ResultSet. This works like a normal ResultSet, I can just loop through this ResultSet and I can retrieve each table name and print it out. This will give me a list of all tables for this given schema.

Time - 03:24
I'd like to do a similar thing for columns. I'd like to get a list of all the columns for a given table. In this example, I want to get all of the columns for the employees table so I pass in employees here and I pass in any other patterns and so on and they're all set to null but here I want to get the employees table. It's going to return a list of columns for this employees table as a ResultSet and just like before I can just walk through this ResultSet and I can print out each column name. They also have methods for getting column types and so on but this is enough to get us started for now.

Time - 04:02
Now we can see the output of the application. The first thing at the top we get a list of tables and so in this example we only have one table and that's employees. Then we can also get a list of columns for this employees table and we have these six columns here: ID, last name, first name, email, department and salary.

[snip] See the "Transcript" tabs for details.

[njunior]

You're awesome man, thanks for the videos...
I am learning so much from you!

[violetanderson]

Thanks you very much for this tutorial. It is very useful to me.

[MASINALI]

very useful features.
In MySQL Workbench we should use a little bit other code to know  programmatically names and numbers of tables and columns, but here in JDBC all looks so simple.

[micharuszkowski]

It actually doesn't works with connector version above 6.0. Solution is adding to the connection parameters.

[clopes]

SchemaInfo.java returns an extremely long list under the List of Tables, such as columns_priv, component, general_log, global_grants, data_locks, etc...