1 SQLite数据库
1.1 SQLite
SQLite是一款轻量级的数据库，它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等主流的操作系统，同时，能够跟很多程序语言相结合，比如Tcl、C#、PHP、Java等，还有ODBC接口，同样，比起Mysql、PostgreSQL这两款开源世界著名的数据库管理系统来讲，它的处理速度较快。SQLite第一个Alpha版本诞生于2000年5月。
1.2 SQLite数据类型
一般数据库采用的固定的静态数据类型，而SQLite采用的是动态数据类型，会根据存入值自动判断。
SQLite具有以下五种数据类型：
①NULL：空值。
②INTEGER：有符号整型，具体取决于存入数字的范围大小。
③REAL：浮点数字，存储8-byte IEEE浮点数
④TEXT：字符串文本
⑤BLOB：二进制对象。
实际上，sqlite3也接受如下数据类型：
samllint 16位元的整数
integer 32位元的整数
decimal(p, s) p：精确值和s大小的十进制位整数，精确值p是指全部有几个数（digits）大小值，s是指小数点后有几位数。如果没有特别指定，则系统会设为p=5， s=0.
float 32位元的实数
double 64位元的实数
char(n) n长度的字串，n不能超过254
varchar(n)长度不固定且最长为n的子串，n不能超过4000
graphic(n) 和 char(n) 一样，不过其单位是两个字元double-bytes，n不能超过127，这个形态是为了支援两个字元长度的字体，例如中文字。
vargraphic(n)可变长度且最大长度为n的双字元字串，n不能超过2000
date 包含了年份、月份、日期
time包含了小时、分钟、秒
timestamp包含了年、月、日、时、分、秒、千分之一秒
datetime包含日期时间格式，必须写成2018-05-20，不能写为2018-5-20，否则在读取时产生错误
1.3 sqlite3数据库使用
一款sqlite的GUI工具：
与MySQL类似，具体参考MySQL。
使用【sqlite3 数据库名】，可以进入一个数据库；如果数据库不存在，则直接创建一个数据库
1.4 示例
创建数据库data.db，创建表t_student，并编写student类的增删改查
本程序采用单元测试的方式，执行程序，并不运行在虚拟机或真机上
可对单元测试中的方法进行单个测试：
activity.java：
public class MainActivity extends Activity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.main, menu);
return true;
}
}public class Student {
private int sid;
private String name;
private short age;
public Student() {
super();
// TODO Auto-generated constructor stub
}
public Student(int sid, String name, short age) {
super();
this.sid = sid;
this.name = name;
this.age = age;
}
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public short getAge() {
return age;
}
public void setAge(short age) {
this.age = age;
}
@Override
public String toString() {
return "sid=" +sid+ "; name=" +name+ "; age=" +age;
}
}public class DBOpenHelper extends SQLiteOpenHelper {
private static final int VERSION = 1;
private static final String DBNAME = "data.db";
public DBOpenHelper(Context context) {
super(context, DBNAME, null, VERSION);
}
public DBOpenHelper(Context context, String name, CursorFactory factory,
int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table t_student(sid integer primary key, name varchar(20), age integer)");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
}
}public class StudentDAO {
private DBOpenHelper dbopenHelper;
private SQLiteDatabase sqliteDB;
public StudentDAO(Context context) {
dbopenHelper = new DBOpenHelper(context);
}
public void add(Student stu) {
sqliteDB = dbopenHelper.getWritableDatabase();
sqliteDB.execSQL("insert into t_student(sid, name, age) values(?, ?, ?)",
new Object[]{stu.getSid(), stu.getName(), stu.getAge()});
}
public void update(Student stu) {
sqliteDB = dbopenHelper.getWritableDatabase();
sqliteDB.execSQL("update t_student set name=?, age=? where sid=?",
new Object[]{stu.getName(), stu.getAge(), stu.getSid()});
}
public Student find(int sid) {
sqliteDB = dbopenHelper.getWritableDatabase();
Cursor cursor = sqliteDB.rawQuery("select sid,name,age from t_student where sid=?",
new String[]{String.valueOf(sid)});
if(cursor.moveToNext()) {
return new Student(cursor.getInt(cursor.getColumnIndex("sid"))
, cursor.getString(cursor.getColumnIndex("name"))
, cursor.getShort(cursor.getColumnIndex("age")) );
}
return null;
}
public void delete(Integer...sids) {
if(sids.length > 0) {
StringBuffer sb = new StringBuffer();
for(int i=0; i<sids.length; i++) {
sb.append('?').append(',');
}
sb.deleteCharAt(sb.length()-1);
sqliteDB = dbopenHelper.getWritableDatabase();
sqliteDB.execSQL("delete from t_student where sid in (" +sb+ ")",
(Object[])sids);
}
}
public List<Student> getScrollData(int start, int count) {
List<Student> students = new ArrayList<Student>();
sqliteDB = dbopenHelper.getWritableDatabase();
Cursor cursor = sqliteDB.rawQuery("select * from t_student limit ?, ?",
new String[]{String.valueOf(start), String.valueOf(count)});
while(cursor.moveToNext()) {
students.add(new Student(
cursor.getInt(cursor.getColumnIndex("sid")),
cursor.getString(cursor.getColumnIndex("name")),
cursor.getShort(cursor.getColumnIndex("age")) ));
}
return students;
}
public long getCount() {
sqliteDB = dbopenHelper.getWritableDatabase();
Cursor cursor = sqliteDB.rawQuery("select count(sid) from t_student", null);
if(cursor.moveToNext()) {
return cursor.getLong(0);
}
return 0;
}
}<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.example.sqlitedemo"
android:versionCode="1"
android:versionName="1.0" >
<uses-sdk
android:minSdkVersion="8"
android:targetSdkVersion="18" />
<application
android:allowBackup="true"
android:icon="@drawable/ic_launcher"
android:label="@string/app_name"
android:theme="@style/AppTheme" >
<activity
android:name="com.example.sqlitedemo.MainActivity"
android:label="@string/app_name" >
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
<uses-library android:name="android.test.runner"/> <!-- 进行单元测试 -->
</application>
<instrumentation android:name="android.test.InstrumentationTestRunner"
android:targetPackage="com.example.sqlitedemo"
android:label="Test My App" /> <!-- 进行单元测试： target填写主程序所在的包 ，而不是测试单元所在的包-->
</manifest>
public class StudentDAOTest extends AndroidTestCase {
private final static String TAG = "StudentDAOTest";
public void testAdd() {
StudentDAO studentDAO = new StudentDAO(this.getContext());
Student stu = new Student(1, "jiaozl", (short) 12);
studentDAO.add(stu);
Log.i(TAG, "add succeeded");
}
public void testUpdate() {
StudentDAO studentDAO = new StudentDAO(this.getContext());
Student stu = studentDAO.find(1);
stu.setName("kuka");
studentDAO.update(stu);
Log.i(TAG, "update succeeded");
}
public void testFind() {
StudentDAO studentDAO = new StudentDAO(this.getContext());
Student stu = studentDAO.find(1);
if(null == stu) {
Log.i(TAG, "not find");
} else {
Log.i(TAG, stu.toString());
}
}
public void testGetScrollData() {
StudentDAO studentDAO = new StudentDAO(this.getContext());
List<Student> stus = studentDAO.getScrollData(0, 1);
for(Student stu:stus) {
Log.i(TAG, stu.toString());
}
}
public void testGetCount() {
StudentDAO studentDAO = new StudentDAO(this.getContext());
long count = studentDAO.getCount();
Log.i(TAG, count+"");
}
}1.5 对1.4中StudentDAO中的方法进行改造
使用SQLiteDB库提供给我们的另一种方法
public void add(Student stu) {
sqliteDB = dbopenHelper.getWritableDatabase();
// sqliteDB.execSQL("insert into t_student(sid, name, age) values(?, ?, ?)",
// new Object[]{stu.getSid(), stu.getName(), stu.getAge()});
ContentValues contentValues = new ContentValues();
contentValues.put("sid", stu.getSid());
contentValues.put("name", stu.getName());
contentValues.put("age", stu.getAge());
// nullColumnHack：当values参数为空或者里面没有内容的时候，我们的inert会失败（底层数据库不允许插入空值）
// 为了防止这种情况，我们指定了一个列名，当发现要插入的行为空时，就会将你指定的这个列的值设为null，
// 然后再向数据库中插入
sqliteDB.insert("t_student", "sid", contentValues);
}
public void update(Student stu) {
sqliteDB = dbopenHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", stu.getName());
values.put("age", stu.getAge());
sqliteDB.update("t_student", values, "sid=?",
new String[]{String.valueOf(stu.getSid())});
}
public Student find(int sid) {
sqliteDB = dbopenHelper.getWritableDatabase();
Cursor cursor = sqliteDB.query("t_student", new String[]{"sid", "name", "age", "sex"},
"sid=?", new String[]{String.valueOf(sid)}, null, null, null);
if(cursor.moveToNext()) {
return new Student(cursor.getInt(cursor.getColumnIndex("sid"))
, cursor.getString(cursor.getColumnIndex("name"))
, cursor.getShort(cursor.getColumnIndex("age")) );
}
return null;
}
public void delete(Integer...sids) {
if(sids.length > 0) {
StringBuffer sb = new StringBuffer();
String[] strPid = new String[sids.length];
for(int i=0; i<sids.length; i++) {
sb.append('?').append(',');
strPid[i]=String.valueOf(sids[i]);
}
sb.deleteCharAt(sb.length()-1);
sqliteDB = dbopenHelper.getWritableDatabase();
sqliteDB.delete("t_student", "sid in("+sb+")", strPid);
}
}
public List<Student> getScrollData(int start, int count) {
List<Student> students = new ArrayList<Student>();
sqliteDB = dbopenHelper.getWritableDatabase();
Cursor cursor = sqliteDB.query("t_student", new String[]{"sid", "name", "age"}, null, null, null, null, "sid desc", start+","+count);
while(cursor.moveToNext()) {
students.add(new Student(
cursor.getInt(cursor.getColumnIndex("sid")),
cursor.getString(cursor.getColumnIndex("name")),
cursor.getShort(cursor.getColumnIndex("age")) ));
}
return students;
}
public long getCount() {
sqliteDB = dbopenHelper.getWritableDatabase();
Cursor cursor = sqliteDB.query("t_student", new String[]{"count(*)"}, null, null, null, null, null);
if(cursor.moveToNext()) {
return cursor.getLong(0);
}
return 0;
}
1.6 数据库更新
public class DBOpenHelper extends SQLiteOpenHelper {
private static final int VERSION = 1;
private static final String DBNAME = "data.db";
private static final String STUDENT = "t_student";
private static final String TAG = "DBOpenHelper";
public DBOpenHelper(Context context) {
super(context, DBNAME, null, VERSION);
}
public DBOpenHelper(Context context, String name, CursorFactory factory,
int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table "+STUDENT+"(sid integer primary key, name varchar(20), age integer)");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.i(TAG, "update");
/*
数据库更新完毕后，建议注释下列语句
*/
String tempTable = "temp_student";
db.execSQL("alter table " + STUDENT + " rename to "+ tempTable);
db.execSQL("create table "+STUDENT+"(sid integer primary key, name varchar(20), age integer, sex varchar(2))");
String sql = "insert into "+STUDENT+" (name, age, sex) select name,age,'男' from " + tempTable;
db.execSQL(sql);
}
}2 文件
2.1 两个重要的方法
①openFileOutput(String name, int mode)
name: 文件名称，不能包含分隔符“/”，如果文件不存在，Android会自动创建。创建的文件目录为：【/data/data/<package_name>/files】。
mode: 操作模式------->
- MODE_APPEND：如果文件中已经存在内容，则在内容末尾追加
- MODE_PRIVATE：文件仅能被该程序访问
- MODE_WORLD_READABLE：文件允许被其他应用程序读
- MODE_WORLD_WRITEABLE：文件允许被其他应用程序写
- MODE_WORLD_READABLE + MODE_WORLD_WRITEABLE
②openFileInput(String name)
2.2 示例
FileService.java
public class FileService {
/**
* 文件保存
* @param outStream
* @param content
* @throws Exception
*/
public void save(OutputStream outStream, String content) throws Exception {
outStream.write(content.getBytes());
outStream.close();
}
/**
* 读取文件信息
*/
public String read(InputStream inStream) throws Exception {
ByteArrayOutputStream outStream = new ByteArrayOutputStream();
byte[] buffer = new byte[1024];
int len;
while((len=inStream.read(buffer))!=-1) {
outStream.write(buffer, 0, len);
}
byte[] data = outStream.toByteArray();
outStream.close();
inStream.close();
return new String(data);
}
}public class FileServiceTest extends AndroidTestCase {
private static final String TAG = "FileServiceTest";
public void testSave() throws Exception {
OutputStream outStream = this.getContext().openFileOutput(
"jiaozl.txt", Context.MODE_APPEND);
FileService service = new FileService();
service.save(outStream, "hello world!");
}
public void testRead() throws Exception {
InputStream inStream = this.getContext().openFileInput("jiaozl.txt");
FileService service = new FileService();
String content = service.read(inStream);
Log.i(TAG, content);
}
}<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.example.filedemo"
android:versionCode="1"
android:versionName="1.0" >
<uses-sdk
android:minSdkVersion="8"
android:targetSdkVersion="18" />
<application
android:allowBackup="true"
android:icon="@drawable/ic_launcher"
android:label="@string/app_name"
android:theme="@style/AppTheme" >
<activity
android:name="com.example.filedemo.MainActivity"
android:label="@string/app_name" >
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
<uses-library android:name="android.test.runner"/> <!-- 单元测试 -->
</application>
<instrumentation android:name="android.test.InstrumentationTestRunner"
android:targetPackage="com.example.filedemo"
android:label="test file service"></instrumentation> <!-- 单元测试 -->
</manifest>3 SharedPreferences
示例：
public class MainActivity extends Activity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
((Button)findViewById(R.id.save)).setOnClickListener(listener);
((Button)findViewById(R.id.show)).setOnClickListener(listener);
}
private OnClickListener listener = new OnClickListener() {
@Override
public void onClick(View v) {
SharedPreferences pres = MainActivity.this.getSharedPreferences(
"jiaozl", Context.MODE_APPEND);
switch (v.getId()) {
case R.id.save:
Editor editor = pres.edit();
editor.putString("name", "jiaozl");
editor.putInt("age", Integer.valueOf(23));
editor.commit();
Toast.makeText(MainActivity.this, "保存成功", Toast.LENGTH_LONG).show();
break;
case R.id.show:
String name = pres.getString("name", "NO");
int age = pres.getInt("age", 0);
String bir = pres.getString("bir", "NO");
Toast.makeText(MainActivity.this, "Name: "+name+";age: " +age+"; bir: " + bir, Toast.LENGTH_LONG).show();
break;
default:
break;
}
}
};
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
