Get Generated Primary Key Sqlite Android
Dec 19, 2014 SQLite – Auto-Increment / Auto Generate GUID David Kittell December 19, 2014 Recently was asked if it’s possible to create an auto-incrementing GUID in SQLite. SQLite AUTOINCREMENT is a keyword used for auto incrementing a value of a field in the table. We can auto increment a field value by using AUTOINCREMENT keyword when creating a table with specific column name to auto increment.
This compiler will generate you db class, content provider and several helper class for you from a describing file. You can set several options like package name, database name, database version, content authority and others.
You can find releases here on GitHub.
Run the above command. There are only 2 parameters.
- in : folder which contains json files that describe the database
- out : path to put generated files. Please note that packagename will be used to generate subfolders
- config : a json file which holds the configuration of the database. It can reside in the specified 'in' folder.
Don't forget to add the content provider to the AndroidManifest.
Gradle configuration
On Android Studio, you can use Gradle to generate files. Just put downloaded sqlitegenerator.jar on your app/libs folder and create a new task on build.gradle:
By default, the following files will be generated.#####[name]DB.java
- This is the main database. It extends
SQLiteOpenHelper
and contains all necessary code for maintaining the database and column names and positions.
#####[contentprovidername].java
- This class is the content provider to use. It's your run of the mill android
ContentProvider
.
#####[contentprovidername]Client.java
- The client provides easy access to CRUD operations on any described table. Below are the methods provided
#####[contentprovidername]BatchClient.java
- The batch client works the same as above, but is used for batched
ContentProviderOperation
. It contains methods to easily start a batch ofContentProviderOperation
and commit it with the correct authority. It contains basically the same methods as above.
It is up to you how you sort your json files inside of the path you pass to the generator. Tables and views are only recognized based on the fact that tables contain 'fields' and views contains 'selects'.
A configuration can be specified in your config.json file (or however you wish to call it). It should however contain the following details
Tables
A table consists of fields and constraints (on a table-level). Each field can also have constraints (on a field-level). By default, the primary key is the '_id' field, which is used by Android's adapters. You can override this by setting a field as 'autoincrement', or creating a constraint.
Consider the following example of a User table with just an id and a name.
There are 2 fields specified under the 'fields' array,and one constraint, which specifies that the combination of userid and username should be unique and on a conflict, is replaced. There are also constraints on the userid field, which state that the default value should be '10' and cannot be null. See this SQLite documentation on sqlite.org on more info on constraints.
Views
A view consists of selects across one or more tables, which can be grouped and ordered, and a join type can be specified.
![Sqlite Sqlite](https://i.stack.imgur.com/P2nMD.png)
Consider the following example of a View which combines our above user table with a table containing avatars of users, a table containing locations from users and a table which contains other photos of users.
We selected a few user details, the avatar's url, important location details and then a count on the user's photos to know how many he has. Next within 'from', we defined the tables we select from. Within 'on' we define the field we join on, in this case, all tables contain the userid field. You can also specify grouping and ordering by a field, and also what type of join you prefer.
###supported data types
These are just types you can use in your json schemes, internally these are converted to the ones SQlite supports. Everything else would be converted to a blob.
Thanks in part to Square, for their excellent Javawriter
-->The SQLite.NET library that Xamarin recommends is a very basic ORM thatlets you easily store and retrieve objects in the local SQLite databaseon an Android device. ORM stands for Object Relational Mapping – anAPI that lets you save and retrieve 'objects' from a databasewithout writing SQL statements.
To include the SQLite.NET library in a Xamarin app, add the following NuGet package to your project:
- Package Name: sqlite-net-pcl
- Author: Frank A. Krueger
- Id: sqlite-net-pcl
- Url:nuget.org/packages/sqlite-net-pcl
Tip
There are a number of different SQLite packages available – be sure to choose the correct one (it might not be the top result in search).
Once you have the SQLite.NET library available, follow these three steps to use it to access a database:
Add a using statement – Add the following statement to the C#files where data access is required:
Create a Blank Database – A database reference can becreated by passing the file path the SQLiteConnection classconstructor. You do not need to check if the file already exists– it will automatically be created if required, otherwise theexisting database file will be opened. The
dbPath
variable shouldbe determined according the rules discussed earlier in thisdocument:Save Data – Once you have created a SQLiteConnectionobject, database commands are executed by calling its methods, suchas CreateTable and Insert like this:
Retrieve Data – To retrieve an object (or a list ofobjects) use the following syntax:
Basic Data Access Sample
The DataAccess_Basic sample code for this document looks like thiswhen running on Android. The code illustrates how to perform simpleSQLite.NET operations and shows the results in as text in theapplication's main window.
Android
The following code sample shows an entire database interaction usingthe SQLite.NET library to encapsulate the underlying database access.It shows:
Creating the database file
Inserting some data by creating objects and then saving them
Querying the data
You'll need to include these namespaces: /mathematica-11-activation-key-generator-mac.html.
The last one requires that you have added SQLite to your project. Notethat the SQLite database table is defined by adding attributes to aclass (the Stock
class) rather than a CREATE TABLE command.
Using the [Table]
attribute without specifying a table name parameterwill cause the underlying database table to have the same name as theclass (in this case, 'Stock'). The actual table name is importantif you write SQL queries directly against the database rather than usethe ORM data access methods. Similarly the [Column('_id')]
attributeis optional, and if absent a column will be added to the table with thesame name as the property in the class.
SQLite Attributes
Common attributes that you can apply to your classes to control howthey are stored in the underlying database include:
[PrimaryKey] – This attribute can be applied to aninteger property to force it to be the underlying table's primarykey. Composite primary keys are not supported.
[AutoIncrement] – This attribute will cause an integerproperty's value to be auto-increment for each new object insertedinto the database
[Column(name)] – The
name
parametersets the underlying database column's name.[Table(name)] – Marks the class as being able to bestored in an underlying SQLite table with the name specified.
[MaxLength(value)] – Restrict the length of a textproperty, when a database insert is attempted. Consuming codeshould validate this prior to inserting the object as thisattribute is only 'checked' when a database insert or updateoperation is attempted.
[Ignore] – Causes SQLite.NET to ignore this property.This is particularly useful for properties that have a type thatcannot be stored in the database, or properties that modelcollections that cannot be resolved automatically by SQLite.
[Unique] – Ensures that the values in the underlyingdatabase column are unique.
Pengertian Primary Key
Most of these attributes are optional. You should always specify an integerprimary key so that selection and deletion queries can be performedefficiently on your data.
More Complex Queries
The following methods on SQLiteConnection
can be used to perform other data operations:
Insert – Adds a new object to the database.
Get<T> – Attempts to retrieve an object using theprimary key.
Table<T> – Returns all the objects in the table.
Delete – Deletes an object using its primary key.
Query<T> – Perform an SQL query that returns a number ofrows (as objects).
Execute – Use this method (and not
Query
) when youdon't expect rows back from the SQL (such as INSERT, UPDATE andDELETE instructions).
Getting an object by the primary key
SQLite.Net provides the Get method to retrieve a single object based on its primary key.
Selecting an object using Linq
Methods that return collections support IEnumerable<T>
so you can useLinq to query or sort the contents of a table. The following code showsan example using Linq to filter out all entries that begin with theletter 'A':
Selecting an object using SQL
Even though SQLite.Net can provide object-based access to your data,sometimes you might need to do a more complex query than Linq allows(or you may need faster performance). You can use SQL commands with theQuery method, as shown here:
Note
When writing SQL statements directly you create adependency on the names of tables and columns in your database, whichhave been generated from your classes and their attributes. If youchange those names in your code you must remember to update anymanually written SQL statements.
Deleting an object
The primary key is used to delete the row, as shown here:
You can check the rowcount
to confirm how many rows were affected(deleted in this case).
Using SQLite.NET with Multiple Threads
SQLite supports three different threading modes: Single-thread,Multi-thread, and Serialized. If you want to access the databasefrom multiple threads without any restrictions, you can configureSQLite to use the Serialized threading mode. It's important to setthis mode early in your application (for example, at the beginning ofthe OnCreate
method).
To change the threading mode, call SqliteConnection.SetConfig
. Forexample, this line of code configures SQLite for Serialized mode:
The Android version of SQLite has a limitation that requires a few moresteps. If the call to SqliteConnection.SetConfig
produces a SQLiteexception such as library used incorrectly
, then you must use thefollowing workaround:
Sqlite Multiple Primary Key
Link to the native libsqlite.so library so that the
sqlite3_shutdown
andsqlite3_initialize
APIs are madeavailable to the app:At the very beginning of the
OnCreate
method, add this codeto shutdown SQLite, configure it for Serialized mode, andreinitialize SQLite:
This workaround also works for the Mono.Data.Sqlite
library. For moreinformation about SQLite and multi-threading, seeSQLite and Multiple Threads.