Android Data Storage with SQLite

[MUSIC] 0:00 Hi. 0:04 My name is Ben Jakuben, and I'm the Android teacher here at Treehouse. 0:05 Thanks for joining us today. 0:08 Before we get started I just want to say, if you 0:09 have any questions during the presentation please type them below the video. 0:11 And we'll try and get to them at the, 0:16 at the end or if it's especially important then 0:17 my friend Chris will flag me down and we'll, 0:20 we'll try and answer it while we're doing the presentation. 0:22 If we don't get to your question, or if 0:24 you're watching this later and you think of something, 0:25 then post it in our Treehouse Forum and somebody 0:27 will try and answer it for you, myself included. 0:31 Or you can find me on Twitter @benjacobin. 0:33 All right, so we're going to cover the basics of SQLite for Android. 0:35 And the first question we wanna ask ourselves is what exactly is SQLite? 0:41 It is a light waist, lightweight SQL-based database for storing data in an app. 0:45 It's available in all versions of Android and a good example to think 0:49 of is the contacts app, where you 0:52 have information about all the different contacts. 0:54 If you think of the database that sits behind 0:57 it you've got people's names and phone numbers, pictures 0:59 maybe, and that's all stored in a way that 1:02 you can access it from that app at any time. 1:05 If it were just well, let me go ahead to the next slide and explain. 1:07 Why exactly do we wanna use this? 1:12 It's so that we can store and retrieve data within the 1:14 app across sessions, that's the big thing, this is about data persistence. 1:16 When we have data in an app, if we're running it you know, in an activity 1:19 and if the app is visible on the screen, then that data in memory is available. 1:23 But, as soon as we close the app, that data in memory goes away, so 1:26 we need to store that data in memory on the phone or on the tablet somehow. 1:30 So that's why solutions like SQLite databases 1:34 are used to persist that data across sessions. 1:37 The reason we're looking at SQLite 1:41 specifically is, very popular with Android developers. 1:43 A lot of software developers are familiar with SQL databases 1:45 and using the SQL language to access and store data. 1:49 You might be wondering if it's secure, well, it is in 1:53 that the databases exist only within the context of the app. 1:57 Android has this concept of sandboxing apps, where apps and all 2:01 of their related files, databases, et cetera are all in a restricted 2:05 section of the file system and no other apps or anything 2:10 else can access that part of the file system without explicit permission. 2:14 So if you wanna give access to this data to outside apps you have to 2:17 explicitly allow it using a content provider which 2:21 we're gonna talk about briefly in a moment. 2:25 The other thing is when you uninstall an app, it does usually delete 2:27 the data as far as I know from, I forget which version of Android, 2:31 there may have been some inconsistencies earlier on, but when you delete an 2:35 app it, corresponding database, and all the 2:40 files in that sandbox should be deleted. 2:42 We're gonna keep things pretty basic today. 2:46 We are going to talk about the C.R.U.D. 2:47 operation, so if you are familiar with web development 2:51 or other, you know, working with data, then these 2:54 are the basic operations you can perform on data. 2:57 The acronym C.R.U.D. stands for create, retrieve, update, and delete. 2:59 Now in the syntax of SQL that is instead insert, select, update and delete. 3:04 And I should mention about SQL specifically, SQL 3:13 stands for, SQL stands for a Structured Query Language. 3:16 Now if you're not familiar with SQL then we 3:19 have a fantastic course by Andrew Chalkley called database basics, 3:22 and it will get you comfortable with creating databases, manipulating 3:25 the data, and it's all based on the SQL language. 3:29 We're gonna work with very basic data we're going to get some forecast data 3:32 from the web and we're going to store it in a table, and all 3:38 we care about right now, just to show how to use these concepts in 3:41 Android, we're gonna do one column of 3:45 data, which is numbers that correspond to temperatures. 3:47 We are also going to talk about upgrading databases. 3:51 So, that is when you're, let's say in our app we are tracking information about 3:54 a user and we have a user name, and an email address, and a picture. 3:59 Well, we released a new version of our 4:02 app and now we wanna track additional data, too. 4:04 Let's say we wanna store their favorite video game for some reason. 4:06 Well, we need to amend the data that's currently in our system. 4:10 And there's different ways we can do that, we 4:14 can, because we have an existing database for the 4:15 people who have the previous version of the app, 4:17 we can either drop the database and re-create it. 4:19 Migrate the data over somehow or we can alter tables in place and add new columns 4:22 like this where the existing data stays the 4:28 same and then we populate the new columns. 4:29 So, we'll take a look at that later on after we cover those C.R.U.D. operations. 4:31 Now, the last thing I mention before we get 4:36 started with actual code is the idea of content providers. 4:39 A lot of times you'll hear content providers 4:44 talked about in the same conversation as SQLite 4:46 databases, and a content provider is an Android 4:48 construct used to manage structured sets of data. 4:52 And a database is a structured set of data, 4:55 they're primarily intended to be used by other applications. 4:58 Which is why I said you have to explicitly provide 5:00 access to your data to other applications through a content provider. 5:04 But you don't need a provider to use an SQL database 5:08 if the use is entirely within the context of your own app. 5:12 So, that's what we're going to do today. 5:15 We're just building a very simple app that's going to have its own data. 5:16 We're not going to provide it to anywhere else. 5:19 That's a topic for another workshop or another Treehouse course. 5:20 But I do wanna just mention before we 5:24 get started one more thing about the content provider. 5:27 There's a, a good discussion that one of our mobile developers Joe 5:30 Steel mentioned to me about the benefits of using a content provider. 5:33 It is a best practice and this is a Google Plus conversation that was 5:37 happening with some Android developers about the 5:41 pros and cons of using a content provider. 5:44 I'm gonna provide all these links for the 5:46 workshop when this is published in a few days. 5:48 So anything that we cover on the web come back when this is published in a 5:51 few days and you'll be able to find these links or you can search for this here. 5:55 Okay. 5:59 So, what exactly are we building? 5:59 We are going to build a simple weather 6:02 forecast app using an, an API provided by forecast.io. 6:05 Now if you're not familiar with forecast.io, it's a web-based weather 6:09 app that is available also as a web-based app on your phones. 6:14 So, it's a, it's a really great example of the kind of web app 6:18 you can build on a phone using web technology that's not a native app. 6:23 It's completely built from the website, but they do some really neat things with 6:26 animations and making it feel like a 6:31 native app despite it all being web technology. 6:32 And the reason we're using this site is they have a friendly 6:35 developer portal where you can access their API to get some forecast data. 6:39 It's free, you can sign up if you 6:42 just come here to developer.forecast.io and register, you 6:44 create an account and you get an API key that unlocks your access to their API. 6:48 That way they can shut you off if you're abusing 6:52 it [LAUGH], but it's a pretty, I, I don't remember 6:54 the exact numbers but you get a lot of calls, 6:56 so, it's really useful to build some simple weather apps. 6:58 And they, they give you a lot of information, so I've 7:02 switched here to the output from the API the forecast call. 7:04 So, this is a, a JSON output and I'm 7:09 formatting my JSON with a chrome extension called JSONView. 7:12 You can see the plain source looks like this, but, if we close this and go 7:15 back we get information about our current location, 7:19 and then we get different types of forecasts. 7:21 The current conditions, minutes, hourly, daily forecasts. 7:23 We're going to look with the hourly forecast because it 7:27 gives us a set of 48 data points to work with. 7:30 And because we wanna show, inserting multiple data points and manipulating 7:34 it this is just a simple data set of 40 data points. 7:37 We know we're getting and we can work with it from there. 7:40 Okay. 7:45 So lets get started with our code. 7:45 I'm building this app in Android Studio. 7:49 And our Treehouse projects currently use Eclipse. 7:52 But everything we do here in Android Studio 7:56 are the code you can just transfer to Eclipse. 7:59 The only thing that's different is the project set up itself. 8:00 So when you create a new project you have to manually add these code 8:03 files in Eclipse, there's no easy way to migrate from Android Studio to Eclipse. 8:06 The reason I'm using Android Studio is 8:11 because the tool itself just gets better and 8:12 better and I want to encourage you 8:14 if you're comfortable to try out Android Studio. 8:16 Take a look I had an different workshop 8:19 a couple months ago about getting started with Android 8:21 Studio, so if you want to check that 8:23 out it's available now as an Android Tools course. 8:24 So you can review getting started with Android 8:28 Studio, and then take a look at this project. 8:31 All the code that we write is also going 8:33 to be available for download, so we'll take a look. 8:35 And the idea is, we're gonna walk through it, write some code, explain what's going 8:38 on, and then you'll have a nice simple example to get started with your own code. 8:41 You can take these examples and, and either copy it and, and manipulate 8:44 it, or maybe even use some of it directly in your own applications. 8:48 And, it's the kind of thing where it's good to know how you do 8:51 it, but then once you get an example, you can just reuse your existing code. 8:54 So let's before we do anything, this code is incomplete but let's 8:58 run it just to get a quick look at the app itself. 9:02 [BLANK_AUDIO] 9:05 Okay and it's about to load here on my Genymotion emulator and 9:07 this is another topic that I covered in that Android tool's course. 9:11 A Genymotion is a third party emulator that works usually works better than the 9:16 default emulator, it's very fast they have 9:20 all sorts of different devices you can try. 9:22 So in general I, I highly recommend 9:24 using that for your development purposes as well. 9:26 It's the next best thing to having an actual device to develop on. 9:28 So you can see here this app just has two activities the first is this front one 9:31 has our C.R.U.D or I.S.U.D operationa, insert, select, update and delete. 9:36 And we're gonna start with just creating the database and inserting some data. 9:42 Makes sense it's a good place to start. 9:47 So back in our project we have, here's the two activities we were working with. 9:49 And the services package here, I've got my, my code organized into 9:54 a couple of sub packages, so that UI packages for our activities. 9:58 Services is for the code related to getting data from the web. 10:02 So I'm not gonna review any of this, it's here. 10:05 You can get the data from the web however you want whatever data you're going after. 10:08 But I'm using a library called Retrofit it's an open source library from Square. 10:12 And I haven't used it much, but what I have used 10:18 I really like, and so I recommend checking that out as well. 10:20 Could be a topic for another live stream, but 10:24 the the code here just as a quick review. 10:26 You give it the base URL, and then you configure 10:28 your, your weather service or whatever service you're running for 10:31 and the, the library makes use of these annotations, and 10:35 it generates a lot of code for you behind the scenes. 10:37 It does a lot of magic but it does it in 10:40 a way that, that makes it very clean and easy to understand. 10:43 So here the method to load the forecast data, we create a new 10:46 rest adapter because our, web service we're going after is a rest API. 10:50 And we call this get forecast and it runs asynchronously. 10:56 So, it goes out, requests the data, it 11:01 uses the key and our current location and then 11:03 it, when it's all done, when the asynchronous 11:07 call finishes, it calls back with this callback method. 11:09 Now, this is that common asynchronous processing with a callback 11:12 that we've seen in all, a few of our Android projects. 11:15 So, when it calls back, we'll go back, so we're calling this from our main activity 11:18 and when the data returns, we pass in this call back down here. 11:23 And it's a retrofit callback and it has 11:28 two different methods that we need to override. 11:32 First is success, which will include a forecast object 11:34 and that's the other object we have over here. 11:37 This is a mapping of the data from 11:39 the JSON file, this is a very simplified version. 11:41 To map the entire data set would be a bit more 11:44 of a complex class structure, but this drills down the reason 11:46 I have it minimal like this is so that we can 11:51 get after that hourly data, the temperatures, specifically, that we want. 11:53 So, on a successful call, let's go back 11:58 to the MainActivity, we get that forecast object. 12:00 And, right now we're just looping through. 12:03 So, if we run this, we are gonna run it just to make sure in the 12:05 log we, I'm, I'm out putting the current 12:08 temperatures just to make sure that we're getting data. 12:10 Let's proceed with actually creating the database. 12:13 So, I've got this other sub-package here called db 12:15 and that's where we're gonna put our database classes. 12:18 So before we write anything let's take a look at the android documentation. 12:20 Now, I'm a little bit in general I really like the android documentation. 12:24 I think they do a great job of explaining how to do things. 12:27 Providing example code of, I use it all the time I use in our courses but the, the 12:31 documentation about SQLite databases is a little bit obtuse 12:36 and it's, it's Well, it is what it is. 12:40 So hopefully this course is enough [LAUGH] to get you started and then 12:43 you can work your way through 12:46 the navigation through the documentation as needed. 12:46 But if you search for saving data, you can find 12:49 this reference of how to save data in SQL databases. 12:52 And we're gonna walk through these steps but the, the first thing 12:56 we need to do is create a database using a SQL helper. 13:00 And we do that by extending a base class called SQLiteOpenHelper. 13:04 So we're gonna create our own custom class that will extend 13:09 that base class, and take care of the create operations for us. 13:13 So if I right-click on db and select New 13:16 > Java Class, we're gonna call this our forecasthelper. 13:18 Okay, and we are going to extend the SQLiteOpenHelper. 13:25 Okay? 13:34 And right away, we get an error that we were missing some required methods. 13:34 SQLiteOpenHelper is an abstract class which means we can't use it directly. 13:37 That's why we need to subclass it like this, and we can 13:41 use Alt+Enter to bring up a quick fix for implementing the methods. 13:43 And if I enter here, we can create the two 13:49 methods that we need, this will be just the stubs. 13:52 Click OK. 13:55 And there you see the first one is called 13:56 onCreate, which is used to, obviously, create the database. 13:57 Second is called onUpgrade which we'll look at a little bit later on 14:00 and that's when we wanna upgrade the database from one version to another. 14:03 Okay, so we still have a warning here about 14:07 a missing constructor, so let's take care of that. 14:09 Now, if we, if we do a quick fix here, if I hit Alt+Enter 14:12 and create the constructor we get a 14:15 little more, little more information than we want. 14:18 This is to create it with passing in a bunch of 14:21 information, but we're gonna just make this information part of the class. 14:23 So our constructor will instead just require 14:26 the context, which is a common, common pattern. 14:28 And then we will add these individual parameters here in the class. 14:32 So the first parameter here is the name of the database. 14:37 We can name this whatever we like, and let's make it a constant here. 14:40 And we're gonna make it a public constant for the 14:43 database name, well, actually no, let's make this one a private. 14:46 Some of these constants we're gonna 14:49 make public, because we'll reference them elsewhere. 14:50 But let's make this a private static final 14:52 String call DB_NAME and we'll call this temperatures. 14:56 And we have to give it the .db extension. 15:01 Okay, so now we can use this DB_NAME here in the construct.DB_NAME. 15:05 Now the next parameter we just are going to 15:11 pass in null, this is what's this parameter, it's a 15:13 I don, I don't remember off the top of my head but we don't need it for this. 15:21 And the version number is just an integer version and this 15:26 is how we know if we're upgrading the database or not. 15:30 So let's make this a private static final int, I'll call a DB_VERSION, 15:32 and I'll set equal to 1, because we're just starting of with 1. 15:37 It's an integer value so its gonna be 1 and above, 15:41 and we need to increment the version when we're upgrading database. 15:43 So its passion, passing DB_VERSION here, and now, our constructor 15:47 is complete, and we can focus on the, onCreate method. 15:51 Let's go back to our documentation. 15:57 All right. 16:00 Okay. 16:01 I apologize. 16:02 So the next thing you wanna do in here is use 16:03 our, database object to run some SQL to create a table. 16:06 So, the next thing we need [LAUGH] is a, the table name that we want. 16:12 This is the constant that we're going to make public. 16:18 So the first final String TABLE, and we'll call this 16:20 table TEMPERATURES, and we'll just set this equal to. 16:26 We'll do these in all caps too as a 16:32 common SQL convention to write in all caps, TEMPERATURES. 16:35 Okay. 16:41 And, let's, you know, let's, let's define our database create statement. 16:41 So what I'm gonna do is use the SQLite database 16:47 that's passed in here as the parameter into the onCreate method. 16:50 It's called db. 16:55 So, if we type db and look at the content assist here then we see 16:56 a, a command for executing SQL and this is where we can write raw SQL. 17:01 Now, in some cases with working with SQLite, we wanna write 17:04 we, in some cases we will write raw SQL like this. 17:08 Where it's a create statement that's just a basic SQL 17:10 statement just like you would see working with any database. 17:12 Other times we're gonna use some methods and some classes to 17:15 encapsulate that SQL and work with it in a different way, 17:18 a more modular way that makes for a cleaner code for 17:21 us, it's a cleaner interface between our app and the underlying database. 17:23 But here we're gonna pass in a string called DB_CREATE. 17:27 And then let's go up here and create that, with the variables we define so. 17:32 Another private static final String named DB_CREATE. 17:39 And right now we're gonna plug in some variables, but let's, let's write 17:46 this as if we were writing it right in some kind of SQL editor. 17:51 So we are going to write, CREATE TABLE, and the name is TEMPERATURES. 17:54 And when we create this table we need to define the 18:00 columns that are included and the data type of those columns. 18:02 So the first column, and they go in parenthesis here. 18:07 The first column we're going to use is a primary key that's just an integer ID. 18:10 And we don't need it for this example like we saw in the, the full 18:15 screen earlier with we're just gonna work with 18:19 temperatures, it's just gonna be one column video. 18:21 However, if we include this ID column that 18:22 I'm about to write, it's common convention for working 18:25 with a SQLite in Android, and we need that 18:28 column to work with certain things in the future. 18:30 So for content providers for example, or a content adapters 18:33 when you're adapting your data for views, they require that ID 18:36 column be in place, so it's a good practice to 18:39 generally create it, even if you're not gonna use it yourself. 18:41 We don't really need to worry about it. 18:43 Once we create it, we're done with it, because we're gonna create it in such 18:44 a way that it auto-increments in the 18:47 database by itself every time we insert data. 18:49 So, the first column the convention is _ID and it is of a type INTEGER. 18:52 And then we're gonna add that additional information 18:58 to make it the primary key, that auto increments. 19:00 Okay. 19:06 The second column is our TEMPERATURE column, and 19:07 a data type for this is just REAL. 19:11 So there's just a few basic datatypes available in SQLite and 19:13 they're REAL, datatype is for numbers that have a decimal value. 19:17 So the, if we take a look at the data that we're getting 19:20 back from our API, we see the temperature is given with a decimal value. 19:23 So we're gonna maintain that accuracy and store it with a decimal in our database. 19:28 Okay so now, let's plug in the actual values, the variables instead of 19:35 having this all hard coded, so here, 19:39 instead of TEMPERATURES, let's plug in TABLE_TEMPERATURES. 19:41 Okay. 19:46 And then we're gonna create column names as well, for these variables. 19:47 So instead of _ID, let's use a variable called COLUMN_ID 19:50 and we're gonna add that in a second and then also down here let's do 19:56 COLUMN_TEMPERATURE and leave that data set. 20:02 Okay. 20:08 So now we just need to define those two variables, I'm gonna copy and paste here. 20:09 First one COLUMN_ID and this is where that, we had that _ID. 20:13 Second one is COLUMN_TEMPERATURES and I'm sorry just TEMPERATURES since this is an 20:21 individual row, an individual value within our 20:27 temperatures table, so we just use temperature. 20:29 So now we have these variables to find our create table statement is ready. 20:32 And we can execute it here within the context of our onCreate method. 20:35 So we're almost ready to create our database 20:40 and make sure that everything is in place. 20:42 But before we do that we want to introduce another class. 20:44 We have, we're going to use two database classes to access our database. 20:47 This database helper is what we use for 20:51 defining the database and creating and upgrading it. 20:55 But then we have a separate class we're gonna create 20:58 called forecast data source that's going to act as a an 21:00 interface on, on top of the, this forecast helper and the 21:04 forecast data source is going to do all of our operations. 21:08 So our code in our app is going to use the call methods from 21:11 forecast data source and in there it will execute on the underlying SQL database. 21:15 So let's right-click on the db package, select New > 21:19 Java Class and like I said, this one's called ForecastDataSource. 21:22 And click OK. 21:30 And this one doesn't have to extend anything, it's just that we are 21:30 going to make use of that data helper class that we just defined. 21:32 Okay, so the first thing that we need is a SQLite database object itself. 21:36 So let's define a private SQLiteDatabase, named mDatabase. 21:39 And then we want our own version of that forecast data, forecast helper. 21:46 So we have private ForecastHelper and we'll call this let's call 21:52 it mForecastHelper. 21:58 Okay. 22:03 So let's start with the constructor, we're gonna, we're gonna write 22:04 this code, and then we're gonna use it in our main activity. 22:07 So the constructor is public ForecastDataSource, and we're gonna pass 22:09 in the context again, we need to pass it along to the ForecastHelper class. 22:14 And all we wanna do here is, well, I 22:22 should have created a context variable to hold on to. 22:24 So we'll do private Context mContext. 22:27 And now we can set mContext equal to the context. 22:32 And the second thing we wanna do is use our instantiate our forecast helper, 22:36 so that's equal to a new ForecastHelper and that requires the mContext. 22:42 So we pass that in. 22:47 Okay? 22:49 And now this class is going to be responsible for a couple different things. 22:50 So let's add some comments here, and we're gonna, we're, we're gonna define what 22:55 we're gonna do in this class and then we're gonna do it piece by piece. 22:59 So the first thing we need to do is open a database, and then conversely close one. 23:01 And then we're gonna do those C.R.U.D operations, 23:06 insert, select, update, and delete. 23:12 Okay, and I know we're doing a lot 23:17 here before running anything, and you know, generally I 23:18 like to do as little as possible, and then test it to make sure that it's working. 23:20 But we'll get this basic stuff setup, 23:24 we'll run, and then hopefully it'll make sense. 23:26 We'll walk through exactly where each piece is cuz you know, 23:28 we have these couple different pieces moving, but once we get 23:30 into the main activity, and call it, it should be more 23:33 apparent as to what's going on behind the scenes for us. 23:35 Okay, so let's start with the open method. 23:37 Let's do, so call this open and this one is special 23:46 we're gonna throw a SQLException just in case anything goes wrong. 23:50 As a generic SQL error that we wanna watch out for with certain operations. 23:56 but, yeah, so, this is just one line of code and it's from our forecast helper. 24:03 So we set our database, the SQLite database 24:09 from the forecast helper, using a method called getWriteableDatabase. 24:12 And this is the important line of code, 24:17 this is really what we were building toward. 24:18 So getWritableDatabase is from that SQLiteOpenHelper method, open help. 24:20 I'm sorry SQLiteOpenHelper class. 24:26 And it's a method that will create the database if it 24:27 doesn't exist, or just open the database if it exists for writing. 24:32 Now in content assist you may 24:35 have seen there's a different version, getReadableDatabase. 24:36 That's where if you want to have a read only database, but we're 24:38 gonna be inserting data and updating so we definitely wanna be able to write. 24:40 So now this is ready to use within our main activities. 24:44 So let's switch over and what I've done here is 24:48 I've left a lot of code based on the UI 24:52 and the buttons and things like that, things that you'll 24:55 hopefully already know how to do based on our Android courses. 24:58 But I've left notes for us to work through on 25:01 and the pieces we need to get the data in place. 25:05 So the first one up here is to declare a data 25:08 source, and that's gonna be using our new forecast data source class. 25:10 So let's make this a protected 25:13 ForecastDataSource, and we'll call it mDataSource. 25:16 Kay the next thing we do, wanna do 25:20 is instantiate that when the activity is created. 25:22 So sure enough down here there's 25:25 another note, mDataSource equals a new ForecastDataSource 25:27 and remember we need to pass on the context so we'll use MainActivity.this. 25:31 Okay. 25:37 So this creates our data source object and now 25:38 that we have it, we can open the database. 25:41 So we just call mDataSource.open and then this show 25:46 to create the database and open a connection to it. 25:50 So that's another thing we need to talk about is database connections. 25:54 So the database the way it works is we once 25:59 it's created, we open a connection to it, and that 26:03 connection we use to write and retrieve data, and when 26:05 we're all done with it, we have to close that connection. 26:08 Now it's important to manage opening and closing appropriately, so 26:11 that we don't leave a connection open when our activity is 26:15 destroyed or our app closes because you can end up with 26:18 no pointer references or memory issues, or corrupting the database somehow. 26:22 So we have to carefully correspond, 26:26 carefully match opening calls with closing calls. 26:30 So there are a couple of different ways you can do this. 26:32 One way would be to whenever you wanna perform a SQL operation, 26:34 you open a connection, perform that operation and then close it right away. 26:38 That's fine, although if you're doing a 26:42 lot of operations in an activity, you'll end 26:43 up with a lot of open and closing 26:45 calls which there's nothing really wrong with it. 26:47 The performance, it is minimal, but it just kinda clutters your code. 26:49 So, in general, what, what I like to do is open 26:53 and close my connections in life cycle methods of the activity. 26:55 So just a quick review of the life cycle of an activity. 26:59 When it's first created this onCreate method is 27:02 called, and that's where we set up and 27:05 instantiate a lot of variables, data we might 27:07 need tie things to the layout et cetera. 27:09 As soon as it's onCreate method is called, as 27:12 soon as it finishes rather, the onResume method is called. 27:15 So those, those two life cycle methods are piggy backed 27:18 on top of each other when an activity's first created. 27:21 Then when that activity is sent to the background 27:23 whether the user navigates to another activity or a 27:25 different app, closes the app, the onPause method is 27:27 called, so you always think of onResume and onPause together. 27:30 So if we open the database in onResume, and close it in onPause, then we'll make 27:33 sure that the database connection is only open 27:38 while our activity is active on the phone. 27:41 And that's the behavior we're after, so that's what we're gonna 27:43 do here is we're gonna move this mDataSource.open to the onResume method. 27:44 So I've got it down here again a little note let's just paste it in. 27:51 In here onPause we want our corresponding closer, and so we call mDataSource.close. 27:55 But wait we haven't defined the close method yet, so 28:01 let's switch back to ForecastDataSource and this is real simple. 28:04 We said we were, are going to use a close method, public void close, 28:09 and all we do is from this database method call the close method itself. 28:13 So this is, again, just a wrap where 28:18 this is an instance where this ForecastDataSource is really 28:19 just a wrapper on the call, so that we 28:22 aren't interacting with the database directly in our code. 28:25 Alright, so at this point, there is no errors 28:31 in the project and it should be ready to run. 28:33 So and this is, I've got a brand new emulator instance here 28:35 and I ran it without any database code in here. 28:40 So there's, there is nothing currently on 28:44 this Android phone, on this Android emulator. 28:46 So if we run it, we should create a database, and 28:48 we'll just wait a moment for it to load on the emulator. 28:52 Okay, there we go, oh, look, I got my log 28:54 head back [LAUGH], I forgot when I run the project. 28:58 That'll bring it back all right so nothing to 29:01 see here in app yet because we haven't done anything. 29:04 But behind the scenes, we should have created the database. 29:07 So how can we tell? 29:09 Well, if you remember we created a database name called TEMPERATURES.db. 29:11 And that's an actual database file that's available on our file system. 29:15 So there's different ways you can look at files on the end of your file system. 29:19 If you're using Eclipse, there's a built 29:22 in file explorer from the DDMS perspective. 29:24 But you can also use command line tools. 29:27 And that's what we're gonna do here because there's an additional 29:29 command line tool that interacts with SQL directly, it's called SQLite3. 29:32 So let's take a look at how to use that tool and make sure that we 29:35 are in fact, that we do in fact have a database created behind the scenes here. 29:38 So in the terminal it depends on how you have your environment set up. 29:42 You may need to navigate to the Android SDK. 29:47 So if you look here I've got I'm in the Android SDK that corresponds 29:49 to Android Studio and specifically I go into platform tools and if I type 29:53 ls for list, then I see a couple different tools available and what we're 29:58 gonna use is this one called adb which now adb stands for Android Debug Bridge. 30:01 So the one thing I wanna show is you can see the currently connected 30:09 devices if I do ./adb devices, then you could see the list of devices attached. 30:12 And this is the serial number for my emulator 30:18 instance and we need that to connect to SQLite. 30:22 So let me go back to Chrome for just a 30:26 moment, that's the next tab I wanted to show you. 30:27 This is the, tool from the command line that we're gonna use to take 30:30 a look at our database, and this is available in, under the Tools section. 30:33 There are a lot of different command line tools 30:37 available in Android, and we haven't really talked about 30:38 many at Treehouse, but if you go in through 30:40 a command line to a connected device like this. 30:43 You can do some of the powerful stuff to 30:45 make sure that your development is proceeding as planned. 30:46 So the next thing we're gonna do is execute, enter the remote ADB shell, 30:49 using this following command, and we need that serial number that I just showed you. 30:53 So the next line type ./adb -s for serial 30:57 number and then I'll just copy and paste this. 31:01 And we want the show. 31:06 Okay, now this is a shell on a device itself, on 31:08 the Genymotion emulator, so here we can use that sqlite3 command. 31:11 And if we just run this, sure enough we 31:18 get the three corresponds to the version of SQL. 31:21 And here we got a SQLite prompt and you know, depending on, on your 31:24 familiarity with SQL development you may have 31:28 used tools like this on other systems. 31:30 But there's not much here, we need to 31:33 instead open up to our, our own database specifically. 31:36 If you type .help you can see a bunch 31:40 of different options these are different commands you can run. 31:42 You can type straight SQL in here as well and 31:45 execute it as long as you end it with a semicolon. 31:47 But if we look here at the databases we should get the 31:50 list of databases but it's not, this is not for our app specifically. 31:52 We need to instead quit here, so we do .quit and we wanna 31:57 open the connection to our database and here you can use the format here. 32:02 So where is it? 32:06 Here we go, this line here shows the path to emulator device instance. 32:09 It's a data/data/package_name/databases, and then you 32:16 append the database at the end. 32:18 So, let's type that out, and that should make more sense. 32:20 So, sqlite3 and then we pass in /data/data/com.teamtreehouse., 32:22 and this is called, I called this friendlyforecast. 32:28 And /databases/, let's see if that's correct. 32:33 Yep. 32:38 We called it temperatures.db, so that should 32:39 open up okay, we've got the command prompt. 32:43 Now if we type let's see databases i think it lists. 32:46 Yeah, there you could see the file that corresponds the main database 32:51 because we're in our temperatures.db, you can see the file name itself. 32:54 Now if we look at the tables we should have one table temperatures. 32:58 Well there's an android meta datatable that's included 33:03 by default as well for your SQLite instance. 33:06 But temperatures is the one we define and you'll notice, you'll 33:09 notice that matches exactly what we created in our ForecastOpenHelper class. 33:11 So before we're go on, let's make sure this is what we expect. 33:19 We can look at the schema by typing schema and then giving the table name 33:22 TEMPERATURES, and sure enough, there is the exact create statement we defined. 33:27 It's got the _ID COLUMN and the second one is for TEMPERATURE. 33:32 Great, so that means our data is in place and we can start using it. 33:35 So, let's insert some data. 33:39 So coming back to Android studio. 33:42 We're gonna just work our way through this 33:46 class, we have instantiated the, the database we've opened. 33:47 We created it, opened it, we're closing it on onPause. 33:51 So now, let's take a look at the insert so let's see where we insert it. 33:53 So, if we go back to the app we're gonna have these couple of 33:58 buttons here and there's nothing to view to SELECT, UPDATE or DELETE right now. 34:01 So those buttons are greyed out. 34:05 But let's add the code to insert. 34:07 So there's an insert button, [COUGH] 34:11 and when it's on the click listener, 34:14 we're just calling this method called loadForecastData. 34:17 So if we scroll down a bit, 34:20 we see that loadForecastData calls our ForecastService. 34:21 And it calls at loadForecastData which executes a call back, so we're passing in. 34:26 This is again what we went through when we took 34:30 a brief look at this class, this is that loadForecastData. 34:32 So this call back gets executed when the data is returned, and that's down here. 34:36 Right now we're just logging the data, but now we can go ahead 34:42 and insert our new data but we need a method to do that. 34:45 So let's go back to our DataSource class, because that's 34:49 where we're writing our interface to all the database operations. 34:51 And for the rest of these operations we're going to 34:56 make use of a SQL construct known as a cursor. 34:59 So each of these methods is going to return a 35:03 cursor, or in some cases, a, a, a different value. 35:06 But what the cursor does is, we say to the database, okay, go and get me this data. 35:09 The database returns something called a results set, and it's a 35:13 set of data in whatever format we need, whatever we requested. 35:16 And then we use a cursor to iterate through that data however we want. 35:19 So you can think of it as like a block of, of rows in 35:23 the table and we say with the cursor, the cursor points to that block 35:26 and we can do operations like, okay go to the first row and it'll 35:29 go there and from that cursor we can then access the values in that table. 35:32 Then we can move the cursor around, we can just 35:36 loop through, we can move to the end, different things. 35:39 But right now with an insert what we wanna do is create our 35:41 create the data and, and store it into the table, into that temperatures table. 35:47 So, the first thing, let's, let's create the method itself. 35:51 Let me just glance at my notes. 35:55 okay, public void, insert forecast, and we had that 35:58 forecast object which has all the data we want. 36:04 Remember, that forecast object is returning from the web service, so, we'll 36:06 pass it in right here and we'll, we'll manipulate it from here directly. 36:11 [SOUND] 36:14 Kay, and, and, I, I got a little bit ahead of myself talking about the cursors. 36:17 That's actually more for selects and updates and deletes, but for the this one 36:21 because we're inserting, we're just going to 36:24 insert and assume success and come back. 36:26 We're not going to look at the data that was 36:29 inserted with the cursor, although you could certainly do that. 36:30 So, let's see, what should we do here? 36:34 Let's insert, we're gonna insert one row first, 36:39 and then we'll talk about inserting multiple rows. 36:42 So to do just one simple insert, now we're not gonna write raw SQL, we're 36:44 not going to create a SQL string that says insert into table values blah, blah, blah. 36:48 We're going to use a method from that sequel 36:53 open helper, from the sequel I dated as class, rather. 36:56 So the first thing we want to do is when we pass in values 37:01 like this whether we're inserting or updating 37:03 we use a class called content values. 37:05 We'll name this values and we'll just initialize it with a new content values. 37:08 Okay, but now it's kind of like a dictionary or a hash map, things 37:13 you might be familiar with, the contact values, we put things in to it. 37:18 So for these values we put a key at a value. 37:21 So the key here, because we're inserting in to our table, we're going 37:24 to insert data in to that temperature column, so we want to temperature key. 37:27 And sure enough that's why we made 37:30 this public constant if we type forecast helper. 37:31 Dot column temperature, that gets us the appropriate 37:35 column and now we just need a value. 37:38 Before we dive into the forecast object let's 37:41 just, let's just test it with decimal value. 37:43 We'll do a nice, beautiful 75 degree day okay. 37:46 And now we can use these values in the insert itself. 37:50 So, from our database object that we have running here. 37:54 We want to call insert and the first 37:58 parameter is the table, so forecast helper.table temperatures. 38:02 Second parameter is a null column hack, that's if 38:08 you, if you want to have null data in 38:11 your table, you can allow Null for certain columns, 38:13 and this is, well we're just gonna use null now. 38:18 But you can provide a value there if you need to get around 38:20 some restrictions with your null columns, it does not matter for this purpose. 38:25 And then here the last parameter you see are those content values. 38:30 So, what's happening is, we're gonna insert into our temperatures table, the 38:32 values that we just defined, so it's gonna go to, column temperature. 38:37 It's gonna add a new temperature of 38:41 75 degrees, and it's going to auto-increment that 38:42 id column that we defined, but you can 38:46 see here we're no manipulating that id ourselves. 38:48 It auto-increments in the database behind the scenes, 38:50 and that should be all we need for this 38:56 insert so let's go back to our main activity. 38:58 And now we can replace this to do with an actual 39:01 insert, so once again, from the data source, we call, insert forecast. 39:04 And lo and behold, we have a nice forecast variable we can use that was returned. 39:08 Okay? 39:13 And these methods here update high and low, we're actually not gonna 39:14 get to that, because we, I don't think we'll have enough time. 39:17 And then enable that other button. 39:20 So, once we insert data, we can go 39:21 ahead and start looking at select update and delete. 39:22 So let's just save this and run it, 39:26 snd make sure that we're actually inserting data. 39:28 Okay, we'll give Genymotion just a second to reload. 39:33 There we go, okay. 39:36 Now, if we type, if we click on 39:38 insert, we should see the buttons enabled, assuming success. 39:40 Great and you see these aren't, doing anything right now. 39:42 That's okay, but how do we know that we actually inserted data? 39:45 Well, we can go right back to the terminal and it didn't have any data before but now 39:49 if we run straight SQL here, if we select everything, select star from temperatures, 39:54 there we go. 40:02 We have one row, the very first ID is 1, because it 40:03 starts auto-incrementing from 1, and there's the 75 degrees we put in. 40:06 Very good. 40:10 Notice that the case the SQL is case sensitive or it's case insensitive rather. 40:11 So I typed in lowercase although common 40:15 convention is to type SQL commands in uppercase. 40:19 That's why we have uppercase names in our classes. 40:21 Okay, so let's take a look, we have 48 40:25 rows of data to insert, so let's input, let's 40:27 put all 48 temperatures into our table, so we 40:29 need to do that back in our forecast data source. 40:33 And the forecast itself crea, has an array 40:37 of hourly forecast and we can just loop through 40:40 each one, and put them in, however, there 40:43 is something we want to think about before that. 40:46 If we just looped through and did it like this using the database 40:49 insert, then, behind the scenes, there's a little bit of a performance hit. 40:52 And, every time you run a SQL command, like this, the data is inserted 40:57 and then immediately, it's committed, so any 41:02 kind of change is committed right afterwards. 41:04 And, when you're doing bulk sequel operations, 41:06 whether it's an Android or any other system. 41:09 What you want to do is try and delay those commits until you're either completely 41:12 done or maybe it's staged commits along the process if you have a lot of data. 41:16 So what we're gonna do is we're going to group all of these 41:21 48 commits, all of this 48 41:24 insertions, rather, into one single database transaction. 41:26 So the transaction will start, we will insert 48 pieces 41:29 of data, and then we'll finish the transaction and commit it. 41:32 That just saves us a little bit of 41:35 overhead because we're writing to the disk for 41:37 the data base, you know, it's going to 41:40 be completely negligible, the performance hit on this app. 41:42 But when you're working with large amounts of data you 41:45 may see a little bit of performance degradation, and another thing 41:47 we wanna do when working with sequel databases with larger 41:51 sets of data is make sure we, we do asynchronous operations. 41:54 So just like when we're getting data or information from the web, and 41:58 we may have some delay introduced 42:01 there, we do things asynchronously with callbacks. 42:02 Well we'll do the same thing with database operations a lot of time. 42:05 We'll execute, asynchronously and then when it's all done, we'll call 42:08 back to our code and say, okay data's ready, go on. 42:11 So, the first thing we need is to begin our transaction. 42:14 So, let's begin the transaction and then end the 42:18 transaction, and then we'll go back and add our loop. 42:20 So if we use our database object, and call, begin transaction and 42:23 then down at the end, we want to end our transaction. 42:29 Okay, so we can right our loop inside here, so 42:34 let's do a foreloop, We'll do a for each, and this 42:38 is for each I'm not going to go into too 42:41 much detail about this is the model structure for our data. 42:43 We'll call this, our, and we'll use the array 42:49 from the forecast, it's called forecast dot hourly dot data. 42:52 This corresponds, again if you want to see, if you have questions 42:56 about this parsing, certainly feel free to ask in the forum afterwards. 42:59 But since that isn't the focus here, we're just focused on the SQL but there's 43:06 one more thing we need to do here. 43:11 Because something might go wrong we wanna put a Try catch black here. 43:15 We wanna make sure we, end our transaction no matter what. 43:18 So instead of just, in case anything was, was, any exceptions were 43:21 thrown, we want to make sure we have a finally block that guarantees 43:25 that the transaction will be closed off because again, we don't wanna leave 43:29 the database in that in between 43:32 state where things didn't complete as anticipated. 43:34 And then, so this is that transaction itself, and then we wanna 43:38 trigger that commit that I mentioned and we do that with M database.set 43:42 transaction is successful, so our set transaction is successful, so notice we're 43:47 doing this here at the end, so after the entire four loop executes. 43:51 We come down here and set the transaction 43:54 as successful, and then we close that transaction. 43:56 So let's this should now insert, oh, but we haven't updated the temperature. 43:59 So this just gotta be to insert 75 48 times, instead we have that hour variable. 44:04 And from there we can get the specific temperature, which is a double value. 44:10 And that double decimal value corresponds to the real value in the database. 44:14 So if we run this, we should be able to see 44:18 in the app we'll execute the Insert. 44:23 Okay. 44:29 Oh and I forgot to add some code that 44:31 re-enables these buttons when we come in and there 44:32 is the data in the database, there was a, 44:35 they had a check for an existing data method. 44:36 But that way these would be enabled because 44:39 we do have data in the database now. 44:40 And it was just a simple select, count, star from 44:42 the database to make sure there were rows in there. 44:44 Okay, so I can retrigger it with the insert. 44:47 And if we go back to our command line, let's take a look at this. 44:49 Unfortunately, I can't just type the up arrow like in a console. 44:53 So, I have to retype this sequel. 44:56 But, if we select star, there we go, 48 rows. 44:58 well, 50 is because we had existing rows in there already. 45:02 Okay. 45:06 So there's a, different types of insert, we inserted 45:08 one row, and then we did a bulk insert. 45:11 So, this is fine, and we can view things in the terminal 45:13 but it'd be nice to view that data in our app, itself. 45:16 So let's see how to select that data from the database, and display it in the app. 45:18 So, to do that, we have a separate 45:22 activity that's the select button on the main activity. 45:24 And if we go up here and look at the on click 45:28 we see that we're just starting a new activity for view forecast activity. 45:30 So view forecast activity is a simplisitic activity, 45:35 and what it does is it's going to 45:39 execute a query, select all the data from 45:40 the database and then display in a simple list. 45:43 There are different ways to do this, this is a 45:45 very basic way again if you're using a content provider 45:47 you can use a thing called a cursor adapter to 45:50 adapt things but it's, again we're just introducing the basics here. 45:53 This is just to show you how to get data in and get data out. 45:57 So, much like the main activity, let's do a few things about getting a data source. 46:00 So, we have a protected, forecast data source, name 46:03 and data source and we're going to instantiate it here 46:08 and create data source equals a new forecast data 46:12 source with the context of view, forecast activity dot this. 46:18 Okay, now we want to, open and close in unpause and unresume. 46:21 So end DataSource dot open and end data source, 46:27 dot close. 46:33 Sure enough, and here, we don't have any button or anything to trigger the action. 46:34 We're just going to query all the data and bring it back 46:38 to the display every time the activity is, called with on resume. 46:41 So let's, add the code to select everything from the 46:45 database and then once we have that we'll update the list. 46:49 So let's go back. 46:52 Again, you'll be familiar with this patter now. 46:53 We're going to go back to our data source 46:55 object and that's where we'll write our select method. 46:57 So, the first one, this is where I was talking about the cursor. 47:00 So, we're going to return a cursor to the 47:04 results and let's call this Select all temperatures [SOUND] 47:06 and we don't need to pass anything into 47:13 this one, but we wanna create a cursor variable. 47:16 Call it Cursor, and we're going to execute a query method from our database object. 47:21 So this is again instead of writing the 47:29 raw sequel we're going to use the query method. 47:33 We'll return the cursor when we're done, and, I'm going 47:36 to break this, the parameters for this method on multiple lines. 47:40 So we can see exactly what we're doing, so the first thing we need is the data table 47:44 we're querying because we're just working with one data 47:48 table, but we can create multiple tables as well. 47:50 So we could have temperature, we could have a separate 47:52 table for wind conditions, or whatever we want, or locations maybe. 47:54 But this is the table name which will 47:59 go through our helper and we'll use table temperatures. 48:01 Okay. 48:06 And then the second thing is the columns that we wanna select. 48:08 So the format here, instead of just a single column, we need to list these as an 48:13 array of strings, so here, like, if we put forecast helper dot column temperature. 48:18 We would get an error which well, I'll just go ahead and fix 48:28 it now, what we do is just you just pass in a new 48:31 string away, and if we put curly braces around the string, that way 48:33 we get the columns names that we want, and this is just one column. 48:38 But we could separate multiple columns as individual items in 48:42 this array, which themselves would be separated by columns in here. 48:45 Okay. 48:49 So now there are a couple more parameters.But if we just 48:51 are selecting everything from a table, then this is good enough. 48:54 We just need to pass in null values for the rest of the parameters. 48:57 So this first one is the where clause. 49:00 Whoops. 49:03 I forgot a comment. 49:03 And what we'll do is we'll come back with a 49:06 separate method and look at some of these in more detail. 49:07 These are the where parameters, and then we 49:10 can do more advanced Sequel operations like group by. 49:14 And 49:17 again, we're just passing in null because we're not worried 49:19 about any of these having and finally, the order by clause. 49:21 And if you want to learn more about any 49:27 of these SQL constructs, again there's the database basics course. 49:30 Ok so this should give us a cursor, and if we go 49:34 back to the view forecast activity we can now create a cursor. 49:36 Cursor, cursor, equals and then from our data source. 49:40 Recall select all temperatures. 49:46 Now, what we're gonna do is just pass this cursor into a method called update list. 49:48 And we have it as a separate method because 49:53 we're gonna update the list in two different places. 49:54 So, if we go down to updateList, you'll be familiar with the part down here. 49:57 All it is, is a simple array adapter of decimals. 50:01 And it's gonna use a simple list item. 50:04 It's just gonna show a text view with the array with the values. 50:06 And we're using a variable called mTemperatures 50:11 which is an array of big decimals. 50:12 Now we're using big decimal here because double is not allowed, primitive types 50:15 are not allowed for inside generics like this we need to use a 50:19 wrapper class which is why we have big decimal it's just a conversion 50:24 back and forth, but it gets us the same data that we want. 50:27 So, what we need to do here is 50:31 loop through the cursor to populate our temperatures. 50:33 So, we start, the cursor is tied to our results set but we need, the 50:38 first thing we need to do is move it to the beginning of the result set. 50:43 So we say, cursor.moveToFirst, then we're gonna loop 50:46 through the entire result set using a wow loop. 50:50 And here we're gonna use a method well, cursor is after last. 50:53 So 50:59 let's, let's see, we'll do stuff in here 51:02 and then we're gonna move the cursor with cursor.moveNext. 51:04 Kay, so we, we move into the first one. 51:11 We come, we enter the while loop, it's not 51:12 after the last, because we're still at the first one. 51:14 We're gonna get the values we need, and then we'll move 51:17 the cursor, and then redo the loop until we're all done. 51:19 Okay, so what do we wanna do in here, where we're doing stuff? 51:22 Let's start with an index, so the way the cursor accesses. 51:27 Well, let's, let's do this, the cursor we, we get 51:32 things based off the index, the indices of the columns. 51:36 So we're after a double value and it's the temperature. 51:38 And 51:42 if we use the cursor we can get all different datatypes. 51:46 So these correspond to the different datatypes, 51:49 we can use in our SQLite database. 51:51 We're getting the double, double based on the column index. 51:53 So to get the column index, now we know that our 51:56 column index is zero because it's the only column in the database. 52:00 well, we have the ID column but that one is an 52:04 index so it starts at zero for the columns that we define. 52:06 So we could just pass on zero here but instead, let's get it this 52:10 is a little bit cleaner because we may change the order of our column. 52:14 So we wanna avoid hard coding the numbers if possible. 52:16 So instead we have an index, and i equals cursor, and low and 52:20 behold, there is a getColumnIndex based on a name, and the name is forecast helper., 52:25 whoops, helper.COLUMN_TEMPERATURE. 52:31 That gets us the appropriate temperature and now we 52:36 can just add this temperature to our array of mTemperatures. 52:39 So we're adding a bigdecimal. 52:43 We need a new big decimal from the double value temperature OK 52:46 and that gets us what we need However, when you're converting a double 52:51 to a big decimal, if you do it just like this with 52:55 a simple constructor you end up with a whole bunch of decimal places. 52:57 So it's gonna fill the entire listview and it looks silly, we don't 53:01 care about that precision cuz we're only getting data back in two decimal places. 53:04 So a simple fix is to add a second parameter, different constructor And 53:08 from a, a class called Math Context, we can use a decimal format. 53:11 And this will just format our data in a little more of a presentable manner. 53:16 And that should, update our list. 53:22 That's all we need here, we're looping through the cursor, setting the adapter. 53:25 So lets run it and verify. 53:28 Okay we'll wait for jenny motion. 53:32 Just a moment. 53:34 [BLANK_AUDIO] 53:34 Okay. 53:39 And, the, there's data already in the system so we should be able to click 53:40 on select but again I, I forgot to add that method to this prototype code. 53:43 So, we have to reinsert new data and it's just gonna keep a pending data into 53:48 our database which is fine because once we get to the end we're gonna delete it all. 53:51 So now that we have the button enabled, if we click on select then there is all the 53:55 data that corresponds to what we saw in the SQL Light tool in the command line. 54:00 Okay. 54:07 So that was getting everything. 54:08 But also, very common, we are going to select specific data. 54:10 You know, whether it's temperature for a specific 54:14 hour or again, like, information about a specific user. 54:17 So we want to use a where clause to limit the data we get back. 54:20 So what I've done is, I've added a field, 54:23 an edit text back here up in the action bar. 54:25 And when enter a temperature, it won't doing anything right now. 54:28 But when we enter a temperature, it's 54:31 going to filter out anything below that temperature. 54:32 So let's go back to our data source and add the method. 54:35 And this going to be, it's going to return a 54:39 cursor because we're still going to have a results set. 54:41 And let's call this, 54:44 select all temps, SelectTempsGreaterThan and then we'll pass 54:48 in a, we're gonna use string value let me just change that to make things easier. 54:54 I'm not worried about type checking here although in a production app we wanna 54:59 make sure that we're actually getting the 55:03 number, this is just just for speed's sake. 55:04 Okay, so let's start with a cursor. 55:07 Actually, what we're gonna do is copy and paste because all we're gonna, we're gonna 55:09 use the same format here, but we're going 55:12 to add a where clause and where parameters. 55:14 So let's copy everything here and paste it below. 55:17 And the where clause is going to be a string. 55:22 Let's call it whereClause, and we'll define it up here. 55:26 We could define it here in the method, but it just gets, it starts to 55:30 get a little too hard to understand so let's add it up here, String whereClause. 55:33 And let's write the where clause we 55:41 want and then were gonna parameterize it appropriately. 55:42 So here were going to select all temperatures 55:46 where temperature is greater then the minimum temp. 55:49 So where TEMPERATURE is greater then minTemp, easy enough. 55:53 So let's swap out TEMPERATURE 55:59 with from our ForecastHelper, our COLUMN_TEMPERATURE. 56:01 And let's swap out minTemp with a question mark. 56:07 That is the placeholder value where we can add our parameters afterwards. 56:10 So, here, we see the where parameters, we can put minTemp here. 56:14 Sure, we could append it in a string this is just showing different ways to do it. 56:17 But Just like we had to use a string array for column names. 56:22 We have to use a string array for the where parameters. 56:26 So, let's type, new String, and we'll put minTemp as the only item. 56:30 Inside and we won't do anything else with group 56:36 I having order by, but now we can use this 56:40 method back in our viewForecastActivity and that code for the action bar 56:42 is down here in a method called configureActionBar. 56:50 And I'm not gonna go in too much detail about this 56:55 if you have any questions, ask me in the forum afterwards. 56:57 But you can see we have an edit text, and from that 57:00 edit text we're gonna use it 57:02 as the parameter for method called filterTemperatures. 57:03 And that's where we will do our magic work. 57:06 We will 57:08 call 57:09 mDataSource.selectTemperaturesGreaterThan and 57:09 we'll just pass in that minTemp. 57:11 And when we're done we're gonna update the list with that cursor, oh, I forgot to 57:14 declare a cursor variable cursor equals the result from that. 57:19 So this filterTemperatures will be called when 57:27 we hit Enter set out of, setOnEditorActionListener. 57:29 When I hit Enter will use the value that was entered in that edit text. 57:32 So let's run it. 57:37 So take a look. 57:37 Okay. 57:40 We'll give Genymotion a moment to reload. 57:40 [SOUND]. 57:42 Okay. 57:45 And again, we're gonna have to insert more data. 57:46 So we're just tripling or quadrupling our data now. 57:49 Now, if hit on SELECT, we get the list view of everything. 57:53 And now, let's see. 57:55 We've got temperatures in the 70s. 57:57 Let's see if there's anything greater than 75 degrees. 57:58 There's a few temperatures you see it was filtered if we 58:02 put a high temperature of a 100 there shouldn't be anything, right? 58:04 And if we do a low temperature freezing 32 then 58:08 everything is the same because there is nothing lower then 32. 58:11 Okay, so that shows how we got two examples here of 58:14 a select all and then a select with a where clause. 58:17 So the rest are that's really you know this 58:20 is the meat of using the SQL database here. 58:24 The update and delete are, should be trivial based on these examples, but 58:27 let's walk through them and make sure that we understand everything that's going on. 58:30 For the update, we have an update button, let's see what do we have it doing. 58:36 It's just an onClick and we're gonna call it update method. 58:42 Let's just update everything to in the interest of time, 58:44 we'll update all values to 100, it'll be a heatwave. 58:49 So, back to our ForecastDataSource, we're gonna do our update method. 58:52 Now, the update returns the number of rows that have been updated. 58:57 So instead of a cursor, we're just gonna return an int. 59:02 And, let's call this updateTemperature, and we'll have a double for the newTemp. 59:05 Okay so because if you go back up to the 59:20 insertion, we used our content values to insert the due data. 59:24 Well we're gonna do the same thing for updating. 59:28 So let's declare ContentValues, call it values equals new ContentValues. 59:30 Okay, and let's put in the new value. 59:37 So this is gonna be once again for ForecastHelper.COLUMN_TEMPERATURE, 59:40 and the value is gonna be that newtemp that we're passing in. 59:48 Oops, I forgot to add the method called put. 59:52 Okay putting those new values in. 59:55 And then from the database, we're gonna call 59:58 the update method, and this is similar to 1:00:02 similar to the other methods we're using, but like 1:00:10 I mentioned, it returns [COUGH] an integer of rows updated. 1:00:12 So let's set that equal there. 1:00:17 We'll return, rowsUpdated. 1:00:19 And then we'll fill in the blanks for this update method. 1:00:21 So as usual, you can guess the first thing we need is the table name. 1:00:24 And then the second is the values that we're 1:00:31 going to update, and we'll pass in the content values. 1:00:35 And the third, we're gonna pass in null, it's a where clause. 1:00:38 And finally it's if we had an where clause, 1:00:44 we would have parameters and passing null there as well. 1:00:46 Okay, I think that's every thing we need, so let's go back to main activity. 1:00:53 To the OnClickListener for the update button. 1:01:00 And all I'll do is call mDataSource.updateTemperatures 1:01:04 and we're gonna update everything to 100. 1:01:08 What we'll do is run the update and then click on 1:01:12 the select button to make sure that all the temperatures have changed. 1:01:14 [BLANK_AUDIO] 1:01:18 Okay, so here comes our app, we'll reinsert, now 1:01:22 we'll UPDATE, okay, no visual indicator here, but if we 1:01:25 go to SELECT, everything is 100, so everything was 1:01:28 updated to 200 because, we didn't have any filter in. 1:01:31 So if we wanted to filter, if we only wanted to 1:01:35 update specific rows, we could use that ID column that we added. 1:01:37 We'd have to get it first, let's just get the appropriate 1:01:40 ID, then we can update the value for just that ID. 1:01:43 And we won't walk though an example, 1:01:49 because it should be pretty straight forward. 1:01:50 So you have an example now of how to add a where clause. 1:01:52 You do the same thing down here in the in the update statement. 1:01:55 Okay, so let's take a look at deleting one more method, public void deleteAll. 1:01:59 We're just gonna clear everything from the database. 1:02:06 And, it's a pretty simple method from the database object, 1:02:10 we just call delete and let's add our table, 1:02:15 ForecastHelper.Table_TEMPERATURES 1:02:20 and we could have a where call, where clause we'll just pass in null. 1:02:25 And go to parameters. 1:02:34 I forgot this comment here. 1:02:37 I'm just leaving these notes for reference for later on. 1:02:39 [SOUND]. 1:02:42 Okay, and then we just need to call this from 1:02:45 the onClickListener for the delete button, so let's go back here. 1:02:49 Scroll up just a little bit and we'll replace 1:02:55 this final to do, woops, clicking on the wrong spot. 1:02:57 Android Studio condenses things just to make, make it 1:03:01 easier to look at a glance what's going on. 1:03:05 You just click on the gray part to, to show it like this. 1:03:07 So from the data source, let's call delete all. 1:03:12 And run it to verify. 1:03:16 And once the emulator reloads, unfortunately, DELETE is grayed 1:03:21 out, so we'll reinsert data, and now delete it. 1:03:25 And now if we click on SELECT, we can 1:03:30 view it, and sure enough, there's nothing in there. 1:03:31 We try and filter, there's nothing to show. 1:03:34 Okay. 1:03:37 So that's a simple overview of the basic 1:03:39 operations you're going to do on a database. 1:03:42 That covers a lot of scenarios you're gonna come across, your, and that's why 1:03:44 it's, it's an acronym that is common 1:03:48 across programming languages, create, retrieve, update, delete. 1:03:50 One last thing I do wanna cover, though, before 1:03:54 we move on, because it's important, is upgrading the database. 1:03:55 So I'm just gonna do another example of how to, how to do that. 1:03:59 And then show a few tools, and then we'll be all set. 1:04:02 So for the upgrade, we wanna go back to 1:04:05 that ForecastHelper class that we haven't touched in a while. 1:04:07 And we have this empty onUpgrade method down here that we haven't used. 1:04:10 What happens is, let me switch to Chrome for a 1:04:15 moment because I've got a article that I'll link to 1:04:19 No I don't, okay. 1:04:24 Well I'll link to it another time in the notes. 1:04:25 But the, when your app calls that onCreate it checks 1:04:28 that version number that we're setting here on line 17. 1:04:34 And as long as that version number is the same, then 1:04:37 it's going to reuse the same database over and over again. 1:04:39 But, as soon as that version number changes, 1:04:42 then it will call the onUpgrade version instead. 1:04:45 And that's how you can trigger an update with a new version of your app. 1:04:47 So if we're releasing version 2.0 in our app, we have whole new 1:04:51 database schema that we want, then 1:04:53 we're gonna increment this database version number. 1:04:55 And when the user installs the update, and the first 1:04:58 time they run it, it's going to upgrade the database. 1:05:02 And it's up to us to make sure all 1:05:03 their existing data migrates over, if that's what we want. 1:05:05 So, that's what we're gonna do here. 1:05:08 We're gonna migrate existing data. 1:05:09 And we're gonna do it with an ALTER statement. 1:05:11 So let's add a new private static final String, DB_ALTER. 1:05:15 And like before, let's write it in plain SQL and then we'll add the parameters. 1:05:23 So we have an ALTER table. 1:05:28 [BLANK_AUDIO] 1:05:34 And join a blank on the alter syntax. 1:05:38 Okay, that's right. 1:05:44 ALTER TABLE with the table name TEMPERATURES. 1:05:45 And then we're going to add a column with a 1:05:49 new name TIME and it's gonna be of type INTEGER. 1:05:53 The reason we're using integer for time is because if we look at the data returned by 1:05:57 the API it's returned in a Unix time 1:06:02 format, which is second since certain time in 1970. 1:06:04 So this is a, there are utilities converts this to 1:06:08 human readable time, but we'll start on the back end 1:06:11 as an integer and that's what, that's how SQLite because 1:06:14 it's a light version that's how it stores time data. 1:06:17 Okay, so let's just plug in the appropriate values 1:06:21 here, so instead of TEMPERATURES let's add the TABLE_TEMPERATURES variable. 1:06:24 Instead of the COLUMN TIME let's add a new variable. 1:06:30 We call it COLUMN_TIME and just like we have 1:06:36 COLUMN_TEMPERATURE, let's copy and paste and make that time. 1:06:39 [BLANK_AUDIO] 1:06:44 Okay. 1:06:50 And to execute on ALTER statement, we use the same exact method 1:06:51 db.execSQL and instead of DB_CREATE we have DB_ALTER. 1:06:57 Now before we just run this though what would happen if a 1:07:02 new user installed version 2.0 of our app who didn't have version 1.0. 1:07:05 If we don't make sure to also upgrade that DB_CREATE statement then they're gonna run 1:07:10 the old version and it's not gonna work 1:07:15 with our, the new code that we're providing. 1:07:18 So we wanna make sure we update, if we're ever providing new upgrade 1:07:19 statement, we wanna make sure that we're providing a new create statement as well. 1:07:23 So let's change the create statement, we're just going to append here, after 1:07:28 COLUMN TEMPERATURE, add a comma, space plus, and then we have COLUMN_TIME. 1:07:33 Plus and it's a type INTEGER, and now our 1:07:40 Create Table matches the upgrade statement from the alter statement. 1:07:44 Okay, so that's all we need to do. 1:07:49 well, no it's not because this is the important part that I, I kept stressing. 1:07:51 We need to we must increment to trigger, an upgrade. 1:07:55 So instead of version one, we are now dealing with version two. 1:08:01 So 1:08:04 now let's try it and see. 1:08:07 And we're gonna go back in to the command line in a moment, and just verify the 1:08:08 data structure, data structure change, we're not going 1:08:11 to write any additional code, to look at it. 1:08:14 [BLANK_AUDIO] 1:08:16 Here in the code should, well it should 1:08:20 have been executed right away in the onCreate method. 1:08:23 So if we go to the terminal and we can do schema for temperatures. 1:08:26 And sure enough, you see that the schema changed. 1:08:37 And now in addition to the temperature itself, 1:08:39 we have the time in the integer format. 1:08:42 But if we select star from temperatures 1:08:44 you see that this blank values. 1:08:50 Because we didn't actually insert the time, we haven't changed our code. 1:08:51 But you can see that the the column ID has been incrementing. 1:08:54 We still have the temperatures, and now we just have a 1:08:57 blank value, and we didn't have these vertical pipes to separate before. 1:08:59 It's just a visual cue, to let us know there's an additional column there. 1:09:02 [BLANK_AUDIO] 1:09:04 Okay, so that's all I wanted to cover in the app itself. 1:09:07 So again, it's an overview of the basic C.R.U.D operations, 1:09:11 an upgrade, how to create and set up your database. 1:09:13 But, as you do more and more with SQL 1:09:16 databases, you may start to get involved with using the 1:09:18 content providers that we talked about, or you my wanna 1:09:21 use some additional tools to make your development life easier. 1:09:23 So, you may be familiar in 1:09:27 other environments with abstracting that data away. 1:09:29 We got that data source object, but we can take it even further by 1:09:31 using model objects that have additional database 1:09:35 behavior that we can access from our activities. 1:09:38 So I, I'm gonna give a quick run down of 1:09:41 a few things, a few tips for SQL development for Android. 1:09:43 The first one here is a really useful way to run some commands 1:09:47 in the ADB shell that makes all SQL statements logged to log head. 1:09:51 So this is great for debugging instead of having to log your own statements. 1:09:56 If you turn this on than every single SQL 1:10:01 statement from the device will be logged in log head 1:10:03 and you can take a look and make sure 1:10:06 that the SQL you're expecting is coming through the device. 1:10:07 Next tool here I really like, it's an open source library called android_dbinspector. 1:10:12 And the way it works is, this version is just for Android Studio so 1:10:18 to get it to work with Eclipse would take a little bit of work. 1:10:24 But it's a library project that you set in your existing project. 1:10:27 And then you change your manifest, you add a debug manifest. 1:10:31 It's a little bit convoluted if you want to try it out and 1:10:35 you have any trouble, certainly stop by in the forum and let us know. 1:10:38 But once you get it up and running, when you run your app from Android Studio, it 1:10:41 will load up this DbInspector app on the side with a, a separate launcher. 1:10:46 And you can view the data for your app and you see 1:10:53 in the screenshots here, there's the 1:10:58 database file, it's got it's individual tables. 1:11:00 If you click into tables, you can see 1:11:02 the structure and you can also see the contents. 1:11:04 So it's a nice way to look on the app at the data behind the scenes. 1:11:06 And then lastly I have two different versions of 1:11:11 how do you wanna call these? 1:11:15 Abstraction models for taking your data into plain Java 1:11:16 objects that you can then reuse in your app. 1:11:20 And that just makes your code easier to understand and maintain. 1:11:22 If you're working with a forecast object instead 1:11:24 of working, writing your SQL and working with 1:11:27 it directly, then your, your code in your 1:11:30 activities and elsewhere is a lot easier to understand. 1:11:31 The first one here is called greenDAO. 1:11:34 DAO stands for data access object. 1:11:37 And this is our wrapper around the SQLite database you can see here they 1:11:39 have some claims about being increased performance 1:11:45 small memory consumption and easy to use APIs. 1:11:51 I have read, I haven't used this for myself, but I have read 1:11:53 reviews and performance tests that say this is a good library to try out. 1:11:57 And the last one was just brought to 1:12:02 my attention yesterday, I was talking with one of 1:12:03 our mobile developers, Joe Steel, and he mentioned cupboard, 1:12:05 another library that does some of the same things. 1:12:07 It extracts out that database layer and it uses plain old 1:12:10 Java objects to, and, and here, the example, I think illustrates. 1:12:14 So, in your activity, you'd have this, this code 1:12:18 with the cupboard library with the database that you provide. 1:12:20 You put a book object, so it puts that book into the database. 1:12:24 And makes it a little bit cleaner to insert or extract data from the database. 1:12:27 So, that just about does it. 1:12:34 Thanks a lot for sticking through to the end here. 1:12:36 I'm gonna check and see if there's any questions at the moment. 1:12:39 And again if not, if we don't get to them. 1:12:41 Then we will catch up on the forum. 1:12:42 Okay, so, Jonathan asks is there a reason why I 1:12:46 decided to use big decimal versus the auto-boxed double class. 1:12:50 no. 1:12:57 No I could have just used that. 1:12:59 I feel like I may have had a reason, before, but no, yeah, so, yeah. 1:13:01 What that's referring to is where we're taking the double 1:13:06 values, the primitive values and is adapting them for the display. 1:13:08 We could have just used the double with a capital D as the, the more complete 1:13:12 object datatype that, that's usable within the array adapters, and within the list. 1:13:18 Okay. 1:13:26 Looks like there may not be any other questions at the moment. 1:13:28 Okay, great. 1:13:30 Again, if you have any questions follow up in the forum 1:13:31 or find me on Twitter @benjakuben, and thanks so much for watching. 1:13:34 See you later. 1:13:37