Security

LIMITING ACCESSIBILITY

package animals.security;
public class ComboLocks {
   public Map<String, String> combos;
}

combos object has public access. This is also poor encapsulation. A key security principle is to limit access as much as possible. Think of it as “need to know” for objects. This is called the principle of least privilege.

better:

package animals.security;
public class ComboLocks {
private Map<String, String> combos;
   public boolean isComboValid(String animal, String combo) {
      var correctCombo = combos.get(animal);
      return combo.equals(correctCombo);
} }

RESTRICTING EXTENSIBILITY

public class GrasshopperCage {
   public static void openLock(ComboLocks comboLocks, String
combo) {
      if (comboLocks.isComboValid("grasshopper", combo))
            System.out.println("Open!");
} }

// nu is mogelijk
public class EvilComboLocks extends ComboLocks {
   public boolean isComboValid(String animal, String combo) {
      var valid = super.isComboValid(animal, combo);
      if (valid) {
         // email the password to Hacker Harry
      }
      return valid;
   }
}

// nu is niet meer mogelijk

public **final** class ComboLocks { private Map<String, String> combos;
   // instantiate combos object
   public boolean isComboValid(String animal, String combo) {
      var correctCombo = combos.get(animal);
      return combo.equals(correctCombo);
} }

CREATING IMMUTABLE OBJECTS

Although there are a variety of techniques for writing an immutable class, you should be familiar with a common strategy for making a class immutable. 1. Mark the class as final. 2. Mark all the instance variables private. 3. Don't define any setter methods and make fields final. 4. Don't allow referenced mutable objects to be modified. 5. Use a constructor to set all properties of theo bject,making a copy if needed.

  import java.util.*;

  public final class Animal {

        private final ArrayList<String> favoriteFoods;

        public Animal() {
           this.favoriteFoods = new ArrayList<String>();
           this.favoriteFoods.add("Apples");

     }

    public List<String> getFavoriteFoods() {
        return favoriteFoods;
    }
}
// kwetsbaar omdat favoriteFoods gewijzigd kan worden bv:  getFavoriteFoods().clear()

// dit zou al helpen ipv vorige getter
public int getFavoriteFoodsCount() {
    return favoriteFoods.size();
}
public String getFavoriteFoodsElement(int index) {
   return favoriteFoods.get(index);
}

// of
public ArrayList<String> getFavoriteFoods() {
   return new ArrayList<String>(this.favoriteFoods);
}

Let's say we want to allow the user to provide the favoriteFoods data, so we implement the following:

public Animal(ArrayList<String> favoriteFoods) {
   if(favoriteFoods == null)
      throw new RuntimeException("favoriteFoods is
   this.favoriteFoods = favoriteFoods;
}
public int getFavoriteFoodsCount() {
   return favoriteFoods.size();
}
public String getFavoriteFoodsElement(int index) {
   return favoriteFoods.get(index);
}

kan dan en dan is favfoods niet meer immutable:
void modifyNotSoImmutableObject() {
    var favorites = new ArrayList<String>();
    favorites.add("Apples");
    var animal = new Animal(favorites);
    System.out.print(animal.getFavoriteFoodsCount());
    favorites.clear();
    System.out.print(animal.getFavoriteFoodsCount());
}

// The solution is to use a copy constructor to make a copy of the list object containing the same elements.



public Animal(List<String> favoriteFoods) {
   if(favoriteFoods == null)
        throw new RuntimeException("favoriteFoods is required");
   this.favoriteFoods = new ArrayList<String> (favoriteFoods);
}

CLONING OBJECTS

ava has a Cloneable interface that you can implement if you want classes to be able to call the clone() method on your object. This helps with making defensive copies.

this.favoriteFoods = (ArrayList) favoriteFoods.clone();

// dan Clonable gebruyiken:
public final class Animal implements Cloneable {

//en
public static void main(String... args) throws Exception {
   ArrayList<String> food = new ArrayList<>();
   food.add("grass");
   Animal sheep = new Animal(food);
   Animal clone = (Animal) sheep.clone();
   System.out.println(sheep == clone);
   System.out.println(sheep.favoriteFoods == clone.favoriteFoods);
}

By default, the clone() method makes a shallow copy of the data, which means only the top‐level object references and primitives are copied. No new objects from within the cloned object are created.

By contrast, you can write an implementation that does a deep copy and clones the objects inside. A deep copy does make a new ArrayList object. Changes to the cloned object do not affect the original.

public Animal clone() {
   ArrayList<String> listClone = (ArrayList)
    favoriteFoods.clone();
   return new Animal(listClone);
}

myObject.clone() | V Implements Clonable? -> No -> Throws Exception | V Overrides Clone() -> No -> Shallow copy | V Implementation dependent

In the last block, implementation‐dependent means you should probably check the Javadoc of the overridden clone() method before using it.

Shallow Copy

A shallow copy of an object is a new instance of that object where the fields of the original object are copied as they are. However, if the object contains references to other objects (i.e., non-primitive fields), only the references are copied, not the actual objects they refer to.

        Address address = new Address("New York");
        Person person1 = new Person("John", address);
        Person person2 = (Person) person1.clone();

        System.out.println(person1.address.city); // Outputs: New York
        System.out.println(person2.address.city); // Outputs: New York

        person2.address.city = "Los Angeles";

        System.out.println(person1.address.city); // Outputs: Los Angeles
        System.out.println(person2.address.city); // Outputs: Los Angeles

In this example, person1 and person2 are separate objects, but they share the same Address object. If you change the city in person2, it will also change in person1, demonstrating that only a shallow copy was made.

Deep Copy

A deep copy of an object involves creating a new object and also recursively copying all objects referenced by the original object. This means that the copy and the original object do not share references to any mutable objects. Any changes made to the deep-copied object will not affect the original object.

// nu in person.class:
@Override
    protected Object clone() throws CloneNotSupportedException {
        Person clonedPerson = (Person) super.clone();
        clonedPerson.address = new Address(this.address); // Deep copy of Address
        return clonedPerson;
    }

// en dan
Address address = new Address("New York");
        Person person1 = new Person("John", address);
        Person person2 = (Person) person1.clone();

        System.out.println(person1.address.city); // Outputs: New York
        System.out.println(person2.address.city); // Outputs: New York

        person2.address.city = "Los Angeles";

        System.out.println(person1.address.city); // Outputs: New York
        System.out.println(person2.address.city); // Outputs: Los Angeles

Shallow copying is faster and uses less memory, but deep copying ensures that the two objects are entirely independent of each other.

Introducing Injection and Input Validation

Injection is an attack where dangerous input runs in a program as part of a command. Kan bv met Statement met raw SQL.

"monday' OR day IS NOT NULL OR day = 'sunday"

An exploit is an attack that takes advantage of weak security.

There are many sources of untrusted data. For the exam, you need to be aware of user input, reading from files, and retrieving data from a database. In the real world, any data that did not originate from your program should be considered suspect.

Using PreparedStatement

If you remember only two things about SQL and security, remember to use a PreparedStatement and bind variables.

INVALIDATING INVALID INPUT WITH VALIDATION

SQL injection isn't the only type of injection. Command injection is another type that uses operating system commands to do something unexpected.

Console console = System.console();
String dirName = console.readLine();
Path path = Paths.get("c:/data/diets/" + dirName);
try (Stream<Path> stream = Files.walk(path)) {
   stream.filter(p -> p.toString().endsWith(".txt"))
      .forEach(System.out::println);
}
// als je input .. is dan zie je de secrets dir... dus validate input

// bv whitelist
`if (dirName.equals("mammal") || dirName.equals("birds")) {
`

A blacklist is a list of things that aren't allowed. In the previous example, we could have put the dot ( .) character on a blacklist. The problem with a blacklist is that you have to be cleverer than the bad guys. There are a lot of ways to cause harm. For example, you can encode characters.

By contrast, the whitelist is specifying what is allowed. You can supply a list of valid characters. Whitelisting is preferable to blacklisting for security because a whitelist doesn't need to foresee every possible problem.

Working with Confidential Information

GUARDING SENSITIVE DATA FROM OUTPUT

The first step is to avoid putting confidential information in a toString() method. Dus oppassen bij - Writing to a log file - Printing an exception or stack trace - System.out and System.err messages - Writing to data files

PROTECTING DATA IN MEMORY

if crashes:

When calling the readPassword() on Console, it returns a char[] instead of a String. This is safer for two reasons. - It is not stored as a String, so Java won't place it in the String pool, where it could exist in memory long after the code that used it is run. - You can null out the value of the array element rather than waiting for the garbage collector to do it.

Console console = System.console();
char[] password = console.readPassword();
Arrays.fill(password, 'x');

When the sensitive data cannot be overwritten, it is good practice to set confidential data to null when you're done using it. If the data can be garbage collected, you don't have to worry about it being exposed later.

LocalDate dateOfBirth = getDateOfBirth();
// use date of birth
dateOfBirth = null;

The idea is to have confidential data in memory for as short a time as possible.

LIMITING FILE ACCESS

Another way is to use a security policy to control what the program can access.

It is good to apply multiple techniques to protect your application. This approach is called defense in depth.

// permissie read only
grant {
   permission java.io.FilePermission
    "C:\\water\\fish.txt",
    "read";
};

// permissie read write
grant {
   permission java.io.FilePermission
    "C:\\water\\fish.txt",
    "read, write";
};

When looking at a policy, pay attention to whether the policy grants access to more than is needed to run the program. If our application needs to read a file, it should only have read permissions. This is the principle of least privilege we showed you earlier.

Serializing and Deserializing Objects

Imagine we are storing data in an Employee record. We want to write this data to a file and read this data back into memory, but we want to do so without writing any potentially sensitive data to disk. From Chapter 19, you should already know how to do this with serialization.

import java.io.*;
public class Employee implements Serializable {
   private String name;
   private int age;
   // Constructors/getters/setters
}

SPECIFYING WHICH FIELDS TO SERIALIZE

marking a field as transient prevents it from being serialized.

private transient int age;

// Alternatively, you can specify fields to be serialized in an array.

private static final ObjectStreamField[]
serialPersistentFields =
   { new ObjectStreamField("name", String.class) };

You can think of serialPersistentFields as the opposite of transient. The former is a whitelist of fields that should be serialized, while the latter is a blacklist of fields that should not.

If you go with the array approach, make sure you remember to use the private, static, and final modifiers. Otherwise, the field will be ignored.

CUSTOMIZING THE SERIALIZATION PROCESS

Security may demand custom serialization.

Take a look at the following implementation that uses writeObject() and readObject() for serialization

import java.io.*;
public class Employee implements Serializable {
   private String name;
   private String ssn;
   private int age;

   // Constructors/getters/setters

   private static final ObjectStreamField[] serialPersistentFields =
            { new ObjectStreamField("name", String.class), new ObjectStreamField("ssn", String.class) };

    private static String encrypt(String input) {
      // Implementation omitted
    }
    private static String decrypt(String input) {
      // Implementation omitted
    }

    private void writeObject(ObjectOutputStream s) throws Exception {
          ObjectOutputStream.PutField fields = s.putFields();
          fields.put("name", name);
          fields.put("ssn", encrypt(ssn));
          s.writeFields();
    }

    private void readObject(ObjectInputStream s) throws Exception {
            ObjectInputStream.GetField fields = s.readFields();

            this.name = (String)fields.get("name", null);
            this.ssn = decrypt((String)fields.get("ssn", null));
    }

}

This version skips the age variable as before, although this time without using the transient modifier. It also uses custom read and write methods to securely encrypt/decrypt the Social Security number. Notice the PutField and GetField classes are used in order to write and read the fields easily.

PRE/POST‐SERIALIZATION PROCESSING

import java.io.*;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
public class Employee implements Serializable {
   ...
   private Employee() {}
   private static Map<String,Employee> pool =
      new ConcurrentHashMap<>();
   public synchronized static Employee getEmployee(String
name) {
      if(pool.get(name)==null) {
         var e = new Employee();
         e.name = name;         pool.put(name, e);
}
      return pool.get(name);
   }
}

This method creates a new Employee if one does not exist. Otherwise, it returns the one stored in the memory pool.

Applying readResolve()

Now we want to start reading/writing the employee data to disk, but we have a problem. When someone reads the data from the disk, it deserializes it into a new object, not the one in memory pool. This could result in two users holding different versions of the Employee in memory!

Enter the readResolve() method. When this method is present, it is run after the readObject() method and is capable of replacing the reference of the object returned by deserialization.

import java.io.*;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
public class Employee implements Serializable {
   ...
    public synchronized Object readResolve() throws ObjectStreamException {
      var existingEmployee = pool.get(name);
      if(pool.get(name) == null) {
        // New employee not in memory
        pool.put(name, this);
        return this;
     } else {
        // Existing user already in memory
        existingEmployee.name = this.name;
        existingEmployee.ssn = this.ssn;
        return existingEmployee;
    }
 }
}

If the object is not in memory, it is added to the pool and returned. Otherwise, the version in memory is updated, and its reference is returned. Notice that we added the synchronized modifier to this method. Java allows any method modifiers (except static) for the readResolve() method including any access modifier. This rule applies to writeReplace(), which is up next.

Applying writeReplace()

Now, what if we want to write an Employee record to disk but we don't completely trust the instance we are holding? For example, we want to always write the version of the object in the pool rather than the this instance. By construction, there should be only one version of this object in memory, but for this example let's pretend we're not 100 percent confident of that.

The writeReplace() method is run before writeObject() and allows us to replace the object that gets serialized.

import java.io.*;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
public class Employee implements Serializable {
   ...
    public Object writeReplace() throws ObjectStreamException {
      var e = pool.get(name);
      return e != null ? e : this;
    }
}

This implementation checks whether the object is found in the pool. If it is found in the pool, that version is sent for serialization; otherwise, the current instance is used.

Making Methods final

Making Classes final

Making Constructor private

public class FoodOrder {
   private String item;
   private int count;

    private FoodOrder(String item, int count) { setItem(item);
        setCount(count);
    }
-> public FoodOrder getOrder(String item, int count) {
      return new FoodOrder(item, count);
   }
   public String getItem() { return item; }
   public void setItem(String item) { this.item = item; }
   public int getCount() { return count; }
   public void setCount(int count) { this.count = count; }
}

HOW TO PROTECT THE SOURCE CODE

jars.....

Preventing Denial of Service Attacks

A denial of service (DoS) attack is when a hacker makes one or more requests with the intent of disrupting legitimate requests. Most denial of service attacks require multiple requests to bring down their targets. Some attacks send a very large request that can even bring down the application in one shot.

By contrast, a distributed denial of service (DDoS) attack is a denial of service attack that comes from many sources at once. For example, many machines may attack the target.

LEAKING RESOURCES

READING VERY LARGE RESOURCES

Another source of a denial of service attacks is very large resources.

public void transform(Path in, Path out) throws IOException  {
   var list = Files.readAllLines(in);
   list.removeIf(s -> s.trim().isBlank());
   Files.write(out, list);
}

gaat mis op grote files want je geheugen loopt vol... To prevent this problem, you can check the size of the file before reading it.

INCLUDING POTENTIALLY LARGE RESOURCES

An inclusion attack is when multiple files or components are embedded within a single file. Any file that you didn't create is suspect. Some types can appear smaller than they really are. For example, some types of images can have a “zip bomb” where the file is heavily compressed on disk. When you try to read it in, the file uses much more space than you thought.

Extensible Markup Language (XML) files can have the same problem. One attack is called the “billion laughs attack” where the file gets expanded exponentially.

OVERFLOWING NUMBERS

When checking file size, be careful with an int type and loops. Since an int has a maximum size, exceeding that size results in integer overflow. Incrementing an int at the maximum value results in a negative number, so validation might not work as expected.

public static void main(String[] args) {
   System.out.println(enoughRoomToAddLine(100));
   System.out.println(enoughRoomToAddLine(2_000_000));
   System.out.println(enoughRoomToAddLine(Integer.MAX_VALUE));
}
public static boolean enoughRoomToAddLine(int requestedSize) {
   int maxLength = 1_000_000;
   String newLine = "END OF FILE";
   int newLineSize = newLine.length();
   return requestedSize + newLineSize < maxLength;
}

//true
//false
//true

WASTING DATA STRUCTURES

One advantage of using a HashMap is that you can look up an element quickly by key. Even if the map is extremely large, a lookup is fast as long as there is a good distribution of hashed keys.

• Identify ways of preventing a denial of service attack. Using a try‐with‐ resources statement for all I/O and JDBC operations prevents resource leaks. Checking the file size when reading a file prevents it from using an unexpected amount of memory. Confirming large data structures are being used effectively can prevent a performance problem.
• Protect confidential information in memory. Picking a data structure that minimizes exposure is important. The most common one is using char[] for passwords. Additionally, allowing confidential information to be garbage collected as soon as possible reduces the window of exposure.
• Compare injection, inclusion, and input validation. SQL injection and command injection allow an attacker to run expected commands. Inclusion is when one file includes another. Input validation checks for valid or invalid characters from users.
• Design secure objects. Secure objects limit the accessibility of instance variables and methods. They are deliberate about when subclasses are allowed. Often secure objects are immutable and validate any input parameters.
• Write serialization and deserializaton code securely. The transient modifier signifies that an instance variable should not be serialized. Alternatively, serialPersistenceFields specifies what should be. The readObject(), writeObject(), readResolve(), and writeReplace() methods are optional methods that provide further control of the process.

prev next

JDBC

Java Database Connectivity Language (JDBC): Accesses data as rows and columns.

Java Persistence API (JPA): Accesses data through Java objects using a concept called object‐relational mapping (ORM).

A relational database is accessed through Structured Query Language (SQL). SQL is a programming language used to interact with database records. JDBC works by sending a SQL command to the database and then processing the response.
SQL keywords are case insensitive.

In addition to relational databases, there is another type of database called a NoSQL database. This is for databases that store their data in a format other than tables, such as key/value, document stores, and graph‐based databases. NoSQL is out of scope for the exam as well.

interfaces in the JDK: - Driver: Establishes a connection to the database - Connection: Sends commands to a database - PreparedStatement: Executes a SQL query - CallableStatement: Executes commands stored in the database - ResultSet: Reads results of a query

public class MyFirstDatabaseConnection {
   public static void main(String[] args) throws SQLException
   {
    String url = "jdbc:derby:zoo";
    try (Connection conn = DriverManager.getConnection(url);
        PreparedStatement ps = conn.prepareStatement( "SELECT name FROM animal");
        ResultSet rs = ps.executeQuery()) {
        while (rs.next())
        System.out.println(rs.getString(1));
    }
   }
}

GETTING A DATABASE CONNECTION

There are two main ways to get a Connection: DriverManager or DataSource. DriverManager is the one covered on the exam. Do not use a DriverManager in code someone is paying you to write. A DataSource has more features than DriverManager. For example, it can pool connections or store the database connection information outside the application.

Connection conn = DriverManager.getConnection("jdbc:derby:zoo");
System.out.println(conn);

It does not actually execute the query yet!

JDBC URL

Unlike web URLs, a JDBC URL has a variety of formats. They have three parts in common, as shown in Figure 21.3. The first piece is always the same. It is the protocol jdbc. The second part is the subprotocol, which is the name of the database such as derby, mysql, or postgres. The third part is the subname, which is a database‐specific format. Colons ( :) separate the three parts.

Notice the three parts. It starts with jdbc and then comes the subprotocol derby, and it ends with the subname, which is the database name. The location is not required, because Derby is an in‐memory database.

jdbc:derby:zoo // The location is not required, because Derby is an in‐memory database.


jdbc:postgresql://localhost/zoo
jdbc:oracle:thin:@123.123.123.123:1521:zoo
jdbc:mysql://localhost:3306
jdbc:mysql://localhost:3306/zoo?profileSQL=true

Statement, CallableStatement, PreparedStatement

            ┌───────────────────┐
            │     Statement     │
            └─────────▲─────────┘
          ┌───────────┴────────────┐
          │                        │
          │                        │
┌───────────────────┐    ┌───────────────────┐
│ PreparedStatement │    │ CallableStatement │
└───────────────────┘    └───────────────────┘

While it is possible to run SQL directly with Statement, you shouldn't. PreparedStatement is far superior for the following reasons: - Performance: In most programs, you run similar queries multiple times. A PreparedStatement figures out a plan to run the SQL well and remembers it. - Security: As you will see in Chapter 22, “Security,” you are protected against an attack called SQL injection when using a PreparedStatement correctly - Readability: It's nice not to have to deal with string concatenation in building a query string with lots of parameters. - Future use: Even if your query is being run only once or doesn't have any parameters, you should still use a PreparedStatement. That way future editors of the code won't add a variable and have to remember to change to PreparedStatement then.

Using the Statement interface is also no longer in scope for the JDBC exam, so we do not cover it in this book.

Prepared Statement

Passing a SQL statement when creating the object is mandatory.

try (var ps = conn.prepareStatement()) { // DOES NOT COMPILE }

correct:

try (
    PreparedStatement ps = conn.prepareStatement( "SELECT * FROM exhibits")) {
        // work with ps
}

Modifying Data with executeUpdate()

10: var insertSql = "INSERT INTO exhibits VALUES(10, 'Deer',
3)";
11: var updateSql = "UPDATE exhibits SET name = '' " +
12:    "WHERE name = 'None'";
13: var deleteSql = "DELETE FROM exhibits WHERE id = 10"; 14:
15: try (var ps = conn.prepareStatement(insertSql)) {
16: int result = ps.executeUpdate();
17:    System.out.println(result); // 1
18: }
19:
20: try (var ps = conn.prepareStatement(updateSql)) {
21: int result = ps.executeUpdate();
22:    System.out.println(result); // 0
23: }
24:
25: try (var ps = conn.prepareStatement(deleteSql)) {
26: int result = ps.executeUpdate();
27:    System.out.println(result); // 1
28: }

Reading Data with executeQuery()

30: var sql = "SELECT * FROM exhibits";
31: try (var ps = conn.prepareStatement(sql); 32: ResultSet rs = ps.executeQuery() ) { 33:
34: // work with rs
35: }

Processing Data with execute()

boolean isResultSet = ps.execute();
if (isResultSet) {
    try (ResultSet rs = ps.getResultSet()) {
        System.out.println("ran a query");
    }
} else {
    int result = ps.getUpdateCount();
    System.out.println("ran an update");
}

PreparedStatment Methods

WORKING WITH PARAMETERS

let op: conn.prepareStatment(sql) VS PreparedStatement ps = conn.prepareStatement(sql)

String sql = "INSERT INTO names VALUES(?, ?, ?)";


public static void register(Connection conn, int key,int type, String name) throws SQLException {

    String sql = "INSERT INTO names VALUES(?, ?, ?)";
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setInt(1, key);
        ps.setString(3, name);
        ps.setInt(2, type);
        ps.executeUpdate(); // GEEN SQL meesturen!!!
    }
}

Remember that JDBC starts counting columns with 1 rather than 0. A common exam (and interview) question tests that you know this!

When not having all parameters defined: The code compiles, and you get a SQLException. The message may vary based on your database driver.

PreparedStatments Methods

• setBoolean
• setDouble
• setInt
• setLong
• setObject (Any type)
• setString

COMPILE VS. RUNTIME ERROR WHEN EXECUTING

ps.setObject(1, key);
ps.setObject(2, type);
ps.setObject(3, name);
ps.executeUpdate(sql);  // INCORRECT

The problem is that the last line passes a SQL statement. With a PreparedStatement, we pass the SQL in when creating the object. More interesting is that this does not result in a compiler error. Remember that PreparedStatement extends Statement. The Statement interface does accept a SQL statement at the time of execution, so the code compiles. Running this code gives SQLException. The text varies by database.

UPDATING MULTIPLE TIMES

var sql = "INSERT INTO names VALUES(?, ?, ?)";
try (var ps = conn.prepareStatement(sql)) {
   ps.setInt(1, 20);
   ps.setInt(2, 1);
   ps.setString(3, "Ester");
   ps.executeUpdate();

   ps.setInt(1, 21);
   ps.setString(3, "Elias");
   ps.executeUpdate();
}// WORKS!!

BATCHING STATEMENTS

JDBC supports batching so you can run multiple statements in fewer trips to the database. For example, if you need to insert 1,000 records into the database, then inserting them as a single network call (as opposed to 1,000 network calls) is usually a lot faster. You don't need to know the addBatch() and executeBatch() methods for the exam, but they are useful in practice.

   public static void register(Connection conn, int firstKey, int type, String... names) throws SQLException {
      var sql = "INSERT INTO names VALUES(?, ?, ?)";
      var nextIndex = firstKey;
      try (var ps = conn.prepareStatement(sql)) {
         ps.setInt(2, type);
        for(var name: names) {
            ps.setInt(1, nextIndex);
            ps.setString(3, name);
            ps.addBatch();
            nextIndex++;
        }
        int[] result = ps.executeBatch();
        System.out.println(Arrays.toString(result));
      }
}

register(conn, 100, 1, "Elias", "Ester");

Getting Data from a ResultSet

begin met rs.next()

-> 0 leeg
* row1 * row2

daarom: while(rs.next()

String sql = "SELECT id, name FROM exhibits";
Map<Integer, String> idToNameMap = new HashMap<>();

try (var ps = conn.prepareStatement(sql);
    ResultSet rs = ps.executeQuery()) {
    while (rs.next()) {
        int id = rs.getInt("id");
        String name = rs.getString("name");
        idToNameMap.put(id, name);
    }
    System.out.println(idToNameMap);

}
// results: {1=African Elephant, 2=Zebra}

kan ook met index column. LET OP begint met 1!!!

    int id = rs.getInt(1);
    String name = rs.getString(2);

Attempting to access a column name or index that does not exist throws a SQLException, as does getting data from a ResultSet when it isn't pointing at a valid row.

var sql = "SELECT * FROM exhibits where name='Not in table'";
try (var ps = conn.prepareStatement(sql);
   var rs = ps.executeQuery()) {
   rs.next();
   rs.getInt(1); // SQLException
}

// fout: staat rs.next() staaat nog op 0
var sql = "SELECT count(*) FROM exhibits";
try (var ps = conn.prepareStatement(sql);
   var rs = ps.executeQuery()) {
   rs.getInt(1); // SQLException
}
// fout column 0 bestaat NIET
var sql = "SELECT count(*) FROM exhibits";
try (var ps = conn.prepareStatement(sql);
   var rs = ps.executeQuery()) {
   if (rs.next())
      rs.getInt(0); // SQLException
}

var sql = "SELECT name FROM exhibits";
try (var ps = conn.prepareStatement(sql);
   var rs = ps.executeQuery()) {
   if (rs.next())
   rs.getInt("badColumn"); // SQLException
}

To sum up this section, it is important to remember the following: - Always use an if statement or while loop when calling rs.next(). - Column indexes begin with 1.

ResultSet Return from getObject

16: var sql = "SELECT id, name FROM exhibits";
17: try (var ps = conn.prepareStatement(sql);
18:    var rs = ps.executeQuery()) {
19:
20:    while (rs.next()) {
21: Object idField = rs.getObject("id");
22: Object nameField = rs.getObject("name");
23:       if (idField instanceof Integer) {
24:          int id = (Integer) idField;
25:          System.out.println(id);
26: }
27:       if (nameField instanceof String) {
28:          String name = (String) nameField;

USING BIND VARIABLES

Wanneer er vars gezet moeten worden en uitgepakt dan nesten met try.

30: var sql = "SELECT id FROM exhibits WHERE name = ?";
31:
32: try (var ps = conn.prepareStatement(sql)) {
        ps.setString(1, "Zebra");
        try (var rs = ps.executeQuery()) {
           while (rs.next()) {
              int id = rs.getInt("id");
              System.out.println(id);
           }
        }
41: }

Calling a CallableStatement

Sometimes you want your SQL to be directly in the database instead of packaged with your Java code. This is particularly useful when you have many queries and they are complex. A stored procedure is code that is compiled in advance and stored in the database. Stored procedures are commonly written in a database‐specific variant of SQL, which varies among database software providers.

CALLING A PROCEDURE WITHOUT PARAMETERS

A stored procedure is called by putting the word call and the procedure name in braces ( {}).

12: String sql = "{call read_e_names()}";
13: try (CallableStatement cs = conn.prepareCall(sql);
14: ResultSet rs = cs.executeQuery()) {
15:
16:    while (rs.next()) {
17:       System.out.println(rs.getString(3));
18: }
19: }

PASSING AN IN PARAMETER

We have to pass a ? to show we have a parameter. This should be familiar from bind variables with a PreparedStatement.

25: var sql = "{call read_names_by_letter(?)}"; 26: try (var cs = conn.prepareCall(sql)) {
        cs.setString("prefix", "Z");
        try (var rs = cs.executeQuery()) {
           while (rs.next()) {
              System.out.println(rs.getString(3));
           }
        }
}

// => cs.setString(1, "Z"); === cs.setString("prefix", "Z");

RETURNING OUT PARAM

40: var sql = "{?= call magic_number(?) }";
41: try (var cs = conn.prepareCall(sql)) {
42: cs.registerOutParameter(1, Types.INTEGER);
43: cs.execute();
44:    System.out.println(cs.getInt("num"));
45: }

On line 40, we included two special characters ( ?=) to specify that the stored procedure has an output value. This is optional since we have the OUT parameter, but it does aid in readability.

On line 42, we register the OUT parameter. This is important. It allows JDBC to retrieve the value on line 44. Remember to always call registerOutParameter() for each OUT or INOUT parameter (which we will cover next).

WORKING WITH AN INOUT PARAMETER

50: var sql = "{call double_number(?)}"; 51: try (var cs = conn.prepareCall(sql)) {
52: cs.setInt(1, 8);
53: cs.registerOutParameter(1, Types.INTEGER);
54: cs.execute();
55:    System.out.println(cs.getInt("num"));
56: }

COMPARING CALLABLE STATEMENT PARAMETERS

Closing Database Resources

it is important to close resources when you are finished with them. This is true for JDBC as well. JDBC resources, such as a Connection, are expensive to create. Not closing them creates a resource leak that will eventually slow down your program.

Closing a JDBC resource should close any resources that it created. In particular, the following are true: - Closing a Connection also closes PreparedStatement (or CallableStatement) and ResultSet. - Closing a PreparedStatement (or CallableStatement) also closes the ResultSet

you learned that it is possible to declare a type before a try‐with‐resources statement. Do you see why this method is bad? ``` 40: public void bad() throws SQLException { 41: var url = "jdbc:derby:zoo"; 42: var sql = "SELECT not_a_column FROM names"; 43: var conn = DriverManager.getConnection(url); 44: var ps = conn.prepareStatement(sql); 45: var rs = ps.executeQuery(); 46: 47: try (conn; ps; rs) { 48: while (rs.next()) 49: System.out.println(rs.getString(1)); 50: } 51: }

Suppose an exception is thrown on line 45. The try‐with‐resources block is never entered, so we don't benefit from automatic resource closing. That means this code has a resource leak if it fails. Do not write code like this.


There's another way to close a ResultSet. JDBC automatically closes a ResultSet when you run another SQL statement from the same Statement. This could be a PreparedStatement or a CallableStatement.

How many are closed?

14: var url = "jdbc:derby:zoo"; 15: var sql = "SELECT count(*) FROM names where id = ?"; 16: try (var conn = DriverManager.getConnection(url); 17: var ps = conn.prepareStatement(sql)) { 18: 19: ps.setInt(1, 1); 20: 21: var rs1 = ps.executeQuery(); 22: while (rs1.next()) { 23: System.out.println("Count: " + rs1.getInt(1)); 24: } 25: 26: ps.setInt(1, 2); 27: 28: var rs2 = ps.executeQuery(); 29: while (rs2.next()) { 30: System.out.println("Count: " + rs2.getInt(1)); 31: } 32: rs2.close();

``` !!!The correct answer is four!!! On line 28, rs1 is closed because the same PreparedStatement runs another query. On line 32, rs2 is closed in the method call. Then the try‐with‐resources statement runs and closes the PreparedSatement and Connection objects.

prev next