SQLite SQLite is an inprocess library that implements

SQLite �SQLite is an in-process library that implements a selfcontained, server less, zero-configuration, transactional SQL database engine. �SQLite is an embedded SQL database engine. �Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. �SQLite is the most used database engine in the world Λευτέρης Κουμάκης ΠΟΛΥΜΕΣΙΚΕΣ ΥΠΗΡΕΣΙΕΣ ΣΤΗΝ ΥΓΕΙΑ

SQLLite �Embedded RDBMS �ACID (Atomicity, Consistency, Isolation, Durability) Compliant �Size – about 257 Kbytes �Not a client/server architecture �Accessed via function calls from the application �Writing (insert, update, delete) locks the database, queries can be done in parallel Λευτέρης Κουμάκης ΠΟΛΥΜΕΣΙΚΕΣ ΥΠΗΡΕΣΙΕΣ ΣΤΗΝ ΥΓΕΙΑ

Storage classes �NULL – null value �INTEGER - signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value �REAL - a floating point value, 8 -byte IEEE floating point number. �TEXT - text string, stored using the database encoding (UTF-8, UTF-16 BE or UTF-16 LE). �BLOB. The value is a blob of data, stored exactly as it was input. This is quite different than the normal SQL data types so please read: http: //www. sqlite. org/datatype 3. html Λευτέρης Κουμάκης ΠΟΛΥΜΕΣΙΚΕΣ ΥΠΗΡΕΣΙΕΣ ΣΤΗΝ ΥΓΕΙΑ

android. database. sqlite - Classes Contains the SQLite database management classes that an application would use � SQLite. Closeable - An object created from a SQLite. Database that can be closed. � SQLite. Cursor - A Cursor implementation that exposes results from a query on a SQLite. Database. � SQLite. Database - Exposes methods to manage a SQLite database. � SQLite. Open. Helper - A helper class to manage database creation and version management. � SQLite. Program - A base class for compiled SQLite programs. � SQLite. Query - A SQLite program that represents a query that reads the resulting rows into a Cursor. Window. � SQLite. Query. Builder - a convenience class that helps build SQL queries to be sent to SQLite. Database objects. � SQLite. Statement - A pre-compiled statement against a SQLite. Database that can be reused. Λευτέρης Κουμάκης ΠΟΛΥΜΕΣΙΚΕΣ ΥΠΗΡΕΣΙΕΣ ΣΤΗΝ ΥΓΕΙΑ

open. Or. Create. Database( ) �This method will open an existing database or create one in the application data area import android. database. sqlite. SQLite. Database; SQLite. Database my. Database; my. Database = open. Or. Create. Database ("my_sqlite_database. db" , SQLite. Database. CREATE_IF_NECESSARY , null); Λευτέρης Κουμάκης ΠΟΛΥΜΕΣΙΚΕΣ ΥΠΗΡΕΣΙΕΣ ΣΤΗΝ ΥΓΕΙΑ

SQLite Database Properties �Important database configuration options include: version, locale, and thread-safe locking. import java. util. Locale; my. Database. set. Version(1); my. Database. set. Locking. Enabled(true); my. Database. Set. Locale(Locale. get. Default()); Λευτέρης Κουμάκης ΠΟΛΥΜΕΣΙΚΕΣ ΥΠΗΡΕΣΙΕΣ ΣΤΗΝ ΥΓΕΙΑ

Creating Tables �Create a static string containing the SQLite CREATE statement, use the exec. SQL( ) method to execute it. String create. Author = "CREAT TABLE authors ( id INTEGER PRIMARY KEY AUTOINCREMENT, fname TEXT, lname TEXT); my. Database. exec. SQL(create. Author); Λευτέρης Κουμάκης ΠΟΛΥΜΕΣΙΚΕΣ ΥΠΗΡΕΣΙΕΣ ΣΤΗΝ ΥΓΕΙΑ

insert( ) �long insert(String table, String null. Column. Hack, Content. Values values) import android. content. Content. Values; Content. Values values = new Content. Values( ); values. put("firstname" , "J. K. "); values. put("lastname" , "Rowling"); long new. Author. ID = my. Database. insert("tbl_authors" , "" , values); Λευτέρης Κουμάκης ΠΟΛΥΜΕΣΙΚΕΣ ΥΠΗΡΕΣΙΕΣ ΣΤΗΝ ΥΓΕΙΑ

update( ) �int update(String table, Content. Values values, String where. Clause, String[ ] where. Args) public void update. Book. Title(Integer book. Id, String new. Title) { Content. Values values = new Content. Values(); values. put("title" , new. Title); my. Database. update("tbl_books" , values , "id=? " , new String[ ] {book. Id. to. String() } ); } Λευτέρης Κουμάκης ΠΟΛΥΜΕΣΙΚΕΣ ΥΠΗΡΕΣΙΕΣ ΣΤΗΝ ΥΓΕΙΑ

delete( ) �int delete(String table, String where. Clause, String[] where. Args) public void delete. Book(Integer book. Id) { my. Database. delete("tbl_books" , "id=? " , new String[ ] { book. Id. to. String( ) } ) ; } Λευτέρης Κουμάκης ΠΟΛΥΜΕΣΙΚΕΣ ΥΠΗΡΕΣΙΕΣ ΣΤΗΝ ΥΓΕΙΑ

android. database �http: //developer. android. com/reference/android/database /package-summary. html �Contains classes and interfaces to explore data returned through a content provider. �The main thing you are going to use here is the Cursor interface to get the data from the resultset that is returned by a query http: //developer. android. com/reference/android/database/Cursor. html Λευτέρης Κουμάκης ΠΟΛΥΜΕΣΙΚΕΣ ΥΠΗΡΕΣΙΕΣ ΣΤΗΝ ΥΓΕΙΑ

Queries �Method of SQLite. Database class and performs queries on the DB and returns the results in a Cursor object �Cursor c = mdb. query(p 1, p 2, p 3, p 4, p 5, p 6, p 7) � p 1 ; Table name (String) � p 2 ; Columns to return (String array) � p 3 ; WHERE clause (use null for all, ? s for selection args) � p 4 ; selection arg values for ? s of WHERE clause � p 5 ; GROUP BY ( null for none) (String) � p 6 ; HAVING (null unless GROUP BY requires one) (String) � p 7 ; ORDER BY (null for default ordering)(String) � p 8 ; LIMIT (null for no limit) (String) Λευτέρης Κουμάκης ΠΟΛΥΜΕΣΙΚΕΣ ΥΠΗΡΕΣΙΕΣ ΣΤΗΝ ΥΓΕΙΑ

Simple Queries �SQL - "SELECT * FROM ABC; " SQLite - Cursor c = mdb. query(abc, null, null); �SQL - "SELECT * FROM ABC WHERE C 1=5" SQLite - Cursor c = mdb. query( abc, null, "c 1=? " , new String[ ] {"5"}, null, null); � SQL – "SELECT title, id FROM BOOKS ORDER BY title ASC" SQLite – String cols. To. Return [ ] {"title", "id"}; String sort. Order = "title ASC"; Cursor c = mdb. query("books", cols. To. Return, null, sort. Order); Λευτέρης Κουμάκης ΠΟΛΥΜΕΣΙΚΕΣ ΥΠΗΡΕΣΙΕΣ ΣΤΗΝ ΥΓΕΙΑ

The Room Component � Room isn’t a new database system. It is an abstract layer that wraps the standard SQLite database adopted by Android � Room is composed of three main components: � Entity is the class that is being saved in the Database. An exclusive database table is created for each class annotated with @Entity. � The DAO (Data Access Objects) is the interface annotated with @Dao that mediates the access to objects in the database and its tables. There are four specific annotations for the basic DAO operations: @Insert, @Update, @Delete, and @Query. � The Database component is an abstract class annotated with @Database, which extends Room. Database. The class defines the list of Entities and its DAOs. Λευτέρης Κουμάκης ΠΟΛΥΜΕΣΙΚΕΣ ΥΠΗΡΕΣΙΕΣ ΣΤΗΝ ΥΓΕΙΑ

An entity corresponds to a single database table and it represents a model class that we want to persist in a database. Each entry in a database represents an object of that class and is stored in a single row. Each column represents data relevant to that object, or, in other words, it’s member variables. Λευτέρης Κουμάκης ΠΟΛΥΜΕΣΙΚΕΣ ΥΠΗΡΕΣΙΕΣ ΣΤΗΝ ΥΓΕΙΑ

Room In Room you don’t have to write a class extending SQLite. Open. Helper or write table creation queries. Room creates the tables automatically for you. In Room we define a simple POJO class and annotate it with @Entity. http: //thetechnocafe. com/how-to-use-room-in-android-all-you-need-to-know-to -get-started/ Pojo: Plain Old Java Object is a Java object not bound by any restriction other than those forced by the Java Language Specification. I. e. , a POJO should not have to Extend prespecified classes, Implement prespecified interface, Contain prespecified annotations Λευτέρης Κουμάκης ΠΟΛΥΜΕΣΙΚΕΣ ΥΠΗΡΕΣΙΕΣ ΣΤΗΝ ΥΓΕΙΑ

Dependencies Open the build. gradle file for your app or module and add the artifacts that you need as dependencies: For Android studio v 3 � implementation "android. arch. persistence. room: runtime: 1. 0. 0 -beta 2" � annotation. Processor "android. arch. persistence. room: compiler: 1. 0. 0 -beta 2“ For Android Studio v 2 At build. gradle (Project…) add allprojects { repositories { jcenter() maven { url "https: //maven. google. com" } } Λευτέρης Κουμάκης At build. gradle (Module: app) add compile 'android. arch. persistence. room: run time: 1. 0. 0 -alpha 1' annotation. Processor 'android. arch. persistence. room: com piler: 1. 0. 0 -alpha 1' ΠΟΛΥΜΕΣΙΚΕΣ ΥΠΗΡΕΣΙΕΣ ΣΤΗΝ ΥΓΕΙΑ

Create database Database : This component represents data holder. The annotated class should be an abstract class that extends Room. Database. At runtime, you can acquire an instance of it by calling Room. database. Builder() or Room. in. Memory. Database. Builder(). Create java file App. Database @Database(entities = {Demographics. class}, version = 1) public abstract class App. Database extends Room. Database { public abstract Demographics. Dao(); } https: //medium. com/mindorks/sqlite-made-easy-room-persistence-library-ecd 1 a 5 bb 0 a 2 c Λευτέρης Κουμάκης ΠΟΛΥΜΕΣΙΚΕΣ ΥΠΗΡΕΣΙΕΣ ΣΤΗΝ ΥΓΕΙΑ

@Entity(table. Name = "demographcis") public class Demographics { @Primary. Key(auto. Generate = true) private int key; @Column. Info(name = "id") private String id; Create Table @Column. Info(name = "age") private int age; @Column. Info(name = "height") private int height; Create java file Demographics @Column. Info(name = "weight") private int weight; @Column. Info(name = "speed") private double speed; public int get. Key() { return key; } In Room you don’t have to write a class extending SQLite. Open. Helper or write table creation queries. Room creates the tables automatically for you. In Room we define a simple POJO class and annotate it with @Entity. public void set. Key(int key) { this. key = key; } public String get. Id() { return id; } public void set. Id(String id) { this. id = id; } public int get. Age() { return age; } public void set. Age(int age) { this. age = age; } …… … } Λευτέρης Κουμάκης ΠΟΛΥΜΕΣΙΚΕΣ ΥΠΗΡΕΣΙΕΣ ΣΤΗΝ ΥΓΕΙΑ

Create Dao (data access object) Create an interface with name Demographics. Dao @Dao public interface Demographics. Dao { @Query("SELECT * FROM demographcis") List<Demographics> get. All(); There are four annotations � @Query � @Insert � @Update � @Delete to perform CRUD operations Λευτέρης Κουμάκης @Query("SELECT * FROM demographcis where id LIKE Demographics find. By. Name(String name); @Query("SELECT COUNT(*) from demographcis") int count. Users(); @Insert void insert. All(Demographics. . . demographics); @Delete void delete(Demographics user); @Query("DELETE FROM demographcis") public void nuke. Table(); } ΠΟΛΥΜΕΣΙΚΕΣ ΥΠΗΡΕΣΙΕΣ ΣΤΗΝ ΥΓΕΙΑ : name")

Create DB File App. Database Define the tables @Database(entities = {Demographics. class}, version = 1) public abstract class App. Database extends Room. Database { private static App. Database INSTANCE; public abstract Demographics. Dao(); Define the operations over the specific table public static App. Database get. App. Database(Context context) { if (INSTANCE == null) { INSTANCE = Room. database. Builder(context. get. Application. Context(), App. Database. class, "user-database") // allow queries on the main thread. // Don't do this on a real app! See Persistence. Basic. Sample for an example. . allow. Main. Thread. Queries(). build(); } return INSTANCE; } public static void destroy. Instance() { INSTANCE = null; } } Λευτέρης Κουμάκης ΠΟΛΥΜΕΣΙΚΕΣ ΥΠΗΡΕΣΙΕΣ ΣΤΗΝ ΥΓΕΙΑ

Use your DB //Initialize DB db = App. Database. get. App. Database(this); //Clean the table db. Demographics. Dao(). nuke. Table(); //Insert one row instert. User. Data(db, "Test", 25, 187, 80, 2. 002); private void instert. User. Data(App. Database db, String id, int age, int height, int weight, double speed) { Demographics user = new Demographics(); user. set. Id(id); user. set. Age(age); user. set. Height(height); user. set. Weight(weight); user. set. Speed(speed); db. Demographics. Dao(). insert. All(user); } Λευτέρης Κουμάκης ΠΟΛΥΜΕΣΙΚΕΣ ΥΠΗΡΕΣΙΕΣ ΣΤΗΝ ΥΓΕΙΑ