1 00:00:05,000 --> 00:00:10,880 In the last few videos we've seen how to use the SQL language to create tables in a database, insert and 2 00:00:10,880 --> 00:00:13,060 delete rows and query the data. 3 00:00:13,220 --> 00:00:16,210 It's now time to use what we've learned in an Android app. 4 00:00:16,309 --> 00:00:19,520 We're going to start with what is really the simplest application 5 00:00:19,520 --> 00:00:21,560 you'll probably ever see for a database. 6 00:00:21,920 --> 00:00:26,840 Now it's not following best practices, so don't think that this is the way that you generally access 7 00:00:26,840 --> 00:00:29,030 a database in your applications. 8 00:00:29,300 --> 00:00:33,980 This exercise is just to give you a simple overview of how to use what you've learned in the previous 9 00:00:33,990 --> 00:00:38,920 videos, in Kotlin code, rather than from the SQLite console program. 10 00:00:39,020 --> 00:00:42,080 We'll be looking at much better ways of doing this in future apps. 11 00:00:42,110 --> 00:00:47,750 So view this more as an introduction to how to use SQL in your Kotlin code and not as the correct 12 00:00:47,750 --> 00:00:50,510 way to work with databases. Alright, 13 00:00:50,520 --> 00:00:51,830 so let's get started. 14 00:00:51,830 --> 00:00:54,270 We're going to create a new project in Android Studio. 15 00:00:54,720 --> 00:00:59,030 We'll call this one Sqlite Test. 16 00:00:59,200 --> 00:01:02,970 I'm going to make sure the domain is set to learnprogramming.academy as it has been throughout the 17 00:01:02,970 --> 00:01:03,760 course. 18 00:01:03,850 --> 00:01:08,370 And make sure the checkbox is checked for include Kotlin support. Click on 19 00:01:08,510 --> 00:01:15,810 Next, and make sure that API 17 is selected and we've de-selected the other options, which we have. Click on 20 00:01:15,810 --> 00:01:16,980 Next. 21 00:01:17,010 --> 00:01:21,870 Now for this one, we're going to be using the floating action button for this app. So we're going to select the basic 22 00:01:21,870 --> 00:01:24,580 activity and click on Next. 23 00:01:25,020 --> 00:01:29,420 And finally, everything on this final configure activity screen is correct and fine as it is. 24 00:01:29,640 --> 00:01:33,560 So I'm going to accept those defaults and click on Finish. 25 00:01:33,740 --> 00:01:39,060 Alright, so we'll just give that project a short while to load and finish building and indexing. 26 00:01:39,170 --> 00:01:44,830 And then we're going to open up MainActivity and write some code in the onCreate method. 27 00:01:44,900 --> 00:01:49,820 Now as I said earlier in the introduction, we're going to create what is probably the simplest application 28 00:01:50,120 --> 00:01:53,190 for writing data to a database that you'll code. 29 00:01:53,240 --> 00:01:54,880 It's very, very simple. 30 00:01:54,950 --> 00:01:56,640 It's just a proof of concept, 31 00:01:56,750 --> 00:02:01,760 before we go into more detail and do things in a more structured way. Alright so you can see that things are loading 32 00:02:01,760 --> 00:02:03,020 here now. We're just about done. 33 00:02:05,610 --> 00:02:14,400 What we'll do is, we'll go to the folder, give that a moment to compile and finish and re-index, or index 34 00:02:14,400 --> 00:02:15,560 for the first time I should say. 35 00:02:16,950 --> 00:02:23,190 It's finished now and I'm going to double-click MainActivity from our academy.learn programming package. 36 00:02:23,230 --> 00:02:24,370 And we're now good to go. 37 00:02:24,700 --> 00:02:31,750 Now the Android framework includes a SQLite database class called SQLite database, and we're 38 00:02:31,750 --> 00:02:36,670 going to be using that to create and access our database. Now you don't need to know the full ins and outs 39 00:02:36,670 --> 00:02:38,920 of how this particular class works. 40 00:02:38,950 --> 00:02:44,020 Think of it like the SQL 3 command line program - the one that we used previously in this section - 41 00:02:44,020 --> 00:02:49,330 to allow us to execute SQL statements. The SQLite database class works in a similar fashion, 42 00:02:49,570 --> 00:02:55,500 but instead of typing sqlite prompt, we pass strings containing SQL statements, and the SQLite 43 00:02:55,540 --> 00:02:57,700 database object executes them for us. 44 00:02:57,910 --> 00:03:03,350 So to get this to work we need to start by creating a database object. So I'm going to go ahead and do that, above 45 00:03:03,370 --> 00:03:12,330 the fab.setOnClickListener line. I'm going to type val database is equal to baseContext dot 46 00:03:12,790 --> 00:03:20,600 and it's going to be openOrCreateDatabase. Now Android Studio's very helpful when you type these methods, and 47 00:03:20,900 --> 00:03:25,580 you saw that as soon as I typed the opening parentheses after the openOrCreateDatabase, 48 00:03:25,640 --> 00:03:27,660 it showed the arguments that we have to provide. 49 00:03:27,920 --> 00:03:33,910 Now by the way, I tend to use the term method when referring to a function belonging to one of the Android 50 00:03:33,920 --> 00:03:38,510 Java classes, and function when talking about Kotlin code. 51 00:03:38,650 --> 00:03:42,240 Now they are the same thing and if you want to call them functions instead that's fine. 52 00:03:42,260 --> 00:03:47,630 Just be aware though of the different terms when you're talking to Java programmers. OK so there's several 53 00:03:47,630 --> 00:03:51,240 ways to use this method, but we'll stick with the first one for now. 54 00:03:51,230 --> 00:03:55,680 And you can see it's asking for a string, an int and something called a CursorFactory. 55 00:03:55,780 --> 00:04:00,110 So to find out what these arguments should be we can check the documentation. 56 00:04:00,160 --> 00:04:05,400 Now if I typed in the arguments I could click anywhere on the openOrCreateDatabase method, and use 57 00:04:05,450 --> 00:04:11,890 Control Q or Control J, depending whether you're on a PC or a Mac, and bring up the documentation. But at the 58 00:04:11,900 --> 00:04:13,320 moment though, this doesn't work. 59 00:04:13,520 --> 00:04:19,750 Android Studio is unable to work out which particular version of the openOrCreateDatabase documentation 60 00:04:19,760 --> 00:04:24,770 we want to see, and that prevents it from linking to the documentation correctly. 61 00:04:24,770 --> 00:04:29,600 Now of course this is a catch 22, because if we knew what the arguments were, we probably wouldn't need 62 00:04:29,600 --> 00:04:32,550 to call up the documentation in the first place. 63 00:04:32,560 --> 00:04:37,670 So it did used to be possible to put the cursor inside the opening and closing parentheses before typing 64 00:04:37,670 --> 00:04:39,390 Control J, and again it's Control Q 65 00:04:39,400 --> 00:04:42,250 on a PC, but that's no longer working. 66 00:04:42,260 --> 00:04:47,690 So if you find yourself in that position when trying to get the documentation for Android Studio, just 67 00:04:47,720 --> 00:04:53,690 enter any suitable values for each parameter, and once you know what they should be, you can change them. So here you can 68 00:04:53,690 --> 00:04:59,900 see the top version of the method - one's a string, an int and something called a SQLitedatabase dot 69 00:04:59,900 --> 00:05:01,050 CursorFactory. 70 00:05:01,060 --> 00:05:06,460 So I'm just going to put a string and call it name, and I'll put the number 1 in there for the int. And I'm just going to pass 71 00:05:06,460 --> 00:05:14,200 null for the time being, for the CursorFactory. So now I can go back to the method name now that we've filled that out. 72 00:05:14,340 --> 00:05:17,970 Put my cursor there and I can do a Control J on a Mac, and again it's control 73 00:05:17,980 --> 00:05:21,640 Q on a PC, and now the documentation is coming up for us. 74 00:05:21,720 --> 00:05:23,700 So that's actually a lot more helpful. 75 00:05:23,830 --> 00:05:29,380 We can see now that this method opens a SQLite database, and reading further you can see it creates 76 00:05:29,380 --> 00:05:35,900 the database file if it doesn't already exist. Now when you use this way to view the documentation, Control 77 00:05:35,930 --> 00:05:37,260 Q or Control J, 78 00:05:37,450 --> 00:05:42,460 you'll often find there's a blue upwards pointing arrow available. And you can see there is up here as 79 00:05:42,460 --> 00:05:43,540 well. 80 00:05:43,580 --> 00:05:47,980 Now if it's greyed out then there's no further documentation available, but here it's in blue and you can 81 00:05:47,980 --> 00:05:53,820 see that the link said that we could view the documentation, or view the external documentation. So I 82 00:05:53,830 --> 00:05:59,550 can click it, and it'll open up the relevant documentation on my Mac's default browser. 83 00:06:01,200 --> 00:06:05,110 And as you can see it hasn't gone straight to the method. We still need to look up the method. 84 00:06:05,150 --> 00:06:11,630 So I'm just going to do a find for the method name we're looking at, and that's openOrCreateDatabase, 85 00:06:11,800 --> 00:06:13,230 and I'll just look for the second one. 86 00:06:13,550 --> 00:06:19,130 And the one that we want to have a look at is the overloaded one that had name, mode and Cursor 87 00:06:19,130 --> 00:06:19,550 Factory. 88 00:06:19,550 --> 00:06:21,180 So I'm going to click on that. 89 00:06:21,410 --> 00:06:25,910 So I'm presuming that was a bug, and that eventually this'll be fixed and it'll go straight to the 90 00:06:25,910 --> 00:06:30,230 method, but you can see there that I needed to add another step to get to the method that I wanted to see 91 00:06:30,230 --> 00:06:31,160 via a browser. 92 00:06:31,570 --> 00:06:36,490 But this part is a lot more helpful. In the old days we had to leaf through the printed manuals, but 93 00:06:36,530 --> 00:06:40,970 now all the documentation's available with a couple of key strokes. And you can do this for just about 94 00:06:41,030 --> 00:06:45,690 anything you want to know more about. Just click in the name of the class or object, and use Control J 95 00:06:45,760 --> 00:06:50,420 if you've got a Mac or Control Q on a PC, and you can find out more information on whatever it is. 96 00:06:51,270 --> 00:06:56,870 Alright so we're going to continue on in the web browser for the documentation, seeing as though we're here now. Now we're going to 97 00:06:56,870 --> 00:06:58,750 use this MODE_PRIVATE, 98 00:06:58,790 --> 00:07:04,250 this option here, for the default operation, but we can check what the other options are by clicking on 99 00:07:04,250 --> 00:07:07,270 the links. So scrolling down this alphabetical list here, 100 00:07:07,410 --> 00:07:10,470 we can see we've got MODE_ENABLE_ WRITE_AHEAD_ LOGGING. 101 00:07:10,790 --> 00:07:13,670 So let's click on that and then prepare to be disappointed. 102 00:07:14,300 --> 00:07:19,670 So that's not really very helpful. If you were hoping for an explanation of what WRITE AHEAD LOGGING 103 00:07:19,670 --> 00:07:20,090 means, 104 00:07:20,090 --> 00:07:22,050 this doesn't really explain a lot. 105 00:07:22,190 --> 00:07:25,720 When that happens, just look for other links that might more provide more information. 106 00:07:25,730 --> 00:07:30,380 Now there's not much point clicking on the first two links here because they're the ones going back to the 107 00:07:30,410 --> 00:07:32,780 openOrCreateDatabase methods. We've just come from there 108 00:07:32,780 --> 00:07:35,300 so we'll end up going round in circles. 109 00:07:35,620 --> 00:07:36,900 But have a look at this third one here, 110 00:07:36,960 --> 00:07:41,720 this enableWriteAheadLogging method. That's far more intuitive. You can see we've got lots of information 111 00:07:41,720 --> 00:07:42,660 about that. 112 00:07:42,800 --> 00:07:45,740 Now I won't read through it all. Basically 113 00:07:45,770 --> 00:07:48,530 it allows concurrent access to the database. 114 00:07:48,560 --> 00:07:54,010 Now in Android there's not often any need to allow your database to be accessed on multiple threads. 115 00:07:54,030 --> 00:07:57,670 There's only one app in the foreground at any one point in time. 116 00:07:57,690 --> 00:08:03,390 Now if you start writing things like services and your service needs to write to the database, then you'd 117 00:08:03,410 --> 00:08:08,960 probably have to enableWriteAheadLogging. By the time you get to that stage you'll know that you need it. 118 00:08:08,960 --> 00:08:13,420 So we're going to use mode private for the mode, and set the factory argument to null. 119 00:08:13,640 --> 00:08:18,110 You can then use your own subclass of Cursor, and that arguments how to do that. 120 00:08:18,140 --> 00:08:22,590 So generally though the Cursor class that Android provides is fine for most purposes. 121 00:08:22,790 --> 00:08:24,300 And if we go back and have a look there, 122 00:08:26,630 --> 00:08:31,590 this factory here, "optional factory class that is called to instantiate a cursor when the query is called". 123 00:08:31,610 --> 00:08:36,820 As I mentioned, generally the cursor class that Android provides is fine for most purposes. Alright, 124 00:08:36,830 --> 00:08:40,909 so let's go back to our code. 125 00:08:41,220 --> 00:08:47,310 So the name argument will be our database file name, and the mode, we should be setting that to context dot 126 00:08:47,310 --> 00:08:48,780 mode underscore private. 127 00:08:48,990 --> 00:08:54,330 Now before I enter those values, you want to hover over the error that the mode is providing, the mode argument 128 00:08:54,330 --> 00:09:00,090 there. The error message mentions a few other options as you can see there. We've got mode world readable and 129 00:09:00,090 --> 00:09:01,640 mode world writeable. 130 00:09:01,940 --> 00:09:06,060 Well they were deprecated in API 17 and we really shouldn't be using them. 131 00:09:06,330 --> 00:09:11,580 Making our database world readable is a pretty bad idea from a security point of view, and world writeable's 132 00:09:11,640 --> 00:09:13,370 obviously much worse. 133 00:09:13,820 --> 00:09:18,930 Now they've been marked as deprecated in the documentation we just looked at, but did allow the database 134 00:09:18,930 --> 00:09:20,310 to be shared with other apps. 135 00:09:20,380 --> 00:09:22,260 So they're still present in the Android source code, 136 00:09:22,260 --> 00:09:25,830 in other words, but could be removed in a future version. 137 00:09:25,890 --> 00:09:28,970 Now there are other ways of sharing data, that we're going to be talking about 138 00:09:28,980 --> 00:09:34,620 those a little bit later - things like a content provider, broadcast receiver and a service. 139 00:09:34,680 --> 00:09:41,960 So let's now update this with the right settings. So we're going to go with sqlite dash test dash 140 00:09:41,960 --> 00:09:42,180 1.db. 141 00:09:42,180 --> 00:09:50,000 And instead of hard-coding a 1 we're going to go with MODE underscore PRIVATE, 142 00:09:50,160 --> 00:09:52,440 then we're going to leave null as a third option for now. 143 00:09:52,620 --> 00:09:57,420 So this first bit of code will open the database for us if it finds it. If there isn't already a database 144 00:09:57,500 --> 00:10:04,520 called sqlite-test-1.db, then it will automatically create one for us. And we'll have 145 00:10:04,520 --> 00:10:08,390 a look once we're ready to run the code and see the database file on the emulator. 146 00:10:08,410 --> 00:10:10,440 For now though let's finish the video here, 147 00:10:10,620 --> 00:10:15,630 and in the next one we'll start writing some SQL code. So we'll start creating some strings with our SQL 148 00:10:15,630 --> 00:10:19,980 code, then get to the stage of executing the app to see what happens. 149 00:10:19,980 --> 00:10:21,490 So I'll see you in the next video.