SQLite数据库的创建

发布时间：2020-12-12 20:11:39 所属栏目：百科来源：网络整理

导读：SQLite数据库的创建: public class PersonDBOpenHelp extends SQLiteOpenHelper { private static final String TAG = null ; public PersonDBOpenHelp(Context context) { //1.上下文 //2.数据库的名称 //3.数据库查询结果的游标工厂 //4.数据库版本=1 supe

SQLite数据库的创建:
public class PersonDBOpenHelp extends SQLiteOpenHelper {
private static final String TAG = null ;
public PersonDBOpenHelp(Context context) { //1.上下文 //2.数据库的名称 //3.数据库查询结果的游标工厂 //4.数据库版本>=1 super (context, "person.db" , null ,1); } /** * 数据库在第一次被创建的时候调用的方法 */ @Override public void onCreate(SQLiteDatabase db) { Log.i( TAG , "数据库被创建" ); db.execSQL( "create table person(id integer primary key autoincrement,name varchar(20),phone varchar(20))" );
}
@Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}

----------------------------------------------------------------------------------------------------------
/** * 创建数据库 * @author Administrator * */
public class MyHelper extends SQLiteOpenHelper {
public MyHelper(Context context) { /* * 参数1: 当前应用的环境,用来确定数据库目录 * 参数2: 数据库文件的名字 * 参数3: 游标工厂,用来创建结果集对象,null代表默认 * 参数4: 数据库版本,从1开始 */ super (context, "itheima.db" , null ,2); }
@Override public void onCreate(SQLiteDatabase db) { // 在数据库创建的时候执行 System. out .println( "onCreate" ); db.execSQL( "CREATE TABLE account(_id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(20))" ); // 执行一条SQL语句 }
@Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // 在数据库更新的时候执行 System. out .println( "onUpgrade" ); db.execSQL( "ALTER TABLE account ADD balance INTEGER" ); } }

----------------------------------------------------------------------------
/** * 数据库操作 * @author Administrator * */ public class AccountDao {
private MyHelper helper ; public AccountDao(Context context) { helper = new MyHelper(context); }
/** * 增加 * @param a * @return */ public int insert(Account a) { SQLiteDatabase db = helper .getWritableDatabase(); db.execSQL( "INSERT INTO account(name,balance) VALUES(?,?)" , new Object[] { a.getName(),a.getBalance() }); Cursor c = db.rawQuery( "SELECT _id FROM account ORDER BY _id DESC LIMIT 1" , null ); c.moveToNext(); int id = c.getInt(0); db.close(); return id; }
/** * 删除 * @param id */ public void delete( int id) { SQLiteDatabase db = helper .getWritableDatabase(); db.execSQL( "DELETE FROM account WHERE _id=?" , new Object[] { id }); db.close(); }
/** * 修改 * @param a */ public void update(Account a) { SQLiteDatabase db = helper .getWritableDatabase(); db.execSQL( "UPDATE account SET name=?,balance=? WHERE _id=?" ,a.getBalance(),a.getId() }); db.close(); }
/** * 查 * @param id * @return */ public Account query( int id) { SQLiteDatabase db = helper .getReadableDatabase(); Cursor c = db.rawQuery( "SELECT name,balance FROM account WHERE _id=?" , new String[] { id + "" }); Account a = null ; if (c.moveToNext()) { String name = c.getString(0); int balance = c.getInt(1); a = new Account(id,name,balance); } c.close(); db.close(); return a; }
/** * 查询所有 * @return */ public List<Account> queryAll() { List<Account> list = new ArrayList<Account>(); SQLiteDatabase db = helper .getReadableDatabase(); Cursor c = db.rawQuery( "SELECT _id,balance FROM account" , null ); while (c.moveToNext()) { int id = c.getInt(0); String name = c.getString(1); int balance = c.getInt(2); Account a = new Account(id,balance); list.add(a); } c.close(); db.close(); return list; }
/** * 分页查询 * @param pageNum * @param pageSize * @return */ public List<Account> queryPage( int pageNum, int pageSize) { String index = (pageNum - 1) * pageSize + "" ; String count = pageSize + "" ;
List<Account> list = new ArrayList<Account>(); SQLiteDatabase db = helper .getReadableDatabase(); Cursor c = db.rawQuery( "SELECT * FROM account LIMIT ?,?" , new String[] { index,count }); while (c.moveToNext()) { int id = c.getInt(c.getColumnIndex( "_id" )); // 获取数据时必须通过列的索引获取,如果想通过名字获取就需要先获取索引 String name = c.getString(1); int balance = c.getInt(2); Account a = new Account(id,balance); list.add(a); } c.close(); db.close(); return list; }
/** * 查询数量 * @return */ public int queryCount() { SQLiteDatabase db = helper .getReadableDatabase(); Cursor c = db.rawQuery( "SELECT COUNT(*) FROM account" , null ); c.moveToNext(); int count = c.getInt(0); c.close(); db.close(); return count; }
/** * 数据库事物 * @param fromId * @param toId * @param amount */ public void remit( int fromId, int toId, int amount) { SQLiteDatabase db = helper .getWritableDatabase(); try { db.beginTransaction(); // 开启事务 db.execSQL( "UPDATE account SET balance=balance-? WHERE _id=?" , new Object[] { amount,fromId }); //System.out.println(1/0); db.execSQL( "UPDATE account SET balance=balance+? WHERE _id=?" ,toId }); db.setTransactionSuccessful(); // 设置成功标记 } finally { db.endTransaction(); // 结束事务,会把最后一次成功标记之前的操作提交 db.close(); } }
}

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

/** * 数据库操作测试 * @author Administrator * */ public class SQLiteTest extends AndroidTestCase { // 这里不能getContext(),不能获取Context对象,因为还没有调用过setContext() // 测试方法在执行的时候,会先把工程发布到手机,创建测试类对象,调用setContext()方法把当前应用的Context设置进来
public void testCreateDB() { MyHelper helper = new MyHelper(getContext()); SQLiteDatabase db = helper.getWritableDatabase(); db.execSQL( "INSERT INTO account(name,balance) VALUES('Don',10000)" ); /* * 获取数据库对象 * 1.数据库不存在: 创建数据库文件,打开数据库,执行onCrate()方法 * 2.存在版本没变: 打开数据库 * 3.存在版本提升: 打开数据库,执行onUpgrade()方法 */ }
public void testInsert() { AccountDao dao = new AccountDao(getContext()); for ( int i = 101; i <= 200; i++) { dao.insert( new Account( "Test" + i, new Random().nextInt(10000))); } }
public void testUpdate() { Account a3 = new Account(6, "张泽华" ,10006); AccountDao dao = new AccountDao(getContext()); dao.update(a3); }
public void testDelete() { AccountDao dao = new AccountDao(getContext()); dao.delete(1); }
public void testQuery() { AccountDao dao = new AccountDao(getContext()); System. out .println(dao.query(1)); System. out .println(dao.query(2)); System. out .println(dao.query(3)); System. out .println(dao.query(4)); }
public void testQueryAll() { AccountDao dao = new AccountDao(getContext()); List<Account> list = dao.queryAll(); for (Account account : list) { System. out .println(account); } } public void testQueryPage() { AccountDao dao = new AccountDao(getContext()); List<Account> list = dao.queryPage(2,20); for (Account account : list) { System. out .println(account); } } public void testCount() { AccountDao dao = new AccountDao(getContext()); System. out .println(dao.queryCount()); } public void testRemit() { AccountDao dao = new AccountDao(getContext()); dao.remit(2,3,100); }
}

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

public class MainActivity extends Activity {
private List<Account> list ; private AccountDao dao ; private MyAdapter adapter ; private EditText nameET ; private EditText balanceET ; private ListView accountLV ;
@Override protected void onCreate(Bundle savedInstanceState) { super .onCreate(savedInstanceState); setContentView(R.layout. activity_main ); // 查询数据库,得到List<Account> dao = new AccountDao( this ); list = dao .queryAll(); // 获取ListView accountLV = (ListView) findViewById(R.id. accountLV ); // 定义一个适配器(ListAdapter),设置给ListView,适配器自动把数据装到ListView中 adapter = new MyAdapter(); accountLV .setAdapter( adapter ); // 给ListView添加监听器 accountLV .setOnItemClickListener( new MyItemListener()); // 获取两个EditText nameET = (EditText) findViewById(R.id. nameET ); balanceET = (EditText) findViewById(R.id. balanceET ); // 给ADD按钮添加监听器 findViewById(R.id. addIV ).setOnClickListener( new OnClickListener() { public void onClick(View v) { String name = nameET .getText().toString().trim(); String balance = balanceET .getText().toString().trim(); Account a = new Account(name,Integer.parseInt(balance)); // 创建对象 list .add(a); // 装入集合 int id = dao .insert(a); // 插入数据库,得到id a.setId(id); // 设置id adapter .notifyDataSetChanged(); // 刷新界面 nameET .setText( "" ); // 清空文本框 balanceET .setText( "" ); accountLV .setSelection( accountLV .getCount() - 1); // 跳转到最后一个 } }); } private class MyAdapter extends BaseAdapter { public int getCount() { // 获取条目数量 return list .size(); } public Object getItem( int position) { // 用来在事件处理的时候,返回数据 return list .get(position); } public long getItemId( int position) { // 获取条目的id return list .get(position).getId(); } public View getView( int position,View convertView,ViewGroup parent) { // 获取一个条目上的视图 System. out .println( "getView: " + position + ",convertView: " + convertView); View view = convertView != null ? convertView : View.inflate(MainActivity. this ,R.layout. item , null ); // 根据XML生成界面 TextView idTV = (TextView) view.findViewById(R.id. idTV ); // 不要从Activity中获取,一定是view.findViewById() TextView nameTV = (TextView) view.findViewById(R.id. nameTV ); TextView balanceTV = (TextView) view.findViewById(R.id. balanceTV ); final Account a = list .get(position); // 获取指定位置上的对象 idTV.setText(a.getId() + "" ); // 设置文本的时候,注意区分int和String nameTV.setText(a.getName()); balanceTV.setText(a.getBalance() + "" ); view.findViewById(R.id. upIV ).setOnClickListener( new OnClickListener() { public void onClick(View v) { // 点击up按钮时执行 a.setBalance(a.getBalance() + 1); // 修改数据 dao .update(a); // 更新数据库 adapter .notifyDataSetChanged(); // 刷新界面 } }); view.findViewById(R.id. downIV ).setOnClickListener( new OnClickListener() { public void onClick(View v) { a.setBalance(a.getBalance() - 1); dao .update(a); adapter .notifyDataSetChanged(); } }); view.findViewById(R.id. deleteIV ).setOnClickListener( new OnClickListener() { public void onClick(View v) { list .remove(a); dao .delete(a.getId()); adapter .notifyDataSetChanged(); } }); return view; } } private class MyItemListener implements OnItemClickListener { public void onItemClick(AdapterView<?> parent,View view, int position, long id) { Account a = (Account) parent.getItemAtPosition(position); // 获取指定条目上的对象,内部调用Adapter中的getItem() Toast.makeText(getApplicationContext(),a.toString(),Toast. LENGTH_SHORT ).show(); } }
}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!