Wednesday, February 26, 2014

MySQL Database and Java Desktop GUI Application Development

Contents:

  1. Machine specification used for this task

  2. Pre-requirement

  3. Creating a Database

  4. Making a Connection to a Database

  5. Creating a Table

  6. Inserting a Sample Data

  7. Creating a New Java Desktop Project

  8. Running the Project for the First Time

  9. Customizing the GUI

  10. Re-run the Project and Testing Its Functionality

In this series of three parts, we will learn how to build Java desktop GUI application that connected to MySQL database. This tutorial is quite long containing a lot of screen snapshots to make it as details as possible. The original tutorial can be found at netbeans.org. All credits must go to the original authors.
Machine specification used for this task
  • Intel Pentium Core 2 Duo, 2.2 GHz,
  • Windows XP Pro SP2 + periodical patches + periodical updates.
  • 2 GB DDR2 RAM
  • 160 GB SATA HDD
  • 17” SyncMaster 713N monitor.
Pre-requirement: NetBeans 6.x.x and MySQL 5.x.x

Creating a Database


Firstly let create a database using MySQL Command Line Client console. We cannot find how to create a MySQL database using NetBeans. However there are wizard to create Java database for Derby database. After finishing the database creation, exit and open NetBeans.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


SQL statement to create database is:


CREATE DATABASE tid5013studentrecord;


Change the database name accordingly if you want.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


When the NetBeans IDE launched, go to the Services pane, expand the Databases folder and expand the Drivers sub-folder.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Making a Connection to a Database


Well, NetBeans 6.0 provides drivers for JDBC-ODBC (Windows driver for Microsoft Access/MSSQL), MySQL, Java and PostgreSQL database. This means that no need for us to install third party driver for these databases as version 5.5. Thanks to NetBEans and in the future there will be more drivers for other dominant databases used in the market such as Oracle, Sybase etc. A new driver can be added to NetBeans using the following steps.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


And fill in the needed information in the following Figure. Get the third party JDBC driver for various databases at sun.com.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Next, select MySQL (Connector/J driver) and right-click mouse button. Select Connect Using… context menu.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


The New Database Connection wizard launched. Use the following connection string. Key in the MySQL username as root and its password that you use to access your MySQL database.


jdbc:mysql://localhost:3306/tid5013studentrecord


The format for the connection string for this driver is:


jdbc:mysql://<hostname>:<database_access_port_number>/<database_name>


Change the database name accordingly if needed. Click the OK button.


Using root user is not a good practice. We need to create MySQL users and assign appropriate level of permissions and rights to those users and use them in different level of database accesses such as users for update, backup and many more. Left the root as the highest administrator similar to Windows (Administrator) and Linux (root) system access. Click the Remember password tick box if you want the password to be remembered so next time no need to key-in the password any more. Click OK.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


If your connection is successful it will be notified (Connection established) in the following frame and same as if it is fail. Click the OK button.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Now we are connected to the MySQL database through NetBeans. You can see icon for the established connection as shown below.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Creating a Table


Next step is to create table and then populate it with sample data. The following table describes our sample table structure. (The red record has some issue to be resolved later. There are some incompatibilities between java.util.date and java.sql.date here. Quick solution is to use int or String type for the stud_date_of_birth or we can separate the year, month and day into different fields/columns).


The structure for a studentrecord table
Column name
Data type
Constraints
student_id
VARCHAR(7)
NOT NULL PRIMARY KEY
stud_first_name
VARCHAR(15)
NOT NULL
stud_last_name
VARCHAR(15)
NOT NULL
stud_date_of_birth
DATE
NOT NULL
stud_address
VARCHAR(50)
NOT NULL
stud_program
VARCHAR(20)
NOT NULL
stud_marital_status
INT(2)
NOT NULL
stud_country
VARCHAR(20)
NOT NULL


And the following is the SQL script for our table creation.


CREATE TABLE IF NOT EXISTS studentrecord (

student_id VARCHAR(7),

stud_first_name VARCHAR(15) NOT NULL,

stud_last_name VARCHAR(15) NOT NULL,

stud_date_of_birth DATE NOT NULL,

stud_address VARCHAR(50) NOT NULL,

stud_program VARCHAR(20) NOT NULL,

stud_marital_status INT(2) NOT NULL,

stud_country VARCHAR(20) NOT NULL,

PRIMARY KEY (student_id)

) ENGINE=innodb;


To execute this SQL script, select the previously established connection (our MySQL database), right-click mouse and select Execute Command… context menu.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Type or copy-paste the SQL script into the SQL Command editor as shown below. Make sure you have chosen a proper database as blue highlighted in the Connection: field


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Then, run/execute the script by clicking the run/execute icon.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Verify the studentrecord database creation by using the following command in new SQL Command editor. You can also combine and execute/run this code together with the previous SQL script.


DESC studentrecord;


The following figure shows that our table has been successfully created. Congrats!!!

Next step is to populate the studentrecord with sample data.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Inserting a Sample Data


Use the following sample SQL scrip to insert sample data into out table. You can try creating your own sample data. Launch and use new SQL Command editor or you can overwrite the previous SQL script. Execute/run the script as done previously.

INSERT INTO studentrecord VALUES(
'88889','Albukori','Zaman Khan','1969-07-08',
'4-5, Dead Wood Street 5, 12000 Sintok, Kedah','MSc. IT',
'1','Malaysia');
INSERT INTO studentrecord VALUES(
'87990','Haslina','Mahathir','1970-11-12',
'345, New Smart Village, 17100 Nilai, N. Sembilan','MSc. ICT',
'2','Malaysia');
INSERT INTO studentrecord VALUES(
'79678','Mohammed','Fajr','1975-04-20',
'Pearl Apt, Level 10, Al-Hijr, 45200 Abu Dhabi','MSc. Expert System',
'2','UEA');
INSERT INTO studentrecord VALUES(
'88799','Mustar','Mohd Dali','1979-06-24',
'345, Side Village, Kerian, 12300 Jawa Barat','MSc. MultiMedia',
'1','Indonesia');
INSERT INTO studentrecord VALUES(
'78998','Satkorn','Chengmo','1968-01-26',
'34 Uptown Street #4, Tech Park, 78100 Bangkok','MSc. IT',
'2','Thailand');


Any success or fail will be notified in the Output window at the bottom of the NetBeans IDE. So don’t worry. A screen snapshot is shown below. Notice the different default colors used in the SQL script. Keywords, values and table name are in different colors and together with the report in the Output window, this makes our tasks in troubleshooting easier.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Verify our data/values insertion by using the following SQL statement.


SELECT * FROM studentrecord;


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


A complete MySQL script for this exercise is given below.

-- create a table
CREATE TABLE IF NOT EXISTS studentrecord (
student_id VARCHAR(7),
stud_first_name VARCHAR(15) NOT NULL,
stud_last_name VARCHAR(15) NOT NULL,
stud_date_of_birth DATE NOT NULL,
stud_address VARCHAR(50) NOT NULL,
stud_program VARCHAR(20) NOT NULL,
stud_marital_status INT(2) NOT NULL,
stud_country VARCHAR(20) NOT NULL,
PRIMARY KEY (student_id)
) ENGINE=innodb;
-- insert a sample data
INSERT INTO studentrecord VALUES(
'88889','Albukori','Zaman Khan','1969-07-08',
'4-5, Dead Wood Street 5, 12000 Sintok, Kedah','MSc. IT',
'1','Malaysia');
INSERT INTO studentrecord VALUES(
'87990','Haslina','Mahathir','1970-11-12',
'345, New Smart Village, 17100 Nilai, N. Sembilan','MSc. ICT',
'2','Malaysia');
INSERT INTO studentrecord VALUES(
'79678','Mohammed','Fajr','1975-04-20',
'Pearl Apt, Level 10, Al-Hijr, 45200 Abu Dhabi','MSc. Expert System',
'2','UEA');
INSERT INTO studentrecord VALUES(
'88799','Mustar','Mohd Dali','1979-06-24',
'345, Side Village, Kerian, 12300 Jawa Barat','MSc. MultiMedia',
'1','Indonesia');
INSERT INTO studentrecord VALUES(
'78998','Satkorn','Chengmo','1968-01-26',
'34 Uptown Street #4, Tech Park, 78100 Bangkok','MSc. IT',
'2','Thailand');

Next step is to create a GUI for our database access and manipulation. The following screen snapshots are self-explanatory.


Creating a New Java Desktop Project


Well, click File > select New Project.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Select Java in the Categories: and Java Desktop Application in the Projects: pane. Click Next.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Put the project name as StudentRecordApp and change the location if needed else just leave as it is. Select the Database Application in the Choose Application Shell pane. Click Next.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Select our previously created database connection in the Database Connection text field. We only have one table here, so no need to select any as shown in the Database Table: field. In this case we include all the table columns. You can exclude some columns if needed. Click Next button.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


By default the first radio button has been selected. Just leave as it is. Click Finish button and wait.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Here you are! Our database GUI template! This GUI can be built and run/executed as other Java project.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


You can view the GUI by clicking the Preview Design icon ().


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Running the Project for the First Time


Next let see our real GUI in action. You can build first and then run. In this case we directly run the project (in the process it will be built as other Java project). Select the StudentRecordApp project folder, right-click mouse and select Run menu.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Here is the Real GUI. Try the File menu and its sub-menu. In this case not all the record was displayed. Only the first three and this is our job to find the cause and it is a normal process in programming! Select any record and test the New (click New button and key-in new data and click Save to update new data), Delete (select a record to be deleted and press the Delete button) and Refresh (refresh the connection and update the data) buttons. All controls supposed to be working else we need to find the reasons and resolve it.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Customizing the GUI


Close this application. Let go back to the design page. Select the Stud Date Of Birth text field. Go to the Properties sheet and click the Binding.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Click the ellipses (…) at the end of the text field.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Try selecting the date int type in the Binding Expression: field, expand the studDateOfBirth java.util.Date and select date int and then click OK.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Re-run the Project and Testing Its Functionality


Re-run this project again.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Select any row of the table. All the record displayed but the Stud Date Of Birth just display the day only. However this program can be said 97% working.

If we want to add other component either to bind to the database table or not it is depend on our creativities now.










-----------------------------------------------------------------------------------------------------------------

Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Close the application and back to the design. Select, drag and drop the position of the table grid to the bottom of the frame as shown below.


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Re-run this project again and see the result!


Step-by-step on Java desktop GUI application development and MySQL screen snapshots


Regarding the Date Of Birth (DOB), well we think here is the reason. Searching in the Internet, the reason is the java.util.Date is not compatible with the java.sql.Date. Older java.util.Date package that contains date only already deprecated. The new version of this java.util.date contains date and time as well, while the java.sql.util only contains the date. Coding wise, we need to parse or convert to the java.sql.Date format. The validation and conversion features already available in the Java Server Faces (JSF) web development.


As said before we can fix this problem immediately. Easy solution is to use a String type for the stud_date_of_birth replacing the DATE data type. From this simple issue, that is why the database design stage is very important.


The structure for a studentrecord table
Column name
Data type
Constraints
student_id
VARCHAR(7)
NOT NULL PRIMARY KEY
stud_first_name
VARCHAR(15)
NOT NULL
stud_last_name
VARCHAR(15)
NOT NULL
stud_date_of_birth
VARCHAR(10)
NOT NULL
stud_address
VARCHAR(50)
NOT NULL
stud_program
VARCHAR(20)
NOT NULL
stud_marital_status
INT(2)
NOT NULL
stud_country
VARCHAR(20)
NOT NULL


Or we can separate the year, month and day into different fields/columns. (We have tried this, also failed).


stud_last_name
VARCHAR(15)
NOT NULL
stud_dob_day
INT(2)
NOT NULL
stud_dob_month
INT(2)
NOT NULL
stud_dob_year
INT(4)
NOT NULL
stud_address
VARCHAR(50)
NOT NULL


And for this case we need to create (I mean, NetBeans create for us and we re-arrange it in the frame) different fields for each column and we need to re-arrange the day, month and year in the standard format in the GUI such as:

Step-by-step on Java desktop GUI application development and MySQL screen snapshots

You can try both solution and let see the outputs however we will solve this issue in the next tutorial, replacing the DATA type to String and we will show the steps on how to edit a table while designing the GUI interface.
 
 
 


Individual / Group / Online classes in English / Sinhala / Tamil. Sample Projects/Assignments Exam Papers, Tutorials, Notes and Answers will be provided.

 

Call +94 777 33 7279 

eMail itclasssl@gmail.com 

Skype ITClassSL


 
 

Wednesday, February 12, 2014

Unit 3 The Knowledge Worker - London Edxcel GCE [Applied ICT | Applied GCE]


3.1 Problem Solving


What shall I wear today? Which programme shall we watch? Many of the
problems we encounter and the decisions we make are personal and have
little effect on others. However, as an advanced ICT user and knowledge
worker, you will be faced with making decisions that have a direct, often
wide-ranging, impact on other individuals, groups or the organisation as a whole.

Once a problem is defined, specific information is required to make
decisions on how best to solve it. You will learn to process the information
you have available to create new information, thereby increasing your
knowledge of the situation.

Often you will find yourself faced with an enormous amount of potentially
useful information and at times with information overload — far too much
information available so that you cannot easily find the knowledge that
you need. You will need to discard irrelevant information so that you are left only with information that you can process. Most importantly, you must remember that technology can only provide you with potentially useful information — it does not create knowledge for you.




3.2 The Decision-Making Process


Informed decision making is a systematic process which takes into account all the valid information available.

As a knowledge worker faced with making a decision you will need to:

  • make sure that you fully understand the situation
  • search for information related to the problem
  • establish what sources of information there are and how reliable they are
  • identify gaps in your knowledge that cannot be filled
  • find out if there are any other factors which need to be considered, including constraints
  • select the information you will use
  • analyse the information
  • identify alternatives
  • make the decision
  • justify the decision
  • explain it to others

You will need to consolidate your understanding of this process by
considering examples, such as the problem of selecting the appropriate
season ticket for a business commuter. There are a number of factors to
take into account including the types of ticket available, the costs, the
availability, the number of journeys, time of day, holidays, etc.




3.3 Understanding the Situation


In order to stand a realistic chance of making the right decision, it is
essential to think things through.

  • What exactly do you have to decide?
  • Are there different viewpoints?
  • How does this decision compare with similar decisions you may have had to make already?
  • Are there variations from time to time or place to place?
  • How long have you got to decide?
  • What resources are at your disposal?




3.4 Sources of Information


When trying to make a decision you need to first establish what you know
and what you need to find out. You need to identify all relevant sources of
information and make judgements about their accuracy and usefulness.

You will need to ask yourself the following questions:

  • What do I need to know?
  • What relevant knowledge do I already have?
  • What are the gaps in my knowledge and can they be filled?
  • What information do I already have access to?
  • Where will any additional information come from?
  • How will I evaluate sources of information to ensure that content is reliable?




3.5 Other Factors to Consider


This is where your qualities as a knowledge worker are really put to the
test, as the best decisions take all available knowledge into account. Once
you have assessed the usefulness of information, you must consider other factors that might influence the decision, such as:

  • gut feeling — ‘I just know that this is the right thing to do’
  • emotion — ‘I will never forgive myself if I make a risky decision’
  • sentiment — ‘what will the others think?’
  • ambition — ‘how will this affect my career?’
  • lack of knowledge — are the gaps significant?




3.6 Making a Decision


You should at this stage have gained as much information as possible about
the situation. You now need to maximise your knowledge by analysing the
information and by testing out alternative solutions.

There are many ways of manipulating information to help you make
decisions but one of the most useful is that of modelling.
Spreadsheet models are powerful aids to decision making. A wellconstructed
model will capture the main features of a situation without getting bogged down with unimportant details. It will allow you to explore alternatives and predict behaviour under different conditions. The results of using the model, combined with all the other knowledge you have relating to the situation, should allow you to make a decision or recommendations for the future.




3.7 Computer Modelling


You will need to practise using spreadsheet models to help you make
decisions. But, bear in mind that the decisions you make are only ever as
good as the model upon which they are based! Before putting your trust in
a model — whether created by you or somebody else — you need to check
that it is correct.

  • Is the logic of the model correct?
  • Are the data formats appropriate?
  • Is the syntax of the formulae correct?
  • Are the cell references correct?

Before you begin to design and construct spreadsheet models of your own,
you will need to try out and evaluate a range of existing models. When
evaluating each model you should determine:

  • the process/scenario being modelled
  • what it does
  • how well it does it
  • whether it could be improved
  • which variables can be input
  • what the output tells you
  • the decisions you could make using it




3.8 Using a Model to Consider Alternatives


Once you are sure that a model is working correctly you can use it to see
the effects of various courses of action. For example, the model may
indicate the most cost-effective solution, but that is unlikely to be the
only consideration. You will need to decide:

  • which decision produces the best results
  • the alternatives
  • factors that differentiate between them
  • anything that the model does not take into account
  • what the impact of these might be




3.9 Justifying the Decision


Having considered both the results of your model and other factors, you
will need to use your knowledge to make a decision. As a knowledge
worker your task is not only to make recommendations based on the
information you have selected or derived, but to justify your decisions to
others. As you go through the decision making process you should record
your progress so that relevant documentation is available when you come
to present your report.




3.10 Reporting it to others


Your recommendations should include:

  • a summary of the current situation
  • sources of information and alternatives you considered
  • other factors you took into consideration
  • the methods you used to reach your decision
  • your decision
  • justification of your decision, supported by evidence of the decisionmaking
process




3.11 Evaluating a Model


You will need to be able to evaluate models. You will need to crossreference
your method of solution against the original objectives in
considering the following.

  • How well has the model performed?
  • To what extent has the model helped you to make the decision?
  • What else would you like to do?
  • Does the model need extending and, if so, how?




3.12 ICT Skills


You must be able to use a range of ICT tools and techniques to:
• carry out spreadsheet modelling tasks, including: —

  • entering and editing data, eg absolute and relative cell referencing, adding data and text to a chart
  • formatting data, eg colours, shading and borders, headers and footers
  • using formulae and function, eg mathematical, statistical, financial and relational
  • validating and checking data, eg errors in formulae, accuracy of results
  • analysing and interpreting data, eg filters, subtotals
  • presenting information, eg graphs and charts
  • modifying spreadsheet models to take account of requirements

• produce word processed documents that communicate effectively and
impart information to an audience, including: —

  • importing data from other applications, eg adding a spreadsheet graph/chart to a word processing document
  • formatting documents
  • creating document layouts, eg tables and columns
  • checking documents

• produce presentations that communicate effectively and impart
information to an audience, including: -

  • creating and editing presentations
  • formatting slides
  • inserting text, pictures and charts into presentations
  • importing data from other applications, eg adding a spreadsheet graph/chart to a presentation
  • checking presentations




3.13 Standard Ways of Working


Whilst working on this unit, you will be expected to use ICT efficiently,
legally and safely. You must adhere to standard ways of working,
including:

• file management
  • saving work regularly
  • using sensible filenames
  • setting up directory/folder structures to organise files
  • making backups
  • choosing appropriate file formats
  • limiting access to confidential or sensitive files
  • using effective virus protection
  • using ‘readme’ files where appropriate to provide technical information, eg system requirements

• personal effectiveness
  • selecting appropriate ICT tools and techniques
  • customising settings
  • creating and using shortcuts
  • using available sources of help
  • using a plan to help you organise your work and meet deadlines

• quality assurance
  • using spell check, grammar check and print preview
  • proofreading
  • seeking views of others
  • authenticating work

• legislation and codes of practice
  • acknowledging sources
  • respecting copyright
  • avoiding plagiarism
  • protecting confidentiality

Source http://chauncy-ict.pbworks.com/w/page/9660296/Unit%203

 Individual / Group / Online classes in English / Sinhala / Tamil. Sample Projects/Assignments Exam Papers, Tutorials, Notes and Answers will we provided.
 Call +94 777 33 7279 | eMail itclasssl@gmail.com | Skype ITClassSL

Civilisation The First Farmers Agriculture | Origins of agriculture | Gr...

Organic Fertilizer Production Line Price The Birth of Civilisation - The First Farmers (20000 BC to 8800 BC) The Histocrat In the first of a...