How to retrieve data from sql server db in android

Question

this is the first time I am developing an android application. I want to bind some data with grid view or list view based employee id provided on the text box. How can I do that. Please help me to find a proper solution. Thank you.

ConnectionClass.java:

 import android.annotation.SuppressLint;
 import android.os.StrictMode;
 import android.util.Log;
 import java.sql.SQLException;
 import java.sql.Connection;
 import java.sql.DriverManager;

/**
 * Created by H-PC on 16-Oct-15.
 */
public class ConnectionClass {

    String ip = "******";
    String classs = "net.sourceforge.jtds.jdbc.Driver";
    String db = "IDB";
    String un = "sa";
    String password = "admin123";

    @SuppressLint("NewApi")
    public Connection CONN() {
        StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder()
                .permitAll().build();
        StrictMode.setThreadPolicy(policy);
        Connection conn = null;
        String ConnURL = null;
        try {

            Class.forName(classs);
            ConnURL = "jdbc:jtds:sqlserver://" + ip + ";"
                    + "databaseName=" + db + ";user=" + un + ";password="
                    + password + ";";
            conn = DriverManager.getConnection(ConnURL);
        } catch (SQLException se) {
            Log.e("ERRO", se.getMessage());
        } catch (ClassNotFoundException e) {
            Log.e("ERRO", e.getMessage());
        } catch (Exception e) {
            Log.e("ERRO", e.getMessage());
        }
        return conn;
    }
}

-------------------------------------------------------------------------------

MainActivity.java:

import android.os.Bundle;
import android.support.design.widget.FloatingActionButton;
import android.support.design.widget.Snackbar;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.Toolbar;
import android.util.Log;
import android.view.View;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.view.View.OnClickListener;
import java.sql.Connection;

public class MainActivity extends AppCompatActivity {

    Button button1;
    TextView txtView1;
    EditText editText1;
    ConnectionClass connectionClass;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        connectionClass = new ConnectionClass();
        button1=(Button)findViewById(R.id.btnSearch);
        txtView1=(TextView)findViewById(R.id.searchLbl);
        editText1=(EditText)findViewById(R.id.txtSearch);

        button1.setOnClickListener(new View.OnClickListener(){

        @Override
        public void onClick(View v) {

                String newValue = editText1.getText().toString().trim();

                if (newValue.toString().trim().equals("")) {
                    txtView1.setText("Please Enter ID");
                }
                else
                {
                    Connection con = connectionClass.CONN();

                if(con==null)
                {
                    String msg="Error in SQL Connection";
                }
                else
                {
                    String query= "Select * From Employees Where EmpId='"+newValue+"'";
                }
                }
            }


        });
        }
    }

---------------------------------------------------------------------------

Text

<?xml version="1.0" encoding="utf-8"?>
<android.support.design.widget.CoordinatorLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:fitsSystemWindows="true"
    tools:context="com.example.laptop37.myapplication2.MainActivity">

    <RelativeLayout
        android:layout_width="match_parent"
        android:layout_height="match_parent">

        <RelativeLayout
            android:orientation="horizontal"
            android:layout_width="match_parent"
            android:layout_height="match_parent"
            android:layout_alignParentLeft="true"
            android:layout_alignParentStart="true">



            <EditText
                android:layout_width="285dp"
                android:layout_height="42dp"
                android:id="@+id/txtSearch"
                android:layout_weight="1"
                android:textColor="@color/material_deep_teal_500"
                android:hint="enter id"
                android:layout_gravity="center_vertical"
                android:layout_below="@+id/textHeading"
                android:layout_alignParentLeft="true"
                android:layout_alignParentStart="true"
                android:layout_marginTop="30px"
                android:background="#b1e1b1"
                android:layout_marginLeft="10px"
                />

            <TextView
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:textAppearance="?android:attr/textAppearanceLarge"
                android:text="Employee Data"
                android:id="@+id/textHeading"
                android:layout_weight="1"
                android:layout_alignParentTop="true"
                android:layout_alignParentLeft="true"
                android:layout_alignParentStart="true"
                android:layout_marginTop="30px"
                android:layout_alignParentRight="true"
                android:layout_alignParentEnd="true"
                android:textAlignment="center"
                android:textColor="#2eb114"
                android:textSize="70px"
                />

            <Button
                android:layout_width="85dp"
                android:layout_height="42dp"
                android:text="Search"
                android:id="@+id/btnSearch"
                android:layout_weight="0.43"
                android:background="@color/background_material_dark"
                android:textColor="#ffffff"
                android:layout_gravity="center_vertical"
                android:layout_alignBottom="@+id/txtSearch"
                android:layout_toRightOf="@+id/txtSearch"
                android:layout_toEndOf="@+id/txtSearch"
                android:layout_marginLeft="10px"
                android:layout_marginStart="10px"
                android:layout_marginRight="10px"
                />

            <TextView
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:id="@+id/searchLbl"
                android:layout_below="@+id/txtSearch"
                android:layout_alignLeft="@+id/txtSearch"
                android:layout_alignStart="@+id/txtSearch"
                android:layout_marginTop="64dp" />

            <GridView
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:id="@+id/gridViewEmp"
                android:layout_alignRight="@+id/txtSearch"
                android:layout_alignEnd="@+id/txtSearch"
                android:layout_below="@+id/txtSearch" />


        </RelativeLayout>
    </RelativeLayout>

</android.support.design.widget.CoordinatorLayout>
-------------------------------------------------------------------------------

Table(Employees) :

EmpId EmpName Designation Gender Age Mob Address
----- ------- ----------- ------ --- --- -------
1      asd    ttt           M     30     fghfhfh
2      dfg    yyy           F     26     fhgfhfhf
3      dfhh   ppp           M     47     fghhfghf

--------------------------------------------------------------------------------

Design:

are you trying to use sql server directly without any webservice? its impossible man — Tanim reja
– Tanim reja, Commented Dec 30, 2015 at 10:36

Tanim reja · Accepted Answer · 2015-12-30 10:50:19Z

0

You cant access sql server directly ...

First you need to make a web service because your database sits on your host not on your phone memory card , web service process a request from app and responds accordingly.That is, you pass some SQL query parameters from app to the web service and let your web service handle the request. This web service can be of any type JAVA, PHP, etc. You just need to use standard HTTP methods. click

answered Dec 30, 2015 at 10:50

Tanim reja

2,1981 gold badge17 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kristo · Accepted Answer · 2015-12-30 11:52:16Z

Here is an example of how you can display data gathered from a database in to a listview.

    final ListView listView = getListView();
    final String[][] _customerData = MySQL.Get("SELECT Id,Name,Surname FROM customers");
    final List<Integer> _currentCustomerIDs = new ArrayList<>();
    final List<String> _nameList = new ArrayList<>();

    for (String[] customer : _customerData)
    {
        _currentCustomerIDs.add(Integer.valueOf(customer[0]));
        _nameList.add(customer[2] + " " + customer[1]);
    }
    listView.invalidateViews();
    listView.setTextFilterEnabled(true);
    listView.setAdapter(getListAdapter());
    setListAdapter(new ArrayAdapter<>(this, R.layout.customer_list, _nameList.toArray()));

You need to make a select query, then declare a list of integers, iterate through that list, add it to your arraylist, and finally add it to your listview.

Above is an example adding integers as well as strings to your list with data gathered from a jdbc database.

Good luck, Hope it helps!

PaulCK · Accepted Answer · 2018-10-25 07:39:33Z

in that case you

button1.setOnClickListener(new View.OnClickListener(){

    @Override
    public void onClick(View v) {

            String newValue = editText1.getText().toString().trim();

            if (newValue.toString().trim().equals("")) {
                txtView1.setText("Please Enter ID");
            }
            else
            {
                Connection con = connectionClass.CONN();

                if(con==null)
                {
                    String msg="Error in SQL Connection";
                }
                else
                {

                    try {

                        st = con.createStatement();

                        // set the result in result set then fetch it
                        rs = st.executeQuery("Select * From Employees Where EmpId='"+newValue+"'");

                        if (rs.next()){
                            Toast.makeText(getApplicationContext()," EmpId :. " + rs.getString("EmpId")+" EmpName :. "+rs.getString("EmpName")+ " Designation :. " + rs.getString("Designation")+" Gender :. "+rs.getString("Gender")+" etc..",LENGTH_LONG).show();

                        }else{
                            // you have no record

                        }


                    }
                }
            }


    });
});

Collectives™ on Stack Overflow

How to retrieve data from sql server db in android

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related