[Android] DB (SQLite)

1. DB 생성

DBHelper.java 생성.

mytable : table / tx : text타입의 column / _id integer : 무조건 필수인 column.

public class DBHelper extends SQLiteOpenHelper {
    public DBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "CREATE TABLE if not exists mytable ("
                + "_id integer primary key autoincrement,"
                + "txt text);";

        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String sql = "DROP TABLE if exists mytable";

        db.execSQL(sql);
        onCreate(db);
    }
}

 

2. MainActivity에 코드추가

public class MainActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        
        DBHelper helper;
        SQLiteDatabase db;
        helper = new DBHelper(MainActivity.this, "newdb.db", null, 1);
        db = helper.getWritableDatabase();
        helper.onCreate(db);
    }
}

 

3. INSERT문

(1) ContentValues를 만들어서 column이름과 data를 넣고 db.insert()함수 사용방법

ContentValues values = new ContentValues();
values.put("txt","HelloAlpaca");
db.insert("mytable",null,values);

(2)execSQL를 사용한 방법

String sql = "INSERT INTO mytable('txt') values('Bulgogi');";
db.execSQL(sql);

4. DELETE문

(1) db.delete() 함수 사용방법

db.delete("mytable","txt=?",new String[]{"Gogi"});

(2) where clauses가 1개 이상이라면 아래와 같이 AND문으로 구현

db.delete("mytable","txt=? AND num=?",new String[]{"Gogi","1"});

(3) sql문을 변경해서 execSQL함수를 사용해 DELETE를 구현방법

String sql3 = "DELETE FROM mytable WHERE txt='Bulgogi';";
db.execSQL(sql3);

 

5. UPDATE문 사용법

db.update() 함수를 사용해서 구현

ContentValues values = new ContentValues();
values.put("txt","Chicken");
db.update("mytable",values,"txt=?", new String[]{"Helloalpaca"});

 

6. SELECT문 사용법

db.query() 함수를 사용해서 구현

Cursor c = db.query("mytable",null,null,null,null,null,null,null);
while(c.moveToNext()){
	System.out.println("txt : "+c.getString(c.getColumnIndex("txt")));
}