· 8 years ago · Aug 10, 2018, 02:02 PM
1Android: timestamp in database
2public class MessagesDBAdapter {
3
4 public static final String KEY_RECIPIENT = "recipient";
5 public static final String KEY_MESSAGE = "message";
6 public static final String KEY_TIME = "time";
7 public static final String KEY_ROWID = "_id";
8
9 private static final String TAG = "MessagesDBAdapter";
10 private DatabaseHelper mDbHelper;
11 private SQLiteDatabase mDb;
12
13 //Database creation sql statement
14
15 private static final String DATABASE_CREATE =
16 "create table notes (" + KEY_ROWID + " integer primary key autoincrement, "
17 + KEY_RECIPIENT + " text not null, " + KEY_MESSAGE + " text not null, " + KEY_TIME + " text not null,);";
18
19 private static final String DATABASE_NAME = "data";
20 private static final String DATABASE_TABLE = "notes";
21 private static final int DATABASE_VERSION = 2;
22 public final String KEY_TIMESTAMP = "timeStamp";
23
24
25 private final Context mCtx;
26
27 private static class DatabaseHelper extends SQLiteOpenHelper {
28
29 DatabaseHelper(Context context) {
30 super(context, DATABASE_NAME, null, DATABASE_VERSION);
31 }
32
33 @Override
34 public void onCreate(SQLiteDatabase db) {
35 db.execSQL(DATABASE_CREATE);
36 }
37
38 @Override
39 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
40 Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
41 + newVersion + ", which will destroy all old data");
42 db.execSQL("DROP TABLE IF EXISTS notes");
43 onCreate(db);
44 }
45 }
46
47 /**
48 * Constructor - takes the context to allow the database to be
49 * opened/created
50 *
51 * @param ctx the Context within which to work
52 */
53 public MessagesDBAdapter(Context ctx) {
54 this.mCtx = ctx;
55 }
56
57 public MessagesDBAdapter open() throws SQLException {
58 mDbHelper = new DatabaseHelper(mCtx);
59 mDb = mDbHelper.getWritableDatabase();
60 return this;
61 }
62
63 public void close() {
64 mDbHelper.close();
65 }
66
67 public long createNote(String phoneNo, String message) {
68 ContentValues initialValues = new ContentValues();
69 initialValues.put(KEY_RECIPIENT, phoneNo);
70 initialValues.put(KEY_MESSAGE, message);
71
72 open();
73
74 return mDb.insert(DATABASE_TABLE, null, initialValues);
75 }
76
77 public boolean deleteNote(long rowId) {
78
79 return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
80 }
81
82 public Cursor fetchAllNotes() {
83
84 return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_RECIPIENT,
85 KEY_MESSAGE}, null, null, null, null, null);
86 }
87
88 public Cursor fetchNote(long rowId) throws SQLException {
89
90 Cursor mCursor =
91
92 mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
93 KEY_RECIPIENT, KEY_MESSAGE}, KEY_ROWID + "=" + rowId, null,
94 null, null, null, null);
95 if (mCursor != null) {
96 mCursor.moveToFirst();
97 }
98 return mCursor;
99
100 }
101
102 public boolean updateNote(long rowId, String phoneNo, String message) {
103 ContentValues args = new ContentValues();
104 args.put(KEY_RECIPIENT, phoneNo);
105 args.put(KEY_MESSAGE, message);
106
107 return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
108 }
109}
110
111btnSend.setOnClickListener(new View.OnClickListener() {
112 public void onClick(View v) {
113 String phoneNo = editTextRecipient.getText().toString();
114 String message = editTextNewMessage.getText().toString();
115 Log.d(phoneNo, message);
116 saveState(phoneNo, message);
117 boolean split = false;
118
119 final Toast toast = Toast.makeText(getBaseContext(),
120 "Your message " + """ + message + """ + " is sent to " +"""+ phoneNo+""",
121 Toast.LENGTH_SHORT);
122
123 Runnable showToastRunnable = new Runnable() {
124 public void run() {
125 dialog.cancel();
126 toast.show();
127 }
128 };
129
130 if (phoneNo.length()>0 && message.length()>0) {
131 showProgress();
132 if (count == 0) {
133 handler.postDelayed(showToastRunnable, 0);
134 }
135 else if (count == 1) {
136 handler.postDelayed(showToastRunnable, 15000);
137 }
138 else if (count == 2) {
139 handler.postDelayed(showToastRunnable, 30000);
140 }
141 else if (count == 3) {
142 handler.postDelayed(showToastRunnable, 60000);
143 }
144 }
145
146 // sendSMS(phoneNo, message, split); */
147 else
148 Toast.makeText(getBaseContext(),
149 "Please enter both phone number and message.",
150 Toast.LENGTH_SHORT).show();
151 }
152 });
153mDbHelper.close();
154 }
155
156CREATE TABLE [myTable]
157(
158[_id] INTEGER NOT NULL PRIMARY KEY,
159[timestampColumn] TIMESTAMP
160);
161
162SELECT datetime(timestampColumn, 'localtime') FROM myTable
163
164public static final String KEY_TIMESTAMP = "timestampColumn";
165
166private static final String DATABASE_CREATE =
167 "create table notes (" + KEY_ROWID + " integer primary key autoincrement, "
168 + KEY_RECIPIENT + " text not null, " + KEY_MESSAGE + " text not null, " + KEY_TIME + " text not null, "+ KEY_TIMESTAMP +" TIMESTAMP DEFAULT CURRENT_TIMESTAMP );";
169
170public Cursor fetchAllNotes() {
171
172 return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_RECIPIENT,
173 KEY_MESSAGE, KEY_TIMESTAMP}, null, null, null, null, null);
174}
175
176public Cursor fetchNote(long rowId) throws SQLException {
177 Cursor mCursor =
178
179 mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
180 KEY_RECIPIENT, KEY_MESSAGE, KEY_TIMESTAMP}, KEY_ROWID + "=" + rowId, null,
181 null, null, null, null);
182 if (mCursor != null) {
183 mCursor.moveToFirst();
184 }
185 return mCursor;
186
187}
188
189public Cursor getAllNotes() {
190
191 Cursor mCursor = mDb.rawQuery("SELECT _id, time, message, recipient, datetime(timestampColumn, 'localtime') AS timestampColumn FROM notes", null);
192}