Android SQLite 数据库存储

最新推荐文章于 2019-01-21 11:22:11 发布

YOYO__yy

最新推荐文章于 2019-01-21 11:22:11 发布

阅读量779

点赞数

分类专栏： Android

本文链接：https://blog.csdn.net/YOYO__yy/article/details/51473347

版权

Android 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

1、创建数据库

Android 为了让我们能够更加方便地管理数据库，专门提供了一个 SQLiteOpenHelper 帮助类，借助这个类就可以非常简单地对数据库进行创建和升级。首先你要知道 SQLiteOpenHelper 是一个抽象类，这意味着如果我们想要使用它的话，就需要创建一个自己的帮助类去继承它。SQLiteOpenHelper 中有两个抽象方法，分别是 onCreate()和 onUpgrade()，我们必须在自己的帮助类里面重写这两个方法，然后分别在这两个方法中去实现创建、升级数据库的逻辑。 SQLiteOpenHelper 中还有两个非常重要的实例方法， getReadableDatabase()和 getWritableDatabase()。这两个方法都可以创建或打开一个现有的数据库（如果数据库已存在则直接打开，否则创建一个新的数据库），并返回一个可对数据库进行读写操作的对象。不同的是，当数据库不可写入的时候（如磁盘空间已满）getReadableDatabase()方法返回的对象将以只读的方式去打开数据库，而etWritableDatabase()方法则将出现异常。SQLiteOpenHelper 中有两个构造方法可供重写，一般使用参数少一点的那个构造方法即可。这个构造方法中接收四个参数，第一个参数是 Context，这个没什么好说的，必须要有它才能对数据库进行操作。第二个参数是数据库名，创建数据库时使用的就是这里指定的名称。第三个参数允许我们在查询数据的时候返回一个自定义的 Cursor，一般都是传入 null。第四个参数表示当前数据库的版本号，可用于对数据库进行升级操作。构建出 SQLiteOpenHelper 的实例之后，再调用它的 getReadableDatabase()或 getWritableDatabase() 方法就能够创建数据库了，数据库文件会存放在/data/data/<package name>/databases/目录下。此时，重写的 onCreate()方法也会得到执行，所以通常会在这里去处理一些创建表的逻辑。

activity_main.xml

<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" 
     tools:context=".MainActivity"  >

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

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

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

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

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

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

</LinearLayout>

book.java

package cn.bzu.bztc.databasetest.entity;

public class Book {
	private int id;
	private String author;
	private float price;
	private int pages;
	private String name;
	public Book(){
		super();
	}
	public Book( String author, float price, int pages, String name) {
		super();		
		this.author = author;
		this.price = price;
		this.pages = pages;
		this.name = name;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getAuthor() {
		return author;
	}
	public void setAuthor(String author) {
		this.author = author;
	}
	public float getPrice() {
		return price;
	}
	public void setPrice(float price) {
		this.price = price;
	}
	public int getPages() {
		return pages;
	}
	public void setPages(int pages) {
		this.pages = pages;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	@Override
	public String toString() {
		return "Book [id=" + id + ", author=" + author + ", price=" + price
				+ ", pages=" + pages + ", name=" + name + "]";
	}
	
}

BookDao.java

package cn.bzu.bztc.databasetest.dao;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import cn.bzu.bztc.databasetest.db.MyDatabaseHelper;
import cn.bzu.bztc.databasetest.entity.Book;

public class BookDao {
	private MyDatabaseHelper myDatabaseHelper;
	private SQLiteDatabase sqLiteDatabase;
	public BookDao(Context context){
		myDatabaseHelper=new MyDatabaseHelper(context, "bookdb", null, 1);
	}
	public void add(Book book){
		sqLiteDatabase=myDatabaseHelper.getWritableDatabase();
		ContentValues contentValues=new ContentValues();
		contentValues.put("author", book.getAuthor());
		contentValues.put("price", book.getPrice());
		contentValues.put("pages", book.getPages());
		contentValues.put("name", book.getName());
		sqLiteDatabase.insert("book", "author", contentValues);
	}
	public List<Book> getAllBooks(){
		List<Book> books=new ArrayList<Book>();
		sqLiteDatabase=myDatabaseHelper.getReadableDatabase();
		Cursor cursor=sqLiteDatabase.query("book", null, null, null, null, null, null);
		if(cursor.moveToFirst()){
			do{
				String author=cursor.getString(cursor.getColumnIndex("author"));
				float price=cursor.getFloat(cursor.getColumnIndex("price"));
				int pages=cursor.getInt(cursor.getColumnIndex("pages"));
				String name=cursor.getString(cursor.getColumnIndex("name"));
				Book book=new Book(author,price,pages,name);
			}while(cursor.moveToNext());
		}
		return books;
	}

}

MyDatabaseHelper.java

package cn.bzu.bztc.databasetest.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

public class MyDatabaseHelper extends SQLiteOpenHelper{
	
	public static final String CREATE_BOOK="create table book(" +
			"id integer primary key autoincrement," +
			"author text," +
			"price real," +
			"pages integer," +
			"name text," +
			"category_id integer)";
	public static final String CREATE_CATEGORY="create table Category(" +
			"id integer primary key autoincrement," +
			"category_name text," +
			"category_code integer)";
	private Context mContext;

	public MyDatabaseHelper(Context context, String name,
			CursorFactory factory, int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
		//mContext=context;
	}

	@Override
	public void onCreate(SQLiteDatabase sqLiteDatabase) {
		// TODO Auto-generated method stub
		sqLiteDatabase.execSQL(CREATE_BOOK);
		sqLiteDatabase.execSQL(CREATE_CATEGORY);
		Toast.makeText(mContext, "Create succeeded", Toast.LENGTH_SHORT).show();
		
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		/*db.execSQL("drop table if exists Book");
		db.execSQL("drop table if exists Category");
		onCreate(db);
		*/
		/*switch (oldVersion){
		case 1:
			sqLiteDatabase.execSQL(CREATE_CATEGORY);
		case 2:
			sqLiteDatabase.execSQL("alter table Book add column category_id integer");
			default:
		}*/
		sqLiteDatabase.execSQL("drop table if exits Book");
		sqLiteDatabase.execSQL("drop table if exits CateGory");
		onCreate(sqLiteDatabase);
	}

}

MainActivity.java

package cn.bzu.bztc.databasetest;

import cn.bzu.bztc.databasetest.dao.BookDao;
import cn.bzu.bztc.databasetest.db.MyDatabaseHelper;
import android.os.Bundle;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.view.Menu;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;

public class MainActivity extends Activity {
	private BookDao bookDao;
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		bookDao=new BookDao(this);
	}
	public void addData(View view){
		
	}
	/*private MyDatabaseHelper dbHelper;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		dbHelper=new MyDatabaseHelper(this, "BookStore.db", null, 2);
		Button createDatabase=(Button) findViewById(R.id.create_database);
		createDatabase.setOnClickListener(new OnClickListener() {
			
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				dbHelper.getWritableDatabase();
				
			}
		});
		//Button addData=(Button) findViewById(R.id.add_data);
		addData.setOnClickListener(new OnClickListener() {
			
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				SQLiteDatabase db=dbHelper.getWritableDatabase();
				ContentValues values=new ContentValues();
				values.put("name", "The Da Vinci Code");
				values.put("author", "Dan Brown");
				values.put("pages", 454);
				values.put("price", 16.95);
				db.insert("Book", null, values);
			}
		});
		Button updateData=(Button) findViewById(R.id.update_data);
		updateData.setOnClickListener(new OnClickListener() {
			
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				SQLiteDatabase db=dbHelper.getWritableDatabase();
				ContentValues values=new ContentValues();
				values.put("price", 10.99);
				db.update("Book", values, "name=?", new String[]{"The Da Vinci Code"});
				
			}
		});
		Button deleteButton=(Button) findViewById(R.id.delete_data);
		deleteButton.setOnClickListener(new OnClickListener() {
			
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				SQLiteDatabase db=dbHelper.getWritableDatabase();
				db.delete("Book", "pages>?", new String[]{"500"});
			}
		});
		Button queryButton=(Button) findViewById(R.id.query_data);
		queryButton.setOnClickListener(new OnClickListener() {
			
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				SQLiteDatabase db=dbHelper.getWritableDatabase();
				Cursor cursor=db.query("Book", null, null, null, null, null, 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("MainActivity", "book name is "+name);
						Log.d("MainActivity", "book author is "+author);
						Log.d("MainActivity", "book pages is "+ pages);
						Log.d("MainActivity", "book price is "+ price);
					}while(cursor.moveToNext());
				}
				cursor.close();
			}
		});
		Button replaceData=(Button) findViewById(R.id.replace_data);
		replaceData.setOnClickListener(new OnClickListener() {
			
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				SQLiteDatabase db=dbHelper.getWritableDatabase();
				db.beginTransaction();
				try{
				db.delete("Book", null, null);
				if(true){
					throw new NullPointerException();
				}
				ContentValues values =new ContentValues();
				values.put("name", "Game of Thrones");
				values.put("author", "George Martin");
				values.put("pages", 720);
				values.put("price", 20.85);
				db.insert("Book", null, values);
				db.setTransactionSuccessful();
				}catch (Exception e){
					e.printStackTrace();
				}finally{
					db.endTransaction();
				}
			}
		});
	}*/

	@Override
	public boolean onCreateOptionsMenu(Menu menu) {
		// Inflate the menu; this adds items to the action bar if it is present.
		getMenuInflater().inflate(R.menu.main, menu);
		return true;
	}

}

BookDaoTest.java

package cn.bzu.bztc.databasetest.test;

import java.util.List;

import cn.bzu.bztc.databasetest.dao.BookDao;
import cn.bzu.bztc.databasetest.entity.Book;
import android.test.AndroidTestCase;

public class BookDaoTest extends AndroidTestCase{
	public void testAdd(){
		BookDao bookDao=new BookDao(getContext());
		bookDao.add(new Book("耿祥义",32.8f,328,"Java"));
	}
	public void testGetAllBooks(){
		BookDao bookDao=new BookDao(getContext());
		List<Book> books=bookDao.getAllBooks();
		System.out.println(books);
	}

}