Get data from SQLite to complete a product information show

In the actual development of iOS, we often use core data as the first choice for storage. But when dealing with a large number of complex data values and correlating data, this has to be achieved using a relational database. For example, a navigation program, itself should contain a large number of map itself data and data need to start when the app starts reading load. Also the data itself changes are not particularly frequent. It is a waste of time to repeatedly send requests to the server for information. So we can use a local data file to configure it directly. SQLite, a lightweight relational database, is the first choice for iOS development. While Xcode itself contains the SQLite library, it is convenient to use the iOS without the need for additional configuration files. Here's a simple example of how to write from installation Sqlite3 to a simple demo.

The results are as follows: Click on the left cell to display the product details

First, you need to install SQLite, most of the current version is more than 3. Mac Version Portal: Https://www.sqlite.org/2016/sqlite-dll-win32-x86-3150100.zip. Installation is complete, the terminal input $sqlite3, display version information is installed successfully

Second, a simple database is required. The requirement here is a catalogue of the company's products, each of which includes detailed information about the manufacturer, product name, etc. to be displayed. SQLite has many visual creation channels, with more users such as SQLite Manager or Navicat, which is a software that supports visual editing of multiple databases. Visual creation is not considered in this article, and is directly used in the form of a command line.

Open the Mac terminal input sqlite3 (file name). DB, this statement starts Sqlite3 and creates a DB data file, (file name) for the custom file name. Enter the command after the terminal enters SQLite edit mode and enter the statement at the command prompt:

CREATE TABLE "main". " Product " (" ID "integer PRIMARY KEY autoincrement not NULL," Name "TEXT," Manufacturerid "Integer," Details "text," "Price" DOUBLE, "Quantityonhand" Integer,"Countryoforiginid" Integer, "Image" text); Statement is free to wrap, end with a semicolon, enter execution to get a created table, its statement content interpretation such as (only the first three lines, the post-order content, just the data format is different):

To better explain the relational data traits of SQLite, we also need to create two table statements as follows:

CREATE TABLE "main". " Manufacturer "(" Manufacturerid "INTEGER PRIMARY KEY autoincrement not NULL," Name "TEXT is not null);

CREATE TABLE "main". " Country "(" Countryid "INTEGER PRIMARY KEY autoincrement not NULL, " country "TEXT not null);

Now that we have a simple database with three tables, but no data to fill, no real value, then we should organize the data into the database, in the command line, we can use the INSERT INTO keyword to insert a row of information into the table. But the efficiency of this method and its low, is generally used as a program when the data is added to the time of use, in the background data collation, usually the need to organize the data into a text document for import, text content as follows: All text content and full code affixed to https://github.com/ Huafushengweirui/data/tree/master/two

1	widget A	1	details of widget A	1.29	5	1	Canvas_1
2	widget B	1	details of widget B	4.29	15	2	Canvas_2
3	widget X	1	details of widget X	0.29	25	3	Canvas_3
4	widget Y	1	details of widget Y	1.79	5	3	Canvas_4
5	widget Z	1	details of widget Z	6.26	15	4	Canvas_5
6	widget R	1	details of widget R	2.29	45	1	Canvas_6
7	widget S	1	details of widget S	3.29	55	1	Canvas_7
8	widget T	1	details of widget T	4.29	15	2	Canvas_8
9	widget L	1	details of widget L	5.29	50	3	Canvas_9
10	widget N	1	details of widget N	6.29	89	3	Canvas_10
11	widget E	1	details of widget E	17.29	26	4	Canvas_11
12	Part alpha	2	details of widget apla	1.49	25	1	Canvas_12
13	Part bata	2	details of widget bata	1.89	35	1	Canvas_13
14	Part gamma	2	details of widget gamma	3.46	45	2	Canvas_14
15	device N	3	details of device N	9.29	15	3	Canvas_15
16	device O	3	details of device O	21.29	15	3	Canvas_16
17	device P	3	details of device P	51.29	15	4	Canvas_17
18	tool A	4	details of tool A	14.99	5	1	Canvas_18
19	tool B	4	details of tool B	44.57	5	1	Canvas_19
20	tool C	4	details of tool C	6.99	5	1	Canvas_20
21	tool D	4	details of tool D	8.29	5	1	Canvas_21

Write your own note that the order of the fields must be the same as the order in which they were created and must be segmented with TAB tab. The text document is processed at the SQLite command prompt. Separator "\ T", which specifies that the tab is the delimiter for the data, followed by input. Import "(filename). txt" priduct enter execution without error warning, Data content has been imported into the table, if there is a visual software can be opened directly to view, if not, you can use the SQL statement in the terminal select * from product Query results command line display text content data import error . The same method fills the data for manufacturer and country, with the following data:

content of country:
1 USA
2 Taiwan
3 China
4 Singapore


manufacyure's contents:
1 sprit industries
2 industrial designs
3 design intl
4 tool masters

Before formally entering the demo, you should probably mention the advantages and characteristics of the relational database. A careful look at the product table will find that in the data bar of the manufacturer and the country of origin, we fill in the number integer, which is clearly inconsistent with the fact that neither the manufacturer nor the origin can be a number. But notice will be found in country and manufacturer tables, the manufacturer and the country have different numbers to indicate, in the terminal input select Name,country from Product,country where Product.countryoforiginid = Country.countryid; results such as:

You can see the results of the query at the command line, and all the numbered content is replaced with the ID corresponding to the country in the Country table, then the problem suddenly clear, we do not explicitly enter the manufacturer and the origin in the main product's datasheet, but the data ID in the other table. Such a way to a large extent avoids the input of a large number of duplicate data, which is the most important feature of relational database. For example, there may be hundreds of products from the same origin. Similarly, we can query the manufacturer of the product through the SQL Association statement. At the same time, you can also directly filter the data, such as query all Chinese-made product statements select Name,country from product,country where Product.countryoforiginid = Country.countryid and country.country = "China"; The results are as follows:

After the database is processed, the rest is to read the data and show it in the code. For more details on display, for convenience. The master-details template is used directly here. This is a simple master-slave interface that allows for quick data presentation without the need for additional code design. Open Xcode directly, create a new project, and select the Master-details template. Its interface is as follows:

Project initial interface as above, in storyboard altogether can see five controller views, respectively is the root controller, two navagation controller, the remaining two need to focus attention is also need to write code display view, namely master and details. Analyze the project file according to the MVC principle, and also lack a model class to encapsulate the parsed data, which should include fields from the data table. To create a new OC class named Product, add the following attribute code for each database field in the header file:

#import <Foundation/Foundation.h>

@interface Product : NSObject
@property(nonatomic)int ID;
@property(nonatomic,copy)NSString *name;
@property(nonatomic,copy)NSString *manufacture;
@property(nonatomic,copy)NSString *details;
@property(nonatomic)float price;
@property(nonatomic)int quantity;
@property(nonatomic,copy)NSString *countryforgin;
@property(nonatomic,copy)NSString *image;
@end

After the model class is processed, the data should be fetched from the database and assigned to the code in the model, and in development, a single class encapsulation of a function can be very good at reducing coupling, so if you write a generic class to perform the function of accessing the database and getting the data, the code will be more flexible and intuitive. So we need to create a class to interact with the database, provide an initialization database, close the database, and get the data and return it as a collection of product model objects. To use SQLite in iOS, you first need to add the SQLite module to the project. Add methods such as

After adding the Sqlite3 related libraries, drag the database db file into the project folder and try to put it in the supporting file for direct reading. Then create a class to interact with the database, a new OC class, named Dbaccess, first import sqlite3.h, because we need to use the relevant function, and then import product, because we have to get the data at the same time the product is assigned, so we will definitely use this class. Then analyze the method requirements of the class, first of all a method to initialize the database, and then close the database, which requires a method to obtain data to create model objects, assignments, and return data. Roughly three methods are required. Name Initializedatebase, CloseDatabase, getallproducts respectively. The first two methods do not return an object, and the last one is to return the object that has been assigned well, so it should be an array. Model objects are generated one at a time and therefore cannot be processed all at once, so it is a mutable array. All three of these methods need to be called to the outside world. So it needs to be declared inside the. h file. The code for the Dbaccess.h file ends here:

#import <Foundation/Foundation.h>
#import "Product.h"
#import <sqlite3.h>
@interface Dbaccess : NSObject
- (void)initializeDatebase;
-(void)closeDatabase;
-(NSMutableArray *)getAllproducts;
@end

Then the method is implemented in the. m file, and the implementation code is as follows:

#import "Dbaccess.h"

@implementation Dbaccess
sqlite3 * database; // Add a class variable to hold a reference to the database
-(id) init {reclass initialization method
    if (self = [super init]) {
        [self initializeDatebase]; // Call the initializeDatebase method when the class is initialized
    }
    return self;
}
-(void) initializeDatebase {// Initialize the database
    NSString * path = [[NSBundle mainBundle] pathForResource: @ "catalog" ofType: @ "db"]; defines the data table path
    if (sqlite3_open ([path UTF8String], & database) == SQLITE_OK) {// Open the data table, if successful, output log
        NSLog (@ "opening database");
    } else {// otherwise close the database and debug with NSAssert1 function, throw an exception
        
        sqlite3_close (database); //
        NSAssert1 (0, @ "faildd to open database:‘% s ’.", Sqlite3_errmsg (database));
    }
}
-(void) closeDatabase {// Close the database
    if (sqlite3_close (database)! = SQLITE_OK) {// If not closed normally, throw an exception
        NSAssert1 (0, @ "error: faild to close database:‘% s ’.", Sqlite3_errmsg (database));
    }
}
-(NSMutableArray *) getAllproducts {Get all data, encapsulate it into a model, and return all model objects
    NSMutableArray * products = [[NSMutableArray alloc] init]; // generate a mutable array
    const char * sql = "SELECT product.ID, product.Name, \ \\ Set a variable to store the SQL statements you need
    Manufacture.name, product.details, product.Price, product.Quantityonhand, country.country, product.Image FROM Product, Manufacture, Country where manufacture.manufactureID = product.manufactureID and product.countryforginID = country.countryid "; \\ statement Set up
    sqlite3_stmt * statement; // Create a sqlite statement object This object is used to execute SQL statements
    int sqlresult = sqlite3_prepare_v2 (database, sql, -1, & statement, nil); //, connect the database to the sql statement and return the result
    if (sqlresult == SQLITE_OK) {If the result is SQLTME_OK, traverse the number of table rows
        while (sqlite3_step (statement) == SQLITE_ROW) {
            Product * product = [[Product alloc] init]; Each time a row is returned, a product object is generated
            char * name = (char *) sqlite3_column_text (statement, 1); defines the string name to save the first column from the table
            The function index is based on 0, so name is the first column
            product.name = (name)? [NSString stringWithUTF8String: name]: @ ""; Assign a value to the name property of Product.
            If name is not empty, then product.name = name (string content), if empty, then product.name = "" (that is, an empty string)
            char * manufacture = (char *) sqlite3_column_text (statement, 2);
            product.manufacture = (manufacture)? [NSString stringWithUTF8String: manufacture]: @ "";
            char * details = (char *) sqlite3_column_text (statement, 3);
            product.details = (details)? [NSString stringWithUTF8String: details]: @ "";
            char * countryforgin = (char *) sqlite3_column_text (statement, 6);
            product.countryforgin = (countryforgin)? [NSString stringWithUTF8String: countryforgin]: @ "";
            char * image = (char *) sqlite3_column_text (statement, 7);
            product.image = (image)? [NSString stringWithUTF8String: image]: @ "";
            // The above is a string property assignment. For safety, we checked whether it is empty, and for the int data type, we get the assignment directly.
            product.ID = sqlite3_column_int (statement, 0); directly fetch the data and assign a value to the ID attribute of Product
            product.price = sqlite3_column_double (statement, 4);
            product.quantity = sqlite3_column_int (statement, 5);
            [products addObject: product]; // Add the assigned model to the array
        }
        sqlite3_finalize (statement);
    } else {
        NSLog (@ "problem with the database:");
        NSLog (@ "% d", sqlresult);
    }
    return products; // return an array containing Product
}

First, the data has been fully read, the rest is the display interface code, the first is the Master interface controller, first import the custom two classes, because the model and database must be used to access the class. , and then we need a property to hold all the model objects that dbaccess gets to, that is, an array, so the MasterViewController.h code is as follows:

#import <UIKit / UIKit.h>
#import "Product.h" // Import model classes
#import "Dbaccess.h" // Import the data access class
@class DetailViewController;

@interface MasterViewController: UITableViewController

@property (strong, nonatomic) DetailViewController * detailViewController; template comes with properties, save the slave controller
@property (strong, nonatomic) NSMutableArray * products; define array properties to save model objects
@end

Then write MASTERVIEWCONTROLLER.M's content code as follows: Remove some code that is not needed, and remove the default code for the edit and add buttons provided by the template itself, because we just need to show it.

#import "MasterViewController.h"
#import "DetailViewController.h"

@interface MasterViewController ()

@property NSMutableArray * objects;
@end

@implementation MasterViewController

-(void) viewDidLoad {
    [super viewDidLoad];
    // Do any additional setup after loading the view, typically from a nib.
    Dbaccess * dbaccess = [[Dbaccess alloc] init]; // Initialize dba class objects and initialize the database
    self.products = [dbaccess getAllproducts]; call getallproduct method to assign to array
    [dbaccess closeDatabase]; // Close the database
    self.detailViewController = (DetailViewController *) [[self.splitViewController.viewControllers lastObject] topViewController]; // Set the interface
}

-(NSInteger) tableView: (UITableView *) tableView numberOfRowsInSection: (NSInteger) section {
    return [self.products count]; // Let the return value be the length of the array, that is, the number of products
}

-(UITableViewCell *) tableView: (UITableView *) tableView cellForRowAtIndexPath: (NSIndexPath *) indexPath {
    static NSString * cellIdentifire = @ "cell"; //
    UITableViewCell * cell = [tableView dequeueReusableCellWithIdentifier: cellIdentifire];
    if (cell == nil) {
        cell = [[UITableViewCell alloc] initWithStyle: UITableViewCellStyleDefault reuseIdentifier: cellIdentifire];
        Product * product = [self.products objectAtIndex: indexPath.row];
        cell.textLabel.text = product.name; // Set the cell title to product name
    }
    return cell;
}

-(BOOL) tableView: (UITableView *) tableView canEditRowAtIndexPath: (NSIndexPath *) indexPath {
    // Return NO if you do not want the specified item to be editable.
    return NO; // Forbidden to edit Because the cell of the template is editable, we only need to show
}

-(void) tableView: (UITableView *) tableView didSelectRowAtIndexPath: (NSIndexPath *) indexPath {
    self.detailViewController.detailItem = [self.products objectAtIndex: indexPath.row];
// When selecting a cell, assign a value to the detailItem property of detailViewController
[self.navigationController pushViewController: self.detailViewController animated: YES]; // push to slave interface
}
 @end

Before writing the code from the interface, which is the product detail interface, the first thing to do is to set up the UI of the interface, as follows: It is worth noting that the outermost layer has a transparent button, for the page to look good, disable the return button, you can use the transparent button to achieve the effect of the light touch interface is returned

In terms of associating tag attributes from the interface controller, and we need a detailitem to save the selected item in the Master interface, the DetailViewController.h code is as follows:

#import <UIKit / UIKit.h>
#import "Product.h" // Import model classes, because models are needed
@interface DetailViewController: UIViewController
@property (weak, nonatomic) IBOutlet UILabel * namelabel; // Set the text label association, it must be corresponding to the text in the storyboard
@property (weak, nonatomic) IBOutlet UILabel * manufacturelabel;
@property (weak, nonatomic) IBOutlet UILabel * detailslabel;
@property (weak, nonatomic) IBOutlet UILabel * pricelabel;
@property (weak, nonatomic) IBOutlet UILabel * quantitylabel;
@property (weak, nonatomic) IBOutlet UILabel * countrylabel;
@property (strong, nonatomic) id detailItem; // Set the property to save the selected product information
@end

Then write the DETAILVIEWCONTROLLER.M, the code is as follows:

#import "DetailViewController.h"

@interface DetailViewController ()

@end

@implementation DetailViewController

#pragma mark-Managing the detail item

-(void) setDetailItem: (id) newDetailItem {// Override the Detailitemset method, which can refresh the displayed product content at a proper time
    if (_detailItem! = newDetailItem) {
        _detailItem = newDetailItem;
            
        // Update the view.
        [self configureView]; // re-assign the display content
    }
}

-(void) configureView {// specific implementation of text assignment
    Product * theproduct = (Product *) self.detailItem;
    self.namelabel.text = theproduct.name;
    self.manufacturelabel.text = theproduct.manufacture;
    self.detailslabel.text = theproduct.details;
    self.detailslabel.text = theproduct.details;
    self.pricelabel.text = [NSString stringWithFormat: @ "%. 2f", theproduct.price];
    self.quantitylabel.text = [NSString stringWithFormat: @ "% d", theproduct.quantity];
    self.countrylabel.text = theproduct.countryforgin;
}
-(void) viewDidLoad {
    [super viewDidLoad];
    [self configureView]; // Assign value to text
}
-(IBAction) comback: (id) sender {// Monitor button clicks on the overlay interface, trigger to return to the previous interface
    [self.navigationController popViewControllerAnimated: YES]; // Return to the product page
}
@end 

Above, all content completed, the blog code may be slightly inconsistent with the source code, all the data can be obtained at https://github.com/huafushengweirui/data/tree/master/two. Data and knowledge points from Patrick Alessi's iOS database application Advanced Programming Book of course we didn't use some data, piece numbers, etc., which involve customizing the cell to show, this article focuses on how to get data from a database instead of tableview usage. In practice, it may also involve a parameterized query of the data, as well as various aspects of the data cache that will be read. Here's just a quick introduction to iOS and SQLite interaction.

Get data from SQLite to complete a product information show