venerdì 15 febbraio 2013

Caricamento dati da db in una ListView in Android

Popolare una ListView con dei dati presenti nel db MySql di android? Semplice, basta portare i dati in una lista tipizzata e agganciare un SimpleAdapter alla ListView in questo modo.
Il progetto consiste in due classi e due layout (uno per la main e ListView, uno per la riga di testo).

Classe MainActivity:
public class MainActivity extends Activity {
 private final static String NAME = "name";
  
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        loadList();
    }

    private void loadList(){
     List<? extends Map<String,?>> listData = createItems();
     
     String[] keys = new String[]{NAME};
     int[] views = new int[]{R.id.sol_question};
     
     final SimpleAdapter simpleAdapter = new SimpleAdapter(this,listData,R.layout.custom_row,keys,views);
     
     ListView listView = (ListView) findViewById(R.id.ListView01);
  listView.setAdapter(simpleAdapter);
 }
    
    private List<? extends Map<String,?>> createItems() {
  ArrayList<Map<String,String>> lista = new ArrayList<Map<String,String>>();

  DataDbHelper dh = new DataDbHelper(MainActivity.this);
  
  lista = dh.selectAll();
  dh.closeDb();

  return lista;
 }   
    
    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.activity_main, menu);
        return true;
    } 
}
Classe DataDbHelper:
public class DataDbHelper {
 private static final String DATABASE_NAME = "example.db";  
 private static final int DATABASE_VERSION = 1; 
 private static final String TABLE_EXAMPLE = "tbl_example";
 private static final String FIELD_EXAMPLE_ID = "id";
 private static final String FIELD_EXAMPLE_NAME = "name";
 
 private Context context;  
 private SQLiteDatabase db;  
 private SQLiteStatement insertStmt; 
    
    public DataDbHelper(Context context) {  
       this.context = context;  
       OpenHelper openHelper = new OpenHelper(this.context); 
       this.db = openHelper.getWritableDatabase(); 
       openHelper.onUpgrade(this.db, 1, DATABASE_VERSION);
    }  

    public void closeDb(){
     if(this.db.isOpen())
      this.db.close();
    }
    
    public  ArrayList<Map<String,String>>  selectAll() {  
     ArrayList<Map<String,String>> listaDb = new ArrayList<Map<String,String>>();
     String NAME="name";
     Cursor cursor = this.db.query(TABLE_EXAMPLE, new String[] { FIELD_EXAMPLE_NAME },   
      null, null, null, null, FIELD_EXAMPLE_NAME+" asc");  
     if (cursor.moveToFirst()) {  
      do {  
       Map<String,String> dataDb = new HashMap<String,String>();
       dataDb.put(NAME,  cursor.getString(0));
       listaDb.add(dataDb);
      } while (cursor.moveToNext());  
    }  
    if (cursor != null && !cursor.isClosed()) {  
     cursor.close();  
    }  
    return listaDb;  
  }  
    

  private static class OpenHelper extends SQLiteOpenHelper {  
   OpenHelper(Context context) {  
       super(context, DATABASE_NAME, null, DATABASE_VERSION);  
   }  
 
    public void onCreate(SQLiteDatabase db) {  
       db.execSQL("CREATE TABLE " + TABLE_EXAMPLE + 
        " (" + FIELD_EXAMPLE_ID + " INTEGER PRIMARY KEY, " + FIELD_EXAMPLE_NAME + " TEXT)"); 
     }

    @Override
  public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
  }  
  }  
}

activity_main.xml
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:background="@android:color/black"
    android:layout_height="match_parent" >
<ListView
    android:id="@+id/ListView01"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:layout_alignParentTop="true" 
    android:choiceMode="none"
    android:paddingTop="1dp"
    android:textFilterEnabled="true" >
</ListView>
</RelativeLayout>
custom_row.xml
<?xml version="1.0" encoding="utf-8"?>
<TableLayout android:layout_width="fill_parent"
 xmlns:android="http://schemas.android.com/apk/res/android" 
 android:id="@+id/row_container"
 android:padding="4dp"
 android:layout_height="wrap_content">
 <TableRow android:id="@+id/widget10"
  android:layout_width="wrap_content"
  android:orientation="horizontal"
  android:layout_height="wrap_content">
  <TextView android:layout_height="wrap_content"
   android:layout_width="wrap_content" 
   android:textColor="@android:color/white"
   android:textSize="12sp"
   android:id="@+id/sol_question"></TextView>
 </TableRow>
</TableLayout>



venerdì 8 febbraio 2013

Query su db in cloud con mysql-connector in Android

E' possibile utilizzare il pacchetto mysql-connector-java-5.1.13-bin.jar per eseguire query su un db in internet.
Basta integrare il jar nel progetto Android e scrivere alcune righe di codice come l'esempio qui sotto.

public ArrayList<Map<String,String>> queryExample(){
Connection con=null;   
ArrayList<Map<String,String>> listDb = new ArrayList<Map<String,String>>();
String name="";
String score="";
  
try{   
 Class.forName("com.mysql.jdbc.Driver").newInstance();   
                
}catch(Exception e){       
}  
  
try{   
            con=DriverManager.getConnection(DB_URL,DB_ID,DB_PSW);  
            Statement st=con.createStatement();   
            String sql ="SELECT name_p, score_p FROM schema.table";
            st.executeQuery(sql);  
            ResultSet rs=st.getResultSet();   
            int i=0;
            while (rs.next ()){  
             Map<String,String> dataDb = new HashMap<String,String>();
             ++i;
             name = rs.getString("name_p");
             score = String.valueOf((int)rs.getInt("score_p"));
             if(name==null) name="";
             if(score==null) score="";
             
             dataDb.put(POSITION, ""+i);
             dataDb.put(ONLINE_NAME, name);
             dataDb.put(ONLINE_SCORE, score);
       listDb.add(dataDb);
            }       
            
            rs=null;
            
        }catch(Exception e){       
        }finally{
         try {
          if(con!=null)
           con.close();
   } catch (SQLException e) {

   }
}
        
return listDb;
}