Flutter - When you store data in SQFLlite

기본적으로 SQLite에서는 NULL, INTEGER, REAL, TEXT, BLOB형태로 저장이 됩니다

NULL은 데이터 값이 비어있을때

INTEGER는 정수형 (int value, DateTime, bool 등)

DateTime의 경우 INSERT할 때 DateTime.millisecondsSinceEpoch

 

값으로 저장 후 불러올 때는 DateTime.fromMillisecondsSinceEpoch(value)과 같은 형태로 불러옵니다

 

ex)

데이터 클래스

void setTodayData(DateTime time, int percent) async {
  _map[time] = percent;
  notifyListeners();

  await _manager.setStatus(time.millisecondsSinceEpoch, percent);
}

 

DBHelper

Future<int> setStatus(int date, int percent) async {
  var database = await _openDB();
  int insert = await database.rawInsert(
      'INSERT INTO $statusTable(date, percent) VALUES (?, ?)',
      [date, percent]);

  return insert;
}

Future<Map<DateTime, int>> getStatus() async {
  var database = await _openDB();
  List<Map> rawList = await database.rawQuery('SELECT * FROM $statusTable');

  return Map.fromIterable(rawList,
      key: (value) => DateTime.fromMillisecondsSinceEpoch(value['date']),
      value: (value) => value['percent']);
}

 

bool 같은 경우 data class에서 다음과 같이 변환 유틸을 만들어 주면 간단합니다

 

ex)

데이터 클래스

class MyItem{
  String title;
  int checkbox;

  MyItem({this.title, this.checkbox = 0}); //1 : true 0 : false

  bool get getCheckBox{
    return (checkbox == 1);
  }

  void toggleCheckbox(){
    if(getCheckBox){
      checkbox = 0;
    } else{
      checkbox = 1;
    }
  }
}

 

TEXT는 문자열
DateTime을 문자열로 저장하고 싶을때는

DateTime.toIso8601String()과 같은 형태로 저장 후 DateTime.parse로 불러오면 됩니다

currentTime.toIso8601String();
time = DateTime.parse(currentTime.toIso8601String());

 

위의 INTEGER예와 같은 DB에서는 다음과 같이 하면 될것같습니다

Future<int> setStatus(String date, int percent) async {
  var database = await _openDB();
  int insert = await database.rawInsert(
      'INSERT INTO $statusTable(date, percent) VALUES (?, ?)',
      [date, percent]);

  return insert;
}

Future<Map<DateTime, int>> getStatus() async {
  var database = await _openDB();
  List<Map> rawList = await database.rawQuery('SELECT * FROM $statusTable');

  return Map.fromIterable(rawList,
      key: (value) => DateTime.parse(value['date']),
      value: (value) => value['percent']);
}

 

이외에 REAL과 BLOB형태가 있는데 

REAL은 소수점이 있는 실수형을 저장할 때 사용되고

BLOB은 bitmap을 byte array로 변환된 값 등을 저장할 때 사용됩니다