java如何从sqlite数据库获取数据

Question

我将我的应用程序数据存储在sqlite数据库中，但当我试图获取它时，它不会显示数据。插入时，它显示在sqlite:1中插入了新的xxxxx

但是，当我试图在其他活动中获取数据时，为什么不向我显示数据呢

 public class SQLiteHandler extends SQLiteOpenHelper {

    private static final String TAG = SQLiteHandler.class.getSimpleName();

    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "ttp";

    // Login table name
    private static final String USER = "user";
    private static final String PROFILE = "profile";
    private static final String CONTRIBUTOR = "contributor";
    private static final String MYBOOKINGS = "mybookings";
    private static final String CATEGORIES = "categories";
    private static final String TABLEOFCONTENTS = "tableofcontents";
    private static final String BOOKINGSTATS = "bookingstats";
    private static final String SAVEBOOKINGS = "savebookings";
    private static final String PAGES = "pages";



    private static final String user_id = "id";
    private static final String user_email = "email";
    private static final String user_password = "password";
    private static final String user_status = "status";
    private static final String user_verified = "verified";
    private static final String user_created = "created";
    private static final String user_modified = "modified";
    private static final String user_token = "token";


    // Login Table Columns names
    private static final String profile_id = "p_id";
    private static final String first_name = "firstname";
    private static final String last_name = "lastname";
    private static final String p_bio = "bio";
    private static final String address = "addrs";
    private static final String profile_img = "profile_pic";
    private static final String user_Id = "user_id";

    // Profile Table Columns names

    private static final String contributor_id = "c_id";
    private static final String profile_Id = "p_id";
    private static final String secret_token = "secretToken";
    private static final String c_statement = "statmnt";
    private static final String c_status = "status";
    private static final String c_time = "time";
    private static final String c_uploaded_on = "uploaded_on";
    private static final String vs_cdn_id = "vs_id";
    private static final String created = "create";
    private static final String modified = "modifi";
//    long id;

//    private static final String created = "p_id";
    private static final String description = "desc";
    private static final String id = "cat_id";
    private static final String image = "cat_image";
    private static final String language_id = "lang_id";
//    private static final String modified = "profile_pic";
    private static final String name = "cat_name";
    private static final String parent_id = "prnt_id";

    public SQLiteHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_USER_TABLE = "CREATE TABLE " + USER + "("+user_id + " TEXT," +user_email + " TEXT," +user_password + " TEXT," + user_status + " TEXT,"+ user_verified + " TEXT,"+ user_created + " TEXT," +user_modified + " TEXT,"+user_token + " TEXT"+")";
        db.execSQL(CREATE_USER_TABLE);

        String CREATE_PROFILE_TABLE = "CREATE TABLE " + PROFILE + "("+profile_id + " TEXT," +first_name + " TEXT," +last_name + " TEXT," + p_bio + " TEXT,"+ address + " TEXT,"+ profile_img + " TEXT," +user_Id + " TEXT"+")";
        db.execSQL(CREATE_PROFILE_TABLE);

        String CREATE_CONTRIBUTOR_TABLE = "CREATE TABLE " + CONTRIBUTOR + "("+contributor_id + " TEXT," +profile_Id + " TEXT," +secret_token + " TEXT," + c_statement + " TEXT,"+ c_status + " TEXT,"+ c_time + " TEXT," +c_uploaded_on + " TEXT"+vs_cdn_id + " TEXT"+created + " TEXT"+modified + " TEXT"+")";
        db.execSQL(CREATE_CONTRIBUTOR_TABLE);

        String CREATE_CATEGORIES_TABLE = "CREATE TABLE " + CATEGORIES + "("+description + " TEXT," +id + " TEXT," +image + " TEXT," + language_id + " TEXT,"+ name + " TEXT,"+ parent_id + " TEXT"+")";
        db.execSQL(CREATE_CATEGORIES_TABLE);

        Log.d(TAG, "Database tables created");

    }


    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + USER);
        db.execSQL("DROP TABLE IF EXISTS " + PROFILE);
        db.execSQL("DROP TABLE IF EXISTS " + CONTRIBUTOR);
        // Create tables again
        onCreate(db);
    }

    /**
     * Storing user details in database
     * */
    public void addUser(String id, String email, String password, String status,String verified, String created, String modified, String token) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(user_Id, id); // profileid
        values.put(user_email, email); // firstname
        values.put(user_password,password ); // lastname
        values.put(user_status, status); // bio
        values.put(user_verified, verified); // address
        values.put(user_created, created);//profilepic
        values.put(user_modified, modified);//profilepic
        values.put(user_token, token);//userid
        // Inserting Row
        long user = db.insert(USER, null, values);
        db.close(); // Closing database connection

        Log.d(TAG, "New user inserted into sqlite: " + user);
    }
    public void addProfile(String p_id, String firstname, String lastname, String bio,String addrs, String profile_pic, String user_id) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(profile_id, p_id); // profileid
        values.put(first_name, firstname); // firstname
        values.put(last_name, lastname); // lastname
        values.put(p_bio, bio); // bio
        values.put(address, addrs); // address
        values.put(profile_img, profile_pic);//profilepic
        values.put(user_Id, user_id);//userid
        // Inserting Row
        long profile = db.insert(PROFILE, null, values);
        db.close(); // Closing database connection

        Log.d(TAG, "New profile inserted into sqlite: " + profile);
    }
    public void addContributor(String c_id, String p_id, String secretToken, String statmnt,String status, String time, String uploaded_on,String vs_id,String create,String modifi) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();

        values.put(contributor_id, c_id); // profileid
        values.put(profile_Id, p_id); // firstname
        values.put(secret_token, secretToken); // lastname
        values.put(c_statement, statmnt); // bio
        values.put(c_status, status); // address
        values.put(c_time, time);//profilepic
        values.put(c_uploaded_on, uploaded_on);//userid
        values.put(vs_cdn_id, vs_id);//userid
        values.put(created, create);//userid
        values.put(modified, modifi);//userid
        // Inserting Row
        long Id = db.insert(CONTRIBUTOR, null, values);
        db.close(); // Closing database connection

        Log.d(TAG, "New contributor inserted into sqlite: " + Id);
    }
    public void addCategories(String desc, String cat_id, String cat_image, String lang_id,String cat_name, String prnt_id) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(description, desc); // profileid
        values.put(id, cat_id); // firstname
        values.put(image, cat_image); // lastname
        values.put(language_id , lang_id); // bio
        values.put(name, cat_name); // address
        values.put(parent_id, prnt_id);//profilepic
         // Inserting Row
        long categories = db.insert(CATEGORIES, null, values);
        db.close(); // Closing database connection

        Log.d(TAG, "New Categories inserted into sqlite: " + categories);
    }

     /**
     * Getting user data from database
     * */
    public HashMap<String, String> getUserDetails() {
        HashMap<String, String> user = new HashMap<String, String>();
        String selectQuery = "SELECT  * FROM " + USER;

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // Move to first row
        cursor.moveToFirst();
        if (cursor.getCount() > 0) {
            user.put("user", cursor.getString(1));
            user.put("first_name", cursor.getString(2));
            user.put("last_name", cursor.getString(3));
            user.put("p_bio", cursor.getString(4));
            user.put("address", cursor.getString(5));
            user.put("profile_pic", cursor.getString(6));
            user.put("user_id", cursor.getString(7));
        }
        cursor.close();
        db.close();
        // return user
        Log.d(TAG, "Fetching user from Sqlite: " + user.toString());

        return user;
    }
    public HashMap<String, String> getProfileDetails() {
        HashMap<String, String> profile = new HashMap<String, String>();
        String selectQuery = "SELECT  * FROM " + PROFILE;

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // Move to first row
        cursor.moveToFirst();
        if (cursor.getCount() > 0) {
            profile.put("profile_id", cursor.getString(1));
            profile.put("first_name", cursor.getString(2));
            profile.put("last_name", cursor.getString(3));
            profile.put("bio", cursor.getString(4));
            profile.put("address", cursor.getString(5));
            profile.put("profile_pic", cursor.getString(6));
            profile.put("user_id", cursor.getString(7));
        }
        Log.d(TAG, "Fetching user from Sqlite: " + profile.toString());
        cursor.close();
        db.close();
        // return user


        return profile;
    }
    public HashMap<String, String> getContributorDetails() {
        HashMap<String, String> contributor = new HashMap<String, String>();
        String selectQuery = "SELECT  * FROM " + CONTRIBUTOR;

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // Move to first row
        cursor.moveToFirst();
        if (cursor.getCount() > 0) {
            contributor.put("contributor_id", cursor.getString(1));
            contributor.put("profile_Id", cursor.getString(2));
            contributor.put("secret_token", cursor.getString(3));
            contributor.put("c_statement", cursor.getString(4));
            contributor.put("c_status", cursor.getString(5));
            contributor.put("c_time", cursor.getString(6));
            contributor.put("c_uploaded_on", cursor.getString(7));
            contributor.put("vs_cdn_id", cursor.getString(8));
            contributor.put("created", cursor.getString(9));
            contributor.put("modified", cursor.getString(10));
        }
        cursor.close();
        db.close();
        // return user
        Log.d(TAG, "Fetching user from Sqlite: " + contributor.toString());

        return contributor;
    }

Saving Profile:

     if (String.valueOf(code).equals("200")) {

                                    JSONObject Profile = json.getJSONObject("profile").getJSONObject("Profile");
                                    p_id = Profile.getString("id");
                                    firstname = Profile.getString("first_name");
                                    String lastname = Profile.getString("last_name");
                                    String bio = Profile.getString("bio");
                                    String addrs = Profile.getString("address");
                                    String profile_pic = Profile.getString("profile_pic");
                                    String user_id = Profile.getString("user_id");
                                    session.createLoginSession(user_email,user_Id);
                                    session.addProfileSession(p_id,firstname);
                                    HashMap<String, String> user = session.getUserDetails();
                                    user_email = user.get(SessionManager.KEY_EMAIL);
                                    Log.d(TAG,"email Check :"+user_email);

                                    db.addProfile(p_id, firstname, lastname, bio, addrs, profile_pic, user_id);

                                    ByteArrayOutputStream stream = new ByteArrayOutputStream();
                                    bitmap.compress(Bitmap.CompressFormat.JPEG, 100, stream);
                                    byteArray = stream.toByteArray();
                                    Intent i = new Intent(UserProfile.this, ContributorProfile.class);
                                    Bundle bundle = new Bundle();
                                    bundle.putString("FIRST_NAME", firstname);
                                    bundle.putString("P_ID",p_id);
                                    bundle.putByteArray("image",byteArray);
                                    i.putExtras(bundle);
                                    startActivity(i);
                                    finish();

Saving contributor:

      public void onResponse(String response) {
                            Log.d(TAG, "Audio Upload Response Check :" + response);

                            Log.d(TAG,"Object Check :"+response);
                            try {
                                JSONObject json = new JSONObject(response).getJSONObject("contributor").getJSONObject("Contributor");
                                contributor_id = json.getString("id");
                                String profile_id = json.getString("profile_id");
                                String secret_token = json.getString("secret_token");
                                String statement = json.getString("statement");
                                String status = json.getString("status");
                                String time = json.getString("time");
                                String uploaded_on = json.getString("uploaded_on");
                                String vs_cdn_id = json.getString("vs_cdn_id");
                                String created = json.getString("created");
                                String modified = json.getString("modified");
                                contributor_id = json.getString("id");
                                session.addContributorSession(contributor_id);
                                db.addContributor(contributor_id, profile_id, secret_token, statement, status, time, uploaded_on, vs_cdn_id, created, modified);


                            } catch (JSONException e) {
                                e.printStackTrace();
                            }

保存类别：

final ProgressDialog loading = ProgressDialog.show(this,"Loading Data", "Please wait...",false,false);
        JsonObjectRequest jsonObjectRequest = new JsonObjectRequest(Config.DATA_URL, null, new Response.Listener<JSONObject>() {
            @Override
            public void onResponse(JSONObject response) {
                loading.dismiss();
                if (response != null) {
                    ObjectMapper mapper = new ObjectMapper();
                    try {
                        String categoriesJsonString = response.getJSONArray("categories").toString();
                        categorys = mapper.readValue(categoriesJsonString, TypeFactory.defaultInstance().constructCollectionType(List.class,
                                Categorys.class));
                        Log.d(TAG,"Reponse Check :"+categorys.toString());

                        for(Categorys category : categorys) {
                            listCategories.add(category.getCategory());
                            db.addCategories(category.getCategory().getDescription(),category.getCategory().getId(),category.getCategory().getImage(),category.getCategory().getLanguage_id(),category.getCategory().getName(),category.getCategory().getParent_id());
                        }
                        Log.d(TAG,"Reponse Check2 :"+listCategories.toString());
                        adapter = new CardAdapter(listCategories, that);
                        recyclerView.setAdapter(adapter);

日志类别：

 Database tables created
05-26 16:06:54.036 8743-8743/com.showhow2.www.thetagoreproject D/SQLiteHandler: Fetching profile from Sqlite: {}
05-26 16:06:54.086 8743-8821/com.showhow2.www.thetagoreproject I/MaliEGL: [Mali]window_type=1, is_framebuffer=0, errnum = 0
05-26 16:06:54.086 8743-8821/com.showhow2.www.thetagoreproject I/MaliEGL: [Mali]surface->num_buffers=4, surface->num_frames=3, win_min_undequeued=1
05-26 16:06:54.086 8743-8821/com.showhow2.www.thetagoreproject I/MaliEGL: [Mali]max_allowed_dequeued_buffers=3

以下是我尝试获取配置文件数据的方式：

db = new SQLiteHandler(getApplicationContext());
        HashMap<String, String> profile=db.getProfileDetails();
        for(int i=0; i<profile.size();i++){
            String f_name = profile.get("first_name").toString();
            String l_name = profile.get("last_name");
            String desc = profile.get("bio");
            String adrs = profile.get("address");
            String phn = profile.get("profile_pic");
            Log.d(TAG,"f_name :"+ f_name);
            Log.d(TAG,"f_name :"+ l_name);
            Log.d(TAG,"f_name :"+ desc);
            firstName.setText(f_name);
            lastName.setText(l_name);
            description.setText(desc);
            address.setText(adrs);
            phone.setText(phn);
        }

Answer 1

您可以使用content resolver将内容移动到db。 下面是一个例子

`

package com.example.MyApplication;
  import java.util.HashMap;

import android.content.ContentProvider;
import android.content.ContentUris;
import android.content.ContentValues;
import android.content.Context;
import android.content.UriMatcher;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;

import android.net.Uri;
import android.text.TextUtils;

public class StudentsProvider extends ContentProvider {
   static final String PROVIDER_NAME = "com.example.MyApplication.StudentsProvider";
   static final String URL = "content://" + PROVIDER_NAME + "/students";
   static final Uri CONTENT_URI = Uri.parse(URL);

   static final String _ID = "_id";
   static final String NAME = "name";
   static final String GRADE = "grade";

   private static HashMap<String, String> STUDENTS_PROJECTION_MAP;

   static final int STUDENTS = 1;
   static final int STUDENT_ID = 2;

   static final UriMatcher uriMatcher;
   static{
      uriMatcher = new UriMatcher(UriMatcher.NO_MATCH);
      uriMatcher.addURI(PROVIDER_NAME, "students", STUDENTS);
      uriMatcher.addURI(PROVIDER_NAME, "students/#", STUDENT_ID);
   }

   /**
      * Database specific constant declarations
   */

   private SQLiteDatabase db;
   static final String DATABASE_NAME = "College";
   static final String STUDENTS_TABLE_NAME = "students";
   static final int DATABASE_VERSION = 1;
   static final String CREATE_DB_TABLE =
      " CREATE TABLE " + STUDENTS_TABLE_NAME +
         " (_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
         " name TEXT NOT NULL, " +
         " grade TEXT NOT NULL);";

   /**
      * Helper class that actually creates and manages
      * the provider's underlying data repository.
   */

   private static class DatabaseHelper extends SQLiteOpenHelper {
      DatabaseHelper(Context context){
         super(context, DATABASE_NAME, null, DATABASE_VERSION);
      }

      @Override
      public void onCreate(SQLiteDatabase db) {
         db.execSQL(CREATE_DB_TABLE);
      }

      @Override
      public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
         db.execSQL("DROP TABLE IF EXISTS " +  STUDENTS_TABLE_NAME);
         onCreate(db);
      }
   }

   @Override
   public boolean onCreate() {
      Context context = getContext();
      DatabaseHelper dbHelper = new DatabaseHelper(context);

      /**
         * Create a write able database which will trigger its
         * creation if it doesn't already exist.
      */

      db = dbHelper.getWritableDatabase();
      return (db == null)? false:true;
   }

   @Override
   public Uri insert(Uri uri, ContentValues values) {
      /**
         * Add a new student record
      */
      long rowID = db.insert(   STUDENTS_TABLE_NAME, "", values);

      /**
         * If record is added successfully
      */
      if (rowID > 0) {
         Uri _uri = ContentUris.withAppendedId(CONTENT_URI, rowID);
         getContext().getContentResolver().notifyChange(_uri, null);
         return _uri;
      }

      throw new SQLException("Failed to add a record into " + uri);
   }

   @Override
   public Cursor query(Uri uri, String[] projection, 
      String selection,String[] selectionArgs, String sortOrder) {
      SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
      qb.setTables(STUDENTS_TABLE_NAME);

      switch (uriMatcher.match(uri)) {
         case STUDENTS:
            qb.setProjectionMap(STUDENTS_PROJECTION_MAP);
         break;

         case STUDENT_ID:
            qb.appendWhere( _ID + "=" + uri.getPathSegments().get(1));
         break;

         default:   
      }

      if (sortOrder == null || sortOrder == ""){
         /**
            * By default sort on student names
         */
         sortOrder = NAME;
      }

      Cursor c = qb.query(db,   projection, selection, 
         selectionArgs,null, null, sortOrder);
      /**
         * register to watch a content URI for changes
      */
      c.setNotificationUri(getContext().getContentResolver(), uri);
      return c;
   }

   @Override
   public int delete(Uri uri, String selection, String[] selectionArgs) {
      int count = 0;
      switch (uriMatcher.match(uri)){
         case STUDENTS:
            count = db.delete(STUDENTS_TABLE_NAME, selection, selectionArgs);
         break;

         case STUDENT_ID:
            String id = uri.getPathSegments().get(1);
            count = db.delete( STUDENTS_TABLE_NAME, _ID +  " = " + id +
               (!TextUtils.isEmpty(selection) ? " 
               AND (" + selection + ')' : ""), selectionArgs);
            break;
         default:
            throw new IllegalArgumentException("Unknown URI " + uri);
      }

      getContext().getContentResolver().notifyChange(uri, null);
      return count;
   }

   @Override
   public int update(Uri uri, ContentValues values, 
      String selection, String[] selectionArgs) {
      int count = 0;
      switch (uriMatcher.match(uri)) {
         case STUDENTS:
            count = db.update(STUDENTS_TABLE_NAME, values, selection, selectionArgs);
         break;

         case STUDENT_ID:
            count = db.update(STUDENTS_TABLE_NAME, values, 
               _ID + " = " + uri.getPathSegments().get(1) +
               (!TextUtils.isEmpty(selection) ? " 
               AND (" +selection + ')' : ""), selectionArgs);
            break;
         default:
            throw new IllegalArgumentException("Unknown URI " + uri );
      }

      getContext().getContentResolver().notifyChange(uri, null);
      return count;
   }

   @Override
   public String getType(Uri uri) {
      switch (uriMatcher.match(uri)){
         /**
            * Get all student records
         */
         case STUDENTS:
            return "vnd.android.cursor.dir/vnd.example.students";
         /**
            * Get a particular student
         */
         case STUDENT_ID:
            return "vnd.android.cursor.item/vnd.example.students";
         default:
            throw new IllegalArgumentException("Unsupported URI: " + uri);
      }
   }

`

public class MainActivity extends Activity {

   @Override
   protected void onCreate(Bundle savedInstanceState) {
      super.onCreate(savedInstanceState);
      setContentView(R.layout.activity_main);
   }
   public void onClickAddName(View view) {
      // Add a new student record
      ContentValues values = new ContentValues();
      values.put(StudentsProvider.NAME,
         ((EditText)findViewById(R.id.editText2)).getText().toString());

      values.put(StudentsProvider.GRADE,
         ((EditText)findViewById(R.id.editText3)).getText().toString());

      Uri uri = getContentResolver().insert(
         StudentsProvider.CONTENT_URI, values);

      Toast.makeText(getBaseContext(),
         uri.toString(), Toast.LENGTH_LONG).show();
   }
   public void onClickRetrieveStudents(View view) {
      // Retrieve student records
      String URL = "content://com.example.MyApplication.StudentsProvider";

      Uri students = Uri.parse(URL);
      Cursor c = managedQuery(students, null, null, null, "name");

      if (c.moveToFirst()) {
         do{
            Toast.makeText(this,
               c.getString(c.getColumnIndex(StudentsProvider._ID)) +
                  ", " +  c.getString(c.getColumnIndex( StudentsProvider.NAME)) +
                     ", " + c.getString(c.getColumnIndex( StudentsProvider.GRADE)),
            Toast.LENGTH_SHORT).show();
         } while (c.moveToNext());
      }
   }

}

Answer 2

试试这个。。。希望能有所帮助 FileName是我用自定义适配器填充的模型

    public List<FileName> getItemFromDatabase(SQLiteDatabase sqLiteDatabase) {
    List<FileName> result = new ArrayList<>();
    Cursor c = sqLiteDatabase.rawQuery("SELECT * FROM "+Coloumn.NewInfo.TABLE_NAME,null);
    while (c.moveToNext()) {
        result.add(
                new FileName(
                        c.getString(c.getColumnIndex("Name")),
                        c.getString(c.getColumnIndex("Note"))
                )
        );
    }
    c.close();
    return result;
}