# 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
Project Files/pom.xml
<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

ConstraintAccepted TypesDescription

AssertFalse
AssertTrue

Boolean, boolean

The annotated element must be either false or true

DecimalMax
DecimalMin

BigDecimal, BigInteger, CharSequence, byte, short, int, long, and respective wrappers

The element must be greater or lower than the specified value

Future
Past

Calendar, Date

The annotated element must be a date in the future or in the past

Max
Min

BigDecimal, BigInteger, byte, short, int, long, and their wrappers

The element must be greater or lower than the specified value

Null
NotNull

Object

The annotated element must be null or not

PatternCharSequenceThe element must match the specified regular expression
DigitsBigDecimal, BigInteger, CharSequence, byte, short, int, long, and respective wrappersThe annotated element must be a number within accepted range
SizeObject[], CharSequence, Collection<?>, Map<?, ?>The element size must be between the specified boundaries

# New Constraints - 2.0

ConstraintAccepted TypesDescription
EmailCharSequenceChecks whether the specified character sequence is a valid email address
NotEmptyCharSequence, Collection, Map and arraysChecks whether the annotated element is not null nor empty
NotBlankCharSequenceChecks that the annotated character sequence is not null and the trimmed length is greater than 0. The difference to @NotEmpty is that this constraint can only be applied on character sequences and that trailing white-spaces are ignored.

Positive
Negative

BigDecimal, BigInteger, byte, short, int, long and the respective wrappers of the primitive types

Checks if the element is strictly positive/negative. Zero values are considered invalid.

PositiveOrZero
NegativeOrZero

BigDecimal, BigInteger, byte, short, int, long and the respective wrappers of the primitive types

Checks if the element is positive/negative or zero.

PastOrPresent
FutureOrPresent

java.util.Date, java.util.Calendar, java.time.*

Checks whether the annotated date is in the present or in the past/future.

# 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
Process

导入方法:

  • 方法 1:Workbench
    File - Open SQL Script ,选择 world.sql 文件打开,内容全选后,按 Execute
  • 方法 2:终端打开 sakila-db 目录
    mysql -u root -p < sakila-schema.sql
    mysql -u root -p < sakila-data.sql
    或者下载 Chinook_Mysql_AutoIncrementPKs.sql 大样本数据
    mysql -u root -p < Chinook_Mysql_AutoIncrementPKs.sql
    wc -l Chinook_Mysql_AutoIncrementPKs.sql
    grep -i insert Chinook_Mysql_AutoIncrementPKs.sql

回到 Workbench,给 itmd 账号添加数据库权限, Administration - Users and Privileges ,记得 information_schema 需要 SELECT 权限。

# 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!

Process
  1. Projects/Source Packages 里创建一个新 Java class Driver.java

  2. Shell 查看某个数据库,选择某个数据库,按某个字段排列

    mysql -u itmd4515 -p
    show databases;
    use world;
    show tables;
    desc country;
  3. Projects/Dependencies - Add Dependency
    Query mysql-connector-java
    or
    groupId: mysql ,
    artifactId: mysql-connector-java ,
    version: 8.0.23
    Build with Dependencies

  4. edit Projects/Project Files/pom.xml , add <scope>compile</scope>

Driver.java
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.

Process
Project Files/pom.xml
<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>
  1. 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 CustomerValidationTestCustomerJDBCTest
      CustomerValidationTest
      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());
          }
          
      }
      CustomerJDBCTest
      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
  2. 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.

  3. 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?

  4. 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.