java - 如何在另一个 Activity 的 GraphView 中将 SQLite 数据库中的日期显示为 X 轴，仅显示 1-1-1970？

Question

我构建了一个应用程序，它在 MainActivity.java 中执行一些计算并将结果存储在 SQLite 数据库中。当用户按下按钮时，将打开带有相应 GraphView 的 GraphActivity.java。也许是因为我对 SQLite 数据库还没有太多经验，但我无法让图表的 X 轴显示每个结果的存储日期。

那么，考虑到 Insert 方法和 Graph 发生在单独的 Activity 中，我如何才能使存储的日期在 GraphActivity 中显示为 X 轴？

我按照 GraphView 日期和 SQLite 的 YouTube 教程进行操作，但它在同一 Activity 中具有存储和图表。 (https://www.youtube.com/watch?v=lSgK6-cKjmA&list=PLFh8wpMiEi88ojfNpavGpMB0dtP4mvEqa&index=16)

我尝试使用 Intent EXTRAS 发送日期变量，但当按下“保存”按钮时，它会强制您进入 GraphActivity。然后我尝试在 GraphActivity 中获取时间，它可以工作，但每个结果的 X 轴日期显示为连续的“1-1-1970”。我使用格式来显示带有时间戳的当前日期，但它仍然不显示每个结果的存储日期，仅显示所有结果的当前日期。

MainActivity.java:结果和日期的插入方法

public void insertData() {
    try {
        (...)

        // Gets the time & date the results are stored

        String timeStamp = new SimpleDateFormat("d M yy hh:mm").format(Calendar.getInstance().getTime());

        // Create a ContentValues object where column names are the keys,
        // and container attributes from the MainActivity are the values.

        ContentValues values = new ContentValues();
        values.put(DexameniEntry.COLUMN_CONTAINER_NAME, "TODO");
        values.put(DexameniEntry.COLUMN_a, X1aInput);
        values.put(DexameniEntry.COLUMN_DATE, timeStamp);
        values.put(DexameniEntry.COLUMN_b, X1bInput);
        values.put(DexameniEntry.COLUMN_c, X1cInput);
        values.put(DexameniEntry.COLUMN_d, dX1StringDouble);
        values.put(DexameniEntry.COLUMN_e, eX1StringDouble);
        values.put(DexameniEntry.COLUMN_percent, percentX1fromDoubletoString);

        (...)
        } catch (NumberFormatException e) {

        } catch (NullPointerException e) {
        }
}

GraphActivity.java:设置GraphView

public class GraphActivity extends AppCompatActivity { 
    (...)
    SimpleDateFormat sdf = new SimpleDateFormat("EEE d M yyyy h mm", Locale.US);

    @Override
    protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.graph_activity);

    GraphView bigGraphX1 = findViewById(R.id.bigGraph);
    mDbHelper = new ResultsDbHelper(this);
    sqLiteDatabase = mDbHelper.getWritableDatabase();

    bigGraphX1.getGridLabelRenderer().setLabelFormatter(new DefaultLabelFormatter() {

        @Override
        public String formatLabel(double value, boolean isValueX) {
            if (isValueX) {
                return
                        sdf.format(new Date((long)value));
            }else {
                return super.formatLabel(value, isValueX);
            }
        }
    });

    (...)

编辑DbHelper.class(名为 ResultsDbHelper)

public class ResultsDbHelper extends SQLiteOpenHelper {

   public static final String LOG_TAG = ResultsDbHelper.class.getSimpleName();

//    Name of the database in a String type constant (to be referenced later)
private static final String DATABASE_NAME = "containerResults.db";
//    Database version in a Integer type constant for updating the database
private static final int DATABASE_VERSION = 1;

//    Constructor: Constructs a new instance of ResultsDbHelper.
//     * @param is the context of the app

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

//    Modify the onCreate method to have the table created on startup of the app
@Override
public void onCreate(SQLiteDatabase db) {

// Define the schema of the table: which columns will be created by the onCreate method.
//        The whole schema creation is put in a String variable (CREATE_RESULTS_TABLE) for easy reference

    String CREATE_RESULTS_TABLE = "CREATE TABLE " + DexameniEntry.TABLE_NAME + " ("
            + DexameniEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + DexameniEntry.COLUMN_CONTAINER_NAME + " TEXT NOT NULL, "
            + DexameniEntry.COLUMN_DATE + " TEXT NOT NULL, "
            + DexameniEntry.COLUMN_a + " REAL NOT NULL, "
            + DexameniEntry.COLUMN_b + " REAL NOT NULL, "
            + DexameniEntry.COLUMN_c + " REAL NOT NULL, "
            + DexameniEntry.COLUMN_d + " REAL NOT NULL, "
            + DexameniEntry.COLUMN_e + " REAL NOT NULL, "
            + DexameniEntry.COLUMN_percent + " REAL NOT NULL);";

//        Execute the SQL Statement (Create the table)
    db.execSQL(CREATE_RESULTS_TABLE);
}

/**
 * This is called when the database needs to be upgraded.
 */
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    final String LOG_TAG = ("Upgrading the database from version " + oldVersion + " to " + newVersion);
    // on upgrade drop older tables
    db.execSQL("DROP TABLE IF EXISTS " + DexameniEntry.TABLE_NAME);

    //        Recreate tables
    onCreate(db);
    }
}

我希望 X 轴显示每个结果的存储日期，以便所有值在 GraphView 中显示为 LineSeries。但我似乎无法将日期从数据库获取到图表，因此所有日期始终相同，并且 LineSeries 不连贯。

编辑:在发布问题之前，我还开始寻找一种方法，在 FormatLabel 方法内将 DATE 列的数据库引用放入 GraphActivity 中。例如

@Override
    public String formatLabel(double value, boolean isValueX) {
        if (isValueX) {
            return
             sdf.format(new Date(**put here the DATE column variable so that the X-axis is populated the column's data**);
        }else {
            return super.formatLabel(value, isValueX);
        }

我也无法做到这一点，因为我不知道如何编写这段代码......

编辑:Picture of the issue

我不知道还能做什么来解决这个问题，你能帮我吗？

编辑2:GraphActivity after changes (看起来图表仍然没有由数据库数据填充。也许将数据保存在数据库中存在问题？idk..)

Answer 1

您试图在 MainActivity 中执行太多操作，并且您可能遇到日期问题，因为从您的评论中可以看出，它们/它是 0。

基本上，您可能希望传递生成图表的条件(例如图表的日期范围)，然后通过 Databasehelper (ResultsHelper) 中编码的方法从数据库中提取数据。

而不是在添加数据时获取当前时间并以 D M Y 格式存储到数据库中。您可以通过以可排序的格式存储日期以及以 SQLite 友好的格式(主要是 YYYY-MM-DD HH:MM:SS)操作/格式化日期来简单地处理问题(例如根据日期范围提取数据)(尽管您可以简单地存储时间戳)。

因此，我建议进行一些相对简单的更改。

1. 将日期列定义更改为:-

   + DexameniEntry.COLUMN_DATE + " TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, " //<<<<<<<<<< LET SQLITE GET THE DATE TIME

   • 如果您只是不提供值，这允许 SQLite 存储当前日期和时间，这是通过注释掉/删除行来完成的:- //values.put(DexameniEntry.COLUMN_DATE, timeStamp); //<<<<<<<<<< Use default

2. 将 insertData 方法移至 ResultHeper 中(然后可以在其他地方使用)

3. 向 ResultHelper 添加一个方法，将数据提取到 Cursor 中，包括动态生成列。

   • 动态列是动态生成的附加列，告诉 SQLite 执行操作。
   • 例如假设您想添加 2 列 a 和 b，您可以说 SELECT *, a + b AS my_addition FROM the_table;结果将包括所有列以及 my_addition 列。

4. 将提取图形数据的基本/最低选择标准从 MainActivity 传递到 GraphActivity，然后提取 graphActivity 中的实际数据。

注释已更改为使用日期标签填充图表。

工作示例

以下代码基于您的代码和上述几点，是一个工作示例(它不会填充图表，但会获取数据)。

DexameniEntry.java

public class DexameniEntry {

    public static final String[] WEEKDAYS_LONG = new String[]{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"};
    public static final String[] WEEKDAYS_SHORT = new String[]{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"};

    public static final String _ID = BaseColumns._ID;
    public static final String TABLE_NAME = "container_results";
    public static final String COLUMN_CONTAINER_NAME = "container_name";
    public static final String COLUMN_DATE = "container_date";
    public static final String COLUMN_a = "container_value_a";
    public static final String COLUMN_b = "container_value_b";
    public static final String COLUMN_c = "container_value_c";
    public static final String COLUMN_d = "container_value_d";
    public static final String COLUMN_e = "container_value_e";
    public static final String COLUMN_percent = "container_percent";


    public static final String EXTRACT_COLUMN_DATELABEL = "date_label";
    public static final String EXTRACT_COLUMN_GRAPHDATELABEL = "graph_date_label";

    public static final String INTENTKEY_GRAPHVALUE = "ik_graph_value";
}

• 这主要由猜测工作(列名和表名)组成。但请注意以下两个常量(可以使用 WEEKDAYS_?? 常量)
  • EXTRACT_COLUMN_DATELABEL(动态生成的列名称)和
  • EXTRACT_COLUMN_GRAPHDATELABEL(动态生成的 unix 时间戳)
  • INTENTKEY_GRAPHVALUE(用于传递意图值)

结果助手

public class ResultsDbHelper extends SQLiteOpenHelper {

    public static final String LOG_TAG = ResultsDbHelper.class.getSimpleName();

    // Date format that suits SQLite i.e. YYYY-MM-DD HH:MM:SS
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); // Not used but could be useful

    //    Name of the database in a String type constant (to be referenced later)
    private static final String DATABASE_NAME = "containerResults.db";
    //    Database version in a Integer type constant for updating the database
    private static final int DATABASE_VERSION = 1;

    SQLiteDatabase mDB;

//    Constructor: Constructs a new instance of ResultsDbHelper.
//     * @param is the context of the app

    public ResultsDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        mDB = this.getWritableDatabase(); // Sets mDB (also forces open of DB)
    }

    //    Modify the onCreate method to have the table created on startup of the app
    @Override
    public void onCreate(SQLiteDatabase db) {

// Define the schema of the table: which columns will be created by the onCreate method.
//        The whole schema creation is put in a String variable (CREATE_RESULTS_TABLE) for easy reference

        String CREATE_RESULTS_TABLE = "CREATE TABLE " + DexameniEntry.TABLE_NAME + " ("
                + DexameniEntry._ID + " INTEGER PRIMARY KEY, " //<<<<<<<<<< AUTOINCREMENT NOT REQD AS IS INEFFICIENT
                + DexameniEntry.COLUMN_CONTAINER_NAME + " TEXT NOT NULL, "
                + DexameniEntry.COLUMN_DATE + " TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, " //<<<<<<<<<< LET SQLITE GET THE DATE TIME
                + DexameniEntry.COLUMN_a + " REAL NOT NULL, "
                + DexameniEntry.COLUMN_b + " REAL NOT NULL, "
                + DexameniEntry.COLUMN_c + " REAL NOT NULL, "
                + DexameniEntry.COLUMN_d + " REAL NOT NULL, "
                + DexameniEntry.COLUMN_e + " REAL NOT NULL, "
                + DexameniEntry.COLUMN_percent + " REAL NOT NULL);";

//        Execute the SQL Statement (Create the table)
        db.execSQL(CREATE_RESULTS_TABLE);
    }

    /**
     * This is called when the database needs to be upgraded.
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        final String LOG_TAG = ("Upgrading the database from version " + oldVersion + " to " + newVersion);
        // on upgrade drop older tables
        db.execSQL("DROP TABLE IF EXISTS " + DexameniEntry.TABLE_NAME);

        //        Recreate tables
        onCreate(db);
    }

    public void insertData(String name,
                           Double X1aInput,
                           Double X1bInput,
                           Double X1cInput,
                           Double X1dInput,
                           Double X1eInput,
                           Double percent) {

        String timeStamp = "";
        try {
            // Gets the time & date the results are stored
            //timeStamp = new SimpleDateFormat("d M yy hh:mm").format(Calendar.getInstance().getTime());
            timeStamp = sdf.format(Calendar.getInstance().getTime());

        } catch (NumberFormatException e) {

        } catch (NullPointerException e) {
        }

        // Create a ContentValues object where column names are the keys,
        // and container attributes from the MainActivity are the values.

        ContentValues values = new ContentValues();
        values.put(DexameniEntry.COLUMN_CONTAINER_NAME, name);
        values.put(DexameniEntry.COLUMN_a, X1aInput);
        //values.put(DexameniEntry.COLUMN_DATE, timeStamp); //<<<<<<<<<< Use default
        values.put(DexameniEntry.COLUMN_b, X1bInput);
        values.put(DexameniEntry.COLUMN_c, X1cInput);
        values.put(DexameniEntry.COLUMN_d, X1dInput);
        values.put(DexameniEntry.COLUMN_e, X1eInput);
        values.put(DexameniEntry.COLUMN_percent, percent);
        mDB.insert(DexameniEntry.TABLE_NAME,null,values);
    }

    public Cursor getAllResults() {
        String formatted_date = "strftime('%d %m %Y'," +
                DexameniEntry.COLUMN_DATE + ") AS " + DexameniEntry.EXTRACT_COLUMN_DATELABEL;
        String unixtype_date = "julianday(" + DexameniEntry.COLUMN_DATE + ") * 86400 * 1000 AS " +DexameniEntry.EXTRACT_COLUMN_GRAPHDATELABEL;
        String[] columns = new String[]{"*",formatted_date,unixtype_date};
        return mDB.query(DexameniEntry.TABLE_NAME,columns,null,null,null,null,DexameniEntry.COLUMN_DATE + " ASC");
    }
}

• 日期列已更改为具有特殊的 DEFAULT 值 CURRENT_TIMESTAMP
• insertData 方法已从 MainActivity 移至此处，现在需要向其传递参数(请参阅它在 MainActivity 中的使用情况)
• 已添加 getAllResults(请参阅 GrapActivty 了解 getAllreults 的使用位置)
  • 请注意，生成的 Cursor 包含表中的所有列加上两个额外的(动态生成的)列，这些列将具有 DD MM YYYY 格式的日期(日期列将具有 YYYY-DD-MM HH:MM:SS 格式的日期(稍后的示例输出))，另一个作为 unix 时间戳。 (julianday(the_column) * 86400 * 1000 AS the_column_name)

MainActivity.java

MainActivity 加载一些数据(仅在第一次运行时)，并有一个按钮，单击该按钮将启动 GraphActivity。

public class MainActivity extends AppCompatActivity {

    Button mShowGraph;
    Context mContext;
    ResultsDbHelper mDBHlpr;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mContext = this;
        mShowGraph = this.findViewById(R.id.show_graph);
        setupShowGraphButton();

        //Instantiate the DatabaseHelper
        mDBHlpr = new ResultsDbHelper(this);

        // Add some data if none exists
        if (DatabaseUtils.queryNumEntries(mDBHlpr.getWritableDatabase(),DexameniEntry.TABLE_NAME) < 1) {
            mDBHlpr.insertData("C1", 10.12, 11.12, 12.12, 13.12, 14.12, 25D);
            mDBHlpr.insertData("C2", 20.12, 21.12, 22.12, 23.12, 24.12, 35D);
            mDBHlpr.insertData("C3", 31.12, 32.12, 33.12, 34.12, 35.12, 25D);
        }
    }

    // Setup the Buttons on click listener to start the GraphActivity
    private void setupShowGraphButton() {
        mShowGraph.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Intent i  = new Intent(mContext,GraphActivity.class);
                i.putExtra(DexameniEntry.INTENTKEY_GRAPHVALUE,100);
                startActivity(i);
            }
        });
    }
}

图形 Activity

虽然这显示了图表，但它不加载任何数据，而是从数据库中提取日期并将其写入日志。

public class GraphActivity extends AppCompatActivity {

    Context mContext;
    Button mDone;
    GraphView mBigGraphX1;
    ResultsDbHelper mDBHlpr;
    Cursor mResults;
    LineGraphSeries<DataPoint> mLGS;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_graph);
        mContext = this;
        mDone = this.findViewById(R.id.done);
        setupDoneButton();
        mDBHlpr = new ResultsDbHelper(this);
        mResults = mDBHlpr.getAllResults();
        mBigGraphX1 = this.findViewById(R.id.graphview);
        demoDatesFromDB();
        doTheGraphLineStuff();
    }

    private void setupDoneButton() {
        mDone.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                finish();
            }
        });
    }

    @Override
    protected void onDestroy() {
        mResults.close(); //<<<<<<<<<< Should always close cursor when done
        super.onDestroy();
    }

    private void demoDatesFromDB() {
        StringBuilder sb = new StringBuilder("Data From Table ").append(DexameniEntry.TABLE_NAME).append(" as per :-");
        while (mResults.moveToNext()) {
            String date_asperdb = mResults.getString(mResults.getColumnIndex(DexameniEntry.COLUMN_DATE));
            String date_formatted = mResults.getString(mResults.getColumnIndex(DexameniEntry.EXTRACT_COLUMN_DATELABEL));
            long unix_date = mResults.getLong(mResults.getColumnIndex(DexameniEntry.EXTRACT_COLUMN_GRAPHDATELABEL));
            sb.append("\n\tResults for row ");
            sb.append(String.valueOf(mResults.getPosition() + 1));
            sb.append(" Date as per DB is ").append(date_asperdb);
            sb.append(" Formatted Date is ").append(date_formatted);
            sb.append(" Unix Date is ").append(unix_date);
        }
        Log.d("DEMODATEINFO",sb.toString());
    }

    private void doTheGraphLineStuff() {

        long one_day = 1000 * 60 * 60 * 24;
        mResults = mDBHlpr.getAllResults();
        int rowcount = mResults.getCount();


        long[] extracted_dates = new long[rowcount];

        DataPoint[] dataPoints = new DataPoint[rowcount];
        while (mResults.moveToNext()) {

            /**
             * NOTE  as all 3 rows are added within milliseconds this adds on a day
             */
            extracted_dates[mResults.getPosition()] = mResults.getLong(
                    mResults.getColumnIndex(
                            DexameniEntry.EXTRACT_COLUMN_GRAPHDATELABEL
                    ))
                    + (one_day * ((long)mResults.getPosition() + 1L) //<<<< Frig the data show it is one day more than the previous

            );
            dataPoints[mResults.getPosition()] = new DataPoint(
                    extracted_dates[mResults.getPosition()],
                    mResults.getDouble(mResults.getColumnIndex(DexameniEntry.COLUMN_a))
            );

        }
        mLGS = new LineGraphSeries<>(dataPoints);
        mBigGraphX1.addSeries(mLGS);
        mBigGraphX1.getGridLabelRenderer().setLabelFormatter(new DateAsXAxisLabelFormatter(mContext));
        mBigGraphX1.getGridLabelRenderer().setNumHorizontalLabels(rowcount);
        mBigGraphX1.getViewport().setMinX(extracted_dates[0]);
        mBigGraphX1.getViewport().setMaxX(extracted_dates[rowcount - 1]);
        mBigGraphX1.getViewport().setXAxisBoundsManual(true);
        mBigGraphX1.getGridLabelRenderer().setHumanRounding(false);

    }

结果

以下内容输出到日志:-

2019-01-12 09:01:07.194 3073-3073/so54140390.so54140390graph D/DEMODATEINFO: Data From Table container_results as per :-
        Results for row 1 Date as per DB is 2019-01-11 21:02:06 Formatted Date is 11 01 2019
        Results for row 2 Date as per DB is 2019-01-11 21:02:06 Formatted Date is 11 01 2019
        Results for row 3 Date as per DB is 2019-01-11 21:02:06 Formatted Date is 11 01 2019

因此，这只是更改或添加格式以适应的问题。- 这方面的圣经是SQL As Understood By SQLite- Date And Time Functions

上面的图表:-