'How to retrieve data from sql server db in android
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:
Solution 1:[1]
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
Solution 2:[2]
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!
Solution 3:[3]
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
}
}
}
}
});
});
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Tanim reja |
| Solution 2 | Kristo |
| Solution 3 | Paul Chu |