Before we Begin
Here, in this Post, I have tried to explain about creating simple form using scenebuilder and javaFX and using it to insert data in MS SQL 2008. Various problems or errors i faced and solutions i found are also included here. I hope this blog will be useful even for the FX beginners.
OK. Lets start from scratch. First thing we need is JDK (Java Development Kit) in order to proceed with our java development (we all know that!!). And of course we don't want to write thousands of lines of codes in notepad. Do we? So we also need IDE (Integrated Development Environment). There are various types of IDE like eclipse, netbeans, intellij etc. I am using netbeans 8.0 for this project. We can use any IDE we are comfortable with (It does not matter, but i found netbeans very easy to use with javaFX). I think you will find latest version of JDK as well as Netbeans here:
http://www.oracle.com/technetwork/java/javase/downloads/index.html
Now we need SceneBuilder for creating GUI of javaFX. Its way too easy to create GUI with scenebuilder and it saves lots of time. You can download latest version of SceneBuilder (2.0 till this date) from here:
http://www.oracle.com/technetwork/java/javase/downloads/sb2download-2177776.html
Oh... and I will assume that you all have MS SQL 2008 installed. Its way to easy to assume.
This post covers following titles:
Since we have everything we need, lets begin by creating a simple form in javafx.
Let's start by creating a new project in Netbeans.
Follow following steps to create a new project:
1. Click file --> New Project
2. Window shown below appears
3. Click JavaFX --> JavaFX FXML Application.
4. Now the window shown below will appear:
Here we can provide project name as well as FXML file name. We will provide project name, fxml file name and we will check the option that says 'create application class'.
5. click finish. As soon as you click finish, three files gets created as shown in picture below:
"insertuser" is default package created by the netbeans.
"InsertUser.fxml" file is our fxml file which deals with GUI.
"InsertUser.java" is our main file containing main class which also loads FXML file.
"InsertUserController.java" is our controller class which is used for controlling and handling various events
such as mouse clicked, button pressed, mouse hovered, mouse entered and so on.
Double click on "InsertUser.fxml" (FXML file) in order to open FXML file in SceneBuilder. To view FXML codes, right click and select edit. After double clicking you will obtain the screen as shown below:
A default program "Hello world" will be automatically created by the netbeans every time you create new project. We don't need those so they must be deleted or we can modify them according to our needs. Here, i am deleting them. I have also deleted all the methods that are automatically created in controller file.
At the top of left-hand-side you will see various GUI components such as panes, textfield, butons, labels etc. We can easily drag and drop what we need so it is very easy to create GUI.
OK. My GUI looks like this:
For now i am just going with username, user address and user image. Image circled in red is ImageView. At the top of right-hand-side you can see the properties of every component you select. As you can see i have selected image from properties tab. You can browse for image and select any image you like to display in ImageView. With this we have created the user-form. Lets leave it for now and lets move on to creating Database in MS SQL 2008.
We are going to create a database and a table that stores all data of the user. Open your "Microsoft SQL Server Management Studio". At the left-hand-side you will see various fields as shown in figure(left):
Right click on database then select new Database to create a new database (We can also create database and tables by using Query. Here, I am going with GUI). Give your database name and click 'Ok'. This will create your database. Expand 'Database' field select your database and expand it as well (right figure).
Now select 'Tables' right click it and select 'New Table'. Enter column name, data type, and check whether you want it to accept null values or not. For now we only need one table.
For image select varbinary(MAX) data type. Save your table and there you go... now we have a database. Name of our database is "InsertUser" and table is "user".
We have our GUI of user-form and we have our Database. Now, lets try to connect our application with MS SQL database.
first of all you need a jdbc driver for MS SQL. To download JDBC driver for MS SQL you can go to following link:
http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx
Second you need to start all the SQL Server services. To start SQL Server services go to
Start -->
SQL server configuration manager -->
Sql server Network Configuration -->
Protocols for SQLEXPRESS. Make sure that all the protocals on right-hand-side are enabled as shown in figure.
Third you need to Select SQL Server Services and start following services:
- SQL server
- SQL server Browser
As shown in figure.
Second and third step given above saves you from following error:
com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host YOUR-HOST, port YOUR-PORT-NO has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190) at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:241)
--------------------------
found out that class path is not necessary. Go here-------------------------------------
Forth step, Now you need to provide the classpath of jdbc jar file. To do this, right click on Computer --> properties --> Advance System Setting --> Environment variable
Search for "CLASSPATH" in system variables. If class path is present double click it and, Copy and paste the path of your jar file in variable value. If not, create new, give its name as "CLASSPATH" and copy-paste the path as shown in figure.
------------------------------------------------------------------------------------------------------------
Editing sqljdbc4.jar file will prevent you from following error:
java.lang.SecurityException: invalid SHA1 signature file digest for com/microsoft/sqlserver/jdbc/InputStreamGetterArgs.class
at sun.security.util.SignatureFileVerifier.verifySection(SignatureFileVerifier.java:453) at sun.security.util.SignatureFileVerifier.verifySection(SignatureFileVerifier.java:453)
at sun.security.util.SignatureFileVerifier.processImpl(SignatureFileVerifier.java:24
Oh... and 1 (one) more thing is required... that is to check port number used by sql server. What you need is tcpView. You can download it from:
http://technet.microsoft.com/en-us/sysinternals/bb897437.aspx
port number is required while connecting your application to MS SQL. Run
tcpView.exe, from here you can easily get port number used by sql server.
Okei now, We are ready for Coding.
Don't forget to add jar file to your library.
First I am creating separate Database.java file for my own ease. It is not compulsory to create one but i recommend creating one. You can create separate package for this class but for now i am creating this class in the same package. Following method is created in
Database class for connection.
System.out.println prints "
Connected" if connection is successful else it throws errors.
public void dbConnect() throws ClassNotFoundException, SQLException {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://SERVER NAME:PORT NUMBER;database=DATABASE NAME;integratedSecurity=true;";
con = DriverManager.getConnection(connectionUrl);
System.out.println("connected");
statement = con.createStatement();
}
Now we must call this '
dbConnect' method from controller class i.e
InsertUserController.java in our case. Code of controller class is provided below:
package insertuser;
import java.net.URL;
import java.sql.SQLException;
import java.util.ResourceBundle;
import java.util.logging.Level;
import java.util.logging.Logger;
import javafx.fxml.Initializable;
/**
*
* @author muskan
*/
public class InsertUserController implements Initializable {
Database db = new Database();
@Override
public void initialize(URL url, ResourceBundle rb) {
try {
db.dbConnect();
} catch (ClassNotFoundException ex) {
Logger.getLogger(InsertUserController.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
Logger.getLogger(InsertUserController.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
And.... we are done!!! Congratulation. We have established a connection to database. We still have image to insert into the database. owww...myaaaannnnnn....
Now its time to open your scenebuilder.... again!!! We have to provide name (or lets say fx:id) for components such as TextField and ImageView. Click the component and at the right-hand-side you can see three tabs: Properties, Layout and Code as shown in figure:
Select Code. You will see field named as "fx:id" at the top. Give any name you like for that particular component. Remember that names must be unique for each component.
Similarly provide fx:id for evey TextField and ImageView. For the Button, fx:id is not compulsory right now. You can leave that field empty but you must provide some value of
on Action field. The value you provide there becomes a method that handles action of the button in controller class.
oh... and i nearly forgot. You must be able to browse for image so that you can insert it into database. For this click on imageview, on the right-hand-side inside
code tab,you can find
on mouse pressed if you scroll down
. Provide some value over there, and it is similar to
on acion given for the button.
Now lets write a code that enables browsing for an image.
@FXML
public void searchImage() {
FileChooser fileChooser = new FileChooser();
//Extention filter remove below two comments for extension filter
FileChooser.ExtensionFilter extentionFilter = new FileChooser.ExtensionFilter("Images (.png, .jpg, .bmp)", "*.jpg", "*.png", "*.bmp");
fileChooser.getExtensionFilters().add(extentionFilter);
//Set to user directory or go to default if cannot access
String userDirectoryString = System.getProperty("user.home");
File userDirectory = new File(userDirectoryString);
if (!userDirectory.canRead()) {
userDirectory = new File("c:/");
}
fileChooser.setInitialDirectory(userDirectory);
//Choose the file
File chosenFile = fileChooser.showOpenDialog(null);
//Make sure a file was selected, if not return default
String path;
if (chosenFile != null) {
path = chosenFile.getPath();
File file = new File(path);
mainfile = file;
//to set image in image view
Image image = new Image(file.toURI().toString());
imgview.setImage(image);
} else {
//default return value
path = null;
}
}
Above code allows us to browse for an image and select particular image. It then sets that image into the imageview. Figure is provided below:
Now, lets get values from the textfields and insert it into database along with user-image at the press of the Insert-button. For this, lets create a method in Database class that handles the query and inserts the values in the database. Code is provided below:
public void runQueryUpdate(String query) throws SQLException {
String queryString = query;
int updatestat = statement.executeUpdate(queryString);
System.out.println(updatestat);
}
Above code is not required for current task but it can be very helpful to insert values in the database later on. Whole code of Database class is provided below:
package insertuser;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
*
* @author muskan
*/
public class Database {
Connection con;
Statement statement;
public void dbConnect() throws ClassNotFoundException, SQLException {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://MUSKAN-PC\\EXPRESS:51206;database=OfficeAssistant;integratedSecurity=true;";
con = DriverManager.getConnection(connectionUrl);
System.out.println("connected");
statement = con.createStatement();
}
public void runQueryUpdate(String query) throws SQLException {
String queryString = query;
int updatestat = statement.executeUpdate(queryString);
System.out.println(updatestat);
}
public Connection getCon() {
return con;
}
}
Now we have everything we need in the Database class. To insert values in the database including image, code is provided below:
@FXML
public void InsertPressed() throws SQLException, FileNotFoundException {
//to insert values
PreparedStatement psmnt = db.getCon().prepareStatement("insert into user_table(username, useraddress, userimage) values(?,?,?)");
FileInputStream fis = new FileInputStream(mainfile);
psmnt.setString(1, namefld.getText());
psmnt.setString(2, addfld.getText());
psmnt.setBinaryStream(3, (InputStream) fis, (int) mainfile.length());
int s = psmnt.executeUpdate();
if (s > 0) {
System.out.println("Uploaded successfully !");
} else {
System.out.println("unsucessfull to upload image.");
}
}
Full code of Controller class is provided below:
package insertuser;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.net.URL;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ResourceBundle;
import java.util.logging.Level;
import java.util.logging.Logger;
import javafx.fxml.FXML;
import javafx.fxml.Initializable;
import javafx.scene.control.TextField;
import javafx.scene.image.Image;
import javafx.scene.image.ImageView;
import javafx.stage.FileChooser;
/**
*
* @author muskan
*/
public class InsertUserController implements Initializable {
Database db = new Database();
@FXML
private ImageView imgview;
@FXML
private TextField namefld;
@FXML
private TextField addfld;
private File mainfile;
@FXML
public void searchImage() {
FileChooser fileChooser = new FileChooser();
//Extention filter remove below two comments for extension filter
FileChooser.ExtensionFilter extentionFilter = new FileChooser.ExtensionFilter("Images (.png, .jpg, .bmp)", "*.jpg", "*.png", "*.bmp");
fileChooser.getExtensionFilters().add(extentionFilter);
//Set to user directory or go to default if cannot access
String userDirectoryString = System.getProperty("user.home");
File userDirectory = new File(userDirectoryString);
if (!userDirectory.canRead()) {
userDirectory = new File("c:/");
}
fileChooser.setInitialDirectory(userDirectory);
//Choose the file
File chosenFile = fileChooser.showOpenDialog(null);
//Make sure a file was selected, if not return default
String path;
if (chosenFile != null) {
path = chosenFile.getPath();
File file = new File(path);
mainfile = file;
//to set image in image view
Image image = new Image(file.toURI().toString());
imgview.setImage(image);
} else {
//default return value
path = null;
}
}
@FXML
public void InsertPressed() throws SQLException, FileNotFoundException {
//to insert values
PreparedStatement psmnt = db.getCon().prepareStatement("insert into user_table(username, useraddress, userimage) values(?,?,?)");
FileInputStream fis = new FileInputStream(mainfile);
psmnt.setString(1, namefld.getText());
psmnt.setString(2, addfld.getText());
psmnt.setBinaryStream(3, (InputStream) fis, (int) mainfile.length());
int s = psmnt.executeUpdate();
if (s > 0) {
System.out.println("Uploaded successfully !");
} else {
System.out.println("unsucessfull to upload image.");
}
}
@Override
public void initialize(URL url, ResourceBundle rb) {
try {
db.dbConnect();
} catch (ClassNotFoundException ex) {
Logger.getLogger(InsertUserController.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
Logger.getLogger(InsertUserController.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
In this way we can insert image into the MS SQL.
Thank you for your time. We will see how to retrieve image from database in another post.
