· 7 years ago · Sep 16, 2018, 08:40 AM
1retrieve data from databse in edit text box in android and want to display simple dropdown items in one line
2// All Static variables
3 // Database Version
4 private static final int DATABASE_VERSION = 2;
5
6 // Database Name
7 private static final String DATABASE_NAME = "android_ap.db";
8
9 // Login table name
10 private static final String TABLE_LOGIN = "login";
11 // Login Table Columns names
12 private static final String KEY_ID = "_id";
13 private static final String KEY_NAME = "name";
14 private static final String KEY_PASSWORD = "password";
15 private static final String TABLE_PARTY="party_master";
16 private static final String KEY_PID="party_id";
17 private static final String KEY_PNAME="party_name";
18 private static final String KEY_ROUTE_NAME="route_name";
19 private static final String KEY_ROUTE_NUM="RootNo";
20
21 private static final String KEY_CONTACT_PERSON="contect_person";
22 private static final String KEY_CELL_NUM="cell_no";
23
24
25 private static final String CREATE_LOGIN_TABLE = "CREATE TABLE " + TABLE_LOGIN + "("
26 + KEY_ID + " INTEGER PRIMARY KEY,"
27 + KEY_NAME + " TEXT NOT NULL,"
28 + KEY_PASSWORD + " TEXT NOT NULL);";
29 private static final String CREATE_PARTY_TABLE = "CREATE TABLE " + TABLE_PARTY + "("
30 + KEY_PID + " INTEGER PRIMARY KEY,"
31 + KEY_PNAME + " TEXT NOT NULL,"
32 + KEY_ROUTE_NAME + " TEXT NOT NULL);";
33
34
35 public LoginDB(Context context) {
36 super(context, DATABASE_NAME, null, DATABASE_VERSION);
37 }
38
39
40 // Creating Tables
41 @Override
42 public void onCreate(SQLiteDatabase db) {
43 db.execSQL(CREATE_LOGIN_TABLE);
44 db.execSQL(CREATE_PARTY_TABLE);
45 }
46
47
48 // Upgrading database
49 @Override
50 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
51 // Drop older table if existed
52 db.execSQL("DROP TABLE IF EXISTS " + TABLE_LOGIN);
53 db.execSQL("DROP TABLE IF EXISTS " + TABLE_PARTY);
54
55 // Create tables again
56 onCreate(db);
57 }
58
59
60 /**
61 * Storing user details in database
62 * */
63 public void addUser(String name, String password) {
64 SQLiteDatabase db = this.getWritableDatabase();
65
66 ContentValues values = new ContentValues();
67 values.put(KEY_NAME, name); // Name
68 values.put(KEY_PASSWORD, password); // password
69
70 // Inserting Row
71 db.insert(TABLE_LOGIN, null, values);
72 db.close(); // Closing database connection
73 }
74
75 public void add_party_master(String party_name, String route_name) {
76 SQLiteDatabase db = this.getWritableDatabase();
77
78 ContentValues values = new ContentValues();
79 values.put(KEY_PNAME, party_name); // Party Name
80 values.put(KEY_ROUTE_NAME, route_name); // Route Name
81
82 // Inserting Row
83 db.insert(TABLE_PARTY, null, values);
84 db.close(); // Closing database connection
85 }
86
87
88 /**
89 * Getting user data from database
90 * */
91 public HashMap<String, String> getUserDetails(String party_name,String route_name,String RootNo, String contect_person,String cell_no){
92 HashMap<String,String> user = new HashMap<String,String>();
93 String selectQuery = "SELECT * FROM " + TABLE_PARTY;
94
95 SQLiteDatabase db = this.getReadableDatabase();
96 Cursor cursor = db.rawQuery(selectQuery, null);
97 // Move to first row
98 cursor.moveToFirst();
99 if(cursor.getCount() > 0){
100 user.put("party_name", cursor.getString(1));
101 user.put("route_name", cursor.getString(2));
102 user.put("RootNo", cursor.getString(3));
103 user.put("contect_person", cursor.getString(4));
104 user.put("cell_no", cursor.getString(5));
105 }
106 cursor.close();
107 db.close();
108 // return user
109 return user;
110 }
111
112 public ArrayList<String> getUserRecords(String route_name){
113 //HashMap<String,String> userNew = new HashMap<String,String>();
114 ArrayList<String> a=new ArrayList<String>();
115 String selectQuery = "SELECT * FROM " + TABLE_PARTY;
116
117 SQLiteDatabase db = this.getReadableDatabase();
118 Cursor cursor = db.rawQuery(selectQuery, null);
119 // Move to first row
120 cursor.moveToFirst();
121 if(cursor.getCount() > 0){
122 //userNew.put("party_name", cursor.getString(1));
123 // userNew.put("route_name", cursor.getString(2));
124 // userNew.put("RootNo", cursor.getString(3));
125 // userNew.put("contect_person", cursor.getString(4));
126 // userNew.put("cell_no", cursor.getString(5));
127
128 a.add(cursor.getString(1));
129 }
130 cursor.close();
131 db.close();
132 // return user
133 return a;
134 }
135
136
137 /**
138 * Getting user login status
139 * return true if rows are there in table
140 * */
141 public int getRowCount() {
142 String countQuery = "SELECT * FROM " + TABLE_LOGIN ;
143 SQLiteDatabase db = this.getReadableDatabase();
144 Cursor cursor = db.rawQuery(countQuery, null);
145 int rowCount = cursor.getCount();
146 db.close();
147 cursor.close();
148
149 // return row count
150 return rowCount;
151 }
152 public int getRowCount_party() {
153 String countQuery = "SELECT * FROM " + TABLE_PARTY ;
154 SQLiteDatabase db = this.getReadableDatabase();
155 Cursor curs = db.rawQuery(countQuery, null);
156 int rowCount_p = curs.getCount();
157 db.close();
158 curs.close();
159
160 // return row count
161 return rowCount_p;
162 }
163
164
165 /**
166 * Re crate database
167 * Delete all tables and create them again
168 * @param pas1
169 * @param username1
170 * */
171
172 public int getAllname(String username1, String password1)
173 {
174 SQLiteDatabase db=this.getReadableDatabase();
175 Cursor cur=db.rawQuery("SELECT KEY_NAME from "+TABLE_LOGIN +"WHERE KEY_NAME = '" +username1+"' and KEY_PASSWORD = '"+password1+"'",null);
176 int rowCount = cur.getCount();
177 db.close();
178 cur.close();
179 return rowCount;
180 }
181 public String getroot() throws SQLException
182 {
183 SQLiteDatabase db=this.getReadableDatabase();
184 Cursor cur=db.rawQuery("SELECT * from "+ TABLE_PARTY + "WHERE route_name=?",null);
185 cur.getString(cur.getColumnIndex("route_name"));
186 db.close();
187 cur.close();
188 return getroot();
189 }
190
191
192 public boolean Login(String username, String password) throws SQLException
193 {
194 SQLiteDatabase db=this.getReadableDatabase();
195 Cursor mCursor = db.rawQuery("SELECT * FROM " + TABLE_LOGIN + " WHERE name=? AND password=?", new String[]{username,password});
196 if (mCursor != null) {
197 if(mCursor.getCount() > 0)
198 {
199 return true;
200 }
201 }
202 return false;
203 }
204
205 public int rootnm(String[] route_name) throws SQLException
206 {
207 SQLiteDatabase db=this.getReadableDatabase();
208 Cursor mnCursor = db.rawQuery("SELECT * FROM " + TABLE_PARTY + " WHERE route_name=? ", new String[] {});
209 if (mnCursor != null) {
210 if(mnCursor.getCount() > 0)
211 {
212 db.getSyncedTables();
213 }
214 }
215 return 0;
216
217 }
218
219 public Cursor queueAll(){
220 String[] columns = new String[]{KEY_PID, KEY_ROUTE_NAME};
221 SQLiteDatabase db = null;
222 Cursor cursor123 = db.query(TABLE_PARTY, columns,
223 null, null, null, null, null);
224
225 return cursor123;
226 }
227
228 public Cursor retrieveRecords()
229 {
230 Cursor c = null;
231 SQLiteDatabase db = null;
232 c = db.rawQuery("select DISTICT route_name from party_master", null);
233 return c;
234 }
235
236 private Cursor query(String selection, String[] selectionArgs, String[] columns) {
237 /* The SQLiteBuilder provides a map for all possible columns requested to
238 * actual columns in the database, creating a simple column alias mechanism
239 * by which the ContentProvider does not need to know the real column names
240 */
241 SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
242 builder.setTables(TABLE_PARTY);
243
244 SQLiteOpenHelper db = null;
245 Cursor cursor = builder.query(db.getReadableDatabase(),
246 columns, selection, selectionArgs, null, null, null);
247
248 if (cursor == null) {
249 return null;
250 } else if (!cursor.moveToFirst()) {
251 cursor.close();
252 return null;
253 }
254 return cursor;
255 }
256
257 public Cursor getWord(String route_name1, String[] columns) {
258 String selection = "route_name = ?";
259 String[] selectionArgs = new String[] {route_name1};
260
261 return query(selection, selectionArgs, columns);
262
263 /* This builds a query that looks like:
264 * SELECT <columns> FROM <table> WHERE rowid = <rowId>
265 */
266 }
267
268 String[] rt = new String[]{KEY_ROUTE_NAME};
269
270
271
272 public void resetTables(){
273 SQLiteDatabase db = this.getWritableDatabase();
274 // Delete All Rows
275 db.delete(TABLE_LOGIN, null, null);
276 db.delete(TABLE_PARTY, null, null);
277 db.close();
278 }
279
280}
281
282private EditText etroute;
283private EditText etshop;
284private Button btnAddItem;
285private Button btnOkRoute;
286private Button btnOkShop;
287public SQLiteDatabase db;
288
289
290/** Called when the activity is first created. */
291@Override
292public void onCreate(Bundle savedInstanceState) {
293 super.onCreate(savedInstanceState);
294 setContentView(R.layout.additem);
295
296 LoginDB lg =new LoginDB(AddItemActivity.this);
297
298 // String[] route_name = lg.retrieveRecords();
299
300String[] route = lg.rt ;
301
302 ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,
303 android.R.layout.simple_dropdown_item_1line, route);
304 AutoCompleteTextView textView = (AutoCompleteTextView)
305 findViewById(R.id.route);
306
307 textView.setThreshold(0);
308 textView.setAdapter(adapter);
309
310 // Get the EditText and Button References
311 etroute = (EditText)findViewById(R.id.route);
312 etshop = (EditText)findViewById(R.id.shop);
313
314 db =openOrCreateDatabase("DATABASE_NAME", MODE_PRIVATE, null);
315 db.execSQL("CREATE TABLE IF NOT EXISTS TABLE_PARTY (KEY_ROUTE_NAME TEXT);");
316 Cursor c1=db.rawQuery("SELECT * FROM TABLE_PARTY", null);
317 if (c1.moveToNext())
318 etroute.setText(c1.getString(0));
319
320
321 etshop = (EditText)findViewById(R.id.shop);
322 btnAddItem = (Button)findViewById(R.id.add_item);
323 btnOkRoute = (Button)findViewById(R.id.btn_ok_route);
324 btnOkShop = (Button)findViewById(R.id.btn_ok_shop);
325
326
327 btnOkRoute.setOnClickListener(new OnClickListener() {
328 @Override
329 public void onClick(View v) {
330 String route_name = etroute.getText().toString();
331
332 }
333 });
334
335 btnOkShop.setOnClickListener(new OnClickListener() {
336 @Override
337 public void onClick(View v) {
338 String party_name = etshop.getText().toString();
339
340 }
341 });
342
343 btnAddItem.setOnClickListener(new OnClickListener() {
344 @Override
345 public void onClick(View v) {
346 String route_name = etroute.getText().toString();
347 String party_name = etshop.getText().toString();
348
349 {
350 Toast.makeText(AddItemActivity.this, "Record Successfully Inserted", 2000).show();
351 }
352 db.close();
353 }
354 });
355
356
357}