#nullcolumnhack

[jacob-cs]

original source : http://stackoverflow.com/questions/32774507/what-does-nullcolumnhack-means

nullColumnHack optional; may be null. SQL doesn't allow inserting a completely empty row without naming at least one column name. If your provided values is empty, no column names are known and an empty row can't be inserted. If not set to null, the nullColumnHack parameter provides the name of nullable column name to explicitly insert a NULL into in the case where your values is empty.

SQLiteOpenHelper obj의 getWritableDatabase () 을 통해 얻어진 SQLiteDatabase obj의 method인 insert() 함수에 두번째 파라미터로 nullColumnHack을 지정할수 있다. empty row가 전달되었을때 여기에 지정된 칼럼을 null로 설정함으로써 다른 빈 칼럼들이 들어간 row를 추가 할수 있게 된다.

[infotainmentplus-blog]

A SQLite primer for Android app developers Sometimes, you need to store more complex data in your app than just simple key/value pairs saved with a text file or Shared Preferences. Databases are ideal for storing complex data structures and are particularly suited to storing records, where each block of data stored uses the same fields, formatted in the same manner. This works like a table or an Excel spreadsheet, and, like Excel, it allows for much more dynamic manipulation and logical organization of data. It’s thanks to databases that many machine-learning and big data applications are possible. Databases also make everyday tools like Facebook possible. As a result it’s a skill in high demand. Programmers will eventually need to learn to use databases This is why programmers will eventually need to learn to use databases. That way, your data will be organized and you’ll have no difficulty retrieving passwords, user data or whatever other information you need. And this also happens to be a great way to store data on an Android device as well. To do all this, we’ll be using SQLite. Introducing SQLite SQL databases are relational databases where data is stored in tables. The Structured Query Language (SQL) is the declarative language used to query those databases so that you can add, remove and edit data. For more on SQL itself, check out this article. SQLite is an implementation of a relational database, specifically aimed for embedded scenarios. It’s ideal for the likes of an Android app. The easiest way to imagine a relational database is to think of it as a series of tables. What’s cool is SQLite doesn’t require a dedicated relational database management system (RDBMS)— it is used directly from your code, rather than via a server or external resource. Your data is saved into a file locally on your device, making it a powerful and surprisingly easy way to store persistent data on Android. SQLite is open-source, easy to use, portable, and highly cross-compatible. There’s no need to install anything additional if you want to start using SQLite in Android Studio. Android provides the classes which you can use to handle your database. Android developers can use the SQLiteOpenHelper to use SQL commands. That’s what we’ll be looking at in this post. In the next few sections, you’ll learn create a table this way and in the process, you’ll hopefully start to feel comfortable with SQLite, SQL, and databases in general. Creating your first database Start a new empty Android Studio project. Now create a new class by right-clicking the package on the left and choosing New > Java Class. I’ve called mine ‘Database’. We want to extend SQLiteOpenHelper class and so enter that as the superclass. To recap: this means we’re inheriting methods from that class, so our new class can act just like it. Right now, your code will be underlined red because you need to implement the inherited methods and add the constructor. The finished article should look like so: package com.androidauthority.sqliteexample; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; public class Database extends SQLiteOpenHelper { public Database(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) { super(context,name,factory, version); } @Override public void onCreate(SQLiteDatabase db) { } @Override public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) { } } The first thing to do is to simplify our constructor. Add these variables: public static final int DATABASE_VERSION = 1; public static final String DATABASE_NAME = "MyDatabase.db"; With that done, update your constructor like so: public Database(Context context) { super(context,DATABASE_NAME,null, DATABASE_VERSION); } Break it down and you can see that we’re calling our database ‘MyDatabase.db’. Now, whenever we make a new Database object from this class, the constructor will build that database for us. Creating tables Now we’re ready to start populating it with some data! This data takes the form of a table and hopefully you’ll see why this is useful. What kind of thing might we use a database for in the real world? Well, how about CRM – customer relationship management? This is what big companies use to keep track of their customers’ details. It’s how they know to call us with special offers in which we may be interested. It’s how your magazine subscription always knows when it’s time for a renewal – that might be a good example to use. In other words, we’re using our powers for evil. To that end, we’re going to need some more variables so that we can build our table and start populating it with data. Logically, that might look something like this: public static final String TABLE_NAME = "SUBSCRIBERS"; public static final String COLUMN_NAME = "NAME"; public static final String COLUMN_MAGAZINE_TITLE = "MAGAZINE_TITLE"; public static final String COLUMN_RENEWAL_DATE= "RENEWAL_DATE"; public static final String COLUMN_PHONE = "PHONE_NUMBER"; Now the publishers who we’re building our app for will be able to query when a certain use is due for a renewal and easily grab their phone number to give them a buzz. Imagine trying to do this without SQL; you’d be forced to create multiple text files with different names for each user, or one text file with an index so you know which line to retrieve information from different text files. Then you’d have to delete and replace each entry manually with no way to check when things got out of sync. Searching for information by name would be a nightmare. You might end up using your own made-up shorthand. It would get very messy, very fast. While it might be possible to avoid using tables with a little creativity— all this can be a little daunting at first— it is an invaluable skill to learn in the long run and will actually make your life a lot easier. It’s also pretty much required if you ever have dreams of becoming a ‘full stack’ developer or creating web apps. SQL is pretty much required if you ever have dreams of becoming a ‘full stack developer' or creating web apps. To build this table, we need to use execSQL. This lets us talk to our database and execute any SQL command that doesn’t return data. So it’s perfect for building our table to begin with. We’re going to use this in the onCreate() method, which will be called right away when our object is created. @Override public void onCreate(SQLiteDatabase db) { db.execSQL("create table " + TABLE_NAME + " ( " + COLUMN_NAME + " VARCHAR, " + COLUMN_MAGAZINE_TITLE + " VARCHAR, " + COLUMN_RENEWAL_DATE + " VARCHAR, " + COLUMN_PHONE + " VARCHAR);"); } What’s happening here is we’re talking to our database and telling it to create a new table with a specific table name, which we’ve defined in our string. If we break the rest of that long ugly string down, it actually contains a number of easy-to-understand SQL commands: create table + TABLE_NAME( COLUMN_NAME + VARCHAR, COLUMN_MAGAZINE_TITLE + VARCHAR, COLUMN_RENEWAL_DATE + VARCHAR, COLUMN_PHONE + VARCHAR) SQLite will also add another column implicitly called rowid, which acts as a kind of index for retrieving records and increases incrementally in value with each new entry. The first record will have the rowid ‘0’, the second will be ‘1’, and so on. We don’t need to add this ourselves but we can refer to it whenever we want. If we wanted to change the name of a column, we would manually create one with the variable INTEGER PRIMARY KEY . That way, we could turn our ‘rowid’ into ‘subscriber_id’ or something similar. The rest of the columns are more straightforward. These are going to contain characters (VARCHAR) and they will each be named by the variables we created earlier. Here is a good resource where you can see the SQL syntax on its own for this command and many others. If we break the string down, it actually contains a number of easy-to-understand SQL commands The other method, onUpgrade, is required for when the database version is changed. This will drop or add tables to upgrade to the new schema version. Just populate it and don’t worry about it: @Override public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME); onCreate(db); } DROP TABLE is used to delete the existing data. Here we’re deleting the table if it already exists before rebuilding it. See the previous post for more. If all that’s in place, you’ve built your first database. Well done! In future, if we to refer to a database that was already created, then we would use getReadableDatabase() or getWriteableDatabase() to open the database ready for reading-from or writing-to. Inserting data To insert new data as a row, simply use db.insert(String table, String nullColumnHack, ContentValues). But what are ContentValues? This is a class used by Android that can store values to be resolved by the ContentResolver. If we create a ContentValues object and fill it with our data, we can pass that to our database for assimilation. It looks like this: contentValues.put(COLUMN_NAME, "Adam"); contentValues.put(COLUMN_MAGAZINE_TITLE, "Women's World"); contentValues.put(COLUMN_RENEWAL_DATE, "11/11/2018"); contentValues.put(COLUMN_PHONE, "00011102"); db.insert(TABLE_NAME, null, contentValues); db.close(); Another option would be to use database.execSQL() and input the data manually: db.execSQL("INSERT INTO " + TABLE_NAME + "(" + COLUMN_NAME + "," + COLUMN_MAGAZINE_TITLE + "," + COLUMN_RENEWAL_DATE + "," + COLUMN_PHONE + ") VALUES('Adam','Women's World','11/11/2018','00011102')"); db.close(); This does the exact same thing. Remember to always close the database when you’re finished with it. You weren’t brought up in a barn, were you? Optional Of course, to really use this database properly, we would probably want to populate our columns using objects. We could use the following class to add new subscribers to our list: public class SubscriberModel { private String ID, name, magazine, renewal, phone; public String getID() { return ID; } public String getName() { return name; } public String getRenewal() { return renewal; } public String getMagazine() { return magazine; } public String getPhone() { return phone; } public void setName(String name) { this.name = name; } public void setMagazine(String magazine) { this.magazine = magazine; } public void setRenewal(String renewal) { this.renewal = renewal; } public void setPhone(String phone) { this.phone = phone; } } Then we could easily build as many new subscribers as we liked and take the variables from there. Better yet, we can also retrieve data from our database this way to build new objects. For instance, we might use something like the following to read through a list of clients and then populate an array list using those objects. This uses a ‘cursor’, which you’ll learn about in the next section. public ArrayList getAllRecords() { SQLiteDatabase db = this.getReadableDatabase(); Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null); ArrayList subs = new ArrayList(); Subscribers subscribers; if (cursor.getCount() > 0) { for (int i = 0; i

[night-finance-site-blog]

データベース SQLiteを利用する SQliteOpenHelper(Content context, String name, SQLiteDatabase. CursorFactory factory, int version) void onCreate(SQLiteDatabase db) void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) SQLiteを操作する SQLiteDatabase getReadableDatabase() SQLiteDatabase getWriteableDatabase() void execSQL(String sql) throws SQLException void beginTransaction() void setTransactionSuccessful() void endTransaction() long insert(String table, String nullColumnHack, ContentValues valuse) int update(String table, ContentValues values, String whereClause, String[] whereArgs) int delete(String table, String whereClause, Stirng[] whereArgs) Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, Stirng orderBy, String limit) テーブルを作成する

テーブルに挿入する db.insert(SQLiteDBHelper.DB_TABLE, null, values); データを更新する db.update(SQLiteDBHelper.DB_TABLE, values, "book = 'Androidポケ���'", null); データをコミットする mDb.beginTransaction(); mDb.execSQL( "insert into " = + SQLiteDBHelper.DB_TABLE+ " (book, type) values ('Web+DB Press', '雑誌');"); mDb.setTransactionSuccessful(); mDb.endTransaction(); データを削除する db.delete(SQLiteDBHelper.DB_TABLE, "book like 'Android%'", null); データを検索する db.query( SQLiteDBHelper.DB_TABLE, SQLiteDBHelper.POKERI_PORODUCTION, null, null, null, null, null); クリップボード クリップボードからテキストを取得する clipMgr.getPrimaryClip(); clipData.getItemAt(0); item.getTExt().toString(); clipMgr.getText().toString(); クリップボードにテキストを設定する void setText(CharSequence text) ClipData.Item(CharSequence text) ClipData(ClipDescription description, ClipData.Item item) ClipDescription(CharSequence label, String[] mimeTypes) void setPrimaryClip(ClipData clip) ClipData.Item(et_target.getText()); ClipDescription.MIMETYPE_TEXT_PLAIN; ClipData(new ClipDescription("text_plain",mimeType), item); clipMgr.setPrimaryClip(clipData); clipMgr.setTExt(et_target.getText()); ローダ ローダを利用してデータを読み込む getSupportLoaderManager().initLoader(0, null, this); getSupportLoaderManager().restartLoader(0, null, this); onCreateLoader(int id, Bundle args) onLoadFinished(Loader<Cursor> loader, Cursor data) onLoaderReset(Loader<Cursor> loader) コンテンツプロバイダ コンテンツプロバイダの概要 コンテンツプロバイダのデータを検索する ContentResolver getContentResolver() Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) boolean moveToFirst() boolean moveToNext() int getString(int columnIndex) int getColumnIndex(String columnName) コンテンツプロバイダのデータを挿入/更新/削除する Uri insert(Uri uri, ContentValues values) Uri update(Uri uri, ContentValues values, String where, String[] selectionArgs) int delete(Uri uri,String where, String[] selectionArgs) void put(String key, String value) ブックマークを取得/登録/更新/削除する BOOKMARK_URI _ID BOOKMARK CREATED DATE FAVICON TITLE UriVISITS com.android.browser.permission.READ_HISTORY_BOOKMARKS com.android.browser.permission.WRITE_HISTORY_BOOKMARKS ブックマークを取得する //クエリで取得する項目 PRIVATE sTRING[] bookmark_production = new String[] ( BookmarkColumn._ID BookmarkColumns.TITLE, BookmarkColumns.Uri }; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); //ブックマークの名前取得 ContentResolver resolver = getContentResolver(); Cursor cursor = resolver.Query(Browser.BOOKMARKS_URI, BOOKMARK_PRODUCTION, null, null, null); SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, android.R.layout.simple_list_item_2, cursor, new String[] (Bookmarkcolumns,TITLE, BookmarkColumns.URL), new int[] (android.R.id.text1, android.R.id.text2), 0); ListView lv_bookmark = (ListView) findViewById(R.id.lv_bookmark); lv_bookmark.setAdapter(adapter); } ブックマークを登録する //ブックマークの追加 ContentValues values = new ContentValues(); values.put(Browser.BookmarkColumns.BOOKMARK, "1"); values.put(Browser.BookmarkColumns.TITLE, title); values.put(Browser.BookmarkColumns.URI, uri); getContentResolver().insert(Browser.BOOKMARKS_URI, values); ブックマークを更新する //タイトルが一致したものを更新 ContentValues values = new ContentValues(); values.put(Browser.BookmarkColumns.BOOKMARK, "1"); values.put(Browser.BookmarkColumns.TITLE, title); values.put(Browser.BookmarkColumns.URI, uri); getContetnResolver().update( Browser.BOOKMARKS_URI, values, Browser.BookmarkColumns.TITLE + " = '" + title + "'", null); ブックマークを削除する //タイトルが一致いたものを削除 getContentResolver().delete( Browser.BOOKMARKS_URI, Browser.BookmarkColumns.TITLE + " = '" + title + "'", null); 連絡先の情報を取得する _ID CONTENT_URI DISPLAY_NAME NUMBER android.permission.READ_CONTACTS カレンダーを取得/登録/更新/削除する CONTENT_URI CONTENT_URI DISTART DTEND EVENT_TIMEZONE TITLEDESCRIPTION CALENDER_ID android.permission.READ_CALENDAR android.permisiion.WRITE_CALENDAR カレンダーを取得する カレンダーに予定を登録する カレンダーの予定を修正する カレンダーから予定を削除する

[night-finance-site-blog]

データベース SQLiteを利用する SQliteOpenHelper(Content context, String name, SQLiteDatabase. CursorFactory factory, int version) void onCreate(SQLiteDatabase db) void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) SQLiteを操作する SQLiteDatabase getReadableDatabase() SQLiteDatabase getWriteableDatabase() void execSQL(String sql) throws SQLException void beginTransaction() void setTransactionSuccessful() void endTransaction() long insert(String table, String nullColumnHack, ContentValues valuse) int update(String table, ContentValues values, String whereClause, String[] whereArgs) int delete(String table, String whereClause, Stirng[] whereArgs) Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, Stirng orderBy, String limit) テーブルを作成する

テーブルに挿入する

データを更新する

データをコミットする

データを削除する

データを検索する

[night-finance-site-blog]

書籍管理アプリ

書籍管理アプリ

activity_main.xml <?xml version="1.0" encoding="utf-8?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical"> <---> <Button android:id="@+id/btnSave" android:layout_width="match_parent"android:layout_height="wrap_content" android:onclick="onSave" android:text="text" /> <Button android:id="@id/btnDelete" android:layout_width="match_parent" android:layout_height="wrap_content" android:onclick="onDelete" android:text="text" /> <Button android:id="@+id/btnSearch" android:layout_width="match_parent" android:layout_height="wrap_content" android:onClick="onSearch" android:text="text" /> <---> <TableLayout android:layout_width="match_parent" android:layout_height="match_parent" android:stretchColumns="1"> <TableRow android:layout_width="match_parent" android:layout_height="match_parent"> <TextView android:id="@+id/tvIsbn" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="ISBNtext" /> <EditText android:id="@+id/txtIsbn" android:layout_width="match_parent" android:layout_height="wrap_content"> <requestFocus /> </EditText> </TableRow> <TableRow android:layout_width="match_parent" android:layout_height="match_parent"> <TableRow android:layout_width="match_parent" android:layout_height="match_parent"> <TextView android:id="@+id/tvTitle" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="text" /> <EditText android:id="@+id/txtTitle" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="text" /> <EditText android:id="@+id/txtTitle" android:layout_width="match_parent" android:layout_height="wrap_content" /> </TableRow> <TableRow android:layout_width="match_parent" android:layout_height="match_parent"> <textView android:id="@+id/tvPrice android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="text" /> <EditText android:id="@+id/txtPrice" android:layout_width="match_parent" android:layout_height="wrap_content" /> </TableRow> </TableLayout> </LinearLayout>

MainActivity.java package to.msn.wings.databasebasic; import android.content.ContentValues; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.support.v7.app.AppCompatAtivity; import android.os.Bundle; import android.view.View; import android.widget.EditText; import android.widget.Toast; public class MainActivity extends AppCompatActivity { private SimpleDatabaseHelper helper = null; private EditText txtIsbn = null; private EditText txtTitle = null; private EditText txtPrice = null; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState);setContentView(R.layout.activity_main); // helper = new SimpleDatabaseHelper(this); txtIsbn = (EditText) findViewById(R.id.txtIsbn); txtTitle = (EditText) findViewById(R.id.txtTitle); txtPrice = (EditText) findViewById(R.id.txtPrice); } // public void onSave(View view) { SQLiteDatabase db = helper.getWritableDatabase(); try { ContentValues cv = new ContentValues(); cv.put("isbn", txtIsbn.getText().toString()); cv.put("title", txtTitlegetText().toString()); cv.put("price", txtPrice.getPrice.getText().toString()); +db.insertWithOnConflict("books", null, cv, SQLiteDatabase.CONFLICT_REPLACE); -db.insert("books", null, cv); Toast.makeText(this, "text", Toast.LENGTH_SHORT).show(); } finally { db.close(); } } // public void onDelete(View view) { SQLiteDatabase db = helper.getWritableDatabase(); try { String[] params = {txtIsbn.getText().toString()}; db.delete("book", "isbn = ?", params); Toast.makeText(this, "text", Toast.LENGTH_SHORT).show(); } finally { db.close(); } } // public void onSearch(View view) { SQLiteDatabase db = helper.getReadableDatabase(); Cursor cs = null; try { String[] cols = {"isbn", "title", "price"}; String[] params = {txtIsbn.getText().toString()}; params, null, null, null); if(cs.moveToFirast()) { txtTitle.setText(cs.getString(1)); txtPrice.setText(cs.getString(2)); } else { Toast.makeText(this, "text", Toast.LENGTH_SHORT).show(); } } finally { cs.close(); db.close(); } } }

putメソッド public long insert(String table, String nullColumnHack, ContentValues values) deleteメソッド public int delete(String table, String whereClause, String[] whereArgs) queryメソッド public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) getXxxxメソッド public abstract T getXxxxx(int columnIndex) insertWithOnConflictメソッド public long insertWithOnConflict(String table, String nullColumnHack, ContentValues values, int conflictAlgorithm) updateメソッド public int update(String table, ContentValues values, String whereClause, String[] whereArgs)

[night-finance-site-blog]

import java.io.BufferedReader; import java.io.InputStreamReader; Intent i = new Intent(Intent.ACTION_OPEN_DOCUMENT); i.setType("text/plain"); i.putExtra(Intent.EXTRA_TITLE, "memo.txt"); starActivityForResult(i, 2); if (requestCode == 2 && resultCode == RESULT_OK) { StringBuilder str = new StringBuilder(); try(BufferedReader reader = new BufferedReader(new InputStreamReader( getContentResolver().openInputStream(data.getData())))) { String line; while ((line = reader.readLine()) != null) { str.append(line); str.append(System.getProperty("line.separator")); } } catch (IOException e) { e.printStackTrace(); } txtMemo.setText(str.toString()); } SQLiteOpenHelperコンストラクター public SQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) onCreateメソッド public abstract void onCreate(SQLiteDatabase db) execSQLメソッド public void execSQL(String sql) onUpgradeメソッド public abstract void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) String[] isbns = {"978-4-7980-4512-2", "978-4-7980-4179-7", "978-4-7741-8030-4",}; String[] titles = {"", "", ""}; int[] prices = {300, 3500, 2680, 2780, 3200}; db.beginTransaction(); try { SQLiteStatement sql = db.compileStatement( "INSERT INTO books(isbn, title, price) VALUES(?, ?, ?)"); for (int i = 0; i < isbns.length; i++) { sql.bindString(1, isbns[i]); sql.bindString(2, titles[i]); sql.bindLong(3, prices[i]); sql.executeInsert(); } sb.setTransactionSuccessful(); } catch (SQLException e) { e.printStackTrace(); } finally { db.endTransaction(); } compleStatement public SQLiteStatement compileStatement(String sql) bindXxxxメソッド public void bindXxxxx(int index, xxxx value) executeInsertメソッド public long executeInsert() putメソッド public void put(String key, T value) insertメソッド public long insert(String table, String nullColumnHack, ContentValues values) deleteメソッド public int delete(String table, String whereClause, String[] whereArgs) queryメソッド public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, Stirng groupBy, String having, String orderBy, String limit) getXxxxメソッド public abstract T getXxx(int columnIndex) db.insertWithOnConflict("books", null, cv, SQLiteDatabase.CONFLICT_REPLACE); insertWithOnConflictメソッド public long insertWithOnConflict(String table, String nullColumnHack, ContentValues values, int conflictAlgorithm) updateメソッド public int update(String table, ContentValues values, String whereClause, String[] whereArgs) addPreferencesFromResourceメソッド public void addPreferencesFromResource(int resId) getDeafaultSharePreferencesメソッド public staticPreferncesメソッド public static SharePreferences getDefaultSharedPreferences(Context context) getXxxxメソッド public abstract T getXxx(String key, T defValue) <activity android:name=".MyConfigActivity"> </activity>

[night-finance-site-blog]

データベース書き込み

void put(String colname, String value)

int upgrade(String tableName, ContentValues values, String where, String[] whereArgs)

long insert(String tableName, String nullColumnHack, ContentValues values)