· 8 years ago · Feb 14, 2018, 08:16 PM
1package com.example.owner.databasesexample.db;
2
3import android.content.Context;
4import android.database.sqlite.SQLiteDatabase;
5import android.database.sqlite.SQLiteOpenHelper;
6
7public class DatabaseOpenHelper extends SQLiteOpenHelper {
8
9 // if you change the database schema, you must increment the db version.
10 public static final int DATABASE_VERSION = 1;
11 // this is used to name the underlying file storing the actual data
12 public static final String DATABASE_NAME = "Countries.db";
13
14 public DatabaseOpenHelper(Context context) {
15 super(context, DATABASE_NAME, null, DATABASE_VERSION);
16 }
17
18 public void onCreate(SQLiteDatabase db) {
19
20 db.execSQL(SQL_CREATE_BLOG_ENTRIES);
21 //Creating tables
22 db.execSQL(createTable);
23 db.execSQL(createTable2);
24 //Countries
25 db.execSQL(insertE);
26 db.execSQL(insertSc);
27 db.execSQL(insertF);
28 db.execSQL(insertB);
29 db.execSQL(insertC);
30 db.execSQL(insertGe);
31 db.execSQL(insertGr);
32 db.execSQL(insertSp);
33 db.execSQL(insertSwe);
34 db.execSQL(insertSwi);
35 //Cities
36 db.execSQL(insertECity1);
37 db.execSQL(insertECity2);
38 db.execSQL(insertECity3);
39 db.execSQL(insertECity4);
40 db.execSQL(insertECity5);
41 db.execSQL(insertSCity1);
42 }
43
44 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
45 // in our case, we simply delete all data and recreate the DB
46 db.execSQL(SQL_DELETE_BLOG_ENTRIES);
47 onCreate(db);
48 }
49
50 private static final String SQL_CREATE_BLOG_ENTRIES =
51 "CREATE TABLE entries (" +
52 "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
53 "title TEXT NOT NULL, " +
54 "body TEXT NOT NULL " +
55 ")";
56
57 private static final String SQL_DELETE_BLOG_ENTRIES =
58 "DROP TABLE IF EXISTS entries";
59
60 String createTable = "CREATE TABLE IF NOT EXISTS 'Countries' ('CountryName' TEXT,'Population' INTEGER,'Currency' TEXT,'Size' INTEGER, 'Favourite' INTEGER," +
61 " PRIMARY KEY('CountryName'))";
62
63 String insertE = "INSERT INTO 'Countries' VALUES ('England', 53000000, 'GBP', 242495, 1)";
64 String insertSc = "INSERT INTO 'Countries' VALUES ('Scotland', 5295000, 'GBP', 80077, 0)";
65 String insertF = "INSERT INTO 'Countries' VALUES ('France', 66900000, 'EUR', 643801, 0)";
66 String insertB = "INSERT INTO 'Countries' VALUES ('Belgium', 11350000, 'EUR', 30528, 0)";
67 String insertC = "INSERT INTO 'Countries' VALUES ('China', 1330044000, 'RMB', 9596960, 0)";
68 String insertGe = "INSERT INTO 'Countries' VALUES ('Germany', 82670000, 'EUR', 357376, 0)";
69 String insertGr = "INSERT INTO 'Countries' VALUES ('Greece', 11000000, 'EUR', 131940, 0)";
70 String insertSp = "INSERT INTO 'Countries' VALUES ('Spain', 46500000, 'EUR', 504782, 0)";
71 String insertSwe = "INSERT INTO 'Countries' VALUES ('Sweeden', 9555893, 'KR', 449964, 0)";
72 String insertSwi = "INSERT INTO 'Countries' VALUES ('Switzerland', 7581000, 'EUR', 41290, 0)";
73
74 String createTable2 = "CREATE TABLE IF NOT EXISTS 'Cities' ('CityName' TEXT,'Country' TEXT,'Information' TEXT," +
75 " PRIMARY KEY('CityName'))";
76
77 String insertECity1 = "INSERT INTO 'Cities' VALUES ('Manchester', 'England', 'Fluff')";
78 String insertECity2 = "INSERT INTO 'Cities' VALUES ('Newcastle', 'England', 'Fluff')";
79 String insertECity3 = "INSERT INTO 'Cities' VALUES ('Liverpool', 'England', 'Fluff')";
80 String insertECity4 = "INSERT INTO 'Cities' VALUES ('London', 'England', 'Fluff')";
81 String insertECity5 = "INSERT INTO 'Cities' VALUES ('Bristol', 'England', 'Fluff')";
82 String insertSCity1 = "INSERT INTO 'Cities' VALUES ('Edinburgh', 'Scotland', 'Fluff')";
83
84}
85Kieran Power
86page showing the listview:
87package com.example.owner.databasesexample;
88
89import android.content.Intent;
90import android.database.Cursor;
91import android.database.DatabaseUtils;
92import android.database.sqlite.SQLiteDatabase;
93import android.os.Bundle;
94import android.support.v7.app.AppCompatActivity;
95import android.view.View;
96import android.widget.AdapterView;
97import android.widget.ArrayAdapter;
98import android.widget.ListView;
99
100import com.example.owner.databasesexample.db.DatabaseOpenHelper;
101
102public class MainActivity extends AppCompatActivity {
103
104 private ListView listView;
105
106 @Override
107 protected void onCreate(Bundle savedInstanceState) {
108 super.onCreate(savedInstanceState);
109 setContentView(R.layout.activity_main);
110
111 listView = findViewById(R.id.list_view);
112 }
113
114 protected void onResume() {
115 super.onResume();
116
117 // we first need a database open helper to even touch the DB...
118 DatabaseOpenHelper doh = new DatabaseOpenHelper(this);
119 // we then get a readable handler to the DB...
120 SQLiteDatabase db = doh.getReadableDatabase();
121 // and then we run a raw SQL query which returns a cursor pointing to the results
122 Cursor cursor = db.rawQuery("SELECT * FROM Countries", null);
123 // number of rows in the result set
124 int numOfRows = cursor.getCount();
125 final String [] CountryName = new String[numOfRows]; // the titles of the blog entries
126 final String [] Population = new String[numOfRows]; // the bodies of the blog entries
127 final String [] Currency = new String[numOfRows];
128 final String [] Size = new String[numOfRows];
129 cursor.moveToFirst();
130 int columnCountryNameIndex = cursor.getColumnIndex("CountryName");
131 int columnPopulationIndex = cursor.getColumnIndex("Population");
132 int columnCurrencyIndex = cursor.getColumnIndex("Currency");
133 int columnSizeIndex = cursor.getColumnIndex("Size");
134 for(int i = 0; i < numOfRows; i++) {
135 CountryName[i] = cursor.getString(columnCountryNameIndex);
136 Population[i] = cursor.getString(columnPopulationIndex);
137 Currency[i] = cursor.getString(columnCurrencyIndex);
138 Size[i] = cursor.getString(columnSizeIndex);
139 cursor.moveToNext();
140 }
141 cursor.close();
142
143 final ArrayAdapter arrayAdapter = new ArrayAdapter<>(this,
144 android.R.layout.simple_expandable_list_item_1, CountryName);
145 listView.setAdapter(arrayAdapter);
146 listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
147 @Override
148 public void onItemClick(AdapterView<?> parent, View v, int pos, long id) {
149 // Toast.makeText(MainActivity.this, bodies[pos], Toast.LENGTH_SHORT).show();
150 viewEntry(CountryName[pos], Population[pos], Currency[pos], Size[pos]);
151
152 }
153 });
154 listView.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
155 @Override
156 public boolean onItemLongClick(AdapterView<?> parent, View view, int position, long id) {
157 deleteEntry(CountryName[position]);
158 onResume();
159 return true;
160 }
161 });
162 }
163
164 private void viewEntry(String CountryName, String Population, String Currency, String Size) {
165 Intent intent = new Intent(this, ViewEntryActivity.class);
166 intent.putExtra(ViewEntryActivity.EXTRA_COUNTRYNAME, CountryName);
167 intent.putExtra(ViewEntryActivity.EXTRA_POPULATION, Population);
168 intent.putExtra(ViewEntryActivity.EXTRA_CURRENCY, Currency);
169 intent.putExtra(ViewEntryActivity.EXTRA_SIZE, Size);
170 startActivity(intent);
171 }
172
173 private void deleteEntry(String title) {
174 DatabaseOpenHelper doh = new DatabaseOpenHelper(this);
175 SQLiteDatabase db = doh.getWritableDatabase();
176 db.delete("entries", "title=" + DatabaseUtils.sqlEscapeString(title) + "", null);
177 }
178}