0

For some resons, I want to use sqlite as a replacement for sqlite I can use room db, create a table called preferences with a structure like this

| key | value |

| key1 | value1 |

| key2 | value2 |

Problem with this approach is value column can only be text but i want to store text/numbers

| key | value | type |

| key1 | value1 | string |

| key2 | value2 | int |

And based on type I can manualy use Integer.parse(stringValue) ..

But this looks like i'm reinventing a wheel

Is there any other approach I'm not able to grasp?

Omkar T
  • 755
  • 8
  • 19
  • Can you add more details? For example, do you want to perform any arithemetic operations on the value(such as WHERE value > 1)? Is the value only restricted to int or string, and no other data type? – Sanil Khurana Dec 11 '21 at 08:30
  • I dont want to do any queries, only getValue for key and putValue for key, thats it – Omkar T Dec 11 '21 at 08:31
  • Just like shared preferences value can be string,int,double,long,boolean etc – Omkar T Dec 11 '21 at 08:32
  • Added an answer, see if it resolves your problem – Sanil Khurana Dec 11 '21 at 08:40
  • *I dont want to do any queries, only getValue for key and putValue for key* There is no getValue or putValue for sql tables. What you will do with this table is execute queries (SELECT, INSERT, UPDATE or DELETE) against the table. Typically in this case you would deal with the data type of the values in your application logic, where you can use a try/catch block to check if a value is numeric or not. But this also is problematic, because there are numeric values that should never be treated as numeric, like an unformatted telephone number. – forpas Dec 11 '21 at 08:42
  • @forpas I'm aware that sqlite doesn't provide putvalue/getvalue, i was talking about my own room implementation, when query will be "Select * from prefs where key == :key" – Omkar T Dec 11 '21 at 08:47

2 Answers2

1

If your datatype is not defined, instead of storing the datatype yourself, and figuring out which parsing function you need to perform, you could instead use JSON to store your data.

In your example, the value becomes a JSON encoded string. So, you can store any data type and parsing it is a single step, and you don't have to care about the datatype.

For example,

|Key                       |Value        |
|aStringVariable           |"Hello"      |
|anIntVariable             |1            |
|aBooleanVariable          |true         |

You just need to use a function to parse the JSON data when you retrieve it from the database and a function to encode data into JSON when you store it.

You haven't mentioned which language you are using, assuming Java, you can use GJson - https://www.javatpoint.com/how-to-convert-string-to-json-object-in-java.

Since you don't know the structure of your data, you can use a map when converting JSON into an object. It seems like all popular Java libraries do support this.

Here is a link that provides more details into how to parse and unparse JSON data into maps using gson and jackson (and a few others) - Convert a JSON String to a HashMap

Sanil Khurana
  • 1,129
  • 9
  • 20
  • That's a nice trick as "abc" , 232 ,3232.323 are all valid json – Omkar T Dec 11 '21 at 08:44
  • So I have to do Gson.fromJson(dbValue, String.class) if i'm not wrong .. right? – Omkar T Dec 11 '21 at 08:45
  • @OmkarT Not really. Haven't done java in some while but looking online it seems like the second argument is the class to which you want to convert your data to. Since you don't know the structure of your data, you can instead use collections, like map – Sanil Khurana Dec 11 '21 at 08:55
  • Since I know the return type I'm expecting eg db.getTapCount() <- int or db.getDraft() <- string, I can pass it manually, so thanks for the answer! – Omkar T Dec 11 '21 at 08:58
  • If you know the return type you are expecting for each key then what is the problem? There are methods to retrieve the values of the column `value` like getInt() or getString(). – forpas Dec 11 '21 at 12:14
  • there's no more a problem as I said in above comment, that's why I accepted the answer! – Omkar T Dec 11 '21 at 21:49
1

Not Sure Why You can't use SharedPreferences But if you want to store and retrieve primitive types you can just use String when saving it and parse it when you retrieve it. Key | someValue Stored as String.

When reading this value if you know what you expect then you can parse it to that specific type.

Manoj Mohanty
  • 372
  • 2
  • 10