How to get values from database and list them as properties?

[denza]

Hi,
can anyone help me with this? I saw one post on this subject but it did not help.
Im trying to read data about clients from database and show them in a list in a property field in a custom aspect.
Thanks!
Denis

[denza]

I managed to connect to the database and read values from there. What I wanna do now is this: when I read values from database that has more the one column to put values into different property fields. For example, information about client - name, address, city etc. should go into properties with tittles Name, Address, City where I can pick value from list of names, address and cities that where read from database! Has anyone manged to do this? Please help! Thanks

[msvoren]

If you post your js code, maybe we can see more clearly how to help you. Shouldn't be hard if you already have query results returned to alfresco through js.

[denza]

I used an example I found on this forum to do that, here it is

package org.contezza.customConstraints;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.faces.model.SelectItem;

import org.alfresco.repo.dictionary.constraint.ListOfValuesConstraint;
import org.alfresco.web.bean.generator.BaseComponentGenerator;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class ListOfValuesQueryConstraint extends ListOfValuesConstraint implements Serializable {

   @SuppressWarnings("unused")
private static Log logger = LogFactory.getLog(BaseComponentGenerator.class);

   private static final long serialVersionUID=1;

   private List<String> allowedLabels;

   @SuppressWarnings("unchecked")
public void setAllowedValues(List allowedValues) {}
   public void setCaseSensitive(boolean caseSensitive) {}

   public void initialize() {
       super.setCaseSensitive(false);
       this.loadDB();
    }

   public List<String> getAllowedLabels() {
      return this.allowedLabels;
   }

   public void setAllowedLabels(List<String> allowedLabels) {
      this.allowedLabels=allowedLabels;
   }

    public List<SelectItem> getSelectItemList() {
      List<SelectItem> result = new ArrayList<SelectItem>(this.getAllowedValues().size());
      for(int i=0;i<this.getAllowedValues().size();i++) {
         result.add(new SelectItem((Object)this.getAllowedValues().get(i),this.allowedLabels.get(i)));
      }
      return result;
   }

    protected void loadDB() {

       String driverName = "org.gjt.mm.mysql.Driver";
        String serverName = "localhost";
        String mydatabase = "denza";
        String username = "root";
        String password = "root";

        List<String> av = new ArrayList<String>();
        List<String> al=new ArrayList<String>();


        try {
           Connection connection = null;
            Class.forName(driverName);
            String url = "jdbc:mysql://" + serverName +  "/" + mydatabase;
            connection = DriverManager.getConnection(url, username, password);
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("select JMBG,name,address,city from klijenti");
            while (rs.next()) {
                av.add(rs.getString("JMBG"));
                av.add(rs.getString("name"));
                av.add(rs.getString("address"));
                av.add(rs.getString("city"));
             //???? al.add(rs.getString("JMBG"));
            }
        }
        catch (Exception e) {}

      super.setAllowedValues(av);
      this.setAllowedLabels(al);
   }
}
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

here is the model:

<property name="mm:city">
               <title>City</title>
               <type>d:text</type>               
               <mandatory>true</mandatory>               
               <constraints>
                  <constraint ref="mm:CodeLabel"></constraint>
               </constraints>
            </property>            
            <property name="mm:test">
               <title>Test</title>
               <type>d:text</type>               
               <mandatory>false</mandatory>               
               <constraints>
                  <constraint ref="mm:CodeLabel" component-generator="TextFieldGenerator"></constraint>
               </constraints>
            </property>
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[msvoren]

So, your java class returns query output..

You get multiple rows with:
while (rs.next())

And inside that, get your document's nodeRef, and put query veraiable to your content model properties.

Or am I not getting the essence of your problem?

[denza]

So, your java class returns query output..

You get multiple rows with:
while (rs.next())

And inside that, get your document's nodeRef, and put query veraiable to your content model properties.

Or am I not getting the essence of your problem?

Dont really understand what "put query veraiable to your content model properties" means. What I am trying to do is this:

Name: here is a drop-down menu with values from column name in my database
City: drop down menu with values from column city in my database

[denza]

So, your java class returns query output..

You get multiple rows with:
while (rs.next())

And inside that, get your document's nodeRef, and put query veraiable to your content model properties.

Or am I not getting the essence of your problem?

What I am getting now is all data from result set as value list in one property.
Don't really understand what "put query veraiable to your content model properties" means. What I am trying to do is this:

      Name: here is a drop-down menu with values under column name in my database
      City: drop down menu with values under column city in my database

Thanks!

[johanpi]

Could you please explain how to implement the model?

Thanks