数据库应用--回顾SQLite数据库--通过按钮来进行对数据库的管理以及登陆验证操作...

最新推荐文章于 2024-09-19 19:11:44 发布

iteye_6233

最新推荐文章于 2024-09-19 19:11:44 发布

阅读量109

点赞数

文章标签：数据库移动开发 java

/* (程序头部注释开始)
* 程序的版权和版本声明部分
* 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);     
    }
    
    //--------------------------------------------------------------------------    
	@Override
	public void onClick(View v) {
		// TODO Auto-generated method stub
		switch(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() {
					@Override
					public void onClick(DialogInterface dialog, int which) {
						// TODO Auto-generated method stub
						return;
					}				
				})
			.setNegativeButton("Register!", new DialogInterface.OnClickListener() {
					@Override
					public void onClick(DialogInterface dialog, int which) {
						// TODO Auto-generated method stub
						Intent 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);
    }

    //--------------------------------------------------------------------------    
	@Override
	public void onClick(View v) {
		// TODO Auto-generated method stub
		switch(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();
	}
    
    //--------------------------------------------------------------------------    
};

运行结果：