Android USES execSQL and rawQuery to complete the detailed analysis of data adding deleting changing and checking
- 2020-05-10 18:49:44
- OfStack
Operate on the SQLite database using SQLiteDatabase
/*
Android provides 1 called SQLiteDatabase Class, which encapsulates 1 Some database operations API , you can use this class to add data (Create) , query (Retrieve) , update, (Update) And delete (Delete) Operations (these operations are called for short CRUD ). right SQLiteDatabase Of learning, we should focus on mastering execSQL() and rawQuery() Methods. execSQL() Method can be executed insert , delete , update and CREATE TABLE Or something like that SQL Statements; rawQuery() Method for execution select Statements.
execSQL() Examples of methods:
SQLiteDatabase db = ....;
db.execSQL("insert into person(name, age) values(' The test data ', 4)");
db.close();
Perform the above SQL Statement is to person Add to the table 1 Bar. In practice, The "test data" parameter values in the statement are provided by the user input interface if the user input is grouped into the above insert Statements, When a user enters something with a single quote, the group spells it out SQL Statements can have syntax errors. The solution to this problem is to escape the single quotation marks, which is to convert the single quotation marks into two single quotation marks. Sometimes users will type in things like" & "These special SQL Symbols, just to make sure it's all right SQL Statement syntax correct, must be correct SQL These special things in the statement SQL Symbols are escaped, obviously, for each one SQL It is tedious for statements to do this. SQLiteDatabase Class provides a 1 After reloading execSQL(String sql, Object[] bindArgs) Method, which solves the problem mentioned earlier because it supports the use of placeholder parameters (?) . Examples of use are as follows:
SQLiteDatabase db = ....;
db.execSQL("insert into person(name, age) values(?,?)", new Object[]{" The test data ", 4});
db.close();
execSQL(String sql, Object[] bindArgs) Methods the first 1 The parameters for SQL Statement, the first 2 The parameters for SQL The value of a placeholder parameter in a statement, and the order of the parameter values in the array corresponds to the position of the placeholder.
*/
public class DatabaseHelper extends SQLiteOpenHelper {
// Class is not instantiated , Is a parameter that cannot be used as a parent constructor , Must be declared static
private static final String name = "itcast"; // Database name
private static final int version = 1; // Database version
public DatabaseHelper(Context context) {
// The first 3 A parameter CursorFactory Specifies to be obtained when the query is executed 1 Factory class for two cursor instances , Set to null, Represents the use of the system's default factory class
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(20), age INTEGER)");
}
@Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL(" ALTER TABLE person ADD phone VARCHAR(12) NULL "); // Add to the table 1 column
// DROP TABLE IF EXISTS person Delete table
}
}
// In real project development, users should be prevented from storing Numbers when the database table structure is updated // The data in the database is lost.
/*
Android provides 1 called SQLiteDatabase Class, which encapsulates 1 Some database operations API , you can use this class to add data (Create) , query (Retrieve) , update, (Update) And delete (Delete) Operations (these operations are called for short CRUD ). right SQLiteDatabase Of learning, we should focus on mastering execSQL() and rawQuery() Methods. execSQL() Method can be executed insert , delete , update and CREATE TABLE Or something like that SQL Statements; rawQuery() Method for execution select Statements.
execSQL() Examples of methods:
SQLiteDatabase db = ....;
db.execSQL("insert into person(name, age) values(' The test data ', 4)");
db.close();
Perform the above SQL Statement is to person Add to the table 1 Bar. In practice, The "test data" parameter values in the statement are provided by the user input interface if the user input is grouped into the above insert Statements, When a user enters something with a single quote, the group spells it out SQL Statements can have syntax errors. The solution to this problem is to escape the single quotation marks, which is to convert the single quotation marks into two single quotation marks. Sometimes users will type in things like" & "These special SQL Symbols, just to make sure it's all right SQL Statement syntax correct, must be correct SQL These special things in the statement SQL Symbols are escaped, obviously, for each one SQL It is tedious for statements to do this. SQLiteDatabase Class provides a 1 After reloading execSQL(String sql, Object[] bindArgs) Method, which solves the problem mentioned earlier because it supports the use of placeholder parameters (?) . Examples of use are as follows:
SQLiteDatabase db = ....;
db.execSQL("insert into person(name, age) values(?,?)", new Object[]{" The test data ", 4});
db.close();
execSQL(String sql, Object[] bindArgs) Methods the first 1 The parameters for SQL Statement, the first 2 The parameters for SQL The value of a placeholder parameter in a statement, and the order of the parameter values in the array corresponds to the position of the placeholder.
*/
/*
SQLiteDatabase the rawQuery() Used to perform select Statement, using the following examples: SQLiteDatabase db = ....;
Cursor cursor = db.rawQuery( " select * from person " , null);
while (cursor.moveToNext()) {
int personid = cursor.getInt(0); // For the first 1 The value of the column , The first 1 The index of the column from 0 start
String name = cursor.getString(1);// For the first 2 The value of the column
int age = cursor.getInt(2);// For the first 3 The value of the column
}
cursor.close();
db.close();
rawQuery() Methods the first 1 The parameters for select Statements; The first 2 The parameters for select The value of a placeholder parameter in a statement, if select The statement does not use a placeholder, which can be set to null . With a placeholder parameter select Examples of statement usage are as follows:
Cursor cursor = db.rawQuery("select * from person where name like ? and age=?", new String[]{"% The think tank %", "4"});
Cursor It's a result set cursor, which is used for random access to the result set, if you're familiar with it jdbc . Actually, Cursor with JDBC In the ResultSet The effect is very similar. use moveToNext() Method moves the cursor down from the current row 1 Row, if it has moved past the end of the result set 1 Line, returns the result as false , or for true . In addition Cursor There are also commonly used moveToPrevious() Method to move the cursor up from the current row 1 Ok, if I've moved past the end of the result set 1 Line, the return value is false , or for true ), moveToFirst() Method (used to move the cursor to the end of the result set 1 Line, if the result set is empty, the return value is false , or for true ) and moveToLast() Method (used to move the cursor to the end of the result set 1 Line, if the result set is empty, the return value is false , or for true ) .
*/
/*
In addition to what I just showed you execSQL() and rawQuery() Method, SQLiteDatabase It also provides operation methods corresponding to adding, deleting, updating and querying: insert() , delete() , update() and query() . These methods are actually for those who don't know much about them SQL Beginners of grammar use it for familiarity SQL For programmers of the syntax, use it directly execSQL() and rawQuery() Methods to perform SQL Statement can complete the data to add, delete, update, query operations.
Insert() Method is used to add data for each field ContentValues Store it. ContentValues Similar to the MAP Relative to the MAP , it provides access to data corresponding to put(String key, Xxx value) and getAsXxx(String key) Method, key Is the field name, value Is the field value, Xxx Refers to various commonly used data types, such as: String , Integer And so on.
SQLiteDatabase db = databaseHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", " The test data ");
values.put("age", 4);
long rowid = db.insert( " person " , null, values);// Returns the line number of the newly added record, with the primary key id Has nothing to do
No matter the first 3 Whether the parameters contain data, execute Insert() Methods must be added 1 A record if a 3 Three parameters are empty and will be added 1 The value of the field other than the primary key is Null The record. Insert() Methods are actually constructed internally insert SQL The statement completes adding data, Insert() Methods the first 2 Three parameters are used to specify the name of the null field, I believe you will be confused about this parameter, what is the function of this parameter? Here's the thing: if no 3 A parameter values for Null Or the number of elements 0 . Due to the Insert() Method requirements must be added 1 Bar except for the primary key Null A record of the values in order to satisfy SQL The need for grammar, insert Statement must be given 1 Field names, such as: insert into person(name) values(NULL) , if the field name is not given . insert The sentence goes like this: insert into person() values() Obviously this does not meet the criteria SQL Syntax. For field names, it is recommended to use fields other than the primary key, if used INTEGER Type of primary key field, performs similarly insert into person(personid) values(NULL) the insert After the statement, the value of the primary key field will not be NULL . If the first 3 A parameter values Don't for Null And the number of elements is greater than 0 , you can put the first 2 Is set to null .
*/
/*
delete() Use of methods:
SQLiteDatabase db = databaseHelper.getWritableDatabase();
db.delete("person", "personid<?", new String[]{"2"});
db.close();
The above code is used from person In the table to delete personid Less than 2 The record.
update() Use of methods:
SQLiteDatabase db = databaseHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put( " name " , "Test data" );//key Is the field name, value For the value
db.update("person", values, "personid=?", new String[]{"1"});
db.close();
The above code is used to put person In the table personid Is equal to the 1 The record of name Change the value of the field to test data.
*/
/*
query() And the way to do that is actually to take select The statement is split into components that are then used as input parameters to the method:
SQLiteDatabase db = databaseHelper.getWritableDatabase();
Cursor cursor = db.query("person", new String[]{"personid,name,age"}, "name like ?", new String[]{"% The think tank %"}, null, null, "personid desc", "1,2");
while (cursor.moveToNext()) {
int personid = cursor.getInt(0); // For the first 1 The value of the column , The first 1 The index of the column from 0 start
String name = cursor.getString(1);// For the first 2 The value of the column
int age = cursor.getInt(2);// For the first 3 The value of the column
}
cursor.close();
db.close();
The above code is used from person In the table lookup name The field contains the record of "pass-through wisdom", and the matching record is pressed personid Sort in descending order, skip the sorted results 1 A record, get only 2 Records.
query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit) The meaning of each parameter of the method:
table : the name of the table. The equivalent of select statements from The part after the keyword. If you have a multi-table joint query, you can use a comma to separate the two table names.
columns : the column name to be queried. The equivalent of select statements select The part after the keyword.
selection : query condition clause, equivalent to select statements where The section after the keyword allows the use of placeholders in the condition clause. ? "
selectionArgs That corresponds to the selection The value of a placeholder in a statement, the position of the value in the array, and the position of the placeholder in the statement must be 1 To, otherwise there will be abnormal.
groupBy : the equivalent of select statements group by The part after the keyword
having : the equivalent of select statements having The part after the keyword
orderBy : the equivalent of select statements order by The part after the keyword, such as: personid desc, age asc;
limit : specifies the offset and the number of records retrieved, equivalent to select statements limit The part after the keyword.
*/
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);
}
}
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;
/**
* The test method through Junit Unit testing
* 1.> Instantiate the test class
* 2.> Pass context information about 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 new 1 records
* @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"));
}
/**
* To find the 1 records
* @throws Throwable
*/
public void testFind() throws Throwable
{
PersonService personService=new PersonService(this.getContext());
Person person=personService.find(1);
Log.i(TAG,person.toString());
}
/**
* Test the update 1 records
* @throws Throwable
*/
public void testUpdate() throws Throwable
{
PersonService personService=new PersonService(this.getContext());
Person person=personService.find(1);
person.setName("lisi");
personService.update(person);
}
/**
* The test gets all the records
* @throws Throwable
*/
public void testGetCount() throws Throwable
{
PersonService personService=new PersonService(this.getContext());
Log.i(TAG, personService.getCount()+"********");
}
/**
* The test page
* @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 to delete 1 records
* @throws Throwable
*/
public void testDelete() throws Throwable
{
PersonService personService=new PersonService(this.getContext());
personService.delete(5);
}
}
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 something extra 1 Fields (requirements)
* You can change the version number But it must be >=1
* After changing the version number It looks at the version number to see if it was created last time (whether the current version number and the incoming version number are 1 to )
* If it's not going to be executed onUpgrade (a) methods
* @param context
*/
public DBOpenHelper(Context context)
{
super(context, "zyq.db", null, 2);
}
/**
* At the time of database creation 1 The method that is called
* Good for creating table structures
*/
@Override
public void onCreate(SQLiteDatabase db)
{
db.execSQL("CREATE TABLE person (personid integer primary key autoincrement, name varchar(20))");// Create a table
}
/**
* Update table structure Called when the database version number changes
* Application upgrade
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
db.execSQL("ALTER TABLE person ADD phone VARCHAR(12) NULL "); // Add to the table 1 column
}
}
<?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>
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(Context context)
{
helper=new DBOpenHelper(context);
}
/**
* new 1 records
* @param person
*/
public void save(Person person)
{
SQLiteDatabase db=helper.getWritableDatabase();//Create and/or open a database that will be used for reading and writing
db.execSQL("INSERT INTO person(name,phone) values(?,?)",new Object[]{person.getName().trim(),person.getPhone().trim()});// Use placeholders for translation
// db.close(); No database connection . You can improve performance Because the mode of operation when creating the database is private.
// Represents this database, which can only be accessed by this application Single user, can maintain long - term link
}
/**
* Update a 1 records
* @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()});
}
/**
* According to the ID Query a record
* @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 The cursor and ResultSet Very much like
if(cursor.moveToFirst())//Move the cursor to the first row. This method will 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 new Person(personid,name,phone);
}
return null;
}
/**
* Delete a 1 records
* @param id
*/
public void delete(Integer id)
{
SQLiteDatabase db=helper.getWritableDatabase();
db.execSQL("delete from person where personid=?",
new Object[]{id});
}
/**
* 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 Statement with MySQL The grammar of the 1 sample
* @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;
}
}
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;
}
}