#import <Foundation/Foundation.h>
@interface LanOuStudent : NSObject
//名字
@property (nonatomic,retain)NSString *name;
//年龄
@property (nonatomic,assign)NSInteger age;
//性别
@property (nonatomic,retain)NSString *gender;
//学号
@property (nonatomic,assign)NSInteger number;
//图片的data
@property (nonatomic,retain)NSData *data;
@interface LanOuStudent : NSObject
//名字
@property (nonatomic,retain)NSString *name;
//年龄
@property (nonatomic,assign)NSInteger age;
//性别
@property (nonatomic,retain)NSString *gender;
//学号
@property (nonatomic,assign)NSInteger number;
//图片的data
@property (nonatomic,retain)NSData *data;
@end
-----------------------------------------------------------------------
#import "LanOuStudent.h"
@implementation LanOuStudent
- (void)dealloc{
[_name release];
[_gender release];
[_data release];
[super dealloc];
}
@implementation LanOuStudent
- (void)dealloc{
[_name release];
[_gender release];
[_data release];
[super dealloc];
}
@end
#import <Foundation/Foundation.h>
#import <sqlite3.h>
#import "LanOuStudent.h"
@interface SQLiteManager : NSObject
//使用SQLite数据库步骤
//1.引入libsqlite3.0 框架
//2.在数据库操作类中 引入<sqlite3.h>头文件
//注:咱们使用的方法 都在这个类中
//单例的初始化方法
+ (SQLiteManager *)shareManager;
#import <sqlite3.h>
#import "LanOuStudent.h"
@interface SQLiteManager : NSObject
//使用SQLite数据库步骤
//1.引入libsqlite3.0 框架
//2.在数据库操作类中 引入<sqlite3.h>头文件
//注:咱们使用的方法 都在这个类中
//单例的初始化方法
+ (SQLiteManager *)shareManager;
// 插入数据
- (void)insertTableWithStudent:(LanOuStudent *)student;
//删除数据(根据一个条件删除)
- (void)deleteWithAge:(NSInteger )age;
//更新数据(根据一个条件来更新)
- (void)updateAge:(NSInteger)age byName:(NSString *)name;
//查询数据 (根据条件查询)
- (LanOuStudent *)selectStudentWithName:(NSString *)name age:(NSInteger)age;
// 查询所有(返回一个数组)
- (NSArray *)selectAllStudents;
- (void)insertTableWithStudent:(LanOuStudent *)student;
//删除数据(根据一个条件删除)
- (void)deleteWithAge:(NSInteger )age;
//更新数据(根据一个条件来更新)
- (void)updateAge:(NSInteger)age byName:(NSString *)name;
//查询数据 (根据条件查询)
- (LanOuStudent *)selectStudentWithName:(NSString *)name age:(NSInteger)age;
// 查询所有(返回一个数组)
- (NSArray *)selectAllStudents;
@end
-----------------------------------------------------------------------
#import "SQLiteManager.h"
@implementation SQLiteManager
//单例的初始化方法
+ (SQLiteManager *)shareManager
{
// 静态区指针
static SQLiteManager *manager = nil;
if (manager == nil) {
manager = [[SQLiteManager alloc] init];
//调用创建表的方法
[manager createTable];
}
return manager;
}
//需要在静态区定义一个指针指向数据库 (让这个指针指向对象 从程序开始到结束 一直存在 程序结束后 被系统自动释放)
static sqlite3 *db = nil;
//打开数据库
- (sqlite3 *)openDB
{
// 判断数据库是否存在 如果存在 直接返回
if (db != nil) {
return db;
}else
{
// 不存在 则创建一个并打开
//获取当前documents的路径
NSString *documents = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
//拼接数据库文件路径(拼接的数据库文件的名字)
NSString *dbPath = [documents stringByAppendingPathComponent:@"Student.sqlite"];
NSLog(@"%@",dbPath);
//创建或者打开数据库
//参数1 文件的路径(需要把oc的字符串 转化成c语言的) dbPath.UTF8String把oc的转化成c语言
//参数2 数据库的地址
//接收一下返回值 判断是否打开成功
int result = sqlite3_open(dbPath.UTF8String, &db);
//SQLITE_OK 可以查表得到错误
if (result == SQLITE_OK) {
NSLog(@"打开成功");
}else
{
NSLog(@"打开失败");
}
return db;
}
}
//关闭数据库
- (void)closeDB
{
int result = sqlite3_close(db);
//判断是否关闭成功
if (result == SQLITE_OK) {
NSLog(@"关闭成功");
//把数据库的指针 重置为空
db = nil;
}else
{
NSLog(@"关闭失败");
}
}
//创建一张表
- (void)createTable
{
//注意:一定在操作时 先打开数据库
//操作完毕 关闭数据库
//打开数据库
db = [self openDB];
//1.写SQL语句
NSString *sql = @"create table IF NOT EXISTS lanOuStudent(number integer primary key not null,name text not null,gender text not null,age integer not null,data blob not null)";
//2.执行sql语句
//准备与执行语句的操作
int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
if (result == SQLITE_OK) {
NSLog(@"建表成功");
}else
{
NSLog(@"建表失败");
}
//关闭数据库
[self closeDB];
}
// 插入数据
- (void)insertTableWithStudent:(LanOuStudent *)student
{
//打开数据库
db = [self openDB];
//写SQL语句
NSString *sql = @"insert into LanOuStudent (number,name,gender,age,data) values (?,?,?,?,?)";
//创建一个跟随指针(指令集)
sqlite3_stmt *stmt = nil;
//执行SQL语句
//预执行函数 需要用跟随指针 绑定问号 然后一步一步执行(可以判断SQL是否正确)
int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
if (result == SQLITE_OK) {
// 绑定问号(根据SQL语句 来决定绑定的顺序)
// 注意:绑定的顺序 从1开始
// 参数2 绑定的顺序
// 参数3 要插入的值
sqlite3_bind_int(stmt, 1, (int)student.number);
sqlite3_bind_text(stmt, 2, student.name.UTF8String, -1, NULL);
sqlite3_bind_text(stmt, 3, student.gender.UTF8String, -1, NULL);
sqlite3_bind_int(stmt, 4, (int)student.age);
// 绑定二进制数据
// [student.data bytes] 相当于获取到data数据的内容
// (int)[student.data length] 相当于获取到data数据的长度
sqlite3_bind_blob(stmt, 5, [student.data bytes], (int)[student.data length], NULL);
//执行绑定的语句
sqlite3_step(stmt);
NSLog(@"插入成功");
}else
{
NSLog(@"插入失败");
}
//不管插入是否成功 都要释放跟随指针
sqlite3_finalize(stmt);
//关闭数据库
[self closeDB];
}
/*
//如果有data数据进行插入的话
//sqlite3_exec 这个函数 会把data数据转化成字符串存入数据库
//如果插入的数据没有data数据的话 直接用sqlite3_exec 函数 插入数据就行 不用绑定了
NSString *sql = @"insert into LanOuStudent (number,name,gender,age) values ('%ld','%@','%@','%ld')"
- (void)text:(LanOuStudent *)student
{
NSString *sql = @"insert into LanOuStudent (number,name,gender,age) values ('%ld','%@','%@','%ld')";
NSString *sqlNew = [NSString stringWithFormat:@"insert into LanOuStudent (number,name,gender,age) values ('%ld','%@','%@','%ld')",student.number,student.name,student.gender,student.age];
}
*/
//删除数据(根据一个条件删除)
- (void)deleteWithAge:(NSInteger )age
{
#pragma mark -- 不绑定删除 需要拼接SQL语句
//打开数据库
// [self openDB];
// //写SQL语句
// NSString *sql = [NSString stringWithFormat:@"delete from lanOuStudent where age > '%ld'",age];
// //SQL语句
// int result = sqlite3_exec(db, sql.UTF8String , NULL, NULL, NULL);
// //判断一下执行结果
// if (result == SQLITE_OK) {
// NSLog(@"删除成功");
// }else
// {
// NSLog(@"删除失败");
// }
// //关闭数据库
// [self closeDB];
#pragma mark -- 通过绑定删除方法
//打开数据库
[self openDB];
//写SQL语句
NSString *sql = @"delete from lanouStudent where age > ?";
//创建跟随指针
sqlite3_stmt *stmt = nil;
//预执行语句
int result = sqlite3_prepare_v2(db, sql.UTF8String , -1, &stmt, NULL);
if (result == SQLITE_OK) {
//绑定问好
sqlite3_bind_int(stmt, 1, (int)age);
//执行跟随执行 绑定的语句
sqlite3_step(stmt);
NSLog(@"删除成功");
}else
{
NSLog(@"删除失败");
}
//不管成功与否 都要释放跟随指针
sqlite3_finalize(stmt);
//关闭数据库
[self closeDB];
}
//更新数据(根据一个条件来更新)
//update lanOuStudent set age = '%ld' where name = '%@'
- (void)updateAge:(NSInteger)age byName:(NSString *)name
{
//打开数据库
[self openDB];
//写SQL语句
NSString *sql = [NSString stringWithFormat:@"update lanouStudent set age = '%ld' where name = '%@'",age,name];
//执行语句
int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
if (result == SQLITE_OK) {
NSLog(@"更新成功");
}else
{
NSLog(@"更新失败");
}
//关闭数据库
[self closeDB];
}
//查询数据 (根据条件查询)
//select name,imageData from lanOuStudent where name = ? and age = ?
- (LanOuStudent *)selectStudentWithName:(NSString *)name age:(NSInteger)age
{
//打开数据库
[self openDB];
//写SQL语句
NSString *sql = @"select * from lanouStudent where name = ? and age = ?";
//创建跟随指针
sqlite3_stmt *stmt = nil;
//创建一个对象
LanOuStudent *student = [[LanOuStudent alloc] init];
//预执行语句
int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
if (result == SQLITE_OK) {
//绑定问号
sqlite3_bind_text(stmt, 1, name.UTF8String, -1, NULL);
sqlite3_bind_int(stmt, 2, (int)age);
// 查询时 如果下一行准备好了 返回 SQLITE_ROW 这时可以继续查询
while (sqlite3_step(stmt) == SQLITE_ROW) {
//读取数据
// <#int iCol#> 列数
//如果你查询所有的字段的话 那么这个列数 就根据你创建表的顺序一样
// 注意: 从0开始
//如果你查询的是特定字段 那么这个列数 就要根据你的SQL语句来写
//也是从0开
int number = sqlite3_column_int(stmt, 0);
char *name = (char *)sqlite3_column_text(stmt, 1);
char *gender = (char *)sqlite3_column_text(stmt, 2);
int age = sqlite3_column_int(stmt, 3);
//读取二进制数据
NSData *data = [NSData dataWithBytes:sqlite3_column_blob(stmt, 4) length:sqlite3_column_bytes(stmt, 4)];
//赋值model
//[NSString stringWithUTF8String:<#(nonnull const char *)#>]; 把c语言字符串转化成oc字符串
student.name = [NSString stringWithUTF8String:name];
student.gender = [NSString stringWithUTF8String:gender];
student.number = number;
student.age = age;
student.data = data;
}
NSLog(@"成功");
}else
{
NSLog(@"失败");
}
//释放跟随指针
sqlite3_finalize(stmt);
//关闭数据库
[self closeDB];
//自动释放 对象
return [student autorelease];
}
// 查询所有(返回一个数组)
// select * from lanOuStudent
- (NSArray *)selectAllStudents
{
//打开数据库
[self openDB];
//写SQL语句
NSString *sql = @"select * from lanouStudent";
//创建一个可变数组备用
NSMutableArray *array = [NSMutableArray array];
//创建跟随指针
sqlite3_stmt *stmt = nil;
//预执行语句
int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
if (result == SQLITE_OK) {
while (sqlite3_step(stmt) == SQLITE_ROW) {
LanOuStudent *student = [[LanOuStudent alloc] init];
//读取数据
int number = sqlite3_column_int(stmt, 0);
char *name = (char *)sqlite3_column_text(stmt, 1);
char *gender = (char *)sqlite3_column_text(stmt, 2);
int age = sqlite3_column_int(stmt, 3);
NSData *data = [NSData dataWithBytes:sqlite3_column_blob(stmt, 4) length:sqlite3_column_bytes(stmt, 4)];
//赋值model
student.number = number;
student.name = [NSString stringWithUTF8String:name];
student.gender = [NSString stringWithUTF8String:gender];
student.age = age;
student.data = data;
//添加到数组
[array addObject:student];
@implementation SQLiteManager
//单例的初始化方法
+ (SQLiteManager *)shareManager
{
// 静态区指针
static SQLiteManager *manager = nil;
if (manager == nil) {
manager = [[SQLiteManager alloc] init];
//调用创建表的方法
[manager createTable];
}
return manager;
}
//需要在静态区定义一个指针指向数据库 (让这个指针指向对象 从程序开始到结束 一直存在 程序结束后 被系统自动释放)
static sqlite3 *db = nil;
//打开数据库
- (sqlite3 *)openDB
{
// 判断数据库是否存在 如果存在 直接返回
if (db != nil) {
return db;
}else
{
// 不存在 则创建一个并打开
//获取当前documents的路径
NSString *documents = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
//拼接数据库文件路径(拼接的数据库文件的名字)
NSString *dbPath = [documents stringByAppendingPathComponent:@"Student.sqlite"];
NSLog(@"%@",dbPath);
//创建或者打开数据库
//参数1 文件的路径(需要把oc的字符串 转化成c语言的) dbPath.UTF8String把oc的转化成c语言
//参数2 数据库的地址
//接收一下返回值 判断是否打开成功
int result = sqlite3_open(dbPath.UTF8String, &db);
//SQLITE_OK 可以查表得到错误
if (result == SQLITE_OK) {
NSLog(@"打开成功");
}else
{
NSLog(@"打开失败");
}
return db;
}
}
//关闭数据库
- (void)closeDB
{
int result = sqlite3_close(db);
//判断是否关闭成功
if (result == SQLITE_OK) {
NSLog(@"关闭成功");
//把数据库的指针 重置为空
db = nil;
}else
{
NSLog(@"关闭失败");
}
}
//创建一张表
- (void)createTable
{
//注意:一定在操作时 先打开数据库
//操作完毕 关闭数据库
//打开数据库
db = [self openDB];
//1.写SQL语句
NSString *sql = @"create table IF NOT EXISTS lanOuStudent(number integer primary key not null,name text not null,gender text not null,age integer not null,data blob not null)";
//2.执行sql语句
//准备与执行语句的操作
int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
if (result == SQLITE_OK) {
NSLog(@"建表成功");
}else
{
NSLog(@"建表失败");
}
//关闭数据库
[self closeDB];
}
// 插入数据
- (void)insertTableWithStudent:(LanOuStudent *)student
{
//打开数据库
db = [self openDB];
//写SQL语句
NSString *sql = @"insert into LanOuStudent (number,name,gender,age,data) values (?,?,?,?,?)";
//创建一个跟随指针(指令集)
sqlite3_stmt *stmt = nil;
//执行SQL语句
//预执行函数 需要用跟随指针 绑定问号 然后一步一步执行(可以判断SQL是否正确)
int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
if (result == SQLITE_OK) {
// 绑定问号(根据SQL语句 来决定绑定的顺序)
// 注意:绑定的顺序 从1开始
// 参数2 绑定的顺序
// 参数3 要插入的值
sqlite3_bind_int(stmt, 1, (int)student.number);
sqlite3_bind_text(stmt, 2, student.name.UTF8String, -1, NULL);
sqlite3_bind_text(stmt, 3, student.gender.UTF8String, -1, NULL);
sqlite3_bind_int(stmt, 4, (int)student.age);
// 绑定二进制数据
// [student.data bytes] 相当于获取到data数据的内容
// (int)[student.data length] 相当于获取到data数据的长度
sqlite3_bind_blob(stmt, 5, [student.data bytes], (int)[student.data length], NULL);
//执行绑定的语句
sqlite3_step(stmt);
NSLog(@"插入成功");
}else
{
NSLog(@"插入失败");
}
//不管插入是否成功 都要释放跟随指针
sqlite3_finalize(stmt);
//关闭数据库
[self closeDB];
}
/*
//如果有data数据进行插入的话
//sqlite3_exec 这个函数 会把data数据转化成字符串存入数据库
//如果插入的数据没有data数据的话 直接用sqlite3_exec 函数 插入数据就行 不用绑定了
NSString *sql = @"insert into LanOuStudent (number,name,gender,age) values ('%ld','%@','%@','%ld')"
- (void)text:(LanOuStudent *)student
{
NSString *sql = @"insert into LanOuStudent (number,name,gender,age) values ('%ld','%@','%@','%ld')";
NSString *sqlNew = [NSString stringWithFormat:@"insert into LanOuStudent (number,name,gender,age) values ('%ld','%@','%@','%ld')",student.number,student.name,student.gender,student.age];
}
*/
//删除数据(根据一个条件删除)
- (void)deleteWithAge:(NSInteger )age
{
#pragma mark -- 不绑定删除 需要拼接SQL语句
//打开数据库
// [self openDB];
// //写SQL语句
// NSString *sql = [NSString stringWithFormat:@"delete from lanOuStudent where age > '%ld'",age];
// //SQL语句
// int result = sqlite3_exec(db, sql.UTF8String , NULL, NULL, NULL);
// //判断一下执行结果
// if (result == SQLITE_OK) {
// NSLog(@"删除成功");
// }else
// {
// NSLog(@"删除失败");
// }
// //关闭数据库
// [self closeDB];
#pragma mark -- 通过绑定删除方法
//打开数据库
[self openDB];
//写SQL语句
NSString *sql = @"delete from lanouStudent where age > ?";
//创建跟随指针
sqlite3_stmt *stmt = nil;
//预执行语句
int result = sqlite3_prepare_v2(db, sql.UTF8String , -1, &stmt, NULL);
if (result == SQLITE_OK) {
//绑定问好
sqlite3_bind_int(stmt, 1, (int)age);
//执行跟随执行 绑定的语句
sqlite3_step(stmt);
NSLog(@"删除成功");
}else
{
NSLog(@"删除失败");
}
//不管成功与否 都要释放跟随指针
sqlite3_finalize(stmt);
//关闭数据库
[self closeDB];
}
//更新数据(根据一个条件来更新)
//update lanOuStudent set age = '%ld' where name = '%@'
- (void)updateAge:(NSInteger)age byName:(NSString *)name
{
//打开数据库
[self openDB];
//写SQL语句
NSString *sql = [NSString stringWithFormat:@"update lanouStudent set age = '%ld' where name = '%@'",age,name];
//执行语句
int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
if (result == SQLITE_OK) {
NSLog(@"更新成功");
}else
{
NSLog(@"更新失败");
}
//关闭数据库
[self closeDB];
}
//查询数据 (根据条件查询)
//select name,imageData from lanOuStudent where name = ? and age = ?
- (LanOuStudent *)selectStudentWithName:(NSString *)name age:(NSInteger)age
{
//打开数据库
[self openDB];
//写SQL语句
NSString *sql = @"select * from lanouStudent where name = ? and age = ?";
//创建跟随指针
sqlite3_stmt *stmt = nil;
//创建一个对象
LanOuStudent *student = [[LanOuStudent alloc] init];
//预执行语句
int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
if (result == SQLITE_OK) {
//绑定问号
sqlite3_bind_text(stmt, 1, name.UTF8String, -1, NULL);
sqlite3_bind_int(stmt, 2, (int)age);
// 查询时 如果下一行准备好了 返回 SQLITE_ROW 这时可以继续查询
while (sqlite3_step(stmt) == SQLITE_ROW) {
//读取数据
// <#int iCol#> 列数
//如果你查询所有的字段的话 那么这个列数 就根据你创建表的顺序一样
// 注意: 从0开始
//如果你查询的是特定字段 那么这个列数 就要根据你的SQL语句来写
//也是从0开
int number = sqlite3_column_int(stmt, 0);
char *name = (char *)sqlite3_column_text(stmt, 1);
char *gender = (char *)sqlite3_column_text(stmt, 2);
int age = sqlite3_column_int(stmt, 3);
//读取二进制数据
NSData *data = [NSData dataWithBytes:sqlite3_column_blob(stmt, 4) length:sqlite3_column_bytes(stmt, 4)];
//赋值model
//[NSString stringWithUTF8String:<#(nonnull const char *)#>]; 把c语言字符串转化成oc字符串
student.name = [NSString stringWithUTF8String:name];
student.gender = [NSString stringWithUTF8String:gender];
student.number = number;
student.age = age;
student.data = data;
}
NSLog(@"成功");
}else
{
NSLog(@"失败");
}
//释放跟随指针
sqlite3_finalize(stmt);
//关闭数据库
[self closeDB];
//自动释放 对象
return [student autorelease];
}
// 查询所有(返回一个数组)
// select * from lanOuStudent
- (NSArray *)selectAllStudents
{
//打开数据库
[self openDB];
//写SQL语句
NSString *sql = @"select * from lanouStudent";
//创建一个可变数组备用
NSMutableArray *array = [NSMutableArray array];
//创建跟随指针
sqlite3_stmt *stmt = nil;
//预执行语句
int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
if (result == SQLITE_OK) {
while (sqlite3_step(stmt) == SQLITE_ROW) {
LanOuStudent *student = [[LanOuStudent alloc] init];
//读取数据
int number = sqlite3_column_int(stmt, 0);
char *name = (char *)sqlite3_column_text(stmt, 1);
char *gender = (char *)sqlite3_column_text(stmt, 2);
int age = sqlite3_column_int(stmt, 3);
NSData *data = [NSData dataWithBytes:sqlite3_column_blob(stmt, 4) length:sqlite3_column_bytes(stmt, 4)];
//赋值model
student.number = number;
student.name = [NSString stringWithUTF8String:name];
student.gender = [NSString stringWithUTF8String:gender];
student.age = age;
student.data = data;
//添加到数组
[array addObject:student];
[student release];
}
NSLog(@"成功");
}else
{
NSLog(@"失败");
}
//释放跟随对象
sqlite3_finalize(stmt);
//关闭数据库
[self closeDB];
return array;
}
NSLog(@"成功");
}else
{
NSLog(@"失败");
}
//释放跟随对象
sqlite3_finalize(stmt);
//关闭数据库
[self closeDB];
return array;
}
@end
-----------------------------------------------------------------------
- (void)viewDidLoad {
[super viewDidLoad];
// Do any additional setup after loading the view.
SQLiteManager *manager = [SQLiteManager shareManager];
//插入数据
LanOuStudent *student = [[LanOuStudent alloc] init];
student.name = @"王龙";
student.gender = @"男";
student.age = 11;
student.number = 1;
student.data = UIImagePNGRepresentation([UIImage imageNamed:@"21.jpg"]);
//插入数据
[manager insertTableWithStudent:student];
//删除数据
//[manager deleteWithAge:15];
//更新数据
[manager updateAge:18 byName:@"王龙"];
//按要求查询
//LanOuStudent *stu = [manager selectStudentWithName:@"王龙" age:18];
//查询你所有
NSArray *array = [manager selectAllStudents];
// Do any additional setup after loading the view.
SQLiteManager *manager = [SQLiteManager shareManager];
//插入数据
LanOuStudent *student = [[LanOuStudent alloc] init];
student.name = @"王龙";
student.gender = @"男";
student.age = 11;
student.number = 1;
student.data = UIImagePNGRepresentation([UIImage imageNamed:@"21.jpg"]);
//插入数据
[manager insertTableWithStudent:student];
//删除数据
//[manager deleteWithAge:15];
//更新数据
[manager updateAge:18 byName:@"王龙"];
//按要求查询
//LanOuStudent *stu = [manager selectStudentWithName:@"王龙" age:18];
//查询你所有
NSArray *array = [manager selectAllStudents];
}