SqLiite的使用
来源：互联网 发布：暗黑破坏神3mac版 编辑：程序博客网 时间：2024/06/03 17:33
// 配置
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; } }}阅读全文
0 0
- SqLiite的使用
- Android中SQLiite的replace、update、insert以及表级和字段级冲突解决
- sqliite接口，很有用
- SQLiite基础语法示例及说明
- 安卓自带SQLIite创建表莫名出错解决
- GUID使用的使用
- ${}的使用
- 的使用
- @[],@()的使用
- ->的使用
- +、-、*、/、%的使用
- &&的使用
- %:的使用
- Makefile中使用$$的使用
- Makefile中使用$$的使用
- Red5的使用--使用日志
- prefernce的使用具体使用
- 【工具使用】git 的使用
- 计算机体系结构要点
- 使用Filters过滤
- linux硬件设备操作函数（open举例）
- 控制流分析之循环
- Spring初始化：org.springframework.web.context.ContextLoaderListener的作用
- SqLiite的使用
- KINECT骨骼关节点对照表
- Android，BLE，OAD，空中升级
- (12)intellij Cannot create package/ Java class in Intellij IDEA with Maven template
- js操作元素样式
- Git本地仓库push至GitHub远程仓库每次输入账户密码问题解决
- Json解析2--Gson解析详解
- Linux多线程编程讲解之系列七
- Android App首次安装完成后在安装界面直接打开应用，再按home键返回桌面，从桌面图标重新进入app，返回不到之前的界面问题
