마카로닉스

291 No

안드로이드 android19_SQLite(select)

2017-09-03 18:46:12 By 머나먼나라 0 796 댓글수 4 포스트 하기

4월 2024
일	월	화	수	목	금	토
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30

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>

ProductDTO

package com.example.choi.ex03_db.dto;

/**
 * Created by choi on 2017-02-11.
 */

public class ProductDTO {

    private int id;
    private String product_name;
    private int price;
    private int amount;


    public ProductDTO(){

    }


    public ProductDTO(int id, String product_name, int price, int amount) {
        this.id = id;
        this.product_name = product_name;
        this.price = price;
        this.amount = amount;
    }

    public ProductDTO( String product_name, int price, int amount) {

        this.product_name = product_name;
        this.price = price;
        this.amount = amount;
    }


    public String getProduct_name() {
        return product_name;
    }

    public void setProduct_name(String product_name) {
        this.product_name = product_name;
    }

    public int getPrice() {
        return price;
    }

    public void setPrice(int price) {
        this.price = price;
    }

    public int getAmount() {
        return amount;
    }

    public void setAmount(int amount) {
        this.amount = amount;
    }


    public int getId() {
        return id;
    }

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


    @Override
    public String toString() {
        return "ProductDTO{" +
                "id=" + id +
                ", product_name='" + product_name + '\'' +
                ", price=" + price +
                ", amount=" + amount +
                '}';
    }
}

ProductDAO.class

package com.example.choi.ex03_db.dao;

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

import com.example.choi.ex03_db.dto.ProductDTO;

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

/**
 * Created by choi on 2017-02-11.
 */

// Context :  문맥, 흐름, 현재 실행중인 화면
public class ProductDAO {

    Context context;
    SQLiteDatabase db;

    //Context context =activity
    public ProductDAO(Context context){
        this.context=context;
    }

//  Context.MODE_PRIVATE : 단독 사용 모드
    public SQLiteDatabase dbConn(){
        //데이터베이스를 오픈하거나 생성
        db=context.openOrCreateDatabase("product.db", Context.MODE_PRIVATE, null);

        //테이블이 존재하지 않으면  create
        // 자동증가컬럼은 자료형을 integer 로 하고 primary key 로 설정
        Log.v("메시지",  "dbConn() start");
        try{

            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 이외의 쿼리

        }catch (Exception e){
            e.printStackTrace();
            Log.v("메시지",  "dbConn() execSQL : " + e.getMessage());
        }

        return db;
    }

    public void insert(ProductDTO dto){
        SQLiteDatabase db=null;
        try{

            db =dbConn();
            Log.v("메시지",  "insert() start");
            String sql =String.format(" insert into product " +
                    " ( product_name , price, amount ) values " +
                    " ('%s' , %d, %d ) ", dto.getProduct_name(), dto.getPrice(), dto.getAmount() );
            db.execSQL(sql); // select  이외의 쿼리 실행
        }catch (Exception e){
            Log.v("메시지",  "error :" + e.getMessage());
            e.printStackTrace();
        }finally {
            if(db!=null) db.close(); //db 닫기
        }
    }



    public List<ProductDTO> list(){

        List<ProductDTO>  items =new ArrayList<>();
        SQLiteDatabase db =null;
        Cursor cursor =null; //결과셋, 레코드셋

        try{

            db =dbConn();
            // select * from product 로 * 를 쓰지말고 컬럼이름을 나열 해야 한다.
            // 왜냐 하면 cursor.getInt(0); 컬럼 이름은 사용할 수 없음 때문이다.
            String sql =" select id, product_name , price, amount" +
                    " from product order by product_name " ;

            // select 쿼리를 실행하여 결과셋을 커서에 리턴함
            // execSQL()  - select  이외의 쿼리
            // rawQuery() - select 쿼리 전용
            // rawQuery(sql , null) - null 자리에 조건 검색 을 쓰면 된다.
            // 그러나 쿼리에 조건절을 써도 된다.
            cursor =db.rawQuery(sql, null);
            //커서.moveToNext() 다음 레코드가 존재하면 true 리턴
            while(cursor.moveToNext()){
            //커서.get자료형(컬럼의 인텍스) 0 부터, 컬럼 이름은 사용할 수 없음
                int id =cursor.getInt(0);
                String product_name =cursor.getString(1);
                int price=cursor.getInt(2);
                int amount=cursor.getInt(3);
                items.add(new ProductDTO(id, product_name, price, amount));

            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(cursor!=null) cursor.close();
            if(db !=null)db.close();
        }
        return items;
    }






}

SQLite(select)

ProductAcitivity

package com.example.choi.ex03_db;

import android.content.Context;
import android.content.Intent;
import android.os.Bundle;
import android.support.annotation.NonNull;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.ListView;
import android.widget.TextView;

import com.example.choi.ex03_db.dao.ProductDAO;
import com.example.choi.ex03_db.dto.ProductDTO;

import java.util.List;

public class ProductAcitivity extends AppCompatActivity {

    //1.변수 선언
    Button btnAdd;
    ListView list;
    ProductDAO dao;
    List<ProductDTO> items;

    //화면 생성될 때 호출
    @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);
        dao=new ProductDAO(this);
        //3.이벤트 처리
        btnAdd.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Intent intent=new Intent(ProductAcitivity.this, ProductAddActivity.class);
                startActivity(intent);
            }
        });

    }


    // onCreate() => onStart() => onResume()
    // 화면생성      화면시작전      화면재시작 - onResume 항상 호출된다.
    @Override
    protected void onResume() {

        super.onResume(); //지우면 안된다.

        items=dao.list();
        // Log.옵션("태그 " , " 출력할 내용" )
        Log.i("test" , "상품목록 : " + items);
/*

        String[] product_name =new String[items.size()];
        for(int i=0; i<product_name.length; i++){
            product_name[i]=items.get(i).getProduct_name();
        }

        ArrayAdapter adapter=new ArrayAdapter(this,
                android.R.layout.simple_list_item_1, product_name);
*/
// 데이터 => 아답터 => 뷰

        MyAdapter adapter =new MyAdapter(this, R.layout.product_row, items);
        list.setAdapter(adapter);
    }

    //커스텀 아답터(내부 클래스)
    class MyAdapter extends ArrayAdapter<ProductDTO>{
        //생성자 (Constructor)
        public MyAdapter(Context context, int resource, List<ProductDTO> objects) {
            super(context, resource, objects);
        }

/*

        attachToRoot 팽창 된 계층을 첨부할지 어떨지
        * 루트 매개 변수? false 인 경우, root는
        XML의 루트 뷰에 대한 올바른 LayoutParams 서브 클래스.
                * @return 팽창 된 계층 구조의 루트 뷰입니다. 루트가 제공되고
        * attachToRoot가 참이면 루트입니다. 그렇지 않으면 그것은 XML 파일.

        inflate(XmlPullParser parser, @Nullable ViewGroup root)
        inflate(XmlPullParser parser, @Nullable ViewGroup root, boolean attachToRoot)
*/

        @NonNull //린턴값은 null 이 될 수 없음
        @Override
        public View getView(int position, View convertView, ViewGroup parent) {

            View v =convertView; //현재 출력할 자식뷰

            if(v==null){ //최초 1 개만 생성
                //레이아웃 생성기
                LayoutInflater li=(LayoutInflater)getLayoutInflater();
                v=li.inflate(R.layout.product_row, null);
            }

            //현재 자식뷰에 출력시킬 데이터
            ProductDTO dto =items.get(position);

            //dto의 값을 product_row.xml 위젯에 출력시킴
            //컨텍스트.findViewById() 메인뷰의 위젯
            //자식뷰.findViewById() 자식뷰의 위젯
            TextView txtProductName=(TextView)v.findViewById(R.id.txtProductName);
            TextView txtPrice =(TextView)v.findViewById(R.id.txtPrice);
            TextView txtAmount=(TextView)v.findViewById(R.id.txtAmount);

            txtProductName.setText(dto.getProduct_name());
            txtPrice.setText("가격:"+ dto.getPrice());
            txtAmount.setText("수량 : "+dto.getAmount());

            return v;
        }
    }



}

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>

product_row.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical" android:layout_width="match_parent"
    android:layout_height="match_parent">

    <TextView
        android:text="TextView"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/txtProductName"
        android:textSize="30sp"
        android:textColor="@android:color/holo_blue_dark" />

    <TextView
        android:text="TextView"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/txtPrice"
        android:textSize="20sp"
        android:textColor="@android:color/holo_green_dark" />

    <TextView
        android:text="TextView"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/txtAmount"
        android:textSize="20sp"
        android:textColor="@android:color/holo_orange_dark" />


</LinearLayout>

ProductAddActivity

package com.example.choi.ex03_db;

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

import com.example.choi.ex03_db.dao.ProductDAO;
import com.example.choi.ex03_db.dto.ProductDTO;

public class ProductAddActivity extends AppCompatActivity {

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


    @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);

        dao=new ProductDAO(this);

        //3.버튼 클릭 이벤트
        //이벤트소스.이벤트리스너(이벤트핸들러)
        btnSave.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                String product_name=editProductName.getText().toString();

                int price =Integer.parseInt(editPrice.getText().toString());

                int amount =Integer.parseInt(editAmount.getText().toString());

                ProductDTO dto
                            =new ProductDTO(product_name, price, amount);

                dao.insert(dto);

                Toast.makeText(ProductAddActivity.this, "저장 되었습니다.",
                        Toast.LENGTH_SHORT).show();
                finish(); //현재 화면을 종료함
            }
        });
    }





}

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>