//JavaFXTemplate1.java
//"List_ListView_PgSQL1B.txt"
package javafxtemplate1;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import javafx.application.Application;
import javafx.beans.property.ReadOnlyStringWrapper;
import javafx.beans.value.ObservableValue;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.scene.Group;
import javafx.scene.Scene;
import javafx.scene.control.Label;
import javafx.scene.control.ListView;
import javafx.scene.control.TreeTableColumn;
import javafx.scene.control.TreeTableColumn.CellDataFeatures;
import javafx.scene.control.TreeTableView;
import javafx.scene.control.TreeItem;
import javafx.scene.layout.HBox;
import javafx.scene.layout.VBox;
import javafx.stage.Stage;
import javafx.util.Callback;

/**
 *
 * @author Manas9
 */
public class JavaFXTemplate1 extends Application {
    //User Interface - display 
    ExternalClass1B ext1  = new ExternalClass1B();
    ExternalClass3 ext2  = new ExternalClass3();
   // TreeItem<EMP1> root = new TreeItem<>();
   @Override
    public void start(Stage primaryStage) throws Exception {
        Group root  = new Group(); 
ext1.loaddata();ext2.loaddata();
// to populate Listview
ObservableList<EMP1> oString1 = 
FXCollections.<EMP1>observableArrayList(ext1.list1);
//
ObservableList<String> oString2 = 
FXCollections.<String>observableArrayList(ext2.list1);
//
ListView<EMP1> lvwList1 = new ListView<>(oString1);
lvwList1.setLayoutX(10); lvwList1.setLayoutY(100);
lvwList1.setMaxWidth(100);
ListView<String> lvwList2 = new ListView<>(oString2);
lvwList2.setLayoutX(110); lvwList2.setLayoutY(80);
//
Label lable1 = new Label("Visitor       ");
Label lable2= new Label("           EMP");
//
lvwList2.setMaxWidth(100);
HBox groot1 = new HBox(); HBox groot2 = new HBox(); 
groot1.setLayoutX(50);groot1.setLayoutY(50);
groot2.setLayoutX(50);groot2.setLayoutY(20);
groot1.getChildren().addAll(lvwList1, lvwList2);
groot2.getChildren().addAll(lable1, lable2);
 root.getChildren().addAll(groot1, groot2);
Scene scene = new Scene(root, 350, 250);
primaryStage.setScene(scene);
primaryStage.setTitle("PostgreSQL:: UIControl");
                primaryStage.show();
    }
    public static void main(String[] args) {
        launch(args);
    }
}
// class ExternalClass1B.java
package javafxtemplate1;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Manas9
 */
public class ExternalClass1B {
    public static String str1, str2, str3; public int n1; 
// call ExtrenalClass2 prior to Externalclass1 
ExternalClass2 extclass = new ExternalClass2();
private static Connection conn;
public List list1 = new ArrayList();
public void loaddata() throws SQLException 
{
// Connection 
Connection connect = extclass.createConnection();
String query = " SELECT * FROM pgsvisitor1b where id=102";
Statement st = null;// query statement support
ResultSet rs = null;// active connection database result
//
try {
st = connect.createStatement(); rs = st.executeQuery(query); 
while(rs.next())
{ 
str1 = rs.getString("fname");
str2 =rs.getString("lname");
str3 = Integer.toString(rs.getInt("age"));
//
list1.add( str1);
list1.add(str2);
list1.add( str3);
}
} catch (Exception e) {
System.out.println(e.toString());
}
//return str3; 
}}
//
//
/*
 * This class has its own'connection 
 * does not need ExternalClass2.java
 * Uses Java RowSet than ResultSet
 */
package javafxtemplate1;

import com.sun.rowset.JdbcRowSetImpl;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import javax.sql.rowset.JdbcRowSet;
import javax.sql.rowset.RowSetProvider;

/**
 *
 * @author Manas9
 */
public class ExternalClass3 {
      public List list1 = new ArrayList();
      public  List<EMP1> eList = new ArrayList<>();
      //
       Connection c = null;  Statement st = null;
       //Below for tableview uses 
//public  List<EMP1>loaddata() throws SQLException 
 public   List loaddata() throws SQLException 
//public   void loaddata() throws SQLException 
{
       String dbURL = "jdbc:postgresql://localhost:5432/pgsdemo1";
       String user = "postgres"; String pwd = "postgre_manas9";
       Properties param = new Properties(); 
       String sql1 = "Select * from emp Where deptno =30";
       //
       param.put("user",user); param.put("password",pwd);
          c = DriverManager.getConnection(dbURL,user,pwd);
          st = c.createStatement();
  JdbcRowSet rowSet = RowSetProvider.newFactory().createJdbcRowSet();
       rowSet.setUrl(dbURL); rowSet.setUsername(user);
       rowSet.setPassword(pwd); rowSet.setCommand(sql1);
        rowSet.execute();
  st = c.createStatement(); 
     while (rowSet.next()) {
            int eID = rowSet.getInt("empno");
             String firstName = rowSet.getString("ename");
             String ejob = rowSet.getString("job");
             int eDept= rowSet.getInt("deptno");
             double esal = rowSet.getDouble("sal");
    EMP1 person = new EMP1(eID,firstName, ejob, eDept, esal);
        eList.add(person);
       // The above used for table view examples                
             list1.add(eID); list1.add(firstName);
             list1.add(ejob); list1.add(eDept); list1.add(esal);
              list1.add("-------"); 
         }
           return list1;
              }
}

//ExternalClass2
/*
 * It is a connection class template and used by 
 * many other classes except ExternalClass3
 * 
 */
package javafxtemplate1;

import java.sql.Connection;
import java.sql.DriverManager;

/**
 *
 * @author Manas9
 */
public class ExternalClass2 {
    Connection cnn= null;
     String dbURL = "jdbc:postgresql://localhost:5432/pgsdemo1";
      String user = "postgres"; String pwd = "postgre_manas9";
      // Creating a function to get a connection
	public Connection createConnection() {
		 System.out.println("Connection Object Created");
		// checking connection
		if (cnn != null) {
			System.out.println("Can't creaate a connection");
			return cnn;
		} else {
		try {
		// Getting connection
		cnn = DriverManager.getConnection(dbURL,user,pwd);
			} catch (Exception e) {
				System.out.println(e.toString());
			}
		}
		return cnn;
	}

}