7 Replies Latest reply on Apr 12, 2013 5:57 PM by biochemistry43

    Data from a database into combobox

    biochemistry43
      I was looking for something to help me accomplish this task but not very clear to me how.

      Could someone give me a clue how to do?

      The idea is to get the result of a query (SELECT DISTINCT ...) in a combobox.

      The combobox is made in FXML.

      In advance thank you very much
        • 1. Re: Data from a database into combobox
          James_D
          The simple answer is that you get the data from the database in your controller (using JDBC for example), put them in a list, and call comboBox.addAll(...).

          You probably don't want JDBC code in your controller though. It's better to encapsulate the JDBC code in an Data Access Object, and arrange for your controller to have access to that object. This means you have a single implementation class to change (or replace) if you change your data access strategy (change the DB design, or even change from JDBC to a ORM framework).
          • 2. Re: Data from a database into combobox
            biochemistry43
            Well ... in fact, the class to access database and I have (is based on JDBC). Rather I would like to know how to introduce the results of a query in my combobox
            • 3. Re: Data from a database into combobox
              James_D
              Assuming the class returns the items you need in a List (or other Collection), you can do
              comboBox.getItems().addAll(...);
              and pass in what you got back from your data access object.
              • 4. Re: Data from a database into combobox
                shakir.gusaroff
                The following works for me:




                // ComboBoxDb.java

                 
                package comboboxdb;
                
                import javafx.application.Application;
                import javafx.fxml.FXMLLoader;
                import javafx.scene.Parent;
                import javafx.scene.Scene;
                import javafx.stage.Stage;
                
                public class ComboBoxDb extends Application {
                    
                    @Override
                    public void start(Stage stage) throws Exception {
                        Parent root = FXMLLoader.load(getClass().getResource("Sample.fxml"));
                        
                        Scene scene = new Scene(root);
                        
                        stage.setScene(scene);
                        stage.show();
                    }
                
                   
                    public static void main(String[] args) {
                        launch(args);
                    }
                }
                //DB.java
                package comboboxdb;
                
                import java.sql.*;
                import java.util.*;
                import java.io.IOException;
                import javafx.collections.FXCollections;
                import javafx.collections.ObservableList;
                import java.text.SimpleDateFormat;
                
                public class DB {
                
                    public DB() {
                        connect();
                    }
                   
                    public String dbUrl = "jdbc:oracle:thin:@exa1-scan:1521/....ORG";
                    public Connection connection = null;
                    public Statement statement = null;
                    public PreparedStatement prstatement = null;
                    public ResultSet rs = null;
                    public String query = null;
                   
                    public Connection connect() {
                
                
                        try {
                            DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
                            connection =
                                    DriverManager.getConnection(dbUrl, "bbb", "bb");
                
                            System.out.println("Connection is created");
                        } catch (SQLException e) {
                            System.out.println("Connection is not  created");
                            e.printStackTrace();
                        }
                        return connection;
                
                    }
                
                    public void close() {
                        try {
                            if (connection == null) {
                                return;
                            }
                            connection.close();
                            connection = null;
                
                        } catch (SQLException e) {
                
                            e.printStackTrace();
                        }
                    }
                
                    public ArrayList<String> getSsns() {
                
                       
                        String query =
                                "SELECT distinct ssn FROM EUPS.EUPS_MEMBER_INFO_V";
                
                        ArrayList<String> al = new ArrayList();
                        prstatement = null;
                
                        try {
                
                            prstatement = connection.prepareStatement(query);
                            rs = prstatement.executeQuery();
                                
                
                            while(rs.next()) {
                                al.add(rs.getString(1));
                
                            }
                          
                            return al;
                
                        } catch (SQLException e) {
                
                            return null;
                
                        } finally {
                
                            prstatement = null;
                
                        }
                    }
                }
                //Sample.fxml
                <?xml version="1.0" encoding="UTF-8"?>
                
                <?import java.lang.*?>
                <?import java.util.*?>
                <?import javafx.scene.*?>
                <?import javafx.scene.control.*?>
                <?import javafx.scene.layout.*?>
                
                <AnchorPane id="AnchorPane" prefHeight="200" prefWidth="320" xmlns:fx="http://javafx.com/fxml" fx:controller="comboboxdb.SampleController">
                    <children>
                        <ComboBox layoutX="126" layoutY="90"   fx:id="cb" />
                       
                    </children>
                </AnchorPane>
                //SampleController
                package comboboxdb;
                
                import java.net.URL;
                import java.util.ArrayList;
                import java.util.List;
                import java.util.ResourceBundle;
                import javafx.collections.FXCollections;
                import javafx.collections.ObservableList;
                import javafx.event.ActionEvent;
                import javafx.fxml.FXML;
                import javafx.fxml.Initializable;
                import javafx.scene.control.ComboBox;
                import javafx.scene.control.Label;
                
                public class SampleController implements Initializable {
                
                      
                    
                    @FXML
                    private ComboBox cb;
                
                
                    @Override
                    public void initialize(URL url, ResourceBundle rb) {
                      
                        DB db = new DB();
                        ArrayList<String>  list =   db.getSsns(); 
                        ObservableList<String> options =  FXCollections.observableArrayList(list);
                  
                        cb.setItems(options);
                        db.close(); 
                
                    }
                }
                • 5. Re: Data from a database into combobox
                  biochemistry43
                  well ... I already tried but I could not.
                  I must say that I am new to programming and is the first time I get with databases seriously.

                  Here what I tried ...
                   public void initialize(URL url, ResourceBundle rb) {
                          
                          //BDConn is class connecting with postgres
                          BDConn bd = new BDConn ();
                          bd.connPostgres();
                          //this i tried
                          List proyList = new ArrayList();
                          projectCombo.getItems().clear();
                          if (bd.initSearch("SELECT DISTINCT * FROM \"Gastos\"")> 0)
                          {
                              try
                              {
                                  do
                                  {
                                      proyList.add(bd.res.getString("project"));
                                  } while( bd.res.next() );                                                                      
                              } 
                              catch(Exception e)            
                              {  
                              
                              }
                          }
                          bd.endSearch();      
                          projectCombo.getItems().addAll(proyList);
                          projectCombo.getSelectionModel().selectFirst();
                      }
                  ...I place the class with which I connect as reference
                  import java.sql.*;
                  import java.util.logging.Level;
                  import java.util.logging.Logger;
                  
                  public class BDConn
                  {
                      public String login;
                      public String password;
                      public String url;
                      public Connection conn = null;
                      public Statement stmt;
                      public ResultSet res;
                  
                      BDConn()
                      {
                          this.login = "postgres";
                          this.password = "nautilus";        
                          url = "jdbc:postgresql://localhost:5432/Giaterm";
                      }
                      
                      
                      public boolean connPostgres()
                      {
                            boolean flag= false;
                            try{
                               Class.forName("org.postgresql.Driver").newInstance();
                               conn = DriverManager.getConnection(url,login,password);
                               if(conn != null) flag = true;
                               System.out.println("connect");
                                 }
                            catch(Exception e){
                                System.out.println(e);
                                flag = false;
                                }
                            return(flag);  
                      }
                      
                      //returns int. indicates the number of records
                      public int initSearch(String data)
                      {
                             int n = 0;
                             try{
                                  stmt = conn.createStatement();
                                  res  = stmt.executeQuery(data);
                                  if (res.next()){
                                      System.out.println("There records");
                                  }
                                  else {
                                      System.out.println("No there records");
                                  }
                                  while(res.next()) {
                                     n++;
                                  }
                                  res.first();
                                  }
                             catch(Exception e){
                                 n = -1;
                             }
                             return(n);
                      }
                      
                      public boolean endSearch()
                      {
                            boolean flag = false;
                            try{
                                 res.close();
                                 stmt.close(); 
                                 flag = true;
                                 }
                            catch(Exception e){
                                flag = false;
                                }
                             return(bandera);
                          
                      }
                      
                       public boolean closePostgress()
                      {    
                             boolean flag= false;
                             try{
                                  conn.close();
                                  flag= true;
                                }
                             catch(Exception e ){
                                  flag= true;
                                  }  
                             return(flag);
                        }
                     
                     public boolean insertData(String record)
                     {
                            boolean flag= false;
                            try{
                                 stmt2 = conn.createStatement();
                                 stmt2.executeUpdate(record);
                                 stmt2.close();
                                 flag = true;
                                 }
                            catch(Exception e) {
                                System.out.println(" " + e);
                               flag= false;
                               }
                            
                            return(flag) ;
                     }
                  }
                  • 6. Re: Data from a database into combobox
                    biochemistry43
                    ok ok not seen your kind response.

                    Let me check ... Thank you very much in advance
                    • 7. Re: Data from a database into combobox
                      biochemistry43
                      works perfectly...

                      Thank you very much

                      Blessings