Tags
People
Badges
Help-
CORE FRAMEWORK
FEATURED COMPONENTS
-
RESOURCES
-
LEARN ZK
RESOURCES
Hi,
Could anyone give me simple example for inserting resultset data to grid ?
my current code :
<zk>
<button label = "Execute Query">
<attribute name="onClick">
try{
String myquery = "Select * from Employee"; // Assume there's 10 column
Connection mycon = up.getJdbcConnection();
Statement s = mycon.createStatement();
s.executeQuery (myquery);
ResultSet rs = s.getResultSet ();
//then ????
..........................
..........................
</attribute>
</button>
<grid id="grid" mold="paging" pageSize="5">
</zk>
i know another example using Listbox (using appendChild method, but on grid i dont know).
Assume, we dont need to specify column header (because we're using select * from employee ).
Could anyone give sample how to bind data automatically (with header and paging)?
Thanks before...
Hi, zuki
You can add your data in a ArrayList
and refer to here
Hi as1225,
i have copy your code, but no success and i'm confused with unsynchronized row and column....
because i'm new to java and zk, i really confused how to bind JDBC Resultset to Grid, look what i have tried
i need more simple code...
MyComposer.java
package ctrl;
import java.util.*;
import org.zkoss.zk.ui.Component;
import org.zkoss.zk.ui.Executions;
import org.zkoss.zk.ui.event.Event;
import org.zkoss.zk.ui.event.EventListener;
import org.zkoss.zk.ui.util.GenericForwardComposer;
import org.zkoss.zul.*;
import java.sql.*;
import java.io.*;
import com.mylib;
import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.query.Query;
public class MyComposer extends GenericForwardComposer {
private Button btn1, btn2, btn3;
private Grid grid;
private ListModelList lmList;
@Override
public void doAfterCompose(Component comp) throws Exception {
super.doAfterCompose(comp);
grid.setRowRenderer(new RowRenderer() {
public void render(Row row, Object data) throws Exception {
File f = new File("mydata.mdb");
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
database+= f.toString() + ";DriverID=22;READONLY=true}"; // add on to the end
Connection con = DriverManager.getConnection( database ,"","");
Database d = Database.open(f); // --> file yg sudah disimpan diobjek
List<Query> query = d.getQueries();
String querys = query.get(0).toSQLString();
String q2 = querys.replace( '"', '\'' );
Statement s = con.createStatement();
s.executeQuery(q2);
ResultSet rs = s.getResultSet();
int totalKolom = rs.getMetaData().getColumnCount();
ResultSetMetaData rm = rs.getMetaData();
//==
if (data == null) return;
Columns columns = row.getGrid().getColumns();
if (columns.getChildren() == null) return;
int colCount = columns.getChildren().size();
Map<String, Object> params = new HashMap<String, Object>();
params.put("items", createStringList(colCount, rs));
row.appendChild(Executions.createComponents("items.zul", row, params));
}
});
lmList = new ListModelList();
grid.setModel(lmList);
EventListener onClickEvt = new EventListener() {
public void onEvent(Event event) throws Exception {
File f = new File("mydata.mdb");
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
database+= f.toString() + ";DriverID=22;READONLY=true}"; // add on to the end
Connection con = DriverManager.getConnection( database ,"","");
Database d = Database.open(f); // --> file yg sudah disimpan diobjek
List<Query> query = d.getQueries();
String querys = query.get(0).toSQLString();
String q2 = querys.replace( '"', '\'' );
Statement s = con.createStatement();
s.executeQuery(q2);
ResultSet rs = s.getResultSet();
int totalKolom = rs.getMetaData().getColumnCount();
ResultSetMetaData rm = rs.getMetaData();
//==
Columns columns = grid.getColumns();
if (columns.getChildren() == null) return;
columns.detach();
Button btn = (Button) event.getTarget();
int num = Integer.parseInt(btn.getLabel());
Map<String, Object> params = new HashMap<String, Object>();
params.put("columns", createColumnList(totalKolom,rm));
grid.appendChild(Executions.createComponents("columns.zul", grid, params));
lmList.clear();
// boolean avr = rs.next(); or while (rs.next) ??? ///---> confused !!!!
// String test = rs.getString(1);
String test = "";
lmList.addAll(createContentList(num, rs, test));
}
};
btn1.addEventListener("onClick", onClickEvt);
btn2.addEventListener("onClick", onClickEvt);
btn3.addEventListener("onClick", onClickEvt);
}
private List<String> createColumnList(int num, ResultSetMetaData rm) throws SQLException
{
List<String> result = new ArrayList<String>();
for (int i = 1; i <= num; i++) {
result.add("" + rm.getColumnName(i));
}
return result;
}
private List<String> createContentList(int num, ResultSet rs, String test) throws SQLException{
List<String> result = new ArrayList<String>();
boolean avr = rs.next();
result.add("" + rs.getString(2));
return result;
}
private List<String> createStringList(int num, ResultSet rs) throws Exception {
List<String> result = new ArrayList<String>();
boolean dor = rs.next();
for (int i = 1; i < num; i++) {
result.add("" + rs.getString(i));
}
return result;
}
}
This is more simple code based on yours, let see ???? tag where i confused. i just want to connect simple JDBC ResultSet to grid where i didnt find in this forum, this is vital because grid is use everywhere... in php i can do this easily, but so confused in java and zk.... (i'm newbie, so i dont know doAfterCompose, ListModel, etc)....
Could you help me ??? thank you
package ctrl;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.*;
import org.zkoss.zk.ui.Component;
import org.zkoss.zk.ui.Executions;
import org.zkoss.zk.ui.event.Event;
import org.zkoss.zk.ui.event.EventListener;
import org.zkoss.zk.ui.util.GenericForwardComposer;
import org.zkoss.zul.*;
import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.query.Query;
public class MyComposer extends GenericForwardComposer {
private Button btn4, btn5, btn6;
private Grid grid;
private ListModelList lmList;
@Override
public void doAfterCompose(Component comp) throws Exception {
super.doAfterCompose(comp);
File f = new File("employee.mdb");
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
database+= f.toString() + ";DriverID=22;READONLY=true}"; // add on to the end
Connection con = DriverManager.getConnection( database ,"","");
Database d = Database.open(f); // -->
List<Query> query = d.getQueries();
String querys = query.get(1).toSQLString();
String q2 = querys.replace( '"', '\'' );
Statement s = con.createStatement();
s.executeQuery(q2);
ResultSet rs2 = s.getResultSet();
ResultSetMetaData rm = rs2.getMetaData();
int colCount = rm.getColumnCount();
//================================================================================================
grid.setRowRenderer(new RowRenderer() {
public void render(Row row, Object data) throws Exception {
if (data == null) return;
Columns columns = row.getGrid().getColumns();
if (columns.getChildren() == null) return;
int colCount = columns.getChildren().size();
Map<String, Object> params = new HashMap<String, Object>();
params.put("items", createStringList(colCount, String.valueOf(data))); //--------> ????????, i know this to put item, but always error....
row.appendChild(Executions.createComponents("items.zul", row, params));
}
});
lmList = new ListModelList();
grid.setModel(lmList);
//--
Columns columns = grid.getColumns();
if (columns.getChildren() == null) return;
columns.detach();
int num = 3;
Map<String, Object> params = new HashMap<String, Object>();
params.put("columns", createColList(colCount, rm)); //-------> solved !!!
grid.appendChild(Executions.createComponents("columns.zul", grid, params));
lmList.clear();
lmList.addAll(createStringList(num, "item")); //---------> ?????????, to make pre-row ???
}
private List<String> createColList(int num, ResultSetMetaData rm) throws Exception {
List<String> result = new ArrayList<String>();
for (int i = 0; i < num; i++) {
result.add("" + rm.getColumnName(i + 1));
}
return result;
}
private List<String> createStringList(int num, String startWith){
List<String> result = new ArrayList<String>();
for (int i = 0; i < num; i++) {
result.add(startWith + (i + 1));
}
return result;
}
}
This my sample
index.zul
<zk> <window apply="ctrl.MyComposer"> <grid id="grid" width="300px"/> </window> </zk>
columns.zul
<columns>
<column forEach="${arg.columns}" label="${each}" />
</columns>
MyComposer.java
package ctrl;
import java.sql.*;
import java.util.*;
import org.zkoss.zk.ui.Component;
import org.zkoss.zk.ui.Executions;
import org.zkoss.zk.ui.util.GenericForwardComposer;
import org.zkoss.zul.*;
public class MyComposer extends GenericForwardComposer {
private Grid grid;
@Override
public void doAfterCompose(Component comp) throws Exception {
super.doAfterCompose(comp);
Map<String, Object> params = new HashMap<String, Object>();
params.put("columns", getAllColumns("User"));
grid.appendChild(Executions.createComponents("columns.zul", grid, params));
grid.setRowRenderer(new RowRenderer() {
public void render(Row row, Object data) throws Exception {
if (data == null) return;
List<String> rowData = (List<String>) data;
for (String text : rowData) {
row.appendChild(new Label(text));
}
}
});
grid.setModel(new ListModelList(getAllData("User")));
}
private Connection createConnection() {
try {
String strurl = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=D:/MyDB.mdb";
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
return DriverManager.getConnection(strurl);
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return null;
}
private List<String> getAllColumns(String table) {
ArrayList<String> result = new ArrayList<String>();
try {
Connection conn = createConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from " + table);
ResultSetMetaData md = rs.getMetaData();
for (int i = 1; i <= md.getColumnCount(); i++) {
result.add(md.getColumnName(i));
}
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
private List<List<String>> getAllData(String table) {
ArrayList<List<String>> result = new ArrayList<List<String>>();
try {
Connection conn = createConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from " + table);
List<String> columns = getAllColumns(table);
while (rs.next()) {
List<String> row = new ArrayList<String>();
for (int i = 0; i < columns.size(); i++) {
row.add(rs.getString(columns.get(i)));
}
result.add(row);
}
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
}
About jdbc connection mdb you can search google
you will find many sample
Hi as1225, thank you very much for your jdbc-grid help...
however, i got problem on how to make on multicolumn sort be work.
As default, from example to make sort multi column we use :
<grid>
<columns >
<column label="Author" sort="auto" />
<column label="Title" sort="auto" />
<column label="Publisher" sort="auto" />
<column label="Hardcover" sort="auto" />
</columns>
<rows>
<row>
.........
</rows>
</grid>
but i can not apply sort="auto" with your jdbc-grid example code like below :
<columns>
<column sort="auto" forEach="${arg.columns}" label="${each}" />
</columns>
because i got this error : java.util.ArrayList cannot be cast to java.lang.comparable
You can also see my new Thread at Grid sorting problem
Could help me out this problem ?
Thanks
You can refer to this thread
still not resolved, i found another great solution on Wiki : http://en.wikibooks.org/wiki/ZK/Examples, the codes :
<window title="Sorting test">
<zscript><![CDATA[
class Comp implements Comparator {
private boolean _ascd;
private int _columnIndex;
public Comp(boolean ascd, int columnIndex) {
_ascd = ascd;
_columnIndex = columnIndex;
}
public int compare(Object o1, Object o2) {
String s1 = o1.getChildren().get(_columnIndex).getValue(),
s2 = o2.getChildren().get(_columnIndex).getValue();
int v = s1.compareTo(s2);
return _ascd ? v: -v;
}
}
// Instances for first column
Comp ascd1 = new Comp(true, 0);
Comp dscd1 = new Comp(false, 0);
// Instances for second column
Comp ascd2 = new Comp(true, 1);
Comp dscd2 = new Comp(false, 1);
]]></zscript>
<grid id="gTest">
<columns>
<column id="gcColOne" label="Year" width="10%" sortAscending="${ascd1}" sortDescending="${dscd1}" />
<column id="gcColTwo" label="Number" width="90%" sortAscending="${ascd2}" sortDescending="${dscd2}" />
</columns>
<rows id="gTestRows">
<row><label value="2000" /><label value="8" /></row>
<row><label value="2006" /><label value="1" /></row>
<row><label value="2002" /><label value="7" /></row>
<row><label value="2003" /><label value="2" /></row>
<row><label value="2007" /><label value="6" /></row>
</rows>
</grid>
</window>
but the questions, how to implement this to columns.zul ? i assume like below :
<zscript><![CDATA[
class Comp implements Comparator {
private boolean _ascd;
private int _columnIndex;
public Comp(boolean ascd, int columnIndex) {
_ascd = ascd;
_columnIndex = columnIndex;
}
public int compare(Object o1, Object o2) {
String s1 = o1.getChildren().get(_columnIndex).getValue(),
s2 = o2.getChildren().get(_columnIndex).getValue();
int v = s1.compareTo(s2);
return _ascd ? v: -v;
}
}
// Instances for first column
Comp ascd1 = new Comp(true, 0);
Comp dscd1 = new Comp(false, 0);
// Instances for second column
Comp ascd2 = new Comp(true, 1);
Comp dscd2 = new Comp(false, 1);
]]></zscript>
<columns sizable="true" >
<column sortAscending="${asc}" sortDescending="${dsc}" forEach="${arg.columns}" label="${each}" width="15" />
<column sort="auto" forEach="${arg.columns}" label="${each}" width="15" sortAscending="${ascd1}" sortDescending="${dscd1}" /> <!-- problem here -->
<!-- but how to make variable {ascd?} and {dscd?} generate number, example : ascd1, ascd2, dscd1, dscd3, etc.. -->
</columns>
or, the more efficient way, how to make them work for sort="auto", i'm confused to change this code so they will not make error : java.util.ArrayList cannot be cast to java.lang.comparable
private List<String> getAllColumns(String table) {
ArrayList<String> result = new ArrayList<String>();
try {
Connection conn = createConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from " + table);
ResultSetMetaData md = rs.getMetaData();
for (int i = 1; i <= md.getColumnCount(); i++) {
result.add(md.getColumnName(i));
}
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
private List<List<String>> getAllData(String table) {
ArrayList<List<String>> result = new ArrayList<List<String>>();
try {
Connection conn = createConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from " + table);
List<String> columns = getAllColumns(table);
while (rs.next()) {
List<String> row = new ArrayList<String>();
for (int i = 0; i < columns.size(); i++) {
row.add(rs.getString(columns.get(i)));
}
result.add(row);
}
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
OK
I found the solution
step 1:
add sort attribute in columns.zul
<columns>
<column forEach="${arg.columns}" label="${each}" sort="auto"/>
</columns>
step2:
remove below codes
grid.setRowRenderer(new RowRenderer() {
public void render(Row row, Object data) throws Exception {
if (data == null) return;
List<String> rowData = (List<String>) data;
for (String text : rowData) {
row.appendChild(new Label(text));
}
}
});
grid.setModel(new ListModelList(getAllData("User")));
setp3:
replace above codes to below
Rows rows = new Rows();
grid.appendChild(rows);
for (List<String> list : getAllData("User")) {
Row row = new Row();
rows.appendChild(row);
for (String text : list) {
row.appendChild(new Label(text));
}
}
Thank you very much as1225...
however there's still some issues :
1. using sort="auto" couldnt sort number, how to do this on ZK 3.6 ? i see on ZK 5, there's option sort=client(number), but i can't migrate, because still need spreadsheet in zk 3.6.
2. How to get Values from Grid, how we can response when click column x and row y ? ex: column: name, row:Dany
i try this :
Rows rows = new Rows();
grid1.appendChild(rows);
for (List<String> list : getAllData(formName)) {
Row row = new Row();
rows.appendChild(row);
for (String text : list) {
row.appendChild(new Label(text));
EventListener rowEvt = new EventListener() {
public void onEvent(Event event) throws Exception {
Row row = (Row)event.getTarget().clone();
Columns columns = row.getGrid().getColumns();
List columnList = columns.getChildren();
Column column = (Column)columnList.get(0); --> null pointer
List list = row.getChildren();
Label label = (Label)list.get(0); //---> We still need specify what column index, can we detect column automatically ?
alert(label.getValue());
alert(row.getGrid().getColumns().getValue()); //---> Resulting Null Pointer Exception, row can't get their column/grid...
}};
row.addEventListener("onClick", rowEvt);
}
}
Thank you as1225...
1. I can sort ID, but they are string number, not int number
maybe you can refer to the previous thread that I posted,
create a java bean and sort with get value, and set column sort="auto(value)", you can see it will refer to getValue, and it returns a int value
2. I make a sample
final Columns columns = grid.getColumns();
Rows rows = new Rows();
grid.appendChild(rows);
for (final List<String> list : getAllData("User")) {
Row row = new Row();
rows.appendChild(row);
for (final String text : list) {
Label label = new Label(text);
row.appendChild(label);
label.addEventListener("onClick", new EventListener() {
@Override
public void onEvent(Event event) throws Exception {
Row row = (Row) event.getTarget().getParent();
Label firstCell = (Label)row.getFirstChild();
int index = list.indexOf(text);
Column column = (Column) columns.getChildren().get(index);
alert("Row = " + firstCell.getValue() + ", Column = " + column.getLabel());
}
});
}
}
Asked: 2010-02-17 21:20:19 +0800
Seen: 6,611 times
Last updated: Jun 25 '12