1 00:00:05,300 --> 00:00:08,119 Alright, so that's the database file created. But at that moment 2 00:00:08,119 --> 00:00:09,060 there's nothing in it. 3 00:00:09,350 --> 00:00:14,690 So we need to execute some SQL statements to create a table and put some data in it. 4 00:00:14,870 --> 00:00:17,280 So let's go ahead and add the code for that. 5 00:00:17,360 --> 00:00:19,780 So we're going to do var sql 6 00:00:19,880 --> 00:00:30,570 equals, and then double quotes CREATE TABLE space contacts. Then parentheses, or left parentheses, underscore 7 00:00:30,650 --> 00:00:39,380 id space INTEGER space PRIMARY KEY, PRIMARY space KEY, space NOT space 8 00:00:39,380 --> 00:00:51,290 NULL comma name space TEXT comma phone space INTEGER comma email space TEXT. Then right parentheses and close the 9 00:00:51,290 --> 00:00:51,900 double quote 10 00:00:51,900 --> 00:01:01,850 to close the string off. Now what we can do is do a database.execSQL, then in parentheses 11 00:01:01,850 --> 00:01:03,890 pass sql being the string. 12 00:01:04,060 --> 00:01:10,250 Then we want execute. And then what we'll do is, on the next line, we'll do sql equals, in double quotes, 13 00:01:10,820 --> 00:01:21,590 INSERT space INTO space contacts parentheses name comma space phone comma space email right parentheses 14 00:01:21,590 --> 00:01:31,570 space. Then values, then left parentheses single quote, and I'll put tim in single quote comma and I'll put 6456789 15 00:01:31,580 --> 00:01:42,530 comma. Then single quotes tim@email.com and a single quote there, then a right parentheses, 16 00:01:42,650 --> 00:01:44,000 then a double quote. 17 00:01:44,120 --> 00:01:51,490 Now I'm going to do the same thing there, database.execSQL and pass sql as the argument again. 18 00:01:51,530 --> 00:01:56,570 So the method we call to execute SQL statements in our database, as you can see, is the execSQL 19 00:01:56,570 --> 00:02:02,460 method, and all we're doing there is passing in a string containing the SQL statement we want executed. 20 00:02:02,480 --> 00:02:09,449 So we start off by creating a table called contacts. Now that contains three columns; name, phone and email. 21 00:02:09,630 --> 00:02:11,750 And we're also including an id column there. 22 00:02:12,050 --> 00:02:16,610 And we've discussed the id column before, and we'll see how Android uses it 23 00:02:16,610 --> 00:02:19,550 when we come to populate a list view from our data. 24 00:02:19,850 --> 00:02:22,150 So next we inserted some data into the table. 25 00:02:22,270 --> 00:02:27,840 We are going to insert some data in the table by using the sql insert statement on line 23. 26 00:02:27,930 --> 00:02:29,600 We've used that in previous videos. 27 00:02:29,760 --> 00:02:35,390 Now by the way, Kotlin strings are enclosed in speech marks or double quotes, so therefore it makes sense to use 28 00:02:35,390 --> 00:02:40,740 single quotes inside our SQL insert statements. And I've done that as you can see on line 23. 29 00:02:40,760 --> 00:02:44,670 Now we could use double quotes or speech marks inside the string, 30 00:02:44,840 --> 00:02:47,630 but then we'd have to escape them with a backslash 31 00:02:47,710 --> 00:02:53,150 so that Kotlin knows we're putting a speech mark or double inside the string, rather than terminating the 32 00:02:53,150 --> 00:02:57,030 string at that point. Now it can get confusing having all those backslash characters 33 00:02:57,050 --> 00:03:02,980 though. So I think it makes a lot more sense to use single quotes for the values inside a SQL string. Alright, so 34 00:03:02,980 --> 00:03:08,780 at this point that's our first insert statement, and it should create a new row in the contacts table with 35 00:03:08,780 --> 00:03:09,840 those values. 36 00:03:10,310 --> 00:03:14,720 And these are the same SQL commands that we've used in previous videos when we entered SQL into 37 00:03:14,720 --> 00:03:17,380 the sqlite3 command line interface. 38 00:03:17,420 --> 00:03:23,010 So here we're literally just passing them as arguments to the execSQL method. 39 00:03:23,030 --> 00:03:28,120 Now the execSQL method isn't really intended to be used for data manipulation statements. 40 00:03:28,190 --> 00:03:30,850 It works and we will verify that shortly, but 41 00:03:30,870 --> 00:03:33,710 let's see what the documentation has to say about it. 42 00:03:33,740 --> 00:03:36,910 So I'm going to come over here and click on the execSQL, 43 00:03:37,190 --> 00:03:40,910 and I'm going to bring up the, open the documentation. 44 00:03:41,100 --> 00:03:45,510 And you can see here it "Executes a single SQL statement that is NOT a SELECT or any other 45 00:03:45,510 --> 00:03:48,080 SQL statement that returns data". 46 00:03:48,100 --> 00:03:52,300 So in other words, if you try to execute a select statement you'll get an error. 47 00:03:52,520 --> 00:03:55,290 Now the documentation, if you scroll down a little bit further, 48 00:03:55,300 --> 00:04:01,660 also mentions the insert and update methods and we're encouraged to use them instead of execSQL 49 00:04:01,670 --> 00:04:04,370 when we want to change the data in the database. 50 00:04:04,370 --> 00:04:10,310 Now one reason for using Insert and Update, is that we've got no record of the ID that was generated for 51 00:04:10,330 --> 00:04:11,160 our new row. 52 00:04:11,420 --> 00:04:12,870 Now that's often important. 53 00:04:13,040 --> 00:04:17,459 We may want to store it in the views tag, for example, so that we can quickly retrieve the record that's 54 00:04:17,480 --> 00:04:18,769 being displayed. 55 00:04:18,829 --> 00:04:19,820 One example. 56 00:04:19,820 --> 00:04:25,310 Now I won't change the way we're adding that record, but I will add another one, this time using the insert 57 00:04:25,310 --> 00:04:26,510 method instead. 58 00:04:26,900 --> 00:04:33,460 And to do that, we can come down here after the INSERT INTO contacts line, after the second call to the exec 59 00:04:33,460 --> 00:04:44,720 SQL method. We can put val values equals, and we're going to call ContentValues, opening and closing parentheses 60 00:04:44,990 --> 00:04:47,060 dot apply. Then left 61 00:04:47,860 --> 00:04:52,340 curly brace and right, which you can say has been added automatically. Then on the next line I'm going 62 00:04:52,340 --> 00:04:55,980 to put put parentheses, 63 00:04:56,110 --> 00:05:04,100 and it's going to be name in double quotes comma and Fred in double quotes. Next line we're going to do a put parentheses 64 00:05:04,100 --> 00:05:04,520 again, 65 00:05:04,700 --> 00:05:09,930 phone in double quotes, and then this time we're going to put 12345 without double quotes. And 66 00:05:10,030 --> 00:05:19,000 on the third line, put parentheses email in double quotes comma and in double quotes fred@nurk dot 67 00:05:19,000 --> 00:05:21,810 com double quote and a right parentheses. 68 00:05:22,110 --> 00:05:24,100 Then we've got the closing right 69 00:05:24,100 --> 00:05:31,240 curly brace there as you can see. Then that after that what we're going to do is put val generatedId is 70 00:05:31,240 --> 00:05:43,690 equal to database.insert parentheses contacts in double quotes comma and values. And I missed 71 00:05:43,690 --> 00:05:49,350 one argument there because if we have a look at the insert again, we'll just go back here and have a look. It 72 00:05:49,480 --> 00:05:55,030 needs to have the null column hack as well, as well as values. So what I'll do is I'll come back there and add null 73 00:05:55,030 --> 00:05:59,760 as the second argument there, and that should fix that up. 74 00:05:59,880 --> 00:06:00,270 Alright. 75 00:06:00,310 --> 00:06:05,830 So we get, you can obviously see now that the insert method is taking three arguments; the name of the table 76 00:06:05,830 --> 00:06:08,560 to insert into - contacts in this case. 77 00:06:08,560 --> 00:06:14,140 We've also got this null column hack, then also and then the values that we're actually adding, or 78 00:06:14,140 --> 00:06:17,770 wanting added to the table, or to that particular table. 79 00:06:17,770 --> 00:06:20,200 Now I'm going to talk about the null column hack argument 80 00:06:20,200 --> 00:06:21,010 a bit later. 81 00:06:21,310 --> 00:06:23,500 For now though, as you can see, I'm just using or passing 82 00:06:23,500 --> 00:06:24,610 null for that. 83 00:06:24,610 --> 00:06:28,990 Now the values are provided in something called a content values object. 84 00:06:28,990 --> 00:06:34,810 And really all that is is just a wrapper class around a hash map which lets it store a set of key value 85 00:06:34,810 --> 00:06:41,080 pairs. And you can see obviously, we're assigning the key value pairs on line 29 through 31. We've basically got one 86 00:06:41,080 --> 00:06:44,380 for each field in that table, each column in that table. 87 00:06:44,500 --> 00:06:48,730 Basically the keys are the column names in the table which you've probably figured out. And we're then passing the 88 00:06:48,730 --> 00:06:52,860 table, table name and our values to that insert method. 89 00:06:52,990 --> 00:07:00,760 Now I've also assigned the value return by insert on line 34, to a variable called generatedId, so that we 90 00:07:00,760 --> 00:07:04,240 can log it and check that it's what we expected it to be. 91 00:07:04,240 --> 00:07:09,080 Now you may not have come across apply before - I'm talking about the code up here 92 00:07:09,230 --> 00:07:10,010 on line 28. 93 00:07:10,500 --> 00:07:17,350 It's a Kotlin extension function that takes a function block, and executes it with an object it's called 94 00:07:17,350 --> 00:07:18,720 on as its receiver. 95 00:07:18,910 --> 00:07:24,790 So effectively that means that the function block runs just as if it was a function of the class, the 96 00:07:24,790 --> 00:07:26,750 ContentValues class, 97 00:07:26,800 --> 00:07:33,520 in this case. So apply returns the object it was called on which is extremely useful, because here we're assigning 98 00:07:33,520 --> 00:07:39,840 that to the values variable, which means that the values get the new content values object. 99 00:07:39,910 --> 00:07:43,190 Now the equivalent code we could have done, I'm just going to paste this 100 00:07:43,220 --> 00:07:44,500 in just to give you an alternative, paste that in there. 101 00:07:47,560 --> 00:07:52,840 I'll just fix up this formatting. 102 00:07:52,940 --> 00:07:57,380 We could have done it that way as you can see. That's a similar way of doing it but I like the way that 103 00:07:57,380 --> 00:08:01,700 apply returns the object that it was called on. I think it makes for better looking code. 104 00:08:01,700 --> 00:08:08,630 But the example that I pasted in here would be how you would write it in, in Java or non-idiomatic Kotlin. 105 00:08:08,790 --> 00:08:09,380 It's usual, 106 00:08:09,380 --> 00:08:13,050 it's more usual to use apply when doing things like this though. 107 00:08:13,460 --> 00:08:18,950 Alright, so the code now that we've written it should create our database and a contacts table, and insert 108 00:08:18,950 --> 00:08:22,010 some data into it. Before we run this and check it though, 109 00:08:22,010 --> 00:08:23,560 let's add some logging. 110 00:08:23,570 --> 00:08:27,230 We're going to start with the usual TAG constant. 111 00:08:27,610 --> 00:08:38,559 Let's go up and do that. We'll do a private constant val tag equals, in double quotes MainActivity, 112 00:08:38,770 --> 00:08:42,159 and obviously that should've been above the class. So let me put that in the right place. 113 00:08:44,169 --> 00:08:48,210 OK, there's our const written, and let's add a bit of logging just to make sure it's working. 114 00:08:48,230 --> 00:08:56,090 So I'm going to come down here, and just before the exec I'm going to put Log.d parentheses TAG 115 00:08:56,120 --> 00:09:06,180 comma, then in double quotes we're going to put onCreate colon sql is $sql. 116 00:09:06,950 --> 00:09:08,870 I'm going to take a copy of that line. 117 00:09:09,000 --> 00:09:13,820 So that's for our creation table, and down here for the SQL for the insert, 118 00:09:14,030 --> 00:09:20,320 I'm going to do the same thing before the exec, the exec is called, onCreate sql is sql again. That's 119 00:09:20,330 --> 00:09:21,420 our second line. 120 00:09:21,550 --> 00:09:26,600 And for the third one after the val generatedId line, let's come down to the end of that. I'm going to 121 00:09:26,600 --> 00:09:29,360 do some logging there and we're going to change that a little bit. 122 00:09:29,360 --> 00:09:38,260 We're going to put record added with id $generatedId. At this point 123 00:09:38,260 --> 00:09:43,270 now we should be able to run the app and see the entries in the logcat. So let's actually run this now. 124 00:09:46,500 --> 00:09:49,890 So what I'm going to do is select an emulator that I've defined 125 00:09:49,890 --> 00:09:51,760 that's not a Google Play emulator. 126 00:09:51,810 --> 00:09:56,690 And the reason is that we're going to be using adb to connect to the device and view the database 127 00:09:56,710 --> 00:10:02,060 later. So I've got one that I've already created here called Nexus 5X API 26 ROOT ACCESS. 128 00:10:02,160 --> 00:10:07,650 And that tells me straight away that it's not a Google Play emulator, and that's because a Google Play emulator 129 00:10:07,890 --> 00:10:09,720 doesn't give you the ability to get root access. 130 00:10:09,720 --> 00:10:10,970 So I'm going to select that one, click on 131 00:10:11,360 --> 00:10:13,350 OK, and we'll just give that a moment to start. 132 00:10:19,130 --> 00:10:21,430 I can make that a bit bigger but we really want to see the logs. 133 00:10:21,440 --> 00:10:22,770 So let's have a look in our log. 134 00:10:26,950 --> 00:10:32,720 And we've got a lot of stuff here, so what we can do just to make it a bit easier to see what we've done 135 00:10:32,720 --> 00:10:33,500 here, 136 00:10:33,710 --> 00:10:38,950 we can remove some unnecessary noise from the filter by filtering on MainActivity. 137 00:10:38,960 --> 00:10:46,190 So let's do that; forward slash mainactivity, and you can see doing that we can see really much more clearly 138 00:10:46,670 --> 00:10:51,440 what's actually happened here. Without the slash, by the way, the forward slash, you may still get some 139 00:10:51,450 --> 00:10:57,890 uninteresting log entries from the Android system. That's expected though if we have a look at the log entries. 140 00:10:58,010 --> 00:11:02,330 The new ID for that second row is number 2 which we would have expected. 141 00:11:02,330 --> 00:11:06,860 Alright so that's how to execute sequel statements from our Kotlin code. 142 00:11:06,950 --> 00:11:13,490 We use the the execSQL method of the sqlite database class to execute SQL statements. 143 00:11:13,490 --> 00:11:16,800 We've also got insert, update and delete methods we can use as well. 144 00:11:16,940 --> 00:11:21,400 And these are more appropriate when inserting or updating data in the database. 145 00:11:21,440 --> 00:11:26,480 Now by the way, the execSQL will work for insert, update and delete statements, but you'll 146 00:11:26,480 --> 00:11:32,540 see later why it can be useful to get that ID that was generated for a count of the number of rows affected, 147 00:11:32,630 --> 00:11:36,570 in the case of the update and delete methods. Alright, so let's finish the video here. 148 00:11:36,570 --> 00:11:42,150 The next step is to write some code to retrieve the data and we'll work on that in the next video.