SQLite basics
Data types commonly used by SQLite
| field | role |
|---|---|
| char(n) | fixednA string of length |
| varchar(n) | String of variable length,nRepresents the maximum length |
| nchar(n) | Unlike char, it can be used to store Chinese characters |
| nvarchar(n) | Unlike VARCHAR, it can be used to store Chinese |
| text | Store the text |
| blob | Storing binary files |
| int | plastic |
| integer | plastic |
| bigint | plastic |
| float | Single precision type |
| double | Double precision floating point |
Here int, integer, the concrete difference between bigint, also didn’t understand. If any of you are aware of this, please direct us in the comments section 😄😄
Create and drop tables
The syntax for creating a table is
create table database_name.table_name(
column1 datatype primary key(one or more columns),
column2 datatype,
column3 datatype,
.....
columnN datatype,
);
Copy the codeThe syntax for deleting a table is
drop table database_name.table_name;
Copy the codeInsert data
The syntax for inserting a table is:
insert into table_name [(column1, column2, column3,...columnN)]
values(value1, value2, value3,... valueN); Or // Note: This ensures that the order of the values is the same as that in the tableinsert into table_name values(value1,value2,value3,... valueN);Copy the codeDelete the data
The syntax for deleting data is:
delete fromTable_name [condition];Copy the codeIf there is no condition for deleting data, all data is deleted by default. If a condition is specified, the data that meets the condition is deleted
Update the data
Grammar:
update table_name
setcolumn1 = value1, column2 = value2.... , columnN = valueN [condition];Copy the codeQuery data
Grammar:
// Query the value of the specified field (column)SELECT column1, column2, columnN FROMtable_name; Or // query the values of all fieldsSELECT * FROM table_name;
Copy the codeSQLite logical operator
| The operator | describe |
|---|---|
AND |
The AND operator allows the existence of multiple conditions in the WHERE clause of an SQL statement |
BETWEEN |
The BETWEEN operator is used to search for values in a range of values within a given minimum and maximum range |
EXISTS |
The EXISTS operator is used to search for the existence of a row in a specified table that satisfies certain conditions |
IN |
The IN operator is used to compare a value to a specified list of values |
NOT IN |
The opposite of the IN operator, used to compare a value to values that are not IN a specified list |
LIKE |
The LIKE operator is used to compare a value with a similar value using the wildcard operator |
GLOB |
The GLOB operator is used to compare a value with a similar value using the wildcard operator. GLOB differs from LIKE in that it is case sensitive |
NOT |
The NOT operator is the opposite of the logical operator used. NOT EXISTS, NOT BETWEEN, NOT IN, etc. It is the negation operator |
OR |
The OR operator is used to combine multiple conditions in the WHERE clause of an SQL statement |
IS NULL |
The NULL operator compares a value to a NULL value |
IS |
The IS operator IS similar to = |
IS NOT |
IS NOTThe operator and! = similar |
|| |
Concatenate two different strings to get a new string |
UNIQUE |
The UNIQUE operator searches each row in the specified table to ensure uniqueness (no duplicates) |
where
Select * from employee where salary >= 65000; Select * from salary where salary >= 65000; To filter the data
and/or
And is equivalent to logic and operation. The result is true only if all conditions are true. Or is equivalent to logic or operations in which the result is true if one of the conditions is true.
LIKE
The LIKE operator is the text value used to match the wildcard specified pattern. If the search expression matches the pattern expression, the LIKE operator returns true, which is 1. There are two wildcards used with the LIKE operator:
- Percent sign (%)
- Underscore (_)
A percent sign (%) represents zero, one, or more digits or characters. The underscore (_) represents a single number or character. These symbols can be used in combination.
Here are some examples of how a LIKE clause with the ‘%’ and ‘_’ operators differs:
| statements | describe |
|---|---|
| WHERE SALARY LIKE ‘200%’ | Find any value starting with 200 |
| WHERE SALARY LIKE ‘%200%’ | Find any value that contains 200 at any location |
| WHERE SALARY LIKE ‘_00%’ | Find any value where the second and third bits are 00 |
| WHERE SALARY LIKE ‘2_%_%’ | Finds any value that starts with 2 and is at least 3 characters long |
| WHERE SALARY LIKE ‘%2’ | Find any value ending in 2 |
| WHERE SALARY LIKE ‘_2%3’ | Find any value whose second digit is 2 and ends in 3 |
| WHERE SALARY LIKE ‘2___3’ | Find any value that is 5 digits long and starts with 2 and ends with 3 |
GLOB
The GLOB operator is the text value used to match the wildcard specified pattern. If the search expression matches the pattern expression, the GLOB operator returns true, which is 1. Unlike the LIKE operator, GLOB is case sensitive and follows UNIX syntax for the following wildcards.
- Asterisk (*)
- Question mark
The asterisk (*) represents zero, one, or more digits or characters. Question mark Represents a single number or character. These symbols can be used in combination.
| statements | describe |
|---|---|
| WHERE SALARY GLOB ‘200*’ | Find any value starting with 200 |
| WHERE SALARY GLOB ‘200‘ | Find any value that contains 200 at any location |
| WHERE SALARY GLOB ‘? 00 * ‘ | Find any value where the second and third bits are 00 |
| WHERE SALARY GLOB ‘2?? ‘ | Finds any value that starts with 2 and is at least 3 characters long |
| WHERE SALARY GLOB ‘*2’ | Find any value ending in 2 |
| WHERE SALARY GLOB ‘? 2 * 3 ‘ | Find any value whose second digit is 2 and ends in 3 |
| WHERE SALARY GLOB ‘2??? 3 ‘ | Find any value that is 5 digits long and starts with 2 and ends with 3 |
LIMIT
Clause is used to limit the amount of data returned by the SELECT statement
ORDER BY
Clauses are used to sort data in ascending or descending order based on one or more columns.
The basic syntax of the ORDER BY clause is as follows:
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC]; //ASC ascending sort,DESC descending sortCopy the codeGROUP BY
Clauses are used with SELECT statements to group the same data. In SELECT statements, the GROUP BY clause is placed after the WHERE clause and before the ORDER BY clause
HAVING
The clause allows you to specify conditions to filter grouped results that will appear in the final result. The WHERE clause sets conditions on selected columns, while the HAVING clause sets conditions on groups created BY the GROUP BY clause.
DISTINCT
The keyword is used with the SELECT statement to eliminate all duplicate records and get only one record. It is possible to have a situation where there are multiple duplicate records in a table. The DISTINCT keyword is particularly meaningful when extracting such records, as it only retrieves a single record, not a duplicate record.
select distinct name from company;
Copy the codeCreating a database
To operate on the database, use SQLiteOpenHelper. Since SQLiteOpenHelper is an abstract class, use the onCreate(), onUpgrade() methods to implement it
public class MyDatabase extends SQLiteOpenHelper {
/ / create a table
public static final String CreateTable_my="create Table user(" +
"id primary key, name text, sex text , age integer, password text)";
Context myContext;
/ * * * *@param context
* @paramName Name of the database *@paramFactory is used to return a custom Cursor, usually null *@paramVersion Indicates the version number of the current database, which can be used to upgrade the database */
public Mydatabase(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
myContext=context;
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
sqLiteDatabase.execSQL(CreateTable_my);/ / create a table
Toast.makeText(myContext, "Table created successfully", Toast.LENGTH_SHORT).show();
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {}}Copy the codeCreate an object for MyDatabase
Private Mydatabase Base =new Mydatabase(DatabaseActivity. This,"first.db",null,1);
Copy the code/data/data/
base.getWritableDatabase(); / / or base. GetReadableDatabase ()Copy the codeThe differences between the two methods are as follows: getWritableDatabase() returns an object that can read and write to the database and raises an exception. GetReadableDatabase () returns a database opened in read-only mode and does not raise an exception
Add a table
public class Mydatabase extends SQLiteOpenHelper {
/ / the user table
public static final String CreateTable_user="create Table user(" +
"id primary key, name text, sex text , age integer, password text)";
// Create another table, class table
public static final String CreateTable_me="create Table clazz(" +
"id primary key, className text, teacher text)";
Context myContext;
/ * * * *@param context
* @paramName Name of the database *@paramFactory is used to return a custom Cursor, usually null *@paramVersion Indicates the version number of the current database, which can be used to upgrade the database */
public Mydatabase(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
myContext=context;
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
sqLiteDatabase.execSQL(CreateTable_user);
sqLiteDatabase.execSQL(CreateTable_me);
Toast.makeText(myContext, "Database created successfully", Toast.LENGTH_SHORT).show();
}
// When the value in version changes, this method is called, which deletes the original table and calls onCreate() to generate two tables
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
sqLiteDatabase.execSQL("drop table if exists user");
sqLiteDatabase.execSQL("drop table if exists clazz");
onCreate(sqLiteDatabase);
Toast.makeText(myContext, "Success", Toast.LENGTH_SHORT).show(); }}Copy the codeThe operating table
- Add data
// Get SQLiteOpenHelper from getWritableDatabase() to manipulate the database
SQLiteDatabase database= base.getWritableDatabase();
ContentValues values=new ContentValues();
values.put("name"."Xiao Ming");
values.put("sex"."Male");
values.put("password"."12hjfgikldgislk");
values.put("age".18);
Name of the table 2. Used to automatically assign NULL to some nullable columns without specifying to add data
/ / 3. ContentValues object
database.insert("user".null,values);
Copy the code- Update the data
SQLiteDatabase database= base.getWritableDatabase();
ContentValues values=new ContentValues();
values.put("name"."Little army");
// The last two parameters are the constraints of the operation
database.update("user",values,"age=?".new String[]{"18"});
Copy the code- Delete the data
SQLiteDatabase database= base.getWritableDatabase();
// The last two parameters are operation constraints that restrict which rows to delete, or all rows if not specified
database.delete("user".null.null);
Copy the code- Query data
SQLiteDatabase database=base.getWritableDatabase();
Cursor cursor= database.query("user".null.null.null.null.null.null);
if (cursor.moveToFirst()){
do{
Log.d("name:",cursor.getString(cursor.getColumnIndex("name")));
Log.d("age:",cursor.getString(cursor.getColumnIndex("age")));
Log.d("sex:",cursor.getString(cursor.getColumnIndex("sex")));
Log.d("password:",cursor.getString(cursor.getColumnIndex("password")));
}while (cursor.moveToNext());
}
cursor.close();
Copy the codeThe query parameters are as follows:
- Use SQL to operate directly on the database
SQLiteDatabase database=base.getWritableDatabase();
database.execSQL();
database.rawQuery();// This method is called only when data is queried
Copy the codeModelled on theLitePalTo achieve a simple database frameworkSimpleDatabase
SimpleDatabaseThe use of
- In the first
assetCreate in filemy_database.xml
My_database. XML is as follows:
<?xml version="1.0" encoding="UTF-8" ? >
<database name="test.db" version="1">
<! The class attribute is the full path of the data table Bean.
<table class="com.example.mylibrary.Employee"/>
</database>
Copy the codeThe source code for Employee is shown below
public class Employee {
private int id;
private String name;
private char sex;
public int getId(a) {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName(a) {
return name;
}
public void setName(String name) {
this.name = name;
}
public char getSex(a) {
return sex;
}
public void setSex(char sex) {
this.sex = sex;
}
@Override
public String toString(a) {
// Use id as a unique identifier for an employee
returnInteger.toString(id); }}Copy the codeNote: SimpleDatabase uses toString to distinguish whether two objects are the same object, using id as the identifier for Employee, for example.
- in
AndroidManifest.xmladdandroid:name="com.example.databaselibrary.MyApplication"
<application
.
android:name="com.example.databaselibrary.MyApplication"
>
Copy the code- use
SimpleDatabase
public class MainActivity extends AppCompatActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {
SimpleDatabase.newInstance().create();/ / initialization
Employee employee =new Employee();
employee.setId(1);
employee.setName("a");
employee.setSex('male');
SimpleDatabase.saveAndUpdate(employee);
Employee employee1 =new Employee();
employee1.setId(2);
employee1.setName("b");
employee1.setSex('male');
SimpleDatabase.saveAndUpdate(employee1);
Employee employee2 =new Employee();
employee2.setId(3);
employee2.setName("c");
employee2.setSex('woman');
SimpleDatabase.saveAndUpdate(employee2);
List<Employee> l=SimpleDatabase.select(Employee.class,null.null.null.null.null.null);
for (int i = 0; i <l.size() ; i++) {
Employee e=l.get(i);
Log.d("= = = = = = = = = = = = = = =",e.getName());
Log.d("= = = = = = = = = = = = = = =",e.getSex()+"");
Log.d("= = = = = = = = = = = = = = =",e.getId()+""); }}}Copy the codeRealize the principle of
First read the configuration information to get the database and table information
/** * parse the XML file */
public class XMLParser {
private final static String RESOURCES="my_database.xml";// Configure the XML name for the database information
private final static String TABLE="table";// XML attribute constants
final static String VERSION="version";// XML attribute constants
final static String DATABASE="database";// XML attribute constants
private final static String NAME="name";// XML attribute constants
private Context context;
private Map<String,String> map=null;// To store database information
private List<String> tables=null;// Store table information
public XMLParser(a){
init();
}
private void init(a){
context=MyApplication.getContext();
map=new HashMap<>(2);
tables=new ArrayList<>();
}
// Parse the data
public void parse(a) throws IOException, XmlPullParserException {
XmlPullParserFactory factory=XmlPullParserFactory.newInstance();
XmlPullParser xmlPullParser=factory.newPullParser();
// Read my_database.xml from the asset file
xmlPullParser.setInput(new InputStreamReader(context.getAssets().open(RESOURCES)));
int type=xmlPullParser.getEventType();
while(type! =XmlPullParser.END_DOCUMENT){if (xmlPullParser.getEventType()==XmlResourceParser.START_TAG){// If it is the start tag
String name=xmlPullParser.getName();
switch (name){
case DATABASE:// Tag
parseTable(xmlPullParser);
break;
}
}
xmlPullParser.next();// Next tagtype=xmlPullParser.getEventType(); }}// Parse database information
private void parseDatabase(XmlPullParser xmlPullParser) {
String databaseName=null;
String version=null;
if (xmlPullParser.getAttributeCount()==2){
String value_1=xmlPullParser.getAttributeName(0);
if (NAME.equals(value_1)){
databaseName=xmlPullParser.getAttributeValue(0);
version=xmlPullParser.getAttributeValue(1);
}else {
databaseName=xmlPullParser.getAttributeValue(1);
version=xmlPullParser.getAttributeValue(0); }}else{
throw new MyException("Wrong parameter for database tag");
}
map.put(DATABASE,databaseName);
map.put(VERSION,version);
}
// Parse the table information
private void parseTable(XmlPullParser xmlPullParser) {
String className=null;
if (xmlPullParser.getAttributeCount()==1){
className=xmlPullParser.getAttributeValue(0);
}else
throw new MyException("Table parameter error");
tables.add(className);
}
public Map<String, String> getMap(a) {
return map;
}
public List<String> getTables(a) {
returntables; }}Copy the codeClass to create a database
public class MyDatabase extends SQLiteOpenHelper {
private onDatabaseUpdateListener listener=null;
private static final String TAG = "MyDatabase";
public MyDatabase(Context context, String name, SQLiteDatabase.CursorFactory factory, int version,onDatabaseUpdateListener listener) {
super(context, name, factory, version);
this.listener=listener;
}
@Override
public void onCreate(SQLiteDatabase db) {
String[] createTables = listener.onCreate();
for (String s: createTables){
db.execSQL(s);
Log.d("====== build predicative sentences",s);
}
Log.d("= = = = = ="."OnCreate execution");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
String[] deleteTable = listener.update(db);
Log.d("= = = = = ="."OnUpgrade execution");
if(deleteTable ! =null) {for (String s: deleteTable){
db.execSQL(s);
Log.d("===== delete table sentences",s);
}
}
onCreate(db);
listener.onCreateLater(db);
}
interface onDatabaseUpdateListener{
String[] update(SQLiteDatabase db);// Called when database version is updated
String[] onCreate();// called when a new table is created
void onCreateLater(SQLiteDatabase db);// called when the table is created}}Copy the codeAn implementation class that completes database operations
/** * create a database */
public class SimpleDatabase implements MyDatabase.onDatabaseUpdateListener{
private final static String NAME="SimpleDatabase.xml";
private final static String OLD="old";
private final static String TABLE="table_";
private final static String NUMBER="number";
MyDatabaseHelper is a helper class that generates the data needed to create databases and tables
private static MyDatabaseHelper databaseHelper=null;
private static SQLiteDatabase db=null;
private Map<String,Cursor> savedData=null;
String simpleNames[]=null;
private static final String TAG = "SimpleDatabase";
private static SimpleDatabase simpleDatabase=new SimpleDatabase();
public SimpleDatabase(a){
init();
}
private void init(a){
databaseHelper=new MyDatabaseHelper();
}
/** * query the specified data */
public static<T> List<T> select(Class
clazz,String columnNames[],String where, String args[],String groupBy, String having, String orderBy)
{
List<T> list = new ArrayList<>();
Cursor cursor= db.query(clazz.getSimpleName(),columnNames,where,args,groupBy,having,orderBy);
while(cursor.moveToNext()){
try {
T t = clazz.newInstance();
Field fields[]=clazz.getDeclaredFields();
for (Field f:fields) {
f.setAccessible(true);
String fieldName = f.getName();
String fieldValue = cursor.getColumnName(cursor.getColumnIndex(fieldName));
// Since getColumnName () only returns String, getInitialTypeValue() is required
// Get the value of the initial type
f.set(t,getInitialTypeValue(f.getType().getSimpleName(),fieldValue));
}
list.add(t);
} catch (InstantiationException | IllegalAccessException e) {
e.printStackTrace();
}
}
cursor.close();
return list;
}
private static Object getInitialTypeValue(String type,String value){
switch (type){
case "int":
case "integer":
return Integer.valueOf(value);
case "boolean":
return Boolean.valueOf(value);
case "float":
return Float.valueOf(value);
case "double":
return Double.valueOf(value);
case "String":
case "Character":
case "char":
return value;
}
return null;
}
/** * query the specified data */
private static Cursor select(Object obj,String columnNames[],String where,String args[]){
String tableName=obj.getClass().getSimpleName();
return db.query(tableName,columnNames,where,args,null.null.null);
}
/** * create a database if it does not exist, or end it if it already exists
public void create(a){
String name=databaseHelper.getName();
String version=databaseHelper.getVersion();
if (databaseHelper.check(getOldVersion(),Integer.valueOf(version)))// Update if needed
saveDataInSharedPreferences(Integer.valueOf(version));
Log.d("= = = = = = = = ="."name"+name);
MyDatabase database = new MyDatabase(MyApplication.getContext(), name, null, Integer.valueOf(version), SimpleDatabase.this);
db= database.getWritableDatabase();
}
public static SimpleDatabase newInstance(a) {
return simpleDatabase;
}
/** * Store batch data *@param list
* @throws IllegalAccessException
*/
public static void save(List<Object> list) {
for(Object o:list) { save(o); }}/** * Store single data in table *@param o
* @throws IllegalAccessException
*/
public static void save(Object o) {
Class clazz=o.getClass();
Field fields[]=clazz.getDeclaredFields();
ContentValues values=new ContentValues();
values.put("simple_database_id",o.toString());
for (Field f:fields) {
try {
f.setAccessible(true);
if(f.get(o)! =null&&!"serialVersionUID".equals(f.getName())) {
values.put(f.getName(), f.get(o).toString());
Log.d("========value", f.get(o).toString()); }}catch (IllegalAccessException e) {
e.printStackTrace();
Log.wtf(TAG,"All data in the class should be set to values.");
}
}
db.insert(clazz.getSimpleName(),null,values);
}
public static void saveAndUpdate(Object o){
Class clazz=o.getClass();
String id=o.toString();
Field fields[]=clazz.getDeclaredFields();
ContentValues values=new ContentValues();
values.put("simple_database_id",o.toString());
for (Field f:fields) {
try {
f.setAccessible(true);
if(f.get(o)! =null&&!"serialVersionUID".equals(f.getName())) {
values.put(f.getName(), f.get(o).toString());
Log.d("========value", f.get(o).toString()); }}catch (IllegalAccessException e) {
e.printStackTrace();
Log.wtf(TAG,"All data in the class should be set to values.");
}
}
Cursor cursor=select(o,null."simple_database_id=?".new String[]{id});
if (cursor.getCount()==0) {/ / insert
db.insert(clazz.getSimpleName(),null,values);
}else {/ / update
db.update(clazz.getSimpleName(),values,"simple_database_id=?".newString[]{id}); }}/** * drop all data from table *@param o
*/
public static void delete(Object o){
Class clazz=o.getClass();
delete(clazz.getSimpleName(),"simple_database_id=?",o.toString());
}
/** * drop the specified data * from the table@param name
* @param where
* @param arg
*/
private static void delete(String name,String where,String... arg){
db.delete(name,where,arg);
}
/** * Stores the latest version * if the version is updated@paramVersion Version */
private static void saveDataInSharedPreferences(int version){
// Get the Editor object for SharedPreferences to perform the save operation
SharedPreferences.Editor editor=MyApplication.getContext().getSharedPreferences(NAME,0).edit();
editor.putInt(OLD,version);
editor.apply();// This method must be called to complete the data storage
}
/** * Store the past table name *@param names
*/
private void saveDataInSharedPreferences(String[] names){
// Get the Editor object for SharedPreferences to perform the save operation
SharedPreferences.Editor editor=MyApplication.getContext().getSharedPreferences(NAME,0).edit();
for (int i=0; i<names.length; i++){ editor.putString(TABLE+i,names[i]); } editor.putInt(NUMBER,names.length); editor.apply();// This method must be called to complete the data storage
}
/** * Get the last version of the database *@return* /
private static int getOldVersion(a){
SharedPreferences get=MyApplication.getContext().getSharedPreferences(NAME,0);
return get.getInt(OLD,0);
}
private String[] getOldTableName(){
SharedPreferences get=MyApplication.getContext().getSharedPreferences(NAME,0);
int length=get.getInt(NUMBER,-1);
if (length==-1)
Log.wtf(TAG,"Original form does not exist");
String names[]=new String[length];
for (int i=0; i<length; i++){ names[i]=get.getString(TABLE+i,"");
}
return names;
}
@Override
public String[] update(SQLiteDatabase db) {// Save the table data before dropping it
simpleNames=getOldTableName();
savedData=new HashMap<>(simpleNames.length);
for (String name:simpleNames) {
Cursor cursor=db.query(name,null.null.null.null.null.null);
savedData.put(name,cursor);
}
Cursor cursor=savedData.get(simpleNames[0]);
if (cursor.moveToFirst()){
String sex=cursor.getString(cursor.getColumnIndex("sex"));
Log.d("===============update"."sex="+sex);
}
return databaseHelper.getDeleteTable();
}
@Override
public String[] onCreate() {
saveDataInSharedPreferences(databaseHelper.getSimpleTableName());
return databaseHelper.getCreateTable();
}
@Override
public void onCreateLater(SQLiteDatabase db) {
recoverAllData(db);
}
/** * Restore all data */
private void recoverAllData(SQLiteDatabase db){
List<String> deleteTable=checkWhichTableDisappear(databaseHelper.getTables());
List<String> nowTable=Arrays.asList(simpleNames);
nowTable.remove(deleteTable);
for (int i=0; i<nowTable.size(); i++){ Cursor cursor=savedData.get(nowTable.get(i)); ContentValues values=new ContentValues();
if (cursor.moveToFirst()){
do{
String columnNames[]=cursor.getColumnNames();
for (int j=0; j<columnNames.length; j++) values.put(columnNames[j],cursor.getString(cursor.getColumnIndex(columnNames[j]))); }while (cursor.moveToNext());
db.insert(nowTable.get(i),null,values); }}for (String n:simpleNames) {// Release all resourcessavedData.get(n).close(); }}/** * check which tables are deleted *@param newTable
* @return* /
private List<String> checkWhichTableDisappear(List<String> newTable){
String deleteTable[]=new String[simpleNames.length];
for (int i=0,j=0; i<simpleNames.length; i++){if (!newTable.contains(simpleNames[i])){
deleteTable[j]=simpleNames[i];
j++;
}
}
returnArrays.asList(deleteTable); }}Copy the codeThe main purpose of SimpleDatabase is to update the database automatically when configuration changes in my_database.xml; Insert and update, through saveAndUpdate(Object O) using objects to achieve insert and update operations (when the same data does not exist in the database, insert; Update when the same data exists in the database); When querying, pass
List
select(Class
clazz,String columnNames[],String where,String args[],String groupBy, String having, String orderBy) gets a collection of query result objects; When deleting an Object, use delete(Object O) to delete the Object.
Implementation principle:
SimpleDatabase listens to MyDatabase’s onCreate and onUpgrade methods by implementing MyDatabase’s onDatabaseUpdateListener interface. In onCreate is invoked, call onDatabaseUpdateListener. OnCreate to store before the table name (if changed the configuration file), and return to create table SQL statement collection (may create multiple tables), Then create the table in myDatabase.oncreate. When onUpgrade is invoked, call onDatabaseUpdateListener. Update to store the data in the database, and returns the SQL delete table set, delete after the success to create a new table, Called after onDatabaseUpdateListener. OnCreateLater method before storing the data stored in the database again.
The Select, DELETE, and saveAndUpdate methods in SimpleDatabase are implemented by reflection, as you can see in the comments.
Other class implementation is very simple, specific can see the source code:
- MyApplication class
/** * get the system context */
public class MyApplication extends Application {
private static Context context;
@Override
public void onCreate(a) {
super.onCreate();
context = getApplicationContext();
}
public static Context getContext(a){
returncontext; }}Copy the code- MyDatabaseHelper class
/** * Generate the data needed to create the database and tables */
public class MyDatabaseHelper {
private String name=null;
private String version=null;
private List<String> tables=null;// Store the full class name
private Map<String,Table[]> maps=null;
private String createTable[]=null;// Store the table builder
private String deleteTable[]=null;// Store the statement to drop the table
private boolean ok=false;
private static final String TAG = "MyDatabaseHelper";
public MyDatabaseHelper(a){
init();
}
/** * Initializes data */
private void init(a){
XMLParser xmlParser=null;
xmlParser=new XMLParser();
try {
xmlParser.parse();
} catch (IOException | XmlPullParserException e) {
e.printStackTrace();
}
name=xmlParser.getMap().get(XMLParser.DATABASE);
version=xmlParser.getMap().get(XMLParser.VERSION);
tables=xmlParser.getTables();
maps=new HashMap<>(tables.size());
}
/** * Check if you need to update *@paramPrevious versions of old *@paramNow The current version */
public boolean check(int old,int now){
if (now>old) {
try {
parseTable();
ok=true;
return true;
} catch(ClassNotFoundException e) { e.printStackTrace(); }}return false;
}
/** * Parses the class's data, calling * when version changes@throws ClassNotFoundException
*/
private void parseTable(a) throws ClassNotFoundException {
for (String name:tables){
Class table=Class.forName(name);
Field[] field=table.getDeclaredFields();
Table info[]=new Table[field.length];
for (int i=0; i<field.length; i++){ Table t=newTable(); t.setProperty(field[i].getName()); t.setType(field[i].getType().getSimpleName()); info[i]=t; } maps.put(name,info); }}/** * generates a construction clause */
private void generateTable(a){
for (int i=0; i<tables.size(); i++){ Table table[]=maps.get(tables.get(i)); StringBuilder stringBuilder=new StringBuilder();
String simpleName=getSimpleName(tables.get(i));
stringBuilder.append("create table "+simpleName+"(");
for (int j=0; j<table.length; j++){ Table t=table[j];if(t! =null)
if(! Table.OTHER.equals(t.getType())) stringBuilder.append(","+t.getProperty()+""+t.getType());
}
String string=stringBuilder.append(")").toString();
string=string.replaceFirst(",".""); createTable[i]=string; }}/** * generate table drop statement */
private void deleteTable(a) {
for (int i = 0; i < tables.size(); i++) {
deleteTable[i]="drop table if exists "+getSimpleName(tables.get(i)); }}/** * gets the simple class name, excluding package *@paramName Class name with package name *@returnClass name */ that does not contain the package name
private String getSimpleName(String name){
int position= name.lastIndexOf('. ');
return name.substring(position+1);
}
public String getName(a) {
return name;
}
public String getVersion(a) {
return version;
}
public String[] getCreateTable() {
if(! ok) Log.e(TAG,"Check () must be called first");
createTable=new String[tables.size()];
generateTable();
return createTable;
}
public String[] getDeleteTable() {
if(! ok) Log.e(TAG,"Check () must be called first");
deleteTable=new String[tables.size()];
deleteTable();
return deleteTable;
}
public List<String> getTables(a) {
return tables;
}
public String[] getSimpleTableName() {
String simpleTableName[]=new String[tables.size()];
for (int i=0; i<tables.size(); i++) { String simpleName = getSimpleName(tables.get(i)); simpleTableName[i] = simpleName; }returnsimpleTableName; }}Copy the code- MyException
public class MyException extends RuntimeException {
public MyException(String message) {
super(message); }}Copy the code- The Table class
/** * Stores the attribute and name for each field */
public class Table {
final static String INTEGER="integer";
final static String TEXT="text";
final static String REAL="real";
final static String BLOB="blob";
final static String INT="int";
final static String CHAR="char";
final static String FLOAT="float";
final static String DOUBLE="double";
final static String STRING="String";
final static String BOOLEAN="boolean";
final static String OTHER="other";
private String property;// The corresponding attribute
private String type;// The type of the corresponding attribute
public String getProperty(a) {
return property;
}
public void setProperty(String property) {
this.property = property;
}
public String getType(a) {
return type;
}
public void setType(String type) {
checkProperty(type);
}
private void checkProperty(String property){
switch (property){
case INT:
case BOOLEAN:
type=INTEGER;
break;
case FLOAT:
case DOUBLE:
type=REAL;
break;
case STRING:
case CHAR:
type=TEXT;
break;
default:
type=OTHER;
break; }}}Copy the codeRefer to the rookie tutorial