Basic use of Swift learning course
- 2020-06-15 10:21:13
- OfStack
preface
In our daily development, we often encounter the situation that users disconnect the Internet or the network is slow, so that users will display blank pages when they enter the page on 1. So how to avoid the embarrassment of showing blank pages without the network? The answer is: first, cache 1 part of the data when the network is good, so that the next time the network is bad, at least the user can see the content cached before, thus improving the user experience of APP.
SQLite is a scheme for us to implement local data cache. SQLite has the following advantages: iOS is embedded with SQLite; Verified by time; Open source; Cross-platform.
OK, without further ado, let's get into the SQLite experience. Of course, we have to do a little preparatory work before we start. After all, we don't fight wars without preparation.
The preparatory work
Creating alternate data
Import SQLite3: import SQLite3 Create an Goods class to represent the type of data stored in the database Create 1 array of type Goods Declare 1 global variable for dbPath and db, and 1 function for libraryDirectory path (how to select the database path)
The code is as follows:
class Goods {
let name: String!
let weight: Int!
var price: Double!
init(name: String, weight: Int, price: Double) {
self.name = name
self.weight = weight
self.price = price
}
}
let goods = Goods(name: "computer", weight: 10, price: 2000.0)
var goodArr = [Goods]()
var dbPath = ""
var db: OpaquePointer?
func createData() {
for index in 0...4 {
let goods = Goods(name: "computer" + "\(index)", weight: index * 10, price: 20.0)
goodArr.append(goods)
}
}
func fetchLibraryPath() {
if let libraryPathString = NSSearchPathForDirectoriesInDomains(.libraryDirectory, .userDomainMask, true).first {
let pathURL = URL(fileURLWithPath: libraryPathString).appendingPathComponent("goods.sqlite")
dbPath = pathURL.path
}
}
Create and connect to the database
func openDatabase() -> OpaquePointer? {
var db: OpaquePointer?
if sqlite3_open(dbPath, &db) == SQLITE_OK {
resultLabel.text = " Successfully open the database, path: \(dbPath)"
return db
} else {
resultLabel.text = " Failed to open database "
return nil
}
}
As we can see from the above code, we first declare an optional value of OpaquePointer type db, and then call the sqlite3_open() method, which opens the database if it has been created before, or creates one if it has not. If the method call is successful, it returns an OpaquePointer assignment to the db you passed in.
SQLITE_OK is 1 constant defined in the SQLite library, which represents the 0 of 1 Int32. Most SQLite functions return an Int32 value, such as SQLITE_ROW (100), SQLITE_DONE (101), etc. You can see the list here.
Now you can open or create a database by calling db = openDatabase(). Normally you will see the database opened successfully, with the output of xxx/ xxx.sqlite.
Now that we have successfully created a database named ES69en.sqlite, all we need to do is create a table.
Create a table
code
func createTable() {
let createTableString = """
CREATE TABLE Computer(
Id INT PRIMARY KEY NOT NULL,
Name CHAR(255),
Weight Int,
Price Float);
"""
var createTableStatement: OpaquePointer?
// The first 1 step
if sqlite3_prepare_v2(db, createTableString, -1, &createTableStatement, nil) == SQLITE_OK {
// The first 2 step
if sqlite3_step(createTableStatement) == SQLITE_DONE {
resultLabel.text = " Table created successfully "
} else {
resultLabel.text = " Table was not created successfully "
}
} else {
}
// The first 3 step
sqlite3_finalize(createTableStatement)
}
Code instructions
First explain createTableString under 1: Create a table named Computer, Id is primary key and not empty, Name is no more than 255 characters, Weight is of type Int and Price is of type Float.
Then I create an OpaquePointer? A variable of type is used for the following function: sqlite3_prepare_v2().
Insert 1 piece of data
code
func insertOneData() {
let insertRowString = "INSERT INTO Computer (Id, Name, Weight, Price) VALUES (?, ?, ?, ?);"
var insertStatement: OpaquePointer?
// The first 1 step
if sqlite3_prepare_v2(db, insertRowString, -1, &insertStatement, nil) == SQLITE_OK {
let id: Int32 = 1
// The first 2 step
sqlite3_bind_int(insertStatement, 1, id)
sqlite3_bind_text(insertStatement, 2, goods.name, -1, nil)
sqlite3_bind_int(insertStatement, 3, Int32(goods.weight))
sqlite3_bind_double(insertStatement, 4, goods.price)
// The first 3 step
if sqlite3_step(insertStatement) == SQLITE_DONE {
resultLabel.text = " Insert data successfully "
} else {
resultLabel.text = " Failed to insert data "
}
} else {
}
// The first 4 step
sqlite3_finalize(insertStatement)
}
Code instructions
insertRowString the & # 63; It is a placeholder that tells the compiler to insert the value when the statement is actually executed. Step 2: sqlite3_bind_int() identifies a value of type Int to which the first argument is your statement(i.e. insertStatement) and the second argument is ? Is in your statement(note that the value is non-zero), in this case 1, and the third parameter is the value you want to bind to. The sqlite3_bind_text() function means that you are binding to an text (usually used for long strings). This function takes two more arguments than sqlite3_bind_int(). The fourth argument means the number of bytes of text, usually -1, and the fifth argument is an closure callback, which is called after processing string. Step 3 Step 4 is the same as above
Insert multiple data
code
func insertMutipleData() {
let insertRowString = "INSERT INTO Computer (Id, Name, Weight, Price) VALUES (?, ?, ?, ?);"
var insertStatement: OpaquePointer?
// The first 1 step
if sqlite3_prepare_v2(db, insertRowString, -1, &insertStatement, nil) == SQLITE_OK {
for (index, good) in goodArr.enumerated() {
let id: Int32 = Int32(index + 1)
// The first 2 step
sqlite3_bind_int(insertStatement, 1, id)
sqlite3_bind_text(insertStatement, 2, good.name, -1, nil)
sqlite3_bind_int(insertStatement, 3, Int32(good.weight))
sqlite3_bind_double(insertStatement, 4, good.price)
// The first 3 step
if sqlite3_step(insertStatement) == SQLITE_DONE {
resultLabel.text = " Insert data successfully "
} else {
resultLabel.text = " Failed to insert data "
}
// The first 4 step
sqlite3_reset(insertStatement)
}
} else {
}
// The first 5 step
sqlite3_finalize(insertStatement)
}
Code instructions
insertRowString ditto. Step 4: Call the sqlite3_reset() function to execute insertStatement again on the next loop Step 1, step 2, Step 3 and Step 5 are the same as above.
Update the data
code
func updateData() {
let updateString = "UPDATE Computer SET Name = 'changeComputer' WHERE Id = 2;"
var updateStatement: OpaquePointer?
// The first 1 step
if sqlite3_prepare_v2(db, updateString, -1, &updateStatement, nil) == SQLITE_OK {
// The first 2 step
if sqlite3_step(updateStatement) == SQLITE_DONE {
resultLabel.text = " The update is successful "
} else {
}
}
// The first 3 step
sqlite3_finalize(updateStatement)
}
Code instructions
updateString: Changes the Name field for Id==2 data to changeComputer. sqlite3_prepare_v2() : ready, sqlite3_step() : Perform update statement, sqlite3_finalize() : end.
Delete the data
code
func deleteData() {
let deleteString = "DELETE FROM Computer WHERE Id = 2;"
var deleteStatement: OpaquePointer?
// The first 1 step
if sqlite3_prepare_v2(db, deleteString, -1, &deleteStatement, nil) == SQLITE_OK {
// The first 2 step
if sqlite3_step(deleteStatement) == SQLITE_DONE {
resultLabel.text = " Delete the success "
}
} else {
}
// The first 3 step
sqlite3_finalize(deleteStatement)
}
Code instructions
deleteString: Delete the data in table Id==2. sqlite3_prepare_v2() : ready, sqlite3_step() : Execute delete statement, sqlite3_finalize() : end.
Query 1 piece of data
code
func queryOneData() {
let queryString = "SELECT * FROM Computer WHERE Id == 2;"
var queryStatement: OpaquePointer?
// The first 1 step
if sqlite3_prepare_v2(db, queryString, -1, &queryStatement, nil) == SQLITE_OK {
// The first 2 step
if sqlite3_step(queryStatement) == SQLITE_ROW {
// The first 3 step
let id = sqlite3_column_int(queryStatement, 0)
let queryResultName = sqlite3_column_text(queryStatement, 1)
let name = String(cString: queryResultName!)
let weight = sqlite3_column_int(queryStatement, 2)
let price = sqlite3_column_double(queryStatement, 3)
resultLabel.text = "id: \(id), name: \(name), weight: \(weight), price: \(price)"
} else {
resultLabel.text = "error"
}
}
// The first 4 step
sqlite3_finalize(queryStatement)
}
Code instructions
queryString: Find all Id == 2 data in Computer table. Step 2: Note that status code is SQLITE_ROW. If true is true, then the data you are querying exists in the table. Step 3: The sqlite3_column_int() function fetches the data according to the number of columns, the first parameter is statement, and the second parameter is the column of the field (Id is the first column in the table, calculated from 0). sqlite3_column_text() is a little more complicated by the fact that he needs to convert the type via String(cString: queryResultName!). . Steps 1 and 4 are the same as above
Query multiple data
code
func queryAllData() {
let queryString = "SELECT * FROM Computer;"
var queryStatement: OpaquePointer?
// The first 1 step
if sqlite3_prepare_v2(db, queryString, -1, &queryStatement, nil) == SQLITE_OK {
// The first 2 step
while(sqlite3_step(queryStatement) == SQLITE_ROW) {
// The first 3 step
let id = sqlite3_column_int(queryStatement, 0)
let queryResultName = sqlite3_column_text(queryStatement, 1)
let name = String(cString: queryResultName!)
let weight = sqlite3_column_int(queryStatement, 2)
let price = sqlite3_column_double(queryStatement, 3)
resultLabel.text = "id: \(id), name: \(name), weight: \(weight), price: \(price)"
}
}
// The first 4 step
sqlite3_finalize(queryStatement)
}
Code instructions
Step 2: Here is the while loop, which ends by returning SQLITE_DONE status code when the last row of the query is reached. Step 1 step 3 Step 4 same as above.
summary
From the above we can summarize the general flow of executing 1 statement :sqlite3_prepare_v2() : ready, sqlite3_step() : execute statement, sqlite3_finalize() : end. Ok, here the basic operation of SQLite3 is finished. In the next part, we will learn about the advanced use of SQLite. Bye ~