SQL INNER JOIN

SELECT placemark.name
FROM feature
INNER JOIN placemark
ON feature.placemark_id=placemark.id;

SQL INNER JOIN

SELECT placemark.name
FROM feature
INNER JOIN placemark
ON feature.placemark_id=placemark.id;

6a. Java: Generic Type Interface

In this tutorial we will learn how to create an Interface that serves any type of object using Java Generic Types introduced in Java 1.5.

6a. Java: Generic Type Interface

In this tutorial we will learn how to create an Interface that serves any type of object using Java Generic Types introduced in Java 1.5.

Step 1: Create interface

Note you can put it in any package, but this Interface is NOT your project specific, it is not even Android specific.

package com.cyberwalkabout.database;
import java.util.List;
/**
 * Created by uki on 10/11/14.
 * This interface simply assures that we don't forget to implement most important methods.
 * We are using Generic TYPE T as we don't know what objects we will be using in the database.
 * The TYPE T could stand for any object e.g. Book, Person, Address, etc.
 * You could add more methods of your own, or better method parameters.
 */
public interface DatabaseCrud<T> {
   /**
    * Saves an object to the database.
    */
   public void create(T object);
   /**
    * This methods reads one record by id.
    * Please notice it returns Generic Type T.
    */
   public T read(int dbRecordId);
   /**
    * fetches all objects that match the String searchText
    */
   public List<T> fetch(String searchText);
   /**
    * Update given object in the database.
    */
   public int update(T object);
   /**
    * Deletes given object from the database.
    */
   public void delete(T object);
}

Step 2: Implement the Interface in your specific database wrapper code

public class BookSqlHelper extends SQLiteOpenHelper implements DatabaseCrud<Book> {

Make sure you auto-copy JavaDocs from the Interface:

Step 3: Implement your generated methods

The IDE automatically generates method stubs like this:

    /**
     * Deletes given object from the database.
     *
     * @param object
     */
    @Override
    public void delete(Book object) {
     
    }

Now, you only have to fill in the blanks, note I changed name of the object from object to book:

    /**
     * Inserts a Book object to the database.
     * Please note that the Interface uses Generic Type T:
     * public void create(T object);
     */
    @Override
    public void create(Book book) {
        Log.w(TAG + "save()", book.toString());
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(FIELD__TITLE, book.getTitle());
        values.put(FIELD__AUTHOR, book.getAuthor());
        values.put(FIELD__ISBN, book.getIsbn());
        values.put(FIELD__LOCATION, book.getLocation());
        db.insert(TABLE_BOOKS, null, values);
        db.close();
    }

7b. SQLite CRUD - BookShelf app

In this tutorial you will learn basic SQLite CRUD  functions (Create, Read, Update, Delete) for database operations. Actually, we will use Save, Fetch, Update and Delete method names.

7b. SQLite CRUD - BookShelf app

In this tutorial you will learn basic SQLite CRUD  functions (Create, Read, Update, Delete) for database operations. Actually, we will use Save, Fetch, Update and Delete method names.

Step 1: Create a new IntelliJ/Android Studio Project

• project name: Week7
• module name: BookShelf

Step 2: The first version of this app will not have any UI, edit strings.xml

<?xml version="1.0" encoding="utf-8"?>
<resources>
    <string name="app_name">Book Shelf - SQLite example</string>
    <string name="hello_world">See logcat output!</string>
    <string name="action_settings">Settings</string>
</resources>

Step 3: Run the app to make sure everything is OK so far

Step 4: Create new package "model" and new Java class "Book"

In this example we will be operating on the OBJECT Book, therefore we need a model for it.

package com.chicagoandroid.cit299.week7.bookshelf.model;
public class Book {
      private int id;
      private String title;
      private String author;
      private String isbn;
      private String location;
      /**
       * Constructor with no parameters
       */
      public Book() {
      }
      /**
       * Constructor with title and author parameters
       * @param title
       * @param author
       */
      public Book(String title, String author) {
            super();
            this.title = title;
            this.author = author;
      }
      /**
       * Constructor with ISBN parameter
       * @param isbn
       */
      public Book(String isbn) {
            super();
            this.isbn = isbn;
      }

For our convenience we will override toString() method that will show us the content of the Book. 

      @Override
      public String toString() {
            return "Book: id=" + id
                  + "\n title = " + title
                  + "\n author = " + author
                  + "\n isbn = " + isbn;
      }

Step 5: Generate getters and Setters methods for Book.java

Step 6: Create new package "database" and new Java Interface "DatabaseCrud"

package com.cyberwalkabout.database;

import java.util.List;

/**
* Created by uki on 10/11/14.
* This interface simply assures that we don't forget to implement most important methods.
* We are using Generic TYPE T as we don't know what objects we will be using in the database.
* The TYPE T could stand for any object e.g. Book, Person, Address, etc.
* You could add more methods of your own, or better method parameters.
*/
public interface DatabaseCrud<T> {

/**
* Saves an object to the database.
*/
public void create(T object);

/**
* This methods reads one record by id.
* This record has to be in the Database to have id.
* Please notice it returns Generic Type T.
*/
public T read(int dbRecordId);

/**
* fetches all objects that match the String searchText
*/
public List<T> fetch(String searchText);

/**
* Update given object in the database.
*/
public int update(T object);

/**
* Deletes given object from the database.
* This method should wrap delete(int objectDbId);
*/
public void delete(T object);
}

Step 7: Create Java class "BookSqlHelper"

package com.chicagoandroid.cit299.week7.bookshelf.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.chicagoandroid.cit299.week7.bookshelf.model.Book;
import com.cyberwalkabout.database.DatabaseCrud;

import java.nio.Buffer;
import java.util.LinkedList;
import java.util.List;

public class BookSqlHelper extends SQLiteOpenHelper implements DatabaseCrud<Book> {
private static final String TAG = BookSqlHelper.class.getSimpleName();

private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "DB_BOOK_SHELF";
private static final String TABLE_BOOKS = "books";
private static final String FIELD_ID = "id";
private static final String FIELD__TITLE = "title";
private static final String FIELD__AUTHOR = "author";
private static final String FIELD__ISBN = "isbn";
private static final String FIELD__LOCATION = "location";

private static final String[] COLUMNS = { //
FIELD_ID, // 0
FIELD__TITLE, // 1
FIELD__AUTHOR, // 2
FIELD__ISBN, // 3
FIELD__LOCATION // 4
};

public BookSqlHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_BOOK_TABLE = //
"CREATE TABLE " + TABLE_BOOKS + " ( " //
+ FIELD_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " //
+ FIELD__TITLE + " TEXT, " //
+ FIELD__AUTHOR + " TEXT, " //
+ FIELD__ISBN + " TEXT, " //
+ FIELD__LOCATION + " TEXT " //
+ ")";
db.execSQL(CREATE_BOOK_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS books");
this.onCreate(db);
}

Step 8 : Add "create" method of BookSqlHelper.java

    /**
     * Inserts a Book object to the database.
     */
    @Override
    public void create(Book book) {
        Log.w(TAG + "save()", book.toString());
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(FIELD__TITLE, book.getTitle());
        values.put(FIELD__AUTHOR, book.getAuthor());
        values.put(FIELD__ISBN, book.getIsbn());
        values.put(FIELD__LOCATION, book.getLocation());
        db.insert(TABLE_BOOKS, null, values);
        db.close();
    }

Step 9: Implement "read" method of BookSqlHelper.java

    /**
     * This methods reads one record by id.
     *
     * @param dbBookId
     */
    @Override
    public Book read(int dbBookId) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query( //
                TABLE_BOOKS, // String table
                COLUMNS, // String[] columns
                " id = ?", // selection
                new String[]{String.valueOf(dbBookId)}, // String[] selection arguments
                null, // String group by
                null, // String having
                null, // String order by
                null); // String limit
        return getBooksFromCursor(cursor).get(0);
    }

Step 9 : Implement "update" method of BookSqlHelper.java

/**
* Update given Book object in the database.
*/
@Override
public int update(Book book) {

SQLiteDatabase db = this.getWritableDatabase();

ContentValues values = new ContentValues();
values.put("title", book.getTitle());
values.put("author", book.getAuthor());
values.put("isbn", book.getIsbn());
values.put("location", book.getLocation());

int i = db.update( //
TABLE_BOOKS, // String table
values, // ContentValues values - column/value pairs
FIELD_ID + " = ?", // String where clause
new String[]{String.valueOf(book.getId()) // String[] where arguments
});
db.close();
Log.w(TAG + "update(Book book)", book.toString());
return i;
}

Step 10: Implement "delete" method(s)

    /**
     * Deletes given object from the database.
     */
    @Override
    public void delete(Book book) {
        delete(book.getId());
        Log.d(TAG + "delete", book.toString());
    }
    /**
     * Delete database object by it's id.
     *
     * @param bookDbId - database id of the object to be deleted.
     */
    public void delete(int bookDbId) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete( //
                TABLE_BOOKS, // String table
                FIELD_ID + " = ?", // String where clause
                new String[]{String.valueOf(bookDbId) // String[] where arguments
                });
        db.close();
        Log.d(TAG + "delete(int bookDbId)", "ID: " + bookDbId);
    }

SQL: like

Select * from table_a where column_a like '%xxxyyyzz.JPG%'

SQL: like

Select * from table_a where column_a like '%xxxyyyzz.JPG%'

SQL: query occurrences of multiple fields in a table

SELECT id, name , email,

COUNT(id) AS NumOccurrences

FROM student_record

GROUP BY id, name, email

HAVING ( COUNT(id) > 1 );

- student_record is table name.

- id, name, email are column names.

- counting more than one occurrences for above column.

SQL: query occurrences of multiple fields in a table

SELECT id, name , email,

COUNT(id) AS NumOccurrences

FROM student_record

GROUP BY id, name, email

HAVING ( COUNT(id) > 1 );

- student_record is table name.

- id, name, email are column names.

- counting more than one occurrences for above column.

mysql: dropping all tables

I just chatted with our server support and they gave me a simple script to drop all of the tables without removing database permissions which we can use when refreshing the UAT and STAGE databases:

mysql -u uname dbname -e "show tables" | grep -v Tables_in | grep -v "+" | \

gawk '{print "drop table " $1 ";"}' | mysql -u uname dbname

mysql: dropping all tables

I just chatted with our server support and they gave me a simple script to drop all of the tables without removing database permissions which we can use when refreshing the UAT and STAGE databases:

mysql -u uname dbname -e "show tables" | grep -v Tables_in | grep -v "+" | \

gawk '{print "drop table " $1 ";"}' | mysql -u uname dbname

SQL: having count

select * from game g join team_game tg on g.id = tg.game_id group by g.id having count(g.id) > 2

The HAVING clause is used with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.

SQL: having count

select * from game g join team_game tg on g.id = tg.game_id group by g.id having count(g.id) > 2

The HAVING clause is used with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.

SQL: joint statment on 3 different tables

select distinct u.email from user u join season_roster sr on u.id=sr.user_id join team t on t.id=sr.team_id where t.league_id in (3, 13) and u.email like '%@%'  and u.email not like '%test.com';

SQL: joint statment on 3 different tables

select distinct u.email from user u join season_roster sr on u.id=sr.user_id join team t on t.id=sr.team_id where t.league_id in (3, 13) and u.email like '%@%'  and u.email not like '%test.com';

Linux: MySQL case sensitivity

As a matter of principle when writing SQL for mix-OS systems:

USE LOWER CASE ONLY

Example proper case SQL statement:

CREATE TABLE `address` (                       

           `id` bigint(20) NOT NULL auto_increment,     

           `attn_care_of` varchar(255) default NULL,  

           PRIMARY KEY  (`id`)                          

         ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

This is especially important when developing on Windows systems where case does not matter. It is possible to configure MySQL to be case sensitive/insensitive, but that is seldom done.

Linux: MySQL case sensitivity

As a matter of principle when writing SQL for mix-OS systems:

USE LOWER CASE ONLY

Example proper case SQL statement:

CREATE TABLE `address` (                       

           `id` bigint(20) NOT NULL auto_increment,     

           `attn_care_of` varchar(255) default NULL,  

           PRIMARY KEY  (`id`)                          

         ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

This is especially important when developing on Windows systems where case does not matter. It is possible to configure MySQL to be case sensitive/insensitive, but that is seldom done.

SQL: simple database insert statement

insert into org_attribute (name,value,organization,level) values 

('name_a', 'My Company.', '1', 2),

('name_b', 'Your Company', '2', 2);

SQL: simple database insert statement

insert into org_attribute (name,value,organization,level) values 

('name_a', 'My Company.', '1', 2),

('name_b', 'Your Company', '2', 2);