Executing Basic SQL Queries

1. Executing Basic SQL Queries

Now that we know how to connect to the database, it's time for the next part: actually running SQL queries and seeing our data! In this video, we'll learn how to execute queries using JDBC's Statement object, read the results with ResultSet, and handle any errors that might pop up along the way.

2. Statements in JDBC

So, how do we actually run SQL from Java? That's where the Statement object comes in. Think of a Statement as a messenger between our Java code and the database. We give it an SQL command, and it delivers it to the database for execution. JDBC provides us with two main methods for working with databases. We use executeQuery() when we want to retrieve data with SELECT statements. And we use executeUpdate() when we want to modify data with INSERT, UPDATE, or DELETE.

3. Creating a Statement

Let's see this in action with some code. First, we define our SQL query as a string. Here, we're selecting some columns from the books table. Then, inside a try-with-resources block, we perform three steps: we obtain our connection using DriverManager.getConnection(), we create a Statement from that connection using conn.createStatement(), and finally, we execute our query by calling stmt.executeQuery() with our SQL string. This returns a ResultSet, which holds all the data we requested. But how do we actually read that data?

4. Reading results with ResultSet

The ResultSet works like a cursor moving through a table, one row at a time. We use a while loop with .next() to move through each row. Each call to next() advances the cursor and returns true if there's another row to process. Once we're on a row, we can extract column values using getter methods. We call getInt() for the book_id column, getString() for title, and getInt() again for publication_year. Notice that we need to use the right getter method for each column's data type. We can retrieve values by column name, like we're doing here, or by column index. Using column names is better for readability. Running this code gives us all the books from our table.

5. Handling Exceptions

Of course, things don't always go smoothly. What if the table doesn't exist, or our SQL has a typo? In this example, we try to query a non-existent table. That's when JDBC throws a SQLException. This exception provides us with helpful debugging information: getMessage() returns a descriptive message explaining what went wrong, and getSQLState() displays a standardized five-character string that identifies the type of error. It's good practice to use try-catch blocks to handle these exceptions gracefully. This allows us to log errors for debugging purposes, rather than crashing our application.

6. Recap

Let's do a quick recap! We use a Statement to send SQL commands to the database. The results come back in a ResultSet, which we loop through row by row using next(). We extract values with getter methods like getInt(), getString(), and getDate(), matching each column's data type. Remember: executeQuery() is for SELECT statements that retrieve data, while executeUpdate() handles INSERT, UPDATE, and DELETE operations that modify data. And always handle potential errors with SQLException!

7. Let's practice!

Time to put this into practice and query some data yourself!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.