<center></center>
#### Repository
https://github.com/dmlloyd/openjdk
#### What Will I Learn?
In this series, we will be building a data analysis application to work like SPSS together. Meanwhile for this tutorial;
- You will learn To create database tables from Outside the DBMS tools
- You will learn to set Default values for JFX table columns from Inside your Application
- You will learn to fix alerts for JavaFx and Accept data base on User Specification
#### Requirements
You'll need the following in the course of this series.
- IntelliJ Idea IDE
- Basic Understanding of Java Methods and Classes
- SQLite Tool
#### Difficulty
- Intermediate
#### Introduction
Hi there, Lol the topic looks very serious yeah I know that but trust me it's way simpler than you can imagine even though it could be a little tough along the line.
So before we begin you can look over this old post of mine about [creating new JavaFx Applications, Adding CSS and building a friendly User Interface](https://steemit.com/utopian-io/@official-hord/java-fx-programming-creating-java-fx-application-with-fxml-a0ae78c061472est). I'll be skipping all that in this tutorial so that we can go straight to the Major work. You can as well give your pratice application the same name as I have given mine to avoid any issues that might come up with naming.
<center></center>
****************************************************************************************************************
###### Quickly:
After creating the application, remove the size specification of the scene in the main class to allow the system use the size specified in the fxml document.
###### Next:
Create a new package inside the Sample package, I named it `CreateTable` because it will be used to handle the table creation activity of our application, this is to make the process simpler for your understanding.
##### Stage One
A - Build the Table Maker Screen:
Items:
> 1. Table (No Columns)
>2. Text (For Labels)
>3. TextFields (Required Text Areas)
>4. Buttons (+ to add columns,- to remove column, and Create Table)
>5. ComboBox (To select Data Typre Accepted by Column)
>6. Spinner (For maximum range of Values)
B - Create TableDetail Class and Column Addition Method
Since the system is meant to create a table for the user based on his/her own requirement for data entry, we will be giving the user a few options of datatypes to be accepted into the system (Text, Numeric and Date) only values of this data type will be accepted into the system.
Step 1:
First we create an Observable List to hold the data types and a comboBox to enable the user select one of the three types.
```
@FXML
private ComboBox datatype;
ObservableList datatypes = FXCollections.observableArrayList("Date","Numeric","Text");
```
Then create an Initialize method to add the observable list items to the ComboBox we have available.
```
public void initialize(){
datatype.setItems(datatypes);
}
```
This way when the DataType ComboBox is clicked, It will display the list of Items in the ObservableList.
Step 2:
Create Identities for All Items/Compnents in the Screen.
```
@FXML
private TextField tablenametxt;
@FXML
private TextField Columnnametxt;
@FXML
private Spinner datasizetxt;
@FXML
private Button createTable;
@FXML
private Button AddRow;
@FXML
private Button RemoveRow;
```
After creating the Id's, Add them to the respective Item in the Scene Builder.
Step 3:
Create a Class to which will be used to Hold the table details.
In the class, Declare
```
String ColumnName;
String ColumnType;
int ColumnSize;
```
This is what we will need to create a table, Right click in the code area to generate a constructor, getter and setter for the variables. You will have this in your code area now:
```
public String getColumnName() {
return ColumnName;
}
public void setColumnName(String columnName) {
ColumnName = columnName;
}
public String getColumnType() {
return ColumnType;
}
public void setColumnType(String columnType) {
ColumnType = columnType;
}
public int getColumnSize() {
return ColumnSize;
}
public void setColumnSize(int columnSize) {
ColumnSize = columnSize;
}
public Tableinfo(String columnName, String columnType, int columnSize) {
ColumnName = columnName;
ColumnType = columnType;
ColumnSize = columnSize;
}
```
This will hep us add column information to the table in our design view.
Step 4:
We have previously created Id's for other components/items in the application but haven't done same for the Table or columns we'll be adding in the table. This is because we need to have created the class for the table and it's columns to pull data from before we create the Table ID.
```
@FXML
private TableView<Tableinfo> tableinfoTableView;
@FXML
private TableColumn<Tableinfo, String> columnname;
@FXML
private TableColumn<Tableinfo, String> columndatatype;
@FXML
private TableColumn<Tableinfo, String> columnmaxvalue;
```
Add Columns to the table and name them as seen below.
Step 5:
WE create another observable list, this time to enable us add and pull data from the class created earlier.
`ObservableList columninfo = FXCollections.observableArrayList();`
Then we create the AddColumn Method.
```
@FXML
private void AddColumn(ActionEvent evt) throws Exception {
String Tablenames = tablenametxt.getText();
String ColumnNames = Columnnametxt.getText();
String ColumnTypes = datatype.getSelectionModel().getSelectedItem().toString();
int ColumnSizes = Integer.parseInt(datasizetxt.getEditor().getText());
if (Tablenames.isEmpty()) {
Alert alert = new Alert(Alert.AlertType.INFORMATION);
alert.setTitle("Table Name - System Alert");
alert.setHeaderText("Table name not found\n \nKindly enter a Table name and Retry");
alert.setContentText("Click \"OK\" to Continue.");
alert.showAndWait();
}
else {
columninfo.add(new Tableinfo(ColumnNames, ColumnTypes, ColumnSizes));
}
columnname.setCellValueFactory(new PropertyValueFactory<>("columnName"));
columndatatype.setCellValueFactory(new PropertyValueFactory<>("columnType"));
columnmaxvalue.setCellValueFactory(new PropertyValueFactory<>("columnSize"));
tableinfoTableView.setItems(columninfo);
}
```
This method adds column information to the columninfo observable list then passes the information through the TableInfo class and adds the information in the tableview. Now if the tablename is left empty, the system will notify the user to input table name else the column is added, you can see the code for the alert in the if statement while the system adds the information in the else section and displays it in the table view.
See [Here](https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=2ahUKEwiXoa7oncDhAhXcSxUIHUVNDHoQFjAAegQIARAB&url=https%3A%2F%2Fdocs.oracle.com%2Fjavase%2F8%2Fjavafx%2Fapi%2Fjavafx%2Fscene%2Fcontrol%2FCell.html&usg=AOvVaw3tsLWgdmI1UQ9ztWYqAxV5) for .setCellFactory and PropertyValueFactory methods.
Step 6:
Now that our column information is saved in the ObservableArrayList, we have to create the table Such that the user can now input data in the table and carry out analysis, but first we have to save the table and column information in a database such that even when the application is closed and reopened, the user can still access the table and the information in it.
I've explained how to Create and Sqlite Database and also how to connect it to our IntelliJ project [Here](https://steemit.com/utopian-io/@official-hord/java-fx-programming-creating-java-fx-application-with-fxml-a0ae78c061472est)... Create your database, connect it to your application and Create two tables in it. One to Hold the table information and another for the Column information, TableInfo Table should have columns TableId and TableName, while the ColumnInfo should have columns TableId, ColumnName, ColumnType and ColumnSize.
These values will be used to create a new table from inside the application.
**To add table information to the database**
To be able to create a table in the database, the syntax of the sql code is; `Create Table TableName (column 1 and properties, column 2.. e.t.c.);`
To achieve this, I'm creating a Global string variable to hold the sql statement, ` String tablecreatorsql =" "; `, here we will add the column properties for every new column and add the tablename to the string at the end.
In the AddColumn action method, add the following code;
```
tablecreatorsql = tablecreatorsql+" "+ColumnNames+" "+ColumnTypes+",";
```
This will add the ColumnName and ColumnType values in the table creation string each time the add column button is clicked.
Next we create a method to create the table
```
@FXML
private void createTable(ActionEvent evt) throws Exception {
String sql = "CREATE TABLE "+Tablenames+" (" + tablecreatorsql + ");";
}
```
In this method, we will create the table creation sql statement from the table name and tablecreatorsql string, because we have added a comma at the end of each column information, this means the last column will still have a comma which will give us an sql error. What to do is, we create another method to remove the last comma from the sql statement.
```
//method to remove last comma
public static String charRemoveAt(String str, int p){
return str.substring(0,p)+str.substring(p+1);
}
```
Next we select the index of the last comma in the satatement and use the charRemoveAt method to make it dissapear.
```
int x = sql.lastIndexOf(',');
sql = charRemoveAt(sql,x);
```
What this will do is divide the string at the a specified point and add the string after that point to the divided string leaving the point in between that space to dissapear.
Now that we have the correct syntax for our statement, we can now go ahead to run the code, but before that Add the following code to the createTable methodl
```
try{
ps = conn.prepareStatement(sql);
ps.execute();
}
catch(Exception e){
e.printStackTrace();
}
```
If we run the code now, we will have created a new table in the database that can now accept data to be used for the analysis.
You can see the execution in the screenshots below.
*****************************************************************************************************************
### <center>I'll have to break the tutorial here because it's getting too long, the continuation which will be changing fxtable models to that of the database and data entry will be made in the next post. Thanks.</center>
#### Curriculum
This is the First tutorial of this series. But you can take a look at previous tutorials related to this for a better understanding on [creating your first application](https://steemit.com/utopian-io/@official-hord/video-java-fx-programming-introduction) and [connecting it to a database](https://steemit.com/utopian-io/@official-hord/video-java-fx-programming-creating-java-fx-login-application-55f3222176ce3est) also [how to apply css styling](https://steemit.com/utopian-io/@official-hord/java-fx-programming-creating-java-fx-application-with-fxml-a0ae78c061472est).
#### Proof of Work Done
https://github.com/officialhord/JavaFxTutorial/tree/DataAnalyserTutorial/DataAnalyser
<center>
hiveblocks