SQLite

最新推荐文章于 2024-03-28 21:20:16 发布

Wi1ls

最新推荐文章于 2024-03-28 21:20:16 发布

阅读量258

点赞数

本文链接：https://blog.csdn.net/github_34853205/article/details/51297277

版权

用户保存常量的类

public class Person_constant {
    public final static String TABLE_NAME_PERSON = "t_person";
    public final static String TABLE_NAME_PERSON_id = "id";
    public final static String TABLE_NAME_PERSON_NAME = "name";
    public final static String TABLE_NAME_PERSON_AGE = "age";

}

用户保存数据Person类

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

    public Person() {
    }

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

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    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;
    }

    @Override
    public String toString() {
        return "Person{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

工具类

public class SQLiteOpenHelperDemo extends SQLiteOpenHelper {
    private static SQLiteOpenHelperDemo sqLiteOpenHelperDemo;
    private final static String DATABASE_NAME = "MyDataBase.db";

    private final static int VERSION = 1;

    private SQLiteOpenHelperDemo(Context context) {
        super(context, DATABASE_NAME, null, VERSION);
        System.out.println(sqLiteOpenHelperDemo);
    }

    public static SQLiteOpenHelperDemo getInstance(Context context) {
        if (sqLiteOpenHelperDemo == null) {
            sqLiteOpenHelperDemo = new SQLiteOpenHelperDemo(context);
        }
        return sqLiteOpenHelperDemo;
    }


    @Override
    public void onCreate(SQLiteDatabase db) {
        StringBuffer creat_table = new StringBuffer();
        creat_table.append("CREATE TABLE IF NOT EXISTS ")
                .append(Person_constant.TABLE_NAME_PERSON)
                .append("(")
                .append(Person_constant.TABLE_NAME_PERSON_id)
                .append(" INTEGER PRIMARY KEY AUTOINCREMENT,")
                .append(Person_constant.TABLE_NAME_PERSON_NAME)
                .append(" VARCHAR(20) UNIQUE,")
                .append(Person_constant.TABLE_NAME_PERSON_AGE)
                .append(" INTEGER DEFAULT 1 CHECK(age BETWEEN 1 AND 150))");
        db.execSQL(creat_table.toString());

        StringBuffer insert_table=new StringBuffer();
        insert_table.append("INSERT INTO ")
        .append(Person_constant.TABLE_NAME_PERSON)
        .append(" VALUES")
        .append("(null,?,?)");
        db.execSQL(insert_table.toString(),new Object[]{"张三",20});
        db.execSQL(insert_table.toString(),new Object[]{"李四",21});
        db.execSQL(insert_table.toString(),new Object[]{"王五",22});
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

接口类`

public interface DAO {
    void insert(Person person);
    void update(Person person);
    void delete(Person person);
    List<Person> select();
    List<Person> select(Integer... id);
    List<Person> select(String... name);
}
实现接口的业务类

//
// long insert(String table,String nullColumnHack,ContentValues values)
// table:插入的表名
// nullColumnHack:当values为null或者size()为0时，强行null时的是数据列列名，不能是主键与非空列
// values:插入的一行记录数据，内部封装了HashMap
//
//update(String table,ContentValues values,String whereClause,String[] whereArgs)
// table:要更新的表名
// values：要更新的数据，内部封装了HashMap
// whereClause:满足该条件的记录会被更新(占位符)
// whereArgs：为whereArgs字句传入参数(代替占位符)
//
//delete(String table,String whereClause,String[] whereArgs)
// table:要删除的表名
// whereClause：满足该条件的记录会被删除（占位符）
// whereArgs:为whereClause传入参数(代替占位符)
//
//Cursor query(boolean distinct,是否要去除重复记录
// String table,要查询数据的表名
// String[] columns,要查询的列名
// String whereClause,查询条件子条件（占位符）
// String[] selectionArgs,为占位符传入参数
// String groupBy,控制分组
// String having,对分组过滤
// String orderBy,对记录排序
// String limit)进行分页
//
//
public class DAOBean implements DAO {
private SQLiteOpenHelperDemo sqLiteOpenHelperDemo;

public DAOBean(Context context) {
    this.sqLiteOpenHelperDemo = SQLiteOpenHelperDemo.getInstance(context);
}

@Override
public void insert(Person person) {
    SQLiteDatabase db = sqLiteOpenHelperDemo.getWritableDatabase();

// String insert_table =
// “INSERT INTO ”
// + Person_constant.TABLE_NAME_PERSON
// + ” VALUES(null,?,?)”;
// db.execSQL(insert_table, new Object[]{person.getName(), person.getAge()});
ContentValues contentValues=new ContentValues();
contentValues.put(“id”,person.getId());
contentValues.put(“name”,person.getName());
contentValues.put(“age”,person.getAge());
db.insert(Person_constant.TABLE_NAME_PERSON,null,contentValues);

}

@Override
public void update(Person person) {
    SQLiteDatabase db = sqLiteOpenHelperDemo.getWritableDatabase();
    String update = "UPDATE "
            + Person_constant.TABLE_NAME_PERSON
            + " SET "
            + Person_constant.TABLE_NAME_PERSON_NAME
            + "=?,"
            + Person_constant.TABLE_NAME_PERSON_AGE
            + "=?"
            + " WHERE "
            + Person_constant.TABLE_NAME_PERSON_id
            + "=?";

    db.execSQL(update, new Object[]{person.getName(), person.getAge(), person.getId()});
}

@Override
public void delete(Person person) {
    SQLiteDatabase db = sqLiteOpenHelperDemo.getWritableDatabase();
    String delete = "DELETE FROM "
            + Person_constant.TABLE_NAME_PERSON
            + " WHERE "
            + Person_constant.TABLE_NAME_PERSON_id
            + "=?";
    db.execSQL(delete, new Object[]{person.getId()});
}

@Override
public List<Person> select() {
    SQLiteDatabase db = sqLiteOpenHelperDemo.getWritableDatabase();
    String selectAll = "SELECT *FROM " + Person_constant.TABLE_NAME_PERSON;
    List<Person> list = new ArrayList<>();
    Cursor cursor = null;
    try {
        cursor = db.rawQuery(selectAll, null);
        while (cursor.moveToNext()) {
            Person person = new Person(
                    cursor.getInt(cursor.getColumnIndex("id")),
                    cursor.getString(cursor.getColumnIndex("name")),
                    cursor.getInt(cursor.getColumnIndex("age"))
            );
            list.add(person);
        }
        return list;
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (db != null && db.isOpen()) {
            db.close();
            db = null;
        }
        if (cursor != null && !cursor.isClosed()) {
            cursor.close();
            cursor = null;
        }
    }
    return null;
}

@Override
public List<Person> select(Integer... id) {
    SQLiteDatabase db = sqLiteOpenHelperDemo.getWritableDatabase();
    List<Person> list = new ArrayList<>();

    StringBuffer sb = new StringBuffer();
    for (int ids : id) {
        sb.append("?,");
    }
    sb.deleteCharAt(sb.length() - 1);
    String select = "SELECT *FROM "
            + Person_constant.TABLE_NAME_PERSON
            + " WHERE id in (" + sb.toString() + ")";
    String[] st = new String[id.length];
    for (int i = 0; i < id.length; i++) {
        st[i] = String.valueOf(id[i]);
    }
    Cursor cursor = null;
    try {
        cursor = db.rawQuery(select, st);
        while (cursor.moveToNext()) {
            Person p = new Person(
                    cursor.getInt(cursor.getColumnIndex("id")),
                    cursor.getString(cursor.getColumnIndex("name")),
                    cursor.getInt(cursor.getColumnIndex("age"))
            );
            list.add(p);
        }
        return list;
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (db != null && db.isOpen()) {
            db.close();
            db = null;
        }
        if (cursor != null && !cursor.isClosed()) {
            cursor.close();
            cursor = null;
        }
    }
    return null;
}

@Override
public List<Person> select(String... name) {
    List<Person> list = new ArrayList<>();
    SQLiteDatabase db = sqLiteOpenHelperDemo.getWritableDatabase();
    StringBuffer sb = new StringBuffer();
    for (String names : name) {
        sb.append("?,");
    }
    sb.deleteCharAt(sb.length() - 1);

    String selectName = "SELECT *FROM "
            + Person_constant.TABLE_NAME_PERSON
            + " WHERE name in (" + sb.toString() + ")";
    Cursor cursor = null;
    try {
        cursor = db.rawQuery(selectName, name);
        while (cursor.moveToNext()) {
            Person p = new Person(
                    cursor.getInt(cursor.getColumnIndex("id")),
                    cursor.getString(cursor.getColumnIndex("name")),
                    cursor.getInt(cursor.getColumnIndex("age"))
            );
            list.add(p);
        }
        return list;
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (db != null && db.isOpen()) {
            db.close();
            db = null;
        }
        if (cursor != null && !cursor.isClosed()) {
            cursor.close();
            cursor = null;
        }
    }
    return null;
}

@Override
public List<Person> selectPagination(int pageNo, int pageSize) {


    return null;
}

}