In this lesson, we're going to look at the statement object a little bit more and also ResultSets. We'll consume ResultSets using queries. We'll also utilize the statement to explore the idea of referential integrity, how schemas are related to each other, either tables have foreign key, primary key relationships, and then finally, we'll use an alternative to ResultSet, and we'll specialize ResultSet called a JDBC RowSet, which will provide us an alternative means for defining connections to a database and executing queries. ResultSet, it processes the query by executing some SQL. It returns a series of rows with columns representing the return data. When we bring back data in the form of ResultSet, we have to find out if there is any in data. We use the next method which returns a Boolean. If it finds data dispositions as before the first row of the ResultSet, and then we continue to iterate over the ResultSet, constantly checking is there a next row. As we saw previously when we consume our ResultSet there are getter method to provide Data Conversions, getInt() for example, these getter methods have two variants. You can take the column index, one based, or it can accept the name of the column in the row of the ResultSet that you're consuming as the parameter. ResultSet is automatically closed when the statement object that generate take it is also closed or if it's re-executed or if it used for some other purpose like executing some other SQL. Although the ResultSet provides a wonderful getter methods, you can be old SQL and execute getObject but this will retry casting either by the overloaded getObject method, having a second argument, the object you want to cast to or really old school, just cast it. As you can see here with lastName. Now a default ResultSet has a cursor that moves forward. It's also got a concurrency read-only property, which means we can only read things with the ResultSet. We can change this as we'll see in a second, to be concur updatable. That means we can perform some update operations. To be a scrollable ResultSet and be able to scroll forwards and backwards, when we create the statement, we need to identify whether the ResultSet is sensitive or insensitive to the underlying objects, that it is going to be iterating over when we bring back that data. This means because it's now scrollable, we can utilize the API of the ResultSet class, such as calling an absolute method to position us maybe on the third row or the fourth row. If it is a type scroll insensitive, we're not aware of changes to the underlying objects, we need a type scroll sensitive, and we need to concur updatable as well. We might have something like this. Position ourselves after the last row and then go backwards previous. The row we are now on we'd like to update the column firstName to Jason. We then want to use the ResultSet to actually update that row, which will update the underlying database, so to speak. Indeed we can execute delete row as well. We will actually delete the row that we are positioned on. Remember, in order to make these updates and have them be reflected in the ResultSet, you need a ResultSet type scroll sensitive, and you need a concurrency model of concur updatable.. These are the methods we might use on a ResultSet. Go to an absolute row, position ourselves after the last row, go to the beginning of the ResultSet before the first row. Go to the first row, go to the last row. Check if you're moving forward, check to see what the next row is, if it exists or not, or if we're going backwards. Use the previous method to move us backwards through the ResultSet. Now, we can actually change our SQL to take parameters to limit the data we're bringing back in a traditional query. It's not very satisfactory with statements, when you come to prepared statements, you'll come across a far more eloquent solution to this. But here I've just used a string format approach with placeholders for percent as simpler percent d to pass in the arguments tester and one. Here I'm updating employee set the lastName to tester where the ID is one, then I'm doing an execute update. The execute update isn't returning a result set. It returns an int identifying the number of rows affected. We will do the same thing with the query. Just use a formatted string to inject in data at the appropriate placeholder and execute the query. As I mentioned before, prepared statements in another course will give us far more options than this, but that's where we are at the moment.
