Android studio connectivity with postgresql

Question

I'm new to android and not much aware about it. I though have been through tutorial but still didn't get any solution. How to connect Android Studio with postgressql? Step by step! I wrote this code in my MainActitvity.java. Is this correct? Or should I write it else where?

static final String JDBC_DRIVER = "org.postgresql.Driver";
    static final String DB_URL = "jdbc:postgresql://localhost:5432/user1";

//  Database credentials
static final String USER = "root";
static final String PASS = "root";

public static void main(String[] args)
{
    Connection conn = null;
    Statement st = null;
    try{
        //STEP 2: Register JDBC driver
        Class.forName("org.postgresql.Driver");

        //STEP 3: Open a connection
        System.out.println("Connecting to database...");
        conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/","root","root");

        //STEP 4: Execute a query
        System.out.println("Creating statement...");
        st = conn.createStatement();
        String sql;
        sql = "SELECT  first, last FROM Employees";
        ResultSet rs = st.executeQuery(sql);

        //STEP 5: Extract data from result set
        while(rs.next()){
            //Retrieve by column name
            String first = rs.getString("first");
            String last = rs.getString("last");

            //Display values
            System.out.print(", First: " + first);
            System.out.println(", Last: " + last);
        }
        //STEP 6: Clean-up environment
        rs.close();
        st.close();
        conn.close();
    }catch(SQLException se){
        //Handle errors for JDBC
        se.printStackTrace();
    }catch(Exception e){
        //Handle errors for Class.forName
        e.printStackTrace();
    }
    finally
    {
        //finally block used to close resources
        try{
            if(st!=null)
                st.close();
        }catch(SQLException se2){
        }// nothing we can do
        try{
            if(conn!=null)
                conn.close();
        }
        catch(SQLException se){
            se.printStackTrace();
        }//end finally try
    }
}

Answer 1

You cannot directly use java.sql.DriverManger, Connection, etc in Android. Android support SQLite DB, if you want to use DB in android you have to go with SQLite database. For Postgres you have to develop server side application and api services which you can then call from Android.

Answer 2

You can not connect the database with android studio directly, you have to make connection with your application and database through api , and you can write your api in java, php etc.

?php
  $db_connection = pg_connect("host=localhost dbname=record user=postgres password= ''");

  //pg query
?>

This is your connect query api.

Answer 3

Okay, this may be obsolete but still helpful for users (it was helpful for me) I copied your example and worked with it because I also need to get postgres running on android. And it works!

1. conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/","root","root");

This will result in an error because you need to enter the database name without a slash at the and, like:

conn = DriverManager.getConnection("jdbc:postgresql://domain.com:5432/databaseName", "username", "password");

2. Network connections (like connection to database) must be done in an AsyncTask using doInBackground(). I did it inside an activity

   public class dbactivity extends AppCompatActivity { //sry code formatting just broke
   
   String message = null;
   String conmsg  = null;
   
   private class pgsqlcon extends AsyncTask<Void, Void, Void>
   {
   
       public pgsqlcon()
       {
           super();
       }
   
       @Override
       protected Void doInBackground(Void... voids) {
           Connection conn = null;
           Statement st = null;
   
           try
           {
               //STEP 2: Register JDBC driver
               Class.forName("org.postgresql.Driver");
   
               //STEP 3: Open a connection
               System.out.println("Connecting to database...");
               message = "Connecting to database...";
               conn = DriverManager.getConnection("jdbc:postgresql://serverdomain.com:5432/databasename",
   

   "dbusername", "password"); //and so on

3. If you need to make UI changes like setText, you must use runOnUiThread like so ():

//using quote because code formatting doesn't work anymore for me xD
    private void setAsyncText(final TextView text,final String value){
    runOnUiThread(new Runnable() {
        @Override
        public void run() {
            if (value == null)
                text.setText("null");
            else
                text.setText(value);
        }
    });
    }

4. Oh yeah and last but not least, since I wrote this inside an Activiy, I have to trigger the trouble by calling my asynctask in OnCreate() of my Activity.

   @Override
   protected void onCreate(Bundle savedInstanceState)
   {
       super.onCreate(savedInstanceState);
       setContentView(R.layout.activity_dbactivity);
   
       pgsqlcon pgcon = new pgsqlcon();
       pgcon.execute();
   }
   

   }

I am not that experienced by myself so you can use this only for getting a connection at all to your postgresdb using JDBC only. Although I managed to get successful query results that way. And again, sorry for the wrong code formatting. I did what they wanted (4 space rule etc.) and it didn't work. I hope you can read it anyway, good luck.

And if nothing of this does work, maybeeee you want to take a look at these little hints: https://jdbc.postgresql.org/documentation/head/prepare.html (I assume you did that anyway since you have done a lot of almost correct code)

Answer 4

use 10.0.2.2 instead of localhost, it works for me.

Answer 5

My app uses PostgreSQL as backend. Use the retrofit library for connecting to the backend. In my app backend is written in python which will make queries in the database. This will make the front-end codes more smooth and secure. And the more controls can be shifted to the back-end.