how to do delete row from grid on bean shell ?
Confluence User - 11 Jan, 2024
i already follow this Load & Store Form Grid Data Using Bean Shell Form Binder
but i still don;t find how to do delete in bean shell . for update and insert i already can.
but i don;t know how to get the id if i do delete on grid .
this is my example code
import org.joget.apps.form.model.*;
import org.joget.apps.form.lib.*;
import org.joget.apps.form.service.*;
import java.sql.*;
import org.apache.commons.collections.SequencedHashMap;
import java.util.*;
public FormRowSet getGridRows() {
return rows ; // this will return the grid rows
}
public saveGridRows(FormRowSet rows) {
Class.forName("com.mysql.jdbc.Driver").newInstance();
con = DriverManager.getConnection("link database", "username", "password");
if(!con.isClosed()){
String recordId = "#requestParam.id#";
// If you need to query data from different table(s) and process it before saving it to database,
// you can do it here
Iterator i= rows.iterator(); // Iterating grid rows
while (i.hasNext()) {
FormRow row = (FormRow) i.next();
String id = row.get("id");
String jenis_fasilitas = row.get("1"); // reading grid column value
String kategori_fasilitas = row.get("2");
String currency = row.get("3");
String outstanding = row.get("4");
String lama = row.get("5");
String perubahan = row.get("6");
String baru = row.get("7");
String jangka_waktu = row.get("8");
String pricing = row.get("9");
String kategori_agunan = row.get("10");
String jenis_pengajuan = row.get("11");
String sql = "select * from table WHERE id=?";// for checking insert or update
PreparedStatement stmtSelect = con.prepareStatement(sql);
stmtSelect.setString(1,id);
ResultSet rs = stmtSelect.executeQuery();
if(!rs.next()){
String InsertSql = "INSERT INTO table(1,2,3,4,5,6,7,8,9,10,11,12)VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement stmtInsert = con.prepareStatement(InsertSql);
stmtInsert.setString(1, "default");
stmtInsert.setString(2, 1);
stmtInsert.setString(3, 2);
stmtInsert.setString(4, 3);
stmtInsert.setString(5, 4);
stmtInsert.setString(6, 5);
stmtInsert.setString(7, 6);
stmtInsert.setString(8, 7);
stmtInsert.setString(9, 8);
stmtInsert.setString(10, 9);
stmtInsert.setString(11, 10);
stmtInsert.setString(12, 11);
stmtInsert.executeUpdate();
}else{
String UpdateSql = "UPDATE table set 1= ? ,2= ?,3= ?, 4= ?, 5= ?, 6= ?,7= ?, 8= ?, 9= ?, 10= ?, 11= ? where id = ?";
PreparedStatement stmtUpdate = con.prepareStatement(UpdateSql);
stmtUpdate.setString(1, 1);
stmtUpdate.setString(2, 2);
stmtUpdate.setString(3, 3);
stmtUpdate.setString(4, 4);
stmtUpdate.setString(5, 5);
stmtUpdate.setString(6, 6);
stmtUpdate.setString(7, 7);
stmtUpdate.setString(8, 8);
stmtUpdate.setString(9, 9);
stmtUpdate.setString(10, 10);
stmtUpdate.setString(11, 11);
stmtUpdate.setString(12, 12);
stmtUpdate.executeUpdate();
}
}
} else {
System.out.println("Connection Problem");
}
con.close();
}
FormRowSet rows = getGridRows(); // getting the grid rows
saveGridRows(rows); // processing & storing the grid rows
grid;formgrid;beanshell