Week 1
A Connection String uses a set of properties to establish a connection to a database server. It consists of:
Driver (jdbc:)
Connection protocol (mysql://, postgresql://, mariadb://, etc.)
IP Address: a universal resource locator (URL) that identifies a specific host machine on a network.
A port number - a positive integer that identifies a specific server software running on a host machine.
A database resource name
Any other vendor specific properties
You will need to create a connection string for each database schema that you need to connect to.
MYSQL_DRIVER=com.mysql.cj.jdbc.Driver
AZURE_MYSQL_CONNECTION=jdbc:mysql://<DB-SERVER-NAME>.mysql.database.azure.com:3306/<DB-SCHEMA>?serverTimezone=UTC&useSSL=true&requireSSL=false
DB_USER=<DB-USER-NAME>
DB_PASSWORD=<DB-PASSWORD>import io.github.cdimascio.dotenv.Dotenv;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Database {
public static Connection getConnection() {
Dotenv dotenv = Dotenv.load();
String db_full_driver = dotenv.get("DB_FULL_DRIVER");
String connectionString = dotenv.get("LEARNX_DB_CONNECTION_STRING");
String user = dotenv.get("DB_PASSWORD");
String password = dotenv.get("DB_USER");
try {
Class.forName(db_full_driver);
} catch (ClassNotFoundException e) {
// what to do if the driver is not found
}
try {
Connection connection = DriverManager.getConnection(connectionString, user, password);
if (connection.isValid(2)) {
return connection;
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
return null;
}
}The DriverManager class is used to get an instance of a Connection object by using the JDBC driver named in the connectionString URL
The URL syntax for a JDBC driver is:
jdbc:<driver>:[subsubprotocol:][databaseName][;attribute=value]
Each vendor (MySQL, PostgreSQL, etc) can implement its own subprotocol.
To test if the connection is valid we can call the isValid() method on the Connection object. If it's connected to the database, it returns true, otherwise it returns false.
CREATE TABLE user (
user_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(255) NOT NULL DEFAULT '',
last_name VARCHAR(255) NOT NULL DEFAULT '',
email VARCHAR(255) NOT NULL UNIQUE,
phone VARCHAR(255) NOT NULL DEFAULT '',
password VARCHAR(255) NOT NULL DEFAULT '',
language VARCHAR(255) NOT NULL DEFAULT 'en-US',
status enum('inactive', 'active', 'locked') NOT NULL,
privileges enum('subscriber', 'user', 'premium', 'admin') NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);CREATE PROCEDURE sp_add_user(
IN p_email VARCHAR(255),
IN p_password VARCHAR(255)
)
BEGIN
INSERT INTO user (email, password)
VALUES (p_email,p_password);
END;CALL sp_add_user('delete-me@example.com', 'badpassword1');
CALL sp_add_user('delete-me2@example.com', 'badpassword2');SELECT * FROM user;CREATE PROCEDURE sp_get_all_users()
BEGIN
SELECT user_id, first_name, last_name, email, phone, password, language, status, privileges, created_at
FROM user;
END;Generate a default and parameterized constructor, getter and setter methods, and a toString method.
import java.time.Instant;
public class User {
private int user_id;
private String firstName;
private String lastName;
private String email;
private String phone;
private char[] password;
private String language;
private String status;
private String privileges;
private Instant created_at;
}getPassword() method returns a char[] and not a String, because String is an immutable data type. That means String objects cannot be changed after a value has been created on the "String Pool". The String pool will eventually be removed by the "Java Garbage Collection Process". Until then, this value will continue to exist on the "Heap".Therefore, the char[] is used to store the password to help with security. This is because the character array can be cleared as an empty array immediately after using it. It will not be stored in the heap so no unauthorized user can access the heap and retrieve the entered password.
public class UserDAO {
public static List<User> getAll() {
List<User> list = new ArrayList<>();
try (Connection connection = getConnection()) {
} catch (SQLException e) {
throw new RuntimeException(e)
}
return list;
}
}public class UserDAO {
public static List<User> getAll() {
List<User> list = new ArrayList<>();
try (Connection connection = getConnection()) {
try (CallableStatement statement = connection.prepareCall("{CALL sp_get_all_users()}")) {
}
} catch (SQLException e) {
throw new RuntimeException(e)
}
return list;
}
}The Connection object has a prepareCall() method that allows us to create a CallableStatement object.
public class UserDAO {
public static List<User> getAll() {
List<User> list = new ArrayList<>();
try (Connection connection = getConnection()) {
try (CallableStatement statement = connection.prepareCall("{CALL sp_get_all_users()}")) {
try (ResultSet resultSet = statement.executeQuery()) {
}
}
} catch (SQLException e) {
throw new RuntimeException(e)
}
return list;
}
}The ResultSet object represents the database response - the actual data returned by the SQL command.
Using a try-with-resources statement will automatically close each Autocloseable object.
public class UserDAO {
public static List<User> getAll() {
List<User> list = new ArrayList<>();
try (Connection connection = getConnection()) {
try (CallableStatement statement = connection.prepareCall("{CALL sp_get_all_users()}")) {
try (ResultSet resultSet = statement.executeQuery()) {
while (resultSet.next()) {
int userId = resultSet.getInt("user_id");
String firstName = resultSet.getString("first_name");
String lastName = resultSet.getString("last_name");
String email = resultSet.getString("email");
String phone = resultSet.getString("phone");
char[] password = resultSet.getString("password").toCharArray();
String language = resultSet.getString("language");
String status = resultSet.getString("status");
String privileges = resultSet.getString("privileges");
Instant created_at = resultSet.getTimestamp("created_at").toInstant();
User user = new User(userId, firstName, lastName, email, phone, password, language, status, privileges, created_at);
list.add(user);
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e)
}
return list;
}
}ResultSet behaves like a Java Collections iterator in that it allows you to review the contents of each record returned by the query, one record at a time.
The .next() method of the ResultSet class will place a pointer at the first row of the result sets returned by the SQL query.
Since we don't know how many records the query will return, this statement is best used as the condition of a while loop.
JDBC does not auto-generate Objects for each row and is ignorant of the data types in each row. The developer must tell the program which fields and data types you want to extract from each row.
Add a main method to the UserDAO class to test the getAll method. Run the method and it should print the users you have in your database table.
public static void main(String[] args) {
System.out.println(getAll().forEach(System.out::println));
}java.sql.Connection: A connection that represents the session between your Java application and the database
Connection connection = DriverManager.getConnection(url, username, password);
java.sql.Statement: An object used to execute a static SQL statement and return the result.
Statement statement = connection.createStatement();
To execute SQL queries with JDBC, you must create a SQL query wrapper object, an instance of the Statement object.
java.sql.ResultSet: An object representing a database result set.
String query = "SELECT * FROM User";
ResultSet resultSet = statement.executeQuery(query);
Use the Statement instance to execute a SQL query:
A ResultSet object represents the database query response in a table-like structure that holds records returned - the actual data returned by the SQL command. The next() method returns true or false. If true, the resultSet object points to the first row of data.
| Method | Returns | Used for |
|---|---|---|
| executeQuery(sqlString) | ResultSet | SELECT statement |
| executeUpdate(sqlString) | int (rows affected) | INSERT, UPDATE, DELETE, or a DDL (DROP, CREATE, ALTER) |
| execute(sqlString) | boolean (true if there was a ResultSet) | Any SQL command or commands |
A CallableStatement allows non-SQL statements (such as stored procedures) to be executed against the database.
The CallableStatement object encapsulates and execute a stored procedure. The Connection object has a prepareCall() method that allows us to create a CallableStatement object.
Then we use the PreparedStatement's executeQuery() method to send the stored procedure to the database to execute the query.
Again, the ResultSet object represents the database response - the actual data returned by the SQL command.
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
@WebServlet("/users")
public class AdminUsers extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
List<User> users = UserDAO.getAll();
req.setAttribute("users", users);
req.getRequestDispatcher("WEB-INF/ecommerce/users.jsp").forward(req, resp);
}
}Step 5: Create/Update a JSP
Create a JSP in the "WEB-INF/ecommerce/" folder called users.jsp.
Add this HTML. Add a non-breaking space ( ) anywhere a space is needed.
<div class="container">
<div class="row">
<!-- Main content START -->
<div class="col-xl-12">
<!-- Title -->
<h1>All Users</h1>
<p class="lead">There ${users.size() == 1 ? "is" : "are"} ${users.size()} user${users.size() != 1 ? "s" : ""}</p>
<c:if test="${users.size() > 0}">
<div class="table-responsive">
<table class="table table-bordered">
<thead>
<tr>
<th scope="col"></th>
<th scope="col">First name</th>
<th scope="col">Last name</th>
<th scope="col">Email</th>
<th scope="col">Phone</th>
<th scope="col">Language</th>
<th scope="col">Status</th>
<th scope="col">Privileges</th>
<th scope="col">Created At</th>
</tr>
</thead>
<tbody>
<c:forEach items="${users}" var="user">
<tr>
<td>
<a href="edit-user?user_id=${user.userId}" class="btn btn-sm btn-outline-primary">Edit</a>
<a href="delete-user?user_id=${user.userId}" class="btn btn-sm btn-outline-danger">Delete</a>
</td>
<td>${user.firstName}</td>
<td>${user.lastName}</td>
<td>${user.email}</td>
<td>${user.phone}</td>
<td>${user.language}</td>
<td>${user.status}</td>
<td>${user.privileges}</td>
<td>${user.created_at}</td>
</c:forEach>
</tbody>
</table>
</div>
</c:if>
</div> <!-- Col END -->
</div> <!-- Row END -->
</div> <!-- Container END -->