目录
创建表
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
建立数据库的方法
实例化数据库
MySQLite sqLite = new MySQLite(this, "school.db", null, 1); SQLiteDatabase writaDb = sqLite.getWritableDatabase();
添加数据
writaDb.execSQL(“insert into 表名 (字段名) values(值)”);
删除数据
writaDb.execSQL(“delete from 表名 where id=条件值”);
修改数据
writaDb.execSQL(“update 表名 set(字段名) values(值)where id=1”);
查询数据
Cursor cursor = writaDb.rawQuery("select t_id from teacher where t_id = “2”, null);
while(cursor.moveToNext()){
String name=cursor.getString(cursor.getColumnIndex("Pwd"));}
事务
如何使用事务批量添加数据
SQLiteDatabase db = getWritableDatabase();
db.beginTransaction();
for (int i=0;i<1000;i++) {
db.execSQL(“insert into 表名 (字段名) values(值)”);
}
db.setTransactionSuccessful();
db.endTransaction();
db.close();
}
事务的特点
Sqlite—案例应用
SQLite-常用类及方法
SQLiteOpenHelper
用来管理数据库的增删改查的创建和更新
SQLiteDatebase
1、execSQL:执行sql的增删改操作
2、rawQuery:执行查的操作
项目应用
1、继承SQLiteOpenHelper重写方法
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) {
}
}
2、获得SQLiteDatabase对象操作数据
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);
}
}
SQLite-操作对象
SQLite导入(老规矩直接代码起手)
1、封装UserDAO
/**
* 操作数据库的工具类
*/
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);
}
}
}
2、创建User实体类
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(){
}
}