mardi 11 août 2015

Table Is Not Deleted

i am trying to delete a table, and i followed this link http://ift.tt/1EkYScj as a tutorial. but when i execute the below code, for the first run, i expected the sqliteFactory.getRowCount() method will return 0 rows as the method sqliteFactory.deleteTable(SysConsts.SQLITE_DATABASE_TABLE_NAME); was just called before it, but what i received is a rowCount which is not zero.

in the second run of the same code, i expected the sqliteFactory.CreateTable(SysConsts.SQLITE_DATABASE_TABLE_NAME); to display Log.i(TAG, "CreateTable", "table: ["+tableName+"] does not exist, will be created"); as the table should have been deleted, but i received Log.i(TAG, "CreateTable", "table: ["+tableName+"] already exists.");

kindly please let me know how why the table is not deleted? and should i do commit after deletion?

main code:

public static void main(String[] args) throws SQLException, ClassNotFoundException {
    SQLiteFactory sqliteFactory = new SQLiteFactory();
    sqliteFactory.newSQLiteConn(SysConsts.SQLITE_DATABASE_NAME);
    sqliteFactory.CreateTable(SysConsts.SQLITE_DATABASE_TABLE_NAME);

    sqliteFactory.insertRecord(new Record("001", "55.07435", "8.79047", "c:\\bremen_0.xml"));
    Log.d(TAG, "main", "rowCount: "+sqliteFactory.getRowCount());

    //sqliteFactory.selectAll();
    //sqliteFactory.getNodeID("53.074415", "8.788047");
    sqliteFactory.selectXMLPathFor("53.074415", "8.788047");
    Log.d(TAG, "", ""+sqliteFactory.getRowCountLatLngFor("53.074415", "8.788047"));
    Log.d(TAG, "", ""+sqliteFactory.getRowCountNodeIDFor("001"));
    Log.d(TAG, "", ""+sqliteFactory.getRowCountXMLPathFor("c:\\brem_0.xml"));

    sqliteFactory.deleteTable(SysConsts.SQLITE_DATABASE_TABLE_NAME);
    Log.d(TAG, "main", "rowCount: "+sqliteFactory.getRowCount());
}

CreateTable:

public void CreateTable(String tableName) throws SQLException, ClassNotFoundException {
    if (!this.isTableExists(tableName)) {
        Log.i(TAG, "CreateTable", "table: ["+tableName+"] does not exist, will be created");

        Connection conn = this.getConnection();
        Statement stmt = conn.createStatement();
        stmt.executeUpdate(this.sqlTable);

        stmt.close();
        conn.close();

    } else {
        Log.i(TAG, "CreateTable", "table: ["+tableName+"] already exists.");
        return;
    }
}

isTableExists:

private boolean isTableExists(String tableName) throws ClassNotFoundException, SQLException {
    Connection conn = this.getConnection();
    DatabaseMetaData dbMeta = conn.getMetaData();
    ResultSet resSet = dbMeta.getTables(null, null, tableName, null);
    boolean exists = false;

    if (resSet.next()) {
        exists = true;
    } else {
        exists = false;
    }

    resSet.close();
    conn.close();

    return exists;
}

deleteTable:

public void deleteTable(String tableName) throws ClassNotFoundException, SQLException {
    if (this.isTableExists(tableName)) {
        Log.i(TAG, "deleteTable", "table: ["+tableName+"] already exists, and will be deleted.");

        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("delete from "+this.TABLE_NAME);

        ps.close();
        conn.close();

    } else {
        Log.i(TAG, "deleteTable", "table: ["+tableName+"] does not exist, can't be deleted.");
        return;
    }
}

getRowCount:

public int getRowCount() throws SQLException, ClassNotFoundException {  
    Connection conn = this.getConnection();
    Statement stmt= conn.createStatement();
    ResultSet resSet = stmt.executeQuery("SELECT COUNT(*) AS rowCount FROM "+TABLE_NAME+";");

    int cnt = resSet.getInt("rowCount");

    resSet.close();
    stmt.close();
    conn.close();

    return cnt;
}

Aucun commentaire:

Enregistrer un commentaire