Android SQLite 数据库操作
对于 数据库的操作
创建数据库和表
- 首先 写一个用来操作数据库的 DB 类,使用单例模式
public static RadishStudyDB getInstance(Context context) {
if (m_radishStudyDB == null) {
m_radishStudyDB = new RadishStudyDB(context);
}
return m_radishStudyDB;
}
- 在DB 类中 写一个私有的 实现 SQLiteOpenHelper的数据库帮助类,并在DB类的 构造函数中实例化,并且获取SQLiteDatabase 对象,操作数据库 都是这个对象来操作的.同时写一个 关闭 SQLiteDatabase 对象的 函数,在退出app的时候关闭数据库.当第一次调用函数 getWritableDatabase( ) 或者 getReadableDatabase()函数的时候才会执行 onCreate()创建表,函数onUpgrade()升级数据库的时候会调用.
DB的构造函数
public RadishStudyDB(Context context) {
m_radishStudyOpenHelper = new StudentOpenHelper(context, DB_NAME, null, m_version);
m_radishStudydb = m_radishStudyOpenHelper.getWritableDatabase();
}
创建表的Sql语句:
private static String create_TABLE_STUDENT = "create table " + TABLE_NAME_STUDENT
+ " (_id integer not null primary key autoincrement,"
+ "name text,"
+ "number integer,"
+ "age integer)";
private class StudentOpenHelper extends SQLiteOpenHelper {
private int m_version = 1;
public StudentOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
this.m_version = version;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(create_TABLE_STUDENT);
Log.i(TAG, "创建数据库,或表");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.i(TAG, "数据库 更新");
}
}
public void clossDB() {
if (m_radishStudydb.isOpen()) {
m_radishStudydb.close();
}
}
到此为止,数据库和表都创建完毕.
删除数据库
context.deleteDatabase("DB_NAME");
对于 表的操作
创建 表 在SQLiteOpenHelper 的 onCreate()方法中 创建
,数据库第一次执行函数 getWritableDatabase( ) 或者 getReadableDatabase()函数的时候才会执行onCreate()方法,真正创建表.
使用SQL语句创建
public void createTable(String tableName,String _id,String name,String number,String age){
String CREATE_TABLE = "create table " + tableName
+ " ("+_id+" integer not null primary key autoincrement,"
+ name+" text,"
+ number+" integer,"
+ age+" integer)";
m_radishStudydb.execSQL(CREATE_TABLE);
}
查询表是否存在 的两种方法(我知道到的)
查询 Sqlite_master
public boolean sqlTableIsExist(String tableName) {
boolean result = false;
try {
String sql = "select count(*) as c from Sqlite_master where type ='table' and name ='" + tableName.trim() + "' ";
Cursor cursor = m_radishStudydb.rawQuery(sql, null);
if (cursor.moveToNext()) {
int count = cursor.getInt(0);
if (count > 0) {
result = true;
}
}
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
使用 函数 query()查询,如果没有表,则会报异常
public boolean sqlTableIsExist(String tableName) {
boolean result = false;
try {
Cursor query = m_radishStudydb.query(tableName, null, null, null, null, null, null);
if (query != null) {
result = true;
}
} catch (Exception e) {
e.printStackTrace();
Log.i("RadishStudyDB","e ------------------- ");
}
return result;
}
查询表中所有字段
public String[] getAllTableLine(String tableName) {
String[] columnNames = null;
try {
Cursor cursor = m_radishStudydb.query(tableName, null, null, null, null, null, null);
columnNames = cursor.getColumnNames();
} catch (Exception e) {
e.printStackTrace();
}
return columnNames;
}
查询表中字段是否存在
public boolean sqlTableFieldIsExist(String tableName,String type) {
boolean result = false;
try {
Cursor cursor = m_radishStudydb.query(tableName, null, null, null, null, null, null);
int count = cursor.getColumnIndex(type); //返回 -1 表示不存在
if (count >= 0) {
result = true;
}
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
添加表中的字段 (alter 只能重命名和添加字段)
public boolean addLine(String insertColumnTable,String lineName) {
boolean databasePath = false;
try {
String ADD_LINE = "alter table "+insertColumnTable+" add column "+lineName+" text ";
m_radishStudydb.execSQL(ADD_LINE);
} catch (SQLException e) {
e.printStackTrace();
return databasePath;
}
databasePath = sqlTableFieldIsExist(insertColumnTable, lineName);
return databasePath;
}
删除表中的字段
修改表中的字段
重命名表的名称
public boolean renameTable(String oldTableName,String newTableName) {
boolean databasePath = false;
try {
String ADD_LINE = "alter table "+oldTableName+" rename to "+newTableName;
m_radishStudydb.execSQL(ADD_LINE);
} catch (SQLException e) {
e.printStackTrace();
return databasePath;
}
databasePath = sqlTableIsExist2(newTableName);
return databasePath;
}
复制表
创建一张新表,复制另一张表的数据
public boolean copyTable(String oldTableName, String newTableName) {
//创建一张表并且拷贝另一张表的数据
boolean databasePath = false;
try {
String COPY_TABLE2 = "create table "+newTableName+" as select _id, number , name ,age from "+oldTableName;
String COPY_TABLE3 = "create table "+newTableName+" as select _id, number , name ,age from "+oldTableName +" where number = '222'";
m_radishStudydb.execSQL(COPY_TABLE2);
} catch (SQLException e) {
e.printStackTrace();
return databasePath;
}
databasePath = sqlTableIsExist2(newTableName);
return databasePath;
}
从一张表中复制数据到另一张表
public void copyTable2(String oldTableName, String newTableName) {
//创建一张表并且拷贝另一张表的数据
try {
String COPY_TABLE2 = "insert into "+newTableName+" select _id, number , name ,age from "+oldTableName;
String COPY_TABLE3 = "insert table "+newTableName+" select _id, number , name ,age from "+oldTableName +" where number = '222'";
m_radishStudydb.execSQL(COPY_TABLE2);
} catch (SQLException e) {
e.printStackTrace();
}
}
删除表
public boolean deleteTable(String tableName) {
boolean databasePath = false;
try {
String DELETE_TABLE = "drop table "+tableName;
m_radishStudydb.execSQL(DELETE_TABLE);
} catch (SQLException e) {
e.printStackTrace();
return databasePath;
}
databasePath = sqlTableIsExist2(tableName);
return !databasePath;
}
对于表中 数据 的操作
添加记录
用insert()函数
public long addRecord (StudentBean studentBean){
ContentValues values = new ContentValues();
values.put("name",studentBean.getName());
values.put("number",studentBean.getNumber());
values.put("age",studentBean.getAge());
long insert = m_radishStudydb.insert(TABLE_NAME_STUDENT, null, values);
return insert;
}
用SQL语句 insert
public void addRecordSQL (StudentBean studentBean){
String ADD_RECORD = "insert into "+TABLE_NAME_STUDENT+"( name,number,age) values ('"
+studentBean.getName()+"',"
+studentBean.getNumber()+","
+studentBean.getAge()+")";
m_radishStudydb.execSQL(ADD_RECORD);
}
用SQL语句 insert 从另一张表中复制数据添加记录
public void addRecordSQLFromTable (String table){
String ADD_RECORD = "insert into "+table+" select _id, number,name,age from "+TABLE_NAME_STUDENT+" where number = 11 and name = 'jhk'";
m_radishStudydb.execSQL(ADD_RECORD);
}
查询记录
查询所有记录
public LinkedList<StudentBean> queryAllRecord(String tableName) {
LinkedList<StudentBean> studentBeenList = new LinkedList<>();
try {
Cursor cursor = m_radishStudydb.query(tableName, null, null, null, null, null, null);
if (cursor != null) {
while (cursor.moveToNext()){
StudentBean studentBean = new StudentBean();
studentBean.set_id(cursor.getInt(cursor.getColumnIndex("_id")));
studentBean.setName(cursor.getString(cursor.getColumnIndex("name")));
studentBean.setNumber(cursor.getInt(cursor.getColumnIndex("number")));
studentBean.setAge(cursor.getInt(cursor.getColumnIndex("age")));
studentBeenList.add(studentBean);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return studentBeenList;
}
按条件查询记录
public LinkedList<StudentBean> queryAllRecord2(String tableName,int id) {
LinkedList<StudentBean> studentBeenList = new LinkedList<>();
try {
Cursor cursor = m_radishStudydb.query(tableName, null, "_id = "+id, null, null, null, null);
if (cursor != null) {
while (cursor.moveToNext()){
StudentBean studentBean = new StudentBean();
studentBean.set_id(cursor.getInt(cursor.getColumnIndex("_id")));
studentBean.setName(cursor.getString(cursor.getColumnIndex("name")));
studentBean.setNumber(cursor.getInt(cursor.getColumnIndex("number")));
studentBean.setAge(cursor.getInt(cursor.getColumnIndex("age")));
studentBeenList.add(studentBean);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return studentBeenList;
}