· 8 years ago · Feb 14, 2018, 07:56 AM
1 ---------------------
2 Sqlitehelper.js
3 ---------------------
4 import React, { Component } from 'react';
5 import {
6 View,
7 StyleSheet,AsyncStorage,
8 } from 'react-native';
9
10 var TAG = "SqliteHelper : ";
11 let SQLiteStorage = require('react-native-sqlite-storage')
12 var db = null;
13
14 export default class SqliteHelper extends Component
15 {
16 /**
17 * This method called once when login or need to create database
18 */
19 static initialize() {
20 console.log(TAG + "------initialize------");
21 db = SQLiteStorage.openDatabase("DemoDB", '1.0', '', 1);
22 _this = this;
23 SqliteHelper.createAllTable();
24 }
25
26 static createAllTable() {
27 console.log(TAG + "------createAllTable-------");
28 _this = this;
29 db.transaction(function (txn) {
30 txn.executeSql('CREATE TABLE IF NOT EXISTS Table1 ( ' +
31 'ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,' +
32 'ITEMNO INTEGER NOT NULL,' +
33 'ITEMNAME VARCHAR NOT NULL )', []);
34 });
35 console.log(TAG + "Item table created/exist");
36 }
37
38 //Default mathods
39 errorCB(err) {
40 console.log(TAG + "SQL Error: " + err);
41 }
42
43 successCB() {
44 console.log(TAG + "SQL executed fine");
45 }
46
47 openCB() {
48 console.log(TAG + "Database OPENED");
49 }
50 //-------
51
52 /**
53 * Drop all tables here , used in logout or flush data
54 */
55 static logoutFlush() {
56 SqliteHelper.dropTable();
57 }
58
59
60 static dropTable() {
61 console.log(TAG + "------dropTable-------");
62 try {
63 //drop table
64 db.transaction((tx) => {
65 tx.executeSql('DROP TABLE IF EXISTS Table1 ', [], (tx, results) => {
66 console.log("table droped");
67 });
68 });
69 } catch (ex) {
70 console.log(JSON.stringify(ex));
71 }
72 }
73
74 }
75
76--------------------
77 App.js
78--------------------
79 import React, { Component } from 'react';
80 import {
81 Platform,
82 StyleSheet,
83 Text,
84 View,TouchableOpacity, TextInput,ListView,ActivityIndicator
85 } from 'react-native';
86
87
88 import Sqlite from './AppComponent/SqliteHelper';
89 let SQLiteStorage = require('react-native-sqlite-storage')
90
91
92 var TAG = "App : ";
93 var records = [];
94 var _this = null;
95 var db = null;
96
97 export default class App extends Component<{}> {
98
99 constructor() {
100 super();
101 const ds = new ListView.DataSource({rowHasChanged: (r1, r2) => r1 !== r2});
102 this.state = {
103 dataSource: ds.cloneWithRows([]),
104 selected :0,
105 itemNo:'',
106 itemName:'',
107 records :[],
108 loading:false,
109 };
110 _this = this;
111 }
112
113 componentWillMount(){
114 console.log(TAG + "-----componentWillMount-----")
115 Sqlite.initialize();
116 }
117
118 /**
119 * List item row UI
120 * @param {*} rowData
121 */
122 listRowItems(rowData) {
123 console.log(TAG + "-----listRowItems------")
124 if (rowData != null) {
125 return <View style={styles.listItemStyle}>
126 <Text>ItemNo:{rowData.ITEMNO}</Text>
127 <Text>ItemName:{rowData.ITEMNAME}</Text>
128 <View style={{marginTop:5,marginBottom:5,backgroundColor:'#000',height:1}}/>
129 </View>
130 } else {
131 console.log(TAG + "rowdata null")
132 }
133 }
134
135 /**
136 * UI modification while selecting diff options
137 * @param {*} value
138 */
139 changeSelection(value){
140 this.setState({selected:value});
141 }
142
143 /**
144 * When click on google
145 */
146 goClick(){
147 console.log(TAG + "------goClick----")
148 switch(this.state.selected){
149 case 0:
150 this.SearchItemWithInsert(this.state.itemNo,this.state.itemName);
151 break;
152 case 1:
153 this.SearchWithUpdate(this.state.itemNo,this.state.itemName)
154 break;
155 case 2:
156 this.SearchWithDelete(this.state.itemNo)
157 break;
158 case 3:
159 this.searchRecord(this.state.itemNo)
160 break;
161 case 4:
162 this.deleteAllRecords();
163 break;
164 case 5:
165 this.getAllItems();
166 break;
167
168 }
169 }
170
171 /**
172 * update record
173 * @param {*} ITEMNO
174 * @param {*} ITEMNAME
175 */
176 updateItemName(ITEMNO, ITEMNAME) {
177 console.log(TAG + "-----updateItemName------");
178
179 _this.startLoading();
180 var query = "UPDATE Table1 set ITEMNAME='" + ITEMNAME + "' where ITEMNO =" + ITEMNO;
181 console.log(TAG + "query : " + query);
182 db = SQLiteStorage.openDatabase("DemoDB", '1.0', '', 1);
183 try {
184 db.transaction((tx) => {
185 tx.executeSql(query, [], (tx, results) => {
186 console.log(TAG + "Item updated...");
187 _this.getAllItems();
188 }, function (error) {
189 _this.stopLoading()
190 console.log(TAG + "Item update error: " + error.message);
191 });
192 });
193 } catch (ex) {
194 console.log(TAG + "error in updateITEMNAME " + JSON.stringify(ex));
195 }
196
197 }
198
199 /**
200 * before delete search record, if found then delete record
201 * @param {*} ITEMNO
202 */
203 SearchWithDelete(ITEMNO) {
204 console.log(TAG + "-----SearchWithDelete------");
205 if (ITEMNO.length > 0) {
206 _this.startLoading();
207 db = SQLiteStorage.openDatabase("DemoDB", '1.0', '', 1);
208 db.transaction((tx) => {
209 tx.executeSql('SELECT * FROM Table1 where ITEMNO=' + ITEMNO, [], (tx, results) => {
210 console.log(TAG + "results :" + JSON.stringify(results))
211 var len = results.rows.length;
212 console.log(TAG + "Items found : " + len);
213 if (len > 0) {
214 _this.DeletesItem(ITEMNO);
215 } else {
216 _this.stopLoading()
217 alert('Not found')
218 }
219 }, function (error) {
220 _this.stopLoading()
221 console.log(TAG + "Item delete error: " + error.message);
222 });
223 });
224 } else {
225 _this.stopLoading()
226 alert('please enter item no')
227 }
228 }
229
230 /**
231 * delete record
232 * @param {*} ITEMNO
233 */
234 DeletesItem(ITEMNO) {
235 console.log(TAG + "-----DeletesItem------");
236 db = SQLiteStorage.openDatabase("DemoDB", '1.0', '', 1);
237 db.transaction((txn) => {
238 txn.executeSql('DELETE FROM Table1 where ITEMNO =' + ITEMNO, [], (txn, resultsN) => {
239 console.log(TAG + "deleted 1 Item");
240 _this.getAllItems()
241 }, function (error) {
242 _this.stopLoading()
243 console.log(TAG + "Item delete error: " + error.message);
244 });
245 });
246 }
247
248 /**
249 * search record, if found update it
250 * @param {*} ITEMNO
251 * @param {*} ITEMNAME
252 */
253 SearchWithUpdate(ITEMNO, ITEMNAME) {
254 console.log(TAG + "-----SearchWithUpdate------");
255 if (ITEMNO.length > 0) {
256 if (ITEMNAME.length > 0) {
257 _this.startLoading();
258 db = SQLiteStorage.openDatabase("DemoDB", '1.0', '', 1);
259 db.transaction((tx) => {
260 tx.executeSql('SELECT * FROM Table1 where ITEMNO=' + ITEMNO, [], (tx, results) => {
261 console.log(TAG + "results :" + JSON.stringify(results))
262 var len = results.rows.length;
263 console.log(TAG + "Items found : " + len);
264 if (len > 0) {
265 _this.updateItemName(ITEMNO, ITEMNAME);
266 } else {
267 _this.stopLoading()
268 alert('Not found')
269 }
270 });
271 });
272 } else {
273 _this.stopLoading()
274 alert('please enter item name')
275 }
276 } else {
277 _this.stopLoading()
278 alert('please enter item no')
279 }
280 }
281
282 /**
283 * search record, if not found then insert it
284 * @param {*} ITEMNO
285 * @param {*} ITEMNAME
286 */
287 SearchItemWithInsert(ITEMNO, ITEMNAME) {
288 console.log(TAG + "-----SearchItem------");
289 if (ITEMNO.length > 0) {
290 if (ITEMNAME.length > 0) {
291 _this.startLoading();
292 db = SQLiteStorage.openDatabase("DemoDB", '1.0', '', 1);
293 db.transaction((tx) => {
294 tx.executeSql('SELECT * FROM Table1 where ITEMNO=' + ITEMNO, [], (tx, results) => {
295 console.log(TAG + "results :" + JSON.stringify(results))
296 var len = results.rows.length;
297 console.log(TAG + "Items found : " + len);
298 if (len > 0) {
299 _this.stopLoading()
300 alert('already available')
301 } else {
302 _this.insertIntoItemTable(ITEMNO, ITEMNAME);
303 }
304 }, function (error) {
305 _this.stopLoading()
306 console.log(TAG + "Item insert error: " + error.message);
307 });
308 });
309 } else {
310 _this.stopLoading()
311 alert('please enter item name')
312 }
313 } else {
314 _this.stopLoading()
315 alert('please enter item no')
316 }
317 }
318
319 /**
320 * Insert function
321 * @param {*} ITEMNO
322 * @param {*} ITEMNAME
323 */
324 insertIntoItemTable(ITEMNO, ITEMNAME) {
325 console.log(TAG + "-------insertIntoItemTable---------")
326
327 try {
328 var query = 'INSERT INTO Table1 ( ITEMNO,ITEMNAME ) VALUES (?,?)';
329 db = SQLiteStorage.openDatabase("DemoDB", '1.0', '', 1);
330 db.transaction((tx) => {
331 tx.executeSql(query,
332 [ITEMNO, ITEMNAME],
333 (tx, results) => {
334 console.log(TAG + "Item : " + ITEMNAME + ' inserted......');
335 _this.getAllItems();
336 }, function (error) {
337 console.log(TAG + "Item : " + ITEMNAME + ' insertion error: ' + error.message);
338 });
339 });
340 } catch (ex) {
341 console.log(TAG + "Exception: " + ex);
342 }
343
344 }
345
346 startLoading(){
347 console.log(TAG + '------startLoading-----')
348 this.setState({loading:true})
349 }
350
351
352 stopLoading(){
353 console.log(TAG + '------stopLoading-----')
354 this.setState({loading:false})
355 }
356
357 /**
358 * search record
359 * @param {*} ITEMNO
360 */
361 searchRecord(ITEMNO) {
362 console.log(TAG + '-----searchRecord-----');
363 if (ITEMNO.length > 0) {
364 this.startLoading();
365 db = SQLiteStorage.openDatabase("DemoDB", '1.0', '', 1);
366 db.transaction((tx) => {
367 tx.executeSql('SELECT * FROM Table1 where ITEMNO=' + ITEMNO, [], (tx, results) => {
368 console.log(TAG + "Query completed");
369 // Get rows with Web SQL Database spec compliance.
370 var len = results.rows.length;
371 console.log(TAG + 'len::::::' + len);
372 var aryData = [];
373 for (let i = 0; i < len; i++) {
374 let row = results.rows.item(i);
375 console.log(TAG + `Record:::::::: ${row.ITEMNO} ${row.ITEMNAME}`);
376 aryData.push({ ITEMNO: row.ITEMNO, ITEMNAME: row.ITEMNAME });
377 }
378 console.log(TAG + 'arydata :: ' + JSON.stringify(aryData));
379 if (aryData.length == 0) {
380 _this.stopLoading()
381 alert('no record found')
382 } else {
383 _this.populateList(aryData);
384 }
385 });
386 });
387 } else {
388 alert('enter item no')
389 }
390 }
391
392 /**
393 * load all items/records from database
394 */
395 getAllItems(){
396 console.log(TAG + '-----getAllItems-----');
397 this.startLoading();
398 db = SQLiteStorage.openDatabase("DemoDB", '1.0', '', 1);
399 db.transaction((tx) => {
400 tx.executeSql('SELECT * FROM Table1', [], (tx, results) => {
401 console.log(TAG + "Query completed");
402 // Get rows with Web SQL Database spec compliance.
403 var len = results.rows.length;
404 console.log(TAG + 'len::::::' + len);
405 var aryData = [];
406 for (let i = 0; i < len; i++)
407 {
408 let row = results.rows.item(i);
409 console.log(TAG + `Record:::::::: ${row.ITEMNO} ${row.ITEMNAME}`);
410 aryData.push({ITEMNO:row.ITEMNO,ITEMNAME:row.ITEMNAME});
411 }
412 console.log(TAG + 'arydata :: ' + JSON.stringify(aryData));
413 if (aryData.length == 0) {
414 _this.stopLoading()
415 alert('no record found')
416 } else {
417 _this.populateList(aryData);
418 }
419 });
420 });
421 }
422
423 /**
424 * attach all data fetched from database to listview
425 * @param {*} aryData
426 */
427 populateList(aryData){
428 var ds = new ListView.DataSource({rowHasChanged: (r1, r2) => r1 !== r2});
429 var dataSourceTemp = ds.cloneWithRows(aryData);
430 this.setState({records:aryData,dataSource:dataSourceTemp});
431 _this.stopLoading()
432
433 }
434
435 /**
436 * delete all records
437 */
438 deleteAllRecords(){
439 console.log(TAG + "-----deleteAllRecords------");
440 db = SQLiteStorage.openDatabase("DemoDB", '1.0', '', 1);
441 db.transaction((txn) => {
442 txn.executeSql('DELETE FROM Table1', [], (txn, resultsN) => {
443 console.log(TAG + "deleted 1 Item");
444 this.getAllItems();
445 });
446 });
447 }
448
449 render() {
450 return (
451
452 (this.state.loading)?<View style={styles.containerholder}>
453 <View style={styles.containerloading}>
454 <ActivityIndicator color='grey' size="large" color="#0000ff" />
455 </View>
456 </View>
457 :
458 <View style={styles.container}>
459 <View style={styles.buttonContainer}>
460 <TouchableOpacity onPress={()=>this.changeSelection(0)} style={(this.state.selected == 0)?styles.buttonStyleSelected:styles.buttonStyle}>
461 <Text style={(this.state.selected == 0)?styles.buttonTextSelected:styles.buttonText}>Insert single</Text>
462 </TouchableOpacity>
463
464 <TouchableOpacity onPress={()=>this.changeSelection(1)} style={(this.state.selected == 1)?styles.buttonStyleSelected:styles.buttonStyle}>
465 <Text style={(this.state.selected == 1)?styles.buttonTextSelected:styles.buttonText}>Update single</Text>
466 </TouchableOpacity>
467
468 <TouchableOpacity onPress={()=>this.changeSelection(2)} style={(this.state.selected == 2)?styles.buttonStyleSelected:styles.buttonStyle}>
469 <Text style={(this.state.selected == 2)?styles.buttonTextSelected:styles.buttonText}>Delete single</Text>
470 </TouchableOpacity>
471
472 <TouchableOpacity onPress={()=>this.changeSelection(3)} style={(this.state.selected == 3)?styles.buttonStyleSelected:styles.buttonStyle}>
473 <Text style={(this.state.selected == 3)?styles.buttonTextSelected:styles.buttonText}>search</Text>
474 </TouchableOpacity>
475 </View>
476
477 <View style={styles.buttonContainer}>
478 <TouchableOpacity onPress={()=>this.changeSelection(4)} style={(this.state.selected == 4)?styles.buttonStyleSelected:styles.buttonStyle}>
479 <Text style={(this.state.selected == 4)?styles.buttonTextSelected:styles.buttonText}>All Delete</Text>
480 </TouchableOpacity>
481
482 <TouchableOpacity onPress={()=>this.changeSelection(5)} style={(this.state.selected == 5)?styles.buttonStyleSelected:styles.buttonStyle}>
483 <Text style={(this.state.selected == 5)?styles.buttonTextSelected:styles.buttonText}>Refresh record</Text>
484 </TouchableOpacity>
485 </View>
486
487 <View style={styles.formStyle}>
488 {((this.state.selected == 4) || (this.state.selected == 5))?null:
489 <View>
490 <Text>Item No</Text>
491 <TextInput keyboardType = {'numeric'} style = {styles.textInputstyle} onChangeText = {(text) => this.setState({itemNo:text})} value = {this.state.itemNo}/>
492 </View>
493 }
494
495 {((this.state.selected == 2) || (this.state.selected == 3) || (this.state.selected == 4) || (this.state.selected == 5))?null:
496 <View style={{marginTop:10}}>
497 <Text>Item Name</Text>
498 <TextInput style = {styles.textInputstyle} onChangeText = {(text) => this.setState({itemName:text})} value = {this.state.itemName}/>
499 </View>
500 }
501
502 <View>
503 <TouchableOpacity style={styles.goStyle} onPress={()=>this.goClick()}>
504 <Text style={{color:'#fff'}}>GO</Text>
505 </TouchableOpacity>
506 </View>
507 </View>
508
509 <ListView
510 style={{flex:1}}
511 dataSource={this.state.dataSource}
512 renderRow={(rowData) => this.listRowItems(rowData)}
513 enableEmptySections ={true}/>
514 </View>
515
516 );
517 }
518 }
519
520 const styles = StyleSheet.create({
521 containerloading: {
522 justifyContent: 'center',
523 height:150,
524 width:150,
525 },
526 containerholder: {
527 flex: 1,
528 backgroundColor: 'rgba(255, 255, 255, .4)',
529 justifyContent: 'center',
530 alignItems:'center',
531 },
532 container: {
533 flex: 1,
534 backgroundColor: '#fff',
535 padding:10,
536 },
537 buttonContainer:{
538 flexDirection:'row',
539 marginTop:10,
540 marginBottom:10,
541 },
542 buttonStyleSelected:{
543 padding:5,
544 backgroundColor:'#00ff',
545 marginLeft:5,
546 },
547 buttonStyle:{
548 padding:5,
549 backgroundColor:'gray',
550 marginLeft:5,
551 },
552 buttonText :{
553 color:'#000',
554 },
555 buttonTextSelected :{
556 color:'#fff',
557 },
558 formStyle:{
559 borderRadius: 4,
560 borderWidth: 0.5,
561 borderColor: '#000',
562 padding:15,
563 },
564 textInputstyle:{
565 height: 40,
566 width:100,
567 borderColor: 'gray',
568 borderWidth: 1 ,
569 marginTop:10,
570 },
571 goStyle:{
572 padding:5,
573 backgroundColor:'gray',
574 width:100,
575 marginTop:15,
576 justifyContent: 'center',
577 alignItems: 'center',
578 },
579 listItemStyle:{
580 padding:10,
581 }
582
583 });