SD卡上写入数据库

     在SD卡上建立数据库和数据库表

1、判断SD卡是否存在

2、1存在，判断文件路径是否存在

3、2存在，判断数据库存是否存在

4、3存在，就可以打开数据库，并建立数据库表，返回该类（要用单例模式，为了确保数据库表只建立一次）

5,、执行增删查改方法，都需要执行打开数据库表，因为是在表里执行增删查改

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import com.yizhen.note.model.Note;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;

public class DBManage {

public static final String PATH = Environment.getExternalStorageDirectory().getAbsolutePath() + "/note/";
public static final String DBFILENAME = "noteBook.db";// 数据库名
public static final String TABLENAME = "note";// 存储事件数据的表名
// 创建表的语句
String sql = "create table " + TABLENAME + "(id integer primary key autoincrement,"
+ "title text,content text,category text," + "editor text,hot text,time text,uploadtag text)";
SQLiteDatabase db;
public DBManage(Context context) {
// dbHelper = new DBHelper(context);// 初始化数据库辅助类
// openData();
// DBManage openData = openData();
}

// 检查SD卡上是否有path数据库目录存在,不存在就创建，如果存在，就创建文件
File filepath = new File(PATH);
File dataFilepath = new File(PATH + DBFILENAME);

private void isPathExists() {
if (isSDexists()) {
if (!filepath.exists()) {
filepath.mkdir();
}
if (!dataFilepath.exists()) {
try {
dataFilepath.createNewFile();
} catch (IOException e) {
e.printStackTrace();
System.out.println("创建文件失败");
}
}
} else {
System.out.println("读取不到SD卡");
}
}

// 判断SD卡是否存在
private Boolean isSDexists() {
if (Environment.MEDIA_MOUNTED.equals(Environment.getExternalStorageState())) {
return true;
}
return false;
}

private static DBManage dbmanage = new DBManage();

public DBManage() {

}

// 打开数据库
public DBManage openData() {
isPathExists();
db = SQLiteDatabase.openOrCreateDatabase(dataFilepath, null);
db.execSQL(sql);
return dbmanage;
}

// 增加
public void addNote(Note note) {
// 使得db对象执行onCreate()
// SQLiteDatabase db = dbHelper.createFileOnSDcard(DBHelper.PATH,
// DBHelper.DBFILENAME);
// db = openData();
db = SQLiteDatabase.openOrCreateDatabase(dataFilepath, null);
String insert = "insert into " + DBHelper.TABLENAME
+ "(title,content,time,uploadtag,category,editor,hot) values(?,?,?,?,?,?,?)";


String[] params = { note.getTitle(), note.getContent(), note.getTime(), note.getUploadtag(),
note.getCategory(), note.getEditor(), note.getHot() };
db.execSQL(insert, params);
db.close();
}

// 删除
public void deleteNote(int id) {
// SQLiteDatabase db = dbHelper.getWritableDatabase();
// SQLiteDatabase db = dbHelper.createFileOnSDcard(DBHelper.PATH,
// DBHelper.DBFILENAME);
db = SQLiteDatabase.openOrCreateDatabase(dataFilepath, null);
String delete = "delete from " + DBHelper.TABLENAME + " where id=" + id;
db.execSQL(delete);
db.close();
}

// 修改
public void modifyNote(Note note, int id) {
// SQLiteDatabase db = dbHelper.getWritableDatabase();
// SQLiteDatabase db = dbHelper.createFileOnSDcard(DBHelper.PATH,
// DBHelper.DBFILENAME);
// db = openData();
db = SQLiteDatabase.openOrCreateDatabase(dataFilepath, null);
String modify = "update " + DBHelper.TABLENAME
+ " set title=?,content=?,time=?,uploadtag=?,category=?,editor=?,hot=? where id=?";
Object[] params = { note.getTitle(), note.getContent(), note.getTime(), note.getUploadtag(),
note.getCategory(), note.getEditor(), note.getHot(), id };
db.execSQL(modify, params);
db.close();
}

// 查询所有
public List<Note> findAll() {
// SQLiteDatabase db = dbHelper.getWritableDatabase();
// SQLiteDatabase db = dbHelper.createFileOnSDcard(DBHelper.PATH,
// DBHelper.DBFILENAME);
db = SQLiteDatabase.openOrCreateDatabase(dataFilepath, null);
String findAll = "select * from " + DBHelper.TABLENAME;
List<Note> list = new ArrayList<Note>();
try {
Cursor cursor = db.rawQuery(findAll, null);
Note note = null;
while (cursor.moveToNext()) {
int id = cursor.getInt(cursor.getColumnIndex("id"));
String title = cursor.getString(cursor.getColumnIndex("title"));
String content = cursor.getString(cursor.getColumnIndex("content"));
String time = cursor.getString(cursor.getColumnIndex("time"));
String uploadtag = cursor.getString(cursor.getColumnIndex("uploadtag"));
String category = cursor.getString(cursor.getColumnIndex("category"));
String editor = cursor.getString(cursor.getColumnIndex("editor"));
String hot = cursor.getString(cursor.getColumnIndex("hot"));
note = new Note(id, title, content, time, uploadtag, category, editor, hot);
list.add(note);
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("查询不到数据");
}
db.close();
return list;
}

public Note findOneEvent(int id) {
// SQLiteDatabase db = dbHelper.getWritableDatabase();
// SQLiteDatabase db = dbHelper.createFileOnSDcard(DBHelper.PATH,
// DBHelper.DBFILENAME);
String findAll = "select * from " + DBHelper.TABLENAME + " where id=" + id;
Cursor cursor = db.rawQuery(findAll, null);


id = cursor.getInt(cursor.getColumnIndex("id"));
String title = cursor.getString(cursor.getColumnIndex("title"));
String content = cursor.getString(cursor.getColumnIndex("content"));
String time = cursor.getString(cursor.getColumnIndex("time"));
String uploadtag = cursor.getString(cursor.getColumnIndex("uploadtag"));
String category = cursor.getString(cursor.getColumnIndex("category"));
String editor = cursor.getString(cursor.getColumnIndex("editor"));
String hot = cursor.getString(cursor.getColumnIndex("hot"));
Note note = new Note(id, title, content, time, uploadtag, category, editor, hot);
db.close();
return note;
}

// 根据标题查找数据
public List<Note> findByCondition(String condition) {
// SQLiteDatabase db = dbHelper.getWritableDatabase();
// SQLiteDatabase db = dbHelper.createFileOnSDcard(DBHelper.PATH,
// DBHelper.DBFILENAME);
String findAll = "select * from " + DBHelper.TABLENAME + " where title like %" + condition + "% or time like %"
+ condition + "% or content like %" + condition + "%";
Cursor cursor = db.rawQuery(findAll, null);
List<Note> list = new ArrayList<Note>();
Note note = null;
while (cursor.moveToNext()) {
int id = cursor.getInt(cursor.getColumnIndex("id"));
String title = cursor.getString(cursor.getColumnIndex("title"));
String content = cursor.getString(cursor.getColumnIndex("content"));
String time = cursor.getString(cursor.getColumnIndex("time"));
String uploadtag = cursor.getString(cursor.getColumnIndex("uploadtag"));
String category = cursor.getString(cursor.getColumnIndex("category"));
String editor = cursor.getString(cursor.getColumnIndex("editor"));
String hot = cursor.getString(cursor.getColumnIndex("hot"));
note = new Note(id, title, content, time, uploadtag, category, editor, hot);
list.add(note);
}
db.close();
return list;
}

// 根据时间查找数据
public List<Note> findByTime(String condition) {
// SQLiteDatabase db = dbHelper.getWritableDatabase();
// SQLiteDatabase db = dbHelper.createFileOnSDcard(DBHelper.PATH,
// DBHelper.DBFILENAME);
String findAll = "select * from " + DBHelper.TABLENAME + " where time like %" + condition + "%";
Cursor cursor = db.rawQuery(findAll, null);
List<Note> list = new ArrayList<Note>();
Note note = null;
while (cursor.moveToNext()) {
int id = cursor.getInt(cursor.getColumnIndex("id"));
String title = cursor.getString(cursor.getColumnIndex("title"));
String content = cursor.getString(cursor.getColumnIndex("content"));
String time = cursor.getString(cursor.getColumnIndex("time"));
String uploadtag = cursor.getString(cursor.getColumnIndex("uploadtag"));
String category = cursor.getString(cursor.getColumnIndex("category"));
String editor = cursor.getString(cursor.getColumnIndex("editor"));
String hot = cursor.getString(cursor.getColumnIndex("hot"));
note = new Note(id, title, content, time, uploadtag, category, editor, hot);
list.add(note);
}
db.close();
return list;
}

}