隔了挺久的,又遇到使用sqlite数据库了。写了个小demo,放在这用来加强记忆。
1.建个数据表
package com.xing.xiagaocheng.fileupdownload3.db; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; public class SqliteHelper extends SQLiteOpenHelper { public SqliteHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) { super(context, name, factory, version); } @Override public void onCreate(SQLiteDatabase sqLiteDatabase) { StringBuffer vStringBuffer=new StringBuffer(); //新建上传表头 文件名(用来管理文件的上传,上传大小,上传) vStringBuffer.append("Create table upload_file("); vStringBuffer.append("AutoID integer primary key autoincrement,"); // vStringBuffer.append("BillID varchar(50),"); // vStringBuffer.append("FileDate varchar(20)"); vStringBuffer.append("FilePath varchar(50),"); //文件途径 vStringBuffer.append("FileSize varchar(50),"); //文件上传大小 vStringBuffer.append("UploadState int)"); //文件上传状态 , 0表示未上传,1表示已上传,2表示正在上传 String sSQL=vStringBuffer.toString(); sqLiteDatabase.execSQL(sSQL); // //新建上传表明细 // vStringBuffer=new StringBuffer(); // vStringBuffer.append("create table t_upload_list("); // vStringBuffer.append("AutoID integer primary key autoincrement,"); // vStringBuffer.append("BillID varchar(50),"); // vStringBuffer.append("UploadNum int)"); // sSQL=vStringBuffer.toString(); // sqLiteDatabase.execSQL(sSQL); } @Override public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) { } }
2.建立一个类,编写增删改查方法
package com.intefly.xiagaocheng.fileupdownload3.db; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.util.Log; import java.util.ArrayList; import java.util.List; public class SqliteManager { private SqliteHelper sqliteHelper; private static SqliteManager sqliteManager; private SqliteManager(Context context,String name,int version){ this.sqliteHelper=new SqliteHelper(context,name,null,version); } public static SqliteManager getInstance(Context context,String name,int version){ if (sqliteManager==null){ synchronized (SqliteManager.class){ sqliteManager=new SqliteManager(context,name,version); } } return sqliteManager; } /** * 添加一个item进去 * @param contentValues */ public void saveItem(ContentValues contentValues){ SQLiteDatabase sdb=sqliteHelper.getWritableDatabase(); sdb.insert("upload_file",null,contentValues); Log.e("tiwolf", "saveItem: "+contentValues.get("FilePath") ); } /** * 删除某一个item * @param filePath */ public void delete(String filePath){ SQLiteDatabase sdb=sqliteHelper.getWritableDatabase(); sdb.execSQL("delete from upload_file where FilePath=?",new Object[]{filePath}); Log.e("tiwolf", "delete: "+filePath ); } /** * 更新某一条数据,这个需要注意符号 * @param contentValues */ public void update(ContentValues contentValues){ SQLiteDatabase sdb=sqliteHelper.getWritableDatabase(); Log.e("tiwolf", "update: "+contentValues.get("FilePath")+"--"+contentValues.get("UploadState") ); // sdb.update("upload_file",contentValues,null,null); //修改SQL语句 String sql="update upload_file set UploadState = "+contentValues.get("UploadState")+",FileSize = '"+contentValues.get("FileSize")+"' where FilePath = '"+contentValues.get("FilePath")+"'"; sdb.execSQL(sql); } /** * 仅仅查询一条数据 * @param fileState * @return */ public String check(String fileState){ SQLiteDatabase sdb=sqliteHelper.getReadableDatabase(); // sdb.execSQL("select FilePath from upload_file where UploadState=?"); //获取FilePath,FileSize 在条件是什么的时候。如果这个缺少,则缺少哪个,下面哪个就会报错 Cursor cursor = sdb.rawQuery("select FilePath,FileSize from upload_file where UploadState=?", new String[]{fileState}); // Cursor cursor = sdb.rawQuery("select FilePath from upload_file where UploadState=?", new String[]{fileState}); String filePath="kong"; // cursor.moveToLast(); if(cursor.moveToLast()){ //一开始我在select 后面写少了FileSize,结果老是报错 //java.lang.IllegalStateException: Couldn't read row 1, col -1 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it. filePath=cursor.getString(cursor.getColumnIndex("FilePath")); String filesize=cursor.getString(cursor.getColumnIndex("FileSize")); filePath=filePath+":"+filesize; } Log.e("tiwolf", "check:"+filePath ); cursor.close(); return filePath; } public List<String> chechAll(String fileSate){ SQLiteDatabase sdb=sqliteHelper.getReadableDatabase(); // vCursor=vReadSQLite.query("t_upload_head",new String[]{"BillID","FilePath","FileSize"},"UploadState=?",new String[]{"0"},null,null,null); Cursor cursor= sdb.query("upload_file",new String[]{"FilePath","FileSize"},"UploadState=?",new String[]{fileSate},null,null,null); List<String> list=new ArrayList<>(); while (cursor.moveToNext()){ Log.e("tiwolf", "chechAll:"+cursor.getString(0)); list.add("路径:"+cursor.getString(cursor.getColumnIndex("FilePath"))+",大小为:"+cursor.getString(cursor.getColumnIndex("FileSize"))); } cursor.close(); return list; } }
3.主界面使用
package com.intefly.xiagaocheng.fileupdownload3; import android.content.ContentValues; import android.os.Bundle; import android.util.Log; import android.view.View; import android.widget.Button; import android.widget.EditText; import android.widget.TextView; import com.hjq.permissions.OnPermission; import com.hjq.permissions.Permission; import com.hjq.permissions.XXPermissions; import com.intefly.xiagaocheng.fileupdownload3.db.SqliteManager; import java.util.List; import androidx.appcompat.app.AppCompatActivity; public class MainActivity extends AppCompatActivity { /** * 动态申请的权限 */ private static final String[] PERMISSIONS = {Permission.READ_EXTERNAL_STORAGE, Permission.WRITE_EXTERNAL_STORAGE,Permission.CAMERA,Permission.RECORD_AUDIO}; SqliteManager sqliteManager; Button addBtn,delBtn,updateBtn,queryBtn,queryAllBtn; EditText inputEt,inputEt1; TextView showTv; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); requestPermission(); sqliteManager=SqliteManager.getInstance(MainActivity.this,"solar",1); addBtn=findViewById(R.id.add); delBtn=findViewById(R.id.delete); updateBtn=findViewById(R.id.update); queryBtn=findViewById(R.id.check); queryAllBtn=findViewById(R.id.check1); inputEt=findViewById(R.id.input); inputEt1=findViewById(R.id.input1); showTv=findViewById(R.id.content); addBtn.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { if (inputEt.getText().toString().length()!=0 && inputEt1.getText().toString().length()!=0){ ContentValues contentValues=new ContentValues(); contentValues.put("FilePath",inputEt.getText().toString()); contentValues.put("UploadState",Integer.valueOf(inputEt1.getText().toString())); sqliteManager.saveItem(contentValues); } } }); delBtn.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { if (inputEt.getText().toString().length()!=0){ sqliteManager.delete(inputEt.getText().toString()); } } }); updateBtn.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { if (inputEt1.getText().toString().length()!=0 && inputEt.getText().toString().length()!=0){ ContentValues contentValues=new ContentValues(); contentValues.put("FilePath",inputEt.getText().toString()); contentValues.put("UploadState",Integer.valueOf(inputEt1.getText().toString())); contentValues.put("FileSize","21453"); sqliteManager.update(contentValues); } } }); queryBtn.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { if (inputEt1.getText().toString().length()!=0){ String get=sqliteManager.check(inputEt1.getText().toString()); showTv.setText(get); } } }); queryAllBtn.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { List<String> stringList = sqliteManager.chechAll(inputEt1.getText().toString()); for (int i = 0; i < stringList.size(); i++) { Log.e("tiwolf", "onClick: "+stringList.get(i) ); } } }); } /** * 请求权限 */ private void requestPermission() { if (!XXPermissions.isHasPermission(MainActivity.this, PERMISSIONS)) { XXPermissions.with(this).constantRequest().permission(PERMISSIONS) .request(new OnPermission() { @Override public void hasPermission(List<String> granted, boolean isAll) { if (isAll) { // ToastUtils.toast(MainActivity.this, "获取权限成功"); Log.e("tiwolf", "获取权限成功"); } else { // ToastUtils.toast(MainActivity.this, "获取权限成功,部分权限未正常授予"); Log.e("tiwolf", "获取权限成功,部分权限未正常授予"); } } @Override public void noPermission(List<String> denied, boolean quick) { if (quick) { // ToastUtils.toast(MainActivity.this, "被永久拒绝授权,请手动授予权限"); Log.e("tiwolf", "被永久拒绝授权,请手动授予权限"); //如果是被永久拒绝就跳转到应用权限系统设置页面 XXPermissions.gotoPermissionSettings(MainActivity.this); } else { // ToastUtils.toast(MainActivity.this, "获取权限失败"); Log.e("tiwolf", "获取权限失败"); } } }); } } }
搞定收工