// 配置
package com.example.administrator.myapplication.db;
public interface DbConfig {
int DATABASE_VERSION = 1;
String DATABASE_NAME = "zhu_xsd.db";
// 共用字段
String MAIN_ID = "id";
String USER_ID = "user_id";
String CONTENT = "content";
String TIME = "time";
String QUESTION_ID = "question_id";
String QUESTION_TABLE_NAME = "question";
String CREATE_QUESTION_TABLE_SQL = "CREATE TABLE " + QUESTION_TABLE_NAME + "("
+ MAIN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ USER_ID + " INTEGER, "
+ CONTENT + " VARCHAR(1000), "
+ QUESTION_ID + " INTEGER, "
+ TIME + " VARCHAR(32)" + ")";
String DROP_QUESTION_TABLE_SQL = "DROP TABLE " + QUESTION_TABLE_NAME;
String ANSWER_ID = "answer_id";
String ANSWER_TABLE_NAME = "answer";
String CREATE_ANSWER_TABLE_SQL = "CREATE TABLE " + ANSWER_TABLE_NAME + "("
+ MAIN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ USER_ID + " INTEGER, "
+ CONTENT + " VARCHAR(1000), "
+ ANSWER_ID + " INTEGER, "
+ TIME + " VARCHAR(32)" + ")";
String DROP_ANSWER_TABLE_SQL = "DROP TABLE " + ANSWER_TABLE_NAME;
String ONLOOKERS_ID = "onlookers_id";
String ONLOOKERS_TABLE_NAME = "onlookers";
String CREATE_ONLOOKERS_TABLE_SQL = "CREATE TABLE " + ONLOOKERS_TABLE_NAME + "("
+ MAIN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ USER_ID + " INTEGER, "
+ CONTENT + " VARCHAR(1000), "
+ ONLOOKERS_ID + " INTEGER, "
+ TIME + " VARCHAR(32)" + ")";
String DROP_ONLOOKERS_TABLE_SQL = "DROP TABLE " + ONLOOKERS_TABLE_NAME;
}
DAO操作
package com.example.administrator.myapplication.db.dao;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import com.example.administrator.myapplication.db.DbConfig;
import com.example.administrator.myapplication.db.MySQLiteOpenHelper;
import com.example.administrator.myapplication.db.bean.Person;
import java.util.ArrayList;
import java.util.List;
/**
* Description: 利用系统API函数实现数据库的增删改查等操作的类
*/
public class ControlDaoFly {
private MySQLiteOpenHelper helper;
public ControlDaoFly(Context context){
helper = new MySQLiteOpenHelper(context);
}
/**
* 添加一条记录到数据库
*/
public long add (Person person){//插入成功,返回记录的id,插入失败,返回-1;
SQLiteDatabase db = helper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(DbConfig.USER_ID, person.getUserId());
values.put(DbConfig.CONTENT, person.getContent());
values.put(DbConfig.QUESTION_ID, person.getQuestionId());
values.put(DbConfig.TIME, person.getTime());
long id = db.insert(DbConfig.QUESTION_TABLE_NAME, null, values);//DbConfig.QUESTION_TABLE_NAME是该数据库中的表名
db.close();
return id;
}
/**
* 根据question_id修改数据内容
*/
public void modify(String questionId, String content,String userId){
SQLiteDatabase db = helper.getReadableDatabase();
ContentValues values = new ContentValues();
values.put(DbConfig.CONTENT, content);
// db.update(DbConfig.QUESTION_TABLE_NAME, values, DbConfig.QUESTION_ID + " =?", new String[]{questionId});
String update = DbConfig.QUESTION_ID + " =?" + " and " + DbConfig.USER_ID + " =?";
db.update(DbConfig.QUESTION_TABLE_NAME, values, update, new String[]{questionId,userId});
db.close();
}
/**
* 根据question_id删除一条记录
*/
public void delete(String questionId){
SQLiteDatabase db = helper.getWritableDatabase();
db.delete(DbConfig.QUESTION_TABLE_NAME, DbConfig.QUESTION_ID + " =?", new String[]{questionId});
db.close();
}
/**
* 删除表
*/
public void drop(){
SQLiteDatabase db = helper.getWritableDatabase();
// db.execSQL("DROP TABLE " + DbConfig.QUESTION_TABLE_NAME); // 删除表
db.execSQL("DELETE FROM " + DbConfig.QUESTION_TABLE_NAME); // 清空表
db.close();
}
/**
* 根据question_id查找数据
*/
public Person find(String questionId,String userId){
SQLiteDatabase db = helper.getWritableDatabase();
String query = DbConfig.QUESTION_ID + " =?" + " and " + DbConfig.USER_ID + " =?";
Cursor cursor = db.query(DbConfig.QUESTION_TABLE_NAME, null, query, new String[]{questionId,userId}, null, null, null);
Person mPerson = null;
while(cursor.moveToNext()) {
mPerson = new Person();
mPerson.setQuestionId(cursor.getInt(cursor.getColumnIndex(DbConfig.QUESTION_ID)));
mPerson.setContent(cursor.getString(cursor.getColumnIndex(DbConfig.CONTENT)));
mPerson.setTime(cursor.getString(cursor.getColumnIndex(DbConfig.TIME)));
mPerson.setUserId(cursor.getInt(cursor.getColumnIndex(DbConfig.USER_ID)));
}
boolean result = cursor.moveToNext(); //true 存在 ;false 不存在
Log.e("dao = ",result + "");
cursor.close();
db.close();
return mPerson;
}
/**
* 返回全部的数据库信息
* @return
*/
public List<Person> findAll(){
SQLiteDatabase db = helper.getWritableDatabase();
List<Person> persons = new ArrayList<>();
// Cursor cursor = db.query(DbConfig.QUESTION_TABLE_NAME, new String[]{DbConfig.QUESTION_ID,DbConfig.CONTENT,DbConfig.USER_ID,DbConfig.TIME}, null, null, null, null, null);
Cursor cursor = db.query(DbConfig.QUESTION_TABLE_NAME, null, null, null, null, null, null);
Person mPerson;
while(cursor.moveToNext()){
mPerson = new Person();
mPerson.setQuestionId(cursor.getInt(cursor.getColumnIndex(DbConfig.QUESTION_ID)));
mPerson.setContent(cursor.getString(cursor.getColumnIndex(DbConfig.CONTENT)));
mPerson.setTime(cursor.getString(cursor.getColumnIndex(DbConfig.TIME)));
mPerson.setUserId(cursor.getInt(cursor.getColumnIndex(DbConfig.USER_ID)));
persons.add(mPerson);
}
cursor.close();
db.close();
return persons;
}
/**
* 分页查询(根据userId)
* @param userId 用户id
* @param pageSize 每页的大小
* @param page 页数
* @return
*/
public List<Person> findLimit(String userId,int pageSize,int page){
SQLiteDatabase db = helper.getWritableDatabase();
List<Person> persons = new ArrayList<>();
/**
* table="表命",
* columns="要查询的列名",
* selection="查询 条件",
* selectionArgs="条件中用了占位符的参数",
* groupBy="数据分组",
* having="分组后的条件",
* orderBy="排序方式",
* limit="分页查询";
**/
// Cursor cursor= db.query(DbConfig.QUESTION_TABLE_NAME, null, DbConfig.USER_ID + " =?", new String[]{userId},null, null, null, pageSize + "," + page);//"5,2",第3行开始,返回5行数据
String sql= "select * from " + DbConfig.QUESTION_TABLE_NAME + " where " + DbConfig.USER_ID + " = ? " +
" Limit "+String.valueOf(pageSize)+ " Offset " +String.valueOf(page*pageSize);
Cursor cursor = db.rawQuery(sql, new String[]{userId});
Person mPerson;
while(cursor.moveToNext()){
mPerson = new Person();
mPerson.setQuestionId(cursor.getInt(cursor.getColumnIndex(DbConfig.QUESTION_ID)));
mPerson.setContent(cursor.getString(cursor.getColumnIndex(DbConfig.CONTENT)));
mPerson.setTime(cursor.getString(cursor.getColumnIndex(DbConfig.TIME)));
mPerson.setUserId(cursor.getInt(cursor.getColumnIndex(DbConfig.USER_ID)));
persons.add(mPerson);
}
cursor.close();
db.close();
return persons;
}
/**
* 分页查询(根据userId)
* @param userId 用户id
* @return
*/
public List<Person> findLimit(String userId){
SQLiteDatabase db = helper.getWritableDatabase();
List<Person> persons = new ArrayList<>();
/**
* table="表命",
* columns="要查询的列名",
* selection="查询 条件",
* selectionArgs="条件中用了占位符的参数",
* groupBy="数据分组",
* having="分组后的条件",
* orderBy="排序方式",
* limit="分页查询";
**/
// Cursor cursor= db.query(DbConfig.QUESTION_TABLE_NAME, null, DbConfig.USER_ID + " =?", new String[]{userId},null, null, null, pageSize + "," + page);//"5,2",第3行开始,返回5行数据
String sql= "select * from " + DbConfig.QUESTION_TABLE_NAME + " where " + DbConfig.USER_ID + " = ? ";
Cursor cursor = db.rawQuery(sql, new String[]{userId});
Person mPerson;
while(cursor.moveToNext()){
mPerson = new Person();
mPerson.setQuestionId(cursor.getInt(cursor.getColumnIndex(DbConfig.QUESTION_ID)));
mPerson.setContent(cursor.getString(cursor.getColumnIndex(DbConfig.CONTENT)));
mPerson.setTime(cursor.getString(cursor.getColumnIndex(DbConfig.TIME)));
mPerson.setUserId(cursor.getInt(cursor.getColumnIndex(DbConfig.USER_ID)));
persons.add(mPerson);
}
cursor.close();
db.close();
return persons;
}
}
数据库帮助类:
package com.example.administrator.myapplication.db;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
* Description: 自定义SQLite数据库打开帮助类
*/
public class MySQLiteOpenHelper extends SQLiteOpenHelper{
/**
* 数据库构造方法 用来定义数据库的名称 数据库的查询结果记录集 数据库的版本
* @param context
*/
public MySQLiteOpenHelper(Context context) {
super(context, DbConfig.DATABASE_NAME, null, DbConfig.DATABASE_VERSION);
}
/**
* 创建数据库,程序第一次运行时调用
* @param db 创建的数据库
*/
@Override
public void onCreate(SQLiteDatabase db) {
//创建表结构 question创建的表名 id,name,phonenum都是表中的字段,代表这个表有三列
db.execSQL(DbConfig.CREATE_QUESTION_TABLE_SQL);
db.execSQL(DbConfig.CREATE_ANSWER_TABLE_SQL);
db.execSQL(DbConfig.CREATE_ONLOOKERS_TABLE_SQL);
}
/**
* 当数据库的版本号发生变化(版本号增加)时调用。
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// db.execSQL("alter table question add group varchar(20)");
// db.execSQL("alter table answer add group varchar(20)");
// db.execSQL("alter table onlookers add group varchar(20)");
if (oldVersion != newVersion) {
db.execSQL(DbConfig.DROP_QUESTION_TABLE_SQL);
db.execSQL(DbConfig.DROP_ANSWER_TABLE_SQL);
db.execSQL(DbConfig.DROP_ONLOOKERS_TABLE_SQL);
onCreate(db);
}
}
}
bean
package com.example.administrator.myapplication.db.bean;
import java.io.Serializable;
public class Person implements Serializable {
private Integer questionId; //
protected String content;
protected Integer userId; //
protected String time; //
public void setQuestionId(Integer questionId) {
this.questionId = questionId;
}
public void setContent(String content) {
this.content = content;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public void setTime(String time) {
this.time = time;
}
public Integer getQuestionId() {
return questionId;
}
public String getContent() {
return content;
}
public Integer getUserId() {
return userId;
}
public String getTime() {
return time;
}
}
测试:
package com.example.administrator.myapplication.db;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import com.example.administrator.myapplication.R;
import com.example.administrator.myapplication.db.bean.Person;
import com.example.administrator.myapplication.db.dao.ControlDaoFly;
import java.util.List;
public class DbActivity extends AppCompatActivity implements View.OnClickListener{
private ControlDaoFly mControlDaoFly;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_db);
findViewById(R.id.button).setOnClickListener(this);
findViewById(R.id.button2).setOnClickListener(this);
findViewById(R.id.button3).setOnClickListener(this);
findViewById(R.id.button4).setOnClickListener(this);
findViewById(R.id.button5).setOnClickListener(this);
findViewById(R.id.button6).setOnClickListener(this);
findViewById(R.id.button7).setOnClickListener(this);
mControlDaoFly = new ControlDaoFly(this.getApplicationContext());
}
int dex = 0;
@Override
public void onClick(View view) {
switch (view.getId()){
case R.id.button:
Person person;
for (int i = 0; i < 8; i++) {
person = new Person();
person.setUserId(1);
person.setTime("2017-11-02 17:24:00");
person.setContent("今天是2017年1月2日,星期4");
person.setQuestionId(i);
mControlDaoFly.add(person);
}
for (int i = 0; i < 6; i++) {
person = new Person();
person.setUserId(2);
person.setTime("2017-11-02 17:24:00");
person.setContent("今天是2017年1月2日,星期4");
person.setQuestionId(i);
mControlDaoFly.add(person);
}
// Person person = new Person();
// person.setUserId(111);
// person.setTime("2017-11-02 17:24:00");
// person.setContent("今天是2017年1月2日,星期4");
// person.setQuestionId(11111111);
// mControlDaoFly.add(person);
//
// Person person1 = new Person();
// person1.setUserId(222);
// person1.setTime("2018-11-02 17:24:00");
// person1.setContent("今天是2018年1月2日,星期3");
// person1.setQuestionId(222222222);
// mControlDaoFly.add(person1);
Log.e("DbActivity",GsonUtils.toJson(mControlDaoFly.findAll()));
break;
case R.id.button2:
mControlDaoFly.delete("3");
Log.e("DbActivity",GsonUtils.toJson(mControlDaoFly.findAll()));
break;
case R.id.button3:
mControlDaoFly.modify("4","13333333338","1");
Log.e("DbActivity",GsonUtils.toJson(mControlDaoFly.findAll()));
break;
case R.id.button4:
Person p = mControlDaoFly.find("4","3");
Log.e("DbActivity111",p + "");
Log.e("DbActivity",GsonUtils.toJson(mControlDaoFly.findAll()));
break;
case R.id.button5:
Person p1 = mControlDaoFly.find("4","1");
Log.e("DbActivity111",GsonUtils.toJson(p1));
Log.e("DbActivity",GsonUtils.toJson(mControlDaoFly.findAll()));
break;
case R.id.button6:
List<Person> limit1 = mControlDaoFly.findLimit("1");
Log.e("DbActivity111",GsonUtils.toJson(limit1));
Log.e("DbActivity111","dex = " + dex);
Log.e("DbActivity",GsonUtils.toJson(mControlDaoFly.findAll()));
dex++;
break;
case R.id.button7:
// List<Person> limit2 = mControlDaoFly.findLimit("1", 6, 7);
// Log.e("DbActivity111",GsonUtils.toJson(limit2));
// Log.e("DbActivity",GsonUtils.toJson(mControlDaoFly.findAll()));
break;
}
}
}