Using Sqlitedatabase to manipulate SQLite databases
Copy Code code as follows:
/*
Android provides a class called Sqlitedatabase that encapsulates the API for manipulating databases that can be used to add (Create), query (Retrieve), update (update), and delete (delete) Operations (these operations are referred to as CRUD). For sqlitedatabase study, we should focus on mastering the Execsql () and Rawquery () methods. The Execsql () method executes SQL statements that have changed behavior, such as inserts, deletes, update, and create table; the Rawquery () method executes the SELECT statement.
Examples of the use of the Execsql () method:
Sqlitedatabase db = ...;
Db.execsql ("INSERT INTO Person" (name, age) VALUES (' Test data ', 4) ');
Db.close ();
Executing the above SQL statement adds a record to the person table. In practice, the parameter values of "test data" in the statements are provided by the user input interface, and if the user input content is spelled into the INSERT statement above, when the user input contains single quotes, There is a syntax error in the SQL statement that is spelled out by the group. To solve this problem, you need to escape the single quotation marks, which translates single quotes into two single quotes. Sometimes users will also input like "&" These special SQL symbols, in order to ensure that the group spelled the SQL statement correctly syntax, must be in the SQL statements of these special SQL symbols are escaped, obviously, for each of the SQL statements do this kind of processing work is more cumbersome. The Sqlitedatabase class provides an overloaded Execsql (String sql, object[] Bindargs) method that solves the problem mentioned earlier because this method supports the use of placeholder arguments (?). Examples of use are as follows:
Sqlitedatabase db = ...;
Db.execsql ("INSERT into person (name, age) VALUES (?,?)", New object[]{"test Data", 4});
Db.close ();
The first parameter of the Execsql (String sql, object[] Bindargs) method is the SQL statement, the second parameter is the value of the placeholder parameter in the SQL statement, and the order of the parameter values in the array corresponds to the position of the placeholder.
*/
Copy Code code as follows:
public class Databasehelper extends Sqliteopenhelper {
Class is not instantiated, is a parameter that cannot be used as a parent class constructor, and must be declared as a static
Private static final String name = "Itcast"; Database name
private static final int version = 1; Database version
Public Databasehelper {
The third parameter, cursorfactory, specifies that the factory class that obtains a cursor instance when executing the query is set to NULL, representing the factory class using the system default
Super (context, name, null, version);
}
@Override public void OnCreate (Sqlitedatabase db) {
Db.execsql ("CREATE TABLE IF not EXISTS person (PersonID Integer primary key autoincrement, name varchar (?), age integer)" );
}
@Override public void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {
Db.execsql ("ALTER TABLE person ADD phone VARCHAR () NULL"); Add a column to the table
drop table IF EXISTS person deletes tables
}
}
In the actual project development, when the database table structure is updated, the user should be prevented from storing the data lost in the number//according to the library.
Copy Code code as follows:
/*
Android provides a class called Sqlitedatabase that encapsulates the API for manipulating databases that can be used to add (Create), query (Retrieve), update (update), and delete (delete) Operations (these operations are referred to as CRUD). For sqlitedatabase study, we should focus on mastering the Execsql () and Rawquery () methods. The Execsql () method executes SQL statements that have changed behavior, such as inserts, deletes, update, and create table; the Rawquery () method executes the SELECT statement.
Examples of the use of the Execsql () method:
Sqlitedatabase db = ...;
Db.execsql ("INSERT INTO Person" (name, age) VALUES (' Test data ', 4) ');
Db.close ();
Executing the above SQL statement adds a record to the person table. In practice, the parameter values of "test data" in the statements are provided by the user input interface, and if the user input content is spelled into the INSERT statement above, when the user input contains single quotes, There is a syntax error in the SQL statement that is spelled out by the group. To solve this problem, you need to escape the single quotation marks, which translates single quotes into two single quotes. Sometimes users will also input like "&" These special SQL symbols, in order to ensure that the group spelled the SQL statement correctly syntax, must be in the SQL statements of these special SQL symbols are escaped, obviously, for each of the SQL statements do this kind of processing work is more cumbersome. The Sqlitedatabase class provides an overloaded Execsql (String sql, object[] Bindargs) method that solves the problem mentioned earlier because this method supports the use of placeholder arguments (?). Examples of use are as follows:
Sqlitedatabase db = ...;
Db.execsql ("INSERT into person (name, age) VALUES (?,?)", New object[]{"test Data", 4});
Db.close ();
The first parameter of the Execsql (String sql, object[] Bindargs) method is the SQL statement, the second parameter is the value of the placeholder parameter in the SQL statement, and the order of the parameter values in the array corresponds to the position of the placeholder.
*/
Copy Code code as follows:
/*
The Sqlitedatabase rawquery () is used to execute the SELECT statement, using the following example: Sqlitedatabase db = ...;
Cursor Cursor = Db.rawquery ("SELECT * from person", NULL);
while (Cursor.movetonext ()) {
int PersonID = cursor.getint (0); Gets the value of the first column, with the index of the first column starting at 0
String name = cursor.getstring (1);//Get the value of the second column
int age = Cursor.getint (2);//Get the value of the third column
}
Cursor.close ();
Db.close ();
The first parameter of the Rawquery () method is the SELECT statement, the second parameter is the value of the placeholder parameter in the SELECT statement, and the parameter can be set to NULL if the SELECT statement does not use a placeholder. Examples of SELECT statements with placeholder parameters are as follows:
Cursor Cursor = Db.rawquery ("select * from person where name is like?") And age=? ", new string[]{"% pass Wisdom% "," 4 "});
Cursor is a result set cursor that is used to randomly access the result set, and if you are familiar with JDBC, cursor is similar to the ResultSet effect in JDBC. You can use the MoveToNext () method to move the cursor from the current row to the next line, or True if you have moved past the last row of the result set and the result is false. In addition cursor also has a common movetoprevious () method (used to move the cursor from the current row to the previous line, if it has moved past the first row of the result set, the return value is false, otherwise true), Movetofirst () method, which is used to move the cursor to the first row of the result set, true if the result set is empty, the return value is false, or true) and the Movetolast () method (used to move the cursor to the last row of the result set, if the result set is empty, and the return value is false).
*/
Copy Code code as follows:
/*
In addition to the Execsql () and Rawquery () methods described earlier, Sqlitedatabase specifically provides actions for adding, deleting, updating, querying: Insert (), delete (), update (), and query (). These methods are actually for those who are unfamiliar with SQL syntax, for those familiar with SQL syntax, directly using the Execsql () and Rawquery () methods to execute SQL statements can complete the data add, delete, update, query operations.
The Insert () method is used to add data, and the data for each field is stored using contentvalues. Contentvalues is similar to map, which provides the put (string key, Xxx value) and getasxxx (string key) methods for accessing data, and key is the field name, and value is the field value. XXX refers to a variety of commonly used data types, such as: String, Integer, and so on.
Sqlitedatabase db = Databasehelper.getwritabledatabase ();
Contentvalues values = new Contentvalues ();
Values.put ("name", "test Data");
Values.put ("Age", 4);
Long rowID = Db.insert ("person", null, values);//Returns the line number of the newly added record, regardless of the primary key ID
Regardless of whether the third parameter contains data, executing the Insert () method inevitably adds a record, and if the third argument is empty, a record is added that has a null field value other than the primary key. Inside the Insert () method actually completes the addition of the data by constructing the Insert SQL statement, and the second parameter of the Insert () method is used to specify the name of the null field. I'm sure you'll be wondering about this parameter. Is this: if the third parameter values are null or the number of elements is 0, because the Insert () method requires that a record be added with a null value other than the primary key, the INSERT statement must be given a field name in order to satisfy the needs of the SQL syntax. For example, insert into person (name) values (NULL), if no field name is given, the INSERT statement becomes this way: INSERT into person () values (), which obviously does not satisfy standard SQL syntax. For field names, it is recommended that you use a field other than the primary key, and that the primary key field value will not be null after you execute an INSERT statement that resembles insert into person (PersonID) VALUES (NULL), if you use an integer type of primary key field. If the third argument values are not null and the number of elements is greater than 0, you can set the second argument to null.
*/
Copy Code code as follows:
/*
Use of the Delete () method:
Sqlitedatabase db = Databasehelper.getwritabledatabase ();
Db.delete ("Person", "personid<?", New string[]{"2"});
Db.close ();
The code above is used to delete records PersonID less than 2 from the person table.
Use of the Update () method:
Sqlitedatabase db = Databasehelper.getwritabledatabase ();
Contentvalues values = new Contentvalues ();
Values.put ("name", "test Data");//key is the field name and value is the
Db.update ("person", Values, "personid=", New string[]{"1"});
Db.close ();
The code above is used to change the value of the name field of a record in the person table that is equal to 1 to "test data". PersonID
*/
Copy Code code as follows:
/*
The query () method actually splits the SELECT statement into several components and then acts as an input parameter to the method:
Sqlitedatabase db = Databasehelper.getwritabledatabase ();
Cursor Cursor = db.query ("Person", new string[]{"Personid,name,age"}, "name like", New string[]{"% pass-wise%"}, NULL, NULL, "PE Rsonid desc "," 1,2 ");
while (Cursor.movetonext ()) {
int PersonID = cursor.getint (0); Gets the value of the first column, with the index of the first column starting at 0
String name = cursor.getstring (1);//Get the value of the second column
int age = Cursor.getint (2);//Get the value of the third column
}
Cursor.close ();
Db.close ();
The code above is used to find the name field from the person table that contains "pass-wise" records, the matching records are sorted in descending order by PersonID, and the sorted results are skipped over the first record, and only 2 records are obtained.
Query (table, columns, selection, Selectionargs, GroupBy, having, by, limit) method The meaning of each parameter:
Table: List name. Corresponds to the section following the SELECT statement from the keyword. If you are a multiple table union query, you can separate the two table names with commas.
Columns: The name of the column to query. Corresponds to the part of the SELECT statement following the SELECT keyword.
Selection: The query condition clause, which is equivalent to the section after the SELECT statement where keyword, allows the placeholder "?" in the conditional clause.
Selectionargs: Corresponds to the value of the placeholder in the selection statement, the position of the value in the array and the position of the placeholder in the statement must be the same, otherwise there will be an exception.
GroupBy: Equivalent to the section following the SELECT statement GROUP BY keyword
Having: the equivalent of the part of a SELECT statement after having a keyword
By: Equivalent to the section following the SELECT statement order by keyword, such as: PersonID DESC, age ASC;
Limit: Specifies the offset and the number of records fetched, which is equivalent to the portion following the Limit keyword of the SELECT statement.
*/
Copy Code code as follows:
Package com.zyq.db;
Import android.app.Activity;
Import Android.os.Bundle;
public class Mainactivity extends activity
{
@Override
public void OnCreate (Bundle savedinstancestate)
{
Super.oncreate (savedinstancestate);
Setcontentview (R.layout.main);
}
}
Copy Code code as follows:
Package com.zyq.db;
Import java.util.List;
Import Android.test.AndroidTestCase;
Import Android.util.Log;
Import Com.zyq.service.DBOpenHelper;
Import Com.zyq.service.PersonService;
Import Com.zyq.voo.Person;
/**
* Test method through JUnit unit test
* 1.> instantiation Test class
* 2.> the contextual information related to the application into the test class instance
* 3.> Run test method
* @author Administrator
*
*/
public class Personservicetest extends Androidtestcase
{
Private final static String tag= "Personservicetest";
/**
* Test CREATE Database
* @throws Throwable
*/
public void Testcreatedb () throws Throwable
{
Dbopenhelper dbopenhelper=new Dbopenhelper (This.getcontext ());
Dbopenhelper.getreadabledatabase (); Create and/or open a database.
}
/**
* Test Add a record
* @throws Throwable
*/
public void Testsave () throws Throwable
{
Personservice personservice=new Personservice (This.getcontext ());
Personservice.save (New person ("Zhangsan", "1360215320"));
Personservice.save (New person ("Lisi", "1123"));
Personservice.save (New person ("Lili", "232"));
Personservice.save (New person ("Wangda", "123123"));
Personservice.save (New person ("Laozhu", "234532"));
}
/**
* Find a record
* @throws Throwable
*/
public void Testfind () throws Throwable
{
Personservice personservice=new Personservice (This.getcontext ());
Person Person=personservice.find (1);
LOG.I (Tag,person.tostring ());
}
/**
* Test updates a record
* @throws Throwable
*/
public void Testupdate () throws Throwable
{
Personservice personservice=new Personservice (This.getcontext ());
Person Person=personservice.find (1);
Person.setname ("Lisi");
Personservice.update (person);
}
/**
* Test to get all of the records
* @throws Throwable
*/
public void Testgetcount () throws Throwable
{
Personservice personservice=new Personservice (This.getcontext ());
LOG.I (TAG, Personservice.getcount () + "********");
}
/**
* Test Paging
* @throws Throwable
*/
public void Testscroll () throws Throwable
{
Personservice personservice=new Personservice (This.getcontext ());
List<person> Persons=personservice.getscrolldata (3, 3);
for (person Person:persons)
{
LOG.I (TAG, person.tostring ());
}
}
/**
* Test Deletes a record
* @throws Throwable
*/
public void Testdelete () throws Throwable
{
Personservice personservice=new Personservice (This.getcontext ());
Personservice.delete (5);
}
}
Copy Code code as follows:
Package com.zyq.service;
Import Android.content.Context;
Import Android.database.sqlite.SQLiteDatabase;
Import Android.database.sqlite.SQLiteOpenHelper;
public class Dbopenhelper extends Sqliteopenhelper
{
/**
* If you want to add an extra field (requirements)
* Can change the version number but must >=1
* Change the version number to determine whether the last time it was created (the current version number and the incoming version number are the same), depending on the version number
* If the Onupgrade () method is not executed
* @param context
*/
Public Dbopenhelper
{
Super (context, "zyq.db", NULL, 2);
}
/**
* The first method to call when the database is created
* Suitable to create TABLE structure
*/
@Override
public void OnCreate (Sqlitedatabase db)
{
Db.execsql ("CREATE TABLE person (PersonID integer primary key autoincrement, name varchar (20))");
}
/**
* The Update table structure is called when the database version number has changed
* Application Upgrades
*/
@Override
public void Onupgrade (sqlitedatabase db, int oldversion, int newversion)
{
Db.execsql ("ALTER TABLE person ADD phone VARCHAR () NULL"); Add a column to the table
}
}
Copy Code code as follows:
<?xml version= "1.0" encoding= "Utf-8"?>
<manifest xmlns:android= "Http://schemas.android.com/apk/res/android"
Package= "Com.zyq.db"
Android:versioncode= "1"
Android:versionname= "1.0" >
<application android:icon= "@drawable/icon" android:label= "@string/app_name" >
<uses-library android:name= "Android.test.runner"/>
<activity android:name= ". Mainactivity "
Android:label= "@string/app_name" >
<intent-filter>
<action android:name= "Android.intent.action.MAIN"/>
<category android:name= "Android.intent.category.LAUNCHER"/>
</intent-filter>
</activity>
</application>
<USES-SDK android:minsdkversion= "8"/>
<instrumentation android:name= "Android.test.InstrumentationTestRunner"
Android:targetpackage= "com.zyq.db" android:label= "Tests for my App"/>
</manifest>
Copy Code code as follows:
Package com.zyq.service;
Import java.util.ArrayList;
Import java.util.List;
Import Android.content.Context;
Import Android.database.Cursor;
Import Android.database.sqlite.SQLiteDatabase;
Import Com.zyq.voo.Person;
public class Personservice
{
Private Dbopenhelper Helper;
Public Personservice
{
Helper=new Dbopenhelper (context);
}
/**
* Add a record
* @param person
*/
public void Save (person person)
{
Sqlitedatabase db=helper.getwritabledatabase ();//create and/or open a database that'll is used for reading and writing
Db.execsql (INSERT into person (name,phone) VALUES (?,?), New Object[]{person.getname (). Trim (), Person.getphone (). Trim ()});//use Placeholder for translation
Db.close (); Does not close the database connection. Can improve performance because the mode of operation when creating the database is private.
Represents this database, can only be accessed by the application of the single user, may maintain a long link
}
/**
* Update a record
* @param person
*/
public void update (person person)
{
Sqlitedatabase db=helper.getwritabledatabase ();
Db.execsql ("Update person set phone=?,name=?") Where personid=? ",
New Object[]{person.getphone (). Trim (), person.getname (), Trim (), Person.getid ()});
}
/**
* Query a record based on ID
* @param ID
* @return
*/
Public person find (Integer ID)
{
Sqlitedatabase db=helper.getreadabledatabase ();
Cursor cursor=db.rawquery ("select * from person where personid=?", New String[]{id.tostring ()});//cursor cursor and ResultSet are alike
if (Cursor.movetofirst ())//move the cursor to the "the". This method would return FALSE if the cursor is empty.
{
int Personid=cursor.getint (Cursor.getcolumnindex ("PersonID"));
String name=cursor.getstring (Cursor.getcolumnindex ("name"));
String phone=cursor.getstring (Cursor.getcolumnindex ("Phone"));
Return to new person (Personid,name,phone);
}
return null;
}
/**
* Delete a record
* @param ID
*/
public void Delete (Integer ID)
{
Sqlitedatabase db=helper.getwritabledatabase ();
Db.execsql ("Delete from person where personid=?")
New Object[]{id});
}
/**
* Get the number of records
* @return
*/
Public long GetCount ()
{
Sqlitedatabase db=helper.getreadabledatabase ();
Cursor cursor=db.rawquery ("SELECT count (*) from person", NULL);
Cursor.movetofirst ();
Return Cursor.getlong (0);
}
/**
* Paging Query method SQL statements are the same as MySQL syntax
* @return
*/
Public list<person> getscrolldata (int offset,int maxresult)
{
List<person> persons=new arraylist<person> ();
Sqlitedatabase db=helper.getreadabledatabase ();
Cursor cursor=db.rawquery ("select * FROM person limit?,?",
New String[]{string.valueof (offset), string.valueof (Maxresult)});
while (Cursor.movetonext ())
{
int Personid=cursor.getint (Cursor.getcolumnindex ("PersonID"));
String name=cursor.getstring (Cursor.getcolumnindex ("name"));
String phone=cursor.getstring (Cursor.getcolumnindex ("Phone"));
Persons.add (new Person (personid,name,phone));
}
return persons;
}
}
Copy Code code as follows:
Package Com.zyq.voo;
public class Person
{
Private Integer ID;
private String name;
Private String phone;
public person (int PersonID, string name, String phone)
{
This.id=personid;
This.name=name;
This.phone=phone;
}
Public person (string name, String phone)
{
THIS.name = name;
This.phone = phone;
}
Public String toString ()
{
return "person [id=" + ID + ", name=" + name + ", phone=" + Phone + "]";
}
Public Integer getId ()
{
return ID;
}
public void SetId (Integer ID)
{
This.id = ID;
}
Public String GetName ()
{
return name;
}
public void SetName (String name)
{
THIS.name = name;
}
Public String Getphone ()
{
return phone;
}
public void Setphone (String phone)
{
This.phone = phone;
}
}
