JDBC - Database Drivers

JDBC - Database Drivers

SQLLite and Java CS-328 Dick Steflik SQLLite Embedded RDBMS

ACID 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 SQLLite Datastore single, cross platform file (kinda like an MS Access DB)

Definitions Tables Indicies Data SQLite Data Types

This is quite different than the normal SQL data types so please read: http://www.sqlite.org/datatype3.html 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-16BE or UTF-16LE). BLOB. The value is a blob of data, stored exactly as it was input. android.database.sqlite Contains the SQLite database management classes that an application would use to manage its own private database. android.database.sqlite - Classes

SQLiteCloseable - An object created from a SQLiteDatabase that can be closed. SQLiteCursor - A Cursor implementation that exposes results from a query on a SQLiteDatabase.

SQLiteDatabase - Exposes methods to manage a SQLite database. SQLiteOpenHelper - A helper class to manage database creation and version management. SQLiteProgram - A base class for compiled SQLite programs. SQLiteQuery - A SQLite program that represents a query that reads the resulting rows into a CursorWindow. SQLiteQueryBuilder - a convenience class that helps build SQL queries to be sent to SQLiteDatabase objects. SQLiteStatement - A pre-compiled statement against a SQLiteDatabase that can be reused.

android.database.sqlite.SQLiteDatabase Contains the methods for: creating, opening, closing, inserting, updating, deleting and quering an SQLite database These methods are similar to JDBC but more method oriented than what we see with JDBC (remember there is not a RDBMS server running) openOrCreateDatabase( )

This method will open an existing database or create one in the application data area import android.database.sqlite.SQLiteDatabase; SQLiteDatabase myDatabase; myDatabase = openOrCreateDatabase ("my_sqlite_database.db" , SQLiteDatabase.CREATE_IF_NECESSARY , null); SQLite Database Properties Important database configuration options include: version, locale, and thread-safe locking.

import java.util.Locale; myDatabase.setVersion(1); myDatabase.setLockingEnabled(true); myDatabase.SetLocale(Locale.getDefault()); Creating Tables Create a static string containing the SQLite CREATE statement, use the execSQL( ) method to execute it. String createAuthor = "CREAT TABLE authors ( id INTEGER PRIMARY KEY AUTOINCREMENT, fname TEXT,

lname TEXT); myDatabase.execSQL(createAuthor); insert( ) long insert(String table, String nullColumnHack, ContentValues values) import android.content.ContentValues; ContentValues values = new ContentValues( ); values.put("firstname" , "J.K."); values.put("lastname" , "Rowling"); long newAuthorID = myDatabase.insert("tbl_authors" , "" , values);

update( ) int update(String table, ContentValues values, String whereClause, String[ ] whereArgs) public void updateBookTitle(Integer bookId, String newTitle) { ContentValues values = new ContentValues(); values.put("title" , newTitle); myDatabase.update("tbl_books" , values , "id=?" , new String[ ] {bookId.toString() } ); }

delete( ) int delete(String table, String whereClause, String[] whereArgs) public void deleteBook(Integer bookId) { myDatabase.delete("tbl_books" , "id=?" , new String[ ] { bookId.toString( ) } ) ; } 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 SQLiteDatabase class and performs queries on the DB and returns the results in a Cursor object

Cursor c = mdb.query(p1,p2,p3,p4,p5,p6,p7) p1 ; Table name (String)

p2 ; Columns to return (String array) p3 ; WHERE clause (use null for all, ?s for selection args) p4 ; selection arg values for ?s of WHERE clause p5 ; GROUP BY ( null for none) (String) p6 ; HAVING (null unless GROUP BY requires one) (String) p7 ; ORDER BY (null for default ordering)(String) p8 ; LIMIT (null for no limit) (String) Simple Queries SQL - "SELECT * FROM ABC;" SQLite - Cursor c = mdb.query(abc,null,null,null,null,null,null);

SQL - "SELECT * FROM ABC WHERE C1=5" SQLite - Cursor c = mdb.query( abc,null,"c1=?" , new String[ ] {"5"},null,null,null); SQL "SELECT title,id FROM BOOKS ORDER BY title ASC" SQLite String colsToReturn [ ] {"title","id"}; String sortOrder = "title ASC"; Cursor c = mdb.query("books",colsToReturn, null,null,null,null,sortOrder);

Tutorial Here is a good tutorial: http://www.screaming-penguin.com/node/7742

Recently Viewed Presentations

  • Foundations of Design - Cengage

    Foundations of Design - Cengage

    Foundations of Art and Design ... Horizontal Balance Vertical Balance Diagonal Balance Radial Balance Fig. 11.3 I Saw Three Cities by Kay Sage Fig. 11.4 London Portrait by Adil Jain Fig. 11.4 London Portrait by Adil Jain is an example...
  • Chapter 9 Language - people.uwplatt.edu

    Chapter 9 Language - people.uwplatt.edu

    The speaker adjusts the shape of their articulatory apparatus (tongue, lips, mouth) in different ways depending on what sounds come next. (e.g., H in Happy vs. Home). They do this in anticipation of the next sound. ... fighting a liar...
  • STEPS GRID handbook

    STEPS GRID handbook

    All of the below and… can write a flowchart/timeplan which contains most of the information needed to make a successful product. can select and store ingredients correctly, but may need help to select equipment to make the product successfully. can...
  •  1 4 A   2 7 1 1 B

    1 4 A 2 7 1 1 B

    Reduced echelon form Reduced echelon form Reduced echelon form Matrix equations Matrix equations Matrix equations Null space Null space Null space Range
  • Current Issues and Trends in Practice

    Current Issues and Trends in Practice

    "Illness is the night-side of life, a more onerous citizenship. Everyone who is born holds dual citizenship, in the kingdom of the sick. Although we all prefer to use only the good passport, sooner or later each of us is...
  • Escape - UNCW Faculty and Staff Web Pages

    Escape - UNCW Faculty and Staff Web Pages

    I learned a lot by using tkinter. I wish I had used pygame instead because of it's limited abilities and simple class structure. I feel as though I would be better at class stuctures that way. However, I did gain...
  • Bacterial Damage to Host Cells - University of Southern ...

    Bacterial Damage to Host Cells - University of Southern ...

    Parenteral route Bite, puncture, injection, wound Most microbes have a preferred portal of entry Streptococci when inhaled may cause pneumonia; when ingested they do not A few microbes cause illness no matter how they enter May cause different illness based...
  • Arch lengthening and expansion - Bds 2oo7

    Arch lengthening and expansion - Bds 2oo7

    Arch lengthening and expansion Functional appliances Produce active expansion ( usually with either expansion screw or palatal arch) to prevent cross bite formation whilst a CL I relation is being obtained Frankel appliance produces passive expansion only by removing influence...