1.在项目中添加相关组件
$ ionic cordova plugin add cordova-sqlite-storage
$ npm install --save @ionic-native/sqlite
2.在app.module.ts文件中导入
...
import { SQLite } from '@ionic-native/sqlite';
...
@NgModule({
...
providers: [
...
SQLite,
...
]
...
})
export class AppModule { }
3.使用方法
1.在获取数据
create方法为创建或打开数据库,所有的操作需要在打开数据库之后创建
getData(){
this.sqlite.create({
name: 'ionicdb.db',
location: 'default'
}).then((db: SQLiteObject) => {
db.executeSql('CREATE TABLE IF NOT EXISTS expense(rowid INTEGER PRIMARY KEY, date TEXT, type TEXT, description TEXT, amount INT)', {})
.then(res => console.log('Executed SQL'))
.catch(e => console.log(e));
// 加载显示数据
db.executeSql('SELECT * FROM expense ORDER BY rowid DESC', {})
.then(res => {
this.expenses = [];
for(var i=0; i<res.rows.length; i++) {
this.expenses.push({rowid:res.rows.item(i).rowid,date:res.rows.item(i).date,type:res.rows.item(i).type,description:res.rows.item(i).description,amount:res.rows.item(i).amount})
}
})
.catch(e => console.log(e));
// 获取总和
db.executeSql('SELECT SUM(amount) AS totalIncome FROM expense WHERE type="Income"', {})
.then(res => {
if(res.rows.length>0) {
this.totalIncome = parseInt(res.rows.item(0).totalIncome);
this.balance = this.totalIncome-this.totalExpense;
}
})
.catch(e => console.log(e));
db.executeSql('SELECT SUM(amount) AS totalExpense FROM expense WHERE type="Expense"', {})
.then(res => {
if(res.rows.length>0) {
this.totalExpense = parseInt(res.rows.item(0).totalExpense);
this.balance = this.totalIncome-this.totalExpense;
}
})
}).catch(e => console.log(e));
}
2.在展示的页面中应在页面第一次初始化及每次进入页面时加载
// 页面加载时调用
ionViewDidLoad() {
this.getData();
}
// 页面即将进入时调用
ionViewWillEnter() {
this.getData();
}
3.数据添加
saveData() {
// 保存数据
this.sqlite.create({
name: 'ionicdb.db',
location: 'default'
}).then((db: SQLiteObject) => {
db.executeSql('INSERT INTO expense VALUES(NULL,?,?,?,?)',[this.data.date,this.data.type,this.data.description,this.data.amount])
.then(res => {
console.log(res);
this.toast.show('Data saved', '5000', 'center').subscribe(
toast => {
this.navCtrl.popToRoot();
}
);
})
.catch(e => {
console.log(e);
this.toast.show(e, '5000', 'center').subscribe(
toast => {
console.log(toast);
}
);
});
}).catch(e => {
console.log(e);
this.toast.show(e, '5000', 'center').subscribe(
toast => {
console.log(toast);
}
);
});
}
4.数据更新
// 根据条件获取数据
getCurrentData(rowid) {
this.sqlite.create({
name: 'ionicdb.db',
location: 'default'
}).then((db: SQLiteObject) => {
db.executeSql('SELECT * FROM expense WHERE rowid=?', [rowid])
.then(res => {
if(res.rows.length > 0) {
this.data.rowid = res.rows.item(0).rowid;
this.data.date = res.rows.item(0).date;
this.data.type = res.rows.item(0).type;
this.data.description = res.rows.item(0).description;
this.data.amount = res.rows.item(0).amount;
}
})
.catch(e => {
console.log(e);
this.toast.show(e, '5000', 'center').subscribe(
toast => {
console.log(toast);
}
);
});
}).catch(e => {
console.log(e);
this.toast.show(e, '5000', 'center').subscribe(
toast => {
console.log(toast);
}
);
});
}
// 更新数据
updateData() {
this.sqlite.create({
name: 'ionicdb.db',
location: 'default'
}).then((db: SQLiteObject) => {
db.executeSql('UPDATE expense SET date=?,type=?,description=?,amount=? WHERE rowid=?',[this.data.date,this.data.type,this.data.description,this.data.amount,this.data.rowid])
.then(res => {
console.log(res);
this.toast.show('Data updated', '5000', 'center').subscribe(
toast => {
this.navCtrl.popToRoot();
}
);
})
.catch(e => {
console.log(e);
this.toast.show(e, '5000', 'center').subscribe(
toast => {
console.log(toast);
}
);
});
}).catch(e => {
console.log(e);
this.toast.show(e, '5000', 'center').subscribe(
toast => {
console.log(toast);
}
);
});
}
5.数据删除
// 删除数据
deleteData(rowid) {
this.sqlite.create({
name: ‘ionicdb.db’,
location: ‘default’
}).then((db: SQLiteObject) => {
db.executeSql(‘DELETE FROM expense WHERE rowid=?’, [rowid])
.then(res => {
console.log(res);
this.getData();
})
.catch(e => console.log(e));
}).catch(e => console.log(e));
}