数据库的使用

作用：

从文本中获取信息存放在数据库中。

 

SQLActivity主要负责界面控制，六个按钮的摆放以及点击效果。

 

package com.example.sql;

import java.io.IOException;

import android.app.Activity;
import android.content.ContentValues;
import android.os.Bundle;
import android.util.Log;
import android.view.View;

public class SQLActivity extends Activity {
    private Database db;
    private final static String FILE_NAME = "data.txt";
    private final static String TXT_TITLE = "标题";
    private final static String TXT_CONTENT = "内容";
    private final static String TXT_TYPE = "类别";
    private final static String TXT_LINK = "链接";
    private final static String TXT_ENDSIGN = "#@#";
  
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_sql);
		db = new Database(getApplicationContext());
//		创建数据库
		findViewById(R.id.create).setOnClickListener(new View.OnClickListener() {
			
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				db.create();
			}
		});
//		插入数据
		findViewById(R.id.insert).setOnClickListener(new View.OnClickListener() {
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				GetTxtContentForAssets getTxtContentForAssets = new GetTxtContentForAssets();
				String txtContent = null;
				try {
					txtContent = getTxtContentForAssets.getAllContent(FILE_NAME, getApplicationContext());
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				if (txtContent != null){
					String title = new String();
					String content = new String();
				    String type = new String();
				    String link = new String();
					while (txtContent.indexOf(TXT_TITLE) != -1){
						title = getTxtContentForAssets.getStringFromXtoY(txtContent, TXT_TITLE, TXT_ENDSIGN);
						txtContent = getTxtContentForAssets.getRemainString(txtContent,TXT_ENDSIGN);
						content = getTxtContentForAssets.getStringFromXtoY(txtContent, TXT_CONTENT,TXT_ENDSIGN);
						txtContent = getTxtContentForAssets.getRemainString(txtContent,TXT_ENDSIGN);
						type = getTxtContentForAssets.getStringFromXtoY(txtContent, TXT_TYPE,TXT_ENDSIGN);
						txtContent = getTxtContentForAssets.getRemainString(txtContent,TXT_ENDSIGN);
						link = getTxtContentForAssets.getStringFromXtoY(txtContent, TXT_LINK,TXT_ENDSIGN);
						txtContent = getTxtContentForAssets.getRemainString(txtContent,TXT_ENDSIGN);
//						插入数据title，content，type和link到数据库中。
						db.insert(title,content,type,link);
						Log.i("database", "insert");
					}
				}
			}
			
		});
//		查询数据
		findViewById(R.id.query).setOnClickListener(new View.OnClickListener() {
			
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				db.queryAll();
			}
		});
//		删除数据库
		findViewById(R.id.delete).setOnClickListener(new View.OnClickListener() {
			
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				db.delete();
			}
		});
//	    修改数据库信息
		findViewById(R.id.update).setOnClickListener(new View.OnClickListener() {
			
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				db.update();
			}
		});
//   删除行
		findViewById(R.id.deleline).setOnClickListener(new View.OnClickListener() {
			
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
			    db.deleteline();	
			}
		});
		
		
	}
   

    
}

  Database类主要完成和数据库相关的各种操作，比如数据库的创建、增删改查等操作，具体看注释。

package com.example.sql;

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

public class Database {
    private Context mContext;
    private SQLiteDatabase db;
    private DatabaseSQLiteOpenHelper sqLiteOpenHelper;
    private final static String DATABASE_NAME = "legerdemain.db";
    private final static int DATABASE_VERSION = 1;
	public Database(Context context) {
		// TODO Auto-generated constructor stub
		mContext = context;
	}
//  创建数据库
	public void create() {
		// TODO Auto-generated method stub
		sqLiteOpenHelper = new DatabaseSQLiteOpenHelper(mContext, DATABASE_NAME, null, DATABASE_VERSION);
	    db = sqLiteOpenHelper.getReadableDatabase();
	    db.close();
	}
	/**
	 * 插入元组到数据库中,各个参数是元组内容
	 * @param title
	 * @param content
	 * @param type
	 * @param link
	 */
	public void insert(String title, String content, String type, String link) {
		// TODO Auto-generated method stub
		sqLiteOpenHelper = new DatabaseSQLiteOpenHelper(mContext, DATABASE_NAME, null, DATABASE_VERSION);
		db = sqLiteOpenHelper.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(sqLiteOpenHelper.TABLE_TITLE, title);
		cv.put(sqLiteOpenHelper.TABLE_CONTENT, content);
		cv.put(sqLiteOpenHelper.TABLE_TYPE, type);
		cv.put(sqLiteOpenHelper.TABLE_LINK, link);
		db.insert(sqLiteOpenHelper.TABLE_NAME, null, cv);
		db.close();
		
	}
	/**
	 * 查询所有数据
	 */
	public void queryAll() {
		// TODO Auto-generated method stub
		sqLiteOpenHelper = new DatabaseSQLiteOpenHelper(mContext, DATABASE_NAME, null, DATABASE_VERSION);
		db = sqLiteOpenHelper.getReadableDatabase();
		Cursor cursor = db.query(sqLiteOpenHelper.TABLE_NAME, new String[]{sqLiteOpenHelper.TABLE_ID,
				sqLiteOpenHelper.TABLE_TITLE,sqLiteOpenHelper.TABLE_CONTENT,sqLiteOpenHelper.TABLE_TYPE,sqLiteOpenHelper.TABLE_LINK}, null, null, null, null, null);
		String title = null;
		String content = null;
		String type = null;
	    String link = null;
		while (cursor.moveToNext()){
			title = cursor.getString(cursor.getColumnIndex(sqLiteOpenHelper.TABLE_TITLE));
			content = cursor.getString(cursor.getColumnIndex(sqLiteOpenHelper.TABLE_CONTENT));
			type = cursor.getString(cursor.getColumnIndex(sqLiteOpenHelper.TABLE_TYPE));
			link = cursor.getString(cursor.getColumnIndex(sqLiteOpenHelper.TABLE_LINK));
			Log.i("database","title="+title+"content="+content+"type="+type+"link="+link);
		}
		db.close();
	}
	/**
	 * 查询title列表中的所有包含keyword的数据
	 * @param keyword
	 */
	public void queryTitle(String keyword) {
		// TODO Auto-generated method stub
		sqLiteOpenHelper = new DatabaseSQLiteOpenHelper(mContext, DATABASE_NAME, null, DATABASE_VERSION);
		db = sqLiteOpenHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select * from "+sqLiteOpenHelper.TABLE_NAME+" where "+sqLiteOpenHelper.TABLE_TITLE+" LIKE '%"+keyword+"%'", null);
		String title = null;
		String content = null;
		String type = null;
	    String link = null;
		while (cursor.moveToNext()){
			title = cursor.getString(cursor.getColumnIndex(sqLiteOpenHelper.TABLE_TITLE));
			content = cursor.getString(cursor.getColumnIndex(sqLiteOpenHelper.TABLE_CONTENT));
			type = cursor.getString(cursor.getColumnIndex(sqLiteOpenHelper.TABLE_TYPE));
			link = cursor.getString(cursor.getColumnIndex(sqLiteOpenHelper.TABLE_LINK));
			Log.i("database","title="+title+"content="+content+"type="+type+"link="+link);
		}
		db.close();
	}
	/**
	 * 删除数据库
	 */
	public void delete() {
		// TODO Auto-generated method stub
		if (mContext.deleteDatabase(DATABASE_NAME)){
			Log.i("database", "deleteDatabase");
		}
	}
	/**
	 * 修改:将title中含有“天”的title修改为Fred
	 */
	public void update() {
		// TODO Auto-generated method stub
		sqLiteOpenHelper = new DatabaseSQLiteOpenHelper(mContext, DATABASE_NAME, null, DATABASE_VERSION);
		db = sqLiteOpenHelper.getWritableDatabase();
		String sql = "UPDATE "+sqLiteOpenHelper.TABLE_NAME+" SET "+sqLiteOpenHelper.TABLE_TITLE+" = 'Fred' WHERE "+sqLiteOpenHelper.TABLE_TITLE+" LIKE '%天%'" ;
		db.execSQL(sql);
		db.close();
	}
	/**
	 * 删除title中含有“F”的一行
	 */
	public void deleteline() {
		// TODO Auto-generated method stub
		sqLiteOpenHelper = new DatabaseSQLiteOpenHelper(mContext, DATABASE_NAME, null, DATABASE_VERSION);
		db = sqLiteOpenHelper.getWritableDatabase();
		String sql = "DELETE FROM "+sqLiteOpenHelper.TABLE_NAME+" WHERE "+sqLiteOpenHelper.TABLE_TITLE+" LIKE '%F%'";
		db.execSQL(sql);
		db.close();
	}
}

 从Asset文件夹中获取信息存入数据库中，作为Insert的一个依赖存在。

package com.example.sql;

import java.io.IOException;
import java.io.InputStream;

import android.content.Context;
import android.content.res.AssetManager;

public class GetTxtContentForAssets {
	private final static String ENCODING = "GB2312";
	/** 
	 * 读取文件中的所有内容
  	 * @param fileName the fileName which in Asset folder that need to be read
	 * @param context
	 * @return the contents of file
	 * @throws IOException
	 */
	public String getAllContent(String fileName,Context context) throws IOException{
		AssetManager aManager = context.getAssets();
		InputStream file = aManager.open(fileName); 
		int length = file.available();
		byte[] buff = new byte[length];
		file.read(buff);
		file.close();
		String content = new String(buff, ENCODING);
		return content;
	}
	/**
	 * 截取字符串中以startsign和stopsign之中的一段
	 * @param string String which is pending on
	 * @param startsign 
	 * @param stopsign
	 * @return the string which is pending over
	 */
	public String getStringFromXtoY(String string, String startsign, String stopsign) {
		// TODO Auto-generated method stub
		int start = string.indexOf(startsign)+startsign.length();
		int stop = string.indexOf(stopsign);
		return string.substring(start, stop);
	}
    /**
     * 取结束符合之后的一段
     * @param string
     * @param Endsign
     * @return the string which is after endsign
     */
	public String getRemainString(String string, String Endsign) {
		// TODO Auto-generated method stub
		int len = string.indexOf(Endsign)+Endsign.length();
		return string.substring(len);
	}
	
	
}

 

package com.example.sql;

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

public class DatabaseSQLiteOpenHelper extends SQLiteOpenHelper{
    public final static String TABLE_NAME = "legerdemain";
    public final static String TABLE_ID = "ID";
    public final static String TABLE_TITLE = "TITLE";
    public final static String TABLE_CONTENT = "CONTENT";
    public final static String TABLE_TYPE = "TYPE";
    public final static String TABLE_LINK = "LINK";
	public DatabaseSQLiteOpenHelper(Context context, String name,
			CursorFactory factory, int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL("Create table "+TABLE_NAME+" ("+TABLE_ID+" integer primary key autoincrement,"
		+TABLE_TITLE+" text,"+TABLE_CONTENT+" text,"+TABLE_TYPE+" text,"+TABLE_LINK+" text);"); 
		Log.i("database", "CreatDatebase");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}

}