第九单元总结

SQLite

1：SQLite-常见操作

create table 表名( g_id integer PRIMARY KEY AUTOINCREMENT NOT NULL, g_name varchar,)

select * from 表名 where t_id=条件值

delete from 表名where id=条件值

insert into 表名 (字段名) values（值）

update 表名 set(字段名) values（值）where id=1

public class MySQLite extends SQLiteOpenHelper {

public MySQLite(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {

super(context, name, factory, version);} }

onCreate(SQLiteDatabase db)：创建数据库，在其中建表

onUpgrade（SQLiteDatabase db, int oldVersion, int newVersion）：更新数据库

MySQLite sqLite = new MySQLite(this, "school.db", null, 1);

SQLiteDatabase writaDb = sqLite.getWritableDatabase();

writaDb.execSQL(“insert into 表名 (字段名) values（值）”);

getWritableDatabase()：得到一个可写的数据库

execSQL()：执行sqL语句

writaDb.execSQL(“delete from 表名 where id=条件值”);

删除语句一般要写条件，如where id=条件值

execSQL()：执行sqL语句

writaDb.execSQL(“update 表名 set(字段名) values（值）where id=1”);

修改语句一般也要写条件，如where id=条件值

execSQL()：执行sqL语句

Cursor cursor = writaDb.rawQuery("select t_id from teacher where t_id = “2”, null);

while(cursor.moveToNext()){

String name=cursor.getString(cursor.getColumnIndex("Pwd"));}

添加数据

ContentValues values = new ContentValues();

values.put("name",”八维”);

values.put("phone", ”13888888888“);

db.insert("user", null, values);

删除数据

db.delete("user", "name=?", new String[]{”八维“});

修改数据

ContentValues values = new ContentValues();

values.put("name",”八维”);

values.put("phone", ”13888888888“);

db.update("user", values, "id=?", new String[]{”3“});

查询数据

Cursor cursor = db.query("user", new String[]{"name", "phone"}, "name = ?", new String[]{"王五"}, null, null, null);

2：

事务

SQLiteDatabase db = getWritableDatabase();

db.beginTransaction();

for (int i=0;i<1000;i++) {

db.execSQL(“insert into 表名 (字段名) values（值）”);

}

db.setTransactionSuccessful();

db.endTransaction();

db.close();

}

SQLite-案例应用

public class MyDbHelper extends SQLiteOpenHelper {

public MyDbHelper(@Nullable Context context) {

super(context, "user.db", null, 1);

}

@Override

public void onCreate(SQLiteDatabase db) {

db.execSQL("CREATE TABLE userInfo(uid integer PRIMARY KEY autoincrement,uname varchar(8),age INT,sex int,birthday date);");

}

@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}

}

public class DatabaseActivity extends AppCompatActivity {

SQLiteDatabase database;

@Override

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.activity_database);

MyDbHelper helper = new MyDbHelper(this);

database = helper.getWritableDatabase();

}

public void insert(){

String[] info = new String[]{"20","0","2020-6-11"};

//方式一

database.execSQL("INSERT INTO userInfo(uname,age,sex,birthday)VALUES(?,?,?,?)",info);

//方式二

ContentValues values = new ContentValues();

values.put("age",25);

values.put("sex",1);

values.put("birthday","2020-6-11");

values.put("uname","戴鑫");

database.insert("userInfo",null,values);

}

public void delete(){

String[] strings = new String[]{"20","1"};

//方式一

database.execSQL("DELETE FROM userInfo WHERE age=?",strings);

//方式二

database.delete("userInfo","age=? or sex=?",strings);

}

public void update(){

String sql = "update userInfo set age = ? where age = ? or uname=?";

String[] strings = new String[]{"25","戴鑫"};

//方式一

database.execSQL(sql,strings);

ContentValues contentValues = new ContentValues();

contentValues.put("age",35);

contentValues.put("uname","戴金");

//方式二

database.update("userInfo",contentValues,"age=? or uname=?",strings);

}

public void select(){

//方式一

Cursor cursor = database.rawQuery("select * from userInfo",null);

//方式二

Cursor cursor1 = database.query("userInfo",null,null,null,null,null,null);

while (cursor.moveToNext()){

int id = cursor.getInt(cursor.getColumnIndex("uid"));

String name = cursor.getString(cursor.getColumnIndex("uname"));

int age = cursor.getInt(cursor.getColumnIndex("age"));

int sex = cursor.getInt(cursor.getColumnIndex("sex"));

String birthday = cursor.getString(cursor.getColumnIndex("birthday"));

Log.d("amy","--"+id+name+age+sex+birthday);

}

}

4：SQLite-操作对象

功能分析

注册功能：向数据表中增加用户对象

修改功能：修改数据库中的用户对象

登录功能：查询数据库中的用户对象

/**

* 操作数据库的工具类

*/

public class UserDAO {

SQLiteDatabase database;

String table;

private static UserDAO userDAO = null;

private UserDAO(){

}

public static UserDAO getInstance(SQLiteDatabase database,String table){

if(userDAO == null){

userDAO = new UserDAO(database,table);

return userDAO;

}

else

return userDAO;

}

private UserDAO(SQLiteDatabase database,String table){

this.database = database;

this.table = table;

}

public long add(User user){

ContentValues values = new ContentValues();

values.put("uname",user.name);

values.put("age",user.age);

values.put("sex",user.sex);

values.put("birthday",user.birthday.toString());

return database.insert(table,null,values);}

public void addAll(ArrayList<User> users){

//开启事务

database.beginTransaction();

for(User user:users){

ContentValues values = new ContentValues();

values.put("uname",user.name);

values.put("age",user.age);

values.put("sex",user.sex);

values.put("birthday",user.birthday.toString());

database.insert(table,null,values);

}

//设置事务成功

database.setTransactionSuccessful();

//结束事务

database.endTransaction();

}

public void delete(User u){

database.delete(table,"uid=?",new String[]{u.id+""});

}

public void updateUser(User user){

ContentValues values = new ContentValues();

values.put("uname",user.name);

database.update(table,values,"uid = ?",new String[]{user.id+""});

}

public void select(){

Cursor cursor = database.query(table,null,null,null,null,null,null);

while (cursor.moveToNext()){

//cursor.getInt(0);

int id = cursor.getInt(cursor.getColumnIndex("uid"));

String name = cursor.getString(cursor.getColumnIndex("uname"));

int age = cursor.getInt(cursor.getColumnIndex("age"));

String sex = cursor.getString(cursor.getColumnIndex("sex"));

String birthday = cursor.getString(cursor.getColumnIndex("birthday"));

Log.d("amy",id+"-"+name+"-"+age+"-"+sex+"-"+birthday);

}

}

}

public class User {

public int id;

public String sex;

public String name;

public Date birthday;

public int age;

public User(String name ,int age,String sex, Date birthday) {

this.sex = sex;

this.name = name;

this.birthday = birthday;

this.age = age;

}

public User(){

}

}