数据库应用--回顾SQLite数据库--通过按钮来进行对数据库的管理以及登陆验证操作
/* (程序头部注释开始)
* 程序的版权和版本声明部分
* Copyright (c) 2011, 烟台大学计算机学院学生
* All rights reserved.
* 文件名称：通过按钮来进行对数据库的管理以及登陆验证操作
* 作 者： 雷恒鑫
* 完成日期： 2012 年 08 月 18 日
* 版 本 号： V1.0
* 对任务及求解方法的描述部分
* 输入描述：
* 问题描述：
* 程序输出：
* 程序头部的注释结束
*/
SQLite数据库应用模式：数据库管理、数据库模式管理、数据库记录管理。
1.数据库管理：
主要是按照指定路径创建数据库与关闭数据库。
代码如下：
public static final String DB_NAME=“/sdcard/FOOLSTUDIO.DB";//创建数据库private SQLiteDatabase mDB = SQLiteDatabase.openOrCreateDatabase(SQLiteDBAct.DB_NAME, null);//关闭数据库mDB.close();
2.数据库模式管理：
①创建或删除数据表：
源程序如下：
TableCreatorAct.java
private void execCreateTableSQL() { String sql = "CREATE TABLE " + tableName + "(_id INTEGER PRIMARY KEY,"; //根据用户输入生成创建表格的sql语句...... //以读/写方式打开数据库 SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(SQLiteDBAct.DB_NAME, null);//执行SQL语句 db.execSQL(sql); //关闭数据库 db.close(); }
（2）获取数据表模式：
一下程序是获取SQL数据表模式信息：
TableSchemaViewerAct.java
package foolstudio.demo;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.regex.Matcher;import java.util.regex.Pattern;import android.app.ListActivity;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.os.Bundle;import android.widget.ListAdapter;import android.widget.SimpleAdapter;//表结构查看活动public class TableSchemaViewerAct extends ListActivity {//行显示组件ID数组private int[] mRowViewIds = {R.id.txtColIndex,R.id.txtColName,R.id.txtColType};private String[] mColumnNames = {"field_index","field_name","field_type"};private List<Map<String,String>> mItems = null; //-------------------------------------- /** Called when the activity is first created. */ @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.table_schema_viewer_view); //获取参数 Bundle extras = getIntent().getExtras(); String table_name = extras.getString(DBManagerAct.INTENT_EXTRAS_NAME); // initList(table_name); //创建列表数据适配器 ListAdapter adapter = new SimpleAdapter(TableSchemaViewerAct.this, mItems, R.layout.table_schema_row_view, mColumnNames, mRowViewIds); //设置数据适配器，绑定数据 setListAdapter(adapter); } //-------------------------------------- //初始化列表项目 private void initList(String table_name) { mItems = new ArrayList<Map<String, String>>(); String[] columnNames = {"sql"}; SQLiteDatabase db = SQLiteDatabase.openDatabase(SQLiteDBAct.DB_NAME, null, SQLiteDatabase.OPEN_READONLY); Cursor cursor = db.query("sqlite_master", columnNames, "(tbl_name='"+table_name+"')", null, null, null, null); if(cursor.getCount() == 1) { //一定要游标复位，否则后续读取会抛出异常 cursor.moveToFirst(); String sql = cursor.getString(0); String[] allColumnNames = getTableColumnNames(db, table_name); SQLiteDBAct.printLog(this, "SQL=" + sql); List<String> sqlParts = parseSQL(sql); for(int i = 0; i < allColumnNames.length; ++i) { String type= getColumnTypeByName(sqlParts, allColumnNames[i]); addItem("#"+i, allColumnNames[i], type); } } else { SQLiteDBAct.printLog(this, "Get " + columnNames[0] + " from table " + table_name + " ocur exception"); } cursor.close(); //一定要及时关闭数据库，否则会提示内存泄露 db.close();} //-------------------------------------- //获取指定表名的全部列名 private String[] getTableColumnNames(SQLiteDatabase db, final String tableName) { Cursor cursor = db.query(tableName, null, "(0>1)", null, null, null, null); String[] columnNames = cursor.getColumnNames(); cursor.close(); return(columnNames); } //-------------------------------------- //解析SQL语句 private List<String> parseSQL(final String sql) { Pattern p = Pattern.compile("[A-Za-z0-9_]+"); Matcher m = p.matcher(sql); List<String> sqlParts = new ArrayList<String>(); while(m.find() ) { sqlParts.add(m.group() ); } return (sqlParts); } //-------------------------------------- //获取指定列的类型 private String getColumnTypeByName(List<String> items, String columnName) { int index = indexOf(items, columnName); if(index != -1) { String type = items.get(index+1).toString(); if(type.toUpperCase().indexOf("CHAR") != -1) { //VARCHAR或CHAR的情形 type += ("(" + items.get(index+2)+")"); } System.out.println("Type of column " + columnName + " is " + type); return (type); } System.out.println("Get type of column " + columnName + " ocur exception"); return(""); } //-------------------------------------- //获取指定字符串在字符串列表中的位置（基于0，不在列表中则返回-1） private int indexOf(List<String> items, final String str) { int itemsCount = items.size(); for(int i = 0; i < itemsCount; ++i) { if(items.get(i).compareTo(str) == 0) { return (i); } } return(-1); } //-------------------------------------- //添加记录 private void addItem(String index, String name, String type) { Map<String,String> item = new HashMap<String,String>(); item.put(mColumnNames[0], index); item.put(mColumnNames[1], name); item.put(mColumnNames[2], type); mItems.add(item); SQLiteDBAct.printLog(this, "Add item: " + index + "," + name + "," + type); } //-------------------------------------- };
3.登陆操作：
下面的程序是验证用户输入输入的标识信息是否合法：
LoginAct.java
package foolstudio.demo;import android.app.Activity;import android.app.AlertDialog;import android.content.DialogInterface;import android.content.Intent;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.os.Bundle;import android.view.View;import android.view.View.OnClickListener;import android.widget.Button;import android.widget.EditText;public class LoginAct extends Activity implements OnClickListener {public static final String INTENT_EXTRAS_NAME = "USER_ID"; //-------------------------------------- /** Called when the activity is first created. */ @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.login_view); Button btnDiscard = (Button)findViewById(R.id.btnLoginDiscard); Button btnCommit = (Button)findViewById(R.id.btnLoginCommit); btnDiscard.setOnClickListener(this); btnCommit.setOnClickListener(this); } //-------------------------------------- @Overridepublic void onClick(View v) {// TODO Auto-generated method stubswitch(v.getId() ) {case R.id.btnLoginCommit: {doLogin();break;}case R.id.btnLoginDiscard: {this.finish();break;}}} //--------------------------------------//登录行为private void doLogin() {String txtID = ((EditText)findViewById(R.id.txtLoginID)).getText().toString().trim();String txtPassword = ((EditText)findViewById(R.id.txtPassword)).getText().toString().trim();if(txtID.length() < 1 || txtPassword.length() < 1) {SQLiteDBAct.showMessage(this, "ID or password can't empty!!");return;}if(checkUser(txtID, txtPassword) ) { //登录通过Intent infoViewer = new Intent(LoginAct.this, UserInfoViewerAct.class);infoViewer.putExtra(INTENT_EXTRAS_NAME, txtID);//压栈startActivity(infoViewer);//出栈this.finish();}else { //登录失败//SQLiteDBAct.showMessage(this, "User ID or password error, pls check!");AlertDialog.Builder builder = new AlertDialog.Builder(this);builder.setMessage("User ID or password error, pls select:").setCancelable(false).setPositiveButton("Try again!", new DialogInterface.OnClickListener() {@Overridepublic void onClick(DialogInterface dialog, int which) {// TODO Auto-generated method stubreturn;}}).setNegativeButton("Register!", new DialogInterface.OnClickListener() {@Overridepublic void onClick(DialogInterface dialog, int which) {// TODO Auto-generated method stubIntent register = new Intent(LoginAct.this, RegisterAct.class);//压栈（2）startActivity(register);//出栈（1）LoginAct.this.finish();}});AlertDialog dlg = builder.create();dlg.show(); //非阻塞}//this.finish();} //-------------------------------------- //初始化列表项目 private boolean checkUser(String ID, String password) { String condition = "(li_id='" + ID + "' and li_passwd='"+password+"')"; SQLiteDatabase db = SQLiteDatabase.openDatabase(SQLiteDBAct.DB_NAME, null, SQLiteDatabase.OPEN_READONLY); Cursor cursor = db.query("login_info", null, condition, null, null, null, null); SQLiteDBAct.printLog(this, "Condition="+condition); SQLiteDBAct.printLog(this, "Records count="+cursor.getCount() ); boolean isValid = (cursor.getCount() == 1); cursor.close(); //一定要及时关闭数据库，否则会提示内存泄露 db.close(); return (isValid);} //--------------------------------------};
4.注册操作：
下面是进行用户信息注册的关键代码：
RegisterAct.java
package foolstudio.demo;import android.app.Activity;import android.database.Cursor;import android.database.SQLException;import android.database.sqlite.SQLiteDatabase;import android.os.Bundle;import android.view.View;import android.view.View.OnClickListener;import android.widget.Button;import android.widget.EditText;import android.widget.Spinner;public class RegisterAct extends Activity implements OnClickListener { //-------------------------------------- /** Called when the activity is first created. */ @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.register_vew); Button btnDiscard = (Button)findViewById(R.id.btnRegisterDiscard); Button btnCommit = (Button)findViewById(R.id.btnRegisterCommit); btnDiscard.setOnClickListener(this); btnCommit.setOnClickListener(this); } //-------------------------------------- @Overridepublic void onClick(View v) {// TODO Auto-generated method stubswitch(v.getId()) {case R.id.btnRegisterDiscard: {this.finish();break;}case R.id.btnRegisterCommit: {if(checkForm() ) {doRegister();}break;}}} //--------------------------------------private boolean checkForm() {String id = ((EditText)findViewById(R.id.txtRegisterUserID)).getText().toString().trim();String passwd = ((EditText)findViewById(R.id.txtRegisterPassword)).getText().toString().trim();String passwd2 = ((EditText)findViewById(R.id.txtRegisterPassword2)).getText().toString().trim();String name = ((EditText)findViewById(R.id.txtRegisterName)).getText().toString().trim();//String sex = ((EditText)findViewById(R.id.spRegisterSex)).//getText().toString().trim();//String birthday = ((EditText)findViewById(R.id.txtRegisterBirthday)).//getText().toString().trim();if(id.length() < 1) {SQLiteDBAct.showMessage(this, "User ID can't empty!");return (false);}if(passwd.length() < 1) {SQLiteDBAct.showMessage(this, "Password can't empty!");return (false);}if(passwd.compareTo(passwd2) != 0) {SQLiteDBAct.showMessage(this, "Confirm password failed!");return (false);}if(name.length() < 1) {SQLiteDBAct.showMessage(this, "Name can't empty!");return (false);}//判断主键是否存在SQLiteDatabase db = SQLiteDatabase.openDatabase(SQLiteDBAct.DB_NAME, null, SQLiteDatabase.OPEN_READWRITE);Cursor cursor = db.query("login_info", new String[] {"li_id"}, "(li_id='" + id +"')", null, null, null, null, null);int recordCount = cursor.getCount(); cursor.close();db.close();if(recordCount > 0) {SQLiteDBAct.showMessage(this, "This ID already exists!");return(false);}return (true);} //--------------------------------------private void doRegister() {String id = ((EditText)findViewById(R.id.txtRegisterUserID)).getText().toString().trim();String passwd = ((EditText)findViewById(R.id.txtRegisterPassword)).getText().toString().trim();//String passwd2 = ((EditText)findViewById(R.id.txtRegisterPassword2)).//getText().toString().trim();String name = ((EditText)findViewById(R.id.txtRegisterName)).getText().toString().trim();String sex = ((Spinner)findViewById(R.id.spRegisterSex)).getSelectedItem().toString().trim();String birthday = ((EditText)findViewById(R.id.txtRegisterBirthday)).getText().toString().trim(); String sql1 = "INSERT INTO login_info(li_id, li_passwd) values('" + id +"','" + passwd+"')"; String sql2 = "INSERT INTO user_info(ui_id, ui_name, ui_sex, ui_birthday) values('" + id +"','" + name +"','" + sex+ "','" + birthday+"')"; SQLiteDBAct.printLog(this, "SQL1=" + sql1); SQLiteDBAct.printLog(this, "SQL2=" + sql2); // SQLiteDatabase db = SQLiteDatabase.openDatabase(SQLiteDBAct.DB_NAME, null, SQLiteDatabase.OPEN_READWRITE); //开始事务 db.beginTransaction(); try { db.execSQL(sql1); db.execSQL(sql2); //事务成功 db.setTransactionSuccessful(); } catch(SQLException e) { e.printStackTrace(); } finally { //终止事务 db.endTransaction(); } db.close(); // this.finish();} //-------------------------------------- };
运行结果：
