topic Re: How to get values from database and list them as properties? in Alfresco Archive

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

denza — Mon, 09 Mar 2009 13:52:08 GMT

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

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

denza — Wed, 11 Mar 2009 11:39:00 GMT

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

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

msvoren — Wed, 11 Mar 2009 12:28:45 GMT

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.

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

denza — Wed, 11 Mar 2009 12:46:49 GMT

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>
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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

msvoren — Wed, 11 Mar 2009 14:08:50 GMT

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?

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

denza — Wed, 11 Mar 2009 14:44:59 GMT

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

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

denza — Wed, 11 Mar 2009 14:49:04 GMT

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!

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

johanpi — Wed, 26 May 2010 05:38:23 GMT

Could you please explain how to implement the model?

Thanks