Getting started with Android (XI) SQLite curd

Original link: http://www.orlion.ga/594/

1、 Add data

The getreadabledatabase () or getwritabledatabase () methods of sqliteopenhelper can be used to create and upgrade databases. In addition, both methods will return a sqlitedatabase object, which can be used to crud data.

Sqlitedatabase provides an insert () method, which receives three parameters. The first parameter is the table name, and the second parameter is used to automatically assign null to some nullable columns without specifying the data to be added. Generally, we do not use this function. We can directly pass in null. The third parameter is a contentvalues object, which provides a series of put () method overloads to add data to contentvalues. You only need to pass in each column name in the table and the corresponding data to be added.

First modify the activity_ Add a button to main.xml, and then modify mainactivity.java:

        @Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		dbHelper = new MyDatabaseHelper(this , "BookStore.db" , null , 2);
		Button button = (Button) findViewById(R.id.create_database);
		button.setOnClickListener(new View.OnClickListener() {
			
			@Override
			public void onClick(View v) {
				dbHelper.getWritableDatabase();
			}
		});
		
		Button addData = (Button) findViewById(R.id.add_data);
		addData.setOnClickListener(new View.OnClickListener() {
			
			@Override
			public void onClick(View v) {
				sqliteDatabase db = dbHelper.getWritableDatabase();
				ContentValues values = new ContentValues();
				
				values.put("name", "Book");
				values.put("author" , "orlion");
				values.put("pages", 9);
				values.put("price", 15.1);
				
				db.insert("book", null, values);
				
				values.clear();
				
				values.put("name", "Book2");
				values.put("author", 9);
				values.put("price" , values);
			}
		});
	}

In the click event of the add data button, we first get the sqlitedatabase object, and then use contentvalues to assemble the data to be added. If you are more careful, you should find that only the data of four columns in the book table are assembled here, and the ID column is not assigned a value to it. This is because when we created the table earlier, we set the ID column to self growth, and its value will be automatically generated when warehousing, so we don't need to assign it manually. Next, the insert () method is called to add the data to the table. Note that we actually added two pieces of data here. In the above code, contentvalues is used to assemble different contents twice, and the insert () method is called twice.

2、 Update data

Sqlitedatabase also provides a very useful update () method for updating data. This method receives four parameters. The first parameter, like the insert () method, is also a table name. Here, it specifies which table data to update. The second parameter is the contentvalues object, where the updated data is assembled. The third and fourth parameters are used to constrain the updating of data in a row or rows. If not specified, all rows will be updated by default.

Use in the project, first in activity_ Add a button to main.xml:

    <Button
        android:id="@+id/update_data"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="更新数据" />

Then modify mainactivity. Java:

        @Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		dbHelper = new MyDatabaseHelper(this , 2);
		Button button = (Button) findViewById(R.id.create_database);
		button.setOnClickListener(new View.OnClickListener() {
			
			@Override
			public void onClick(View v) {
				dbHelper.getWritableDatabase();
			}
		});
		Button addData = (Button) findViewById(R.id.add_data);
		addData.setOnClickListener(new View.OnClickListener() {
			
			@Override
			public void onClick(View v) {
				sqliteDatabase db = dbHelper.getWritableDatabase();
				ContentValues values = new ContentValues();
				
				values.put("name", values);
			}
		});
		
		Button updateData = (Button) findViewById(R.id.update_data);
		updateData.setOnClickListener(new View.OnClickListener() {
			
			@Override
			public void onClick(View v) {
				sqliteDatabase db = dbHelper.getWritableDatabase();
				ContentValues values = new ContentValues();
				values.put("price", 19.0);
				
				db.update("book", values, "name = ?", new String[] {"Book"});
			}
		});
	}

In this way, when you click the update data button, the price of "book" will be changed to 19.0

3、 Delete data

                Button deleteData = (Button) findViewById(R.id.delete_data);
		deleteData.setOnClickListener(new View.OnClickListener() {
			
			@Override
			public void onClick(View v) {
				sqliteDatabase db = dbHelper.getWritableDatabase();
				db.delete("book", new String[] {"Book"});
			}
		});

Here we add a delete button to delete the book with name = book.

4、 Query data

Sqlitedatabase also provides a query () method to query the data. The parameters of this method are very complex. The shortest method overload also needs to pass in seven parameters. Let's take a look at the meanings of these seven parameters first. Needless to say, the first parameter is of course the table name, indicating which table we want to check from

Query data. The second parameter is used to specify which columns to query. If not specified, all columns will be queried by default. The third and fourth parameters are used to query the data of a row or rows. If they are not specified, the data of all rows will be queried by default. The fifth parameter is used to specify the column to be group by. If it is not specified, it means that the query result will not be operated by group by. The sixth parameter is used to further filter the data after group by. If it is not specified, it means that no filtering will be performed. The seventh parameter is used to specify the sorting method of query results. If it is not specified, it means that the default sorting method is used. In fact, the overloads of other query () methods are similar.

After calling the query () method, a cursor object will be returned, and all the data queried will be taken out of this object.

Next, we add a query button and bind it to events to fetch data:

                Button queryData = (Button) findViewById(R.id.query_data);
		queryData.setOnClickListener(new View.OnClickListener() {
			
			@Override
			public void onClick(View v) {
				sqliteDatabase db = dbHelper.getWritableDatabase();
				Cursor cursor = db.query("book", null);
				if (cursor.moveToFirst()) {
					do {
						String name = cursor.getString(cursor.getColumnIndex("name"));
						String author = cursor.getString(cursor.getColumnIndex("author"));
						int pages = cursor.getInt(cursor.getColumnIndex("pages"));
						double price =  cursor.getDouble(cursor.getColumnIndex("price"));
						
						Log.d("databasedemo", "name = " + name);
						Log.d("databasedemo", "author = " + author);
						Log.d("databasedemo", "pages = " + pages);
						Log.d("databasedemo", "price = " + price);
					} while (cursor.moveToNext());
				}
				
				cursor.close();
			}
		});

5、 Operating database with SQL

The method of adding data is as follows:

db.execsql("insert into Book (name, author, pages, price) values(?, ?, ?)",new String[] { "The Da Vinci Code", "Dan Brown", "454", "16.96" });
db.execsql("insert into Book (name,new String[] { "The Lost Symbol", "510", "19.95" });

The method of updating data is as follows:

db.execsql("update Book set price = ? where name = ?", new String[] { "10.99","The Da Vinci Code" });

The method of deleting data is as follows:

db.execsql("delete from Book where pages > ?", new String[] { "500" });

The method of querying data is as follows:

db.rawQuery("select * from Book", null);