Working with the SQLite-Database - Cursors
来源:互联网 发布:linux运行sh文件 编辑:程序博客网 时间:2024/05/05 15:54
What you learn: You will learn how to create databases and tables, insert and query datasets in the Android-built-in SQLite-DataBase-Server.
Difficulty: 1 of 5
Questions/Problems: Simply post below...
What it will look like:
Description:
We'll need to to the following things:
- Create a DataBase (generally this is done just once)
- Open the DataBase
- Create a Table (generally this is done just once)
- Insert some Datasets
- Query for some Datasets
- Close the Database
0.) So lets work it out:
We first do some setup. Declaring the DataBases/Tables we are using as final should always be preferred before typing the name to every single statement. (Changes are a lot easier !).
Java:
public class DataBaseWork extends ListActivity {
private final String MY_DATABASE_NAME = "myCoolUserDB";
private final String MY_DATABASE_TABLE = "t_Users";
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle icicle) {
super.onCreate(icicle);
/* Will hold the 'Output' we want to display at the end. */
ArrayList results = new ArrayList();
private final String MY_DATABASE_NAME = "myCoolUserDB";
private final String MY_DATABASE_TABLE = "t_Users";
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle icicle) {
super.onCreate(icicle);
/* Will hold the 'Output' we want to display at the end. */
ArrayList results = new ArrayList();
1.) So lets create the DataBase:
Java:
SQLiteDatabase myDB = null;
try {
/* Create the Database (no Errors if it already exists) */
this.createDatabase(MY_DATABASE_NAME, 1, MODE_PRIVATE, null);
try {
/* Create the Database (no Errors if it already exists) */
this.createDatabase(MY_DATABASE_NAME, 1, MODE_PRIVATE, null);
2.) Having created the DataBase we want to open it:
Java:
/* Open the DB and remember it */
myDB = this.openDatabase(MY_DATABASE_NAME, null);
myDB = this.openDatabase(MY_DATABASE_NAME, null);
3.) Now we create a simple Table with just four columns:
Java:
/* Create a Table in the Database. */
myDB.execSQL("CREATE TABLE IF NOT EXISTS "
+ MY_DATABASE_TABLE
+ " (LastName VARCHAR, FirstName VARCHAR,"
+ " Country VARCHAR, Age INT(3));");
myDB.execSQL("CREATE TABLE IF NOT EXISTS "
+ MY_DATABASE_TABLE
+ " (LastName VARCHAR, FirstName VARCHAR,"
+ " Country VARCHAR, Age INT(3));");
4.) Put two DataSets to the recently created Table:
Java:
/* Add two DataSets to the Table. */
myDB.execSQL("INSERT INTO "
+ MY_DATABASE_TABLE
+ " (LastName, FirstName, Country, Age)"
+ " VALUES ('Gramlich', 'Nicolas', 'Germany', 20);");
myDB.execSQL("INSERT INTO "
+ MY_DATABASE_TABLE
+ " (LastName, FirstName, Country, Age)"
+ " VALUES ('Doe', 'John', 'US', 34);");
myDB.execSQL("INSERT INTO "
+ MY_DATABASE_TABLE
+ " (LastName, FirstName, Country, Age)"
+ " VALUES ('Gramlich', 'Nicolas', 'Germany', 20);");
myDB.execSQL("INSERT INTO "
+ MY_DATABASE_TABLE
+ " (LastName, FirstName, Country, Age)"
+ " VALUES ('Doe', 'John', 'US', 34);");
5.) Having written some DataSets to the Table, we would want to receive them back somewhen. Thr result of a query is a Cursor that can move over all the results returned by the query. We apply Projection (Just the Specified Columns) and Selection (WHERE ...) to it and a LIMIT. Just as we would do in any other SQL-"Dialect":
Java:
/* Query for some results with Selection and Projection. */
Cursor c = myDB.query("SELECT FirstName,Age" +
" FROM " + MY_DATABASE_TABLE
+ " WHERE Age > 10 LIMIT 7;",
null);
Cursor c = myDB.query("SELECT FirstName,Age" +
" FROM " + MY_DATABASE_TABLE
+ " WHERE Age > 10 LIMIT 7;",
null);
6.) Now having queried, we retrieve the ColumIndexes of two Columns calling the getColumnIndex(String);-method of the Cursor:
Java:
/* Get the indices of the Columns we will need */
int firstNameColumn = c.getColumnIndex("FirstName");
int ageColumn = c.getColumnIndex("Age");
/* Check if our result was valid. */
if (c != null) {
/* Check if at least one Result was returned. */
if (c.first()) {
int i = 0;
/* Loop through all Results */
do {
i++;
/* Retrieve the values of the Entry
* the Cursor is pointing to. */
String firstName = c.getString(firstNameColumn);
int age = c.getInt(ageColumn);
/* We can also receive the Name
* of a Column by its Index.
* Makes no sense, as we already
* know the Name, but just to shwo we can */
String ageColumName = c.getColumnName(ageColumn);
/* Add current Entry to results. */
results.add("" + i + ": " + firstName
+ " (" + ageColumName + ": " + age + ")");
} while (c.next());
}
}
int firstNameColumn = c.getColumnIndex("FirstName");
int ageColumn = c.getColumnIndex("Age");
/* Check if our result was valid. */
if (c != null) {
/* Check if at least one Result was returned. */
if (c.first()) {
int i = 0;
/* Loop through all Results */
do {
i++;
/* Retrieve the values of the Entry
* the Cursor is pointing to. */
String firstName = c.getString(firstNameColumn);
int age = c.getInt(ageColumn);
/* We can also receive the Name
* of a Column by its Index.
* Makes no sense, as we already
* know the Name, but just to shwo we can */
String ageColumName = c.getColumnName(ageColumn);
/* Add current Entry to results. */
results.add("" + i + ": " + firstName
+ " (" + ageColumName + ": " + age + ")");
} while (c.next());
}
}
7.) Finally close the DataBase (if it has been opened):
Java:
} catch (FileNotFoundException e) {
} finally {
if (myDB != null)
myDB.close();
}
} finally {
if (myDB != null)
myDB.close();
}
8.) In the end, display our Entries:
Java:
this.setListAdapter(new ArrayAdapter(this,
android.R.layout.simple_list_item_1_small, results));
}
}
android.R.layout.simple_list_item_1_small, results));
}
}
- Working with the SQLite-Database - Cursors
- 与android database 交互(chapter 14-Working with the SQLite Database)
- Fundamentals of Working with the Database
- WORKING WITH SQLite DATABASES
- Working with the DOM
- Working with the Smartphone Emulator
- Working With The WPF Dispatcher
- Working with the Eclipse FileSystem
- Working with the Alert Monitor
- Working with the Workload Monitor
- The Basic Working with TListBox
- windows phone:Using the SQLite database engine with Windows Phone 8 apps
- Working With The File System & Streams (1)
- Working With The File System & Streams (2)
- Working With The File System & Streams (3)
- Working With The File System & Streams (4)
- Working with the JavaFX Scene Graph
- Working with the Central Workload Monitor
- P2P之UDP穿透NAT的原理与实现(英文原版)
- javascript解析XML
- ADSP-BF561软件优化(2):移植x264
- Reading and writing text files
- 《Understanding Linux networking 》1.21Memory caches
- Working with the SQLite-Database - Cursors
- 一个成功项目经理人必做的29件事
- thickobx 源码
- Zend_Auth登陆实现
- HibernateDaoSupport
- 去除烦人的阿里旺旺右键发送菜单
- 人力资源总监HR十项具体工作
- 第0部分 前言 第一章 设计模式介绍
- DNS Server