NetBeans Laboratory Manual for Java App Development 2020
Laboratory Manual 5.4
Java Database Programming
A database management system (DBMS) defines, creates and maintains a database. DBMS also allows controlled
access to data in the database. A DBMS is a combination of five components; hardware, software, data, users and
procedures.
Networked RDBMS
– Oracle
• For large mission critical systems runs on Linux
– MS SQL Server
• Used in small to medium sized systems run in Windows server
– DB2
• For large mission critical systems runs on IBM OS
– MySQL
• Open source, runs on major operating system, commonly used in web application
– Apache Derby/Java DB
• New java based relation database, can be embedded in application
– …
Embedded RDBMS
– Apache Derby/Java DB
– MS Access
– SQLite
– Compact version of MS SQL, Oracle, …
5.4 Connecting Java Application to MS SQL Database System
Microsoft SQL server is one of the market leaders for database technology. It’s a relational database management
system that supports a number of applications, including business intelligence, transaction processing and
analytics. Microsoft SQL Server is built on SQL, which is a programming language used to manage databases and
query data.
Microsoft JDBC Driver for SQL Server
The Microsoft JDBC Driver for SQL Server is a Type 4 JDBC driver that provides database connectivity through
the standard JDBC application program interfaces (APIs) available on the Java platform. The driver downloads
are available to all users at no additional charge. They provide access to SQL Server from any Java application,
application server, or Java-enabled applet.
Download it from [Link]
5.4.1 Design and Create Database system for your application
1. Study the requirement and design database
2. Create database and database objects, use appropriate tool to design and develop database for your
application; such as
a. MS SQL Management Studio
b. NetBeans IDE
c. MS SQL DOS Command
d. Visual Studio
e. etc.
DBU, Department of Computer Science Page 1 of 10
NetBeans Laboratory Manual for Java App Development 2020
Connecting to MS SQL from MS SQL Management Studio
1. start SQL Management Studio
2. Set username and password to login to SQL database server
Fig : Connecting to MySQL server from Workbench
3. Create database and database objects
Fig : SQL Management Studio working area
DBU, Department of Computer Science Page 2 of 10
NetBeans Laboratory Manual for Java App Development 2020
Connecting to MS SQL from NetBeans IDE
1. Open NetBeans IDE and Go to the “Service” tab of project explorer window
Fig : NetBeans service tab
2. Right click on “Database” => R. Click “Driver …” , Click “New Driver”
Fig : Add SQL Server jdbc driver to NetBeans IDE
DBU, Department of Computer Science Page 3 of 10
NetBeans Laboratory Manual for Java App Development 2020
3. Right click on MS SQL Connection Driver , click “Connect Using” and fill the MS SQL Connection form
Fig : starting SQL server from NetBeans IDE
4. Expand the connection string and Create tables and other objects for your database using either the GUI
designer or Code editor(Refer Lab Manual 5.1 on how to create table using NetBeans IDE )
Fig : creating database objects
DBU, Department of Computer Science Page 4 of 10
NetBeans Laboratory Manual for Java App Development 2020
Note: Create Java Project
1. Create new project (use the existing project we created in the previous class “RegistrarSystemApp”)
2. Design the user interface and add the required events for the controls
3. Add MySQL java connector/j to your project class path
a. Download mssql-jdbc-8.4.1.jre11 from [Link]
b. Add the jar file to your project class path (Refer Lab Manual 5.1 on how to add jar file to
NetBeans Project Library path)
5.4.2 Connect your Application to your database
1. Create a database connection class “DbConnection” class
2. Implement a method called Connection getConnection() static method, that
a. Load driver
b. Get connected to the database system and
c. Return connection object
import [Link];
import [Link];
import [Link];
public class DbConnection {
public static Connection getConnection() throws SQLException, ClassNotFoundException {
[Link]("[Link] ");
String dbUrl = " jdbc:sqlserver://localhost\\sqlexpress;databaseName=registrardb ";
String dbUser = "sa";
String dbPass = "1234";
return [Link](dbUrl, dbUser, dbPass);
}
}
3. Add an event handler/application code for the form “NewFaculty” form save button click event
Fig : New Faculty registration form
DBU, Department of Computer Science Page 5 of 10
NetBeans Laboratory Manual for Java App Development 2020
4. Insert you first data to your database
Write the following code to save button action performed event
private void btnSaveActionPerformed([Link] evt) {
Connection conn = null;
PreparedStatement pst = null;
try {
String sql = "INSERT INTO Faculty(Name, Phone, Fax, Building, OfficeNumber, Desc)
Values(?,?, ?, ?, ?, ?)";
int aRow;
conn = [Link]();
pst = [Link](sql);
[Link](1, [Link]());
[Link](2, [Link]());
[Link](3, [Link]());
[Link](4, [Link]());
[Link](5, [Link]());
[Link](6, [Link]());
aRow = [Link]();
if (aRow != 0) {
[Link](this, "Faculty details saved Successfully.");
[Link]("");
[Link]("");
[Link]();
}
} catch (SQLException | ClassNotFoundException | HeadlessException e) {
[Link](this, "Database server error" + [Link]());
} finally {
// it is a good idea to release resources in a finally{} block in reverse-order of their creation
// if they are no-longer needed
if (pst != null) {
try {
[Link]();
} catch (SQLException sqlEx) { } // ignore
pst = null;
}
if (conn != null) {
try {
[Link]();
} catch (SQLException sqlEx) { } // ignore
conn = null;
}
}
}
}
************* Run your application and test your code functionality. *****************
DBU, Department of Computer Science Page 6 of 10
NetBeans Laboratory Manual for Java App Development 2020
5.4.3 Retrieving data from database
1. Add a form “FacultyList” to your project , design the user interface
2. Ad application code to form window opened event, which retrieves the data from the database and
display to the user
Fig : Faculty list form
private void formWindowOpened([Link] evt) {
Connection conn = null;
Statement stmt = null;
ResultSet rst = null;
DefaultTableModel facultyTblModel = (DefaultTableModel)[Link]();
[Link](0);
String sql = "Select * From Faculty order by FacultyName ASC";
try {
conn = [Link]();
stmt = [Link]();
rst = [Link](sql);
int row = 0;
while([Link]()){
[Link](new Object[]{[Link]("FacultyName"),
[Link]("FacultyPhone"), [Link]("FacultyFax"), [Link]("FacultyBuilding"),
[Link]("FacultyOfficeNumber")});
}
} catch (SQLException | ClassNotFoundException e) {
[Link](this, "Database server error" + [Link]());
} finally {
if (rst != null) {
DBU, Department of Computer Science Page 7 of 10
NetBeans Laboratory Manual for Java App Development 2020
try {
[Link]();
} catch (SQLException sqlEx) {
}
rst = null;
}
if (stmt != null) {
try {
[Link]();
} catch (SQLException sqlEx) {
}
stmt = null;
}
if (conn != null) {
try {
[Link]();
} catch (SQLException sqlEx) {
}
conn = null;
}
}
}
3. Run your program and test its functionality
Fig: list of registered faculties
DBU, Department of Computer Science Page 8 of 10
NetBeans Laboratory Manual for Java App Development 2020
Exercise: Complete the CRUD operation functionalities “Update” and “Delete”
1. Add a button control “Update” and “Delete” as given below
2. Write a code for each button control to perform the required operation.
DBU, Department of Computer Science Page 9 of 10
NetBeans Laboratory Manual for Java App Development 2020
DBU, Department of Computer Science Page 10 of 10