objective-c 如何将数据插入到 iPhone 中的 SQLite 数据库中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2184861/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert data into a SQLite database in iPhone
提问by Warrior
I am new to iPhone development. I want to insert certain data into my database and retrieve it
and display it in a table. I have created Database data.sqlitewith table 'user'. The table has two values, id(varchar) and name(varchar). I have inserted 3 rows of data in the table via "insert into user value('1','xxxx');" in the terminal window. I have created a navigation based applicaton named "testDb". In AppDelegateI have two methods
我是 iPhone 开发的新手。我想将某些数据插入我的数据库并检索它并将其显示在表格中。我已经创建Database data.sqlite了表“用户”。该表有两个值，id(varchar) 和 name(varchar)。我通过“insert into user value('1','xxxx');”在表中插入了3行数据。在终端窗口中。我创建了一个名为“testDb”的基于导航的应用程序。在AppDelegate我有两种方法
- (void)createEditableCopyOfDatabaseIfNeeded {
NSLog(@"Creating editable copy of database");
// First, test for existence.
BOOL success;
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"data.sqlite"];
success = [fileManager fileExistsAtPath:writableDBPath];
if (success) return;
// The writable database does not exist, so copy the default to the appropriate location.
NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"data.sqlite"];
success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
if (!success) {
NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
}
}
and
和
+(sqlite3 *) getNewDBConnection{
sqlite3 *newDBconnection;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"data.sqlite"];
// Open the database. The database was prepared outside the application.
if (sqlite3_open([path UTF8String], &newDBconnection) == SQLITE_OK) {
NSLog(@"Database Successfully Opened :)");
}
else {
NSLog(@"Error in opening database :(");
}
return newDBconnection;
}
In rootviewcontroller
在根视图控制器中
-(void)initializeTableData{
sqlite3 *db=[DatabaseTestAppDelegate getNewDBConnection];
sqlite3_stmt *statement=nil;
const char *sql="select * from user";
if (sqlite3_prepare_v2(db, sql, -1, &statement, NULL)!=SQLITE_OK)
NSAssert1(0,@"error in preparing staement",sqlite3_errmsg(db));
else {
while(sqlite3_step(statement)==SQLITE_ROW)
[tableData addObject:[NSString stringWithFormat:@"%s",(char*)sqlite3_column_text(statement,1)]];
}
sqlite3_finalize(statement);
}
- (void)viewDidLoad {
[super viewDidLoad];
tableData=[[NSMutableArray alloc]init];
[self addMyObjectIntoDatabase];
[self initializeTableData];
self.title=@"Database test";
}
To display the contents
显示内容
- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath {
static NSString *CellIdentifier = @"Cell";
UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier];
if (cell == nil) {
cell = [[[UITableViewCell alloc] initWithStyle:UITableViewCellStyleDefault reuseIdentifier:CellIdentifier] autorelease];
}
NSLog(@"before displaying data");
cell.textLabel.text=[tableData objectAtIndex:indexPath.row];
NSLog(@"after displaying data");
// Configure the cell.
return cell;
}
This is working fine to display the content of table. But now I want to insert another row into my table and display the inserted content along with previous content. Since I am new to iPhone I learnt from tutorials to read the database and display its contents. How do I proceed further to my task of inserting and displaying again?
这可以很好地显示表格的内容。但是现在我想在我的表格中插入另一行并将插入的内容与以前的内容一起显示。由于我是 iPhone 新手，我从教程中学习了阅读数据库并显示其内容。如何进一步执行插入和再次显示的任务？
采纳答案by Wolfgang Schreurs
If you're really interested in learning how to use SQLite to manage data in an iPhone app, I would recommend you to complete the following tutorial, as it's a very good introduction:
如果你真的有兴趣学习如何使用 SQLite 来管理 iPhone 应用程序中的数据，我建议你完成以下教程，因为它是一个很好的介绍：
iphone-programming-tutorial-creating-a-todo-list-using-sqlite-part-1
iphone-programming-tutorial-creating-a-todo-list-using-sqlite-part-1
The tutorial handles Selecting, Updating, Inserting and Deleting data in an iPhone app, using SQLite.
本教程使用 SQLite 处理在 iPhone 应用程序中选择、更新、插入和删除数据。
回答by pchap10k
You basically have two recommended options (and using the C class is not one of them). FMDB is used by many any developers as a wrapper to Sqlite. http://code.google.com/p/flycode/source/browse/trunk/fmdbFMDB is lightweight and pretty easy to use.
您基本上有两个推荐的选项（使用 C 类不是其中之一）。FMDB 被许多开发人员用作 Sqlite 的包装器。http://code.google.com/p/flycode/source/browse/trunk/fmdbFMDB 是轻量级的并且非常易于使用。
Here is an example call:
这是一个示例调用：
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"mysqlite.db"];
appSettings.dao = [FMDatabase databaseWithPath:path];
FMResultSet *rs = [[appSettings dao] executeUpdate:@"SELECT * FROM mytable"];
while ([rs next]){
// do stuff ...
}
This assumes am ApplicationSettings instance inside your AppDelegate .m file.
这假设您的 AppDelegate .m 文件中是 ApplicationSettings 实例。
ApplicationSettings *appSettings = [ApplicationSettings sharedApplicationSettings];
Inserts are easy too:
插入也很简单：
sql = [NSString stringWithFormat:@"INSERT INTO table (intCol1, strCol2) VALUES ('%d','%@')", myObj.theNumber, myObj.theString];
[appSettings.dao executeUpdate:sql];
The other option is to use Core Data (available as of SDK 3.0). Core Data is meant to be quite fast and optimized, although it does limit you from doing non-standard stuff. Although it can lead to slower SQL queries unless you give due consideration to how you use it.
另一种选择是使用 Core Data（从 SDK 3.0 开始可用）。Core Data 旨在非常快速和优化，尽管它确实限制了你做非标准的事情。尽管它会导致 SQL 查询变慢，除非您适当考虑使用它的方式。
There's a tutorial on the iPhone Dev Center here
iPhone Dev Center 上有一个教程在这里
回答by Luc Wollants
To insert a user with certain values, you need to create an SQL insert statement like. Lets say the User object has a first name and last name then it would look like:
要插入具有特定值的用户，您需要创建一个 SQL 插入语句，例如。假设 User 对象有一个名字和姓氏，那么它看起来像：
NSString *insertStatement = [NSString stringWithFormat:@"INSERT INTO USER (FIRSTNAME, LASTNAME) VALUES (\"%@\", \"%@\")", user.firstName, user.lastName];
char *error;
if ( sqlite3_exec(databaseHandle, [insertStatement UTF8String], NULL, NULL, &error) == SQLITE_OK)
You can check a full example at: http://www.apptite.be/tutorial_ios_sqlite.php
您可以在以下位置查看完整示例：http: //www.apptite.be/tutorial_ios_sqlite.php
回答by Lalit
please check:
请检查：
dbName = @"db.sqlite";
NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDir = [documentPaths objectAtIndex:0];
dbPath = [documentsDir stringByAppendingPathComponent:dbName];
BOOL success;
NSFileManager *fileManager = [NSFileManager defaultManager];
success = [fileManager fileExistsAtPath:dbPath];
if(success) return;
NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:dbName];
[fileManager copyItemAtPath:databasePathFromApp toPath:dbPath error:nil];
and
和
sqlite3 *database ;
[app checkAndCreateDatabase];
// Open the database from the users filessytem
if(sqlite3_open([app.dbPath UTF8String], &database) == SQLITE_OK)
{
sqlite3_stmt *compiledStatement1;
NSString* query;
query=[NSString stringWithFormat:@ " insert into test ( name , company , phone , email , address , country , state , city, zip , online_id , in_time , flag_delete, flag_update,type_id) values (?, ?, ?,?,?, ?, ?, ?, ?,?,?,? ,?,?)"];
const char *compiledStatement = [query UTF8String];
if(sqlite3_prepare_v2(database, compiledStatement, -1, &compiledStatement1, NULL) == SQLITE_OK)
{
sqlite3_bind_text(compiledStatement1, 1, [text UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(compiledStatement1, 2, [text UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(compiledStatement1, 3, [text UTF8String], -1, SQLITE_TRANSIENT);
}
// Loop through the results and add them to the feeds array
if(SQLITE_DONE != sqlite3_step(compiledStatement1))
NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));
sqlite3_finalize(compiledStatement1);
sqlite3_close(database);
}
