# Notes

# JDBC

# Using PreparedStatement

PreparedStatement is a subclass of Statement that allows you to pass arguments to a precompiled SQL statement.

double value = 100_000.00;

String query = "SELECT * FROM Employee WHERE Salary > ?"; // Parameter for substitution

PreparedStatement pStmt = con.prepareStatement(query);

pStmt.setDouble(1, value); // Substitutes value for the first parameter in the prepared statement.

ResultSet rs = pStmt.executeQuery();

• In this code fragment, a prepared statement returns all columns of all rows whose salary is greater than $100,000.
• PreparedStatement is useful when you have a SQL statements that you are going to execute multiple times.

# Situation

• You are repeatedly executing query or update where format stays consistent, but values change
  您正在重复执行查询或更新，格式保持一致，但值改变的地方
• You can make a parameterized query or update, then pass in values for the placeholders
  您可以进行参数化查询或更新，然后传递占位符的值

# Advantages

• More convenient than string concatenation
  比字符串连接更方便
• Significantly faster with most drivers and databases
  在大多数驱动程序和数据库下，速度显著提高
• If values contain user data, much less susceptible to SQL injection attacks
  如果值为用户输入，则更不容易受到 SQL 注入攻击

# Using CallableStatement

A CallableStatement allows non-SQL statements (such as stored procedures) to be executed against the database.

CallableStatement cStmt = con.prepareCall("{CALL EmplAgeCount (?, ?)}");

int age = 50;

cStmt.setInt (1, age); // The IN parameter is passed in to the stored procedure.

ResultSet rs = cStmt.executeQuery();

cStmt.registerOutParameter(2, Types.INTEGER);

boolean result = cStmt.execute();

int count = cStmt.getInt(2); // The OUT parameter is returned from the stored procedure.

System.out.println("There are " + count +

                    " Employees over the age of " + age);

• Stored procedures are executed on the database.

# Transaction 事务

# What is a Transaction?

• A transaction is a mechanism to handle groups of operations as though they were one.
  事务是一种处理一组操作的机制。

• Either all operations in a transaction occur or none occur at all.
  事务中的所有操作都发生了，或者根本没有发生。

• The operations involved in a transaction might rely on one or more databases.
  事务中涉及的操作可能依赖于一个或多个数据库。

# ACID Properties of a Transaction 事务的 ACID 属性

A transaction is formally defined by the set of properties that is known by the acronym ACID.
事务由 ACID 属性集定义。

Atomicity 原子性

A transaction is done or undone completely. In the event of a failure, all operations and procedures are undone, and all data rolls back to its previous state.
事务全部完成或全部撤消。发生故障时，所有操作和过程都将被撤消，并且所有数据都会回滚到以前的状态。

Consistency 一致性

A transaction transforms a system from one consistent state to another consistent state.
事务将系统从一个一致状态转换为另一个一致状态。

Isolation 隔离性

Each transaction occurs independently of other transactions that occur at the same time.
同时发生的事务相互独立。

Durability 耐久性

Completed transactions remain permanent,even during system failure.
即使在系统故障期间，已完成的事务也将保持永久不变。

A - Successful transfer
B - Unsuccessful transfer (Accounts are left in an inconsistent state.)

A - Changes within a transaction are buffered.
B - If a transfer is successful, changes are committed (made permanent).

A - Changes within a transaction are buffered.
B - If a problem occurs, the transaction is rolled back to the previous consistent state.

# JDBC Transactions

By default, when a Connection is created, it is in auto-commit mode.

• Each individual SQL statement is treated as a transaction and automatically committed after it is executed.
• To group two or more statements together, you must disable auto-commit mode.
  con.setAutoCommit (false);
• You must explicitly call the commit method to complete the transaction with the database.
  con.commit();
• You can also programmatically roll back transactions in the event of a failure.
  con.rollback();

# Bean Validation

• Bean Validation 1.1 in Java EE 7
• Bean Validation 2.0 in Jakarta EE 8
• javax.validation package
• Hibernate Validator
  • Custom Validation in all layers vs Standard Validation in Domain Model
• Getting Starting with Bean Validation
  • Maven Coordinates
  • Junit
    • Where to place Validator?
    • Jakarta EE 8 API Docs

<dependency>

    <groupId>org.hibernate.validator</groupId>

    <artifactId>hibernate-validator</artifactId>

    <version>7.0.0.Final</version>

</dependency>

<dependency>

    <groupId>org.glassfish</groupId>

    <artifactId>jakarta.el</artifactId>

    <version>4.0.0</version>

</dependency>

<dependency>

    <groupId>org.hibernate.validator</groupId>

    <artifactId>hibernate-validator-cdi</artifactId>

    <version>7.0.0.Final</version>

</dependency>

# Built-In Constraints - 1.1

# New Constraints - 2.0

# Testing

• Maven
• Unit Tests vs Integration Tests
  • Unit == Maven Surefire Plugin
  • Integration == Maven Failsafe Plugin

# Lab

# Summary

The purpose of this assignment is to refresh on the basics of JDBC, learn the basics of Bean Validation, and to practice these techniques using JUnit test cases.

# Requirements

# Database Setup

No custom database for this lab - but you must pick one of the sample databases to work with.  You must grant access to the sample database for your itmd4515 user (see Lab 2 - Setup and Introductory Webapp).

Some MySQL sample databases were installed for you during Week 1.  Here are some links to other MySQL sample databases:

1. mysql
2. chinook

# Project Setup

Create a Java with Maven Java Application project in NetBeans with the following coordinates and configuration:

1. Project Name: uid-lab3
2. Artifact ID: uid-lab3 (default is fine)
3. Group ID: edu.itmd4515.uid
4. Version: 1.0-SNAPSHOT (default is fine)
5. Use a base package for your Java code of edu.itmd4515.uid

Accept the Lab 3 assignment to create your GitHub repository and configure your projects for source control as documented in Lab 2 - Setup and Introductory Webapp.

Note, this is a Java SE application.  It is not a web application.

Deviating from the package convention given above will mean that you can not benefit from Sonar and other automated tools, and I will not be able to fix this. Please follow the specification!

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.logging.Level;

import java.util.logging.Logger;

/**

 *

 * @author amehime

 */

public class Driver {

    private static final Logger LOG = Logger.getLogger(Driver.class.getName());

    public static void main(String ...args) {

        String url = "jdbc:mysql://localhost:3306/world?zeroDateTimeBehavior=CONVERT_TO_NULL";

        String username = "itmd4515";

        String password = "itmd4515";

        String query = "select * from country where Code = ?";

        try(Connection c = DriverManager.getConnection(url, username, password);

                PreparedStatement ps = c.prepareStatement(query);) {

            ps.setString(1, "MEX");

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {

                LOG.info("data from database: " + rs.getString("Name"));

            }

            // prepared statement protects some against SQL Injection

            ps.setString(1, "drop table country;");

            ResultSet rs2 = ps.executeQuery();

            while (rs2.next()) {

                LOG.info("data from database: " + rs2.getString("Name"));

            }

            rs.close();

            rs2.close();

        } catch (SQLException ex) {

            LOG.log(Level.SEVERE, null, ex);

        }

    }

}

# Project Requirements

1. What database did you select, and which table are you going to represent as a Java POJO?  What fields did you select to map from table to Java class?  Discuss in your wiki page.

2. Add the following dependencies to your  pom.xml using a scope you feel is appropriate:

   1. junit (latest non-beta version of junit 5)
      Code

      	<dependency>
      	<groupId>org.junit.jupiter</groupId>
      	<artifactId>junit-jupiter-engine</artifactId>
      	<version>5.7.0</version>
      	<scope>test</scope>
      	</dependency>

   2. mysql-connector-java (latest non-beta version of 8)
      Code

      	<dependency>
      	<groupId>mysql</groupId>
      	<artifactId>mysql-connector-java</artifactId>
      	<version>8.0.23</version>
      	<scope>compile</scope>
      	</dependency>

   3. Important - build and plugins section.  At this time, we need to add specific versions of the maven test plugins for use with JUnit 5.  Add the following to your pom.xml

      	<build>
      	<plugins>
      	<plugin>
      	<groupId>org.apache.maven.plugins</groupId>
      	<artifactId>maven-surefire-plugin</artifactId>
      	<version>2.22.2</version>
      	</plugin>
      	<plugin>
      	<groupId>org.apache.maven.plugins</groupId>
      	<artifactId>maven-failsafe-plugin</artifactId>
      	<version>2.22.2</version>
      	</plugin>
      	</plugins>
      	</build>

3. What scope(s) did you select and why?  Discuss in your wiki page.

4. Follow the Bean Validation Getting Started guide to add Hibernate Validator and dependencies to your pom.xml using a scope you feel is appropriate.

<dependency>

    <groupId>org.hibernate.validator</groupId>

    <artifactId>hibernate-validator</artifactId>

    <version>7.0.0.Final</version>

</dependency>

<dependency>

    <groupId>org.glassfish</groupId>

    <artifactId>jakarta.el</artifactId>

    <version>4.0.0</version>

</dependency>

<dependency>

    <groupId>org.hibernate.validator</groupId>

    <artifactId>hibernate-validator-cdi</artifactId>

    <version>7.0.0.Final</version>

</dependency>

5. In your Lab 3 project, create the following as demonstrated in class:

   1. POJO to represent the structure of a single database table.  You can pick any table in your preferred sample database.  You do not have to traverse relationships.  Keep it simple for this lab.
      POJO 代表单个数据库表的结构。你可以选择你喜欢的示例数据库中的任何表。不必遍历关系。对于本练习，请使其保持简单。

      1. Include all required (not null) database columns as fields in your POJO.
      2. Include a minimum of 2 bean validation constraints in your POJO.  These bean validation constraints should relate to database constraints (column length, not null, etc) or type of data (email, string, date, etc)
      Process

      • 在 Projects/Source Packages 新建一个 package，命名为 domain ，新建一个 Java Class，命名为 Customer 。
      • 修改 NetBeans 配置 - Editor - Formating
        Language: Java, Ctegory: Ordering, Insertion Point: Last in Category
      • Source - Insert Code - Add Property
        Name: id , Type: Integer
        Name: firstName, Type: String
        Name: lastName
        Name: email
      • Source - Insert Code - Constructor （三个，一个无参数，一个全参数，一个排除 id 外的其他参数）
      • Source - Insert Code - toString

      	···
      	import jakarta.validation.constraints.Email;
      	import jakarta.validation.constraints.NotBlank;
      	import jakarta.validation.constraints.NotNull;
      	import jakarta.validation.constraints.Positive;
      	···
      	@NotNull
      	@Positive
      	private Integer id;
      	@NotBlank
      	private String firstName;
      	@NotBlank
      	private String lastName;
      	@Email
      	@NotBlank
      	private String email;

   2. Two JUnit test classes.  One should be for testing the validation constraints in your POJO, and the other for testing JDBC CRUD operations.

      1. Make appropriate use of test fixtures, as discussed in class.

      2. Make appropriate use of helper methods to extract and parameterize functionality - DRY (Don't repeat yourself)

      3. In your validation test class, include at least 2 test methods proving your bean validation constraints work

      4. In your JDBC test class, include a test method for each of the 4 CRUD operations (Create, Read, Update, Delete).

         I recommend you consider inserting a row for testing purposes in the @Before test fixture, and removing it in the @After test fixture.  By following that pattern, your test cases will always be testing a consistent database row, and will be completely separate from the "actual" data in the tables themselves.

         I will be running your projects using maven, so make sure not to deviate from database name, username and password conventions outlined in our initial setup.

      Process

      • 在 Projects/Test Packages 下新建两个 Java Class CustomerValidationTest 和 CustomerJDBCTest

      	import domian.Customer;
      	import jakarta.validation.ConstraintViolation;
      	import jakarta.validation.Validation;
      	import jakarta.validation.Validator;
      	import jakarta.validation.ValidatorFactory;
      	import java.util.Set;
      	import org.junit.jupiter.api.AfterAll;
      	import org.junit.jupiter.api.AfterEach;
      	import org.junit.jupiter.api.BeforeAll;
      	import org.junit.jupiter.api.BeforeEach;
      	import org.junit.jupiter.api.Test;
      	import static org.junit.jupiter.api.Assertions.*;
      	public class CustomerValidationTest {
      	// 1. beforeAll
      	// 2. beforeEach
      	// 3. test1
      	// 4. afterEach
      	// 5. beforeEach
      	// 6. test2
      	// 7. afterEach
      	// 8. afterAll
      	private static Validator validator;
      	@BeforeAll
      	public static void beforeAll() {
      	ValidatorFactory factory = Validation.buildDefaultValidatorFactory();
      	validator = factory.getValidator();
      	}
      	@AfterAll
      	public static void afterAll() {
      	// if validator were not auto-closeable, we should close it here
      	}
      	@BeforeEach
      	public void beforeEach() {
      	}
      	@AfterEach
      	public void afterEach() {
      	}
      	@Test
      	public void customerInvalidEmail_expectFailure() {
      	Customer c = new Customer(1, "Customer", "One", "customercustomer.net");
      	Set<ConstraintViolation<Customer>> constraintViolations = validator.validate(c);
      	assertTrue(constraintViolations.size() == 1);
      	assertEquals(1, constraintViolations.size());
      	assertFalse(constraintViolations.isEmpty());
      	for (ConstraintViolation<Customer> problem : constraintViolations) {
      	System.out.println(problem.toString());
      	}
      	}
      	@Test
      	public void customerIsCompletelyValid_expectPass() {
      	Customer c = new Customer(1, "Customer", "One", "customer@customer.net");
      	Set<ConstraintViolation<Customer>> constraintViolations = validator.validate(c);
      	assertTrue(constraintViolations.size() == 0);
      	assertEquals(0, constraintViolations.size());
      	assertTrue(constraintViolations.isEmpty());
      	}
      	}

      	import domian.Customer;
      	import java.sql.Connection;
      	import java.sql.DriverManager;
      	import java.sql.PreparedStatement;
      	import java.sql.ResultSet;
      	import java.sql.SQLException;
      	import org.junit.jupiter.api.AfterAll;
      	import org.junit.jupiter.api.AfterEach;
      	import org.junit.jupiter.api.BeforeAll;
      	import org.junit.jupiter.api.BeforeEach;
      	import org.junit.jupiter.api.Test;
      	import static org.junit.jupiter.api.Assertions.*;
      	public class CustomerJDBCTest {
      	private Connection connection;
      	// helper utility methods - to help us DRY (don't repeat yourself, at least
      	// not in your code. Repeat yourself all you want in your speech
      	private Connection getConnection() throws SQLException {
      	String url = "jdbc:mysql://localhost:3306/Chinook?zeroDateTimeBehavior=CONVERT_TO_NULL";
      	String username = "itmd4515";
      	String password = "itmd4515";
      	return DriverManager.getConnection(url, username, password);
      	}
      	private void createACustomer(Customer c) throws SQLException {
      	String INSERT_SQL = "insert into Customer "
      	+ "(CustomerId, FirstName, LastName, Email) "
      	+ "values (?,?,?,?)";
      	PreparedStatement ps = connection.prepareStatement(INSERT_SQL);
      	ps.setInt(1, c.getId());
      	ps.setString(2, c.getFirstName());
      	ps.setString(3, c.getLastName());
      	ps.setString(4, c.getEmail());
      	ps.executeUpdate();
      	}
      	// returns either null, or the valid customer found in the database
      	private Customer findACustomer(Integer id) throws SQLException {
      	Customer c = null;
      	String FIND_SQL = "select * from Customer where CustomerId = ?";
      	PreparedStatement ps = connection.prepareStatement(FIND_SQL);
      	ps.setInt(1, id);
      	ResultSet rs = ps.executeQuery();
      	if(rs.next()){
      	c = new Customer();
      	c.setId(rs.getInt("CustomerId"));
      	c.setFirstName(rs.getString("FirstName"));
      	c.setLastName(rs.getString("LastName"));
      	c.setEmail(rs.getString("Email"));
      	}
      	return c;
      	}
      	private void readFromDatabaseAfterUpdate(Customer c) throws SQLException {
      	String UPDATE_SQL = "update Customer set "
      	+ "FirstName = ?, "
      	+ "LastName = ?, "
      	+ "Email = ? "
      	+ "where CustomerId = ?";
      	PreparedStatement ps = connection.prepareStatement(UPDATE_SQL);
      	ps.setString(1, c.getFirstName());
      	ps.setString(2, c.getLastName());
      	ps.setString(3, c.getEmail());
      	ps.setInt(4, c.getId());
      	ps.executeUpdate();
      	}
      	private void deleteACustomer(Integer id) throws SQLException {
      	String DELETE_SQL = "delete from Customer where CustomerId = ?";
      	PreparedStatement ps = connection.prepareStatement(DELETE_SQL);
      	ps.setInt(1, id);
      	ps.executeUpdate();
      	}
      	@BeforeAll
      	public static void beforeAll() {
      	}
      	@AfterAll
      	public static void afterAll() {
      	}
      	// before each test, you can stage any test data you need to work with
      	// you won't want to rely on the data in the tables, because the next time
      	// you run your test, it might be different
      	@BeforeEach
      	public void beforeEach() throws SQLException {
      	connection = getConnection();
      	Customer c = new Customer(9999, "Mayuri", "Mizuki", "mayuri@lostyu.me");
      	createACustomer(c);
      	}
      	// after each test, you can clean up any test data that you staged earlier
      	@AfterEach
      	public void afterEach() throws SQLException {
      	deleteACustomer(9999);
      	connection.close();
      	}
      	// testing C R U D
      	//
      	// you never want test methods to depend on one another!
      	// don't create data in the createTest, to test it with the readTest!
      	// test methods should be atomic!
      	@Test
      	public void createCustomerTest() throws SQLException {
      	// this is test data I am staging within this method
      	Customer customerToCreate = new Customer(9998, "Test", "Method", "mayuri@lostyu.meu");
      	createACustomer(customerToCreate);
      	// now that I've created the customer - I need to find it, and assert
      	// the create was successful
      	Customer foundInDatabase = findACustomer(9998);
      	assertNotNull(foundInDatabase);
      	assertEquals(customerToCreate.getId(), foundInDatabase.getId());
      	assertEquals(customerToCreate.getFirstName(), foundInDatabase.getFirstName());
      	assertEquals(customerToCreate.getLastName(), foundInDatabase.getLastName());
      	assertEquals(customerToCreate.getEmail(), foundInDatabase.getEmail());
      	System.out.println(foundInDatabase.toString());
      	// clean up the data I staged within this method
      	deleteACustomer(9998);
      	}
      	@Test
      	public void readCustomerTest() throws SQLException {
      	Customer readFromDatabase = findACustomer(9999);
      	assertNotNull(readFromDatabase);
      	assertEquals(9999, readFromDatabase.getId());
      	}
      	@Test
      	public void updateCustomerTest() throws SQLException {
      	Customer readFromDatabaseBeforeUpdate = findACustomer(9999);
      	readFromDatabaseBeforeUpdate.setLastName("Updated Successfully");
      	readFromDatabaseAfterUpdate(readFromDatabaseBeforeUpdate);
      	Customer updatedInDatabase = findACustomer(9999);
      	assertEquals(readFromDatabaseBeforeUpdate.getLastName(),updatedInDatabase.getLastName());
      	}
      	@Test
      	public void deleteCustomerTest() throws SQLException {
      	// this is test data I am staging within this method
      	Customer customerToDelete = new Customer(9998, "Test", "Method", "mayuri@lostyu.me");
      	createACustomer(customerToDelete);
      	// now that I've created the customer - I need to delete it, and then
      	// try to re-find it, in order to assert that I can not - and therefore
      	// it has been successfully deleted
      	deleteACustomer(9998);
      	Customer tryAndFindDeletedCustomerInDatabase = findACustomer(9998);
      	assertNull(tryAndFindDeletedCustomerInDatabase);
      	}
      	}

      • Run - Test Project
      • Shell

      	cd NetBeansProjects/
      	ls -ltr
      	cd lab3
      	ls
      	mvn clean test

6. Document Lab 3 execution in your wiki page by taking a screenshot of your NetBeans Test Results window (the red/yellow/green stoplight view shown in class).  Discuss any other issues or insights you had with Lab 3.

7. Test your Java SE project on the command line using maven. Document your experience (with code block output) on your wiki page, and discuss how command line Java relates to Maven (hint - think classpath and dependencies). What would you need to do (step by step) in order to run your project without maven, using only the Java SE provided java and javac binaries? In what ways does Maven help us?

8. Submit to Blackboard

   1. Right click your uid-lab3project and select Clean
   2. Go to your NetBeans Projects directory.  Create a zip file of the uid-lab3 folder named (you guessed it) uid-lab3.zip.  Submit that zip file to the Blackboard assignment.