package com.hanqi.cunchu;
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 shujuku extends AppCompatActivity {
EditText name, id, sex, age;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.shujuku);
id = (EditText) findViewById(R.id.i1);
name = (EditText) findViewById(R.id.i2);
sex = (EditText) findViewById(R.id.i3);
age = (EditText) findViewById(R.id.i4);
}
//初始化数据库
public void b1(View view) {
//使用工具类得到数据库对象
//test表名
mdb mdb = new mdb("test", 1);
//得到连接
SQLiteDatabase sqLiteDatabase = mdb.getWritableDatabase();
//关闭连接
sqLiteDatabase.close();
}
//升级数据库
public void b2(View view) {
//使用工具类得到数据库对象
mdb mdb = new mdb("test", 2);
//得到连接
SQLiteDatabase sqLiteDatabase = mdb.getWritableDatabase();
//关闭连接
sqLiteDatabase.close();
}
//增加数据
public void b3(View view) {
//连接数据库,得到数据库连接对象
//注意版本号
SQLiteDatabase sqLiteDatabase = new mdb("test", 2).getWritableDatabase();
//得到数据
ContentValues cv = new ContentValues();
cv.put("_id", id.getText().toString());
cv.put("name", name.getText().toString());
cv.put("sex", sex.getText().toString());
cv.put("age", age.getText().toString());
//调用 insert(),插入数据
//添加表名。不是文件名
long l = sqLiteDatabase.insert("NewTable", null, cv);
Toast.makeText(shujuku.this, "增加的数据条数" + l, Toast.LENGTH_SHORT).show();
//关闭连接
sqLiteDatabase.close();
}
//全表查询数据
public void b4(View view) {
//连接数据库,得到数据库连接对象
SQLiteDatabase sqLiteDatabase = new mdb("test", 2).getWritableDatabase();
//搜索全表字段,null代表搜索条件
Cursor c = sqLiteDatabase.query("NewTable", null, null, null, null, null, null);
//查询到的结果条数c.getCount();
//遍历结果
while (c.moveToNext()) {
//读取数据
//getstring(表中的排列顺序,从0开始,类似索引)
//c.getColumnIndex("_id")返回字段名对应的序号
String st = c.getLong(c.getColumnIndex("_id")) + "name=" + c.getString(1) + "sex=" + c.getString(2)
+ "age=" + c.getString(3);
String st2 = c.getLong(c.getColumnIndex("_id")) + "name=" + c.getString(0);
Log.e("tag", st);
Log.e("tag", st2);
}
//关闭连接
sqLiteDatabase.close();
c.close();
}
//条件查询
public void b5(View view) {
//连接数据库,得到数据库连接对象
SQLiteDatabase sqLiteDatabase = new mdb("test", 2).getWritableDatabase();
//防止为空where and之间没条件报错
//!!!注意语句之间的
String s = "1=1";
//得到条件
if (id.getText().length() > 0) {
s += " and _id=" + id.getText().toString();
}
if (name.getText().length() > 0) {
s += " and name like'%" + id.getText().toString() + "%'";
}
if (age.getText().length() > 0) {
s += " and age=" + id.getText().toString();
}
if (sex.getText().length() > 0) {
s += " and sex=" + id.getText().toString();
}
//条件搜索
Cursor c = sqLiteDatabase.query("NewTable", null, s, null, null, null, null);
while (c.moveToNext()) {
//读取数据
//getstring(表中的排列顺序,从0开始,类似索引)
//c.getColumnIndex("_id")返回字段名对应的序号
String st = c.getLong(c.getColumnIndex("_id")) + "name=" + c.getString(1) + "sex=" + c.getString(2)
+ "age=" + c.getString(3);
String st2 = c.getLong(c.getColumnIndex("_id")) + "name=" + c.getString(0);
Log.e("tag", st);
Log.e("tag", st2);
}
//关闭连接
sqLiteDatabase.close();
c.close();
}
//修改数据
public void b6(View view) {
//连接数据库,得到数据库连接对象
SQLiteDatabase sqLiteDatabase = new mdb("test", 2).getWritableDatabase();
//修改
ContentValues cv=new ContentValues();
cv.put("name", name.getText().toString());
cv.put("sex", sex.getText().toString());
cv.put("age", age.getText().toString());
int i= sqLiteDatabase.update("NewTable",cv,"_id=?",new String[]{id.getText().toString()});
Toast.makeText(shujuku.this, "修改的记录条数="+i, Toast.LENGTH_SHORT).show();
//关闭连接
sqLiteDatabase.close();
}
//删除数据
public void b7(View view) {
//连接数据库,得到数据库连接对象
SQLiteDatabase sqLiteDatabase = new mdb("test", 2).getWritableDatabase();
//删除
//(,"1",)代表所有数据
int i=sqLiteDatabase.delete("NewTable","_id=?",new String[]{id.getText().toString()});
Toast.makeText(shujuku.this, "删除的记录条数"+i, Toast.LENGTH_SHORT).show(); //关闭连接 sqLiteDatabase.close(); }
public void b8(View view) {
//连接数据库,得到数据库连接对象
SQLiteDatabase sqLiteDatabase = new mdb("test", 2).getWritableDatabase();
//启动事务
sqLiteDatabase.beginTransaction();
try {
//修改
ContentValues cv = new ContentValues();
cv.put("age", "20");
ContentValues cv2 = new ContentValues();
cv.put("age", "30");
//修改1
int i = sqLiteDatabase.update("NewTable", cv, "_id=1", null);
//抛出异常中断操作
if (i > 0) {
throw new RuntimeException("模拟事务异常");
}
//修改2
int k = sqLiteDatabase.update("NewTable", cv2, "_id=2", null);
Toast.makeText(shujuku.this, "修改的记录条数=" + (i + k), Toast.LENGTH_SHORT).show();
//设置事务是成功完成的
sqLiteDatabase.setTransactionSuccessful();
}
catch (Exception e){
e.printStackTrace();
Toast.makeText(shujuku.this, "失败", Toast.LENGTH_SHORT).show();
}
finally {
//结束事务
//1.没有异常,提交事务
//2.发生异常,回本事务
sqLiteDatabase.endTransaction();
//关闭连接
sqLiteDatabase.close();
}
}
//内部类
class mdb extends SQLiteOpenHelper {
//(要创建的文件名,版本号)
public mdb(String dbname, int ver) {
//显示调用父类的构造方法
//必须在第一行
//(content,数据库名,默认值,版本号)
super(shujuku.this, dbname, null, ver);
}
//创建初始化数据库
@Override
public void onCreate(SQLiteDatabase db) {
//1.执行创建数据的语句
String sql = "CREATE TABLE \"main\".\"NewTable\" (\"_id\" INTEGER NOT NULL," +
"\"name\" varchar(20),\"sex\" char(1)," +
"\"age\" INTEGER,PRIMARY KEY (\"_id\")\n" +
")\n" +
";";
//void execSQL(sql语句);
//通常是建表,修改或删除表等语句
db.execSQL(sql);
Log.e("tag", "创建完成");
//2.执行初始化数据的语句,insert语句
// ContentValues 放入值的方法
ContentValues cv = new ContentValues();
cv.put("name", "张三");
cv.put("sex", "男");
cv.put("age", 15);
//执行插入
//表名,字段的缺省值,要插入的值
long l = db.insert("NewTable", null, cv);
//l返回新插入数据的主键值_id
//失败返回-1
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 NewTable set name='李四' where _id=1";
// db.execSQL(sql);
String s[] = {"1", "18"};
//调用db的更新方法
//"表名",contentValues,带占位符?的where条件,String[]匹配?的条件值的数组
int i = db.update("NewTable", cv, "_id=? and age <?", s);
//i返回改变的数据条数
//失败返回-1
Log.e("tag", "升级数据 数据条数=" + i);
}
}
}
}