1, Mainactivity
Package Com.example.sqlitetest;import Java.util.list;import Android.os.bundle;import android.app.activity;import Android.view.menu;import Android.view.view;import Android.view.viewgroup;import Android.widget.AdapterView;import Android.widget.baseadapter;import Android.widget.listview;import Android.widget.textview;import Android.widget.toast;import Android.widget.adapterview.onitemclicklistener;public class MainActivity extends Activity {private ListView personlv;private list<person> persons; @Overrideprotected void OnCreate (Bundle Savedinstancestate) {super.oncreate (savedinstancestate); Setcontentview (R.layout.main); Persondao dao = new Persondao (this);p ersons = Dao.queryall1 ();p ERSONLV = (ListView) Findviewbyid (R.ID.PERSONLV); Personlv.setadapter (New Mybaseadapter ());//Display the ListView Personlv.setonitemclicklistener (new Myonitemclicklistener ());} Private class Myonitemclicklistener implements onitemclicklistener{@Overridepublic void Onitemclick (adapterview<? > Parent, View vieW, int Position,long id) {Person p = (person) parent.getitematposition (position); Toast.maketext (Getapplicationcontext (), P.getname (), 1). Show ();}} Private class Mybaseadapter extends baseadapter{//defines a adapter, each person generates an entry, all entries are loaded into the listview @overridepublic int GetCount () {//returns the number of entries to be loaded by the ListView return Persons.size ();} @Overridepublic Object getItem (int position) {//Returns the entry at the specified position return persons.get (position);} @Overridepublic long Getitemid (int position) {//returns the Idreturn position of the entry;} @Overridepublic view GetView (int position, view Convertview, ViewGroup parent) {//To return a view, is because the root node of R.layout.item is a linearlayout, which is a subclass of view view item = View.inflate (Getapplicationcontext (), R.layout.item, NULL); TextView iDTV = (TextView) Item.findviewbyid (R.ID.IDTV); TextView Nametv = (TextView) Item.findviewbyid (R.ID.NAMETV); TextView Balancetv = (TextView) Item.findviewbyid (R.ID.BALANCETV); Person P = persons.get (position), Idtv.settext (P.getid () + "");//need attention here ... If do not add "", will go to R file to find Nametv.settext (P.getname ()); Balancetv.SetText (p.getbalance () + ""); return item;}} @Overridepublic boolean Oncreateoptionsmenu (Menu menu) {//Inflate the menu; This adds items to the action bar if it is PR Esent.getmenuinflater (). Inflate (R.menu.main, menu); return true;}}
2, Dbopenhelper
Package Com.example.sqlitetest;import Android.content.context;import android.database.sqlite.SQLiteDatabase; Import Android.database.sqlite.sqliteopenhelper;public class Dbopenhelper extends Sqliteopenhelper {public Dbopenhelper (Context context) {//The parent class does not have a parameterless constructor and must be shown to call the parameter constructor./* * Because the parent class does not have a parameterless constructor, you must explicitly call the parameter constructor * Parameter 1: Context, which is used to determine the directory where the database file is stored. The database that was created exists in the/data/data/application/databases/xxx.db * Parameter 2: Database file name * Parameter 3: The factory that generated the cursor, NULL is to use the default * Parameter 4: Version of the database, starting from 1 */super (Contex T, "njupt.db", null,3);} /** * Generally, the code that creates the table is placed in onCreate ()/@Overridepublic void OnCreate (Sqlitedatabase db) {System.out.println ("----------- >oncreate ");d b.execsql (" CREATE TABLE person (ID INTEGER PRIMARY KEY autoincrement, name VARCHAR (20)) ");//Execute SQL statement, CREATE TABLE }/** * Modify the table's code in OnUpdate () ... */@Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {System . OUT.PRINTLN ("---------->onupdate");d b.execsql ("ALTER TABLE person ADD balance INTEGER");}}
3, Persondao
Package Com.example.sqlitetest;import Java.util.arraylist;import Java.util.list;import Android.content.contentvalues;import Android.content.context;import Android.database.cursor;import Android.database.sqlite.sqlitedatabase;public class Persondao {private Context context;private dbopenhelper helper; Public Persondao (Context context) {This.context = Context;helper = new Dbopenhelper (context);} public void Insert (person p) {Sqlitedatabase db = Helper.getwritabledatabase ();d b.execsql ("Insert to person (name, Balance) VALUES (?,?) ", new object[] {p.getname (), P.getbalance ()}); Execute SQL statement, insert Db.close ();} /** * In some cases the program will accept a contentvalues, it is convenient to use this storage method ... * @param p */public void Insert1 (person p) {Sqlitedatabase db = Helper . Getwritabledatabase ();/** * Contentvalues: Similar to map, key to column name, value to insert content ... * Why contentvalues is similar to map, actually look at his member variables and put method to know Adds a value to the set. * * @param key * The name of the value to put * @param value * The data for the value to put * * Public void put (string key, String value) {* Mvalues.put (key, value);} */contentvalues values = new Contentval UEs (); Values.put ("Name", P.getname ()), Values.put ("Balance", p.getname ());/** * The second parameter writes a column name casually to handle the case where the values are empty. Because the column name cannot be null */db.insert ("person", "name", "values");//In fact, it is the bottom layer of the use of the way to make a SQL statement. Returns the number of Db.close () of the inserted ID. public void Delete (int id) {Sqlitedatabase db = Helper.getwritabledatabase ();d b.execsql ("Delete from person WHERE id=?", N EW object[] {ID});d b.close ();} public void Delete1 (Int. ID) {Sqlitedatabase db = Helper.getwritabledatabase ();d b.delete ("person", "id=?", New string[]{ ID + ""});d B.close ();} public void Update (person p) {Sqlitedatabase db = Helper.getwritabledatabase ();d b.execsql ("Update person SET name=?, Bala Nce=? WHERE id=? ", new object[] {p.getname (), P.getbalance (), P.getid ()});d b.close ();} public void update1 (person p) {Sqlitedatabase db = Helper.getwritabledatabase (); Contentvalues values = new Contentvalues (), Values.put ("name", P.getname ()), Values.put ("BalancE ", P.getbalance ());d b.update (" person ", Values," id= ", new String[]{p.getid () +" "}); Db.close ();} Public person query (int id) {Sqlitedatabase db = Helper.getreadabledatabase ();//Get database connection, readable cursor c = db.rawquery ("Selec T name, balance from person WHERE id=? ", new string[] {id +" "}); Person p = null;if (C.movetonext ()) {//Determines whether the cursor contains the next record, and if so, moves the cursor back one string name = c.getstring (0);//Gets the data on index NO. 0, Convert to String type//String name = C.getstring (C.getcolumnindex ("name"));//This method is also excellent int balance = C.getint (1);p = new Person (ID , name, balance);} C.close ();d b.close (); return p;} Public person Query1 (int id) {Sqlitedatabase db = Helper.getreadabledatabase ();//Get database connection, readable//cursor c = db.rawquery (" Select name, balance from person WHERE id=? ",//new string[] {id +" "});/** * db.query (table name, column name to query, query criteria, parameters for query criteria, group By,h Aving,order by); */cursor C = db.query ("Person", new string[]{"name", "Balance"}, "Id=?", New String[]{id + ""}, NULL, NULL, NULL); Person p = null;if (C.movetonext ()) {//determines if the cursor contains the next record, and if it contains, the cursorMove backward one string name = c.getstring (0);//Get Data on index NO. 0, convert to String type//String name = C.getstring (C.getcolumnindex ("name"));// This method is also excellent for int balance = C.getint (1);p = new person (ID, name, balance);} C.close ();d b.close (); return p;} Public list<person> Queryall () {Sqlitedatabase db = Helper.getreadabledatabase (); Cursor C = Db.rawquery ("Select ID, name, balance from person", NULL); list<person> persons = new arraylist<person> (); while (C.movetonext ()) {person p = new Person (c.getint (0), C.G Etstring (1), C.getint (2));p Ersons.add (p);} C.close ();d b.close (); return persons;} Public list<person> queryAll1 () {Sqlitedatabase db = Helper.getreadabledatabase ();//cursor C = Db.rawquery (" Select ID, name, balance from person ", NULL); Cursor C = db.query ("person", NULL, NULL, NULL, NULL, NULL, "ID DESC"); list<person> persons = new arraylist<person> (); while (C.movetonext ()) {person p = new Person (c.getint (0), C.G Etstring (1), C.getint (2));p Ersons.add (p);} C.close ();d b.close (); Return persons;} public int Querycount () {Sqlitedatabase db = Helper.getreadabledatabase (),//cursor C = db.rawquery ("Select COUNT (*) from P Erson ", NULL); Cursor C = db.query ("Person", New string[]{"COUNT (*)"}, NULL, NULL, NULL, NULL, NULL); C.movetonext (); int COUNT = C.getint ( 0); C.close ();d b.close (); return count;} public int queryCount1 () {Sqlitedatabase db = Helper.getreadabledatabase (); Cursor C = db.rawquery ("Select COUNT (*) from person", null); C.movetonext (); int COUNT = c.getint (0); C.close ();d b.close (); return count;} Public list<person> querypage (int pagenum, int capacity) {String offset = (pageNum-1) * capacity + ""; String len = capacity + ""; Sqlitedatabase db = Helper.getreadabledatabase (); Cursor C = Db.rawquery ("Select ID, name, balance from person LIMIT?,?", new string[] {offset, len}); list<person> persons = new arraylist<person> (); while (C.movetonext ()) {person p = new Person (c.getint (0), C.G Etstring (1), C.getint (2));p Ersons.add (p);} C.close ();d b.close (); return persons;} Public list<person> queryPage1 (int pagenum,int capacity) {String offset = (pageNum-1) * capacity + ""; String len = capacity + ""; Sqlitedatabase db = Helper.getreadabledatabase ()//cursor c = db.rawquery (//"SELECT ID, name, balance from person LIMIT?, ? ", new string[] {//offset, len}); Cursor C = db.query ("person", NULL, NULL, NULL, NULL, NULL, Null,offset + "," + len); list<person> persons = new arraylist<person> (); while (C.movetonext ()) {person p = new Person (c.getint (0), C.G Etstring (1), C.getint (2));p Ersons.add (p);} C.close ();d b.close (); return persons;} /** * Database Transactions * * from this account remittance to the account, remit amount this more money * * @param from * @param to * @param amount */public void remit (int from , int to, int amount) {Sqlitedatabase db = Helper.getwritabledatabase (); try {db.begintransaction ();//Start Transaction Db.execsql (" UPDATE person SET balance=balance-? WHERE id=? ", new object[] {amount, from});d the B.execsql (" UPDATE person SET balance=balance+? "). WHERE id=? ", new object[] {amount, to});d B.SETTRANSACTIONSUCCEssful ();//SET Transaction success point ... The SQL statement before executing the success point at the end of the transaction} finally {db.endtransaction ();//End Transaction Db.close ();}}
4. Person
Package Com.example.sqlitetest;public class Person {private integer id;private String name;private integer balance; Public person () {super ();} Public person (integer ID, String name, integer balance) {super (); this.id = Id;this.name = Name;this.balance = balance;} @Overridepublic String toString () {return "person [id=" + ID + ", name=" + name + ", balance=" + balance+ "]";} Public Integer GetId () {return ID;} public void SetId (Integer id) {this.id = ID;} Public String GetName () {return name;} public void SetName (String name) {this.name = name;} Public Integer GetBalance () {return balance;} public void Setbalance (Integer balance) {this.balance = balance;}}
5, DBTest
Package Com.example.sqlitetest;import Java.util.list;import Android.test.androidtestcase;public class DBTest extends androidtestcase {public void Testcreatedb () {Dbopenhelper helper = new Dbopenhelper (GetContext ()); Helper.getwritabledatabase ();} /** * It is important to note that in this example, the ID in the person table is self-increasing .... Do not accept ID in person This member variable affects ... */public void Testinsert () {Persondao dao = new Persondao (GetContext ()); for (int i = 1; I < 100; ++i) {Dao.insert (new person (I, "hjd" + I, 45000+i));} Dao.insert (New person (2, "Hjd", 40000)); public void TestInsert1 () {Persondao dao = new Persondao (GetContext ()), for (int i = 0; i <; ++i) {Dao.insert1 (new Pe Rson (i, "hjd" + I, 30000));} Dao.insert1 (New person (3, "DZDP", 10000)); public void Testdelete () {Persondao dao = new Persondao (GetContext ());d ao.delete (1);} public void TestDelete1 () {Persondao dao = new Persondao (GetContext ());d ao.delete1 (1);} public void Testupdate () {Persondao dao = new Persondao (GetContext ()); Person p = new person (2, "Zzt", 10000);d ao.update (p);} Public VOID testUPdate1 () {Persondao dao = new Persondao (GetContext ()); Person p = new person (2, "Hjd", 40000);d ao.update1 (p);} public void Testquery () {Persondao dao = new Persondao (GetContext ()); System.out.println (Dao.query (2));} public void TestQuery1 () {Persondao dao = new Persondao (GetContext ()); System.out.println ("------------>" + dao.query1 (2));} public void Testqueryall () {Persondao dao = new Persondao (GetContext ()); list<person> persons = Dao.queryall (); for (person p:persons) {System.out.println (P);}} public void TestQueryAll1 () {Persondao dao = new Persondao (GetContext ()); list<person> persons = Dao.queryall1 (); for (person p:persons) {System.out.println ("----------->" + P);}} public void Testquerycount () {Persondao dao = new Persondao (GetContext ()); System.out.println ("--------->" + dao.querycount ());} public void TestQueryCount1 () {Persondao dao = new Persondao (GetContext ()); System.out.println ("------->querycount1:" + dao.querycount1 ());} public void Testquerypage () {PersondaoDAO = new Persondao (GetContext ()); list<person> persons = Dao.querypage (5, ten); for (person p:persons) {System.out.println (P);}} public void TestQueryPage1 () {Persondao dao = new Persondao (GetContext ()); list<person> persons = Dao.querypage1 (2), for (person p:persons) {System.out.println ("--------->" + P);}}}
6, Item.xml
<?xml version= "1.0" encoding= "Utf-8"? ><linearlayout xmlns:android= "/http Schemas.android.com/apk/res/android "android:layout_width=" match_parent "android:layout_height=" Match_parent "and roid:orientation= "Horizontal" android:padding= "10DP" > <textview android:id= "@+id/idtv" Android: Layout_width= "0DP" android:layout_height= "Wrap_content" android:layout_weight= "1" android:text= "1" Android:textsize= "20sp"/> <textview android:id= "@+id/nametv" android:layout_width= "0DP" android:layout_height= "Wrap_content" android:layout_weight= "2" android:text= "Zhang San" android:textsize= " 20sp "/> <textview android:id=" @+id/balancetv "android:layout_width=" 0DP "Android:layout_hei ght= "Wrap_content" android:layout_weight= "2" android:text= "50000" android:textsize= "20SP"/></l Inearlayout>
7, Main.xml
<?xml version= "1.0" encoding= "Utf-8"? ><linearlayout xmlns:android= "http://schemas.android.com/apk/res/ Android "Android:layout_width=" Fill_parent "android:layout_height=" fill_parent "android:orientation=" vertical "&G T <linearlayout android:layout_width= "match_parent" android:layout_height= "wrap_content" android:orientation= "H Orizontal "android:padding=" 10DP "> <textview android:layout_width=" 0DP "android:layout_height=" Wrap_content "android:layout_weight=" 1 "android:text=" id "android:textsize=" 20sp "/> <textvi EW android:layout_width= "0DP" android:layout_height= "Wrap_content" android:layout_weight= "2" a ndroid:text= "name" android:textsize= "20sp"/> <textview android:layout_width= "0DP" Android:lay out_height= "Wrap_content" android:layout_weight= "2" android:text= "Salary" android:textsize= "20SP"/>&L T;/linearlayout> < ListView android:id= "@+id/personlv" android:layout_width= "fill_parent" android:layout_height= "Fill_pa Rent "/></linearlayout>
SOURCE Download:
http://download.csdn.net/detail/caihongshijie6/7624007