本文介绍了Android中SQLite数据库的基础知识，包括如何利用SQLiteOpenHelper创建和管理数据库，以及数据库操作如增删改查的基本方法。此外，还探讨了ORM（对象关系映射）的概念，它是将对象模型与数据库表映射的技术，简化了数据库操作。通过示例展示了如何使用ORM技术根据JavaBean对象创建和操作数据库表。
前言:勤奋的男人和爱笑的女人一般运气都不会太差
感觉好久没写博客了,这段时间一直在摸搜自己写个数据库框架,还是写到了一点点,肯定是要等到年后才能写完,今天就写个sqlite入门以及ORM概念以及底层使用了什么技术!
android上自带了一个数据库sqlite,而不像做后台那样需要使用到什么mysql,oracle,牛逼的谷歌工程师已经帮我们内置了一个轻量级的sqlite数据库,并给我们提供了api去操作,关于更多的介绍我就不说了,用了都说好,哈哈,好像ios中数据库也是这么弄的.
今天的知识图：
现在创建一个android项目叫sqlite,来演示数据库基本的操作：
数据库怎么产生的
要想在android创建一个数据库比如xxx.db,在android给我们提供了一个类SQLiteOpenHelper,这个类就是帮助我们来创建一个数据库文件的,如下：
package com.sqlite; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; /** * Created by admin on 2017/1/22. */ public class DBHelper extends SQLiteOpenHelper { public DBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) { super(context, name, factory, version); } /** * @param context 上下文 * @param name 数据库名称 * @param version 数据库版本号 */ public DBHelper(Context context, String name, int version){ this(context, name, null, version); } @Override public void onCreate(SQLiteDatabase db) { } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } }使用：
package com.sqlite; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; public class MainActivity extends AppCompatActivity { @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); DBHelper dbHelper = new DBHelper(this,"user.db",1); } }我们运行后去data/data/包名/数据库文件 去这个目录下查找你刚才创建的数据库.在studio中怎么去查找创建的数据库文件名：
点击Android Device Monitor后,
查找创建的数据库文件名：
你会发现数据库并没有创建成功,哦,哪可能是我没有在Oncreate()方法中创建表了,于是我在onCreate()把创建表的数据语句写好了,
@Override public void onCreate(SQLiteDatabase db) { db.execSQL("CREATE TABLE IF NOT EXISTS user(personid integer primary key autoincrement, name varchar(20), age INTEGER)"); }于是我又运行下,发现数据库文件还是没创建成功,后来又插入数据到数据库中,发现数据库终于创建成功了!
/** * @param values */ public synchronized void insert(ContentValues values){ //获取SQLiteDatabase实例 SQLiteDatabase db = getWritableDatabase(); //插入数据库中 db.insert("user", null, values); db.close();//记得一定要关闭 }上层调用：
package com.sqlite; import android.content.ContentValues; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; public class MainActivity extends AppCompatActivity { @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); DBHelper dbHelper = new DBHelper(this,"user.db",2); ContentValues contentValues = new ContentValues(); contentValues.put("name","zhouguizhi"); contentValues.put("age",18); dbHelper.insert(contentValues); } }在DDMS中去查看数据库文件是否成功!
总结：
不是写一个类去继承SQLiteOpenHelper或者在其onCreate()方法中创建表就代表数据库文件创建成功了,必须要对数据库进行增删改的操作,数据库文件才能创建成功.
现在看下SQLiteOpenHelper帮助类有哪些方法：
方法不多,把主要的方法介绍下：
public String getDatabaseName() 获取数据库的名字
public abstract void onCreate(SQLiteDatabase db)创建数据库时调用,如果数据库已经创建了则不在调用,这点和Activity的OnCreate()方法类似,一般用于创建表的逻辑在onCreate()方法
public abstract void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)数据库升级的时候调用,
参数说明：
db:底层操作数据库的具体类
oldVersion：数据库的久版本
newVersion：数据库的新版本 (升级的根据就是和上面的数据进行对比来判断数据库是否升级)
下面2个重要的方法：
public SQLiteDatabase getReadableDatabase()
public SQLiteDatabase getWritableDatabase()
进入这个2个方法的源码：
public SQLiteDatabase getWritableDatabase() { synchronized (this) { return getDatabaseLocked(true); } }
public SQLiteDatabase getReadableDatabase() { synchronized (this) { return getDatabaseLocked(false); } }你会发现都是调用了getDatabaseLocked()方法只是传递的boolean值不一样,看下getDatabaseLocked()方法的源码如下
private SQLiteDatabase getDatabaseLocked(boolean writable) { if (mDatabase != null) { //判断SQLiteDatabase实例对象是否为null if (!mDatabase.isOpen()) {//判断对象是否打开 // Darn! The user closed the database by calling mDatabase.close(). mDatabase = null;//没有被打开这时候SQLiteDatabase就赋值为null了 } else if (!writable || !mDatabase.isReadOnly()) {//writable是上面方法的形参表示是否可写 // The database is already open for business. return mDatabase; } } if (mIsInitializing) { throw new IllegalStateException("getDatabase called recursively"); } SQLiteDatabase db = mDatabase; try { mIsInitializing = true;//走到这里表示已经初始化了 if (db != null) { if (writable && db.isReadOnly()) { db.reopenReadWrite(); } } else if (mName == null) { db = SQLiteDatabase.create(null); } else { try { if (DEBUG_STRICT_READONLY && !writable) {// 可写 final String path = mContext.getDatabasePath(mName).getPath();获取数据库的路径 db = SQLiteDatabase.openDatabase(path, mFactory, SQLiteDatabase.OPEN_READONLY, mErrorHandler);打开数据库 } else { //可读
//在已创建的数据库路径下进行数据库读操作 db = mContext.openOrCreateDatabase(mName, mEnableWriteAheadLogging ? Context.MODE_ENABLE_WRITE_AHEAD_LOGGING : 0, mFactory, mErrorHandler); } } catch (SQLiteException ex) {//当在可读时发生错误还是会在data/data/文件下创建数据库对象 if (writable) { throw ex; } Log.e(TAG, "Couldn't open " + mName + " for writing (will try read-only):", ex); final String path = mContext.getDatabasePath(mName).getPath(); db = SQLiteDatabase.openDatabase(path, mFactory, SQLiteDatabase.OPEN_READONLY, mErrorHandler); } } onConfigure(db); final int version = db.getVersion(); if (version != mNewVersion) {//数据库版本号对比 if (db.isReadOnly()) { throw new SQLiteException("Can't upgrade read-only database from version " + db.getVersion() + " to " + mNewVersion + ": " + mName); } db.beginTransaction();//开启事务 try { if (version == 0) { onCreate(db); } else { if (version > mNewVersion) { onDowngrade(db, version, mNewVersion); } else { onUpgrade(db, version, mNewVersion); } } db.setVersion(mNewVersion); db.setTransactionSuccessful(); } finally { db.endTransaction();//关闭事务 } } onOpen(db); if (db.isReadOnly()) { Log.w(TAG, "Opened " + mName + " in read-only mode"); } mDatabase = db; return db; } finally { mIsInitializing = false; if (db != null && db != mDatabase) { db.close(); } } }
public SQLiteDatabase getWritableDatabase() 以只写的方式返回或者创建一个操作数据库类SQLiteDatabase
public SQLiteDatabase getReadableDatabase() 以只读的方式返回或者创建一个操作数据库类SQLiteDatabase
同时通过上面的代码我们也观察到sqlite数据库也是支持事务的
如何改变数据库创建存放的路径
我们知道我们继承SQLiteOpenHelper类去创建数据库时是默认放在data/data/packageName/dbName.db，这个路径创建在SQLiteOpenHelper源码中就可以找到.
但是有的可能要求你数据库不能存放在data/data/packageName/dbName.db这个路径下,因为只要app一旦被用户卸载了,数据库也就没了,所以我们可以把数据库放在sd卡中,现在我们就写一个程序把创建数据库文件放在sd卡中
package com.sdtodb; import android.content.ContentValues; import android.database.sqlite.SQLiteDatabase; import android.os.Environment; import android.util.Log; /** * Created by admin on 2017/1/23. */ public class DbFactory { private String dbPath; private SQLiteDatabase db; private DbFactory(){ init(); } private void init() { dbPath= Environment.getExternalStorageDirectory().getAbsolutePath()+"/zhouguizhi.db"; openDatabase(); } private void openDatabase() { Log.e("DbFactory","dbPath="+dbPath); this.db= SQLiteDatabase.openOrCreateDatabase(dbPath,null); createTable(); } /** * 创建表 */ private void createTable() { db.execSQL("create table if not exists guizhi(username varchar(20),password varchar(10))"); } private static class Factory{ private static final DbFactory INSTANCE = new DbFactory(); } public static DbFactory getInstance(){ return Factory.INSTANCE; } public void insert(){ ContentValues values = new ContentValues(); values.put("username","zhouguizhi"); values.put("password","123456"); db.insert("guizhi",null,values); db.close(); } }这个类只要是创建库和表
上层使用：
package com.sdtodb; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; public class MainActivity extends AppCompatActivity { @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); DbFactory.getInstance().insert();//插入一条数据 } }如图：
通过Sqlite工具软件打开看看：
我们看到数据库已经在sd卡中创建成功了,现在就是把你app卸载了,他也还会sd中!
我们做android对数据库的操作基本是增删改查操作了,这些操作都封装了SQLiteDatabase中,应该来讲就是查比较麻烦,其他三种操作都好说,现在对SQLiteDatabase类一些常用的方法做简单的介绍
private static boolean isMainThread()判断当前操作数据库是否是在主线程中
public void beginTransaction()开启事务
public void endTransaction()关闭事务
public static SQLiteDatabase openOrCreateDatabase(String path, CursorFactory factory)打开或者没有就创建一个SQLiteDatabase实例对象,
参数说明;
path是创建数据库存放的路径
factory:游标工厂 几乎没用过
public static boolean deleteDatabase(File file)这是删除数据库文件在api16才能用,后期Google添加的方法,这个太霸气,直接把库删除掉
public int getVersion()获取数据库版本号
public boolean isOpen()的当前数据库是否已打开
public final String getPath()获取数据库文件存放的路径
public void setLocale(Locale locale)设置数据库语言
下面的方法就是关于数据库方面的增删改查方法了,有的方法有很多重载形式.
public void execSQL(String sql) 这是执行一条sql语句,比如上面我们创建表
删除方法：
public void deleteUser(String userName){ String whereClause = "username=?";//删除的条件 String[] whereArgs = {userName};//删除的条件参数 db.delete("guizhi",whereClause,whereArgs);//执行删除 }这是带条件的是删除数据库某一条记录 通过sql语句删除一条记录：
public void deleteUserBySql(){ String sql = "delete from guizhi where username='laowang7'";//删除操作的SQL语句 db.execSQL(sql);//执行删除操作 }如果什么条件也不带的话,就是删除数据库中所有的数据了
public void deleteAll(){ db.delete("guizhi",null,null);//执行删除 }这是删除表中所有的数据,但是表还存在,sql语句怎么写就不在这说了! 删除整个表,一般这个需求就是直接把这个表不使用了,
/** * 删除表 */ public void deleteTable() { String sql = "drop table guizhi"; db.execSQL(sql);//执行删除操作 }这是删除整张表！不要轻易做这个操作,否则你就要重新找工作了.
上面把数据库的删操作算是写完了!下面接着写数据库更新吧,如果不写条件的话就是更新表某个字段所有的值了,如果这样的话,你也快被老板开除了,
/** * 不带条件的就是更新表中所有数据 */ public void updateAll() { ContentValues values = new ContentValues(); values.put("password","10086");//修改的值 db.update("guizhi",values,null,null); }
这就是把表中的password字段全部改为了10086值了,o my god
上面把更新的方法讲了下,数据库增的操作其实已经有了,
public void insert(String username){ for(int i=0;i<20;i++){ ContentValues values = new ContentValues(); values.put("username",username+i); values.put("password","123456"); db.insert("guizhi",null,values); } }
最麻烦的就是属于查了,能把查询弄懂了,就ok,其他几乎没啥事!
我们看下SQLiteDatabase类给我们提供了查询语句：
public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) {这参数也是变态的多：
参数说明：
table:表名
colums:列名称数组
selection:查询条件子语句,相当于where
selectionArgs:上面条件子语句是根据那些字段的数组
groupBy:用于分组
having:分组条件
orderBy:排序
limit:分页查询从哪开始查询几条数据
返回的是一个Cursor类,数据库查询的结果都是封装在这个类中,所以我们要去获取查询的结果就是要对Cursor类进行操作了,
Cursor发现它是一个接口,我把源码中注释给删除了,
package android.database; import android.content.ContentResolver; import android.net.Uri; import android.os.Bundle; import java.io.Closeable; public interface Cursor extends Closeable { /* * Values returned by {@link #getType(int)}. * These should be consistent with the corresponding types defined in CursorWindow.h */ /** Value returned by {@link #getType(int)} if the specified column is null */ static final int FIELD_TYPE_NULL = 0; /** Value returned by {@link #getType(int)} if the specified column type is integer */ static final int FIELD_TYPE_INTEGER = 1; /** Value returned by {@link #getType(int)} if the specified column type is float */ static final int FIELD_TYPE_FLOAT = 2; /** Value returned by {@link #getType(int)} if the specified column type is string */ static final int FIELD_TYPE_STRING = 3; /** Value returned by {@link #getType(int)} if the specified column type is blob */ static final int FIELD_TYPE_BLOB = 4; int getCount(); int getPosition(); boolean move(int offset); boolean moveToPosition(int position); boolean moveToFirst(); boolean moveToLast(); boolean moveToNext(); boolean moveToPrevious(); boolean isFirst(); boolean isLast(); boolean isBeforeFirst(); boolean isAfterLast(); int getColumnIndex(String columnName); int getColumnIndexOrThrow(String columnName) throws IllegalArgumentException; String getColumnName(int columnIndex); String[] getColumnNames(); int getColumnCount(); byte[] getBlob(int columnIndex); String getString(int columnIndex); void copyStringToBuffer(int columnIndex, CharArrayBuffer buffer); short getShort(int columnIndex); int getInt(int columnIndex); long getLong(int columnIndex); float getFloat(int columnIndex); double getDouble(int columnIndex); int getType(int columnIndex); boolean isNull(int columnIndex); void deactivate(); @Deprecated boolean requery(); void close(); boolean isClosed(); void registerContentObserver(ContentObserver observer); void unregisterContentObserver(ContentObserver observer); void registerDataSetObserver(DataSetObserver observer); void unregisterDataSetObserver(DataSetObserver observer); void setNotificationUri(ContentResolver cr, Uri uri); Uri getNotificationUri(); boolean getWantsAllOnMoveCalls(); void setExtras(Bundle extras); Bundle getExtras(); Bundle respond(Bundle extras); }
现在单独创建一个表来演示查询的功能,因为查询query()形参比较多,而且查询也是最麻烦的一个。
package com.sdtodb; import android.content.ContentValues; import android.database.sqlite.SQLiteDatabase; import android.os.Environment; import android.util.Log; import java.io.File; import java.util.Random; /** * Created by admin on 2017/1/23. */ public class DbFactory { private String dbPath; private SQLiteDatabase db; private String[] provinces = {"北京市","天津市","河北省","山西省","辽宁省","吉林省","上海市","江苏省","浙江省","安徽省","福建省","江西省" ,"山东省","河南省","湖北省","湖南省","广东省","海南省","四川省","贵州省","云南省","陕西省","甘肃省"}; private DbFactory(){ init(); } private void init() { dbPath= Environment.getExternalStorageDirectory().getAbsolutePath()+"/person.db"; openDatabase(); } private void openDatabase() { this.db= SQLiteDatabase.openOrCreateDatabase(dbPath,null); createTable(); } /** * 创建表 */ private void createTable() { db.execSQL("create table if not exists person(name varchar(20),age Integer,province varchar,job_number Integer,salary Double )"); } private static class Factory{ private static final DbFactory INSTANCE = new DbFactory(); } public static DbFactory getInstance(){ return Factory.INSTANCE; } /** * 插入数据 * @param username */ public void insert(String username){ Random random = new Random(); for(int i=0;i<60;i++){ ContentValues values = new ContentValues(); values.put("name",username+i); values.put("age",18+i); values.put("province",provinces[random.nextInt(provinces.length)]); values.put("job_number",i); values.put("salary",10000+5*i); db.insert("person",null,values); } } }Person.java实体bean
package com.sdtodb; /** * Created by admin on 2017/1/25. */ public class Person { private String name; private int age; private String province; private int job_number;//工号 private double salary; public Person(){} public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public String getProvince() { return province; } public void setProvince(String province) { this.province = province; } public int getJob_number() { return job_number; } public void setJob_number(int job_number) { this.job_number = job_number; } public double getSalary() { return salary; } public void setSalary(double salary) { this.salary = salary; } }上层调用：
package com.sdtodb; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; public class MainActivity extends AppCompatActivity { @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); insert(); } private void insert() { DbFactory.getInstance().insert("laowang"); } }数据库就创建成功了并插入了60条数据,如下：
查询数据库中所有的数据：
/** * 查询表中所有数据 */ public List<Person> selectAll() { List<Person> persons = new ArrayList<>(); String sql = "select * from person"; Cursor cursor = db.rawQuery(sql,null); Log.e(TAG,"cursor"); if(cursor!=null){ while (cursor.moveToNext()){ String name = cursor.getString(cursor.getColumnIndex("name")); int age = cursor.getInt(cursor.getColumnIndex("age")); String province = cursor.getString(cursor.getColumnIndex("province")); int job_number = cursor.getInt(cursor.getColumnIndex("job_number")); double salary = cursor.getDouble(cursor.getColumnIndex("salary")); Person person = new Person(name,age,province,job_number,salary); persons.add(person); } } db.close(); cursor.close(); return persons; }log:
如果是使用query()方法查询的话是这样的：
Cursor cursor = db.query("person",null,null,null,null,null,null);如果只是查询某一个或者几个列的字段如下：
public List<Person> selectAll() { List<Person> persons = new ArrayList<>(); String sql = "select name,age from person"; Cursor cursor = db.rawQuery(sql,null); Log.e(TAG,"cursor"); if(cursor!=null){ while (cursor.moveToNext()){ String name = cursor.getString(cursor.getColumnIndex("name")); int age = cursor.getInt(cursor.getColumnIndex("age")); // String province = cursor.getString(cursor.getColumnIndex("province")); // int job_number = cursor.getInt(cursor.getColumnIndex("job_number")); // double salary = cursor.getDouble(cursor.getColumnIndex("salary")); Person person = new Person(name,age); persons.add(person); } } db.close(); cursor.close(); return persons; }如果是使用query()方法查询某几个列的字段如下：
String[] columns = {"name","age"};
Cursor cursor = db.query("person",columns,null,null,null,null,null);
如果你只查询name和age字段的话,那么查询的结果你去获取其他非name,age字段的值会报错！
根据某个字段去查询某条记录这就是带上条件了,where是带条件查询的子语句
/** * 根据age查询某条记录 */ public List<Person> selectPersonByAge(int argsAge) { List<Person> persons = new ArrayList<>(); String sql = "select * from person where age=?"; Cursor cursor = db.rawQuery(sql,new String[]{argsAge+""}); if(cursor!=null){ while (cursor.moveToNext()){ String name = cursor.getString(cursor.getColumnIndex("name")); int age = cursor.getInt(cursor.getColumnIndex("age")); String province = cursor.getString(cursor.getColumnIndex("province")); int job_number = cursor.getInt(cursor.getColumnIndex("job_number")); double salary = cursor.getDouble(cursor.getColumnIndex("salary")); Person person = new Person(name,age,province,job_number,salary); person.setName(name); persons.add(person); } } db.close(); cursor.close(); return persons; }现在根据省份去查找所有这个省的用户：
log:
01-25 07:09:00.586 18694-18694/? E/MainActivity: Person{name='laowang19', age=37, province='浙江省', job_number=19, salary=10095.0}
01-25 07:09:00.586 18694-18694/? E/MainActivity: Person{name='laowang22', age=40, province='浙江省', job_number=22, salary=10110.0}
01-25 07:09:00.586 18694-18694/? E/MainActivity: Person{name='laowang33', age=51, province='浙江省', job_number=33, salary=10165.0}
01-25 07:09:00.586 18694-18694/? E/MainActivity: Person{name='laowang47', age=65, province='浙江省', job_number=47, salary=10235.0}
01-25 07:09:00.586 18694-18694/? E/MainActivity: Person{name='laowang50', age=68, province='浙江省', job_number=50, salary=10250.0}
01-25 07:09:00.586 18694-18694/? E/MainActivity: Person{name='laowang56', age=74, province='浙江省', job_number=56, salary=10280.0}
01-25 07:09:00.586 18694-18694/? E/MainActivity: Person{name='laowang4', age=22, province='浙江省', job_number=4, salary=10020.0}
01-25 07:09:00.586 18694-18694/? E/MainActivity: Person{name='laowang35', age=53, province='浙江省', job_number=35, salary=10175.0}
01-25 07:09:00.586 18694-18694/? E/MainActivity: Person{name='laowang44', age=62, province='浙江省', job_number=44, salary=10220.0}
01-25 07:09:00.586 18694-18694/? E/MainActivity: Person{name='laowang53', age=71, province='浙江省', job_number=53, salary=10265.0}
如果是使用sql查询就是这样
String sql = "select * from person where province=?"; Cursor cursor = db.rawQuery(sql,new String[]{argProvince});
现在query()方法后三个形参还没用到,现在就用下：
现在弄个分页查询,从10开始获取10条记录
/** * 分页查询 * @return */ public List<Person> selectPersonLimit(int offset,int num) { List<Person> persons = new ArrayList<>(); String sql = "select * from person limit ?,?"; String[] selectionArgs ={offset+"",num+""}; Cursor cursor = db.rawQuery(sql,selectionArgs); if(cursor!=null){ while (cursor.moveToNext()){ String name = cursor.getString(cursor.getColumnIndex("name")); int age = cursor.getInt(cursor.getColumnIndex("age")); String province = cursor.getString(cursor.getColumnIndex("province")); int job_number = cursor.getInt(cursor.getColumnIndex("job_number")); double salary = cursor.getDouble(cursor.getColumnIndex("salary")); Person person = new Person(name,age,province,job_number,salary); person.setName(name); persons.add(person); } } db.close(); cursor.close(); return persons; }但是真正从offset+1的位置开始查询,查询num条数据
如果是使用query()方法分页查询：
String limit = offset+","+num; Cursor cursor = db.query("person",null,null,null,null,null,null,limit);
如果按照年龄大小进行排序;
/** * 排序 * @return */ public List<Person> selectPersonByOrder() { List<Person> persons = new ArrayList<>(); String orderBy = "age asc"; Cursor cursor = db.query("person",null,null,null,null,null,orderBy,null); if(cursor!=null){ while (cursor.moveToNext()){ String name = cursor.getString(cursor.getColumnIndex("name")); int age = cursor.getInt(cursor.getColumnIndex("age")); String province = cursor.getString(cursor.getColumnIndex("province")); int job_number = cursor.getInt(cursor.getColumnIndex("job_number")); double salary = cursor.getDouble(cursor.getColumnIndex("salary")); Person person = new Person(name,age,province,job_number,salary); person.setName(name); persons.add(person); } } db.close(); cursor.close(); return persons; }desc:降序从大到小
asc:升序从小到大
现在query还剩下二个参数没讲就是分组groupBy和分组条件having.
/** * 分组 * @return */ public List<Person> selectPersonByGroup() { List<Person> persons = new ArrayList<>(); String groupBy = "age"; String having = "age>40"; Cursor cursor = db.query("person",null,null,null,groupBy,having,null,null); if(cursor!=null){ while (cursor.moveToNext()){ String name = cursor.getString(cursor.getColumnIndex("name")); int age = cursor.getInt(cursor.getColumnIndex("age")); String province = cursor.getString(cursor.getColumnIndex("province")); int job_number = cursor.getInt(cursor.getColumnIndex("job_number")); double salary = cursor.getDouble(cursor.getColumnIndex("salary")); Person person = new Person(name,age,province,job_number,salary); person.setName(name); persons.add(person); } } db.close(); cursor.close(); return persons; }
这就是简单介绍了分组了,根据age字段去分组了!
总算把query()方法中几个参数说明了并写了几个例子去玩了下,相信应该大致都了解了每个参数什么意思了!我们做android基本上能熟练使用就行,熟练使用意味着你要多写点东西才能熟练！
现在把Cursor这个类也讲下,今天是年前最后一天了,还有2个小时下班,希望能把这篇博客写完！
void close();关闭,这个方法非常重要,不然可能会引起内存泄露
boolean isClosed();判断cursor是否关闭了
getCount();cursor结果集有多少条记录
moveToNext();把结果集移到下一个记录上,
getColumnCount():这是获取创建表的总列表,
String getColumnName(int columnIndex)通过columnIndex参数获取列名：
int count = cursor.getColumnCount(); Log.e(TAG,"count="+count); for(int i=0;i<count;i++){ String columname = cursor.getColumnName(i); Log.e(TAG,"columname="+columname); }log:
01-25 08:34:39.880 3170-3170/? E/DbFactory: columname=name
01-25 08:34:39.880 3170-3170/? E/DbFactory: columname=age
01-25 08:34:39.880 3170-3170/? E/DbFactory: columname=province
01-25 08:34:39.880 3170-3170/? E/DbFactory: columname=job_number
01-25 08:34:39.880 3170-3170/? E/DbFactory: columname=salary
这个列名顺序和你创建表的顺序是一样的
String[] getColumnNames()获取所有的列名 返回的是一个String数组
int getColumnIndex(String columnName);根据columnName列名获取它所在的位置
int columnName = cursor.getColumnIndex("name"); int columnAge= cursor.getColumnIndex("age"); int columnProvince = cursor.getColumnIndex("province"); int columnJob_number = cursor.getColumnIndex("job_number"); int columnSalary = cursor.getColumnIndex("salary"); Log.e(TAG,"columnName="+columnName); Log.e(TAG,"columnAge="+columnAge); Log.e(TAG,"columnProvince="+columnProvince); Log.e(TAG,"columnJob_number="+columnJob_number); Log.e(TAG,"columnSalary="+columnSalary);log:
01-25 08:45:24.992 5471-5471/? E/DbFactory: columnName=0
01-25 08:45:24.992 5471-5471/? E/DbFactory: columnAge=1
01-25 08:45:24.992 5471-5471/? E/DbFactory: columnProvince=2
01-25 08:45:24.992 5471-5471/? E/DbFactory: columnJob_number=3
01-25 08:45:24.992 5471-5471/? E/DbFactory: columnSalary=4
这和我们创建表时候的顺序是一样的：
db.execSQL("create table if not exists person(name varchar(20),age Integer,province varchar,job_number Integer,salary Double )");
String getString(int columnIndex);获取列名对应的值
这个通过getColumnIndex()方法返回一个int值然后通过这个去调用getString()值获取这个字段的值
while (cursor.moveToNext()){ int columnName = cursor.getColumnIndex("name"); int columnAge= cursor.getColumnIndex("age"); int columnProvince = cursor.getColumnIndex("province"); int columnJob_number = cursor.getColumnIndex("job_number"); int columnSalary = cursor.getColumnIndex("salary"); String name = cursor.getString(columnName); int age = cursor.getInt(columnAge); String province = cursor.getString(columnProvince); int job_number = cursor.getInt(columnJob_number); double salary = cursor.getDouble(columnSalary); Log.e(TAG,"name="+name+"-"+"age="+age+"-"+"province="+province+"-"+"job_number="+job_number+"-"+"salary="+salary); }log:
01-25 08:51:15.334 6547-6547/? E/DbFactory: name=laowang0-age=18-province=海南省-job_number=0-salary=10000.0
01-25 08:51:15.334 6547-6547/? E/DbFactory: name=laowang1-age=19-province=山东省-job_number=1-salary=10005.0
01-25 08:51:15.334 6547-6547/? E/DbFactory: name=laowang2-age=20-province=河北省-job_number=2-salary=10010.0
01-25 08:51:15.334 6547-6547/? E/DbFactory: name=laowang3-age=21-province=辽宁省-job_number=3-salary=10015.0
01-25 08:51:15.334 6547-6547/? E/DbFactory: name=laowang4-age=22-province=海南省-job_number=4-salary=10020.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang5-age=23-province=湖南省-job_number=5-salary=10025.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang6-age=24-province=海南省-job_number=6-salary=10030.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang7-age=25-province=安徽省-job_number=7-salary=10035.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang8-age=26-province=河南省-job_number=8-salary=10040.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang9-age=27-province=福建省-job_number=9-salary=10045.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang10-age=28-province=北京市-job_number=10-salary=10050.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang11-age=29-province=陕西省-job_number=11-salary=10055.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang12-age=30-province=山西省-job_number=12-salary=10060.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang13-age=31-province=安徽省-job_number=13-salary=10065.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang14-age=32-province=江苏省-job_number=14-salary=10070.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang15-age=33-province=浙江省-job_number=15-salary=10075.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang16-age=34-province=吉林省-job_number=16-salary=10080.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang17-age=35-province=江西省-job_number=17-salary=10085.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang18-age=36-province=吉林省-job_number=18-salary=10090.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang19-age=37-province=陕西省-job_number=19-salary=10095.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang20-age=38-province=陕西省-job_number=20-salary=10100.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang21-age=39-province=安徽省-job_number=21-salary=10105.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang22-age=40-province=云南省-job_number=22-salary=10110.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang23-age=41-province=海南省-job_number=23-salary=10115.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang24-age=42-province=甘肃省-job_number=24-salary=10120.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang25-age=43-province=四川省-job_number=25-salary=10125.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang26-age=44-province=天津市-job_number=26-salary=10130.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang27-age=45-province=山东省-job_number=27-salary=10135.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang28-age=46-province=海南省-job_number=28-salary=10140.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang29-age=47-province=福建省-job_number=29-salary=10145.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang30-age=48-province=北京市-job_number=30-salary=10150.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang31-age=49-province=山西省-job_number=31-salary=10155.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang32-age=50-province=湖北省-job_number=32-salary=10160.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang33-age=51-province=海南省-job_number=33-salary=10165.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang34-age=52-province=江苏省-job_number=34-salary=10170.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang35-age=53-province=贵州省-job_number=35-salary=10175.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang36-age=54-province=甘肃省-job_number=36-salary=10180.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang37-age=55-province=甘肃省-job_number=37-salary=10185.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang38-age=56-province=辽宁省-job_number=38-salary=10190.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang39-age=57-province=江苏省-job_number=39-salary=10195.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang40-age=58-province=山西省-job_number=40-salary=10200.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang41-age=59-province=河南省-job_number=41-salary=10205.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang42-age=60-province=山东省-job_number=42-salary=10210.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang43-age=61-province=山西省-job_number=43-salary=10215.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang44-age=62-province=浙江省-job_number=44-salary=10220.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang45-age=63-province=北京市-job_number=45-salary=10225.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang46-age=64-province=云南省-job_number=46-salary=10230.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang47-age=65-province=湖北省-job_number=47-salary=10235.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang48-age=66-province=吉林省-job_number=48-salary=10240.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang49-age=67-province=河北省-job_number=49-salary=10245.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang50-age=68-province=四川省-job_number=50-salary=10250.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang51-age=69-province=云南省-job_number=51-salary=10255.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang52-age=70-province=甘肃省-job_number=52-salary=10260.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang53-age=71-province=江西省-job_number=53-salary=10265.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang54-age=72-province=海南省-job_number=54-salary=10270.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang55-age=73-province=北京市-job_number=55-salary=10275.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang56-age=74-province=江苏省-job_number=56-salary=10280.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang57-age=75-province=陕西省-job_number=57-salary=10285.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang58-age=76-province=湖南省-job_number=58-salary=10290.0
01-25 08:51:15.335 6547-6547/? E/DbFactory: name=laowang59-age=77-province=江西省-job_number=59-salary=10295.0
ok，当然如果你知道每个字段对应的下标数是多少也可以直接写,比如：
String name = cursor.getString(0); int age = cursor.getInt(1); String province = cursor.getString(2); int job_number = cursor.getInt(3); double salary = cursor.getDouble(4);如图：
今天把最后2个问题解决也就是orm概念,以及底层用到什么技术
orm概念：它是ORM(Object/Relational Mapping)中文翻译为对象/关系型数据映射,它也可以理解为一种数据持久化技术,其主要是把对象模型,例如JavaBean对象和关系型数据库的表建立对应关系,并且提供了一个通过JavaBean对象去操作数据库表的机制
比如我们在调用层这么使用：
UserDao dao = new UserDao(); User user = new User(); user.setName("zhouguizhi"); dao.insert(user);上层不直接与sql语句打交道,创建表和数据的增删改查都封装在底层,上层就知道调用即可,哪它是怎么做到的呢?也就是会使用到的技术点:'
就比如这创建表的语句：
create table if not exists person(name varchar(20),age Integer,province varchar,job_number Integer,salary Double )
你会发现person表中有这些字段name,age,province,job_number,salary,这个创建表的语句是我们写死的,哪如何根据一个对象去创建一个表呢?
package com.sdtodb; /** * Created by admin on 2017/1/25. */ public class Person { private String name; private int age; private String province; private int job_number;//工号 private double salary; public Person(){} public Person(String name, int age) { this.name = name; this.age = age; } public Person(String name, int age, String province, int job_number, double salary) { this.name = name; this.age = age; this.province = province; this.job_number = job_number; this.salary = salary; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public String getProvince() { return province; } public void setProvince(String province) { this.province = province; } public int getJob_number() { return job_number; } public void setJob_number(int job_number) { this.job_number = job_number; } public double getSalary() { return salary; } public void setSalary(double salary) { this.salary = salary; } @Override public String toString() { return "Person{" + "name='" + name + '\'' + ", age=" + age + ", province='" + province + '\'' + ", job_number=" + job_number + ", salary=" + salary + '}'; } }比如上面是一个Person类,如何动态的去创建一个person表,而且还有可能表中的字段和对象中的成员变量起的名字不一样,要用到一个知识点就是注解:然后通过反射去读取这个类上的注解,表名以及字段名都可以得到;
package com.zgzdb.annotation; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; /** * Created by Administrator on 2017/1/9 0009. */ @Target(ElementType.TYPE) @Retention(RetentionPolicy.RUNTIME) public @interface DbTable { String value(); }这是定义了一个注解DbTable
package com.zgzdb; import com.zgzdb.annotation.DbFiled; import com.zgzdb.annotation.DbTable; /** * Created by Administrator on 2017/1/9 0009. */ @DbTable("tb_user") public class User { public User(String name, String password) { this.name = name; this.password = password; } public User( ) { } @DbFiled("name") public String name; @DbFiled("password") public String password; }发现User类使用了DbTable这个注解,我们在反射的时候就知道获取注解上面的内容,比如tb_user就是表名,那么name这个成员变量上的注解name(这个name就是我们创建表时候的字段)获取后就可以动态的创建表了,至于增删改查也要封装了,
这是使用到的技术的一部分!也是很关键的实现orm框架的部分!
扫一扫
274
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
