Lab6 Guide

LAB 6
Connect from SQL Server Database to Java Application (Manipulate)
I. Prerequisites
- Same as Lab 5 prerequisites
- Your Lab 5 IntelliJ IDEA’s project folder
II. Lab Setup & Guide
1. Preventing SQL Injection – Parameterized Query Approach.
- Recall the code for using Java to perform a query operation to the SQL Database.
public static void showQuery(String txtQuery) {
Connection con = null;
PreparedStatement stmt;
ResultSet rs;
try {
con = DriverManager.getConnection(connectionUrl);
stmt = con.prepareStatement(txtQuery);
rs = stmt.executeQuery();
- Examine these questions again and answer them to familiarize yourself with the above
code
1. What is a Connection, PreparedStatement, and ResultSet variable type? Find out
some of the methods they provided.
2. The method executeQuery() is used to query the database. Find the corresponding
method in PreparedStatement to perform the manipulation operation to the database
engine.
3. What if a user inputs a malicious SQL Query inside an input box? Could you explain
some of the consequences of this action?
- Luckily, experts have developed many mitigations to prevent the above attack (SQL
Injection). One is the so-called Parameterized Query, as the query text is parameterized
and only accepts user input after it validates them as safe. Here is an example code of
using parameterized query in Java code.
public static String showQuery(String txtCond) {
ResultSet rs;
String result = "";
try {
String preparedQuery =
"""
SELECT *
FROM Student
WHERE StudentID = ?""";
stmt = con.prepareStatement(preparedQuery);
stmt.setString(1, txtCond);
rs = stmt.executeQuery();
while (rs.next()) {
result = rs.getString("StudentName");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
closeConnect(con);
}
return result;
}
- Notice the use of preparedQuery with the SQL Query and the? mark to replace the user
input. From then on, we can use stmt.setString() for Java to validate the input and
complete the query. This way, the malicious code cannot be executed and produce bad
consequences to the database.
2. Set up the helper ConnectSQL.java class.
- Below the previous methods from this class, copy and replace the <?> with your answer
to complete the following code and perform the tasks below.
- This is an Insert operation code to let Java send the query to our database engine.
public static boolean insertData(<?>) {
int rs;
boolean isUpdated = false;
try {
con.setAutoCommit(<?>);
String insertString = <?>;
stmt = con.<?>(insertString);
stmt.<?>(1, <?>);
rs = stmt.<?>();
if (<?>) {
isUpdated = true;
}
con.commit();
return isUpdated;
} finally {
closeConnect(con);
}
return <?>;
}
- Use these questions to guide you when completing the code.

1. What should we put inside the parameter of this method?
2. Examine the setAutoCommit() behavior and complete the right code for it.
3. The insertString is the query you write to operate. Write your own SQL code with
the appropriate? to form a parameterized query.
4. Then, complete the Connection and PreparedStatement methods to invoke the
parameter from the above function.
5. Complete the condition for the ResultSet and return the needed value for future tasks
to follow.
- This Delete operation code lets Java send the query to our database engine.
public static boolean deleteData(<?>) {
int rs;
boolean isUpdated = false;
try {
con.setAutoCommit(<?>);
String deleteString = <?>;
stmt = con.<?>(deleteString);
stmt.<?>(1, <?>);
rs = stmt.<?>();
if (<?>) {
isUpdated = true;
}
con.commit();
return isUpdated;
} finally {
closeConnect(con);
}
return <?>;
}
- Use these questions to guide you when completing the code.

1. What should we put inside the parameter of this method?
2. Examine the setAutoCommit() behavior and complete the right code for it.
3. The deleteString is the query you write to operate. Write your own SQL code with
the appropriate? to form a parameterized query.
4. Then, complete the Connection and PreparedStatement methods to invoke the
parameter from the above function.
5. Complete the condition for the ResultSet and return the needed value for future tasks
to follow.
- Verify all your code and make sure it is error-free, then proceed to the next step. It would
help if you had each function for each operation defined in the Task below.
3. Set up the frmManage.java class.
- Refer to this step in Lab 5 and code yourself for this class task. Here is an example with a
missing <?> field for you to fill in.
<?>.addActionListener(
e -> {
if (txtStudent.getText().<?>) {
JOptionPane.showMessageDialog(
null, "Student's name is empty!", "Warning",
JOptionPane.WARNING_MESSAGE);
return;
}
String <?> = txtStudent.getText();
int option =
JOptionPane.showConfirmDialog(
null,
<?>,
"Confirmation",
JOptionPane.YES_NO_OPTION,
JOptionPane.QUESTION_MESSAGE);
if (option == JOptionPane.YES_OPTION) {
<?>.setEnabled(<?>);
SwingWorker<Void, Void> worker =
new SwingWorker<>() {
@Override
protected Void doInBackground() {
if (ConnectSQL.<?>) {
<?>.setText(<?>);
null,
<?>,
"Success",
JOptionPane.INFORMATION_MESSAGE);
} else {
null,
<?>,
"Warning",
JOptionPane.WARNING_MESSAGE);
<?>.setText(<?>);
}
return null;
}
@Override
protected void done() {
<?>.setEnabled(true);
}
};
worker.execute();
}
});
- Here are some requirements and questions for you to follow:

1. What should be named as the title for this frame?
2. What is the size of this frame window?
3. On close operation, what action should be taken from the WindowConstants class?
4. Implement the button event listener using the SwingWorker design pattern.
5. Have the input validation check and provide JOptionPane context to confirm user
input, error logging, and operation success.
6. When SQL is busy executing the query, the button must be disabled.
7. After adding the information, their file must be cleared.
8. Have the getInstance() method to be used in the Main.java class.
4. Set up the Main.java class.
- With the instruction from the above step, create, copy, and complete the code for this
class by replacing the <?> part with your answer.
public class Main {
public static void main(String[] args) {
<?>.getInstance().setVisible(<?>);
}
}
III. Lab Task

1. Using the guide from Lab 5, design and replicate the GUI form as in the Figure
below and name it frmManage.
- Hint: The component tree should look like this, with the name following your choice.
2. Complete the Java code to build the application. Then, test the database connection
and the application code.
3. Perform these tasks by writing your query and showing the result, given the ERD of
this database.
- Insert a new student record by their name into the database.
I add new student “ Kim Tuyen Huynh” into the database
- Delete all attendance records for a specific student’s name.

I choose Jane Smith to delete
After deleted successfully, we can check by write this query:
SELECT DISTINCT s.StudentName
FROM Attendance a
INNER JOIN Student s ON a.StuID = s.StudentID;
And get that notifycation
- Insert a new subject and assign it to existing teachers by name.

- Insert a new lesson by date and associate it with a specific teacher and subject by name.
(Hint: you may use the DECLARE keywords to help when doing the query or use the
strategy to query the ID from the name and use it as input for your query function).

Lab6 Guide

Uploaded by

Copyright:

Available Formats

Lab6 Guide

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Lab6 Guide

Uploaded by

Copyright:

Available Formats

LAB 6

Connect from SQL Server Database to Java Application (Manipulate)

- Use these questions to guide you when completing the code.

- Use these questions to guide you when completing the code.

- Here are some requirements and questions for you to follow:

III. Lab Task

- Delete all attendance records for a specific student’s name.

And get that notifycation

- Insert a new subject and assign it to existing teachers by name.

You might also like