rich:suggestionBox

[puh]

Hallo!

Ich würde mir gerne mit der suggestionBox den Inhalt einer MySQL-Datenbank anzeigen lassen. Wie kann ich das anstatt des Strings mit Städtenamen, der dort momentan enthalten ist, implementieren??

Freue mich schon auf Lösungsvorschläge, Kirsten!

[ilya_shaikovsky]

Sorry, but could you please write your question in English?

[puh]

Oh, yes, I´m sorry!

I`d like to use the suggestionBox with a MySQL database. So that the result comes from the database and not from a String with city names like it is implemented in the demo. How do I have to do that?

Yours Kirsten!

[ilya_shaikovsky]

In any way, to avoid unnecessary calls to db, while characters typed to suggested input - you need to 'cache' your values in the collection from session scoped bean and after that use as it shown in example.

[kyuss]

Just just need to call your SQL statement from the backing javabean. Then, instead of filling the list with citynames, fill them with the result from your sql statement.
If you have problems with java or mysql try this tutorial(german).

http://www.torsten-horn.de/techdocs/java-sql.htm

Grüße,

Alex

[puh]

I´m not sure, if I have understood you correctly. I´ve just began to work with RichFaces. Could you explain it very simple :) for me. Where do I have to put my DbConnection code? And how do I have to couple it with the result String in the RichFaces code?
Perhabs I could send you an example and you could explain it to me?

Yours, Kirsten!

[puh]

I´m working with Tomcat and I have put the Java code in WEB-INF/src. But if I try to compile the code for the database access I always get errors that my code cant´t be compiled, because of try or if blocks, but the code must be right. The backing javabean is this the file, where the java code is written in?
Do someone has an working example perhabs which she or he could send to me?

[kyuss]

if done that few days ago. In the suggestQuery, you have to change dbName, host, user and pw to your need. You'll need the mysql-connector-java.jar in your lib-directory of your webapp.

Here is the code from the bean:

public List autocomplete(Object event) {
 List v = new Vector();
 String prefix = event.toString();
 if(event.toString().length()>0){
 v = executeSuggestQuery("SELECT column FROM table");
 }
 return v;
 }
private List executeSuggestQuery(String query) {
 //System.out.println(query);
 String dbName="test";
 String host="localhost";
 String user="user";
 String pw="xxx";
 Vector v = null;
 Connection conn;
 Statement stmt;
 ResultSet rs;
 try{
 Class.forName("com.mysql.jdbc.Driver");
 conn = DriverManager.getConnection("jdbc:mysql://"+host+"/"+dbName,user,pw);

 stmt = conn.createStatement();
 rs = stmt.executeQuery(query);
 ResultSetMetaData rsmd = rs.getMetaData();
 v = new Vector(rs.getFetchSize());
 while(rs.next()){
 Data d = new Data(rs.getString(1));
 v.add(d);
 }
 } catch (SQLException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 v = new Vector();
 v.add(e.getMessage());
 return v;
 } catch (ClassNotFoundException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 v = new Vector();
 v.add(e.getMessage());
 return v;
 }
 return v;
 }

}

You'll also need this data class:

import java.io.Serializable;

public class Data implements Serializable {
 private String text;

 public Data(String text){
 this.text = text;
 }

 public String getText() {
 return text;
 }

 public void setText(String text) {
 this.text = text;
 }
}

finally, the code for your jsp-page, adjust yourBean:

h:inputText value="#{yourBean.input}" id="suggestInput" />
<rich:suggestionbox width="100" height="150" for="suggestInput"
 suggestionAction="#{yourBean.autocomplete}" var="data">
 <h:column>
 <h:outputText value="#{data.text}" />
 </h:column>
 </rich:suggestionbox>

Hope that helps :)

[kyuss]

Oh, and delete the code in the catch-blocks. Doesn't make any sense...

[puh]

Thank you very, very much!!!!
I will try it as soon as possible and write you back if it works!!
It´s very nice!!

Yours Kirsten!

[puh]

Hello Alex!

I´ve implemented your example in Tomcat. and changed the settings for my database. I have put the Data.class and yourBean.class in the folder classes/org/rf and then I´ve written

<managed-bean> <managed-bean-name>suggestInput</managed-bean-name> <managed-bean-class>org.rf.yourBean</managed-bean-class> <managed-bean-scope>session</managed-bean-scope>
</managed-bean>

in the faces-config.xml. But when I want to start the programm and enter a word, there is an error 500 and it says that the programm can´t find the autocomplete method.
Do you know, what that could mean? Do I have to write somthing else in the faces-config.xml or do I have to put the classes somewhere else?

Yours Kirsten!

[puh]

He gives out the error, too, if I write nothing into the faces-config.xml. He seems not to find the yourBean.class. What could I do to solve the problem?

[puh]

And when I write

<managed-bean>
<managed-bean-name>yourBean</managed-bean-name>
<managed-bean-class>org.rf.yourBean</managed-bean-class>
<managed-bean-scope>session</managed-bean-scope>

</managed-bean>

in the faces-config.xml I get this error as result:

PropertyNotFoundException: Error getting property 'input' from bean of type org.rf.yourBean

O.k., perhabs you know what to do?

[puh]

I think that I have understood you wrong. Did you mean that I have to write for yourBean.input suggestionBox.property in the jsp-file as it is written in the demo-file?
I´m sorry that I am disturbing you so often!!
I only want to understand how it works!

Ok, I´m looking forward for your answer.

[puh]

Hello!

It works!! Jippieh! Thank you for the code help!
But he database I´m using ist very big and I sometimes get a javaheapException. Do you know what I could do to solve the problem?