- package com.example.sqlite;
- import android.os.Bundle;
- import android.app.Activity;
- import android.content.Context;
- import android.database.Cursor;
- import android.database.sqlite.SQLiteDatabase;
- import android.database.sqlite.SQLiteDatabase.CursorFactory;
- import android.database.sqlite.SQLiteOpenHelper;
- import android.database.sqlite.SQLiteStatement;
- import android.view.Menu;
- import android.view.View;
- import android.widget.AdapterView;
- import android.widget.AdapterView.OnItemSelectedListener;
- import android.widget.ArrayAdapter;
- import android.widget.ListView;
- import android.widget.Spinner;
- import android.widget.TextView;
- public class MainActivity extends Activity
- {
- private DatabaseHelper helper;
- @Override
- public void onCreate(Bundle savedInstanceState) {
- super.onCreate(savedInstanceState);
- setContentView(R.layout.activity_main);
- helper = new DatabaseHelper(this);
- final SQLiteDatabase db = helper.getReadableDatabase();
- Cursor c = db.query ("StudentTB", new String[]{"student_no"}, null, null, null, null, null);
- //Cursor c = db.rawQuery(sql, null);
- c.moveToFirst();
- CharSequence[] list = new CharSequence[c.getCount()];
- for(int i = 0; i < list.length; i++)
- {
- list[i] = c.getString(0);
- c.moveToNext();
- }
- c.close();
- Spinner spinner = (Spinner)this.findViewById(R.id.Spinner01);
- spinner.setAdapter(new ArrayAdapter<CharSequence>(this,android.R.layout.simple_spinner_item,list));
- spinner.setOnItemSelectedListener(new OnItemSelectedListener()
- {
- public void onItemSelected(AdapterView<?> parent, View view,
- int position, long id) {
- // TODO Auto-generated method stub
- String student_no = ((Spinner)parent).getSelectedItem().toString();
- Cursor c = db.query("StudentTB", new String[]{"student_name"}, "student_no='"+student_no+"'", null, null, null, null);
- c.moveToFirst();
- String student_name = c.getString(0);
- TextView textView = (TextView)findViewById(R.id.TextView01);
- textView.setText(student_name);
- c.close();
-
- c = db.query("CoursesTB", new String[]{"course_name"}, "student_no='"+student_no+"'", null, null, null, null);
- c.moveToFirst();
- CharSequence[] list = new CharSequence[c.getCount()];
- for(int i = 0; i < list.length; i++)
- {
- list[i] = c.getString(0);
- c.moveToNext();
- }
- c.close();
- ArrayAdapter<CharSequence> adapter = new ArrayAdapter<CharSequence>(MainActivity.this,android.R.layout.simple_list_item_1,list);
- ListView listView = (ListView)findViewById(R.id.ListView01);
- listView.setAdapter(adapter);
- }
- public void onNothingSelected(AdapterView<?> arg0) {
- // TODO Auto-generated method stub
-
- }
-
- });
- }
- @Override
- public boolean onCreateOptionsMenu(Menu menu) {
- getMenuInflater().inflate(R.menu.activity_main, menu);
- return true;
- }
- }
- class DatabaseHelper extends SQLiteOpenHelper
- {
- private static final String DATABASE_NAME = "StudentDB";
- private static final int DATABASE_VERSION = 1;
-
- private String[][] STUDENTS = {
- {"A123","Thomas Wang"},
- {"A135","Tony Huang"},
- {"A146","Johnny Kao"},
- {"A158","Mary Liao"}
- };
-
- private String[][] COURSES = {
- {"A123","Computer Science"},
- {"A123","English"},
- {"A123","Mathematic"},
- {"A123","Chinese"},
- {"A135","Computer Science"},
- {"A135","Chinese"},
- {"A135","Japanese"},
- {"A135","Mathematic"},
- {"A146","English"},
- {"A146","Computer Science"},
- {"A146","Mathematic"},
- {"A158","Japanese"},
- {"A158","France"},
- {"A158","Chinese"}
- };
-
- public DatabaseHelper(Context context) {
- super(context, DATABASE_NAME, null, DATABASE_VERSION);
- // TODO Auto-generated constructor stub
- }
- @Override
- public void onCreate(SQLiteDatabase db)
- {
- // TODO Auto-generated method stub
- db.beginTransaction();
- try
- {
- String sql = "create table StudentsTB(student_no text primary key," +
- "student_name text not null);";
- db.execSQL(sql);
-
- SQLiteStatement stmt;
- stmt = db.compileStatement("insert into StudentTB values(?,?);");
- for(String[] data :STUDENTS)
- {
- stmt.bindString(1, data[0]);
- stmt.bindString(2, data[1]);
- stmt.executeInsert();
- }
-
- sql = "create table CoursesTB(student_no text primary key," +
- "course_name text not null);";
- db.execSQL(sql);
-
- stmt = db.compileStatement("insert into CoursesTB values(?,?);");
- for(String[] data :COURSES)
- {
- stmt.bindString(1, data[0]);
- stmt.bindString(2, data[1]);
- stmt.executeInsert();
- }
- }
- catch(Exception e)
- {}
-
- }
- @Override
- public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
- // TODO Auto-generated method stub
-
- }
- }
複製代碼 |