A summary of the key points of Android app using SQLite database _android

Source: Internet
Author: User
Tags sorted by name sqlite sqlite database first row

The/data/data/package name/databases is the directory where the program holds the data, which is the path returned by the Environment.getdatadirectory () method. After you locate the database, you can select User.db to perform the export.
With the real machine debugging, the data directory if not open, indicating that your mobile phone does not root, instead of the simulator on the OK.

1. Get the Sqlitedatabase object:

Sqlitedatabase db = openorcreatedatabase (file file, Sqlitedatabase.cursor, Factory factor);

2.SQLiteDatabase provides the following methods:

Db.execsql (SQL)     //execute any SQL statement
Db.insert (table, Nullcolumnhack, value)   //(ADD)
db.delete (table, Whereclause, Whereargs)  //(delete)
db.updata (table, values, Whereclause, Whereargs)//(change)
db.query (table, Columns,whereclause,whereargs,groupby,having,orderby)//(check)
db.rawquery (SQL, Selectionargs)  // You can use SQL statements to query directly

3. Execute query and Rawquery operations, return a cursor cursor object that traverses the contents of the entire query, cursor provides the following methods for moving the cursor:

C.move (int offset)  //cursor moves the specified number of rows up or down, positive numbers down, negative
values up C.movetofirst ()    //moving to the first row, returning a Boolean
c.movetolast ()
C.movetonext ()
c.movetopostion (int postion)  //move to specified row, return Boolean
c.movetoprevious ()  //Move to Previous line
C.isfirst ();       Whether to point to the first 
c.islast ();       Whether to point to the last 
C.isbeforefirst ();  C.isafterlast () before pointing to the first article 
;   Whether to point after the last bar 
c.isnull (int columnindex);///Specify whether the column is empty (the column cardinality is 0) 
c.isclosed ();     Whether the cursor has been closed 
c.getcount ();    Total number of data items 
c.getposition ();  Returns the number of rows that the current cursor is pointing to 
c.getcolumnindex (String columnName);//Returns the column index value 
c.getstring (int columnindex)         for a column name; Returns the value of the specified column in the current row

Here is an instance of creating a Sqlitedatabase object that queries only with SQL statements

protected void OnCreate (Bundle savedinstancestate) {super.oncreate (savedinstancestate);
  Setcontentview (R.layout.activity_main); Each program has its own database, and does not interfere//create a database and open, this method returns a Sqlitedadabase object (if created without, open directly)// The name of this database is called user.db, so it is to be exported to the computer later to facilitate the third party software to open, the second parameter is a constant, this example indicates that the private database can not access sqlitedatabase db = Openorcreatedatabase ("
  User.db ", mode_private, NULL); Create a table USERTB, the primary key name suggested written _id, 1 primary keys, 3 columns, plus _id total 4 columns db.execsql ("CREATE table if not exists USERTB (_id integer PRIMARY Key A
  Utoincrement, name text NOT NULL, age integer NOT null, sex text NOT NULL);
   Add 3 data to the table USERTB, 3 columns, 3 corresponding values Db.execsql (insert into USERTB (name, age, Sex) VALUES (' Jack ', ' 18 ', ' Man '));
   Db.execsql ("INSERT into USERTB (name, age, Sex) VALUES (' Hellen ', ' 19 ', ' Female ')");

  Db.execsql ("INSERT into USERTB (name, age, Sex) VALUES (' Mike ', ' 20 ', ' Male ')");
  Query data, the first parameter is also a statement, query method, specify how to find the query conditions, the second parameter is the query condition, the default data all query out//The return value here is cursor, is the query data after the management set of a class, can be understood as a list (cursor interface) Cursor C = Db.rawquery ("SELECT * from USERTB", Null);                if (c!= null) {//If the data can be queried c.movetofirst (); If no data has been processed, this can be omitted, the default cursor on the first line while (C.movetonext ()) {//each time only one data can be queried to determine whether the next line can be queried (emphasis: each time the cursor arrives at a row, the following statement prints the data in that row, followed by the       Ring, print the following line of data) log.i ("info", "" + C.getint (C.getcolumnindex ("_id"));      The first field, int, needs to be converted to a string to print with Log (find the integer data in this data with a field angle of 0) log.i ("Info", C.getstring (C.getcolumnindex ("name"));
       The second field is the text type LOG.I ("info", "" +c.getint (C.getcolumnindex ("Age"));
       LOG.I ("Info", C.getstring (C.getcolumnindex ("Sex"));                          LOG.I ("info", "~~~~~~~~");                                  Test how much data is printed in a cycle c.close ();    
After the query, the cursor must be released} db.close ();

 }

4. The relevant parameters of the search and deletion:

Table: Tables name for query data
Columns: The name of the column to query out
Whereclause: Query criteria clause, allow placeholder "?"
Whereargs: Used to pass parameter values for placeholders
GroupBy: Used to control grouping
Having: Used to filter groups
ORDER BY: Used to sort records

Contentvalues is a wrapper over the Key/value, which can be encapsulated in a key/value form that will be inserted or modified, and used directly when using the corresponding modification method.
It has two deposits and takes out two methods:

Put (String key,xxx);
Getasxxx (String Key);

The following is an example of using a built-in function to manipulate database additions and deletions:

 Sqlitedatabase db = Openorcreatedatabase ("User.db", mode_private, NULL); Db.execsql ("CREATE table if not exists USERTB" (_id Integer primary key autoincrement, name text isn't null, age integer Not n

  ull, sex integer NOT null) "; 
  Before performing the method of adding and modifying, first create a Contentvalues object in the Insert method, and then deposit the data into the object, and then insert values into contentvalues values = new Contentvalues ();
  Add Values.put ("name", "John");
  Values.put ("Age", 18);
  Values.put ("Sex", "male");  Db.insert ("USERTB", null, values);    The return value of the insertion method is a long representing the row number of the newly added record Values.clear ();
  You need to empty values before inserting the next piece of data, and then store values in the new data values.put ("name", "Dick");
  Values.put ("age", 19);
  Values.put ("Sex", "male");  
  Db.insert ("USERTB", null, values);
  Values.clear ();
  Values.put ("name", "Harry");
  Values.put ("Age", 20);
  Values.put ("Sex", "male");  
  Db.insert ("USERTB", null, values);
  Values.clear ();
  Change the gender of the ID greater than the female values.put ("sex", "female");
  Db.update ("USERTB", Values, "_id"), New string[]{"2"}); Delete (remove the person with three name) Db.delete ("UESRTB", "name like?", new String [] {"% three%"}); Check (query USERTB This table, all rows are bad, _id >0 data, query out of the data sorted by name) Cursor C = db.query ("USERTB", NULL, "_id >?", New string[]{"0"

  }, NULL, NULL, "name");

  C.close ();
  Closes the current database Db.close ();

 Deletes the USER.DB database (note is not table-Name table) deletedatabase ("user.db");


5.SQLiteOpenHelper:
Sqliteopenhelper is a helper class that manages our database by inheriting it and implementing OnCreate methods and upgrade methods.

Sqlitedatabase db = Helper.getwritabledatabase ();
Sqlitedatabase db = Helper.getreadabledatabase ();

One of the following instances, a new class inherits Sqliteopenhelper

The public class Dbopenhelper extends sqliteopenhelper{public    
  dbopenhelper (context, String name) {
    Super ( context, name, NULL, 1);
  Public Dbopenhelper (context, String name, cursorfactory factory,int version) {
    Super (context, name, factory, version);

  @Override///The first time a database is created, calls can normally be made to build a table operation public
  void OnCreate (Sqlitedatabase db) {
    db.execsql ("CREATE table if not" exists STUTB (_id integer primary key autoincrement,name text not null,sex text not null,age integer NOT null) ");
    Db.execsql ("INSERT into STUTB (name,sex,age) VALUES (' John ', ' Female ',") ");
  }

  @Override///When the version of the database changes, the public
  void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {}<) is automatically executed.

  c15/>}

The object of this subclass can then be created in the main activity, and the Sqlitedatabase object is obtained by the Get method of this class

Dbopenhelper helper =new dbopenhelper (mainactivity.this, "stu.db");
Sqlitedatabase db = Helper.getwritabledatabase ();

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.