CRUD MS SQL SERVER ANDROID

Pada postingan kali ini, saya akan membahas membuat Insert, Update, Delete dengan android Studio dan Microsoft SQL Server. Biasanya programming dengan android menggunakan middleware untuk komunikasi antara aplikasi dengan database yaitu web service, tapi pada postingan kali ini saya akan memberikan contoh dimana aplikasi android akan langsung konek ke database tanpa perantara middleware (Webservice).

Ok Pertama Buat database di SQL Server dengan nama DBProducttbl, Kemudian buat table dengan nama Producttbl

Atau bisa copy script berikut untuk membuat table di Sql Server :

CREATE TABLE [dbo].[Producttbl](

[Id] [int] IDENTITY(1,1) NOT NULL,

[ProName] [varchar](50) NULL,

[ProDesc] [varchar](50) NULL,

[OnDate] [datetime] NULL

)

Ok Selanjutnya Create New Project di Android Studio :

Pertama buat layout seperti gambar berikut :

Atau bisa copy xml file berikut :

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:background="#282828"
    android:orientation="vertical"
    tools:context="app.app.com.droidsql2.MainActivity">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="vertical"
        android:layout_alignParentTop="true"
        android:padding="2dp">

        <TextView
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="ADD PRODUCTS"
            android:layout_marginTop="7dp"
            android:typeface="sans"
            android:textSize="35sp"
            android:textColor="#ffffff"
            android:gravity="center" />

        <EditText
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_marginTop="10dp"
            android:textColor="#ffffff"
            android:textColorHint="#ffffff"
            android:textStyle="bold"
            android:background="#5d5d5d"
            android:padding="10dp"
            android:hint="PRODUCT NAME"
            android:textSize="20sp"
            android:id="@+id/edtproname" />

        <EditText
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:textSize="16sp"
            android:hint="DESCRIPTION"
            android:textColor="#ffffff"
            android:textColorHint="#ffffff"
            android:textStyle="bold"
            android:background="#5d5d5d"
            android:padding="10dp"
            android:inputType="textMultiLine"
            android:maxLines="3"
            android:minLines="2"
            android:layout_gravity="top|left"
            android:layout_marginTop="5dp"
            android:id="@+id/edtprodesc" />

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:weightSum="3"
            android:layout_marginTop="5dp"
            android:orientation="horizontal">

            <Button
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:textColor="#ffffff"
                android:textSize="20sp"
                android:layout_margin="2dp"
                android:padding="7dp"
                android:layout_marginTop="10dp"
                android:id="@+id/btnadd"
                android:text="Add" />

            <Button
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:layout_margin="2dp"
                android:textColor="#ffffff"
                android:textSize="20sp"
                android:padding="7dp"
                android:layout_marginTop="10dp"
                android:id="@+id/btnupdate"
                android:text="Update" />

            <Button
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:textColor="#ffffff"
                android:textSize="20sp"
                android:layout_margin="2dp"
                android:padding="7dp"
                android:layout_marginTop="10dp"
                android:id="@+id/btndelete"
                android:text="Delete" />
        </LinearLayout>

        <ProgressBar
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_gravity="center"
            android:id="@+id/pbbar" />

        <ListView
            android:id="@+id/lstproducts"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:divider="#808080"
            android:layout_marginTop="5dp"
            android:dividerHeight="1dp"
            android:padding="5dp">

        </ListView>
    </LinearLayout>
</RelativeLayout>

Tambahkan satu layout baru dengan nama lsttemplate.xml

kemudian masukkan code xml berikut :

<?xml version="1.0" encoding="utf-8"?>
  <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="horizontal"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:weightSum="5"
    android:padding="5dp"
    android:layout_marginTop="2dp">
  

    <TextView
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:textColor="#ffffff"
        android:layout_weight="1"
        android:textSize="15sp"
        android:text="1"
        android:id="@+id/lblproid"/>
  

    <TextView
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:layout_weight="4"
        android:textColor="#ffffff"
        android:textSize="15sp"
        android:text="MOTOROLA"
        android:id="@+id/lblproname"/>
  

    <TextView
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:layout_weight="0"
        android:textColor="#ffffff"
        android:textSize="0sp"
        android:text="MOTOROLA"
        android:id="@+id/lblprodesc"/>


</LinearLayout>

Selanjutnya tambahkan class baru dengan nama : ConnectionClass.java

Berikut detail source code dari ConnectionClass.java

package app.app.com.droidsql2;

  

  import android.annotation.SuppressLint;

  import android.os.StrictMode;

  import android.util.Log;

  

  import java.sql.Connection;

  import java.sql.DriverManager;

  import java.sql.SQLException;

  

  /**

 * Created by IT on 2/17/2017.

 */

  public class ConnectionClass {

    String ip = "192.168.88.197";

    String classs = "net.sourceforge.jtds.jdbc.Driver";

    String db = "ERP120";

    String un = "sa";

    String password = "Admin123456kr";

  

    @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;

    }

}

Kemudian di MainActivity.java tuliskan source code seperti berikut :

package app.app.com.droidsql2;

import android.os.AsyncTask;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.AdapterView;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.ProgressBar;
import android.widget.SimpleAdapter;
import android.widget.Toast;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;

public class MainActivity extends AppCompatActivity {

    ConnectionClass connectionClass;
    EditText edtproname, edtprodesc;
    Button btnadd,btnupdate,btndelete;
    ProgressBar pbbar;
    ListView lstpro;
    String proid;

    @Override    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        connectionClass = new ConnectionClass();
        edtproname = (EditText) findViewById(R.id.edtproname);
        edtprodesc = (EditText) findViewById(R.id.edtprodesc);
        btnadd = (Button) findViewById(R.id.btnadd);
        btnupdate = (Button) findViewById(R.id.btnupdate);
        btndelete = (Button) findViewById(R.id.btndelete);
        pbbar = (ProgressBar) findViewById(R.id.pbbar);
        pbbar.setVisibility(View.GONE);
        lstpro = (ListView) findViewById(R.id.lstproducts);
        proid = "";

        FillList fillList = new FillList();
        fillList.execute("");


        btnadd.setOnClickListener(new View.OnClickListener() {
            @Override            public void onClick(View v) {
                AddPro addPro = new AddPro();
                addPro.execute("");

                edtproname.setText("");
                edtprodesc.setText("");
            }
        });

        btnupdate.setOnClickListener(new View.OnClickListener(){

            @Override            public void onClick(View v) {
                UpdatePro updatePro = new UpdatePro();
                updatePro.execute("");

                edtproname.setText("");
                edtprodesc.setText("");
            }
        });

        btndelete.setOnClickListener(new View.OnClickListener(){

            @Override            public void onClick(View v) {
                DeletePro deletePro = new DeletePro();
                deletePro.execute("");

                edtproname.setText("");
                edtprodesc.setText("");
            }
        });
    }

    public class FillList extends AsyncTask<String, String, String> {

        String z = "";

        List<Map<String, String>> prolist  = new ArrayList<Map<String, String>>();

        @Override        protected void onPreExecute() {

            pbbar.setVisibility(View.VISIBLE);
        }

        @Override        protected void onPostExecute(String r) {

            pbbar.setVisibility(View.GONE);
            Toast.makeText(MainActivity.this, r, Toast.LENGTH_SHORT).show();

            String[] from = { "A", "B", "C", "D" };
            int[] views = { R.id.lblproid, R.id.lblproname, R.id.lblprodesc, R.id.lbldesc };
            final SimpleAdapter ADA = new SimpleAdapter(MainActivity.this,

            prolist, R.layout.lsttemplate, from, views);
            lstpro.setAdapter(ADA);


            lstpro.setOnItemClickListener(new AdapterView.OnItemClickListener() {

                @Override

                    public void onItemClick(AdapterView<?> arg0, View arg1,
                                        int arg2, long arg3) {
                    HashMap<String, Object> obj = (HashMap<String, Object>)

                    ADA.getItem(arg2);
                    proid = (String) obj.get("A");
                    String proname = (String) obj.get("B");
                    String prodesc = (String) obj.get("C");
                    edtprodesc.setText(prodesc);
                    edtproname.setText(proname);
                    //     qty.setText(qtys);                }
            });



        }

        @Override

           protected String doInBackground(String... params) {
            try {
                Connection con = connectionClass.CONN();
                if (con == null) {
                    z = "Error in connection with SQL server";
                } else {
                    String query = "select Id,ProName,ProDesc from Producttbl";
                    PreparedStatement ps = con.prepareStatement(query);
                    ResultSet rs = ps.executeQuery();

                    //ArrayList data1 = new ArrayList();

                   while (rs.next()) {
                        Map<String, String> datanum = new HashMap<String, String>();
                        datanum.put("A", rs.getString("Id"));
                        datanum.put("B", rs.getString("ProName"));
                        datanum.put("C", rs.getString("ProDesc"));
                        prolist.add(datanum);
                    }


                    //z = "Success";                }
            } catch (Exception ex) {
                z = "Error retrieving data from table";

            }
            return z;
        }
    }

    public class AddPro extends AsyncTask<String, String, String> {
        String z = "";
        Boolean isSuccess = false;

        String proname = edtproname.getText().toString();
        String prodesc = edtprodesc.getText().toString();

        @Override

        protected void onPreExecute() {
            pbbar.setVisibility(View.VISIBLE);
        }

        @Override        protected void onPostExecute(String r) {
            pbbar.setVisibility(View.GONE);
            Toast.makeText(MainActivity.this, r, Toast.LENGTH_SHORT).show();
            if(isSuccess==true) {
                FillList fillList = new FillList();
                fillList.execute("");
            }
        }

        @Override

         protected String doInBackground(String... params) {
            if (proname.trim().equals("") || prodesc.trim().equals(""))
                z = "Please enter User Id and Password";
            else {
                try {
                    Connection con = connectionClass.CONN();
                    if (con == null) {
                        z = "Error in connection with SQL server";
                    } else {

                        String dates = new SimpleDateFormat("MM/dd/yyyy", Locale.ENGLISH)
                                .format(Calendar.getInstance().getTime());
                        String query = "insert into Producttbl (ProName,ProDesc,OnDate)

                        values ('" + proname + "','" + prodesc + "','" + dates + "')";
                        PreparedStatement preparedStatement = con.prepareStatement(query);
                        preparedStatement.executeUpdate();
                        z = "Added Successfully";
                        isSuccess = true;
                    }
                } catch (Exception ex) {
                    isSuccess = false;
                    z = "Exceptions";
                }
            }
            return z;
        }
    }

    public class UpdatePro extends AsyncTask<String, String, String> {

        String z = "";
        Boolean isSuccess = false;

        String proname = edtproname.getText().toString();
        String prodesc = edtprodesc.getText().toString();

        @Override

         protected void onPreExecute() {
            pbbar.setVisibility(View.VISIBLE);
        }

        @Override

         protected void onPostExecute(String r) {
            pbbar.setVisibility(View.GONE);
            Toast.makeText(MainActivity.this, r, Toast.LENGTH_SHORT).show();
            if(isSuccess==true) {
                FillList fillList = new FillList();
                fillList.execute("");
            }

        }

        @Override

         protected String doInBackground(String... params) {
            if (proname.trim().equals("") || prodesc.trim().equals(""))
                z = "Please enter User Id and Password";
            else {
                try {
                    Connection con = connectionClass.CONN();
                    if (con == null) {
                        z = "Error in connection with SQL server";
                    } else {

                        String dates = new SimpleDateFormat("MM/dd/yyyy", Locale.ENGLISH)
                                .format(Calendar.getInstance().getTime());

                        String query = "Update Producttbl set ProName='"+proname+"',

                        ProDesc='"+prodesc+"' , OnDate='"+dates+"' where Id="+proid;
                        PreparedStatement preparedStatement = con.prepareStatement(query);
                        preparedStatement.executeUpdate();
                        z = "Updated Successfully";

                        isSuccess = true;
                    }
                } catch (Exception ex) {
                    isSuccess = false;
                    z = "Exceptions";
                }
            }
            return z;
        }
    }

    public class DeletePro extends AsyncTask<String, String, String> {

        String z = "";
        Boolean isSuccess = false;

        String proname = edtproname.getText().toString();
        String prodesc = edtprodesc.getText().toString();

        @Override

        protected void onPreExecute() {
            pbbar.setVisibility(View.VISIBLE);
        }

        @Override

         protected void onPostExecute(String r) {
            pbbar.setVisibility(View.GONE);
            Toast.makeText(MainActivity.this, r, Toast.LENGTH_SHORT).show();
            if(isSuccess==true) {
                FillList fillList = new FillList();
                fillList.execute("");
            }

        }

        @Override

         protected String doInBackground(String... params) {
            if (proname.trim().equals("") || prodesc.trim().equals(""))
                z = "Please enter User Id and Password";
            else {
                try {
                    Connection con = connectionClass.CONN();
                    if (con == null) {
                        z = "Error in connection with SQL server";
                    } else {

                        String dates = new SimpleDateFormat("MM/dd/yyyy", Locale.ENGLISH)
                                .format(Calendar.getInstance().getTime());

                        String query = "delete from Producttbl where Id="+proid;
                        PreparedStatement preparedStatement = con.prepareStatement(query);
                        preparedStatement.executeUpdate();
                        z = "Deleted Successfully";
                        isSuccess = true;
                    }
                } catch (Exception ex) {
                    isSuccess = false;
                    z = "Exceptions";
                }
            }
            return z;
        }
    }
}

Terakhir rubah Manifest menjadi seperti berikut :

<?xml version="1.0" encoding="utf-8"?>

<manifest xmlns:android="http://schemas.android.com/apk/res/android"

package="app.app.com.droidsql2">

    <uses-permission android:name="android.permission.INTERNET" />
    <uses-permission android:name="android.permission.ACCESS_NETWORK_STATE" />
    <uses-permission android:name="android.permission.ACCESS_WIFI_STATE" />

    <application

       android:allowBackup="true"

       android:icon="@mipmap/ic_launcher"

       android:label="@string/app_name"

       android:supportsRtl="true"

       android:theme="@style/AppTheme">
        <activity android:name=".MainActivity">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>
</manifest>

Jika sudah jalankan aplikasinya :D

Untuk source code project lengkapnya bisa Di Download Disini

Terimakasih, Semoga Bermanfaat :D

Menu

CRUD MS SQL SERVER ANDROID

0 Comments

Tags

Menu Footer Widget

Contact form

Menu

CRUD MS SQL SERVER ANDROID

You may like these posts

0 Comments

Tags

Menu Footer Widget

Contact form