package com.example.wang.testapp3;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.EditText;
import android.widget.Toast;
public class TestActivity2
extends AppCompatActivity {
EditText et_id,et_name,et_sex,et_age;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_test2);
et_id=
(EditText)findViewById(R.id.et_id);
et_name=
(EditText)findViewById(R.id.et_name);
et_sex=
(EditText)findViewById(R.id.et_sex);
et_age=
(EditText)findViewById(R.id.et_age);
}
//初始化数据库
public void bt1_OnClick(View v)
{
//使用工具类得到数据库对象
MyDBHelper myDBHelper=
new MyDBHelper("test.db",1
);
//得到连接
SQLiteDatabase sd =
myDBHelper.getReadableDatabase();
Toast.makeText(TestActivity2.this, "连接数据库成功"
, Toast.LENGTH_SHORT).show();
//关闭连接
sd.close();
}
//初始化数据库
public void bt2_OnClick(View v)
{
//使用工具类得到数据库对象
MyDBHelper myDBHelper=
new MyDBHelper("test.db",2
);
//得到连接
SQLiteDatabase sd =
myDBHelper.getReadableDatabase();
Toast.makeText(TestActivity2.this, "连接数据库成功"
, Toast.LENGTH_SHORT).show();
//关闭连接
sd.close();
}
//插入新数据
public void bt3_OnClick(View v)
{
//1-连接数据库，得到数据库连接对象
SQLiteDatabase sd =
new MyDBHelper("test.db",2
).getReadableDatabase();
//2-准备数据
ContentValues cv=
new ContentValues();
cv.put("name"
,et_name.getText().toString());
cv.put("sex"
,et_sex.getText().toString());
cv.put("age"
,et_age.getText().toString());
//3-调用insert（），插入数据
Long l=sd.insert("t_user",
null, cv);
Toast.makeText(TestActivity2.this, "插入数据的主键="+
l, Toast.LENGTH_SHORT).show();
//4-关闭连接
sd.close();
}
//数据查询
public void bt4_OnClick(View v)
{
//1.得到连接
SQLiteDatabase sd =
new MyDBHelper("test.db",2
).getReadableDatabase();
//2.全表查询
Cursor c=sd.query("t_user",
null,
null,
null,
null,
null,
null);
//3.遍历结果集
while (c.moveToNext())
{
//读取数据
String str="_id="+c.getLong(0)+" name="+c.getString(1)+
" sex="+c.getString(2)+" age="+c.getInt(3
);
Log.e("TAG"
,str);
}
Toast.makeText(TestActivity2.this, "结果集的记录条数="+
c.getCount(), Toast.LENGTH_SHORT).show();
c.close();
//4.关闭连接
sd.close();
}
//带条件数据查询
public void bt5_OnClick(View v)
{
//1.得到连接
SQLiteDatabase sd =
new MyDBHelper("test.db",2
).getReadableDatabase();
String strWhere="1=1"
;
//得到条件
if (et_id.getText().length()>0
)
{
strWhere+="and _id="+
et_id.getText().toString();
}
if (et_name.getText().length()>0
)
{
strWhere+=" and name like '%"+et_name.getText().toString()+"%'"
;
}
if (et_sex.getText().length()>0
)
{
strWhere+=" and sex = '"+et_sex.getText().toString()+"'"
;
}
if (et_age.getText().length()>0
)
{
strWhere+=" and age = "+et_age.getText().toString()+""
;
}
//2.全表查询
Cursor c=sd.query("t_user",
null, strWhere,
null,
null,
null,
null);
//3.遍历结果集
while (c.moveToNext())
{
//读取数据
String str="_id="+c.getLong(0)+" name="+c.getString(1)+
" sex="+c.getString(2)+" age="+c.getInt(3
);
Log.e("TAG"
,str);
}
Toast.makeText(TestActivity2.this, "结果集的记录条数="+
c.getCount(), Toast.LENGTH_SHORT).show();
c.close();
//4.关闭连接
sd.close();
}
//修改数据
public void bt6_OnClick(View v)
{
//1.得到连接
SQLiteDatabase sd =
new MyDBHelper("test.db",2
).getReadableDatabase();
//修改
ContentValues cv=
new ContentValues();
cv.put("name"
, et_name.getText().toString());
cv.put("sex"
, et_sex.getText().toString());
cv.put("age"
, et_age.getText().toString());
int i= sd.update("t_user", cv, "_id=?",
new String[]{et_id.getText().toString()});
Toast.makeText(TestActivity2.this, "修改的记录条数="+
i, Toast.LENGTH_SHORT).show();
//4.关闭连接
sd.close();
}
//删除数据
public void bt7_OnClick(View v)
{
//1.得到连接
SQLiteDatabase sd =
new MyDBHelper("test.db",2
).getReadableDatabase();
//2.删除
int i= sd.delete("t_user", "_id=?",
new String[]{et_id.getText().toString()});
Toast.makeText(TestActivity2.this, "删除的记录条数="+
i, Toast.LENGTH_SHORT).show();
//4.关闭连接
sd.close();
}
//数据库事务
public void bt8_OnClick(View v)
{
SQLiteDatabase sd =
new MyDBHelper("test.db",2
).getReadableDatabase();
try {
//1.得到连接
sd.beginTransaction();//1-启动事务
//修改
ContentValues cv=
new ContentValues();
cv.put("age", "44"
);
//修改1
int i= sd.update("t_user", cv, "_id=1",
null);
//抛出异常
if (i>0
)
{
throw new RuntimeException("模拟事务异常"
);
}
ContentValues cv1=
new ContentValues();
cv1.put("age", "54"
);
//修改2
int k= sd.update("t_user", cv1, "_id=2",
null);
Toast.makeText(TestActivity2.this, "修改的记录条数="+(i+
k), Toast.LENGTH_SHORT).show();
//设置事务是成功完成的
sd.setTransactionSuccessful();
//结束事务
//1.没有异常，提交事务
//2.发生异常，回滚事务
}
catch (Exception e)
{
e.printStackTrace();
Toast.makeText(TestActivity2.this, "修改出错"
, Toast.LENGTH_SHORT).show();
}
finally {
//一定会被执行的代码
sd.endTransaction();
//4.关闭连接
sd.close();
}
}
//实现SQLiteOpenHelper的内部类
class MyDBHelper
extends SQLiteOpenHelper
{
//构造方法
public MyDBHelper(String dbname,
int ver)
{
//显示调用父类的构造方法
//必须在第一行
super(TestActivity2.
this,dbname,
null,ver);
}
//创建和初始化数据库
@Override
public void onCreate(SQLiteDatabase db) {
//1-执行创建数据库的语句
String sql="CREATE TABLE t_user" + " (\"_id\" INTEGER AUTOINCREMENT NOT NULL," +
"\"name\" VARCHAR(20) NOT NULL," +
"\"sex\" CHAR(1)," +
"\"age\" INTEGER," +
"PRIMARY KEY (\"_id\"))"
;
db.execSQL(sql);
Log.e("TAG","表创建成功"
);
//2-执行初始化数据的语句，insert语句
ContentValues cv =
new ContentValues();
cv.put("name","张三"
);
cv.put("sex","男"
);
cv.put("age", 20
);
//执行插入
Long l=db.insert("t_user",
null, cv);
Log.e("TAG", "初始化数据=" +
l);
}
//升级数据库
@Override
public void onUpgrade(SQLiteDatabase db,
int oldVersion,
int newVersion) {
//修改数据
if(newVersion==2
)
{
ContentValues cv=
new ContentValues();
cv.put("name","李四"
);
String sql="update t_user set name='李四' where _id=1"
;
//调用数据库连接类的方法
String[] str={"1","18"
};
int i=db.update("t_user", cv, "_id=? and age>?"
,str);
Log.e("TAG","升级数据 数据条数="+
i);
}
}
}
}
java
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:paddingBottom="@dimen/activity_vertical_margin"
android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin"
tools:context="com.example.wang.testapp3.TestActivity2"
android:orientation="vertical">
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="初始化数据库"
android:onClick="bt1_OnClick"/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="升级数据库"
android:onClick="bt2_OnClick"/>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content">
<EditText
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:hint="id"
android:id="@+id/et_id"/>
<EditText
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:hint="name"
android:id="@+id/et_name"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content">
<EditText
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:hint="sex"
android:id="@+id/et_sex"/>
<EditText
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:hint="age"
android:id="@+id/et_age"/>
</LinearLayout>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="新增数据"
android:onClick="bt3_OnClick"/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="数据全部查询"
android:onClick="bt4_OnClick"/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="带条件查询数据"
android:onClick="bt5_OnClick"/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="修改数据"
android:onClick="bt6_OnClick"/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="删除数据"
android:onClick="bt7_OnClick"/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="数据库事务"
android:onClick="bt8_OnClick"/>
</LinearLayout>
xml
转载于:https://www.cnblogs.com/wangchuanqi/p/5561496.html
相关资源：数据库事务的语法和基本使用
