Basic use of sqlite

Basic use of sqlite
I. Basic operation 1 inherits SQLiteOpenHelper

public class UserSqliteOpenHelper extends SQLiteOpenHelper {      private static final int versionNo = 1;      public UserSqliteOpenHelper(Context context) {            super(context, "user.db", null, versionNo);     }      @Override     public void onCreate(SQLiteDatabase db) {           Log. i("" , "sqlite onCreate" );           db.execSQL( "create table user (id Integer primary key autoincrement, name varchar(20))");     }      @Override     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {           Log. i("" , "sqlite onUpgrade" );           db.execSQL( "insert into user(name) values(?)", new Object[]{"version"+versionNo });     } }

 

From the code, we can see that UserSqliteOpenHelper has rewritten the onCreate and onUpgrade methods of SQLiteOpenHelper. Description: onCreate: called when the database is created. Generally, the onUpgrade statement is executed ( VersionNoThis method is generally used to change, add, or delete the table structure. * onCreate is executed when the database is created for the first time, and onUpgrade is not executed. After the version number is changed, onUpgrade is executed, instructions on not executing onCreate operations: When private  Static  Final  Int  VersionNoThe result is as follows: When private  Static  Final  Int  VersionNo= 2. The result is as follows. 2. Create the dao file to operate the sqlite database.

public class UserDao {     private UserSqliteOpenHelper tsoh;          public UserDao(Context context) {            tsoh = new UserSqliteOpenHelper(context);     }          public void insert(String name){           SQLiteDatabase db = tsoh.getWritableDatabase();           db. execSQL("insert into user(name) values(?)", new Object[]{name});           db.close();     }          public boolean find(String name){           SQLiteDatabase db = tsoh.getReadableDatabase();           Cursor cursor = db.rawQuery( "select * from user where name = ?", new String[]{name});            boolean result = cursor.moveToNext();           cursor.close();           db.close();            return result;     }          public void update(String name, String newName){           SQLiteDatabase db = tsoh.getWritableDatabase();           db. execSQL("update user set name = ? where name = ?", new Object[]{newName, name});           db.close();     }          public void delete(String name){           SQLiteDatabase db = tsoh.getWritableDatabase();           db. execSQL("delete from user where name = ?", new Object[]{name});           db.close();     }          public List<User> findAll(){           List<User> users = new ArrayList<User>();           SQLiteDatabase db = tsoh.getReadableDatabase();           Cursor cursor = db.rawQuery( "select * from user", null);            while(cursor.moveToNext()){                 int id = cursor.getInt(cursor.getColumnIndex("id" ));                String name = cursor.getString(cursor.getColumnIndex("name" ));                User user = new User(id, name);                users.add(user);           }           cursor.close();           db.close();            return users;     } }

 

User class definition

public class User {      private int id;     private String name;          public User() {            super();     }          public User(int id, String name) {            super();            this. id = id;            this. name = name;     }          public int getId() {            return id;     }     public void setId(int id) {            this. id = id;     }     public String getName() {            return name;     }     public void setName(String name) {            this. name = name;     }     @Override     public String toString() {            return "info:"+ id+ ","+ name;     }}

 

The code is very simple and I will not explain it too much. Note that you must close cursor and database every time you operate the database to release resources. Use try catch finally to operate project-level code. Here we still use the String concatenation method to execute SQL statements. In fact, android also provides encapsulated APIs for US (and finally concatenates parameters into strings, but for callers, the new API is simple and clear, so you don't have to worry about the single quotation marks and double quotation marks. 3. Call Code

@Override    protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout. activity_main);               UserDao testDao = new UserDao( this);        testDao.insert( "name1");        testDao.insert( "name2");        testDao.insert( "name3");               testDao.update( "name1", "name4");               boolean result = testDao.find("name2" );               testDao.delete( "name3");               List<User> users = testDao.findAll();        for(User user:users){           Log. i("MainActivity" , user.toString());        }    }

 

The basic operation is complete. Ii. Recommended sqlite interface tool: a colleague from two days ago from sqlite expert professional asked me how many Integer values can store in sqlite. I didn't know at the time, so I tested the test result on the spot:-9223372036854775808 ~ 9223372036854775807-2 ^ 63 to 2 ^ 63-1. This is the result of the sqlite tool experiment.

INTEGER: used to store an INTEGER. It can be stored in 1, 2, 3, 4, 6, or 8 bytes based on the size (Network transcription, from the perspective of my experiment results, the upper limit is an INTEGER of 8 bytes)