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();
}
}
}
