在android上的sqlite数据库中创建表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10349337/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-20 03:23:23 来源:igfitidea点击:
Creating tables in sqlite database on android
提问by Slayonie
@Override
public void onCreate(SQLiteDatabase db)
{
try{
db.execSQL("create table " + NotificationManager.getUserStatic(context) + "log ("+
KEY_TIME +" INTEGER primary key, "+ KEY_TEXT +" TEXT not null);");
}
catch (SQLException e) {
e.printStackTrace();
}
}
/**
* onOpen method called when app is opening.
*/
@Override
public void onOpen(SQLiteDatabase db)
{
try{
System.out.println("tophere");
db.execSQL("create table if not exists "+DATABASE_NAME+"." + NotificationManager.getUserStatic(context) + "log ("+
KEY_TIME +" INTEGER primary key, "+ KEY_TEXT +" TEXT not null);");
System.out.println("downhere");
}
catch (SQLException e){
e.printStackTrace();
}
}
We have this code for creating a database for an app. problem is, as far as we can tell, it doesn't create the tables, so when we try to insert into the tables and read from them, it crashes. we have tried everything, the System.out.println's are there to see where it fails. we get the tophere out, but it never gets to the downhere part in the log, so we are guessing something is causing it to fail there. we have checked with DDMS that the database is in the correct folder, so the database should be there, but for some reason it cant find it(line 2 and 3 in the log).
我们有为应用程序创建数据库的代码。问题是，据我们所知，它不会创建表，所以当我们尝试插入表并从中读取时，它崩溃了。我们已经尝试了所有方法，System.out.println 会在那里查看失败的地方。我们得到了顶部，但它永远不会到达日志中的下方部分，所以我们猜测是什么导致它在那里失败。我们已经用 DDMS 检查了数据库在正确的文件夹中，所以数据库应该在那里，但由于某种原因它找不到它（日志中的第 2 行和第 3 行）。
Any thoughts?
有什么想法吗？
this is the error log:
这是错误日志：
04-27 10:45:46.768: I/System.out(6441): tophere
04-27 10:45:46.772: I/SqliteDatabaseCpp(6441): sqlite returned: error code = 1, msg = unknown database NOPO, db=/data/data/dmri.nopo/databases/NOPO
04-27 10:45:46.772: W/System.err(6441): android.database.sqlite.SQLiteException: unknown database NOPO: , while compiling: create table if not exists NOPO.log (time INTEGER primary key, text TEXT not null);
04-27 10:45:46.792: W/System.err(6441): at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
04-27 10:45:46.792: W/System.err(6441): at android.database.sqlite.SQLiteCompiledSql.(SQLiteCompiledSql.java:68)
04-27 10:45:46.811: W/System.err(6441): at android.database.sqlite.SQLiteProgram.compileSql(SQLiteProgram.java:134)
04-27 10:45:46.811: W/System.err(6441): at android.database.sqlite.SQLiteProgram.compileAndbindAllArgs(SQLiteProgram.java:361)
04-27 10:45:46.811: W/System.err(6441): at android.database.sqlite.SQLiteStatement.acquireAndLock(SQLiteStatement.java:260)
04-27 10:45:46.811: W/System.err(6441): at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:84)
04-27 10:45:46.811: W/System.err(6441): at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1899)
04-27 10:45:46.823: W/System.err(6441): at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1839)
04-27 10:45:46.823: W/System.err(6441): at dmri.nopo.DBAdapter$DatabaseHelper.onOpen(DBAdapter.java:67)
04-27 10:45:46.823: W/System.err(6441): at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:180)
04-27 10:45:46.902: W/System.err(6441): at dmri.nopo.DBAdapter.open(DBAdapter.java:86)
04-27 10:45:46.912: W/System.err(6441): at dmri.nopo.LogManager.readLogFile(LogManager.java:32)
04-27 10:45:46.912: W/System.err(6441): at dmri.nopo.LogActivity.onCreate(LogActivity.java:25)
04-27 10:45:46.921: W/System.err(6441): at android.app.Activity.performCreate(Activity.java:4465)
04-27 10:45:46.921: W/System.err(6441): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1049)
04-27 10:45:46.931: W/System.err(6441): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1920)
04-27 10:45:46.931: W/System.err(6441): at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1981)
04-27 10:45:46.941: W/System.err(6441): at android.app.ActivityThread.access0(ActivityThread.java:123)
04-27 10:45:46.941: W/System.err(6441): at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1147)
04-27 10:45:46.953: W/System.err(6441): at android.os.Handler.dispatchMessage(Handler.java:99)
04-27 10:45:46.953: W/System.err(6441): at android.os.Looper.loop(Looper.java:137)
04-27 10:45:46.972: W/System.err(6441): at android.app.ActivityThread.main(ActivityThread.java:4424)
04-27 10:45:46.972: W/System.err(6441): at java.lang.reflect.Method.invokeNative(Native Method)
04-27 10:45:46.972: W/System.err(6441): at java.lang.reflect.Method.invoke(Method.java:511)
04-27 10:45:46.982: W/System.err(6441): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:784)
04-27 10:45:46.982: W/System.err(6441): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551)
04-27 10:45:46.992: W/System.err(6441): at dalvik.system.NativeStart.main(Native Method)
04-27 10:45:47.002: I/SqliteDatabaseCpp(6441): sqlite returned: error code = 1, msg = no such table: log, db=/data/data/dmri.nopo/databases/NOPO
04-27 10:45:47.012: D/AndroidRuntime(6441): Shutting down VM
04-27 10:45:47.012: W/dalvikvm(6441): threadid=1: thread exiting with uncaught exception (group=0x409c01f8)
04-27 10:45:47.181: E/AndroidRuntime(6441): FATAL EXCEPTION: main
04-27 10:45:47.181: E/AndroidRuntime(6441): java.lang.RuntimeException: Unable to start activity ComponentInfo{dmri.nopo/dmri.nopo.LogActivity}: android.database.sqlite.SQLiteException: no such table: log: , while compiling: SELECT time, text FROM log
04-27 10:45:47.181: E/AndroidRuntime(6441): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1956)
04-27 10:45:47.181: E/AndroidRuntime(6441): at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1981)
04-27 10:45:47.181: E/AndroidRuntime(6441): at android.app.ActivityThread.access0(ActivityThread.java:123)
04-27 10:45:47.181: E/AndroidRuntime(6441): at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1147)
04-27 10:45:47.181: E/AndroidRuntime(6441): at android.os.Handler.dispatchMessage(Handler.java:99)
04-27 10:45:47.181: E/AndroidRuntime(6441): at android.os.Looper.loop(Looper.java:137)
04-27 10:45:47.181: E/AndroidRuntime(6441): at android.app.ActivityThread.main(ActivityThread.java:4424)
04-27 10:45:47.181: E/AndroidRuntime(6441): at java.lang.reflect.Method.invokeNative(Native Method)
04-27 10:45:47.181: E/AndroidRuntime(6441): at java.lang.reflect.Method.invoke(Method.java:511)
04-27 10:45:47.181: E/AndroidRuntime(6441): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:784)
04-27 10:45:47.181: E/AndroidRuntime(6441): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551)
04-27 10:45:47.181: E/AndroidRuntime(6441): at dalvik.system.NativeStart.main(Native Method)
04-27 10:45:47.181: E/AndroidRuntime(6441): Caused by: android.database.sqlite.SQLiteException: no such table: log: , while compiling: SELECT time, text FROM log
04-27 10:45:47.181: E/AndroidRuntime(6441): at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
04-27 10:45:47.181: E/AndroidRuntime(6441): at android.database.sqlite.SQLiteCompiledSql.(SQLiteCompiledSql.java:68)
04-27 10:45:47.181: E/AndroidRuntime(6441): at android.database.sqlite.SQLiteProgram.compileSql(SQLiteProgram.java:143)
04-27 10:45:47.181: E/AndroidRuntime(6441): at android.database.sqlite.SQLiteProgram.compileAndbindAllArgs(SQLiteProgram.java:361)
04-27 10:45:47.181: E/AndroidRuntime(6441): at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:127)
04-27 10:45:47.181: E/AndroidRuntime(6441): at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:94)
04-27 10:45:47.181: E/AndroidRuntime(6441): at android.database.sqlite.SQLiteQuery.(SQLiteQuery.java:53)
04-27 10:45:47.181: E/AndroidRuntime(6441): at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:47)
04-27 10:45:47.181: E/AndroidRuntime(6441): at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1564)
04-27 10:45:47.181: E/AndroidRuntime(6441): at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1449)
04-27 10:45:47.181: E/AndroidRuntime(6441): at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1405)
04-27 10:45:47.181: E/AndroidRuntime(6441): at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1485)
04-27 10:45:47.181: E/AndroidRuntime(6441): at dmri.nopo.DBAdapter.getAllSMS(DBAdapter.java:116)
04-27 10:45:47.181: E/AndroidRuntime(6441): at dmri.nopo.LogManager.readLogFile(LogManager.java:34)
04-27 10:45:47.181: E/AndroidRuntime(6441): at dmri.nopo.LogActivity.onCreate(LogActivity.java:25)
04-27 10:45:47.181: E/AndroidRuntime(6441): at android.app.Activity.performCreate(Activity.java:4465)
04-27 10:45:47.181: E/AndroidRuntime(6441): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1049)
04-27 10:45:47.181: E/AndroidRuntime(6441): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1920)
04-27 10:45:47.181: E/AndroidRuntime(6441): ... 11 more
采纳答案by EvilDuck
First of all I would recommend using android.util.Log for logging exceptions in Android.
首先，我建议使用 android.util.Log 来记录 Android 中的异常。
Second - I suspect you have tables with wrong names created. Your error says query can't find "log", but I see you make some concatenation in "CREATE" statement. That may be the reason.
第二 - 我怀疑您创建了名称错误的表。您的错误说查询找不到“日志”，但我看到您在“CREATE”语句中进行了一些连接。这可能就是原因。
You can check what is actually created for you. By viewing the sqlite base created.
您可以检查实际为您创建的内容。通过查看创建的 sqlite 库。
You can try:
你可以试试：
adb shellcd /data/data/<your.package.name>/databasessqlite3 <yourdbname>.tables
adb shellcd /data/data/<your.package.name>/databasessqlite3 <yourdbname>.tables
回答by Goofy
this will help you i have tried it and its also working
这将帮助你我已经尝试过它也可以工作
this is just an example
这只是一个例子
public class DatabaseMarks {
public static final String KEY_STUID = "stuid";
public static final String KEY_SUB1 = "subject_one";
public static final String KEY_SUB2 = "subject_two";
public static final String KEY_SUB3= "subject_three";
public static final String KEY_MARKS1= "marks_one";
public static final String KEY_MARKS2 = "marks_two";
public static final String KEY_MARKS3 = "marks_three";
private static final String DATABASE_NAME = "Student";
private static final String DATABASE_MARKSTABLE = "StudentMarks";
private static final int DATABASE_VERSION = 1;
private DbHelper ourHelper;
private final Context ourContext;
private SQLiteDatabase ourDatabase;
private static class DbHelper extends SQLiteOpenHelper{
public DbHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL(" CREATE TABLE " + DATABASE_MARKSTABLE + " (" +
KEY_STUID + " TEXT PRIMARY KEY, " +
KEY_SUB1 + " TEXT NOT NULL, " +
KEY_SUB2 + " TEXT NOT NULL, " +
KEY_SUB3 + " TEXT NOT NULL, " +
KEY_MARKS1 + " INTEGER NOT NULL, " +
KEY_MARKS2 + " INTEGER NOT NULL, " +
KEY_MARKS3 + " INTEGER NOT NULL);"
);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
db.execSQL("DROP TABLE IF EXISTS " + DATABASE_MARKSTABLE);
onCreate(db);
}
}
public DatabaseMarks(Context c){
ourContext = c;
}
public DatabaseMarks open()throws SQLException{
ourHelper = new DbHelper(ourContext);
ourDatabase = ourHelper.getWritableDatabase();
return this;
}
public void close(){
ourHelper.close();
}
public long createInsert(String stuid, String subject1, String subject2,
String subject3, String marks1, String marks2, String marks3) {
// TODO Auto-generated method stub
ContentValues cv = new ContentValues();
cv.put(KEY_STUID, stuid);
cv.put(KEY_SUB1, subject1);
cv.put(KEY_SUB2, subject2);
cv.put(KEY_SUB3, subject3);
cv.put(KEY_MARKS1, marks1);
cv.put(KEY_MARKS2, marks2);
cv.put(KEY_MARKS3, marks3);
return ourDatabase.insert(DATABASE_MARKSTABLE, null, cv);
}
回答by Dot Cink
Remove DATABASE_NAMEpart of your SQLs.
删除DATABASE_NAME部分 SQL。
If you want NOPOyour database name, and want to create a table log, then:
如果你想要NOPO你的数据库名称，并且想要创建一个 table log，那么：
private static class DatabaseHelper extends SQLiteOpenHelper {
DatabaseHelper(Context context) {
super(context, "NOPO", null, 2);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE " + "log" + " ("
+ KEY_TIME +" INTEGER primary key, "
+ KEY_TEXT +" TEXT not null);");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
+ newVersion + ", which will destroy all old data");
db.execSQL("DROP TABLE IF EXISTS log");
onCreate(db);
}
}
super(context, "NOPO", null, 2);makes this DatabaseHelper object just in the NOPOdatabase environment, so you need not to specify it in SQL.
super(context, "NOPO", null, 2);使这个 DatabaseHelper 对象只是在NOPO数据库环境中，所以你不需要在 SQL 中指定它。
ps. I hardcode something and you should AVOID it.
附：我硬编码了一些东西，你应该避免它。
回答by Dhruv Panchani
public class LoginDataBaseAdapter { static final String DATABASE_NAME = "login.db";
公共类 LoginDataBaseAdapter { static final String DATABASE_NAME = "login.db";
static final int DATABASE_VERSION = 1;
public static final int NAME_COLUMN = 1;
// TODO: Create public field for each column in your table.
// SQL Statement to create a new database.
static final String DATABASE_CREATE = "create table "+"LOGIN"+
"( " +"ID"+" integer primary key autoincrement,"+ "USERNAME text,PASSWORD text,UNAME text); ";
// Variable to hold the database instance
public SQLiteDatabase db;
// Context of the application using the database.
private final Context context;
// Database open/upgrade helper
private DataBaseHelper dbHelper;
public LoginDataBaseAdapter(Context _context)
{
context = _context;
dbHelper = new DataBaseHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
}
public LoginDataBaseAdapter open() throws SQLException
{
db = dbHelper.getWritableDatabase();
return this;
}
public void close()
{
db.close();
}
public SQLiteDatabase getDatabaseInstance()
{
return db;
}
回答by Nadeem Abbas
public class DBHandler extends SQLiteOpenHelper {
private static String DATABASE_NAME = "mydb";
private static int DATABASE_VERSION = 1;
String TABLE_NAME = "Student";
String KEY_ID = "id";
String KEY_STUDENT_NAME = "name";
String KEY_STUDENT_MARKS = "marks";
public DBHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
// creating table
String query = "CREATE TABLE " + TABLE_NAME +
" ( " +
KEY_ID + " INTEGER PRIMARY KEY," +
KEY_STUDENT_NAME + " TEXT," +
KEY_STUDENT_MARKS + " INTEGER"
" ) ";
db.execSQL(query);
// insertion in the table
ContentValues values = new ContentValues();
values.put(KEY_QUESTION_ID, "1");
values.put(KEY_STUDENT_NAME , "abc");
values.put(KEY_STUDENT_MARKS , "95");
db.insert(TABLE_NAME, null, values);
values.put(KEY_QUESTION_ID, "2");
values.put(KEY_STUDENT_NAME , "def");
values.put(KEY_STUDENT_MARKS , "93");
db.insert(TABLE_NAME, null, values);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
// Create tables again
onCreate(db);
}
// retrieve date
List<Student> getAllData(int Id) {
List<Student> student = new ArrayList<Student>();
// Select All Query
String selectQuery = "SELECT * FROM " + TABLE_NAME + " WHERE KEY_ID =" + "'" + Id + "'";
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
Student std = new Student();
u.setId(cursor.getInt(0));
u.setStudentName(cursor.getString(1));
u.setMarks(cursor.getString(2));
student.add(std);
} while (cursor.moveToNext());
}
// return student list
return student;
}
}
public class Student {
public static int Id;
public static String studentName;
public static int marks;
public int getId() {
return Id;
}
public static void setId(int id) {
Id = id;
}
public String getStudentName() {
return studentName;
}
public static void setStudentName(String studentName) {
Student.studentName = studentName;
}
public int getMarks() {
return marks;
}
public static void setMarks(int marks) {
Student.marks = marks;
}
}
public class MainActivity extends AppCompatActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
DBHandler db = new DBHandler(getApplicationContext());
List<Student> std = new ArrayList<Student>();
rl = db.getAllData(1);
for (Student s : std) {
Log.v("Id",s.getId());
Log.v("Name",s.getStudentName());
Log.v("Marks",s.getMarks());
}
}
}
