频道导航

SQLite数据库的创建

2020-06-26 Sqlite 前端之家

前端之家收集整理的这篇文章主要介绍了SQLite数据库的创建，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

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) {

}

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

@H_502_498@

@H_502_498@ /**

@H_502_498@ * 创建数据库

@H_502_498@ * @author Administrator

@H_502_498@ *

@H_502_498@ */

@H_502_498@ @H_502_498@ public class MyHelper extends sqliteOpenHelper {

@H_502_498@

@H_502_498@ public MyHelper(Context context) {

@H_502_498@ /* @H_502_498@ * 参数1: 当前应用的环境,用来确定数据库目录 @H_502_498@ * 参数2: 数据库文件的名字 @H_502_498@ * 参数3: 游标工厂,用来创建结果集对象,null代表默认 @H_502_498@ * 参数4: 数据库版本,从1开始 @H_502_498@ */ @H_502_498@ super (context, "itheima.db" , null ,2); @H_502_498@ } @H_502_498@
@H_502_498@ @Override @H_502_498@ public void onCreate(sqliteDatabase db) { // 在数据库创建的时候执行 @H_502_498@ System. out .println( "onCreate" ); @H_502_498@ db.execsql( "CREATE TABLE account(_id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(20))" ); // 执行一条sql语句 @H_502_498@ } @H_502_498@
@H_502_498@ @Override @H_502_498@ public void onUpgrade(sqliteDatabase db, int oldVersion, int newVersion) { // 在数据库更新的时候执行 @H_502_498@ System. out .println( "onUpgrade" ); @H_502_498@ db.execsql( "ALTER TABLE account ADD balance INTEGER" ); @H_502_498@ } @H_502_498@ @H_502_498@ } @H_502_498@
@H_502_498@
@H_502_498@
@H_502_498@
@H_502_498@ ---------------------------------------------------------------------------- @H_502_498@
@H_502_498@ @H_502_498@ /** @H_502_498@ * 数据库操作 @H_502_498@ * @author Administrator @H_502_498@ * @H_502_498@ */ @H_502_498@ public class AccountDao { @H_502_498@ @H_502_498@
@H_502_498@ @H_502_498@ private MyHelper helper ; @H_502_498@ public AccountDao(Context context) { @H_502_498@ helper = new MyHelper(context); @H_502_498@ } @H_502_498@
@H_502_498@ /** @H_502_498@ * 增加 @H_502_498@ * @param a @H_502_498@ * @return @H_502_498@ */ @H_502_498@ public int insert(Account a) { @H_502_498@ sqliteDatabase db = helper .getWritableDatabase(); @H_502_498@ db.execsql( "INSERT INTO account(name,balance) VALUES(?,?)" , new Object[] { a.getName(),a.getBalance() }); @H_502_498@ Cursor c = db.rawQuery( "SELECT _id FROM account ORDER BY _id DESC LIMIT 1" , null ); @H_502_498@ c.moveToNext(); @H_502_498@ int id = c.getInt(0); @H_502_498@ db.close(); @H_502_498@ return id; @H_502_498@ } @H_502_498@
@H_502_498@ @H_502_498@ /** @H_502_498@ * 删除 @H_502_498@ * @param id @H_502_498@ */ @H_502_498@ public void delete( int id) { @H_502_498@ sqliteDatabase db = helper .getWritableDatabase(); @H_502_498@ db.execsql( "DELETE FROM account WHERE _id=?" , new Object[] { id }); @H_502_498@ db.close(); @H_502_498@ } @H_502_498@
@H_502_498@ /** @H_502_498@ * 修改 @H_502_498@ * @param a @H_502_498@ */ @H_502_498@ public void update(Account a) { @H_502_498@ sqliteDatabase db = helper .getWritableDatabase(); @H_502_498@ db.execsql( "UPDATE account SET name=?,balance=? WHERE _id=?" ,a.getBalance(),a.getId() }); @H_502_498@ db.close(); @H_502_498@ } @H_502_498@
@H_502_498@ /** @H_502_498@ * 查 @H_502_498@ * @param id @H_502_498@ * @return @H_502_498@ */ @H_502_498@ public Account query( int id) { @H_502_498@ sqliteDatabase db = helper .getReadableDatabase(); @H_502_498@ Cursor c = db.rawQuery( "SELECT name,balance FROM account WHERE _id=?" , new String[] { id + "" }); @H_502_498@ Account a = null ; @H_502_498@ if (c.moveToNext()) { @H_502_498@ String name = c.getString(0); @H_502_498@ int balance = c.getInt(1); @H_502_498@ a = new Account(id,name,balance); @H_502_498@ } @H_502_498@ c.close(); @H_502_498@ db.close(); @H_502_498@ return a; @H_502_498@ } @H_502_498@
@H_502_498@ /** @H_502_498@ * 查询所有 @H_502_498@ * @return @H_502_498@ */ @H_502_498@ public List<Account> queryAll() { @H_502_498@ List<Account> list = new ArrayList<Account>(); @H_502_498@ sqliteDatabase db = helper .getReadableDatabase(); @H_502_498@ Cursor c = db.rawQuery( "SELECT _id,balance FROM account" , null ); @H_502_498@ while (c.moveToNext()) { @H_502_498@ int id = c.getInt(0); @H_502_498@ String name = c.getString(1); @H_502_498@ int balance = c.getInt(2); @H_502_498@ Account a = new Account(id,balance); @H_502_498@ list.add(a); @H_502_498@ } @H_502_498@ c.close(); @H_502_498@ db.close(); @H_502_498@ return list; @H_502_498@ } @H_502_498@
@H_502_498@ /** @H_502_498@ * 分页查询 @H_502_498@ * @param pageNum @H_502_498@ * @param pageSize @H_502_498@ * @return @H_502_498@ */ @H_502_498@ public List<Account> queryPage( int pageNum, int pageSize) { @H_502_498@ String index = (pageNum - 1) * pageSize + "" ; @H_502_498@ String count = pageSize + "" ; @H_502_498@
@H_502_498@ List<Account> list = new ArrayList<Account>(); @H_502_498@ sqliteDatabase db = helper .getReadableDatabase(); @H_502_498@ Cursor c = db.rawQuery( "SELECT * FROM account LIMIT ?,?" , new String[] { index,count }); @H_502_498@ while (c.moveToNext()) { @H_502_498@ int id = c.getInt(c.getColumnIndex( "_id" )); // 获取数据时必须通过列的索引获取,如果想通过名字获取就需要先获取索引 @H_502_498@ String name = c.getString(1); @H_502_498@ int balance = c.getInt(2); @H_502_498@ Account a = new Account(id,balance); @H_502_498@ list.add(a); @H_502_498@ } @H_502_498@ c.close(); @H_502_498@ db.close(); @H_502_498@ return list; @H_502_498@ } @H_502_498@
@H_502_498@ /** @H_502_498@ * 查询数量 @H_502_498@ * @return @H_502_498@ */ @H_502_498@ public int queryCount() { @H_502_498@ sqliteDatabase db = helper .getReadableDatabase(); @H_502_498@ Cursor c = db.rawQuery( "SELECT COUNT(*) FROM account" , null ); @H_502_498@ c.moveToNext(); @H_502_498@ int count = c.getInt(0); @H_502_498@ c.close(); @H_502_498@ db.close(); @H_502_498@ return count; @H_502_498@ } @H_502_498@
@H_502_498@ /** @H_502_498@ * 数据库事物 @H_502_498@ * @param fromId @H_502_498@ * @param toId @H_502_498@ * @param amount @H_502_498@ */ @H_502_498@ public void remit( int fromId, int toId, int amount) { @H_502_498@ sqliteDatabase db = helper .getWritableDatabase(); @H_502_498@ try { @H_502_498@ db.beginTransaction(); // 开启事务 @H_502_498@ db.execsql( "UPDATE account SET balance=balance-? WHERE _id=?" , new Object[] { amount,fromId }); //System.out.println(1/0); @H_502_498@ db.execsql( "UPDATE account SET balance=balance+? WHERE _id=?" ,toId }); @H_502_498@ db.setTransactionSuccessful(); // 设置成功标记 @H_502_498@ } finally { @H_502_498@ db.endTransaction(); // 结束事务,会把最后一次成功标记之前的操作提交 @H_502_498@ db.close(); @H_502_498@ } @H_502_498@ } @H_502_498@
@H_502_498@ } @H_502_498@
@H_502_498@
@H_502_498@
@H_502_498@
@H_502_498@ -------------------------------------------------------------------------------------- @H_502_498@
@H_502_498@
@H_502_498@
@H_502_498@
@H_502_498@
@H_502_498@ @H_502_498@ /** @H_502_498@ * 数据库操作测试 @H_502_498@ * @author Administrator @H_502_498@ * @H_502_498@ */ @H_502_498@ public class sqliteTest extends AndroidTestCase { @H_502_498@ // 这里不能getContext(),不能获取Context对象,因为还没有调用过setContext() @H_502_498@ // 测试方法在执行的时候,会先把工程发布到手机,创建测试类对象,调用setContext()方法把当前应用的Context设置进来 @H_502_498@
@H_502_498@ public void testCreateDB() { @H_502_498@ MyHelper helper = new MyHelper(getContext()); @H_502_498@ sqliteDatabase db = helper.getWritableDatabase(); @H_502_498@ db.execsql( "INSERT INTO account(name,balance) VALUES('Don',10000)" ); @H_502_498@ /* @H_502_498@ * 获取数据库对象 @H_502_498@ * 1.数据库不存在: 创建数据库文件,打开数据库,执行onCrate()方法 @H_502_498@ * 2.存在版本没变: 打开数据库 @H_502_498@ * 3.存在版本提升: 打开数据库,执行onUpgrade()方法 @H_502_498@ */ @H_502_498@ } @H_502_498@
@H_502_498@ public void testInsert() { @H_502_498@ AccountDao dao = new AccountDao(getContext()); @H_502_498@ for ( int i = 101; i <= 200; i++) { @H_502_498@ dao.insert( new Account( "Test" + i, new Random().nextInt(10000))); @H_502_498@ } @H_502_498@ } @H_502_498@
@H_502_498@ public void testUpdate() { @H_502_498@ Account a3 = new Account(6, "张泽华" ,10006); @H_502_498@ AccountDao dao = new AccountDao(getContext()); @H_502_498@ dao.update(a3); @H_502_498@ } @H_502_498@
@H_502_498@ public void testDelete() { @H_502_498@ AccountDao dao = new AccountDao(getContext()); @H_502_498@ dao.delete(1); @H_502_498@ } @H_502_498@
@H_502_498@ public void testQuery() { @H_502_498@ AccountDao dao = new AccountDao(getContext()); @H_502_498@ System. out .println(dao.query(1)); @H_502_498@ System. out .println(dao.query(2)); @H_502_498@ System. out .println(dao.query(3)); @H_502_498@ System. out .println(dao.query(4)); @H_502_498@ } @H_502_498@
@H_502_498@ public void testQueryAll() { @H_502_498@ AccountDao dao = new AccountDao(getContext()); @H_502_498@ List<Account> list = dao.queryAll(); @H_502_498@ for (Account account : list) { @H_502_498@ System. out .println(account); @H_502_498@ } @H_502_498@ } @H_502_498@ @H_502_498@ public void testQueryPage() { @H_502_498@ AccountDao dao = new AccountDao(getContext()); @H_502_498@ List<Account> list = dao.queryPage(2,20); @H_502_498@ for (Account account : list) { @H_502_498@ System. out .println(account); @H_502_498@ } @H_502_498@ } @H_502_498@ @H_502_498@ public void testCount() { @H_502_498@ AccountDao dao = new AccountDao(getContext()); @H_502_498@ System. out .println(dao.queryCount()); @H_502_498@ } @H_502_498@ @H_502_498@ public void testRemit() { @H_502_498@ AccountDao dao = new AccountDao(getContext()); @H_502_498@ dao.remit(2,3,100); @H_502_498@ } @H_502_498@
@H_502_498@ } @H_502_498@
@H_502_498@
@H_502_498@
@H_502_498@ ---------------------------------------------------------------------------------------------------- @H_502_498@
@H_502_498@
@H_502_498@ @H_502_498@ public class MainActivity extends Activity { @H_502_498@
@H_502_498@ private List<Account> list ; @H_502_498@ private AccountDao dao ; @H_502_498@ private MyAdapter adapter ; @H_502_498@ private EditText nameET ; @H_502_498@ private EditText balanceET ; @H_502_498@ private ListView accountLV ; @H_502_498@
@H_502_498@ @Override @H_502_498@ protected void onCreate(Bundle savedInstanceState) { @H_502_498@ super .onCreate(savedInstanceState); @H_502_498@ setContentView(R.layout. activity_main ); @H_502_498@ @H_502_498@ // 查询数据库,得到List<Account> @H_502_498@ dao = new AccountDao( this ); @H_502_498@ list = dao .queryAll(); @H_502_498@ @H_502_498@ // 获取ListView @H_502_498@ accountLV = (ListView) findViewById(R.id. accountLV ); @H_502_498@ @H_502_498@ // 定义一个适配器(ListAdapter),设置给ListView,适配器自动把数据装到ListView中 @H_502_498@ adapter = new MyAdapter(); @H_502_498@ accountLV .setAdapter( adapter ); @H_502_498@ @H_502_498@ // 给ListView添加监听器 @H_502_498@ accountLV .setOnItemClickListener( new MyItemListener()); @H_502_498@ @H_502_498@ // 获取两个EditText @H_502_498@ nameET = (EditText) findViewById(R.id. nameET ); @H_502_498@ balanceET = (EditText) findViewById(R.id. balanceET ); @H_502_498@ @H_502_498@ // 给ADD按钮添加监听器 @H_502_498@ findViewById(R.id. addIV ).setOnClickListener( new OnClickListener() { @H_502_498@ public void onClick(View v) { @H_502_498@ String name = nameET .getText().toString().trim(); @H_502_498@ String balance = balanceET .getText().toString().trim(); @H_502_498@ Account a = new Account(name,Integer.parseInt(balance)); // 创建对象 @H_502_498@ list .add(a); // 装入集合 @H_502_498@ int id = dao .insert(a); // 插入数据库,得到id @H_502_498@ a.setId(id); // 设置id @H_502_498@ adapter .notifyDataSetChanged(); // 刷新界面 @H_502_498@ nameET .setText( "" ); // 清空文本框 @H_502_498@ balanceET .setText( "" ); @H_502_498@ accountLV .setSelection( accountLV .getCount() - 1); // 跳转到最后一个 @H_502_498@ } @H_502_498@ }); @H_502_498@ } @H_502_498@ @H_502_498@ private class MyAdapter extends BaseAdapter { @H_502_498@ public int getCount() { // 获取条目数量 @H_502_498@ return list .size(); @H_502_498@ } @H_502_498@ public Object getItem( int position) { // 用来在事件处理的时候,返回数据 @H_502_498@ return list .get(position); @H_502_498@ } @H_502_498@ public long getItemId( int position) { // 获取条目的id @H_502_498@ return list .get(position).getId(); @H_502_498@ } @H_502_498@ public View getView( int position,View convertView,ViewGroup parent) { // 获取一个条目上的视图 @H_502_498@ System. out .println( "getView: " + position + ",convertView: " + convertView); @H_502_498@ @H_502_498@ View view = convertView != null ? convertView : View.inflate(MainActivity. this ,R.layout. item , null ); // 根据XML生成界面 @H_502_498@ TextView idTV = (TextView) view.findViewById(R.id. idTV ); // 不要从Activity中获取,一定是view.findViewById() @H_502_498@ TextView nameTV = (TextView) view.findViewById(R.id. nameTV ); @H_502_498@ TextView balanceTV = (TextView) view.findViewById(R.id. balanceTV ); @H_502_498@ @H_502_498@ final Account a = list .get(position); // 获取指定位置上的对象 @H_502_498@ idTV.setText(a.getId() + "" ); // 设置文本的时候,注意区分int和String @H_502_498@ nameTV.setText(a.getName()); @H_502_498@ balanceTV.setText(a.getBalance() + "" ); @H_502_498@ @H_502_498@ view.findViewById(R.id. upIV ).setOnClickListener( new OnClickListener() { @H_502_498@ public void onClick(View v) { // 点击up按钮时执行 @H_502_498@ a.setBalance(a.getBalance() + 1); // 修改数据 @H_502_498@ dao .update(a); // 更新数据库 @H_502_498@ adapter .notifyDataSetChanged(); // 刷新界面 @H_502_498@ } @H_502_498@ }); @H_502_498@ @H_502_498@ view.findViewById(R.id. downIV ).setOnClickListener( new OnClickListener() { @H_502_498@ public void onClick(View v) { @H_502_498@ a.setBalance(a.getBalance() - 1); @H_502_498@ dao .update(a); @H_502_498@ adapter .notifyDataSetChanged(); @H_502_498@ } @H_502_498@ }); @H_502_498@ @H_502_498@ view.findViewById(R.id. deleteIV ).setOnClickListener( new OnClickListener() { @H_502_498@ public void onClick(View v) { @H_502_498@ list .remove(a); @H_502_498@ dao .delete(a.getId()); @H_502_498@ adapter .notifyDataSetChanged(); @H_502_498@ } @H_502_498@ }); @H_502_498@ @H_502_498@ return view; @H_502_498@ } @H_502_498@ } @H_502_498@ @H_502_498@ private class MyItemListener implements OnItemClickListener { @H_502_498@ public void onItemClick(AdapterView<?> parent,View view, int position, long id) { @H_502_498@ Account a = (Account) parent.getItemAtPosition(position); // 获取指定条目上的对象,内部调用Adapter中的getItem() @H_502_498@ Toast.makeText(getApplicationContext(),a.toString(),Toast. LENGTH_SHORT ).show(); @H_502_498@ } @H_502_498@ } @H_502_498@
@H_502_498@ } 原文链接：https://www.f2er.com/sqlite/201017.html