android数据库SQLite的设计模式

2023-07-31 09:32:58

Dao设计模式可能是使用最多的数据库的设计模式其基本思路是将数据库操作的代码

与设计代码分离以便于维护和升级。具体的实现方法是使用包，然后在设计代码中调

用数据库的操作代码，dao设计模式需要创建5个包，每个包中所包含的java文件会有

所区别，如下图所示：

android数据库SQLite的设计模式

bean包中包含的是实体类，即一个数据表就对应一个实体类

common包中包含的是一些共有的java文件

dao包中包含的是数据库的有关操作如插入、删除、修改等

db包中包含的是数据库的创建文件

traveler包中包含的是界面代码文件

下图是各包种所包含的文件

android数据库SQLite的设计模式

接下来是这些文件的源码

bean包中给出一个User实体类的源码如下：

package com.llwjzy.bean;

import java.io.Serializable;

//实现Serializable接口的作用在于便于在界面的跳转中进行对象传递

public class User implements Serializable{
　　//数据成员就是数据表中的字段

    private int id;

    private int age;

    private String name;

    private String alias;

    private String sex;

    private String password;

    private String city;

    private String email;

    private String phone;

    private String info;

    public User(){

        age=0;

        name=null;

        alias=null;

        sex=null;

        password=null;

        city=null;

        email=null;

        phone=null;

        info=null;

    }

    public User(int id, int age, String name, String alias, String sex,

            String password, String city, String email, String phone,

            String info) {

        super();

        this.id = id;

        this.age = age;

        this.name = name;

        this.alias = alias;

        this.sex = sex;

        this.password = password;

        this.city = city;

        this.email = email;

        this.phone = phone;

        this.info = info;

    }

    public int getAge() {

        return age;

    }

    public void setAge(int age) {

        this.age = age;

    }

    public int getId() {

        return id;

    }

    public void setId(int id) {

        this.id = id;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    public String getAlias() {

        return alias;

    }

    public void setAlias(String alias) {

        this.alias = alias;

    }

    public String getSex() {

        return sex;

    }

    public void setSex(String sex) {

        this.sex = sex;

    }

    public String getPassword() {

        return password;

    }

    public void setPassword(String password) {

        this.password = password;

    }

    public String getCity() {

        return city;

    }

    public void setCity(String city) {

        this.city = city;

    }

    public String getEmail() {

        return email;

    }

    public void setEmail(String email) {

        this.email = email;

    }

    public String getPhone() {

        return phone;

    }

    public void setPhone(String phone) {

        this.phone = phone;

    }

    public String getInfo() {

        return info;

    }

    public void setInfo(String info) {

        this.info = info;

    }

}

common包中Utils文件的代码如下：

package com.llwjzy.common;

import java.util.Calendar;

public class Utils {

    public static final String DATABSE_NAME="Traveler.db";

    public static final int VERSION=1;

    //User数据表的定义

    public static final String TABLE_User_NAME="User";

    public static final String User_id="user_id";

    public static final String User_name="user_name";

    public static final String User_alias="user_alias";

    public static final String User_sex="user_sex";

    public static final String User_age="user_age";

    public static final String User_password="user_password";

    public static final String User_city="user_city";

    public static final String User_email="user_email";

    public static final String User_phone="user_phone";

    public static final String User_info="user_info";

    //Relationship数据表的定义

    public static final String TABLE_Relationship_NAME="Relationship";

    public static final String Relationship_id="rel_id";

    public static final String Relationship_my_id="rel_my_id";

    public static final String Relationship_user_id="rel_user_id";

    public static final String Relationship_style="rel_style";

    //Talk数据表的定义

    public static final String TABLE_Talk_NAME="Talk";

    public static final String Talk_id="talk_id";

    public static final String Talk_my_id="talk_my_id";

    public static final String Talk_context="talk_context";

    public static final String Talk_my_date="talk_my_date";

    //Talk_rel数据表的定义

    public static final String TABLE_Talk_rel_NAME="Talk_rel";

    public static final String Talk_rel_id="talk_rel_id";

    public static final String Talk_rel_my_id="my_id";

    public static final String Talk_rel_friend_id="friend_id";

    public static final String Talk_rel_talk_id="talk_id";

    //Views数据表的定义

    public static final String TABLE_Views_NAME="Views";

    public static final String Views_id="views_id";

    public static final String Views_image="views_image";

    public static final String Views_name="views_name";

    public static final String Views_introduction="views_introduction";

    public static final String Views_price="views_price";

    public static final String Views_open="views_open";

    public static final String Views_location="views_location";

    public static final String Views_grade="views_grade";

    public static final String Views_grade_number="views_grade_number";

    //Memory数据表的定义

    public static final String TABLE_Memory_NAME="Memory";

    public static final String Memory_id="memory_id";

    public static final String Memory_my_id="memory_my_id";

    public static final String Memory_date="memory_date";

    public static final String Memory_title="memory_title";

    public static final String Memory_location="memory_location";

    public static final String Memory_context="memory_context";

    //Mark数据表的定义

    public static final String TABLE_Mark_NAME="Mark";

    public static final String Mark_id="mark_id";

    public static final String Mark_user_id="mark_user_id";

    public static final String Mark_views_id="mark_views_id";

    public static final String Mark_date="mark_date";

    //获取当前时间方法

    public static String gettime(){

        String time="";

        Calendar calendar=Calendar.getInstance();

        time=calendar.get(Calendar.YEAR)+"年"+(calendar.get(Calendar.MONTH)+1)+"月"+calendar.get(Calendar.DATE)+"日";

        int hour=calendar.get(Calendar.HOUR_OF_DAY);

        if(hour<10){

            time+="0"+hour+":";

        }else{

            time+=hour+":";

        }

        int minth=calendar.get(Calendar.MINUTE);

        if(minth<10){

            time+="0"+minth;

        }else{

            time+=minth;

        }

        return time;

    }

}

dao包中Userdao接口用于描述对User数据表的操作，代码如下：

package com.llwjzy.dao;

import java.util.List;

import com.llwjzy.bean.User;

public interface Userdao extends Dao{

    //用户登录检测

    public boolean check(String name,String psd);

    //用户注册

    public boolean register(User user);

    //修改用户信息

    public boolean UpdateUser(User user);

    //用户名不能重复

    public boolean checkname(String name);

    //按id获取用户信息

    public User getUserByid(int user_id);

    //按用户名获取用户信息

    public User getUserByName(String user_name);

    //按用户号码获取用户信息

    public User getUserByPhone(String phone);

}

dao包中Userdaoinstance类实现Userdao接口，这样便能是代码得到扩展使代码的调用变得更灵活，

如当数据库的类型改变后可以再写一个类实现Userdao接口，在调用时使用Userdao的引用指向不同实

现类，代码如下：

package com.llwjzy.dao;

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import com.llwjzy.bean.User;

import com.llwjzy.common.Utils;

import com.llwjzy.db.MySQLiteOpenHelper;

public class Userdaoinstance implements Userdao{

    private MySQLiteOpenHelper helper;

    public static Userdaoinstance instance_userdao;

    public Userdaoinstance(Context context) {

        helper = new MySQLiteOpenHelper(context);

    }

    public static synchronized Userdaoinstance getInstacne(Context context) {

        if (instance_userdao == null) {

            instance_userdao = new Userdaoinstance(context);

        }

        return instance_userdao;

    }

    @Override

    public boolean check(String name, String psd) {

        // TODO Auto-generated method stub

        boolean flag=false;

        SQLiteDatabase db = helper.getReadableDatabase();

        String columns[] = {Utils.User_name };

        String sql = Utils.User_name + "='" + name + "' AND " + Utils.User_password + "='"

                + psd + "'";

        Cursor cursor = db.query(Utils.TABLE_User_NAME, columns, sql, null,

                null, null, null);

        if (cursor.moveToFirst()) {

            flag = true;

        }

        if (cursor != null) {

            cursor.close();

        }

        if (db != null) {

            db.close();

        }

        return flag;

    }

    @Override

    public boolean register(User user) {

        // TODO Auto-generated method stub

        boolean flag=false;

        SQLiteDatabase db = helper.getWritableDatabase();

        String sort = "User_id desc";

        ContentValues values = new ContentValues();

        values.put(Utils.User_name, user.getName());

        values.put(Utils.User_password, user.getPassword());

        values.put(Utils.User_alias, user.getAlias());

        values.put(Utils.User_city, user.getCity());

        values.put(Utils.User_email, user.getEmail());

        values.put(Utils.User_info, user.getInfo());

        values.put(Utils.User_phone, user.getPhone());

        values.put(Utils.User_sex, user.getSex());

        values.put(Utils.User_age, user.getAge());

        db.insert(Utils.TABLE_User_NAME, null, values);

        Cursor cursor = db.query(Utils.TABLE_User_NAME, null, null, null, null,

                null, sort);

        if(cursor.moveToFirst()){

            flag=true;

        }

        if (cursor != null) {

            cursor.close();

        }

        if (db != null) {

            db.close();

        }

        return flag;

    }

    @Override

    public boolean UpdateUser(User user) {

        // TODO Auto-generated method stub

        boolean flag=false;

        SQLiteDatabase db=helper.getWritableDatabase();

        String select = Utils.User_id + "=?";

        String[] whereArgs = { String.valueOf(user.getId()) };

        ContentValues values = new ContentValues();

        values.put(Utils.User_name, user.getName());

        values.put(Utils.User_password, user.getPassword());

        values.put(Utils.User_alias, user.getAlias());

        values.put(Utils.User_city, user.getCity());

        values.put(Utils.User_email, user.getEmail());

        values.put(Utils.User_info, user.getInfo());

        values.put(Utils.User_phone, user.getPhone());

        values.put(Utils.User_sex, user.getSex());

        values.put(Utils.User_age, user.getAge());

        int count=db.update(Utils.TABLE_User_NAME, values, select, whereArgs);

        if(count==1){

            flag=true;

        }

        if (db != null) {

            db.close();

        }

        return flag;

    }

    @Override

    public boolean checkname(String name) {

        // TODO Auto-generated method stub

        boolean flag=false;

        SQLiteDatabase db=helper.getReadableDatabase();

        String[] columns={Utils.User_name};

        String selection=Utils.User_name+"='"+name+"'";

        Cursor cursor=db.query(Utils.TABLE_User_NAME, columns, selection, null, null, null, null);

        if(cursor.moveToFirst()){

            flag=true;

        }

        if (cursor != null) {

            cursor.close();

        }

        if (db != null) {

            db.close();

        }

        return flag;

    }

    @Override

    public User getUserByid(int user_id) {

        // TODO Auto-generated method stub

        SQLiteDatabase db=helper.getReadableDatabase();

        String selection=Utils.User_id+"='"+user_id+"'";

        Cursor cursor=db.query(Utils.TABLE_User_NAME, null, selection, null, null, null, null);

        cursor.moveToFirst();

        int age=cursor.getInt(cursor.getColumnIndexOrThrow(Utils.User_age));

        int id=cursor.getInt(cursor.getColumnIndexOrThrow(Utils.User_id));

        String name=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_name));

        String password=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_password));

        String alias=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_alias));

        String sex=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_sex));

        String phone=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_phone));

        String info=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_info));

        String city=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_city));

        String email=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_email));

        if (cursor != null) {

            cursor.close();

        }

        if (db != null) {

            db.close();

        }

        return new User(id, age, name, alias, sex, password, city, email, phone, info);

        }

    @Override

    public User getUserByName(String user_name){

        SQLiteDatabase db=helper.getReadableDatabase();

        String selection=Utils.User_name+"='"+user_name+"'";

        User user=null;

        Cursor cursor=db.query(Utils.TABLE_User_NAME, null, selection, null, null, null, null);

        if(cursor.moveToFirst()){

            int age=cursor.getInt(cursor.getColumnIndexOrThrow(Utils.User_age));

            int id=cursor.getInt(cursor.getColumnIndexOrThrow(Utils.User_id));

            String name=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_name));

            String password=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_password));

            String alias=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_alias));

            String sex=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_sex));

            String phone=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_phone));

            String info=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_info));

            String city=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_city));

            String email=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_email));

            user=new User(id, age, name, alias, sex, password, city, email, phone, info);

        }

        if (cursor != null) {

            cursor.close();

        }

        if (db != null) {

            db.close();

        }

        return user;

    }

    @Override

    public User getUserByPhone(String phone) {

        // TODO Auto-generated method stub

        SQLiteDatabase db=helper.getReadableDatabase();

        String selection=Utils.User_phone+"='"+phone+"'";

        User user=null;

        Cursor cursor=db.query(Utils.TABLE_User_NAME, null, selection, null, null, null, null);

        if(cursor.moveToFirst()){

            int age=cursor.getInt(cursor.getColumnIndexOrThrow(Utils.User_age));

            int id=cursor.getInt(cursor.getColumnIndexOrThrow(Utils.User_id));

            String name=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_name));

            String password=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_password));

            String alias=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_alias));

            String sex=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_sex));

            String phone1=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_phone));

            String info=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_info));

            String city=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_city));

            String email=cursor.getString(cursor.getColumnIndexOrThrow(Utils.User_email));

            user=new User(id, age, name, alias, sex, password, city, email, phone1, info);

        }

        if (cursor != null) {

            cursor.close();

        }

        if (db != null) {

            db.close();

        }

        return user;

    }

}

dao包中DaoFactory文件是所有数据库操作文件的集合，将所有实体类的构造都写在一起便于创建实体对象，代码如下：

package com.llwjzy.dao;

import android.content.Context;

public class DaoFactory {

    public static Userdaoinstance getinstanceUserdao(Context context){

        return Userdaoinstance.getInstacne(context);

    }

    public static Viewsdaoinstance getinstanceViewsdao(Context context){

        return Viewsdaoinstance.getinstance(context);

    }

    public static Markdaoinstance getinstanceMarkdao(Context context){

        return Markdaoinstance.getinstance(context);

    }

    public static Memorydaoinstance getinstanceMemorydao(Context context){

        return Memorydaoinstance.getinstance(context);

    }

    public static Relationshipdaoinstance getinstanceRelationshipdao(Context context){

        return Relationshipdaoinstance.getinstance(context);

    }

    public static Talkdaoinstance getinstanceTalkdao(Context context){

        return Talkdaoinstance.getinstance(context);

    }

    public static Talk_reldaoinstance getinstanceTalk_reldao(Context context){

        return Talk_reldaoinstance.getinstance(context);

    }

}

db包中MySQLiteOpenHelper文件写的是数据库的创建代码：

package com.llwjzy.db;

import com.llwjzy.common.Utils;

import com.llwjzy.dao.DaoFactory;

import com.llwjzy.dao.Viewsdaoinstance;

import com.llwjzy.traveler.R;

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

import android.util.Log;

/**

 *

 * @author mxt SQLiteOpenHelper用来管理数据库的创建和版本的管理

 *         onCreate：当数据库第一次被建立的时候被执行，例如创建表,初始化数据等。

 *         onUpgrade：当数据库需要被更新的时候执行，例如删除旧表，创建新表。

 *

 */

public class MySQLiteOpenHelper extends SQLiteOpenHelper {

    private Context mycontext;

    public MySQLiteOpenHelper(Context context) {

        super(context, Utils.DATABSE_NAME, null, Utils.VERSION);

        this.mycontext = context;

    }

    /**

     * 当数据库被创建的时候，此方法被调用

     */

    @Override

    public void onCreate(SQLiteDatabase db) {

        // 创建User数据表

        String table_user = "create table " + Utils.TABLE_User_NAME + " ( "

                + Utils.User_id + " integer primary key autoincrement,"

                + Utils.User_name + " text not null," + Utils.User_password

                + " text ," + Utils.User_alias + " text ," + Utils.User_sex

                + " text ," + Utils.User_city + " text ," + Utils.User_email

                + " text," + Utils.User_age + " integer ," + Utils.User_phone

                + " text," + Utils.User_info + " text);";

        Log.d("luo", table_user);

        db.execSQL(table_user);

        // 创建Relationship数据表

        String table_relstionship = "create table "

                + Utils.TABLE_Relationship_NAME + " ( " + Utils.Relationship_id

                + " integer primary key autoincrement,"

                + Utils.Relationship_my_id + " integer not null,"

                + Utils.Relationship_style + " integer not null,"

                + Utils.Relationship_user_id + " integer not null);";

        Log.d("luo", table_relstionship);

        db.execSQL(table_relstionship);

        // 创建Talk数据表

        String table_talk = "create table " + Utils.TABLE_Talk_NAME + " ( "

                + Utils.Talk_id + " integer primary key autoincrement,"

                + Utils.Talk_my_id + " integer not null," + Utils.Talk_context

                + " text," + Utils.Talk_my_date + " text);";

        Log.d("luo", table_talk);

        db.execSQL(table_talk);

        // 创建Talk_rel数据表

        String table_talk_rel = "create table " + Utils.TABLE_Talk_rel_NAME

                + "(" + Utils.Talk_rel_id

                + " integer primary key autoincrement," + Utils.Talk_rel_my_id

                + " integer not null, " + Utils.Talk_rel_friend_id

                + " integer not null, "+Utils.Talk_rel_talk_id+" integer not null);";

        db.execSQL(table_talk_rel);

        // 创建Views数据表

        String table_views = "create table " + Utils.TABLE_Views_NAME + " ( "

                + Utils.Views_id + " integer primary key autoincrement,"

                + Utils.Views_image + " integer ," + Utils.Views_name

                + " text ," + Utils.Views_introduction + " text ,"

                + Utils.Views_open + " text ," + Utils.Views_price + " text ,"

                + Utils.Views_location + " text ," + Utils.Views_grade

                + " text ," + Utils.Views_grade_number + " integer );";

        Log.d("luo", table_views);

        db.execSQL(table_views);

        // 创建Mark数据表

        String table_mark = "create table " + Utils.TABLE_Mark_NAME + " ( "

                + Utils.Mark_id + " integer primary key autoincrement,"

                + Utils.Mark_user_id + " integer ," + Utils.Mark_views_id

                + " integer ," + Utils.Mark_date + " text );";

        Log.d("luo", table_mark);

        db.execSQL(table_mark);

        // 创建Memory数据表

        String table_memory = "create table " + Utils.TABLE_Memory_NAME + " ( "

                + Utils.Memory_id + " integer primary key autoincrement,"

                + Utils.Memory_my_id + " integer not null,"

                + Utils.Memory_location + " text ," + Utils.Memory_title

                + " text ," + Utils.Memory_date + " text ,"

                + Utils.Memory_context + " text );";

        Log.d("luo", table_memory);

        db.execSQL(table_memory);

    }

    /**

     * 如果构造器的版本比之前创建的高，就自动调用此方法

     */

    @Override

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        String upgrade_mark = "DROP TABLE IF EXIST " + Utils.TABLE_Mark_NAME;

        db.execSQL(upgrade_mark);

        String upgrade_memory = "DROP TABLE IF EXIST "

                + Utils.TABLE_Memory_NAME;

        db.execSQL(upgrade_memory);

        String upgrade_relationship = "DROP TABLE IF EXIST "

                + Utils.TABLE_Relationship_NAME;

        db.execSQL(upgrade_relationship);

        String upgrade_talk = "DROP TABLE IF EXIST " + Utils.TABLE_Talk_NAME;

        db.execSQL(upgrade_talk);

        String upgrade_user = "DROP TABLE IF EXIST " + Utils.TABLE_User_NAME;

        db.execSQL(upgrade_user);

        String upgrade_views = "DROP TABLE IF EXIST " + Utils.TABLE_Views_NAME;

        db.execSQL(upgrade_views);

        String upgrade_talk_rel = "DROP TABLE IF EXIST " + Utils.TABLE_Talk_rel_NAME;

        db.execSQL(upgrade_talk_rel);

        onCreate(db);

    }

}

traveler包中包含的设计代码，比较多，就不一一列出，只说说调用代码：

private Userdao userdaoinstance;//声明引用

userdaoinstance = DaoFactory.getinstanceUserdao(Register.this);

userdaoinstance.register(user);//创建实例对象

List<User>  rel_user_id = instanceRelationshipdao.getrelationship(

                Login.user.getId(), j);//调用实例中写好的方法

if (userdaoinstance.check(name, psd))  //调用实例中写好的方法
android数据库SQLite的设计模式
上一篇：Cisco IOS版本命名规则


下一篇：sqlite升级--浅谈Android数据库版本升级及数据的迁移