Android:SQLiteOpenHelper数据库的两套API

                        SQLiteOpenHelper数据库的两套API

目录

                        Android:sqliteopenHelper数据库的两套API

一、目录结构

二、类：MainActivity

 三、类：MyopenHelper

四：activity_main.xml

---

一、目录结构

二、类：MainActivity

package com.example.sqliter;

import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.Toast;

public class MainActivity extends Activity {

	MyopenHelper myopenHelper = new MyopenHelper(this);
	
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		
		
		
		
		SQLiteDatabase readableDatabase = myopenHelper.getReadableDatabase();
		
	}
	
	//1.查询
	public void query(View V) {
		//获取database
		SQLiteDatabase database = myopenHelper.getReadableDatabase();
		
		String sql = "select * from info";
		Cursor rawQuery = database.rawQuery(sql, null);
		
		while(rawQuery.moveToNext()) {
			
			//取出当前行的name列，索引为1
			String name = rawQuery.getString(1);
			
			//取出当前行的phone列，索引为2,通过列的名字获取列的索引
			String phone = rawQuery.getString(rawQuery.getColumnIndex("phone"));
			
			System.out.println("name:"+name+"  phone:"+phone);
		}
		
		rawQuery.close();
		database.close();
	}
	
	//2.插入
	public void insert(View V) {
		//获取database
		SQLiteDatabase database = myopenHelper.getReadableDatabase();
		
		String sql = "insert into info (name,phone,age) values('aaa',111,10)";
		database.execSQL(sql );
		
		sql = "insert into info (name,phone,age) values('bbb',222,20)";
		database.execSQL(sql );
		
		//关闭数据库
		database.close();
	}
	
	//3.删除
	public void delete(View V) {
		SQLiteDatabase database = myopenHelper.getReadableDatabase();
		
		String sql = "delete from info where name = 'bbb'";
		database.execSQL(sql );
		
		database.close();
	}
	
	//4.更新
	public void update(View V) {
		SQLiteDatabase database = myopenHelper.getReadableDatabase();
		
		String sql = "update info set phone='333' where name = 'aaa'";
		database.execSQL(sql );
		
		database.close();
	}
	
	/*
	 * 
	 * */
	//1.查询
	public void query1(View V) {
		//获取database
		SQLiteDatabase database = myopenHelper.getReadableDatabase();
		
		Cursor cursor = database.query("info", new String[] {"name","phone"}, "name = ?",new String[] {"qqq"} , null, null, null, null);
		
		while(cursor.moveToNext()) {
			String string = cursor.getString(cursor.getColumnIndex("phone"));
			Toast.makeText(this, "phone查询结果:"+string, Toast.LENGTH_SHORT).show();
		}
		
		cursor.close();
		database.close();
	}
	
	//2.插入
	public void insert1(View V) {
		//获取database
		SQLiteDatabase database = myopenHelper.getReadableDatabase();
		
		//默认字段
		String nullColumnHack = "name";
		
		//通过key value封装要插入的数据
		ContentValues values = new ContentValues();
		values.put("name", "qqq");
		values.put("phone", 1111);
		values.put("age", 01);
		long id = database.insert("info", nullColumnHack, values);
		
		System.out.println("插入第:"+id+"行！");
		
		if(id != -1) {
			Toast.makeText(this, "插入第:"+id+"行！", Toast.LENGTH_SHORT).show();
		}else {
			Toast.makeText(this, "插入失败！", Toast.LENGTH_SHORT).show();
		}
		database.close();
	}
	
	//3.删除
	public void delete1(View V) {
		SQLiteDatabase database = myopenHelper.getReadableDatabase();
		
		
		//删除整个表，后两个参数置为null
		String table = "info";
		String whereClause = "name = ?";		
		String[] whereArgs = {"aaa"};
		
		//返回值:删除了几条数据
		int delete = database.delete(table, whereClause, whereArgs);
		
		
		Toast.makeText(this, "删除了"+delete+"条数据！", Toast.LENGTH_SHORT).show();
		database.close();
	}
	
	//4.更新
	public void update1(View V) {
		SQLiteDatabase database = myopenHelper.getReadableDatabase();
		
		ContentValues values = new ContentValues();
		values.put("phone", 2222);

		int update = database.update("info", values , "name = ?" , new String[] {"qqq"} );
		
		Toast.makeText(this, "删除了"+update+"条数据！", Toast.LENGTH_SHORT).show();
		
		database.close();
	}
	
	
}

 三、类：MyopenHelper

package com.example.sqliter;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class MyopenHelper extends SQLiteOpenHelper {

	public MyopenHelper(Context context) {
		super(context, "demo.db", null, 3);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		
		//sqlite不管传入的是什么类型的参数，都是以字符串类型存储的
		db.execSQL("create table info(_id integer primary key autoincrement, name varchar(20), phone varchar(20))");
	}

	//库升级
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		//修改数据库
		db.execSQL("alter table info add age inteher");
		System.out.println("oldVersion:"+oldVersion +" newVersion:" +oldVersion);
	}
	
	//数据库降级
	@Override
	public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		//super.onDowngrade(db, oldVersion, newVersion);
	}
}

四：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:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    android:orientation="vertical"
    tools:context="com.example.sqliter.MainActivity" >

		<Button 
	    android:layout_width="wrap_content"
	    android:layout_height="wrap_content"
	    android:text="查询"
	    android:onClick="query"
	    />

		<Button 
	    android:layout_width="wrap_content"
	    android:layout_height="wrap_content"
	    android:text="插入"
	    android:onClick="insert"
	    />
		<Button 
	    android:layout_width="wrap_content"
	    android:layout_height="wrap_content"
	    android:text="删除"
	    android:onClick="delete"
	    />
		<Button 
	    android:layout_width="wrap_content"
	    android:layout_height="wrap_content"
	    android:text="更新"
	    android:onClick="update"
	    />
	
		<View 
		    android:layout_width="match_parent"
		    android:layout_height="2dp"
		    android:background="#88007700"
		    />
		
				<Button 
	    android:layout_width="wrap_content"
	    android:layout_height="wrap_content"
	    android:text="查询1"
	    android:onClick="query1"
	    />

		<Button 
	    android:layout_width="wrap_content"
	    android:layout_height="wrap_content"
	    android:text="插入1"
	    android:onClick="insert1"
	    />
		<Button 
	    android:layout_width="wrap_content"
	    android:layout_height="wrap_content"
	    android:text="删除1"
	    android:onClick="delete1"
	    />
		<Button 
	    android:layout_width="wrap_content"
	    android:layout_height="wrap_content"
	    android:text="更新1"
	    android:onClick="update1"
	    />
</LinearLayout>