android SQLiteOpenHelper 使用
1、实体
package mydemo.mycom.demo2.entity; public class UserInfo { private int id; private String username; private String password; public UserInfo() { } public UserInfo(int id, String username, String password) { this.id = id; this.username = username; this.password = password; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } }
2.UserSQLiteOpenHelper
package mydemo.mycom.demo2.db; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; public class UserSQLiteOpenHelper extends SQLiteOpenHelper { /** * 数据库的构造方法 用来定义数据库的名称 数据库查询的结果集 数据库的版本 * **/ public UserSQLiteOpenHelper(Context context) { super(context, "user.db", null, 1); } /** * 数据库第一次被创建的时候调用的方法 * db被创建的数据库 * **/ @Override public void onCreate(SQLiteDatabase db) { //初始化数据库的表结构 d integer primary key autoincrement, db.execSQL("create table user (id integer primary key autoincrement,username varchar(20),password varchar(20))"); } /** * 当数据库的版本号发生变化的时候(增加的时候) 调用 * */ @Override public void onUpgrade(SQLiteDatabase db, int i, int i2) { db.execSQL("alter table user add account varchar(20)"); } }
3.JDBC的UserDAO
package mydemo.mycom.demo2.dao; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import java.util.ArrayList; import java.util.List; import mydemo.mycom.demo2.db.UserSQLiteOpenHelper; import mydemo.mycom.demo2.entity.UserInfo; public class UserDao { private UserSQLiteOpenHelper helper; public UserDao(Context context) { helper = new UserSQLiteOpenHelper(context); } /** * 添加一条记录到数据库 * username 用户名 * password 密码 * * */ public void add(String username,String password) { SQLiteDatabase db = helper.getWritableDatabase(); db.execSQL("insert into user(username,password) values(?,?)",new String[]{username,password}); db.close(); } /** * 判断数据库是否存在username 的数据 * * username 用户名 * */ public boolean findByUsername(String username) { SQLiteDatabase db = helper.getWritableDatabase(); Cursor cursor = db.rawQuery("select * from user where username=?",new String[]{username}); boolean result = cursor.moveToNext(); db.close(); return result; } /** * 修改一条记录 * 通过username 修改 password * * username 用户名 * password 密码 * * */ public void update(String username,String password) { SQLiteDatabase db = helper.getWritableDatabase(); db.execSQL("update user set password=? where username=?",new String[]{password,username}); db.close(); } /** * 删除记录 * 通过username修改一条记录 * * username 用户名 * */ public void delete(String username) { SQLiteDatabase db = helper.getWritableDatabase(); db.execSQL("delete from user where username=?",new String[]{username}); db.close(); } /** * 找出所有用户信息 * */ public List<UserInfo> findAll() { SQLiteDatabase db = helper.getWritableDatabase(); List<UserInfo> list = new ArrayList<UserInfo>(); Cursor cursor = db.rawQuery("select * from user",null); while(cursor.moveToNext()) { int id = cursor.getInt(cursor.getColumnIndex("id")); String username = cursor.getString(cursor.getColumnIndex("username")); String password = cursor.getString(cursor.getColumnIndex("password")); UserInfo userInfo = new UserInfo(id,username,password); list.add(userInfo); } cursor.close(); db.close(); return list; } /** * 通过id 用户信息 * id 用户id * */ public UserInfo findById(int id) { SQLiteDatabase db = helper.getWritableDatabase(); Cursor cursor =db.rawQuery("select * from user where id=?",new String[]{id+""}); UserInfo user = new UserInfo(); while(cursor.moveToNext()) { int uid = cursor.getInt(cursor.getColumnIndex("id")); String username = cursor.getString(cursor.getColumnIndex("username")); String password = cursor.getString(cursor.getColumnIndex("password")); user.setId(uid); user.setPassword(password); user.setUsername(username); break; } cursor.close(); db.close(); return user; } }
4.SQLiteOpenHelper的 UserDAO
package mydemo.mycom.demo2.dao; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import java.util.ArrayList; import java.util.List; import mydemo.mycom.demo2.db.UserSQLiteOpenHelper; import mydemo.mycom.demo2.entity.UserInfo; /** * Created by Administrator on 2015/5/15. */ public class UserDao2 { private UserSQLiteOpenHelper helper; public UserDao2(Context context) { helper = new UserSQLiteOpenHelper(context); } /** * 添加一条记录到数据库 * username 用户名 * password 密码 * * */ public long add(String username,String password) { SQLiteDatabase db = helper.getWritableDatabase(); ContentValues values = new ContentValues(); values.put("username",username); values.put("password",password); long num = db.insert("user",null,values); db.close(); return num; } /** * 判断数据库是否存在username 的数据 * * username 用户名 * */ public boolean findByUsername(String username) { SQLiteDatabase db = helper.getWritableDatabase(); Cursor cursor = db.query("user",null,"username=?",new String[]{username},null,null,null); boolean result = cursor.moveToNext(); db.close(); return result; } /** * 修改一条记录 * 通过username 修改 password * * username 用户名 * password 密码 * * */ public int update(String username,String password) { SQLiteDatabase db = helper.getWritableDatabase(); ContentValues values = new ContentValues(); values.put("password",password); int num = db.update("user",values,"username=?", new String[]{username}); db.close(); return num; } /** * 删除记录 * 通过username修改一条记录 * * username 用户名 * */ public int delete(String username) { SQLiteDatabase db = helper.getWritableDatabase(); int num = db.delete("user","username=?",new String[]{username}); db.close(); return num; } /** * 找出所有用户信息 * */ public List<UserInfo> findAll() { SQLiteDatabase db = helper.getWritableDatabase(); List<UserInfo> list = new ArrayList<UserInfo>(); Cursor cursor = db.query("user",null,null,null,null,null,null); while(cursor.moveToNext()) { int id = cursor.getInt(cursor.getColumnIndex("id")); String username = cursor.getString(cursor.getColumnIndex("username")); String password = cursor.getString(cursor.getColumnIndex("password")); UserInfo userInfo = new UserInfo(id,username,password); list.add(userInfo); } cursor.close(); db.close(); return list; } /** * 通过id 用户信息 * id 用户id * */ public UserInfo findById(int id) { SQLiteDatabase db = helper.getWritableDatabase(); Cursor cursor = db.query("user",null,"id=?",new String[]{id+""},null,null,null); UserInfo user = new UserInfo(); while(cursor.moveToNext()) { int uid = cursor.getInt(cursor.getColumnIndex("id")); String username = cursor.getString(cursor.getColumnIndex("username")); String password = cursor.getString(cursor.getColumnIndex("password")); user.setId(uid); user.setPassword(password); user.setUsername(username); break; } cursor.close(); db.close(); return user; } }
5.SQLiteOpenHelper 简单使用
package mydemo.mycom.demo2; import android.support.v7.app.ActionBarActivity; import android.os.Bundle; import android.text.TextUtils; import android.view.Menu; import android.view.MenuItem; import android.view.View; import android.widget.Button; import android.widget.EditText; import android.widget.Toast; import mydemo.mycom.demo2.dao.UserDao; import mydemo.mycom.demo2.dao.UserDao2; public class Register extends ActionBarActivity implements View.OnClickListener { private EditText et_register_username; private EditText et_register_password; private Button btn_register; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_register); et_register_username = (EditText)findViewById(R.id.et_register_username); et_register_password = (EditText)findViewById(R.id.et_register_password); btn_register = (Button)findViewById(R.id.btn_register); btn_register.setOnClickListener(this); } @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_register, 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); } @Override public void onClick(View view) { String username = et_register_username.getText().toString().trim(); String password = et_register_password.getText().toString().trim(); if(TextUtils.isEmpty(username) || TextUtils.isEmpty(password)) { Toast.makeText(this,"用户名和密码不能为空",Toast.LENGTH_SHORT).show(); return; } UserDao2 userDao = new UserDao2(this); userDao.add(username,password); Toast.makeText(this,"注册成功",Toast.LENGTH_SHORT).show(); } }
6.SQLiteOpenHelper事务管理
package mydemo.mycom.demo2.testUserDao; import android.database.sqlite.SQLiteDatabase; import android.test.AndroidTestCase; import mydemo.mycom.demo2.db.UserSQLiteOpenHelper; public class TestUserInfo extends AndroidTestCase{ private String s; public void testTransaction() throws Exception { UserSQLiteOpenHelper helper = new UserSQLiteOpenHelper(getContext()); SQLiteDatabase db = helper.getWritableDatabase(); //开始数据库的事务 db.beginTransaction(); try{ db.execSQL("update user set account=account-1000 where id=1"); //空指针异常 s.equals("123"); db.execSQL("update user set account=account+1000 where id=2"); //提交事务 db.setTransactionSuccessful(); } catch(Exception ex) { } finally { //结束事务 db.endTransaction(); db.close(); } } }
