Saving objects to the database

Complete list of JDBC data types

In addition to the data types you know, JDBC allows you to work with many native data types for the DBMS. Below I will list the types and the functions to get them:

We have already considered primitive types. Let's now try to work with objects.

BLOB data type

If you want to save some object to the database, then the easiest way to do this is using the SQL BLOB type. JDBC has its counterpart called Blob.

BLOB stands for Binary L arge Object . It is used to store an array of bytes. The Blob type in JDBC is an interface and you can put (and receive) data in it in two ways:

• Using InputStream
• Using an array of bytes

Example: column number 3 contains the BLOB type:

Statement statement = connection.createStatement();
    ResultSet results = statement.executeQuery("SELECT * FROM user");
    results.first();

    Blob blob = results.getBlob(3);
    InputStream is = blob.getBinaryStream();

To create your own Blob object, you need to use the createBlob() function . Example:

String insertQuery = “INSERT INTO images(name, image) VALUES (?, ?)”;
PreparedStatement statement = connection.prepareStatement(insertQuery);

// Create a Blob object and get an OtputStream from it to write data to it
Blob blob = connection.createBlob();

// Populate the Blob with data...
OutputStream os = blob.setBinaryStream(1);

// Passing Blob as query parameter
statement.setBlob(2, blob);
statement.execute();

There are two ways to populate a Blob with data. The first one is via OutputSteam :

Path avatar = Paths.get("E:\\images\\cat.jpg");
OutputStream os = blob.setBinaryStream(1);
Files.copy(avatar, os);

And the second - through filling with bytes:

Path avatar = Paths.get("E:\\images\\cat.jpg");
byte[] content = Files.readAllBytes(avatar);
blob.setBytes(1, content);

Saving a Java Object to a Database

We have learned how to save binary objects to the database: byte arrays, byte streams, and so on. What about Java objects? How do we save a Java object to a database?

Let's say we have an Employee Java class that describes an employee of a company:

public class Employee {
    public Integer id;
    public String name;
    public String occupation;
    public Integer salary;
    public Date joinDate;
}

How can we save an object of this class to the database using JDBC?

In fact, you already know everything you need to know. First you need to create a table in the database that corresponds to this class. For example, this one:

CREATE TABLE employee {
    id INT PRIMARY KEY NOT NULL,
    name VARCHAR(100),
    occupation VARCHAR(100),
    salary INT,
    join_date DATE
}

And now let's write the code that will add an object of our Employee class to the base:

public static boolean addEmployee(Connection connection, Employee employee) throws Exception {
    	// Create and prepare a query to insert data into the table
    	String insertQuery = "INSERT INTO employee(name, occupation, salary, join_date ) VALUES (?, ?, ?, ?)";
    	PreparedStatement statement = connection.prepareStatement(insertQuery);

    	// Populate the query with data from the Employee object
    	statement.setString(1, employee.name);
    	statement.setString(2, employee.occupation);
    	statement.setInt(3, employee.salary);
    	statement.setDate(4, employee.joinDate);

    	// Execute our query and it returns true if a new row has been added
    	int count = statement.executeUpdate();
    	return count > 0;
    }

Simple and clear. The method will work great.

Reading a Java object from a database

We learned how to write an object to the database, now let's write the code to read the object from the database. Let's start with the code that reads an object from the database by its ID:

public static Employee getEployeeById(Connection connection, int id) throws Exception {
    	// Create and prepare a query to get an employee from the table
    	PreparedStatement statement = connection.prepareStatement("SELECT * FROM employee WHERE id = ?");
    	statement.setInt(1, id);

    	// Execute our query and it returns null if there are no rows in the query result
    	ResultSet results = statement.executeQuery();
    	if (!results.first())
        	return null;

    	// Populate the Employee object with the data from the ResultSet
    	Employee employee = new Employee();
    	employee.id = results.getInt(1);
    	employee.name = results.getString(2);
    	employee.occupation = results.getString(3);
    	employee.salary = results.getInt(4);
    	employee.joinDate = results.getDate(5);
    	return employee;
}

And if we need not one object, but several? Such a request is also easy to write. Let's get all the employees of our company:

public static List<Employee> getAllEployees(Connection connection) throws Exception {
	// Create and execute a query to get employees from the table
	Statement statement = connection.createStatement();
	ResultSet results = statement.executeQuery("SELECT * FROM employee");

	ArrayList<Employee> list = new ArrayList<Employee>();
	while (results.next()) {
        // Populate the Employee object with the data from the current row of the ResultSet
	        Employee employee = new Employee();
	        employee.id = results.getInt(1);
	        employee.name = results.getString(2);
	        employee.occupation = results.getString(3);
	        employee.salary = results.getInt(4);
	        employee.joinDate = results.getDate(5);

	        list.add(employee);
	}
	return list;
}

By the way, if there are many such methods, then in each of them you will have to write the same code for converting the ResultSet string into an objectemployee. So this code can be moved to a separate method.

This can be especially useful if the Employee class contains complex fields like Enum, InputStream, or references to other objects that we also want to store in the database.

1

Task

Module 4. Working with databases, level 8, lesson 3

Locked

task0805

task0805

Open

1

Task

Module 4. Working with databases, level 8, lesson 3

Locked

task0806

task0806

Open