android tutorial: sqllite example from asset with crud

download: http://www.mediafire.com/?tncc0fdp360xfde

package com.example.sqlliteimport;

import java.util.ArrayList;

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.view.View.OnLongClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.Toast;

public class MainActivity extends Activity {

CRUDonDB mCRUDonDB;

Cursor cursor;

public static ListView lvForDbData;

public static ListArrayAdapter mListArrayAdapter;

ArrayList<String> nameAl = new ArrayList<String>();
ArrayList<Integer> idAl = new ArrayList<Integer>();

Button btAdd;
EditText etInsertData;

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);

lvForDbData = (ListView) findViewById(R.id.lvForDbData);
btAdd = (Button) findViewById(R.id.btAdd);
etInsertData = (EditText) findViewById(R.id.etInsertData);

mCRUDonDB = new CRUDonDB(MainActivity.this);

select_all_data();

mListArrayAdapter = new ListArrayAdapter(MainActivity.this, nameAl,
idAl);

lvForDbData.setAdapter(mListArrayAdapter);

btAdd.setOnClickListener(new OnClickListener() {

@Override
public void onClick(View v) {
mCRUDonDB.insert_into_db(etInsertData.getText().toString().trim() );

// mListArrayAdapter = new ListArrayAdapter(MainActivity.this,
// nameAl, idAl);

select_all_data();
// lvForDbData.setAdapter(mListArrayAdapter);

mListArrayAdapter.notifyDataSetChanged();
// mListArrayAdapter.notifyDataSetInvalidated();

etInsertData.setText("");
}
});

}

void select_all_data() {
cursor = mCRUDonDB.select_all_from_db();

nameAl.clear();
idAl.clear();
if (cursor != null) {
if (cursor.moveToFirst()) {
do {
String name = cursor.getString(cursor
.getColumnIndex("name"));

Integer id = cursor.getInt(cursor.getColumnIndex("id"));

nameAl.add(name);
idAl.add(id);

} while (cursor.moveToNext());
}
}
}

}

-------------------------------------------

package com.example.sqlliteimport;

import java.io.File;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.io.OutputStream;

import java.util.ArrayList;

import android.content.Context;

import android.database.Cursor;

import android.database.SQLException;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteException;

import android.util.Log;

public class CRUDonDB {

String DB_PATH = "";

// String path = "/data/";

String fileName = "ani_db";

// String fileName = "MyDatabase";

private final String DB_NAME = "ani_db";

private final String TABLE_NAME = "animal_info";

// private final String DB_NAME = "ani_db";

// private final String TABLE_NAME = "animal_info";

Context context;

SQLiteDatabase mSQLiteDatabase = null;

ArrayList<String> results = new ArrayList<String>();

Cursor cursorForLimitQuery;

boolean exist = false;

public CRUDonDB(Context _context) {

context = _context;

// DB_PATH = context. ;

// DB_PATH = "mnt/sdcard/Download/";

DB_PATH = "/data/data/" + context.getPackageName() + "/";

try {

exist = checkDataBase();

if (exist == false) {

try {

// Copy the database from assests

copyDataBase();

Log.e("---", "createDatabase database created");

} catch (Exception mIOException) {

mIOException.printStackTrace();

}

} else {

Log.d("---CRUDonDB()------database already exists-----------",

" ");

}

// limit_query();

} catch (SQLiteException se) {

Log.d("-----CRUDonDB()----catch-----------", " ");

se.printStackTrace();

}

// Open the database, so we can query it

public boolean openDataBase() throws SQLException {

String mPath = DB_PATH + DB_NAME;

// Log.v("mPath", mPath);

mSQLiteDatabase = SQLiteDatabase.openDatabase(mPath, null,

SQLiteDatabase.CREATE_IF_NECESSARY);

// mDataBase = SQLiteDatabase.openDatabase(mPath, null,

// SQLiteDatabase.NO_LOCALIZED_COLLATORS);

return mSQLiteDatabase != null;

}

// Check that the database exists here: /data/data/your package/databases/Da

// Name

private boolean checkDataBase() {

Log.d("-----checkDataBase()----", " ");

File dbFile = new File(DB_PATH + DB_NAME);

// Log.v("dbFile", dbFile + " "+ dbFile.exists());

return dbFile.exists();

}

// Copy the database from assets

private void copyDataBase() {

Log.d("---copyDataBase()----", " ");

InputStream mInput = null;

try {

mInput = context.getAssets().open(DB_NAME);

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

String outFileName = DB_PATH + DB_NAME;

OutputStream mOutput = null;

try {

mOutput = new FileOutputStream(outFileName);

} catch (FileNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

byte[] mBuffer = new byte[1024];

int mLength;

try {

while ((mLength = mInput.read(mBuffer)) > 0) {

mOutput.write(mBuffer, 0, mLength);

}

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

try {

mOutput.flush();

mOutput.close();

mInput.close();

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

public void insert_into_db(String _name)

{

openDataBase();

try {

mSQLiteDatabase.execSQL(" INSERT INTO " + TABLE_NAME+ " (name) " + " Values ( " +

"'" + _name + "' )" +

";");

Log.d("---------insert_into_db()-----------", _name.toString());

} catch (SQLException e) {

Log.d("----------SQLException------------", " ");

e.printStackTrace();

}

public Cursor select_all_from_db() {

// Cursor cursor = mSQLiteDatabase.rawQuery("SELECT * FROM "

// + TABLE_NAME

// +" where train_type='"+train_type+"' and shoppingMallName='"+shoppingMallName+"' ORDER BY purchased desc ",

// null);

Cursor cursor = null;

try {

Log.d("----try-----select_item_from_db()-----------", " ");

openDataBase();

String sql = "select * from " + TABLE_NAME;

cursor = mSQLiteDatabase.rawQuery(sql, null);

if (cursor != null) {

if (cursor.moveToFirst()) {

do {

String Name = cursor.getString(cursor

.getColumnIndex("name"));

Log.d("---------Name=" + Name + "-----------", " ");

} while (cursor.moveToNext());

} else {

Log.d("---------cursor==null-----------", " ");

}

} catch (SQLiteException se) {

se.printStackTrace();

}

return cursor;

}

public void update_info(Integer _id, String _name) {

openDataBase();

try {

mSQLiteDatabase.execSQL("update " + TABLE_NAME

+ " set name='"+_name+"' where id= '"+_id+"' " );

Log.d("----------update success------------", " ");

} catch (SQLException e) {

Log.d("----------update SQLException="

+ e.getStackTrace().toString() + "------------", " ");

}

public void delete_info(Integer _id)

{

openDataBase();

try {

mSQLiteDatabase.execSQL(" delete from " + TABLE_NAME + " where id= '"+_id+"' ");

Log.d("----------delete success------------", " ");

}

catch (SQLException e)

{

Log.d("----------update SQLException="

+ e.getStackTrace().toString() + "------------", " ");

}

public void limit_query() {

try {

Cursor cursorLimotQuery = mSQLiteDatabase.rawQuery("SELECT * FROM "

+ TABLE_NAME + " where purchased='1' limit 2,4 ", null);

Log.d("----------limit query success------------", " ");

if (cursorLimotQuery != null) {

if (cursorLimotQuery.moveToFirst()) {

do {

String productName = cursorLimotQuery

.getString(cursorLimotQuery

.getColumnIndex("productName"));

Integer purchased = cursorLimotQuery

.getInt(cursorLimotQuery

.getColumnIndex("purchased"));

Log.d("---------productName=" + productName.toString()

+ "-----------",

"purchased=" + purchased.toString());

// results.add("" + productName + ",purchased: " +

// purchased.to);

} while (cursorLimotQuery.moveToNext());

}

// SELECT * FROM `your_table` LIMIT 0, 10

}

} catch (SQLException e) {

Log.d("----------limit query Exception="

+ e.getStackTrace().toString() + "------------", " ");

}

public void close_db() {

openDataBase();

mSQLiteDatabase.close();

}

--------------------------------------------------

package com.example.sqlliteimport;

import java.util.ArrayList;

import android.app.AlertDialog;

import android.content.Context;

import android.content.DialogInterface;

import android.database.Cursor;

import android.view.LayoutInflater;

import android.view.View;

import android.view.View.OnClickListener;

import android.view.View.OnLongClickListener;

import android.view.ViewGroup;

import android.widget.BaseAdapter;

import android.widget.Button;

import android.widget.EditText;

import android.widget.ListView;

import android.widget.TextView;

import android.widget.Toast;

public class ListArrayAdapter extends BaseAdapter {

Context context;

TextView tvName, tvId;

Button btDelete, btEdit;

ArrayList<String> nameAl = new ArrayList<String>();

ArrayList<Integer> idAl = new ArrayList<Integer>();

CRUDonDB mCRUDonDB;

Cursor cursor;

ListArrayAdapter mListArrayAdapter;

ListView lvForDbData;

public ListArrayAdapter(Context _myContext, ArrayList<String> _nameAl,

ArrayList<Integer> _idAl) {

context = _myContext;

nameAl = _nameAl;

idAl = _idAl;

}

@Override

public int getCount() // number of item in gridView

{

return nameAl.size();

}

@Override

public View getView(final int position, View convertView, ViewGroup parent) {

View myView = convertView;

final Integer positionInteger = position + 1;

if (convertView == null) {

LayoutInflater li = (LayoutInflater) context

.getSystemService(Context.LAYOUT_INFLATER_SERVICE);

myView = li.inflate(R.layout.list_item, null);

}

tvName = (TextView) myView.findViewById(R.id.tvName);

tvId = (TextView) myView.findViewById(R.id.tvId);

btDelete = (Button) myView.findViewById(R.id.btDelete);

btEdit = (Button) myView.findViewById(R.id.btEdit);

tvName.setText(nameAl.get(position).toString());

tvId.setText(idAl.get(position).toString());

btDelete.setOnLongClickListener(new OnLongClickListener() {

@Override

public boolean onLongClick(View v) {

Toast.makeText(context, "deleted".toString(),

Toast.LENGTH_SHORT).show();

mCRUDonDB = new CRUDonDB(context);

mCRUDonDB.delete_info((Integer) idAl.get(position));

select_all_data();

MainActivity.mListArrayAdapter.notifyDataSetChanged();

// MainActivity.mListArrayAdapter.notifyDataSetInvalidated();

return false;

}

});

btEdit.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View v)

{

final AlertDialog.Builder alert = new AlertDialog.Builder(context);

final EditText input = new EditText(context);

input.setHint("edit '" + nameAl.get(position).toString() + "' ?");

alert.setView(input);

alert.setPositiveButton("Ok", new DialogInterface.OnClickListener() {

public void onClick(DialogInterface dialog, int whichButton) {

String value = input.getText().toString().trim();

if(!input.getText().toString().equals(""))

{

mCRUDonDB = new CRUDonDB(context);

mCRUDonDB.update_info(idAl.get(position), input.getText().toString().trim() );

select_all_data();

MainActivity.mListArrayAdapter.notifyDataSetChanged();

}

// MainActivity.mListArrayAdapter.notifyDataSetInvalidated();

}

});

alert.setNegativeButton("Cancel", new DialogInterface.OnClickListener() {

public void onClick(DialogInterface dialog, int whichButton) {

dialog.cancel();

}

});

alert.show();

}

});

return myView;

}

void select_all_data() {

cursor = mCRUDonDB.select_all_from_db();

nameAl.clear();

idAl.clear();

if (cursor != null) {

if (cursor.moveToFirst()) {

do {

String name = cursor.getString(cursor

.getColumnIndex("name"));

Integer id = cursor.getInt(cursor.getColumnIndex("id"));

nameAl.add(name);

idAl.add(id);

} while (cursor.moveToNext());

}

@Override

public Object getItem(int arg0) {

// TODO Auto-generated method stub

return null;

}

@Override

public long getItemId(int arg0) {

// TODO Auto-generated method stub

return 0;

}

-----------------------------------

main.xml

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"

xmlns:tools="http://schemas.android.com/tools"

android:layout_width="fill_parent"

android:layout_height="fill_parent" >

<ListView

android:id="@+id/lvForDbData"

android:layout_width="fill_parent"

android:layout_height="300dp" >

</ListView>

<EditText

android:id="@+id/etInsertData"

android:layout_width="200dp"

android:layout_height="50dp"

android:layout_alignParentLeft="true"

android:layout_below="@+id/lvForDbData"

android:layout_marginTop="52dp"

android:ems="10"

android:hint="Add data" >

</EditText>

<Button

android:id="@+id/btAdd"

android:layout_width="fill_parent"

android:layout_height="50dp"

android:layout_alignParentRight="true"

android:layout_alignTop="@+id/etInsertData"

android:layout_marginRight="14dp"

android:layout_toRightOf="@+id/etInsertData"

android:text="Add" />

</RelativeLayout>

-------------------------------------------

<?xml version="1.0" encoding="utf-8"?>

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"

android:layout_width="fill_parent"

android:layout_height="fill_parent" >

<TextView

android:id="@+id/tvId"

android:gravity="center"

android:layout_width="wrap_content"

android:layout_height="50dp"

android:layout_alignParentLeft="true"

android:layout_alignParentTop="true"

android:layout_marginLeft="28dp"

android:text="id" />

<TextView

android:id="@+id/tvName"

android:layout_width="100dp"

android:layout_height="50dp"

android:layout_alignBaseline="@+id/tvId"

android:layout_alignBottom="@+id/tvId"

android:layout_toRightOf="@+id/tvId"

android:gravity="center"

android:paddingRight="10dip"

android:text="Country Abbrev" />

<Button

android:id="@+id/btDelete"

android:layout_width="30dp"

android:layout_height="30dp"

android:layout_alignBottom="@+id/tvId"

android:layout_alignParentRight="true"

android:layout_marginRight="19dp"

android:background="@drawable/close"

android:gravity="center"

android:layout_marginBottom="5dp" />

<Button

android:id="@+id/btEdit"

android:layout_width="30dp"

android:layout_height="30dp"

android:layout_alignBaseline="@+id/btDelete"

android:layout_alignBottom="@+id/btDelete"

android:layout_marginRight="23dp"

android:layout_toLeftOf="@+id/btDelete"

android:background="@drawable/edit" />

</RelativeLayout>

android tutorial

Tuesday, April 2, 2013

sqllite example from asset with crud

No comments:

Post a Comment