Using Postgres on Android with Asynctask

Question

I want to connect my android app direct with the bank that is on a server. I made the connection in an Asynctask, follows the code.

public class ConnectionTask extends AsyncTask<Object, Object, Connection> {
    private final Context context;
    private final String endereco = "jdbc:postgres://192.168.1.36/sics";
    private final String usuario = "postgres";
    private final String senha = "postdba";

    private ProgressDialog progress;

    public ConnectionTask(Context context) {
        this.context = context;
    }

    @Override
    protected void onPreExecute() {
        progress = ProgressDialog.show(context, "Aguarde...",
                "Tentando realizar conexão com o banco de dados!", true, true);
    }

    @Override
    protected Connection doInBackground(Object... params) {
        Connection conn = null;
        try {
            Class.forName("org.postgresql.Driver").newInstance();
            conn = DriverManager.getConnection(endereco, usuario, senha);
        } catch (Exception e) {
            Log.i("CONEXAO", "NAO CONECTADO " + e.getMessage());
        }
        return conn;
    }

    @Override
    protected void onPostExecute(Connection result) {
        progress.dismiss();
    }
}

So far everything is without error, but error in my DAO. Follow my method list below.

public List<Categoria> listarCategoria(Context context) {
    List<Categoria> lista = new ArrayList<Categoria>();
    /*Cursor c = helper.getReadableDatabase().rawQuery(
            "select * from categorias", null);
    while (c.moveToNext()) {
        Categoria categoria = fill(c);
        lista.add(categoria);
    }
    return lista;*/
    Connection conn = new ConnectionTask(context).execute();
    PreparedStatement stmt = conn.prepareStatement("select * form categoria");

    return lista;

}

You’re making a mistake on the line

    Connection conn = new ConnectionTask(context).execute();

Follow a print with the error

Thank you very much in advance! :)

Answer 1

Here is a functional example to connect to the server with Postgresql database using the Wifi network. Note only works on Android above 3.0;

main.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent" >

<Button
    android:id="@+id/button1"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_alignParentTop="true"
    android:layout_centerHorizontal="true"
    android:layout_marginTop="30dp"
    android:text="Teste de Conexão" />

<TextView
    android:id="@+id/textView1"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_below="@+id/button1"
    android:layout_centerHorizontal="true"
    android:layout_marginTop="48dp"
    android:text="Large Text"
    android:textAppearance="?android:attr/textAppearanceLarge" /> 
</RelativeLayout>

Java bank.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import android.util.Log;

public class Banco{
private Connection conn = null;
private Statement st;
private ResultSet rs;
String _senha, _usuario;
List<String> lista = new ArrayList<String>();

public void conectarPostgres(){
    try {
        Class.forName("org.postgresql.Driver");
    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    try {
        DriverManager.setLoginTimeout(5);
        conn = DriverManager.getConnection("jdbc:postgresql://192.168.1.34:5432/sae0114user=postgres&password=teste");
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
} 

public void desconectarPostgres() {
    try { 
        conn.close(); 
    } catch (SQLException e) {
        e.printStackTrace(); 
    } 
}

public void sqlLogin(String usuario){ 
    try{
        String sql="SELECT * FROM saeusr where usuario='"+usuario+"'";
        st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        rs=st.executeQuery(sql);
        while (rs.next()){
            _senha = rs.getString("senha");
            _usuario = rs.getString("usuario");
        }
    } catch (Exception erro){
        Log.e("Postgres","Erro pesquisa: "+erro);
    }
}

public void sqlLista(){ 
    try{
        String sql="SELECT * FROM sae001 order by nom";
        st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        rs=st.executeQuery(sql);
        while (rs.next()){
            lista.add(rs.getString("nom"));
        }
    } catch (Exception erro){
        Log.e("Postgres","Erro pesquisa: "+erro);
    }
}

}

Main java.

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import android.app.Activity;
import android.app.ProgressDialog;
import android.os.AsyncTask;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;

public class Main extends Activity {
Statement st;
ResultSet rs;
Connection conn = null;
Button bt;
ProgressDialog dialog; 
Banco db = new Banco();

@Override
protected void onCreate(Bundle savedInstanceState) {
    // TODO Auto-generated method stub
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    bt = (Button) findViewById(R.id.button1);
    bt.setOnClickListener(new View.OnClickListener() {          
        @Override
        public void onClick(View v) {
            bt.setEnabled(false);

            TextView textoView = (TextView) findViewById(R.id.textView1);
            textoView.setText("");

            new EfetuandoTeste().execute("SUPERVISOR");             
        }
    });
}

class EfetuandoTeste extends AsyncTask<String, String, String>{
    String texto;

    protected String doInBackground(String... params) {

        db.conectarPostgres();
        db.sqlLogin(params[0]);
        db.desconectarPostgres();
        if (db._senha!=null){
            texto = db._senha;
        }
        return null;
    }

    @Override
    protected void onPreExecute() {
        // Cria a caixa de dialogo em quanto faz a conexão          
        dialog= new ProgressDialog(Main.this);
        dialog.setIndeterminate(true);
        dialog.setCancelable(false);
        dialog.setMessage("Atualizando...");
        dialog.show();

        super.onPreExecute();
    }

    @Override
    protected void onPostExecute(String result) {
        // TODO Auto-generated method stub          

        dialog.dismiss();
        bt.setEnabled(true);
        TextView textoView = (TextView) findViewById(R.id.textView1);
        textoView.setText(" - " + texto + " - ");
        super.onPostExecute(result);
    }
}
}