SQLite : DataBase helper 객체 이용하기

데이터베이스의 버전 업데이트 관리를 위해서 Helper 클래스를 이용하는 방법에 대해 설명 합니다. 헬퍼 클래스를 사용하려면 SQLiteOpenHelper 클래스를 상속해서 Helper 클래스를 만들고, 객체를 만들어서 사용하면 됩니다. 

1-1) SQLiteOpenHelper 클래스 만들기 

class DatabaseHelper extends SQLiteOpenHelper {


}

1-2) onCreate() 메소드 , onUpgrade 메소드 implement 실행하기

@Override
public void onCreate(SQLiteDatabase db) {
 	}

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

1-3) onOpen() 메소드 만들기

public void onOpen(SQLiteDatabase db){

}

1-4) 생성자 만들기 

public DatabaseHelper(Context context ,String name, SQLiteDatabase.CursorFactory factory, int version) {
            super(context, name, factory, version);
}

1-5) SQLiteOpenHelper 클래스 전체 code

    class DatabaseHelper extends SQLiteOpenHelper {
        String tableName;
        public DatabaseHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version,String tableName) {
            super(context, name, factory, version);
            this.tableName = tableName;
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            println( "onCreate 호출 됨" );
            String sql ="create table if not exists " + tableName +"(_id integer PRIMARY KEY autoincrement, name text, age text)";
            db.execSQL( sql );
            println( "table 생성됨" );
        }
        public void onOpen(SQLiteDatabase db){
            println("onOpen 호출됨");
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            println("onUpgrade 호출됨." + oldVersion + "," + newVersion);

            if (newVersion > 1){
                db.execSQL("drop table if exists " + tableName);
                println("table 삭제 함");

                String sql ="create table if not exists " + tableName +"(_id integer PRIMARY KEY autoincrement, name text, age text)";
                db.execSQL(sql);
                println( "table 새로 생성됨" + tableName );
            }
        }
    }

2-1) 기존 createDatabase 호출 블럭에서 new DatabaseHelper 객체 만든후 helper.getWritableDatabase(); 

public void createDatabase(String tableName){
 String tableName = editText2.getText().toString().trim();
 DatabaseHelper helper = new DatabaseHelper(this, tableName , null, 1);
 database = helper.getWritableDatabase();

 println( "database 생성함 " + tableName );
}

//기존 database 생성법
database = openOrCreateDatabase(tableName, MODE_PRIVATE, null);

2-2) 전체 코드 

public class MainActivity extends AppCompatActivity {
    Button button, button2,button3,button4,button5,button6;
    EditText editText,editText2,editText3,editText4,editText5,editText6;
    TextView textView;
    SQLiteDatabase database;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate( savedInstanceState );
        setContentView( R.layout.activity_main );
        userfindId();
        userfuction();

    }
    private void  userfindId(){
        button = (Button) findViewById( R.id.button );
        button2 = (Button) findViewById( R.id.button2 );
        button3 = (Button) findViewById( R.id.button3 );
        button4 = (Button) findViewById( R.id.button4 );
        /*
        button5 = (Button) findViewById( R.id.button5 );
        button6 = (Button) findViewById( R.id.button6 );
        */
        editText = (EditText) findViewById( R.id.editText );
        editText2 = (EditText) findViewById( R.id.editText2 );
        editText3 = (EditText) findViewById( R.id.editText3 );
        editText4 = (EditText) findViewById( R.id.editText4 );
        textView = (TextView) findViewById( R.id.textView );
    }
    private void  userfuction(){
        //open or create database
        button.setOnClickListener( new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                String databaseName = editText.getText().toString().trim();
                createDatabase( databaseName );
            }
        } );
        //create table
        button2.setOnClickListener( new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                String tableName = editText2.getText().toString().trim();
                createTable( tableName );
            }
        } );
        //insert data info
        button3.setOnClickListener( new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                String tableName = editText2.getText().toString().trim();
                String name = editText3.getText().toString().trim();
                String age = editText4.getText().toString().trim();
                insertInfo( tableName, name, age );
            }
        } );
        //select data
        button4.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                String tableName = editText2.getText().toString().trim();
                executeQuery(tableName);
            }
        });
    }
    public void createDatabase(String name){
        //println( "createDatabase 호출 됨" );
        /*
        database = openOrCreateDatabase( name, MODE_PRIVATE, null );
        */
        String tableName = editText2.getText().toString().trim();
        DatabaseHelper helper = new DatabaseHelper(this, name , null, 6, tableName );
        database = helper.getWritableDatabase();

        println( "database 생성함 " + name );
    }
    public void createTable(String name){
        println( "create Table 호출 됨" );
        if (database != null){
            String sql ="create table if not exists " + name +"(_id integer PRIMARY KEY autoincrement, name text, age text)";
            try {
                database.execSQL( sql );
                println( "table 생성됨" + name );
            } catch (SQLException e){
                println(" 에러 발생됨 "+ e.getMessage());
            }
        } else {
            println( "database db를 생성 하시오" );
            return ;
        }
    }
    public void insertInfo(String tableName, String name, String age){
        if(database != null) {
            String sql = "insert into " + tableName + "(name, age) " + "values(?,?)";
            Object[] params = {name, age};
            database.execSQL( sql, params );
            println( "데이터 추가함."+ name +" : " + age  );
        } else {
            println( "먼저 데이터베이스를 오픈하세요" );
        }
    }
    public void executeQuery(String tableName){
        println("executeQuery 호출 됨");

        String sql = "select _id, name, age from " + tableName;
        Cursor cursor = database.rawQuery(sql, null);

        println("레코드 개수" + cursor.getCount());

        for(int i=0; i < cursor.getCount(); i++){
            cursor.moveToNext();
            int id = cursor.getInt(0);
            String name = cursor.getString(1);
            String age = cursor.getString(2);
            println("레코드#" + i + "id : "+ id +" : " + name + " , " + age);
        }
        cursor.close();
    }

    public void println(String data){
        textView.append( data + "\n" );
    }

    class DatabaseHelper extends SQLiteOpenHelper {
        String tableName;
        public DatabaseHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version,String tableName) {
            super(context, name, factory, version);
            this.tableName = tableName;
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            println( "onCreate 호출 됨" );
            String sql ="create table if not exists " + tableName +"(_id integer PRIMARY KEY autoincrement, name text, age text)";
            db.execSQL( sql );
            println( "table 생성됨" );
        }
        public void onOpen(SQLiteDatabase db){
            println("onOpen 호출됨");
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            println("onUpgrade 호출됨." + oldVersion + "," + newVersion);

            if (newVersion > 1){
                db.execSQL("drop table if exists " + tableName);
                println("table 삭제 함");

                String sql ="create table if not exists " + tableName +"(_id integer PRIMARY KEY autoincrement, name text, age text)";
                db.execSQL(sql);
                println( "table 새로 생성됨" + tableName );
            }
        }
    }
}

3) 코드 링크 : https://github.com/rain2002kr/sampleDataBase_helper.git