SQLite操作
以纯SQL语言的方式,对SQLite的基本操作,打开数据库(在指定路径)、插入、删除、修改、查询、删除表。
在插入时判断关键字存在否?是否插入。
输出到可滚动的TextView。
1、布局文件
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:orientation="vertical">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="50dp"
android:layout_marginLeft="2dp"
android:orientation="horizontal">
<Button
android:id="@+id/button"
android:layout_width="56dp"
android:layout_height="wrap_content"
android:layout_gravity="center"
android:onClick="onOpenDB"
android:text="打开"
android:textSize="12sp" />
<Button
android:id="@+id/button2"
android:layout_width="56dp"
android:layout_height="wrap_content"
android:layout_gravity="center"
android:onClick="onInsertData"
android:text="插入"
android:textSize="12sp" />
<Button
android:id="@+id/button3"
android:layout_width="56dp"
android:layout_height="wrap_content"
android:layout_gravity="center"
android:onClick="onDeleteData"
android:text="删除"
android:textSize="12sp" />
<Button
android:id="@+id/button4"
android:layout_width="56dp"
android:layout_height="wrap_content"
android:layout_gravity="center"
android:onClick="onUpdateData"
android:text="修改"
android:textSize="12sp" />
<Button
android:layout_width="56dp"
android:layout_height="wrap_content"
android:layout_gravity="center"
android:onClick="onQueryData"
android:text="查询"
android:textSize="12sp" />
<Button
android:layout_width="72dp"
android:layout_height="wrap_content"
android:layout_gravity="center"
android:onClick="onDropTable"
android:text="删除表"
android:textSize="12sp" />
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="58dp"
android:layout_marginLeft="8dp"
android:orientation="horizontal">
<EditText
android:id="@+id/eID"
android:layout_width="76dp"
android:layout_height="48dp"
android:layout_gravity="center"
android:ems="8"
android:hint="ID"
android:inputType="number"
android:textSize="14sp" />
<EditText
android:id="@+id/eName"
android:layout_width="116dp"
android:layout_height="48dp"
android:layout_gravity="center"
android:ems="8"
android:hint="Name(Field)"
android:inputType="textPersonName"
android:textSize="14sp" />
<EditText
android:id="@+id/ePhone"
android:layout_width="154dp"
android:layout_height="48dp"
android:layout_gravity="center"
android:ems="8"
android:hint="Phone(Value)"
android:inputType="textPersonName"
android:textSize="14sp" />
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="330dp"
android:orientation="horizontal">
<TextView
android:id="@+id/dbList"
android:layout_width="324dp"
android:layout_height="wrap_content"
android:layout_marginLeft="18dp"
android:maxLines="11"
android:background="@drawable/textview_border"
android:textColor="#FF000000"
android:scrollbars="vertical"
android:singleLine="false"
android:text="TextView" />
</LinearLayout>
</LinearLayout>
2、代码文件
package com.ccc.cwh.cmdb;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.text.method.ScrollingMovementMethod;
import android.view.View;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;
import java.io.File;
public class MainActivity extends AppCompatActivity {
SQLiteDatabase dbSQL = null;
private TextView dbList;
private EditText eID;
private EditText eName;
private EditText ePhone;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbList = (TextView) this.findViewById(R.id.dbList);
dbList.setMovementMethod(ScrollingMovementMethod.getInstance());
eID = (EditText) this.findViewById(R.id.eID);
eName = (EditText) this.findViewById(R.id.eName);
ePhone = (EditText) this.findViewById(R.id.ePhone);
OpenSQLiteDB();
ShowQueryData();
}
@Override
public void onDestroy() {
super.onDestroy();
if (dbSQL != null) dbSQL.close();
}
public void OpenSQLiteDB() {
File sDB = new File("/storage/emulated/0/ATBW/DB/cSQLa.db");//指定数据库路径
dbSQL = SQLiteDatabase.openOrCreateDatabase(sDB, null);
String sql = "create table if not exists aTable(_id integer primary key autoincrement,sName text,sPhone text)";
dbSQL.execSQL(sql);
sql = "create table if not exists bTable(_id integer primary key autoincrement,sID text,sName text,sPhone text)";
dbSQL.execSQL(sql);
}
public void onOpenDB(View v) {
ShowToast("onOpenDB");
OpenSQLiteDB();
}
public void onInsertData(View v) {
ShowToast("onInsertData");
// 判断 ID 存在?
String sql = String.format("select * from bTable where sID = %s", eID.getText());
Cursor cursor = dbSQL.rawQuery(sql, null);
if (cursor.getCount() > 0) {
sql = String.format("该ID : %s 已存在!", eID.getText());
ShowToast(sql);
return;
}
sql = String.format("insert into bTable(sID,sName,sPhone) values('%s', '%s', '%s')", eID.getText(), eName.getText(), ePhone.getText());
dbSQL.execSQL(sql);
ShowQueryData();
}
public void onDeleteData(View v) {
ShowToast("onDeleteData");
String sql = String.format("delete from bTable where sID = %s", eID.getText());
dbSQL.execSQL(sql);
ShowQueryData();
}
public void onUpdateData(View v) {
ShowToast("onUpdateData");
// 这里 eName.getText() == 字段名, ePhone.getText() == 新值
String sql = String.format("update bTable set %s = '%s' where sID = %s", eName.getText(), ePhone.getText(), eID.getText());
dbSQL.execSQL(sql);
ShowQueryData();
}
public void onQueryData(View v) {
ShowToast("onQueryData");
ShowQueryData();
}
public void onDropTable(View v) {
ShowToast("onDropTable");
String sql = "drop table if exists bTable";
dbSQL.execSQL(sql);
ShowQueryData();
}
public void ShowQueryData() {
//Cursor cursor = dbSQL.query("bTable", null, null, null, null, null, null); //查询获得游标
Cursor cursor = dbSQL.rawQuery("select * from bTable order by sID", null);
int nCount = cursor.getCount();
if (nCount == 0) return;
String s = "";
cursor.moveToFirst();
for (int i = 0; i < nCount; i++) {
String sID = cursor.getString(1); //获得ID
String sName = cursor.getString(2); //获得用户名
String sPhone = cursor.getString(3); //获得电话
String ss = String.format("%s %s %s", sID, sName, sPhone);
if (s.equals("")) s = ss;
else s = s + "\n" + ss;
cursor.moveToNext();
}
dbList.setText(s); //输出用户信息
}
public void ShowToast(String sShow) {
Toast.makeText(this, sShow, Toast.LENGTH_SHORT).show();
}
}
3、增加相应的权限
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.ccc.cwh.cmdb">
<application
android:allowBackup="true"
android:icon="@mipmap/ic_launcher"
android:label="@string/app_name"
android:roundIcon="@mipmap/ic_launcher_round"
android:supportsRtl="true"
android:theme="@style/AppTheme">
<activity android:name=".MainActivity">
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
</application>
<uses-permission android:name="android.permission.INTERNET" />
<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />
<uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" />
<uses-permission android:name="android.permission.MOUNT_UNMOUNT_FILESYSTEMS"/>
<uses-permission android:name="android.permission.SYSTEM_ALERT_WINDOW" />
</manifest>