android_18_sqlite数据库

效果:

 

 

清单文件:

 

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.sg31.sqlitedatabase"
    android:versionCode="1"
    android:versionName="1.0" >

    <uses-sdk
        android:minSdkVersion="8"
        android:targetSdkVersion="21" />

    // 注意位置  和  targetPackage!!!!!!!
    <instrumentation   
        android:name="android.test.InstrumentationTestRunner"  
        android:targetPackage="com.sg31.sqlitedatabase"  
        ></instrumentation>  
    <application
        android:allowBackup="true"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme" >
        // 注意位置  
        <uses-library android:name="android.test.runner"/>
        <activity
            android:name=".MainActivity"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>

 

 

 

 

 

 

 

数据库帮助类:

 

package com.sg31.sqlitedatabase;

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

public class SGDBHelper extends SQLiteOpenHelper {

	public SGDBHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// 数据库创建时,建表
		System.out.println("数据库创建时调用");
		db.execSQL("create table user(_id integer primary key autoincrement, name char(10), pwd char(10), phone integer(20))");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		System.out.println("数据库升级时调用");
	}

}

 

 

 

 

 

 

测试代码:

 

 

package com.sg31.sqlitedatabase;

import android.R.bool;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.test.AndroidTestCase;

public class sqlDatabaseTest extends AndroidTestCase {

	private SGDBHelper dbHelper;
	private SQLiteDatabase db;
	
	boolean flag = false;

	@Override
	protected void setUp() throws Exception {
		super.setUp();
		// 初始化
		// 建立数据库引用
		dbHelper = new SGDBHelper(getContext(), "user.sqlite", null, 1);
		db = dbHelper.getWritableDatabase();
	}

	@Override
	protected void tearDown() throws Exception {
		super.tearDown();
		// 收尾工作
		db.close();
	}
	
	//测试insert 
	public void testInsert() {
		
		if (flag == false) {
			return;
		}
		System.out.println("testInsert");
		for (int i = 0; i < 5; i++) {
			System.out.println("sg__i:"+i);
			String sql = "insert into user (name, pwd, phone)values(?, ?, ?)";
			Object[] bindArgs = new Object[]{"beyond"+i, 123456+i, "13876"+i};
	 		db.execSQL(sql, bindArgs);			
		}
		
	}
	
	// 测试delete
	public void testDelete(){
		
		if (flag == false) {
			return;
		}
		System.out.println("testDelete");
		
		String sql = "delete from user where name = ?";
		Object[] bindArgs = new Object[]{"beyond"};
 		db.execSQL(sql, bindArgs);
    }
	public void testDeleteAll(){
		
		if (flag == false) {
			return;
		}
		System.out.println("testDeleteAll");
		
		String sql = "delete from user";
 		db.execSQL(sql);
 		
 		
    }
	
	
	// 测试update
	public void testUpdate(){
		
		if (flag == false) {
			return;
		}
		System.out.println("testUpdate");
		
		String sql = "update user set pwd = ? where name = ?";
		Object[] bindArgs = new Object[]{"sg31.com","beyond3"};
 		db.execSQL(sql, bindArgs);
    }
	
	// 测试查询
	public void testSelect(){
		
		if (flag == false) {
			return;
		}
		System.out.println("testSelect");
		
		System.out.println("sg__begin");
        Cursor cursor = db.rawQuery("select name,pwd,phone from user", null);
        
        while(cursor.moveToNext()){
            //通过列索引获取列的值
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String pwd = cursor.getString(cursor.getColumnIndex("pwd"));
            String phone = cursor.getString(cursor.getColumnIndex("phone"));
            
            System.out.println(name + ";" +pwd + ";" +phone + ";");
        }
        System.out.println("sg__over");
    }

	
	// 使用封装好的API进行CRUD
	public void testInsertApi(){
		
		
		if (flag == false) {
			return;
		}
		System.out.println("testInsertApi");
		
        //把要插入的数据全部封装至ContentValues对象
        ContentValues values = new ContentValues();
        values.put("name", "shuiying");
        values.put("pwd", "sg31.com");
        values.put("phone", 1346943);
        db.insert("user", null, values);
        
        
        

    }
	
	// 使用封装好的API进行CRUD
	public void testDeleteApi(){
		if (flag == false) {
			return;
		}
		System.out.println("testDeleteApi");
        int i = db.delete("user", "name = ?", new String[]{"shuiying"});
        System.out.println(i);
        
    }
	
	// 使用封装好的API进行CRUD
	public void testUpdateApi(){
		
		
		
		if (flag == false) {
			return;
		}
		System.out.println("testUpdateApi");
        ContentValues values = new ContentValues();
        values.put("pwd", 654321);
        int i = db.update("user", values, "name = ?", new String[]{"shuiying"});
        System.out.println(i);
        
        
    }
	
	// 使用封装好的API进行CRUD
	public void testSelectApi(){
		
		
		if (flag == false) {
			return;
		}
		System.out.println("testSelectApi");
		
        Cursor cursor = db.query("user", null, null, null, null, null, null, null);
        while(cursor.moveToNext()){
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String pwd = cursor.getString(cursor.getColumnIndex("pwd"));
            String phone = cursor.getString(cursor.getColumnIndex("phone"));
            System.out.println(name + ";" + pwd + ";" + phone);
        }
        
    }
	
	// 使用API进行事务提交
	public void transaction(){
        try{
            //开启事务
            db.beginTransaction();
            ContentValues values = new ContentValues();
            // 减100
            values.put("money", 900);
            db.update("user", values, "name = ?", new String[]{"甲"});
            
            // 非常关键的一步,不然数据就重合了
            values.clear();
            
            // 加100
            values.put("money", 1100);
            db.update("user", values, "name = ?", new String[]{"乙"});
            
            // 特意放置一个exception
            int i = 3/0;
            
            
            //重要!!!必须设置  事务执行成功
            db.setTransactionSuccessful();
        }
        finally{
            //关闭事务，同时提交，如果已经设置事务执行成功，那么sql语句就生效了，反之，sql语句回滚
            db.endTransaction();
        }
    }
	
	
}