In this video we will learn how to use JDBC database metadata.
Please SUBSCRIBE to this channel
Time – 00:00
Hi, this is Chad (Shod) with luv2code.com. Welcome back to another tutorial on Java JDBC. In this video we will learn how to use Database Metadata. For this tutorial we’ll use the following table: employees. The table also has sample data for testing. I have a SQL script that’ll create the table and add sample data for you. You can download it from the link below.
Time – 00:30
Database Metadata can give you information about your database. To get started with Database Metadata, you must first retrieve it from the connection object. This is accomplished by calling myCon.getMetadata, then we can call some methods on the object such as get product name and version, get JDBC driver information and so on.
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.
Time – 04:26
One thing that I’ll do here is I like to switch over to MySQL tool and just verify some of the information we saw from the previous program. I’ll expand this item here for demo, that’s the one database schema that we’re connected to. I’ll also expand tables, we’ll see that there’s only one table and it’s called employees that checks out okay. Then I’ll open up the employees table and then I’ll take a look at the columns and I see here that we have six columns, same information that we’ve had from our Java program so we’re in good shape.
Time – 05:00
That wraps up our discussion on Database Metadata with JDBC. We were successful in retrieving information about the database connection and also database schema. Please subscribe to our channel to view more videos on Java, click the thumbs up to like our video also visit our website https://luv2code.com to download the Java source code used in this video.