SQLite数据库sql语句查询以及API查询

1，xml文件
不知道为啥放不进去 就看代码部分吧

2，mainActivity

public class MainActivity extends AppCompatActivity {
private MySQLiteHelper helper;
/**
 * 数据库的查询操作
 * @param savedInstanceState
 */
@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    /**
     * 获取帮助类对象
     */
    helper = DbManager.getIntance(this);
}

public void creatDb(View view){
    SQLiteDatabase  db = helper.getWritableDatabase();
    for (int i = 1;i<=30;i++){
        String sql = "insert into "+ Constant.TABLENAME+" values("+i+",'zhangshan"+i+"',20+"+i+")";
        db.execSQL(sql);
    }
    db.close();
}
public void onClick(View view){
    switch (view.getId()){
        case R.id.SQLquery:
            SQLiteDatabase db = helper.getWritableDatabase();
            String sql = "select * from "+Constant.TABLENAME;

            Cursor  cursor = DbManager.selectDataBySQL(db,sql,null);

            List<Person> list = DbManager.cursorToList(cursor);

            for (Person p :list){
                Log.i("tag",p.toString());
            }
            db.close();

            break;
        case R.id.APIquery:
            db = helper.getWritableDatabase();
            /**
             * query(String table, String[] columns, String selection,
             * String[] selectionArgs, String groupBy, String having, String orderBy)
             * String table 查询的表的名称
             * String[] columns 表示查询表中的字段名称  如果是null则表示查询所有
             * String selection 查询条件
             * String[] selectionArgs 表示查询条件占位符的取值
             * String groupBy 表示分组条件 group by 子句
             * String having 表示筛选条件 having 子句
             * String orderBy 表示排序 order by子句  desc 降序排序 asc 升序（默认）
             */
            cursor = db.query(Constant.TABLENAME,null,Constant._ID+" >1",null,null,
                    null,Constant._ID+" desc");
            list = DbManager.cursorToList(cursor);
            for (Person p :list){
                Log.i("tag",p.toString());
            }
            db.close();
            break;
    }
}

}

3，DbManager.class

/**
 * 单例模式保证在内存中只有一个对象，避免了多次创建造成内存的紧张
 * 通过单例模式创建工作类对象
 * @param context
 * @return
 */
public class DbManager {
private static MySQLiteHelper helper;
public static MySQLiteHelper getIntance(Context context){
    if (helper==null){
        helper = new MySQLiteHelper(context);
    }
        return helper;

}

/**
 * 封装的方法
 * 根据sql语句返回一个cursor对象
 * @param db
 * @param sql
 * @param selectionArgs 查询条件的占位符
 */

public static Cursor selectDataBySQL (SQLiteDatabase db,String sql, String[] selectionArgs){
    Cursor cursor =null;
    if (db!=null){
       cursor =  db.rawQuery(sql,selectionArgs);

    }
    return cursor;
}
/**
 * Curson对象转化成List集合
 * @param cursor 游标对象
 * moveToNext()的返回值是一个布尔类型的如果返回的是true则表示下一条数据仍然存在
 * @return
 */
public static List<Person> cursorToList(Cursor cursor){
    List<Person>  list = new ArrayList<>();
    while (cursor.moveToNext()){

        /**
         * getColumnIndex(Constant._ID)根据参数中指定字段名称获取字段下标
         */
        int columnIndex = cursor.getColumnIndex(Constant._ID);
        /**
         * getInt()根据参数中指定的字段下标 获取对应的int类型的value
         */
        int _id =cursor.getInt(columnIndex);
        String name = cursor.getString(cursor.getColumnIndex(Constant.NAME));
        int age = cursor.getInt(cursor.getColumnIndex(Constant.AGE));
        Person person = new Person(_id,name,age);
        list.add(person);
    }
    return list;

}

}

4,MySQLiteHelper.class

public class MySQLiteHelper extends SQLiteOpenHelper {
public MySQLiteHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
    super(context, name, factory, version);
}
public MySQLiteHelper(Context context){
    super(context,Constant.DATABASE_NAME,null,Constant.DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    String sql ="creat table "+Constant.DATABASE_NAME+" ("+Constant._ID+" Integer primary key, "+Constant.NAME
            +" varchar(10),"+Constant.AGE+" Integer)";
    db.execSQL(sql);
}

@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

}

}

5,实例化常数类
/**
* Created by sky on 2017/1/6.
*/
public class Constant {
public static final String DATABASE_NAME =”info.db”;
public static final int DATABASE_VERSION =1;
public static final String TABLENAME = “person”;
public static final String _ID = “_id”;
public static final String NAME = “name”;
public static final String AGE =”age”;

}
6，实体类

/**
 * 实体类
 */

public class Person {  
private int _id;
private String name;
private int age;

public Person(int _id, String name, int age) {
    this._id = _id;
    this.name = name;
    this.age = age;
}

@Override
public String toString() {
    return "_id="+this._id+",name="+this.name+",age="+this.age;
}

public int getAge() {
    return age;
}

public void setAge(int age) {
    this.age = age;
}

public int get_id() {
    return _id;
}

public void set_id(int _id) {
    this._id = _id;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

}