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

}

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

/**

* 创建数据库

* @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();

}

SQLite数据库的创建

猜你在找的Sqlite相关文章