※임베드 DB

● 스마트폰에 내장된 DB
● 저메모리
● 빠른 처리속도
● 오픈소스 : http://sqlite.org/famouse.html
● 표준 SQL 지원

● 지원하지 않는 것 : http://sqlite.org/omitted.html

   
※ 특징

● 매니페스트 타입 사용

    ● 컬럼 데이터타입에 해당하지 않는 타입도 저장 가능
    ● SQL

     - 1) RIGHT and FULL OUTER JOIN 지원 안함
     - 2) RENAME TABLE and ADD COULUMN 만 지원
     - 3) ROW tiggers 지원
     - 4) VIEWs in SQLite are read-only
     - 5) GRANT and REVOKE 지원 안함    

 

 

SQLite 개요
SQLite API
OB 작업
 

 

 

 

 

MainActivity 

 

package com.example.choi.ex03_db;

import android.content.Intent;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;

public class MainActivity extends AppCompatActivity {

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


    //버튼을 눌렀을때 호출 되는 메서드
    public void onClick(View v){
        Intent intent =null;
        switch (v.getId()){
            case R.id.button1:
               intent =new Intent(this, ProductAcitivity.class);
               break;
        }

        startActivity(intent);
    }





}

 

main.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/main"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context="com.example.choi.ex03_db.MainActivity">

    <LinearLayout
        android:orientation="vertical"
        android:layout_width="match_parent"
        android:layout_height="match_parent">

        <Button
            android:text="상품목록"
            android:onClick="onClick"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:id="@+id/button1" />
    </LinearLayout>

</RelativeLayout>

 

 

 

ProductAcitivity

package com.example.choi.ex03_db;

import android.content.Intent;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.Button;
import android.widget.ListView;

public class ProductAcitivity extends AppCompatActivity {

    //1.변수 선언
    Button btnAdd;
    ListView list;


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

        //2.객체 생성
        btnAdd=(Button)findViewById(R.id.btnAdd);
        list=(ListView)findViewById(R.id.list);
        //3.이벤트 처리
        btnAdd.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Intent intent=new Intent(ProductAcitivity.this, ProductAddActivity.class);
                startActivity(intent);

            }
        });


    }


}

product_acitivity.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/product_acitivity"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context="com.example.choi.ex03_db.ProductAcitivity">

    <Button
        android:text="추가"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentTop="true"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true"
        android:id="@+id/btnAdd" />

    <ListView
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:layout_below="@+id/btnAdd"
        android:id="@+id/list" />
</RelativeLayout>

ProductAddActivity 

 

 

package com.example.choi.ex03_db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class ProductAddActivity extends AppCompatActivity {

    //1.변수 선언
    EditText editProductName ,editPrice, editAmount;
    Button btnSave;
    SQLiteDatabase db; // sqlite 객체

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

        //2. 객체 생성
        editProductName=(EditText)findViewById(R.id.editProductName);
        editPrice=(EditText)findViewById(R.id.editPrice);
        editAmount=(EditText)findViewById(R.id.editAmount);

        btnSave=(Button)findViewById(R.id.btnSave);

        //데이터베이스를 오픈하거나 생성
        db=openOrCreateDatabase("product.db", Context.MODE_PRIVATE, null);

        String sql ="create table if not exists product (" +
                " id integer primary key autoincrement , " +
                " product_name varchar(50) not null , " +
                " price int  not null ," +
                " amount int not null )";
        db.execSQL(sql);  // select 이외의 쿼리
        Toast.makeText(this, "데이터베이스가 생성 되었습니다. ",
                Toast.LENGTH_SHORT).show();

    }



}

 

product_add.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/product_add"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context="com.example.choi.ex03_db.ProductAddActivity">

    <EditText
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:inputType="textPersonName"
        android:ems="10"
        android:id="@+id/editProductName"
        android:hint="상품이름을 입력하세요" />

    <EditText
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:inputType="number"
        android:ems="10"
        android:layout_marginTop="77dp"
        android:id="@+id/editAmount"
        android:hint="수량을 입력하세요"
        android:layout_below="@+id/editPrice"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true" />

    <EditText
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:inputType="number"
        android:ems="10"
        android:id="@+id/editPrice"
        android:hint="가격을 입력하세요"
        android:layout_below="@+id/editProductName"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true"
        android:layout_marginTop="37dp" />

    <Button
        android:text="저장"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/editAmount"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true"
        android:layout_marginTop="35dp"
        android:id="@+id/btnSave" />


</RelativeLayout>

 

android17_SQLite 테이블 생성

 

※임베드 DB

 

● 스마트폰에 내장된 DB

● 저메모리

● 빠른 처리속도

● 오픈소스 : http://sqlite.org/famouse.html

● 표준 SQL 지원

 

● 지원하지 않는 것 : http://sqlite.org/omitted.html

 

 

※ 특징

 

● 매니페스트 타입 사용

 

● 컬럼 데이터타입에 해당하지 않는 타입도 저장 가능

● SQL

 

- 1) RIGHT and FULL OUTER JOIN 지원 안함

- 2) RENAME TABLE and ADD COULUMN 만 지원

- 3) ROW tiggers 지원

- 4) VIEWs in SQLite are read-only

- 5) GRANT and REVOKE 지원 안함

 

※ DB 접속

● SQLiteOpenHelper

●DB파일명, DB버전 정보 설정(SQLite DB는 파일별로 관리됨)

- onCreate: db 최초 생성시 한번 호출

- onUpgrade : DB버전이 올라갔을 때만 호출

●SQLiteDatebase에서 DB 연결

 

● getWriteableDatabase();

● getReadableDatabase();

 

※ 테이블 작업

●삭제

● db.execSQL(“drop table...”);

● 생성

● db.execSQL(“create table tbl (id integer primary key autoincrement , name text,

var real)“);

 

※ 데이터 조회

●실행 방법

●rawQuery() : select 문 직접 실행

●query() : 메소드 인자로 각 부분의 값을 넘겨 실행

- 테이블명, 컬럼 이름 배열, where 구문, where 인자값, group by, order by , having

 

●SQLiteQueryBuilder 클래스

- 컨텐트 프로바이더에 적용 가능

 

●조회 결과는 Cursor 이용

● Cursor 에서는 여러 건의 결과를 하나씩 받아오면서 처리 가능

 

 

platform tools 디렉토리로 이동

 

c:\Users\사용자계정\AppData\Local\Android\sdk\platform-tools

 

adb shell

 

안드로이드 리눅스 모드로 들어간다. 이것은 android Device Monitor 가 켜진 상태이어야 한다.

C:\Users\choi>adb shell

generic_x86:/ $

 

D 나 F에있어서 안될 경우 직접 JAV_HOME처럼 설정 > 환경 설정변수에서 path 경로를 잡아준다.

%%

 

DB 접근이 안되면 su

# su

generic_x86:/ $ su generic_x86:/ # ls -l total 3688 drwxr-xr-x 30 root root 0 2017-02-11 09:17 acct drwxrwx--- 6 system cache 4096 2017-02-07 13:41 cache lrwxrwxrwx 1 root root 13 1970-01-01 00:00 charger -> /sbin/healthd drwxr-xr-x 3 root root 0 2017-02-11 09:17 config lrwxrwxrwx 1 root root 17 1970-01-01 00:00 d -> /sys/kernel/debug drwxrwx--x 36 system system 4096 2017-02-11 09:17 data -rw-r--r-- 1 root root 928 1970-01-01 00:00 default.prop drwxr-xr-x 14 root root 2400 2017-02-11 09:17 dev lrwxrwxrwx 1 root root 11 1970-01-01 00:00 etc -> /system/etc -rw-r--r-- 1 root root 76613 1970-01-01 00:00 file_contexts.bin -rw-r----- 1 root root 943 1970-01-01 00:00 fstab.goldfish -rw-r----- 1 root root 968 1970-01-01 00:00 fstab.ranchu -rwxr-x--- 1 root root 1486420 1970-01-01 00:00 init -rwxr-x--- 1 root root 887 1970-01-01 00:00 init.environ.rc -rwxr-x--- 1 root root 2924 1970-01-01 00:00 init.goldfish.rc -rwxr-x--- 1 root root 2368 1970-01-01 00:00 init.ranchu.rc -rwxr-x--- 1 root root 25583 1970-01-01 00:00 init.rc -rwxr-x--- 1 root root 9283 1970-01-01 00:00 init.usb.configfs.rc -rwxr-x--- 1 root root 5715 1970-01-01 00:00 init.usb.rc -rwxr-x--- 1 root root 411 1970-01-01 00:00 init.zygote32.rc drwxr-xr-x 10 root system 220 2017-02-11 09:17 mnt drwxr-xr-x 2 root root 0 1970-01-01 00:00 oem dr-xr-xr-x 103 root root 0 2017-02-11 09:17 proc -rw-r--r-- 1 root root 4757 1970-01-01 00:00 property_contexts drwx------ 2 root root 0 2016-10-04 14:46 root drwxr-x--- 2 root root 0 1970-01-01 00:00 sbin lrwxrwxrwx 1 root root 21 1970-01-01 00:00 sdcard -> /storage/self/p imary -rw-r--r-- 1 root root 758 1970-01-01 00:00 seapp_contexts -rw-r--r-- 1 root root 79 1970-01-01 00:00 selinux_version -rw-r--r-- 1 root root 177921 1970-01-01 00:00 sepolicy -rw-r--r-- 1 root root 11167 1970-01-01 00:00 service_contexts drwxr-xr-x 5 root root 100 2017-02-11 09:19 storage dr-xr-xr-x 12 root root 0 2017-02-11 09:17 sys drwxr-xr-x 16 root root 4096 1970-01-01 00:00 system -rw-r--r-- 1 root root 323 1970-01-01 00:00 ueventd.goldfish.rc -rw-r--r-- 1 root root 323 1970-01-01 00:00 ueventd.ranchu.rc -rw-r--r-- 1 root root 4853 1970-01-01 00:00 ueventd.rc lrwxrwxrwx 1 root root 14 1970-01-01 00:00 vendor -> /system/vendor generic_x86:/ # pwd /

 

# cd data/data/package명/databases

data/data/com.example.choi.ex03_db/databases

 

 

#sqlite3 DB명.db

#sqlite3 product.db

 

 

sqlite> .table android_metadata product

 

insert into product (product_name ,price, amount ) values ('냉장고', 60000, 1)

sqlite> insert into product (product_name ,price, amount ...> ) ...> values ('refre', 5000, 5) ...> ;

insert into product (product_name ,price, amount ) values ('냉장고', 60000, 5)

 

sqlite> select * from product ...> ; 1|refre|5000|5 2|냉장고|60000|1

 

# ls

225771279.  

215888784. * from table명;
215888784. _id, name from table 명;

 

 

SQLite 개요

SQLite API

OB 작업