'안드로이드/Android SQLite'에 해당되는 글 10건

import android.content.Context;
import android.content.SharedPreferences;

public class PrefsHelper {
    public static final String PREFERENCE_NAME="pref";
    private Context mContext;
    private static SharedPreferences prefs;
    private static SharedPreferences.Editor prefsEditor;
    private static PrefsHelper instance;

    public static synchronized PrefsHelper init(Context context){
        if(instance == null)
            instance = new PrefsHelper(context);
        return instance;
    }

    private PrefsHelper(Context context) {
        mContext = context;
        prefs = mContext.getSharedPreferences(PREFERENCE_NAME, Context.MODE_PRIVATE );
        prefsEditor = prefs.edit();
    }

    public static String read(String key, String defValue) {
        return prefs.getString(key, defValue);
    }

    public static void write(String key, String value) {
        prefsEditor.putString(key, value);
        prefsEditor.commit();
    }

    public static Integer read(String key, int defValue) {
        return prefs.getInt(key, defValue);
    }

    public static void write(String key, Integer value) {
        prefsEditor.putInt(key, value).commit();
    }

    public static boolean read(String key, boolean defValue) {
        return prefs.getBoolean(key, defValue);
    }

    public static void write(String key, boolean value) {
        prefsEditor.putBoolean(key, value);
        prefsEditor.commit();
    }
}

buildscript {
    ext.kotlin_version = '1.3.71'
    repositories {
        google()
        jcenter()
       
    }
    dependencies {
        classpath 'com.android.tools.build:gradle:3.6.2'
        classpath "org.jetbrains.kotlin:kotlin-gradle-plugin:$kotlin_version"
    }
}

allprojects {
    repositories {
        google()
        jcenter()
       
    }
}

task clean(type: Delete) {
    delete rootProject.buildDir
}

dependencies {
    def lifecycle_version = "2.2.0"

    implementation fileTree(dir: 'libs', include: ['*.jar'])
    implementation 'androidx.appcompat:appcompat:1.1.0'
    implementation 'androidx.constraintlayout:constraintlayout:1.1.3'
    implementation 'com.google.android.material:material:1.1.0'

    implementation 'androidx.room:room-runtime:2.2.5'
    annotationProcessor 'androidx.room:room-compiler:2.2.5'

    // https://developer.android.com/jetpack/androidx/releases/lifecycle?hl=ko
    // Lifecycle components (ViewModel and LiveData)
    implementation 'androidx.lifecycle:lifecycle-extensions:2.2.0'
    annotationProcessor "androidx.lifecycle:lifecycle-compiler:$lifecycle_version"
    implementation "androidx.lifecycle:lifecycle-runtime:$lifecycle_version"
}

import androidx.room.Entity;
import androidx.room.PrimaryKey;

// @Entity 어노테이션이 지정된 클래스는 데이터베이스의 테이블을 정의하는데 사용된다.
@Entity
public class Todo {
    // 1. 엔티티 클래스 만들기
    // 먼저 데이터 구조를 정의할 엔티티를 클래스 형태로 만들어 준다.

    @PrimaryKey(autoGenerate = true)
    private int id;
    private String title;

    public Todo(String title) {
        this.title = title;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    @Override
    public String toString() {
        return "Todo{" +
                "id=" + id +
                ", title='" + title + '\'' +
                '}';
    }
}

import androidx.lifecycle.LiveData;
import androidx.room.Dao;
import androidx.room.Delete;
import androidx.room.Insert;
import androidx.room.OnConflictStrategy;
import androidx.room.Query;
import androidx.room.Update;

import java.util.List;

@Dao
public interface TodoDao {
    // 2. DAO 인터페이스 만들기

    @Query("SELECT * FROM Todo")
    LiveData<List<Todo>> getAll();

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    void insert(Todo todo);

    @Update
    void update(Todo todo);

    @Delete
    void delete(Todo todo);

    @Query("DELETE FROM Todo")
    void deleteAll();

}

import android.content.Context;

import androidx.room.Database;
import androidx.room.Room;
import androidx.room.RoomDatabase;

import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

@Database(entities = {Todo.class}, version = 1)
public abstract class AppDatabase extends RoomDatabase {
    // 3. 데이터베이스 클래스 만들기
    // https://webcoding.tistory.com/ 참조하면 좋은 자료 많다.

    public abstract TodoDao todoDao(); // Dao 인터페이스
    private static volatile AppDatabase INSTANCE;

    private static final int NUMBER_OF_THREADS = 4;
    static final ExecutorService databaseWriteExecutor = Executors.newFixedThreadPool(NUMBER_OF_THREADS);

    // 인스턴스를 생성하여 반환
    public static AppDatabase getInstance(Context context){
        if(INSTANCE == null){
            synchronized (AppDatabase.class){
                if(INSTANCE == null){
                    INSTANCE = Room.databaseBuilder(context.getApplicationContext(),AppDatabase.class,"Todo.db")
                            //.allowMainThreadQueries()
                            .build();
                }
            }
        }
        return INSTANCE;
    }
}

import android.app.Application;
import android.os.AsyncTask;

import androidx.annotation.NonNull;
import androidx.lifecycle.AndroidViewModel;
import androidx.lifecycle.LiveData;

import java.util.List;

public class MainViewModel extends AndroidViewModel {
    private AppDatabase db;

    public MainViewModel(@NonNull Application application) {
        super(application);
        db = AppDatabase.getInstance(application); // 데이터베이스 접근 인스턴스 사용하기
    }

    public LiveData<List<Todo>> getAll(){
        return db.todoDao().getAll();
    }

    public void insert(Todo todo){
        new InsertAsyncTask(db.todoDao()).execute(todo);
    }

    private static class InsertAsyncTask extends AsyncTask<Todo,Void, Void> {
        private TodoDao mTodoDao;

        public InsertAsyncTask(TodoDao todoDao) {
            this.mTodoDao = todoDao;
        }

        @Override
        protected Void doInBackground(Todo... todos) {
            mTodoDao.insert(todos[0]);
            return null;
        }
    }
}

import android.os.Bundle;
import android.widget.EditText;
import android.widget.TextView;

import androidx.appcompat.app.AppCompatActivity;
import androidx.lifecycle.ViewModelProvider;
import androidx.lifecycle.ViewModelProviders;
import androidx.lifecycle.ViewModelStore;
import androidx.lifecycle.ViewModelStoreOwner;

public class MainActivity extends AppCompatActivity implements ViewModelStoreOwner {
    private EditText mTodoEditText;
    private TextView mResultTextView;

    private ViewModelProvider.AndroidViewModelFactory viewModelFactory;
    private ViewModelStore viewModelStore = new ViewModelStore();
    private MainViewModel viewModel;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        mTodoEditText = findViewById(R.id.todo_edit);
        mResultTextView = findViewById(R.id.result_text);

        if(viewModelFactory == null){
            viewModelFactory = ViewModelProvider.AndroidViewModelFactory.getInstance(getApplication());
        }
        viewModel = new ViewModelProvider(this,viewModelFactory).get(MainViewModel.class);

        // UI 갱신
        viewModel.getAll().observe(this, todos -> {
            mResultTextView.setText(todos.toString());
        });

        // 버튼 클릭시 DB에 insert
        findViewById(R.id.add_btn).setOnClickListener(v -> {
            viewModel.insert(new Todo(mTodoEditText.getText().toString()));
        });
    }

    @Override
    protected void onDestroy() {
        super.onDestroy();
        viewModelStore.clear();
    }

    @Override
    public ViewModelStore getViewModelStore(){
        return viewModelStore;
    }
}

@Entity(tableName = "note_table", indices = @Index(value = {"title"}, unique = true))
public class Note {

    @PrimaryKey(autoGenerate = true)
    private int id;
    private String title; // 중복 입력 체크
    private String description;
    private int priority;

    public Note(String title, String description, int priority) {
        this.title = title;
        this.description = description;
        this.priority = priority;
    }
}

@Dao
public interface NoteDao {

    // 중복 값 입력시 새로운 값으로 변경됨.
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    void insert(Note note);

    // 기존에 존재하는 제목 값으로 변경시 무시
    @Update(onConflict = OnConflictStrategy.IGNORE)
    void update(Note note);

    @Delete
    void delete(Note note);

    @Query("DELETE FROM note_table")
    void deleteAllNotes();

    @Query("SELECT * FROM note_table ORDER BY priority DESC")
    LiveData<List<Note>> getAllNotes();

}

public class DbHelper extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "data2";
    public static final String TABLE_NAME = "comments_table";
    public static final String C_ID = "_id";
    public static final String TITLE = "title";
    public static final String TYPE = "type";
    public static final String DETAIL = "description";
    public static final String TIME = "time";
    public static final String DATE = "date";
    public static final int DATABASE_VERSION = 2;

    private final String createDB = "create table if not exists " + TABLE_NAME + " ( "
            + C_ID + " integer primary key autoincrement, "
            + TITLE + " text, "
            + DETAIL + " text, "
            + TYPE + " text, "
            + TIME + " text, "
            + DATE + " text)";

    public DbHelper(Context context){
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(createDB);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("drop table " + TABLE_NAME);
    }

    public Cursor LoadSQLiteDBCursor() {
        SQLiteDatabase db = this.getReadableDatabase();
        db.beginTransaction();
        // Select All Query
        String selectQuery = "SELECT _id,title,type,description,time,date FROM " + TABLE_NAME;
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(selectQuery, null);
            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.endTransaction();
        }
        return cursor;
    }

}

import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;

import androidx.appcompat.app.AppCompatActivity;
import androidx.recyclerview.widget.LinearLayoutManager;
import androidx.recyclerview.widget.RecyclerView;

import java.util.ArrayList;

public class MainActivity extends AppCompatActivity {
    Context context;

    SQLiteDatabase db;
    DbHelper mDbHelper;

    private RecyclerView listView;
    private ArrayList<Memo_Item> memoItemList= new ArrayList<>(); // SQLite에서 가져온 원본 데이터 리스트
    RecyclerView.Adapter listViewAdapter; // ListViewAdapter 대신 RecyclerView.Adapter
    RecyclerView.LayoutManager layoutManager;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        context = this.getBaseContext();

        listView = (RecyclerView)findViewById(R.id.commentslist);
        listView.setHasFixedSize(true);

        memoItemList.clear(); // 데이터 초기화
        mDbHelper = new DbHelper(this);
        db= mDbHelper.getReadableDatabase();
        db.beginTransaction();

        Cursor cursor = mDbHelper.LoadSQLiteDBCursor();
        try {
            cursor.moveToFirst();
            System.out.println("SQLiteDB 개수 = " + cursor.getCount());
            while (!cursor.isAfterLast()) {
                addGroupItem(cursor.getLong(0),cursor.getString(1),cursor.getString(2),
                        cursor.getString(3),cursor.getString(4),cursor.getString(5));
                cursor.moveToNext();
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
                db.endTransaction();
            }
        }

        // Set Layout Manager
        LinearLayoutManager layoutManager = new LinearLayoutManager(this);
        layoutManager.setOrientation(LinearLayoutManager.VERTICAL);
        listView.setLayoutManager(layoutManager);

        listViewAdapter = new ListViewAdapter(memoItemList, this); // Adapter 생성
        listView.setAdapter(listViewAdapter); // 어댑터를 리스트뷰에 세팅
    }

    public void addGroupItem(Long uid, String title, String memo_type, String detail, String time, String date){
        Memo_Item item = new Memo_Item();
        item.setUid(uid);
        item.setTitle(title);
        item.setMemo_type(memo_type);
        item.setDetail(detail);
        item.setTime(time);
        item.setDate(date);
        memoItemList.add(item);
    }

    @Override
    public void onBackPressed() {
        moveTaskToBack(true);
    }

    @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_main, menu);
        return true;
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {

        switch(item.getItemId()) {
            case R.id.action_new:
                Intent openCreateNote = new Intent(MainActivity.this, CreateNote.class);
                startActivity(openCreateNote);
                return true;

            default:
                return super.onOptionsItemSelected(item);
        }
    }

}

public class Memo_Item {
    private Long uid;
    private String title; // 제목
    private String memo_type; // 메모 타입
    private String detail; // 내용
    private String time; // 시간
    private String date; // 작성일자

    public Memo_Item() {

    }

    public Memo_Item(Long uid, String title, String memo_type, String detail, String time, String date) {
        this.uid = uid;
        this.title = title;
        this.memo_type = memo_type;
        this.detail = detail;
        this.time = time;
        this.date = date;
    }

    public Long getUid() {
        return uid;
    }

    public void setUid(Long uid) {
        this.uid = uid;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getMemo_type() {
        return memo_type;
    }

    public void setMemo_type(String memo_type) {
        this.memo_type = memo_type;
    }

    public String getDetail() {
        return detail;
    }

    public void setDetail(String detail) {
        this.detail = detail;
    }

    public String getTime() {
        return time;
    }

    public void setTime(String time) {
        this.time = time;
    }

    public String getDate() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }
}

import android.content.Context;
import android.content.Intent;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ImageView;
import android.widget.TextView;

import androidx.recyclerview.widget.RecyclerView;

import com.bumptech.glide.Glide;

import java.util.ArrayList;

public class ListViewAdapter extends RecyclerView.Adapter<RecyclerView.ViewHolder> {
    Context mContext;
    private ArrayList<Memo_Item> IvList;
    int Position;

    public ListViewAdapter(ArrayList<Memo_Item> items, Context context) {
        IvList = items;
        mContext = context;
    }

    @Override
    public RecyclerView.ViewHolder onCreateViewHolder(ViewGroup parent, int viewType) {
        View view = LayoutInflater.from(parent.getContext()).inflate(R.layout.list_entry, parent, false);
        return new ViewHolder(view);
    }

    @Override
    public void onBindViewHolder(RecyclerView.ViewHolder holder, int position) {
        // ListView의 getView 부분을 담당하는 메소드
        ((ViewHolder) holder).onBind(IvList.get(position));
    }

    @Override
    public int getItemCount() {
        return IvList.size(); // 데이터 개수 리턴
    }

    public class ViewHolder extends RecyclerView.ViewHolder {
        public TextView mTitle;
        public TextView memoType;
        public TextView mDetail;
        public ImageView mImage;
        public TextView mTime;
        public TextView mDate;

        public ViewHolder(View itemView) {
            super(itemView);
            // 화면에 표시될 View 로부터 위젯에 대한 참조 획득
            mTitle = itemView.findViewById(R.id.title);
            memoType = itemView.findViewById(R.id.type);
            mDetail = itemView.findViewById(R.id.Detail);
            mImage = itemView.findViewById(R.id.alarmImage);
            mTime = itemView.findViewById(R.id.time);
            mDate = itemView.findViewById(R.id.date);

            itemView.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View v) {
                    Position = getAdapterPosition();
                    Intent intent = new Intent(mContext, View_Note.class);
                    intent.putExtra(mContext.getString(R.string.row_id),IvList.get(Position).getUid());
                    Log.e("rowID", String.valueOf(IvList.get(Position).getUid()));
                    mContext.startActivity(intent);
                }
            });
        }

        public void onBind(Memo_Item item) {
            mTitle.setText(item.getTitle());
            memoType.setText(item.getMemo_type());
            mDetail.setText(item.getDetail());
            int resourceId = R.drawable.ic_action_alarms;
            Glide.with(mContext).load(resourceId).into(mImage);
            mTime.setText(item.getTime());
            mDate.setText(item.getDate());
        }
    }
}

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent"
    android:layout_height="match_parent" android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    android:paddingBottom="@dimen/activity_vertical_margin" tools:context=".MainActivity">

    <LinearLayout
        android:orientation="vertical"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true"
        >

        <androidx.recyclerview.widget.RecyclerView
            android:id="@+id/commentslist"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"/>
    </LinearLayout>

</RelativeLayout>

apply plugin: 'com.android.application'

android {
    compileSdkVersion 28


    defaultConfig {
        applicationId "com.tistory.link2me.memo"
        minSdkVersion 19
        targetSdkVersion 28
        versionCode 1
        versionName "1.0"

        testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"
    }

    buildTypes {
        release {
            minifyEnabled false
            proguardFiles getDefaultProguardFile('proguard-android-optimize.txt'), 'proguard-rules.pro'
        }
    }

}

dependencies {
    implementation fileTree(dir: 'libs', include: ['*.jar'])

    implementation 'androidx.appcompat:appcompat:1.1.0'
    implementation 'androidx.constraintlayout:constraintlayout:1.1.3'
    implementation 'androidx.recyclerview:recyclerview:1.0.0'
    implementation 'androidx.cardview:cardview:1.0.0'
    implementation 'gun0912.ted:tedpermission:2.0.0'
    implementation 'com.github.bumptech.glide:glide:3.8.0' // 이미지 라이브러리
}

apply plugin: 'com.android.application'

android {
    compileSdkVersion 28


    defaultConfig {
        applicationId "com.tistory.link2me.memo"
        minSdkVersion 19
        targetSdkVersion 28
        versionCode 1
        versionName "1.0"

        testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"
    }

    buildTypes {
        release {
            minifyEnabled false
            proguardFiles getDefaultProguardFile('proguard-android-optimize.txt'), 'proguard-rules.pro'
        }
    }

}

dependencies {
    implementation fileTree(dir: 'libs', include: ['*.jar'])
    implementation 'androidx.appcompat:appcompat:1.1.0'
    implementation 'androidx.constraintlayout:constraintlayout:1.1.3'
}

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.tistory.link2me.memo">

    <uses-permission android:name="android.permission.INTERNET" />

    <application
        android:allowBackup="false"
        android:icon="@mipmap/ic_launcher"
        android:label="@string/app_name"
        android:supportsRtl="true"
        android:usesCleartextTraffic="true" <!-- 서버와 http 통신할 경우 추가해야 정상동작 -->
        android:theme="@style/AppTheme">
        <activity android:name=".Alert"></activity>
        <activity android:name=".Edit_Note" />
        <activity android:name=".View_Note" />
        <activity android:name=".CreateNote" />
        <activity android:name=".MainActivity">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>

        <receiver
            android:name=".AlarmReceiver"
            android:enabled="true"
            android:exported="true" />
    </application>

</manifest> 

import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ImageView;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;

import androidx.appcompat.app.AppCompatActivity;

public class MainActivity extends AppCompatActivity {
    SQLiteDatabase db;
    DbHelper mDbHelper;
    ListView list;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        list = (ListView)findViewById(R.id.commentslist);
        mDbHelper = new DbHelper(this);
        db= mDbHelper.getWritableDatabase();
        final ImageView alarmImage = (ImageView) findViewById(R.id.alarmImage);

        String[] from = {mDbHelper.TITLE, mDbHelper.DETAIL, mDbHelper.TYPE, mDbHelper.TIME, mDbHelper.DATE};
        final String[] column = {mDbHelper.C_ID, mDbHelper.TITLE, mDbHelper.DETAIL, mDbHelper.TYPE, mDbHelper.TIME, mDbHelper.DATE};
        int[] to = {R.id.title, R.id.Detail, R.id.type, R.id.time, R.id.date};

        final Cursor cursor = db.query(mDbHelper.TABLE_NAME, column, null, null ,null, null, null);
        final SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, R.layout.list_entry, cursor, from, to, 0);

        list.setAdapter(adapter);
        list.setOnItemClickListener(new AdapterView.OnItemClickListener(){
            public void onItemClick(AdapterView<?> listView, View view, int position,
                                    long id){
                Intent intent = new Intent(MainActivity.this, View_Note.class);
                intent.putExtra(getString(R.string.rodId), id);
                startActivity(intent);
            }

        });

    }

    @Override
    public void onBackPressed() {
        moveTaskToBack(true);
    }

    @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_main, menu);
        return true;
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {

        switch(item.getItemId()) {
            case R.id.action_new:
                Intent openCreateNote = new Intent(MainActivity.this, CreateNote.class);
                startActivity(openCreateNote);
                return true;

            default:
                return super.onOptionsItemSelected(item);
        }
    }

}

 SQLiteDatabase db = getReadableDatabase(); // 읽기 가능한 데이터베이스

 Cursor cursor = db.rawQuery("select * from myTable where _idx = ?", new String[] { idx });

 SQLiteDatabase db = getReadableDatabase(); // 읽기 가능한 데이터베이스

 String sql = "SELECT idx, id, pw, name FROM member";

 Cursor cursor = db.rawQuery(sql, null);

 Cursor cursor = db.query(DB_TABLE,
    new String[] { KEY_ROWID, KEY_CATEGORY, KEY_SUMMARY, KEY_DESCRIPTION },
    null, null, null, null, null);

 query() 가 받아서 처리하는 인자의 순서
 1. 대상 테이블 이름(DB_TABLE)
 2. 값을 가져올 컬럼 이름의 배열
    String[] columns ={KEY_ROWID, KEY_CATEGORY, KEY_SUMMARY, KEY_DESCRIPTION};
 3. WHERE 구문. 물음표를 사용해 인자의 위치를 지정할 수 있다.

     "name=?"
 4. WHERE 구문에 들어가는 인자값

     String[] params ={"홍길동"};
 5. GROUP BY 구문
 6. ORDER BY 구문
 7. HAVING 구문
 테이블 이름을 제외한 각 값이 필요없는 경우라면 null을 지정한다.

 db.query(DBTable,columns,whereclause,whereargs,null,null,null);

 String query =
    "SELECT column1, (SELECT max(column1) FROM table1) AS max FROM table1 " +
    "WHERE column1 = ? OR column1 = ? ORDER BY column1";
 sqLiteDatabase.rawQuery(query, whereArgs);

SharedPreference

 key - value 으로 저장하여 모든 Intent에서 활용 가능

 주로 서버 로그인후 idx, userID 등의 정보를 저장하여 활용한다.

 앱이 삭제되면 저장된 정보는 삭제된다.

 Internal Storage

 안드로이드는 리눅스 운영체제를 수정하여 만들었으며,

 /data/data/어플리케이션패키지명/files 디렉토리에 저장한다.

 앱이 삭제되면 어플리케이션패키지명이 삭제되므로 이 공간에 저장된 파일들도

 삭제된다.

 External Storage

 외부저장공간은 탈착이 가능한 SD카드다.

 안드로이드 장치는 외부 저장 공간을 지원하므로

 외부 저장공간에 파일을 저장할 수 있다.

 누구나 읽을 수 있고 사용자에의해 변경될 수 있다.

 SQLite 데이터베이스

 구조화된 데이터를 DB에 저장한다.

 SQLite는 초경량 데이터베이스로 데이터를 디스크 파일에 저장한다.

 데이터 정의 명령어는 테이블을 생성/변경/삭제한다.

 데이터 조작 명령어는 데이터를 조회/추가/삭제/수정한다.

인자

내용 설명

 moveToFirst

커서가 쿼리(질의) 결과 레코드들 중에서 가장 처음에 위치한 레코드를 가리키도록 한다.

 moveToLast

 마지막 행으로 이동

 moveToNext

 다음 레코드로 커서를 이동

 moveToPrevious

 이전 레코드로 커서를 이동

 moveToPosition(int position)

 파라미터로 주어진 절대 행 위치로 이동한다. 성공적이면 true 반환

 getCount

 질의 결과값(레코드)의 갯수를 반환, 조건이 없으면 총개수 반환

 getColumnCount

 총 칼럼 개수를 반환

 getColumnIndex

 해당 이름의 칼럼의 0 베이스 칼럼 인덱스 값을 반환해준다.

 해당 이름의 칼럼이 존재하지 않으면 -1을 반환한다.

 getColumnIndexOrThrow

 해당 이름을 가지고 있는 칼럼의 0 베이스 인덱스 번호를 반환한다.
 해당 이름의 칼럼이 존재하지 않으면 IllegalArgumentException 을 발생시킨다.

 getColumnName

 특정 인덱스값에 해당하는 필드 이름을 반환

 getColumnNames

 칼럼의 이름들을 String(문자열) 배열 형태로 반환

 moveToPosition

 커서를 특정 레코드로 이동시킨다

 getPosition

 커서가 현재 가리키고 있는 위치를 반환

 getDouble

 해당 인덱스의 칼럼 값을 double 형으로 반환한다.

 close()

 열려있는 커서를 닫는다.

SQL 기본 문법은 http://www.w3schools.com/sql/default.asp 에서 익히면 된다.

SQLite 는 완전히 독립적이고 서버 기능을 제공하지 않으며, 트랜잭션을 지원하며 쿼리를 수행할 수 있는 표준 SQL 언어를 사용한다.

SQLite 만의 문법사항을 알아야만 편하다. 간단하게 SQLite 의 문법 사항을 알아보자.

ㅇ Data Type : Null, Integer(정수), Real(실수), Text(문자열), Blob

    - Integer : 부호있는 정수, 실제 값에 따라 1byte ~ 8bytes까지 가변적으로 저장됨

    - Boolean values are stored as integers 0 (false) and 1 (true).

    - Date and Time 데이터 타입은 없다. datetime은 입력되는 값에 따라 Text, Real, Integer 타입으로 저장됨

ㅇ JOIN : Left Outer Join, INNER Join 지원

ㅇ Rename Table, Add Column 지원 (Drop Column, Alter Column, Add Constraint 미지원)

    - 한번에 여러 Column 추가 안된다.

    - Join Update 를 지원하지 않는다.

ㅇ Grant and Revoke : 별도로 권한 부여 기능이 없고, OS 파일 시스템 권한을 사용

SQLite 에 대한 자세한 설명은 http://overoid.tistory.com/19 에 잘 설명되어 있다.

영문 원본을 읽어보려면 http://www.sqlite.org/datatype3.html 를 보면 된다.

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

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

public class SQLiteDBHandler extends SQLiteOpenHelper {
    public static final String TAG = "DBHelper";

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "mCRM.db";
    public static final String TABLE_NAME = "PBbook";

    public static final String Column_Uid = "uid";
    public static final String Column_Name = "name";
    public static final String Column_mobileNO ="mobileNO";
    public static final String Column_officeNO ="officeNO";
    public static final String Column_profile_image = "profile_image";

    public SQLiteDBHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String Member_Create = "create table "+ TABLE_NAME + " (" +
                "idx INTEGER PRIMARY KEY AUTOINCREMENT,"+
                "uid INTEGER not null," +
                "name TEXT not null," +
                "mobileNO TEXT not null," +
                "officeNO TEXT," +
                "profile_image TEXT);";
        db.execSQL(Member_Create);
        Log.v(TAG,"DB Created");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w("LOG_TAG", "Upgrading database from version " + oldVersion + " to " + newVersion
                + ", which will destroy all old data");
        // KILL PREVIOUS TABLES IF UPGRADED
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db); //새로 생성하기
        Log.v(TAG,"DB Updated");
    }

    public void InsertData(String uid,String name, String mobileNO, String officeNO,String profile_image) {
        SQLiteDatabase db = getWritableDatabase(); // 쓰기 가능한 데이터베이스를 가져와 입력

        // 이름 + 휴대폰번호 기준으로 중복 체크
        String query = "select idx from " + TABLE_NAME
                + " where " + Column_Name + "= '"+ name +"' and " + Column_mobileNO + "= '" + mobileNO + "'";
        Cursor cursor = db.rawQuery(query, null);
        cursor.moveToFirst(); // Cursor를 제일 첫행으로 이동
        if( cursor.getCount() == 0) {  // 중복이 없으면 저장하라.
            ContentValues cv = new ContentValues(); // 객체 생성
            cv.put(Column_Uid, uid);
            cv.put(Column_Name, name);
            cv.put(Column_mobileNO, mobileNO);
            cv.put(Column_officeNO, officeNO);
            cv.put(Column_profile_image, profile_image);
            db.beginTransaction();  // 대량건수 데이터 입력 처리를 고려
            try{
                long rowId = db.insert(TABLE_NAME, null, cv);
                if(rowId < 0){
                    throw new SQLException("Fail to Insert");
                }
                db.setTransactionSuccessful();
            } catch(Exception e){
                Log.i(TAG,e.toString());
            } finally {
                db.endTransaction();
                Log.v(TAG,"DB Inserted " + name + " uid =" + uid);
            }
        }
        cursor.close();
        db.close();
    }

    /* Get the first row Column_ID from the table */
    public int getFirstId() {
        int idToUpdate = 0;
        String query = "select idx from " + TABLE_NAME + " LIMIT 1";

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery(query, null);

        if (null != res && res.getCount() > 0) {
            res.moveToFirst(); // Cursor를 제일 첫행으로 이동
            idToUpdate = res.getInt(0);
        }
        return idToUpdate;
    }

    /* Update the table row with Column_ID - id */
    public boolean updateDB(Integer idx, String name, String mobileNO, String officeNO) {
        Log.i(TAG, "Updating Column_ID : " + idx);
        ContentValues cv = new ContentValues();
        cv.put(Column_Name, name);
        cv.put(Column_mobileNO, mobileNO);
        cv.put(Column_officeNO, officeNO);

        SQLiteDatabase db = this.getWritableDatabase();
        db.update(TABLE_NAME, cv, "idx = ? ", new String[]{Integer.toString(idx)});
        return true;
    }

    /* Delete the row with Column_ID - id from the employees table */
    public Integer deleteRow(Integer idx) {
        SQLiteDatabase db = this.getWritableDatabase();
        return db.delete(TABLE_NAME, "idx = ? ", new String[]{Integer.toString(idx)});
    }

    public int getTableRowCount() {
        String countQuery = "SELECT * FROM " + TABLE_NAME;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        Log.i(TAG, "Total Row : " + cursor.getCount());
        cursor.close();
        return cursor.getCount();
    }

    // Getting All Contacts
    public List<Address_Item> getAllPBook() {
        List<Address_Item> pbookData = new ArrayList<Address_Item>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_NAME;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst() && cursor.getCount() > 0) {
            do {
                Address_Item contact = new Address_Item();
                contact.setUid(cursor.getString(1));
                contact.setName(cursor.getString(2));
                contact.setMobileNO(cursor.getString(3));
                contact.setOfficeNO(cursor.getString(4));
                contact.setProfile_image(cursor.getString(5));
                // Adding contact to list
                pbookData.add(contact); // DB에서 받아온 값을 ArrayList에 Add
            } while (cursor.moveToNext());
        }
        cursor.close();
        return pbookData; // return contact list
    }
}

 public class Address_Item {
    // PersonData 정보를 담고 있는 객체 생성
    private String profile_image; // 이미지 경로를 String으로 받기 위해서
    private String uid;
    private String name;
    private String mobileNO;
    private String officeNO;
    boolean checkBoxState;

    public Address_Item() {
    }

    public Address_Item(String profile_image, String uid, String name, String mobileNO, String officeNO, boolean checkBoxState) {
        this.profile_image = profile_image;
        this.uid = uid;
        this.name = name;
        this.mobileNO = mobileNO;
        this.officeNO = officeNO;
        this.checkBoxState = checkBoxState;
    }

    public String getProfile_image() {
        return profile_image;
    }

    public void setProfile_image(String profile_image) {
        this.profile_image = profile_image;
    }

    public String getUid() {
        return uid;
    }

    public void setUid(String uid) {
        this.uid = uid;
    }

    public String getName() {
        return name;
    }

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

    public String getMobileNO() {
        return mobileNO;
    }

    public void setMobileNO(String mobileNO) {
        this.mobileNO = mobileNO;
    }

    public String getOfficeNO() {
        return officeNO;
    }

    public void setOfficeNO(String officeNO) {
        this.officeNO = officeNO;
    }

    public boolean isCheckBoxState() {
        return checkBoxState;
    }

    public void setCheckBoxState(boolean checkBoxState) {
        this.checkBoxState = checkBoxState;
    }
}

import android.app.Activity;
import android.app.ProgressDialog;
import android.content.ContentResolver;
import android.content.Context;
import android.content.SharedPreferences;
import android.net.Uri;
import android.os.AsyncTask;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;
import android.widget.Toast;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import java.util.List;

public class MainActivity extends AppCompatActivity {

    SQLiteDBHandler sqLiteDBHandler;
    public SharedPreferences settings;
    ProgressDialog mProgressDialog;

    TextView textView;
    Context context;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_sqlite2contact);

        textView = (TextView) findViewById(R.id.sqlite_txt);

        sqLiteDBHandler = new SQLiteDBHandler(MainActivity.this);
        // 서버에서 데이터를 가져와서 DB에 insert
        settings = getSharedPreferences("settings", Activity.MODE_PRIVATE);
        Uri.Builder builder = new Uri.Builder()
                .appendQueryParameter("idx", settings.getString("idx",""));
        String postParams = builder.build().getEncodedQuery();
        new getOrgChartData().execute(Value.IPADDRESS + "/get_json.php",postParams);

        // 저장버튼을 클릭하면 SQLite DB에 저장된 데이터를 폰의 주소록에 저장하는 기능
        Button btn_contactSave = (Button) findViewById(R.id.btn_sqlite_save);
        btn_contactSave.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                List<Address_Item> contacts = sqLiteDBHandler.getAllPBook();
                int num = 0;
                for(Address_Item item : contacts){
                    num++;
                    int rawContactId = 0;
                    Contacts phonebook = new Contacts(); // 전화번호부 객체 생성
                    ContentResolver cr = getContentResolver();
                    String strContactName = item.getName();
                    String strMobileNO = item.getMobileNO();
                    String strofficeNO = item.getOfficeNO();
                    String strEmail = "";
                    String strPhoto = "";
                    if (item.getProfile_image().length() > 0) {
                        strPhoto = Value.IPADDRESS + "/photos/" + item.getProfile_image();
                    }
                    rawContactId = phonebook.ContactsIDExistCheck(cr, strContactName);
                    if (rawContactId > 0) {
                        // 기존 전화번호가 존재하면 삭제하고 새로 입력
                        phonebook.ContactsIDdelete(cr, context, rawContactId);
                    }
                    phonebook.ContactsIDinsert(cr, context, strContactName, strMobileNO, strofficeNO, strEmail, strPhoto);
                }
                Toast.makeText(getApplicationContext(), "총" + num + "개 연락처가 저장되었습니다.", Toast.LENGTH_LONG).show();
            }
        });

    }

    class getOrgChartData extends AsyncTask<String, Void, String> {
        @Override
        protected void onPreExecute() {
            super.onPreExecute();
            // Create a progressdialog
            mProgressDialog = new ProgressDialog(MainActivity.this);
            mProgressDialog.setTitle("Personal Profile JSON Parse");
            mProgressDialog.setMessage("Loading...");
            mProgressDialog.setIndeterminate(false);
            mProgressDialog.show();  // Show progressdialog
        }

        @Override
        protected String doInBackground(String... params) {
            try {
                return PHPComm.getJson(params[0],params[1]);
            } catch (Exception e) {
                return new String("Exception: " + e.getMessage());
            }
        }

        protected void onPostExecute(String result){
            searchJSON=result;
            showJSONObjectList();
            mProgressDialog.dismiss();
        }
    }

    // 서버 정보를 파싱하기 위한 변수 선언
    String searchJSON;
    private static final String TAG_RESULTS="result";
    private static final String TAG_UID = "uid"; // 서버 테이블의 실제 필드명
    private static final String TAG_NAME = "userNM";
    private static final String TAG_MobileNO ="mobileNO";
    private static final String TAG_OfficeNO ="telNO";
    private static final String TAG_Image = "photo"; // 이미지 필드
    JSONArray peoples = null;

    protected void showJSONObjectList() {
        try {
            JSONObject jsonObj = new JSONObject(searchJSON);
            peoples = jsonObj.getJSONArray(TAG_RESULTS);

            for(int i=0;i<peoples.length();i++){
                JSONObject c = peoples.getJSONObject(i);
                final String uid = c.getString(TAG_UID);
                final String name = c.getString(TAG_NAME);
                final String mobileNO = c.getString(TAG_MobileNO);
                final String officeNO = c.getString(TAG_OfficeNO);
                final String Profile_Image = c.getString(TAG_Image);

                // 서버에서 가져온 데이터 저장
                sqLiteDBHandler.InsertData(uid,name, mobileNO, officeNO,Profile_Image);
            }

        } catch (JSONException e) {
            e.printStackTrace();
        }

        textView.setText(String.valueOf(sqLiteDBHandler.getTableRowCount()));
    }

}

저장하기

 SharedPreferences settings = getSharedPreferences("PrefName", MODE_PRIVATE);
 SharedPreferences.Editor editor = settings.edit();
 editor.putString(KEY_MY_PREFERENCE, text);
 editor.commit();

 불러오기

 SharedPreferences settings = getSharedPreferences("PrefName", MODE_PRIVATE);
 String text = settings.getString(KEY_MY_PREFERENCE, "");