To create a db , first create a schema
- create a class that extends SQLiteOpenHelper
- create an object of SQLdatabse and initialise using getwritabledatabase
- create sepatrate methods for CRUD operation
Schema class
package com.example.avinash.sql2;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.sql.SQLException;
/**
* Created by Avinash on 01-01-2015.
*/
public class DBAdapter {
DBHelper helper;
public DBAdapter(Context c) {
helper = new DBHelper(c);
}
public long insertdata(String u,String p)
{ SQLiteDatabase sqldb=helper.getWritableDatabase();
ContentValues cv=new ContentValues();
cv.put(DBHelper.name,u);
cv.put(DBHelper.password,p);
long id=sqldb.insert(DBHelper.tname,null,cv);
return id;
}
public String getalldata()
{
SQLiteDatabase db=helper.getWritableDatabase();
String[] col={helper.uid,helper.name,helper.password};
Cursor cursor=db.query(helper.tname,col,null,null,null,null,null);
StringBuffer buffer=new StringBuffer();
while (cursor.moveToNext())
{
int cid=cursor.getInt(0);
String name=cursor.getString(1);
String pass=cursor.getString(2);
buffer.append(cid+" "+name+" "+pass+"\n");
}
return buffer.toString();
}
public String getData(String name,String passsword)
{ SQLiteDatabase db=helper.getWritableDatabase();
String[] col={helper.uid};
String query=helper.name+"=? AND "+helper.password+"=?";
String[] selectargs={name,passsword};
Cursor cursor=db.query(helper.tname,col,query,selectargs,null,null,null,null);
StringBuffer buffer=new StringBuffer();
while (cursor.moveToNext())
{
int cid=cursor.getInt(0);
buffer.append(cid+"\n");
}
return buffer.toString();
}
public int update(String on,String nn) {
SQLiteDatabase db=helper.getWritableDatabase();
ContentValues cv=new ContentValues();
cv.put(helper.name,nn);
String[] wherearg={on};
int count=db.update(helper.tname,cv,helper.name+" =? ",wherearg);
return count;
}
public void delete(String name)
{ SQLiteDatabase db=helper.getWritableDatabase();
String[] wherearg={name};
db.delete(helper.tname,helper.name+" =? ",wherearg);
}
public static class DBHelper extends SQLiteOpenHelper {
private static final String dbname = "mydn";
private static final String tname = "mytable";
private static final int dbver = 5;
private static final String uid = "_id";
private static final String name = "name";
private static final String password = "password";
Context context;
public DBHelper(Context context) {
super(context, dbname, null, dbver);
this.context = context;
}
@Override
public void onCreate(SQLiteDatabase db) {
try {
db.execSQL("CREATE TABLE " + tname + " (" + uid + " INTEGER PRIMARY KEY AUTOINCREMENT, " + name + " VARCHAR(255), " +password + " VARCHAR(255));");
Message.show(context, "onCreate called");
} catch (Exception e) {
Message.show(context, "Oops! Check onCreate!");
}
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
try {
db.execSQL("DROP TABLE IF EXISTS " + tname);
onCreate(db);
Message.show(context, "onUpgrade called");
} catch (Exception e) {
Message.show(context, "Oops!check onUpgrade!");
}
}
}
}
MainAcitivity Class
package com.example.avinash.sql2;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.ActionBarActivity;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
public class MainActivity extends ActionBarActivity {
EditText un, pw,filter;
Button add,view,view2,update,delete;
DBAdapter dba;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
initialise();
add.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
String user = un.getText().toString();
String pass = pw.getText().toString();
long id = dba.insertdata(user, pass);
if (id < 0)
Message.show(MainActivity.this, "Jhol h bhaiya! Tanik code check karba");
else
Message.show(MainActivity.this, "Data inserted successfully");
}
});
view.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
Message.show(MainActivity.this,dba.getalldata());
}
});
view2.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
String s=filter.getText().toString();
String sub1=s.substring(0,s.indexOf(" "));
String sub2=s.substring(s.indexOf(" ")+1);
Message.show(MainActivity.this,dba.getData(sub1,sub2));
}
});
update.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
String s=filter.getText().toString();
String sub1=s.substring(0,s.indexOf(" "));
String sub2=s.substring(s.indexOf(" ")+1);
dba.update(sub1,sub2);
Message.show(MainActivity.this,"Updation successful");
}
});
delete.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
String s=filter.getText().toString();
dba.delete(s);
Message.show(MainActivity.this,"Deletion successful");
}
});
}
public void initialise() {
un = (EditText) findViewById(R.id.username);
pw = (EditText) findViewById(R.id.password);
filter = (EditText) findViewById(R.id.filter);
add = (Button) findViewById(R.id.addbtn);
dba=new DBAdapter(MainActivity.this);
view = (Button) findViewById(R.id.viewbtn);
update = (Button) findViewById(R.id.update);
delete = (Button) findViewById(R.id.delete);
view2 = (Button) findViewById(R.id.view2btn);
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.menu_main, menu);
return true;
}
@Override
public boolean onOptionsItemSelected(MenuItem item) {
// Handle action bar item clicks here. The action bar will
// automatically handle clicks on the Home/Up button, so long
// as you specify a parent activity in AndroidManifest.xml.
int id = item.getItemId();
//noinspection SimplifiableIfStatement
if (id == R.id.action_settings) {
return true;
}
return super.onOptionsItemSelected(item);
}
}
XML layout
<ScrollView
xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
android:paddingBottom="@dimen/activity_vertical_margin"
android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin"
tools:context=".MainActivity">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="USERNAME" />
<EditText
android:id="@+id/username"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginBottom="10dp" />
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="PASSWORD" />
<EditText
android:id="@+id/password"
android:layout_width="match_parent"
android:layout_height="wrap_content" />
<Button
android:id="@+id/addbtn"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="ADD USER IN DB" />
<Button
android:id="@+id/viewbtn"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="View Details" />
<EditText
android:id="@+id/filter"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="add space between two parameters" />
<Button
android:id="@+id/view2btn"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Show ID of above combination" />
<Button
android:id="@+id/update"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Update the first word with second" />
<Button
android:id="@+id/delete"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Delete the rows with above name" />
</LinearLayout>
</ScrollView>
Comments
Post a Comment