DB를 활용하여 우편번호 가져오기

Posted on: 2018년 04월 16일 2018년 04월 16일
Categories: 강의자료, 안드로이드

Layout, DTO, Adapter, Listener 생성하기
app/src/main/assets/post.dat 만들기
1. app/src/main/assets 폴더 생성 : app 마우스 오른쪽 버튼 클릭 -> New -> Folder -> Assets Folder
2. post.dat를 다운받아 assets폴더에 붙여넣기

MainDBHelper.java 수정

public class MainDBHelper extends SQLiteOpenHelper {
    private Context context;

    private static final String PostTable = "post";
    private static final int DB_VER = 15;

    public MainDBHelper(Context context) {
        super(context, "mydb", null, DB_VER);
        this.context = context;
    }

    // Post 테이블 생성
    private void f_CreateTable_Post(SQLiteDatabase db) {
        String sql = "";

        // 테이블 삭제
        try {
            db.execSQL("drop table if exists `" + PostTable + "`");
        } catch (Exception ex) {
            Log.e("MYTAG", "Exception in DROP_SQL", ex);
        }

        // 테이블 생성
        try {
            sql = "CREATE TABLE `" + PostTable + "` ("
                    + "  `_id`	INTEGER PRIMARY KEY AUTOINCREMENT"
                    + ", `post`	TEXT"
                    + ", `sido`	TEXT"
                    + ", `sigu`	TEXT"
                    + ", `eupm`	TEXT"
                    + ", `road`	TEXT"
                    + ", `even`	TEXT"
                    + ");";
            Log.v("MYTAG", sql);
            db.execSQL(sql);
        } catch (Exception ex) {
            Log.e("MYTAG", "SQL : " + sql + "\nException in CREATE_SQL", ex);
        }

        // 테이블 삽입(Assets의 post.dat 파일을 읽어 parsing하여 삽입)
        try {
            AssetManager assetManager = context.getAssets();
            BufferedReader reader = new BufferedReader(new InputStreamReader(assetManager.open("post.dat"), "utf8"));
            String buf = "";

            while ((buf = reader.readLine()) != null) {
                String[] tokens = buf.split("\\|");
                if (tokens != null && tokens.length > 4) {
                    sql = "INSERT INTO `" + PostTable
                            + "`(`post`, `sido`, `sigu`, `eupm`, `road`) VALUES ("
                            + "'" + tokens[0] + "'"
                            + ",'" + tokens[1] + "'"
                            + ",'" + tokens[2] + "'"
                            + ",'" + tokens[3] + "'"
                            + ",'" + tokens[4] + "');";
                    Log.v("MYTAG", sql);
                    db.execSQL(sql);
                }
            }
            reader.close();
        } catch (Exception ex) {
            Log.e("MYTAG", "SQL : " + sql + "\nException in Master Table insert SQL", ex);
        }
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.v("MYTAG", "onCreate() 실행");

        f_CreateTable_Post(db);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.v("MYTAG", "Upgrading database from version " + oldVersion + " to " + newVersion + ".");

        f_CreateTable_Post(db);
    }
}

public class MainDBHelper extends SQLiteOpenHelper {

private Context context;

private static final String PostTable = "post";

private static final int DB_VER = 15;

public MainDBHelper(Context context) {

super(context, "mydb", null, DB_VER);

this.context = context;

}

// Post 테이블 생성

private void f_CreateTable_Post(SQLiteDatabase db) {

String sql = "";

// 테이블 삭제

try {

db.execSQL("drop table if exists `" + PostTable + "`");

} catch (Exception ex) {

Log.e("MYTAG", "Exception in DROP_SQL", ex);

}

// 테이블 생성

try {

sql = "CREATE TABLE `" + PostTable + "` ("

+ " `_id` INTEGER PRIMARY KEY AUTOINCREMENT"

+ ", `post` TEXT"

+ ", `sido` TEXT"

+ ", `sigu` TEXT"

+ ", `eupm` TEXT"

+ ", `road` TEXT"

+ ", `even` TEXT"

+ ");";

Log.v("MYTAG", sql);

db.execSQL(sql);

} catch (Exception ex) {

Log.e("MYTAG", "SQL : " + sql + "\nException in CREATE_SQL", ex);

}

// 테이블 삽입(Assets의 post.dat 파일을 읽어 parsing하여 삽입)

try {

AssetManager assetManager = context.getAssets();

BufferedReader reader = new BufferedReader(new InputStreamReader(assetManager.open("post.dat"), "utf8"));

String buf = "";

while ((buf = reader.readLine()) != null) {

String[] tokens = buf.split("\\|");

if (tokens != null && tokens.length > 4) {

sql = "INSERT INTO `" + PostTable

+ "`(`post`, `sido`, `sigu`, `eupm`, `road`) VALUES ("

+ "'" + tokens[0] + "'"

+ ",'" + tokens[1] + "'"

+ ",'" + tokens[2] + "'"

+ ",'" + tokens[3] + "'"

+ ",'" + tokens[4] + "');";

Log.v("MYTAG", sql);

db.execSQL(sql);

}

reader.close();

} catch (Exception ex) {

Log.e("MYTAG", "SQL : " + sql + "\nException in Master Table insert SQL", ex);

}

@Override

public void onCreate(SQLiteDatabase db) {

Log.v("MYTAG", "onCreate() 실행");

f_CreateTable_Post(db);

}

@Override

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

Log.v("MYTAG", "Upgrading database from version " + oldVersion + " to " + newVersion + ".");

f_CreateTable_Post(db);

}

PostDAO.java 생성

public class PostDAO {
    private String tableName = "post";
    private Context m_context;

    public PostDAO(Context context) {
        this.m_context = context;
    }

    // DB연결 메소드
    public SQLiteDatabase getConn() {
        MainDBHelper dbHelper = new MainDBHelper(m_context);
        SQLiteDatabase db = dbHelper.getWritableDatabase();

        return db;
    }

    // 테이블 전체 내용을 ArrayList로
    public ArrayList<PostDTO> getArrayList(String road) {
        ArrayList<PostDTO> arList = new ArrayList<PostDTO>();
        SQLiteDatabase db = getConn();
        String sql;
        Cursor cursor;

        // 목록 작성 시작
        sql = "SELECT * FROM `" + tableName + "` WHERE `road` like '"+ road + "%' ORDER BY `_id` ASC ;";
        cursor = db.rawQuery(sql, null);

        while(cursor.moveToNext()){
            PostDTO dto = new PostDTO();
            dto._id = cursor.getInt(cursor.getColumnIndexOrThrow("_id"));
            dto.post = cursor.getString(cursor.getColumnIndexOrThrow("post"));
            dto.sido = cursor.getString(cursor.getColumnIndexOrThrow("sido"));
            dto.sigu = cursor.getString(cursor.getColumnIndexOrThrow("sigu"));
            dto.eupm = cursor.getString(cursor.getColumnIndexOrThrow("eupm"));
            dto.road = cursor.getString(cursor.getColumnIndexOrThrow("road"));
            dto.even = cursor.getString(cursor.getColumnIndexOrThrow("even"));
            dto.addr = dto.sido + " " + dto.sigu + " " + dto.eupm + " " + dto.road;
            arList.add(dto);
        }
        cursor.close();
        // 목록 작성 끝

        return arList;
    }// getRecordDTO()
}

public class PostDAO {

private String tableName = "post";

private Context m_context;

public PostDAO(Context context) {

this.m_context = context;

}

// DB연결 메소드

public SQLiteDatabase getConn() {

MainDBHelper dbHelper = new MainDBHelper(m_context);

SQLiteDatabase db = dbHelper.getWritableDatabase();

return db;

}

// 테이블 전체 내용을 ArrayList로

public ArrayList<PostDTO> getArrayList(String road) {

ArrayList<PostDTO> arList = new ArrayList<PostDTO>();

SQLiteDatabase db = getConn();

String sql;

Cursor cursor;

// 목록 작성 시작

sql = "SELECT * FROM `" + tableName + "` WHERE `road` like '"+ road + "%' ORDER BY `_id` ASC ;";

cursor = db.rawQuery(sql, null);

while(cursor.moveToNext()){

PostDTO dto = new PostDTO();

dto._id = cursor.getInt(cursor.getColumnIndexOrThrow("_id"));

dto.post = cursor.getString(cursor.getColumnIndexOrThrow("post"));

dto.sido = cursor.getString(cursor.getColumnIndexOrThrow("sido"));

dto.sigu = cursor.getString(cursor.getColumnIndexOrThrow("sigu"));

dto.eupm = cursor.getString(cursor.getColumnIndexOrThrow("eupm"));

dto.road = cursor.getString(cursor.getColumnIndexOrThrow("road"));

dto.even = cursor.getString(cursor.getColumnIndexOrThrow("even"));

dto.addr = dto.sido + " " + dto.sigu + " " + dto.eupm + " " + dto.road;

arList.add(dto);

}

cursor.close();

// 목록 작성 끝

return arList;

}// getRecordDTO()

}

DialogPostDB.java 생성

public class DialogPostDB extends Dialog implements View.OnClickListener{
    private Context m_context = null;
    private DialogPostListener m_dialogPostListener;


    public DialogPostDB(@NonNull Context context, DialogPostListener clickListener) {
        super(context);
        this.m_context = context;
        this.m_dialogPostListener = clickListener;
    }

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);

        WindowManager.LayoutParams params = new WindowManager.LayoutParams();
        params.flags = WindowManager.LayoutParams.FLAG_DIM_BEHIND;
        params.dimAmount = 0.8f; //뒤 배경의 어두운 정도를 나타내는 수치
        getWindow().setAttributes(params);
        setContentView(R.layout.dialog_post);

        ((Button)this.findViewById(R.id.btnSearch)).setOnClickListener(this);
    }

    @Override
    public void onClick(View view) {
        switch (view.getId()){
            case R.id.btnSearch :
                PostDAO dao = new PostDAO(m_context);
                String road = ((EditText)findViewById(R.id.txtRoad)).getText().toString();
                road = "천잠로";
                final ArrayList<PostDTO> arList = dao.getArrayList(road);
                ListView listView = findViewById(R.id.lstAddr);
                PostAdapter adapter = new PostAdapter(this.getContext(), arList);
                listView.setAdapter(adapter);
                listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
                    @Override
                    public void onItemClick(AdapterView<?> adapterView, View view, int position, long id) {
                        String post = arList.get(position).post;
                        String addr1 = arList.get(position).addr;
                        m_dialogPostListener.onPositiveClicked(post, addr1);
                        dismiss();
                    }
                });
                break;
       }
    }
}

public class DialogPostDB extends Dialog implements View.OnClickListener{

private Context m_context = null;

private DialogPostListener m_dialogPostListener;

public DialogPostDB(@NonNull Context context, DialogPostListener clickListener) {

super(context);

this.m_context = context;

this.m_dialogPostListener = clickListener;

}

@Override

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

WindowManager.LayoutParams params = new WindowManager.LayoutParams();

params.flags = WindowManager.LayoutParams.FLAG_DIM_BEHIND;

params.dimAmount = 0.8f; //뒤 배경의 어두운 정도를 나타내는 수치

getWindow().setAttributes(params);

setContentView(R.layout.dialog_post);

((Button)this.findViewById(R.id.btnSearch)).setOnClickListener(this);

}

@Override

public void onClick(View view) {

switch (view.getId()){

case R.id.btnSearch :

PostDAO dao = new PostDAO(m_context);

String road = ((EditText)findViewById(R.id.txtRoad)).getText().toString();

road = "천잠로";

final ArrayList<PostDTO> arList = dao.getArrayList(road);

ListView listView = findViewById(R.id.lstAddr);

PostAdapter adapter = new PostAdapter(this.getContext(), arList);

listView.setAdapter(adapter);

listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {

@Override

public void onItemClick(AdapterView<?> adapterView, View view, int position, long id) {

String post = arList.get(position).post;

String addr1 = arList.get(position).addr;

m_dialogPostListener.onPositiveClicked(post, addr1);

dismiss();

}

});

break;

}

FragRegister.java 수정(DialogPostDB를 호출하는 클래스 수정)

@Override
public void onClick(View view) {
	switch (view.getId()){
		case R.id.btnPost :
			DialogPostDB m_dlgPost = new DialogPostDB(getActivity(), dlg_onClickListener);
			m_dlgPost.setCancelable(true);
			m_dlgPost.getWindow().setGravity(Gravity.CENTER);
			m_dlgPost.show();
			break;
	}
}

@Override

public void onClick(View view) {

switch (view.getId()){

case R.id.btnPost :

DialogPostDB m_dlgPost = new DialogPostDB(getActivity(), dlg_onClickListener);

m_dlgPost.setCancelable(true);

m_dlgPost.getWindow().setGravity(Gravity.CENTER);

m_dlgPost.show();

break;

}

whan0623

517