androidnoob androidnoob - 23 days ago 5
Android Question

Adding tables in SQLite android

I was researching on how to add tables in an sqlite database for more information but I can't have a grip on how it was truly done.

I have this code:

package com.example.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.database.sqlite.SQLiteDatabase.CursorFactory;

public class SQLiteAdapter {

public static final String MYDATABASE_NAME = "SCORING";
public static final String MYDATABASE_TABLE = "SCORING_TABLE";
public static final String MYDATABASE_TABLE1 = "Assessment";
public static final int MYDATABASE_VERSION = 3;
public static final String KEY_ID = "_id";
public static final String KEY_CONTENT1 = "Content1";
public static final String KEY_CONTENT2 = "Content2";
public static final String KEY_CONTENT3 = "Content3";

//create table SCORING (ID integer primary key, Content text not null);
private static final String SCRIPT_CREATE_DATABASE =
"create table " + MYDATABASE_TABLE + " ("
+ KEY_ID + " integer primary key autoincrement, "
+ KEY_CONTENT1 + " text not null, "
+ KEY_CONTENT2 + " text not null, "
+ KEY_CONTENT3 + "text not null);";

private SQLiteHelper sqLiteHelper;
private SQLiteDatabase sqLiteDatabase;

private Context context;

public SQLiteAdapter(Context c){
context = c;
}

public SQLiteAdapter openToRead() throws android.database.SQLException {
sqLiteHelper = new SQLiteHelper(context, MYDATABASE_NAME, null, MYDATABASE_VERSION);
sqLiteDatabase = sqLiteHelper.getReadableDatabase();
return this;
}

public SQLiteAdapter openToWrite() throws android.database.SQLException {
sqLiteHelper = new SQLiteHelper(context, MYDATABASE_NAME, null, MYDATABASE_VERSION);
sqLiteDatabase = sqLiteHelper.getWritableDatabase();
return this;
}

public void close(){
sqLiteHelper.close();
}

public long insert(String content1, String content2, String content3){

ContentValues contentValues = new ContentValues();
contentValues.put(KEY_CONTENT1, content1);
contentValues.put(KEY_CONTENT2, content2);
contentValues.put(KEY_CONTENT3, content3);
return sqLiteDatabase.insert(MYDATABASE_TABLE, null, contentValues);
}

public int deleteAll(){
return sqLiteDatabase.delete(MYDATABASE_TABLE, null, null);
}

public Cursor queueAll(){
String[] columns = new String[]{KEY_ID, KEY_CONTENT1, KEY_CONTENT2, KEY_CONTENT3};
Cursor cursor = sqLiteDatabase.query(MYDATABASE_TABLE, columns,
null, null, null, null, null);

return cursor;
}

public class SQLiteHelper extends SQLiteOpenHelper {

public SQLiteHelper(Context context, String name,
CursorFactory factory, int version) {
super(context, name, factory, version);
}

@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL(SCRIPT_CREATE_DATABASE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
// If you need to add a column
if (newVersion > oldVersion) {
db.execSQL("ALTER TABLE SCORING_TABLE ADD COLUMN Content4 TEXT");
}
}
}
}


and for the second one I'm having a hard time inputting data to the second table

package com.example.database;



import com.example.database.R;
import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import android.widget.TextView;
import android.widget.Toast;

public class AndroidSQLite extends Activity {

EditText inputContent2;
TextView textView1, textView2;
Button buttonAdd, buttonDeleteAll;

private SQLiteAdapter mySQLiteAdapter;
ListView listContent;

SimpleCursorAdapter cursorAdapter;
Cursor cursor;

/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);


textView1 = (TextView)findViewById(R.id.textView1);
textView2 = (TextView)findViewById(R.id.textView2);
inputContent2 = (EditText)findViewById(R.id.content2);
buttonAdd = (Button)findViewById(R.id.add);
buttonDeleteAll = (Button)findViewById(R.id.deleteall);
listContent = (ListView)findViewById(R.id.contentlist);

mySQLiteAdapter = new SQLiteAdapter(this);
mySQLiteAdapter.openToWrite();

cursor = mySQLiteAdapter.queueAll();
String[] from = new String[]{SQLiteAdapter.KEY_ID, SQLiteAdapter.KEY_CONTENT1, SQLiteAdapter.KEY_CONTENT2, SQLiteAdapter.KEY_CONTENT3};
int[] to = new int[]{R.id.id, R.id.text1, R.id.text2, R.id.text3};
cursorAdapter =
new SimpleCursorAdapter(this, R.layout.row, cursor, from, to);
listContent.setAdapter(cursorAdapter);

buttonAdd.setOnClickListener(buttonAddOnClickListener);
buttonDeleteAll.setOnClickListener(buttonDeleteAllOnClickListener);



}

Button.OnClickListener buttonAddOnClickListener
= new Button.OnClickListener(){

@Override
public void onClick(View arg0) {
// TODO Auto-generated method stub
int a=Integer.parseInt(textView1.getText().toString());
int b=a+2;
String s1 = String.valueOf(b);
textView1.setText(s1);
Toast.makeText(getApplicationContext(), "Wrong",
Toast.LENGTH_SHORT).show();

String data1 = textView1.getText().toString();
String data2 = inputContent2.getText().toString();
String data3 = textView2.getText().toString();
mySQLiteAdapter.insert(data1, data2, data3);
updateList();



}

};


Button.OnClickListener buttonDeleteAllOnClickListener
= new Button.OnClickListener(){

@Override
public void onClick(View arg0) {
// TODO Auto-generated method stub
mySQLiteAdapter.deleteAll();
updateList();
}

};

@Override
protected void onDestroy() {
// TODO Auto-generated method stub
super.onDestroy();
mySQLiteAdapter.close();
}



private void updateList(){
cursor.requery();
}

}

Answer

I personally found using ContentProviders to be a clearer and easier to maintain way to manage multiple tables.

There is a nice article here that explains ContentProviders and other ways of dealing with android database/

http://www.vogella.com/articles/AndroidSQLite/article.html#tutorialusecp

Comments