Set up and use databases in Android and databases in Android

Set up and use databases in Android and databases in Android

Hello everyone, I wrote an article about how to use the ormlite database a few days ago. It is very convenient and easy to build for the database, because the underlying SQL language for the database has been encapsulated into a method, It is very convenient to avoid errors and use them. However, for those who are new to the database, it can be used directly without figuring out the principle. It is obvious that the disadvantage of acceptance is greater than the advantage, so today, I will bring you together to build a database without using a third-party jar package and encapsulate a class that operates the database, so that you can get a basic understanding of the composition and use of the android database. Okay. Now let's go to the topic.

I simply set up a database for storing News. I think everyone should create the News Attribute Class (that is, define several attributes and construct them with parameters, no parameter construction plus get and set methods), I am not wasting time on this. Before you build a database, you should write this attribute class first, after the setup, follow the steps below:

Step 1: Create a java class and inherit from SQLiteOpenHelper

1. In this class, we need to declare the Database Name and database version number.

1 private static final String DB_NAME = "NEWS. DB"; // database name 2 private static final int DB_VERSION = 1; // database version number

2. We also need to use the java method to overload and define three onCreate methods with parameters and rewrite them to create database tables.

1 public NewsSQLiteOpenHelper (Context context) {2 super (context, DB_NAME, null, DB_VERSION); 3} 4 // version database version number context Context name database name 5 public NewsSQLiteOpenHelper (context Context context, string name, SQLiteDatabase. cursorFactory factory, int version) {6 super (context, name, factory, version); 7} 8 public NewsSQLiteOpenHelper (Context context, String name, SQLiteDatabase. cursorFactory factory, int version, DatabaseErrorHandler errorHandler) {9 super (context, name, factory, version, errorHandler); 10} 11 @ Override12 public void ** onCreate ** (SQLiteDatabase db) {13 // obtain SQL14 String netNewsSQL = Constant. newNewsTable. getCreatNetNewsSQL (); 15 // Z: Execute SQL16 db.exe cSQL (netNewsSQL); 17}

3. Override the update database method of the parent class

1 @Override2 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {3     db.execSQL("drop table "+ Constant.NewNewsTable.TBL_NAME);4     onCreate(db);5 }

So far, even if the database is set up almost, the next step is to create a table.

Step 2: Create a table. After the database is set up, add a table to the database. It is mainly used to define a static generation method using the database language.

It must be noted that the uppercase and lowercase letters and spaces between words are used when the database language is generated (this is also the most error-prone place in database construction, so you must note when writing), the Code is as follows:

 1 public static final class NewNewsTable { 2     public static final String TBL_NAME = "NETNEWS"; 3     public static final String COL_ID = "ID"; 4     public static final String COL_TITLE = "TITLE"; 5     public static final String COL_CTIME = "CTIME"; 6     public static final String COL_PICURL = "PICURL"; 7     public static final String COL_URL = "URL"; 8     public static final String COL_DESCRIPTION = "DESCRIPTION"; 9     public static final String COL_NEWSTYPE = "NEWSTYPE";10 11     public static String getCreatNetNewsSQL() {12         String sql = "CREATE TABLE IF NOT EXISTS "13                 + TBL_NAME + "(" + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"14                 + COL_TITLE + " VARCHAR(200),"15                 + COL_CTIME + " VARCHAR(50),"16                 + COL_PICURL + " VARCHAR(200),"17                 + COL_URL + " VARCHAR(200),"18                 + COL_DESCRIPTION + " TEXT,"19                 + COL_NEWSTYPE + " integer"20                 + ")";21         return sql;22     }23 }

After the database and attribute classes are complete, we need to provide an operation class to operate the database, so the last step is to define an operation class.

Step 3: Create an operation class to provide the addition, deletion, modification, and query functions for the Operation database.

In order to facilitate your understanding, I will write these functions separately:

1. The method for writing a piece of data into the database and the method for writing a set of data into the database:

Add one:

 1 public long addNetNews(NetNews nn){ 2    SQLiteDatabase db = this.openHelper.getReadableDatabase(); 3    ContentValues values = new ContentValues(); 4    values.put(Constant.NewNewsTable.COL_TITLE,nn.getTitle()); 5    values.put(Constant.NewNewsTable.COL_DESCRIPTION,nn.getDescription()); 6    values.put(Constant.NewNewsTable.COL_CTIME,nn.getCtime()); 7    values.put(Constant.NewNewsTable.COL_PICURL,nn.getPicUrl()); 8    values.put(Constant.NewNewsTable.COL_URL,nn.getPicUrl()); 9    values.put(Constant.NewNewsTable.COL_NEWSTYPE, nn.getNewsType());10    long id = db.insert(11            Constant.NewNewsTable.TBL_NAME,12            null,13            values14    );15    db.close();16    return id;  17    }

Add a group of data (that is, use the for loop to add a data record)

1 public  void addNetNews(List<NetNews> netNewsList){2         for(NetNews nn:netNewsList){3       addNetNews(nn);4     }5 }

2. delete data in the database based on News Type

1 public void removeNewsByType (int type) {2 SQLiteDatabase DB = this. openHelper. getReadableDatabase (); 3 // Delete 4 DB according to news type. delete (5 Constant. newNewsTable. TBL_NAME, 6 Constant. newNewsTable. COL_NEWSTYPE + "=? ", 7 new String [] {type +" "} 8); 9 DB. close (); 10}

3. query data by Data Type

 1 public List<NetNews> findNewsByType(int type){ 2     List<NetNews> list = new ArrayList<>(); 3     SQLiteDatabase db = this.openHelper.getReadableDatabase(); 4     Cursor cursor = db.query( 5             Constant.NewNewsTable.TBL_NAME, 6             new String[]{ 7                     Constant.NewNewsTable.COL_ID, 8                     Constant.NewNewsTable.COL_CTIME, 9                     Constant.NewNewsTable.COL_DESCRIPTION,10                     Constant.NewNewsTable.COL_TITLE,11                     Constant.NewNewsTable.COL_PICURL,12                     Constant.NewNewsTable.COL_URL,13                     Constant.NewNewsTable.COL_NEWSTYPE14             },15             Constant.NewNewsTable.COL_NEWSTYPE+" =?",16             new String[]{type+""},17             null,18             null,19             null,20             null21     );22     while(cursor.moveToNext()){23         NetNews nn = new NetNews();24         nn.setTitle(cursor.getString(cursor.getColumnIndex(Constant.NewNewsTable.COL_TITLE)));25         nn.setCtime(cursor.getString(cursor.getColumnIndex(Constant.NewNewsTable.COL_CTIME)));26         nn.setDescription(cursor.getString(cursor.getColumnIndex(Constant.NewNewsTable.COL_DESCRIPTION)));27         nn.setPicUrl(cursor.getString(cursor.getColumnIndex(Constant.NewNewsTable.COL_PICURL)));28         nn.setUrl(cursor.getString(cursor.getColumnIndex(Constant.NewNewsTable.COL_URL)));29         nn.setNewsType(cursor.getInt(cursor.getColumnIndex(Constant.NewNewsTable.COL_NEWSTYPE)));30         list.add(nn);31     }32     cursor.close();33     db.close();34     return list;35 }

In this way, the basic operations on the database are completed. Next we will assign a complete code for the database operation class. Let's take a closer look at it:

1 public class NetNewsDao {2 private NewsSQLiteOpenHelper openHelper; 3 public NetNewsDao (Context mContext) {4 this. openHelper = new NewsSQLiteOpenHelper (mContext); 5} 6 public List <NetNews> findNewsByType (int type) {7 List <NetNews> list = new ArrayList <> (); 8 SQLiteDatabase db = this. openHelper. getReadableDatabase (); 9 Cursor cursor = db. query (10 Constant. newNewsTable. TBL_NAME, 11 new String [] {12 Constant. newNewsTable. COL_ID, 13 Constant. newNewsTable. COL_CTIME, 14 Constant. newNewsTable. COL_DESCRIPTION, 15 Constant. newNewsTable. COL_TITLE, 16 Constant. newNewsTable. COL_PICURL, 17 Constant. newNewsTable. COL_URL, 18 Constant. newNewsTable. COL_NEWSTYPE19}, 20 Constant. newNewsTable. COL_NEWSTYPE + "=? ", 21 new String [] {type +" "}, 22 null, 23 null, 24 null, 25 null26); 27 while (cursor. moveToNext () {28 NetNews nn = new NetNews (); 29 nn. setTitle (cursor. getString (cursor. getColumnIndex (Constant. newNewsTable. COL_TITLE); 30 nn. setCtime (cursor. getString (cursor. getColumnIndex (Constant. newNewsTable. COL_CTIME); 31 nn. setDescription (cursor. getString (cursor. getColumnIndex (Constant. newNewsTable. COL_DESCRIPTION ))); 32 nn. setPicUrl (cursor. getString (cursor. getColumnIndex (Constant. newNewsTable. COL_PICURL); 33 nn. setUrl (cursor. getString (cursor. getColumnIndex (Constant. newNewsTable. COL_URL); 34 nn. setNewsType (cursor. getInt (cursor. getColumnIndex (Constant. newNewsTable. COL_NEWSTYPE); 35 list. add (nn); 36} 37 cursor. close (); 38 db. close (); 39 return list; 40} 41 public long addNetNews (NetNews nn) {42 SQLiteDatabase db = This. openHelper. getReadableDatabase (); 43 ContentValues values = new ContentValues (); 44 values. put (Constant. newNewsTable. COL_TITLE, nn. getTitle (); 45 values. put (Constant. newNewsTable. COL_DESCRIPTION, nn. getDescription (); 46 values. put (Constant. newNewsTable. COL_CTIME, nn. getCtime (); 47 values. put (Constant. newNewsTable. COL_PICURL, nn. getPicUrl (); 48 values. put (Constant. newNewsTable. COL_URL, nn. getPicUrl (); 49 values. put (Constant. newNewsTable. COL_NEWSTYPE, nn. getNewsType (); 50 long id = db. insert (51 Constant. newNewsTable. TBL_NAME, 52 null, 53 values54); 55 db. close (); 56 return id; 57} 58 public void addNetNews (List <NetNews> netNewsList) {59 for (NetNews nn: netNewsList) {60 addNetNews (nn ); 61} 62} 63 public void removeNewsByType (int type) {64 SQLiteDatabase DB = this. openHelper. getReadableDatabase (); 65 // Delete 66 DB. delete (67 Constant. newnewnewstable. TBL_NAME, 68 Constant. newnewnewstable. COL_NEWSTYPE + "=? ", 69 new String [] {type +" "} 70); 71 DB. close (); 72} 73}

Now we have built the database and written the operation class of the database. During the call, We will generate a database operation object and perform operations on the database.

The construction of the original Android database and the compilation of Operation classes are still very troublesome, but it still plays a major role in understanding the operating principles of the database and the SQL language. For Beginners, it is particularly important to consolidate the foundation, and understanding the principles of the database will greatly benefit your future work and study. Therefore, we suggest you take a look at it, after being proficient, you can use ormlite, greendao, and other Jar packages to build a database. Well, let's add this to the construction of the original Android database. If you feel that your understanding of the database is helpful, let's recommend it, if you want to correct your remarks, thank you.