android sqlitedatabase 数据库

最新推荐文章于 2022-02-09 11:53:40 发布

过瘾饺子

最新推荐文章于 2022-02-09 11:53:40 发布

阅读量427

点赞数

分类专栏： sqlite

本文链接：https://blog.csdn.net/dashuxiaoai/article/details/45395083

版权

sqlite 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文深入探讨了 Android 中如何使用 SQLite 数据库进行数据存储。从创建数据库和表，到插入、查询、更新和删除数据，详细阐述了 SQLite 的基本操作，并提供实例代码，帮助开发者更好地理解和应用 SQLite 在 Android 应用开发中的功能。

摘要由CSDN通过智能技术生成

package com.example.databasedemo;

import android.app.Activity;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;

import com.example.databasedemo.DatabaseHelper.book;

public class MainActivity extends Activity implements OnClickListener {

	Button mCreate;
	Button mAdd;
	Button mDelete;
	Button mQuery;
	Button mUpdate;
	Button mUpgrade;
	Button mAddTranslate;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		mCreate = (Button) findViewById(R.id.create);
		mAdd = (Button) findViewById(R.id.add);
		mDelete = (Button) findViewById(R.id.delete);
		mQuery = (Button) findViewById(R.id.query);
		mUpdate = (Button) findViewById(R.id.update);
		mUpgrade = (Button) findViewById(R.id.upgrade_db);
		mAddTranslate = (Button)findViewById(R.id.add_translate);

		mCreate.setOnClickListener(this);
		mAdd.setOnClickListener(this);
		mDelete.setOnClickListener(this);
		mQuery.setOnClickListener(this);
		mUpdate.setOnClickListener(this);
		mUpgrade.setOnClickListener(this);
		mAddTranslate.setOnClickListener(this);
		helper = new DatabaseHelper(this, "bk", null,15);
	}

	DatabaseHelper helper;
	SQLiteDatabase db;

	private void createTable() {
		db = helper.getWritableDatabase();
	}

	static int tag=0;
	@Override
	public void onClick(View v) {
		// TODO Auto-generated method stub
		switch (v.getId()) {
		case R.id.create:
			createTable();
			break;
		case R.id.add:
			helper.addBook(helper.new book("book"+tag++,  (float) 12.26, 231));
			helper.addBook(helper.new book("book"+tag++,  (float) 12.26, 234));
			helper.addBook(helper.new book("book"+tag++,  (float) 12.26, 235));
			helper.addBook(helper.new book("book"+10000,  (float) 12.26, 222));
			break;
		case R.id.delete:
			helper.delBook("book14");
			break;
		case R.id.query:
			helper.selectBook();
			break;
		case R.id.update:
			helper.updateBook("book13", (float)222);
			break;
		case R.id.upgrade_db:
			
			break;
		case R.id.add_translate:
			SQLiteDatabase db = helper.getWritableDatabase();
			try{
				
		    db.beginTransaction();
			helper.delBook("book2");
			if(true)
			throw new NullPointerException();
			helper.addBook(helper.new book("book777",(float)777,777));
			db.setTransactionSuccessful();
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				db.endTransaction();
			}
			break;
		}

	}

}

package com.example.databasedemo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {

	private final static String CREATE_BOOK = "create table book ( id Integer primary key autoincrement ,name text,price real,pages integer);";
	private final static String CREATE_CATEGORY = "create table category (id integer primary key autoincrement,name text);";
	
	public DatabaseHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}


	public void addBook(book b){
		SQLiteDatabase db = getWritableDatabase();
//		ContentValues values = new ContentValues();
		Log.d("data", "name="+b.name+" price="+b.price+" pages="+b.pages);
//		values.put("name", b.name);
//		values.put("price", b.price);
//		values.put("pages", b.pages);
//		db.insert("book", null, values);
		String sql = "insert into  book (name,price,pages) values (?,?,?);";
		db.execSQL(sql,new String[]{b.name,""+b.price,""+b.pages});
	}
	public void delBook(String name){
//		getWritableDatabase().delete("book", "name=?", new String[]{name});
		String sql = "delete from book where name=?;";
		getWritableDatabase().execSQL(sql,new String[]{name});
	}
	public void updateBook(String name,float price){
//		ContentValues values = new ContentValues();
//		values.put("price", price);
//		getWritableDatabase().update("book", values, "name=?", new String[]{name});
		String sql = "update book set price=? where name=?";
		getWritableDatabase().execSQL(sql,new String[]{price+"",name});
	}
	public void selectBook(){
//		Cursor c = getWritableDatabase().query("book", new String[]{"name","price","pages"}, "name=?", new String[]{name}, null, null, "price");
//		Cursor c = getWritableDatabase().query("book",null, null, null, "pages", "SUM(name)=1", "price");
		String sql = "select * from book group by pages having sum(price)>=100 order by price;";
		Cursor c = getWritableDatabase().rawQuery(sql,null);
		Log.d("data", "count="+c.getCount());
		if(c.moveToFirst()){
			do{
				Log.d("data", "name="+c.getString(c.getColumnIndex("name"))+" price="+c.getFloat(c.getColumnIndex("price"))+" pages="+c.getInt(c.getColumnIndex("pages")));
			}while(c.moveToNext());
		}
		c.close();
	}
	public void selectBook(float price){
		Cursor c = getWritableDatabase().query("book", new String[]{"name","price","pages"}, "price=?", new String[]{price+""}, null, null, "pages");
		if(c.moveToFirst()){
			do{
				Log.d("data", "name="+c.getString(c.getColumnIndex("name"))+" price="+c.getFloat(c.getColumnIndex("price"))+" pages="+c.getInt(c.getColumnIndex("pages")));
			}while(c.moveToNext());
		}
		c.close();
	}
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		Log.d("data", "onCreate db");
		db.execSQL(CREATE_BOOK);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		Log.d("data", "onUpgrade db oldVersion="+oldVersion+" newVersion="+newVersion);
//		db.execSQL("drop table if exists book");//*********
//		db.execSQL(CREATE_BOOK);
		switch(oldVersion){
		case 1: 
		case 2:
		case 3:
		case 4:
		case 13:db.execSQL(CREATE_CATEGORY);//
		case 14:db.execSQL("alter table book add column category_id integer");
		}
	}

	public class book{
		public String name;
		public float price;
		public int pages;
		public book(String n,float p,int pg){
			name = n;
			price = p;
			pages = pg;
		}
	}
}

<pre name="code" class="html"><LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >

    <Button
        android:id="@+id/create"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="create" />

    <Button
        android:id="@+id/upgrade_db"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="upgrade db" />

    <Button
        android:id="@+id/add"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="add" />

    <Button
        android:id="@+id/delete"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="delete" />

    <Button
        android:id="@+id/query"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="query" />

    <Button
        android:id="@+id/update"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="update" />

    <Button
        android:id="@+id/add_translate"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="add by translate" />

</LinearLayout>