· 10 years ago · Nov 27, 2015, 12:54 AM
1/*
2* Author: Keith Kenny
3* Date of last edit: 26/11/2015
4* Third Year, Android Programming assignment
5* Class Description:
6* This is the SQLite Database that I created, It does
7* INSERT, UPDATE, SELECT and DELETE
8*
9*
10* CODE SOURCES:
11* This database and use of SQlite confused me a lot, and so I looked up a lot of video tutorials about it
12* This code is largely based off of that implementation, altered heavily and changed to suit me.
13*
14* A link to the youtube video:
15* https://www.youtube.com/watch?v=p8TaTgr4uKM&ab_channel=ProgrammingKnowledge
16*
17* And a link to the code source:
18* http://programmingknowledgeblog.blogspot.de/2015/04/android-sqlite-database-tutorial-1.html
19*
20 */
21
22package com.example.keith.thelibrary;
23
24import android.content.ContentValues;
25import android.content.Context;
26import android.database.Cursor;
27import android.database.sqlite.SQLiteDatabase;
28import android.database.sqlite.SQLiteOpenHelper;
29import android.util.Log;
30
31import java.util.ArrayList;
32
33public class Database extends SQLiteOpenHelper {
34
35 //Database name
36 public static final String DATABASE_NAME = "TheLibrary.db";
37
38 // Books Table, and Columns
39 public static final String TABLE_NAME_BOOKS = "Books";
40 public static final String TITLE_COLUMN = "TITLE";
41 public static final String AUTHOR_COLUMN = "AUTHOR";
42 public static final String OWNED_COLUMN = "OWNED";
43 public static final String OWNED_BY = "OWNER";
44
45 // User Table and Column
46 public static final String TABLE_NAME_USER = "User";
47 public static final String USER_COLUMN = "USERNAME";
48 public static final String PASSWORD_COLUMN = "PASSWORD";
49 public static final String EMAIL_COLUMN = "EMAIL";
50
51 public Database(Context context) {
52
53 super(context, DATABASE_NAME, null, 1);
54 }
55
56 @Override
57 //The Create Statements of the database
58 //Creating a User table locally and a Book table locally
59 //AS well as hard coding inserts, for reasons explained in the Books Activity Class document
60 public void onCreate(SQLiteDatabase db) {
61
62 db.execSQL("create table " + TABLE_NAME_USER + " (" + USER_COLUMN + " TEXT PRIMARY KEY, " + PASSWORD_COLUMN + " TEXT, " + EMAIL_COLUMN + " TEXT)");
63 db.execSQL("create table " + TABLE_NAME_BOOKS + " (" + TITLE_COLUMN + " TEXT PRIMARY KEY, " + AUTHOR_COLUMN + " TEXT, " + OWNED_COLUMN + " TEXT, "+OWNED_BY+" TEXT)");
64
65 //Inserts various users and books into the table for demo purposes
66 ContentValues c = new ContentValues();
67 ContentValues b = new ContentValues();
68 c.put(USER_COLUMN, "KeithKenny");
69 c.put(PASSWORD_COLUMN, "VinDiesel");
70 c.put(EMAIL_COLUMN, "Keith_Kny@hotmail.com");
71 db.insert(TABLE_NAME_USER, null, c);
72 c.put(USER_COLUMN, "ShaneOneill");
73 c.put(PASSWORD_COLUMN, "password");
74 c.put(EMAIL_COLUMN, "fasterbananastudios@gmail.com");
75 db.insert(TABLE_NAME_USER, null, c);
76 c.put(USER_COLUMN, "Big_Guy");
77 c.put(PASSWORD_COLUMN, "12345");
78 c.put(EMAIL_COLUMN, "example@gmail.com");
79 db.insert(TABLE_NAME_USER, null, c);
80 c.put(USER_COLUMN, "test");
81 c.put(PASSWORD_COLUMN, "test");
82 c.put(EMAIL_COLUMN, "example@gmail.com");
83 db.insert(TABLE_NAME_USER, null, c);
84
85 b.put(TITLE_COLUMN, "The Dark Tower: II");
86 b.put(AUTHOR_COLUMN, "Stephen King");
87 b.put(OWNED_COLUMN, "Owned Book");
88 b.put(OWNED_BY, "test");
89 db.insert(TABLE_NAME_BOOKS, null, b);
90 b.put(TITLE_COLUMN, "Bioshock");
91 b.put(AUTHOR_COLUMN, "Ken Levine");
92 b.put(OWNED_COLUMN, "Owned Book");
93 b.put(OWNED_BY, "KeithKenny");
94 db.insert(TABLE_NAME_BOOKS, null, b);
95 b.put(TITLE_COLUMN, "Eragon");
96 b.put(AUTHOR_COLUMN, "Christopher Paolini");
97 b.put(OWNED_COLUMN, "Wanted Book");
98 b.put(OWNED_BY, "KeithKenny");
99 db.insert(TABLE_NAME_BOOKS, null, b);
100 b.put(TITLE_COLUMN, "Harry Potther & The Chamber of Secrets");
101 b.put(AUTHOR_COLUMN, "J.K Rowling");
102 b.put(OWNED_COLUMN, "Owned Book");
103 b.put(OWNED_BY, "KeithKenny");
104 db.insert(TABLE_NAME_BOOKS, null, b);
105 b.put(TITLE_COLUMN, "The Lake House");
106 b.put(AUTHOR_COLUMN, "Keanu Reeves");
107 b.put(OWNED_COLUMN, "Owned");
108 b.put(OWNED_BY, "Big_Guy");
109 db.insert(TABLE_NAME_BOOKS, null, b);
110 b.put(TITLE_COLUMN, "The Dark Tower: I");
111 b.put(AUTHOR_COLUMN, "Stephen King");
112 b.put(OWNED_COLUMN, "Owned Book");
113 b.put(OWNED_BY, "test");
114 db.insert(TABLE_NAME_BOOKS, null, b);
115 }
116
117 @Override
118 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
119
120 }
121
122 //Function for inserting values to the user table
123 public boolean insertValuesUser (String _username, String _password, String _email) {
124 SQLiteDatabase db = this.getWritableDatabase();
125 ContentValues c = new ContentValues();
126 c.put(USER_COLUMN, _username);
127 c.put(PASSWORD_COLUMN, _password);
128 c.put(EMAIL_COLUMN, _email);
129 long isInserted = db.insert(TABLE_NAME_USER, null, c);
130
131 if(isInserted == -1) {
132 return false;
133 } else {
134 return true;
135 }
136
137 }
138
139 //Function for inserting values into the book table
140 public void insertValuesBooks (String _title, String _author, String _owned,String _user) {
141
142 SQLiteDatabase db = this.getWritableDatabase();
143 ContentValues c = new ContentValues();
144 c.put(TITLE_COLUMN, _title);
145 c.put(AUTHOR_COLUMN, _author);
146 c.put(OWNED_COLUMN, _owned);
147 c.put(OWNED_BY, _user);
148 db.insert(TABLE_NAME_BOOKS, null, c);
149
150 }
151
152 //Checks if a user has been created and exists on the login/register page
153 //This would be more effective if an online server existed for this app
154
155 public boolean userExists() {
156 SQLiteDatabase db = this.getWritableDatabase();
157 Cursor users = db.rawQuery("SELECT * FROM " + TABLE_NAME_USER, null);
158 boolean userExists = users.moveToFirst();
159 return userExists;
160 }
161
162 //This code is for getting the string username
163 //That the user inputted upon registration
164 //To display at the top of the page
165 public String getUsername() {
166 SQLiteDatabase db = this.getWritableDatabase();
167 Cursor users = db.rawQuery("SELECT * FROM " + TABLE_NAME_USER, null);
168 String _username = "";
169
170 if(users.moveToFirst()) {
171
172 _username = users.getString(users.getColumnIndex(USER_COLUMN));
173 }
174
175 return _username;
176 }
177
178 //This function takes the current users name and only returns books that they specifically own or want
179 public ArrayList<String> getAllBooks(String curUser) {
180 SQLiteDatabase db = this.getWritableDatabase();
181 Cursor Cbooks = db.rawQuery("SELECT * FROM " + TABLE_NAME_BOOKS + " WHERE " + OWNED_BY + " = '" + curUser + "'", null);
182 ArrayList<String> books = new ArrayList<String>();
183 if(Cbooks.moveToFirst()) {
184
185 do{
186
187 books.add("Title: " + Cbooks.getString(Cbooks.getColumnIndex(TITLE_COLUMN)) + "\nAuthor: " + Cbooks.getString(Cbooks.getColumnIndex(AUTHOR_COLUMN)) + "\nCategory: " + Cbooks.getString(Cbooks.getColumnIndex(OWNED_COLUMN)));
188
189 }while(Cbooks.moveToNext());
190
191 }
192
193 return books;
194 }
195
196 //This gets the books that are similar to the string passed in by the user
197 //And that the user does not currently own
198 public ArrayList<String> getSomeBooks(String _title, String curUser) {
199
200 SQLiteDatabase db = this.getWritableDatabase();
201 Cursor Cbooks = db.rawQuery("SELECT * FROM " + TABLE_NAME_BOOKS + " WHERE " + TITLE_COLUMN + " LIKE '%" + _title +"%' AND " +OWNED_BY+" <> '" + curUser+"'" , null);
202 ArrayList<String> books = new ArrayList<String>();
203
204 if(Cbooks.moveToFirst()) {
205
206 do{
207 books.add(Cbooks.getString(Cbooks.getColumnIndex(TITLE_COLUMN)) + " by, "+ Cbooks.getString(Cbooks.getColumnIndex(AUTHOR_COLUMN)) + "\nCategory: " + Cbooks.getString(Cbooks.getColumnIndex(OWNED_COLUMN)));
208
209 }while(Cbooks.moveToNext());
210
211 }
212
213 return books;
214
215 }
216
217 //This is the delete function for boos
218 public void removeBook(String _title) {
219 SQLiteDatabase db = this.getWritableDatabase();
220 db.delete(TABLE_NAME_BOOKS, TITLE_COLUMN + "= ?", new String[]{_title});
221
222 }
223
224 //This is the edit function for books
225 //Takes in new book info, and old book title
226 //Changes the book with the old title
227 public void changeBook(String _oldTitle, String _title, String _author, String _owned) {
228 SQLiteDatabase db = this.getWritableDatabase();
229
230 ContentValues change = new ContentValues();
231
232 change.put(TITLE_COLUMN, _title);
233 change.put(AUTHOR_COLUMN, _author);
234 change.put(OWNED_COLUMN, _owned);
235 db.update(TABLE_NAME_BOOKS, change, TITLE_COLUMN + " = ?", new String[]{_oldTitle});
236
237 }
238
239 //This gets the email address of the user, to open an email
240 public String getEmail(String _owner) {
241 SQLiteDatabase db = this.getWritableDatabase();
242
243
244 Cursor results = db.rawQuery("SELECT * FROM " + TABLE_NAME_USER + " WHERE " + USER_COLUMN + " = '" + _owner + "'", null);
245 if(results.moveToFirst()) {
246
247 String _email = results.getString(results.getColumnIndex(EMAIL_COLUMN));
248 return _email;
249
250 } else {
251 return "";
252 }
253
254 }
255
256 //This gets the owner of a book, again for emailing purposes, and to add their name
257 //Into the email being sent
258 public String getOwner(String _title) {
259 SQLiteDatabase db = this.getWritableDatabase();
260
261 Cursor results = db.rawQuery("SELECT * FROM " + TABLE_NAME_BOOKS + " WHERE " + TITLE_COLUMN + " = '" + _title + "'", null);
262 if(results.moveToFirst()) {
263
264 String _owner = results.getString(results.getColumnIndex(OWNED_BY));
265
266 return _owner;
267
268 } else {
269 return "";
270 }
271
272 }
273 public boolean login(String _username, String _password) {
274 SQLiteDatabase db = this.getWritableDatabase();
275
276 Cursor c = db.rawQuery("SELECT * FROM " + TABLE_NAME_USER + " WHERE " + USER_COLUMN + " = '" + _username +"' AND " + PASSWORD_COLUMN + " = '" + _password + "'", null);
277 return c.moveToFirst();
278
279 }
280
281
282}