Java/Android CRUD SQL design

Question

All the SQL in my app...

1. Should I be confining it to its own special class? For example a MyDatabase class that extends SQLiteOpenHelper and implements all the create-tables, defines all the table names, column names, queries, holds all the CRUD methods for all my objects, etc.

2. Or should I be putting all this stuff in the actual objects themselves and have all my objects implement some common interface like "DatabaseModel" where all DatabaseModels implement things like create, save, delete, onCreate, onUpgrade, etc? What do people choose and why?

3. I want to know how most people handle create/update. In my case I have my objects contain many of the same members that they do in the SQL database. For example a Player object might have an id, name, about-me text, etc. That id would correspond to the auto-incremented id in the database. But in the app, when you're first filling in the data for that object, it doesn't exist in the database yet, so I default it to an id of -1. Then if someone wants to save the object, I have it check if the id is negative, and if so, I create it in the database and assign the object that id immediately in the SQL method. Otherwise it is an update and it'll have the correct id anyway. Is this what most people do? Is this a sound decision?

If anyone does answer, please provide some kind of example of what you mean.

Answer 1

If I'm an object in your system that needs to be persisted please don't shove me through some 'all sql goes here' God object that barely knows me.

Also don't expect me to use some barely-different-than-sql api to save myself. Let me define my values and how deeply my structure needs to be copied. Don't ask me to know anything about what kind of persistence you're using. Structured db, noSQL, graph, flat file, it's nothing to do with me.

Oh and I don't get my identity from any ID value from what ever you persist me with. I got my identity when I was created.

All I should deal with is what I know. I know who I am. I know my state. I know what should die if I die. Give me an api to say that through and a set of things behind that api that know how to talk to whatever you saving me in.

Do this and I'll play nice when a new kind of database comes out.

Answer 2

That is basically what I do, except that my id is usually 'new' until I save. But only if an ID is needed. If it is a transaction record where no unique ID exists for the record (except a combo of other known values that become unique together), I try an update first, and if it fails, I execute an insert.

Answer 3

As a counterpoint, I would endorse having a DB model class. Such a class allows you to implement code which can auto-update the backing database when you install subsequent versions of the app. The data object classes can still provide methods to the DB class such that it will know exactly what it needs to know to properly marshal data into and out of the DB.