package com.sample.Sample_8_4;
import android.app.Activity;
import android.app.AlertDialog;
import android.content.ContentValues;
import android.content.DialogInterface;
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.Toast;
public class Sample_8_4 extends Activity {
private Button baseButton;
private Button tableButton;
private Button btn_sqlmod, btn_cvmod, btn_qur;
private final String dbName = "mydb";
private final String tableName = "users";
private SQLiteDatabase db = null;
private int i = 1;
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
this.setContentView(R.layout.main);
baseButton = (Button) findViewById(R.id.base); // 实例化Button对象
baseButton.setOnClickListener(new OnClickListener() { // 为Button对象添加监听
public void onClick(View v) {
db = openOrCreateDatabase(dbName, MODE_PRIVATE, null); // 创建名为“mydb”的数据库
db.endTransaction();
Toast.makeText(getApplicationContext(), "创建数据库成功", 1000)
.show();
}
});
tableButton = (Button) findViewById(R.id.table); // 实例化Button对象
tableButton.setOnClickListener(new OnClickListener() { // 为Button对象添加监听
public void onClick(View v) {
if (db != null) {
creatTable(); // 开始创建数据库表
} else {
Toast.makeText(getApplicationContext(), "没有数据库",
1000).show();
}
}
});
btn_sqlmod = (Button) findViewById(R.id.sql_mod);
btn_sqlmod.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
if (db != null) {
sql_executeData();
} else {
Toast.makeText(getApplicationContext(), "没有数据库", 1000)
.show();
}
}
});
btn_cvmod = (Button) findViewById(R.id.cv_mod);
btn_cvmod.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
if (db != null) {
cv_executeData();
} else {
Toast.makeText(getApplicationContext(), "没有数据库", 1000)
.show();
}
}
});
btn_qur = (Button) findViewById(R.id.que);
btn_qur.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
if (db != null) {
queryData();
} else {
Toast.makeText(getApplicationContext(), "没有数据库", 1000)
.show();
}
}
});
}
public void creatTable() {
// 创建表的SQL语句,创建一个名为users的表,该表有id,uname和pwd三个字段
String sql = "CREATE TABLE IF NOT EXISTS "
+ tableName
+ " (id INTEGER PRIMARY KEY AUTOINCREMENT, uname VARCHAR(50), pwd VARCHAR(50));";
db.execSQL(sql); // 执行sql语句
db.endTransaction();
// 查询sqlite_master表中类型为table的记录的name字段
Cursor cursor = db.query("sqlite_master", new String[] { "name" },
"type = ?", new String[] { "table" }, null, null, null, null);
String tables = "";
if (cursor.getCount() != 0) { // 判断查询结果的条数是否为0
cursor.moveToFirst(); // 游标指向第一条记录
for (int i = 0; i < cursor.getCount(); i += 1) {
tables = tables + cursor.getString(0) + " "; // 累加字符串
cursor.moveToNext(); // 游标下移
}
}
// 把累加的结果显示在一个信息框中
new AlertDialog.Builder(Sample_8_4.this).setTitle("Message")
.setMessage(tables)
.setNegativeButton("确定", new DialogInterface.OnClickListener() {
public void onClick(DialogInterface dialog, int which) {
}
}).show();
}
public void sql_executeData() {
String sql;
for (; i < 4; i++) {
sql = "insert into " + tableName + " values ('" + i
+ "','a','123456')"; // 添加一条记录
db.execSQL(sql);
db.endTransaction();
}
sql = "update " + tableName + " set pwd='654321' where id='1'"; // 修改一条记录
db.execSQL(sql);
db.endTransaction();
sql = "delete from " + tableName + " where id='2'"; // 删除一条记录
db.execSQL(sql);
db.endTransaction();
Toast.makeText(getApplicationContext(), "使用SQL语句修改数据成功", 1000).show();
}
public void cv_executeData() {
ContentValues cv = new ContentValues(); // 实例化ContentValues对象
cv.put("uname", "b"); // 插入字段值
cv.put("pwd", "987654"); // 插入字段值
db.insert(tableName, null, cv); // 执行insert方法
db.insert(tableName, null, cv); // 执行insert方法
db.insert(tableName, null, cv); // 执行insert方法
cv = new ContentValues(); // 实例化ContentValues对象
cv.put("pwd", "456789"); // 插入字段值
db.update(tableName, cv, "id=?", new String[] { "4" }); // 执行update方法
db.delete(tableName, "id=?", new String[] { "5" }); // 执行delete操作
Toast.makeText(getApplicationContext(), "使用Android语句修改数据成功", 1000)
.show();
}
public void queryData() {
Cursor cursor = db.query(tableName, null, null, null, null, null, null,
null); // 执行query操作获得Cursor对象
String str = "";
if (cursor.getCount() != 0) { // 判断返回的记录条数
cursor.moveToFirst(); // 游标指向第一条记录
for (int i = 0; i < cursor.getCount(); i += 1) {
str = str + cursor.getString(0) + " " + cursor.getString(1)
+ " " + cursor.getString(2) + "\n"; // 获取一条记录的每个字段的值
cursor.moveToNext(); // 游标指向下一条记录
}
}
// 在信息框上显示所有记录信息
new AlertDialog.Builder(Sample_8_4.this).setTitle("Message")
.setMessage(str)
.setNegativeButton("确定", new DialogInterface.OnClickListener() {
public void onClick(DialogInterface dialog, int which) {
}
}).show();
}
}