androidSQLite小工具

本次学习android刚好，接触到了数据库方面的知识，借此制作了一个，数据库的小工具，通过输入sql语句实现增、删、改，并将数据表展现出来，效果图如下：

输入SQL语句，创建一个表（这里我用的是SQLite的简化语句，当然 你也可以利用MySQL等等数据库用的语句）：

      

代码结构图：

源代码：

MainActivity.java:

package com.example.mysqlitetest2;

import java.util.ArrayList;
import java.util.List;

import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.AdapterView.OnItemLongClickListener;
import android.widget.ArrayAdapter;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.PopupMenu;
import android.widget.PopupMenu.OnMenuItemClickListener;
import android.widget.Toast;

import com.example.tableData.BundleDataInfo;
import com.example.tableData.TableDataInfo;


public class MainActivity extends Activity {
	private SQLiteDatabase db;
	private EditText SQLStatement;
	private String MySQLStatement;
	private ListView list;
	String []tables;
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		list = (ListView)findViewById(R.id.list);
		SQLStatement = (EditText)findViewById(R.id.text);
		//创建一个数据数据库
		createSQLite();
		//列出各列表
		showTable();
		//列表长按事件
		tableLongClickListener();
		//列表单击事件
		tableClickListener();
	}
	public void click(View v){
		try{
		<span style="white-space:pre">	</span>//创建学生表
			Log.i("statement",getMySQLStatement() );
			db.execSQL(getMySQLStatement());
			showTable();
		}catch(Exception e){
			Toast.makeText(this, "语法有误，请重新检查", Toast.LENGTH_LONG).show();
		}
	}
	
	//得到SQL语句
	public String getMySQLStatement(){
		MySQLStatement = SQLStatement.getText().toString().trim();
		return MySQLStatement;
	}
	//创建一个数据my的数据库
	public void createSQLite(){
		db = SQLiteDatabase.openOrCreateDatabase(this.getFilesDir()+"/my.db3", null);
	}
	
	/**
	 * 得到数据库的表名
	 * @return
	 */
	public String[] getTableName(){
		List<String> list = new ArrayList<String>();
		//利用游标返回表名
		Cursor cursor = db.rawQuery("select name from sqlite_master where type='table' order by name", null);  
		  while(cursor.moveToNext()){  
			   //遍历出表名  
			   String name = cursor.getString(0);  
			   list.add(name);
		   Log.i("TableName:", name);
		  }
		  String []tableName = new String[list.size()];
		  Log.i("listSize:", ""+list.size());
		  list.toArray(tableName);
		  return tableName;	  
	}
	
	/**
	 * 列出各表
	 */
	public void showTable(){
		//得到表名
		tables = getTableName();
		for(String table:tables)
		Log.i("tables:   ", ""+table);
		ArrayAdapter<String> arrayAdapter = new ArrayAdapter<String>(this,
				android.R.layout.simple_list_item_1,tables);
		list.setAdapter(arrayAdapter);
	}
	
	//列出的表名，被单击事件
	public void tableClickListener(){
		list.setOnItemClickListener(new OnItemClickListener(){
			@Override
			public void onItemClick(AdapterView<?> arg0, View arg1, int position,
					long arg3) {
				Toast.makeText(MainActivity.this, "点击了："+tables[position], Toast.LENGTH_SHORT).show();
				Bundle data = new Bundle();
				//存入所打开表的名称
				data.putString("tableName", tables[position]);
				TableDataInfo dataInfo = new TableDataInfo(db,tables[position]);
				//存入数据表行数
				data.putInt("tableRowNum", dataInfo.getTableRowNum());
				//存入数据表列数
				data.putInt("tableColNum", dataInfo.getTableColNum());
				//存入数据表表头名称
				data.putStringArray("tableColumnName", dataInfo.getTableColumnName());
				//利用序列化存入表的所有内容
				data.putSerializable("tableDataInfo", new BundleDataInfo(dataInfo.getTableDataInfo()));
				Intent intent = new Intent(MainActivity.this,ShowSQLDataTable.class);
				intent.putExtras(data);
				startActivity(intent);
			}	
		});
	}
	
	//列表长按事件
	public void tableLongClickListener(){
		list.setOnItemLongClickListener(new OnItemLongClickListener(){
			@Override
			public boolean onItemLongClick(AdapterView<?> arg0, View arg1,
					int arg2, long arg3) {
				Toast.makeText(MainActivity.this, "长按了："+tables[arg2], Toast.LENGTH_SHORT).show();
				final String tableName = tables[arg2];
				PopupMenu popupMenu = new PopupMenu(MainActivity.this,arg1);
				getMenuInflater().inflate(R.menu.table_list_menu, popupMenu.getMenu());
				//为弹出菜单设置监听器
				popupMenu.setOnMenuItemClickListener(new OnMenuItemClickListener(){
					@Override
					public boolean onMenuItemClick(MenuItem item) {
						// TODO Auto-generated method stub
						if(item.getItemId() == R.id.delete){
							db.execSQL("drop table "+tableName);
							//更新下表的目录
							showTable();
						}
						return true;
					}
				});
				popupMenu.show();
				return true;//true即不会再发生单击事件，false 紧接着发生单击事件
			}
		});
	}
}

ShowSQLDataTable.java:

package com.example.mysqlitetest2;

import android.app.ActionBar;
import android.app.Activity;
import android.graphics.Color;
import android.os.Bundle;
import android.view.MenuItem;
import android.widget.TableLayout;
import android.widget.TableRow;
import android.widget.TextView;

import com.example.tableData.BundleDataInfo;

public class ShowSQLDataTable extends Activity{
	private TableLayout mytable;
	private TableRow tb;
	private ActionBar actionBar;
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		// TODO Auto-generated method stub
		super.onCreate(savedInstanceState);
		setContentView(R.layout.data_table);
		//actionBar设置返回键
		actionBar = getActionBar();
		actionBar.setDisplayHomeAsUpEnabled(true);
		//设置标题
		String tableName = this.getIntent().getStringExtra("tableName");
		this.setTitle(tableName+"数据表");
		
		//表的名称
		String []s = this.getIntent().getStringArrayExtra("tableColumnName");
		mytable = (TableLayout) findViewById(R.id.mytable);
		tb = (TableRow) findViewById(R.id.columnName);
		for(int i = 0;i < s.length;i++){
			TextView tv = new TextView(this);
			tv.setBackgroundResource(R.drawable.table_style1);
			new Color();
			//tv.setBackgroundColor(Color.parseColor("#9e9478"));
			tv.setTextSize(30);
			tv.setText(s[i]);
			tb.addView(tv);
		}
		
		//表内容
		BundleDataInfo ss= (BundleDataInfo) this.getIntent().getSerializableExtra("tableDataInfo");
		for(int i = 0;i< ss.getDataInfo().length;i++){
			System.out.println(ss.getDataInfo()[i][0]);
		}
		int colLong = this.getIntent().getIntExtra("tableColNum", 0);
		for(int i = 0;i< ss.getDataInfo().length;i++){//行
			TableRow tableRow = new TableRow(this);
			for(int j = 0;j < colLong;j++){//列
				System.out.println(ss.getDataInfo()[i][j]);
				TextView tv = new TextView(this);
				tv.setBackgroundResource(R.drawable.table_style2);
				tv.setTextSize(30);
				tv.setText(""+ss.getDataInfo()[i][j]);
				tableRow.addView(tv);
			}
			mytable.addView(tableRow);
		}
	}
	//对actionBar返回键响应设置
	public boolean onOptionsItemSelected(MenuItem item)
	  {
	      // TODO Auto-generated method stub
	      if(item.getItemId() == android.R.id.home)
	      {
	          finish();
	          return true;
	      }
	      return super.onOptionsItemSelected(item);
	  }
}

BundleDataInfo.java:

package com.example.tableData;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

public class BundleDataInfo implements Serializable{
	private Object [][] tableInfo;
	public BundleDataInfo(Object [][] tableInfo){
		this.tableInfo = tableInfo;
	}
	public Object[][] getDataInfo(){
		return tableInfo;
	}
}

TableDataInfo.java:

package com.example.tableData;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class TableDataInfo{
	private Cursor cursor;
	private String[][] dataInfo;
	private int tableRowNum;
	private int tableColNum;
	private String[] tableColName;
	public TableDataInfo(SQLiteDatabase db,String tableName){
		//利用游标的到查询的数据
		String sql = "select * from "+tableName;
		cursor = db.rawQuery(sql, null);
	}
	
	/**
	 * 获取表的行数
	 * @return
	 */
	public int getTableRowNum(){
		tableRowNum = cursor.getCount();
		return tableRowNum;
	}
	
	/**
	 * 获取表的列数
	 * @return
	 */
	public int getTableColNum(){
		tableColNum = cursor.getColumnCount();
		return tableColNum;
	}
	
	/**
	 * 获取表中列表名字
	 * @return
	 */
	public String [] getTableColumnName(){
		tableColName = cursor.getColumnNames();
		return tableColName;
	}
	
	/**
	 * 得到数据表的所有内容
	 * @return
	 */
	public Object[][] getTableDataInfo(){
		Data();
		return dataInfo;
	}
	/**
	 * 将数据表的所以内容存于dataInfo二维数组中
	 */
	public void Data(){
		//用了移动到下一行
		dataInfo = new String[tableRowNum][tableColNum];
//		Log.i("行tableRowNum:", ""+tableRowNum);
//		Log.i("列tableColNum:", ""+tableColNum);
			int next = 0;
			while(cursor.moveToNext()){
				for(int i = 0;i < tableColNum;i++){
					
					Log.i("next:",i+"  "+cursor.getString(i));//
					dataInfo[next][i] = cursor.getString(i);
					
				}
				next++;
			}
		}
	}
	

接下来是android界面的xml代码：

结构图：

activity_main.xml:

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical" >
    <TextView 
        android:layout_width="match_parent"
	    android:layout_height="wrap_content"
	    android:textSize="18dp"
	    android:text="请输入SQL语句："
        />
	<EditText 
	    android:id="@+id/text"
	    android:layout_width="match_parent"
	    android:layout_height="wrap_content"/>
	<Button 
	    android:layout_width="match_parent"
	    android:layout_height="wrap_content"
	    android:text="提交"
	    android:onClick="click"/>
	<TextView 
	    android:layout_width="wrap_content"
	    android:layout_height="wrap_content"
	    android:layout_marginTop="5dp"
	    android:textSize="18dp"
	    android:text="数据库中各表："/>
	<ListView 
	    android:id="@+id/list"
	    android:layout_marginTop="5dp"
	    android:layout_width="match_parent"
	    android:layout_height="wrap_content"
	    />
</LinearLayout>

data_table.xml:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >
   <!-- 定义垂直的滚动条 -->
    <ScrollView 
        android:layout_width="match_parent"
		android:layout_height="wrap_content">
		<!-- 定义横向的滚动条 -->
        <HorizontalScrollView 
            android:layout_width="match_parent"
			android:layout_height="wrap_content">
		    <TableLayout
		        	android:id="@+id/mytable"
		        	android:layout_width="wrap_content"
			        android:layout_height="wrap_content"
			        android:layout_marginTop="20dp">
			    <TableRow 
			        android:id="@+id/columnName"
			        android:layout_width="match_parent"
			        android:layout_height="wrap_content">
			    </TableRow>
			</TableLayout>
		</HorizontalScrollView>
	</ScrollView>
</LinearLayout>

table_list_menu.xml:(此代码用于长按列表名，弹出选项删除)

<?xml version="1.0" encoding="utf-8"?>
<menu xmlns:android="http://schemas.android.com/apk/res/android" >
    <group>
        <item
            android:id="@+id/delete"
            android:title="删除"/>
    </group>
</menu>

ok,到此为止，希望对您有所帮助，good luck！