Follow an exercise in configuring and using the JDBC driver.
- [Instructor] At the heart of JDBC operations is the JDBC driver. And we're going to take a look at how to actually use the JDBC driver in association with the Connection Manager to make a connection to our database, so that we can start doing work within that database itself. So let's jump into our IDE. And we're going to open up our project in src, main, java, and then our package. And we're going to create a new class, and that class is going to be called DatabaseConnectionManager.
Now this is a pattern that I often use with databases to have a central place of handling all of our connection needs within the system. Now, the first thing that we're going to do is we're going to put a private final String called url. And we're going to do a private final Properties called properties. Now we're going to create a constructor for our class and we are going to pass in a few values to our constructor.
The first is the host, and then a database name. And then a username. And then a password. Now, often you would bring these in through some sort of configuration mechanism, specifically something like a properties file, but in this case, we're just going to pass them into our constructor from our main method. I'm going to go ahead and make this a little bit easier to read.
Alright, so now, in this method, we're going to set our url equal to jdbc postgresql://. Then I'm going to add in the host value that comes in. And I'm going to put another slash. And then I'm going to pass in the database name. Now, again, this is not really the best form when you're dealing with production system, but for the purposes of our demo, this at least gets the point across, and that's really the important part.
Now we're going to set some properties here, but to do that, we need to instantiate it. And now we will set our username to the property of user. And we will set our password to the property of password. And that's we all need in our constructor.
Now we're going to create a simple method that returns a connection and this from java.sql. And we'll call this getConnection. Now our connection will throw an SQL exception and we're going to talk about SQL exceptions later on in the course, but for right now, we'll just go ahead and throw it. And what I'm going to do is I'm going to return a DriverManager.getConnection, and I'm going to pass it, this.url and this.properties.
And that's all we need to do to create a connection to our database. So let's jump back over here to our JDBC executor. Okay, so we're going to replace the text here, and create an instance of our DatabaseConnectionManager. Create a new instance of our DatabaseConnectionManager. And we're going to pass in localhost, since we're running our database locally. And we are going to pass in the name, which is hplussport.
And then we will pass in our user, which in this case is postgres. And we'll pass in our password, which is that uber-secure password of password. So now we're going to build a try block. And we're going to create a connection from our DatabaseConnectionManager. And we'll create a simple statement. And we're going to talk a lot more about what we're doing later, but this is just to show you that it's working.
So we'll get a statement from our connection. So now we're going to create an instance of ResultSet, and this is from java.sql. And we're going to do that equal to statement.executeQuery. And the query that we are going to pass it is SELECT COUNT parentheses star FROM CUSTOMER.
And then we're going to iterate over our resultSet. And we will simply do a System.out.println on resultSet.getInt, because it's a count. And it'll be the first value that comes across. And now all we need to do is catch our SQL exception. And if we get one, we will simply do an e.printStackTrace on that.
So now we're ready to run our method. Now, before we do that, we want to make sure that our database is running. So we'll do that by just doing psql dash h of localhost dash uppercase U of postgres. Type in our password of password. And we've connected to our database, which means it is actually running. So now we can jump back to our code, and run our main method.
And as the terminal runs, you'll see that we have 1,000 records on our customer table, which corresponds to the SQL script that we've already imported.
- Deciphering JDBC terminology
- Using containerized PostgreSQL
- Creating, reading, updating, and deleting data
- The Repository pattern
- Using stored procedures
- Ordering and limiting results
- Transactions in JDBC
- Handling exceptions