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
This comment has been removed by the author.
ReplyDeleteKeren.. tulisan yang sangat bermanfaat..
ReplyDeleteMy blog
:D
DeleteBisa gak klo mau buat app windows dan android dgn 1 database? Saya mau buat windows app untuk backend, android app utk usersnya.
ReplyDeleteSatu database untuk lebih dari satu aplikasi dengan platform yang berbeda sangat2 bisa, tergantung kita mau design sistemnya seperti apa
DeleteDidalam android studio harus melampirkan modul jtds gan?
ReplyDeleteya gan, download aja di filenya gan nanti bisa dilihat disana :D
Deletemakasih,sangat bermanfaat
ReplyDeletesaya dah ikutin, tapi z = "Error in connection with SQL server"; kira2 kenapa y
ReplyDeletelibrary untuk koneksi ke SQL server sudah di tambahkan gak gan?
Deleteada tutorialnya ga gan, mklum baru belajar, masih blm mudeng
Deletecara nambahin librarynya, kmren googling, tp msh blm konek
Deletedah bisa gan, terimakasih buanyak, d tunggu postingan selanjutnya,
Deletegan ini solved ny gmn ya ?
Deletesaya juga ngalamin hal yang sama,,
ini perbaikannya bagaimana ya ?
DeleteTerima kasih
Muncul error seperti dibawah, kira2 karena apa y, mohon petunjuknya
ReplyDeleteE/SpannableStringBuilder: SPAN_EXCLUSIVE_EXCLUSIVE spans cannot have a zero length
SPAN_EXCLUSIVE_EXCLUSIVE spans cannot have a zero length
E/SpannableStringBuilder: SPAN_EXCLUSIVE_EXCLUSIVE spans cannot have a zero length
SPAN_EXCLUSIVE_EXCLUSIVE spans cannot have a zero length
E/SpannableStringBuilder: SPAN_EXCLUSIVE_EXCLUSIVE spans cannot have a zero length
SPAN_EXCLUSIVE_EXCLUSIVE spans cannot have a zero length
kalau nampilin data ke edit text berdasarkan id gimana mas, saya coba 1 fild bisa, kalau lebih jadi error
ReplyDeleteBagi Kodingan nya gan. Ane gagal terus
DeleteThis comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteHalo kak, terimakasih artikelnya... ini sangat membantu saya dalam menyelesaikan tugas kuliah saya. oh ya kak, perkenalkan nama saya Nurhamimah dan jangan lupa kunjungi website kampus saya ISB Atma Luhur
ReplyDeletemuncul pesan error
ReplyDelete> Task :app:processDebugResources FAILED
AGPBI: {"kind":"error","text":"Android resource linking failed","sources":[{"file":"C:\\Users\\HP\\AndroidStudioProjects\\MyApplication\\app\\build\\intermediates\\packaged_manifests\\debug\\AndroidManifest.xml","position":{"startLine":14}}],"original":"C:\\Users\\HP\\AndroidStudioProjects\\MyApplication\\app\\build\\intermediates\\packaged_manifests\\debug\\AndroidManifest.xml:15: AAPT: error: resource style/AppTheme (aka com.example.myapplication:style/AppTheme) not found.\n ","tool":"AAPT"}
detil pesan errornya seperti ini
ReplyDeleteAAPT: error: resource style/AppTheme (aka com.example.myapplication:style/AppTheme) not found
Saya coba buat sama persis tpi muncul "Error in connection with SQL server". Pesan error muncul dari MainActivity.java dimulai dari ngebaca line code 144. Kemungkinan ada masalah koneksi ke SQL Server. Gimana cara mengatasi masalah ini ya kak ?
ReplyDeleteTerima kasih
Mungkin manifestnya ka blm di Add
Deletemanifestnya sudah sesuai dgn contoh yg diatas mas. Kira2 apanya ya ?
Delete