Android—第三方數據庫SQLite—SQLiteOpenHelper+SQLiteDatabase

package com.example.jreduch08.DataBaseHelpp;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;

import com.j256.ormlite.android.apptools.OrmLiteSqliteOpenHelper;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

public  class DatabaseHelper extends OrmLiteSqliteOpenHelper {
    private static final String TABLE_NAME = "ormtest.db";
    private Map daos = new HashMap();
    private static DatabaseHelper instance;
    private DatabaseHelper(Context context) {
        super(context, TABLE_NAME, null, 1);
    }
    @Override  
    public void onCreate(SQLiteDatabase database,
            ConnectionSource connectionSource) {
        try {
            TableUtils.createTable(connectionSource, User.class);
        } catch (SQLException e) {
            e.printStackTrace();  
        }  
    }
    @Override  
    public void onUpgrade(SQLiteDatabase database,  
            ConnectionSource connectionSource, int oldVersion, int newVersion) {
        try {
            TableUtils.dropTable(connectionSource, User.class, true);
            onCreate(database, connectionSource);  
        } catch (SQLException e) {
            e.printStackTrace();  
        }  
    }
    /** 
     * 單例獲取該Helper 
     *  
     * @param context 
     * @return 
     */  
    public static synchronized DatabaseHelper getHelper(Context context) {
        context = context.getApplicationContext();  
        if (instance == null) {
            synchronized (DatabaseHelper.class) {
                if (instance == null)  {
                    instance = new DatabaseHelper(context);
                }
            }  
        }
        return instance;  
    }
    public synchronized Dao getDao(Class clazz) throws SQLException {
        Dao dao = null;  
        String className = clazz.getSimpleName();
        if (daos.containsKey(className)) {
            dao = daos.get(className);  
        }  
        if (dao == null) {
            dao = super.getDao(clazz);  
            daos.put(className, dao);  
        }  
        return dao;  
    }
    /** 
     * 釋放資源 
     */  
    @Override  
    public void close() {
        super.close();
        for (String key : daos.keySet()) {
            Dao dao = daos.get(key);  
            dao = null;  
        }  
    }
}  

package com.example.jreduch08.DataBaseHelpp;

import com.j256.ormlite.field.DatabaseField;
import com.j256.ormlite.table.DatabaseTable;

/**
 * Created by 沖天之峰 on 2016/8/23.
 */

@DatabaseTable (tableName = "user")//表名
public class User {
    @DatabaseField(id=true)  //主鍵
private String  userId;
    @DatabaseField            //(columnName = "userName")
    private String name;
    @DatabaseField
    private int age;

    public User(){}

    public User(String userId, String name, int age) {
        this.userId = userId;
        this.name = name;
        this.age = age;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getUserId() {
        return userId;
    }

    public String setUserId(String userId) {
        this.userId = userId;
        return userId;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }



}

package com.example.jreduch08.DataBaseHelpp;

import android.content.Context;

import com.j256.ormlite.dao.Dao;

import java.sql.SQLException;
import java.util.List;

/**
 * Created by 沖天之峰 on 2016/8/23.
 */
public class UserDaoOrm {

    private Context context;
    private Dao userDao;
    private DatabaseHelper helper;

    public   UserDaoOrm(Context context){
        this.context=context;
        helper=DatabaseHelper.getHelper(context);
        try {
            userDao=helper.getDao(User.class);
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public  void  add(User user){

        try {
            userDao.createOrUpdate(user);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //查詢全部
    public List select() {
        try {
            return  userDao.queryForAll();
        } catch (SQLException e) {
            e.printStackTrace();
        }
return null;

    }

    public  void delete(User user){

        try {
            userDao.delete(user);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }



    public User selectUser(String id){

        try {
           return   userDao.queryBuilder().where()
                   .eq("userId",id)
                   .queryForFirst();

        } catch (SQLException e) {
            e.printStackTrace();
        }
return null;
    }




}

package com.example.jreduch08.DataBaseHelpp;

import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Spinner;
import android.widget.TextView;
import android.widget.Toast;

import com.example.jreduch08.R;

import java.util.ArrayList;
import java.util.List;

public class UserDaoTestActivity extends AppCompatActivity {
    private EditText name,age,userId;
    private Button button1,button2,button4,button5,button6;
    private TextView tv;
    private Spinner sp;
    private UserDaoOrm userDaoOrm;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_user_dao_test);
        name= (EditText) findViewById(R.id.name);
        age= (EditText) findViewById(R.id.age);
       userId= (EditText) findViewById(R.id.userId);
        button1= (Button) findViewById(R.id.button1);
        button2= (Button) findViewById(R.id.button2);
        button6= (Button) findViewById(R.id.button6);
        button4= (Button) findViewById(R.id.button4);
        button5= (Button) findViewById(R.id.button5);
        sp= (Spinner) findViewById(R.id.sp);
        tv= (TextView) findViewById(R.id.tv);
        userDaoOrm=new UserDaoOrm(this);
        button1.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                 User user=new User();
                user.setUserId(userId.getText().toString());
                user.setName(name.getText().toString());
                user.setAge(Integer.parseInt(age.getText().toString()));
                userDaoOrm.add(user);
                Toast.makeText(getBaseContext(),"新增成功",Toast.LENGTH_SHORT).show();

            }
        });
        button2.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                List list=userDaoOrm.select();
                String text="";
                List myData=new ArrayList();
                for (User u:list){
                    myData.add(u.getUserId()+":"+u.getName()+":"+u.getAge());
                    text=text+u.getUserId()+":"+u.getName()+":"+u.getAge()+"\n";
                }
                ArrayAdapter aa=new ArrayAdapter(getBaseContext(),android.R.layout
                        .simple_list_item_1,myData);
                sp.setAdapter(aa);
                tv.setText(text);
            }
        });
        button4.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                User user=new User();
                user.setUserId(userId.getText().toString());
                user.setName(name.getText().toString());
                user.setAge(Integer.parseInt(age.getText().toString()));
                userDaoOrm.delete(user);
                Toast.makeText(getBaseContext(),"刪除成功",Toast.LENGTH_SHORT).show();

        }
        });
        button5.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                User user=new User();
                String  id="";
               id= userId.getText().toString();

               tv.setText(userDaoOrm.selectUser(id).getUserId()+":"+userDaoOrm.selectUser(id).getName()+
                       ":"+userDaoOrm.selectUser(id).getAge());
               user=userDaoOrm.selectUser(id);
               name.setText(user.getName().toString());
                age.setText(user.getAge()+"");
                Toast.makeText(getBaseContext(),"查詢一個成功",Toast.LENGTH_SHORT).show();

            }
        });

        button6.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                if(!userId.getText().toString().equals("")){
                    User u=new User();
                    u.setUserId(userId.getText().toString());
                    userDaoOrm.delete(u);
                    Toast.makeText(getBaseContext(), "刪除一個成功", Toast.LENGTH_SHORT).show();
                }
            }
        });
    }
}