The purpose of this tutorial is to use a separate layer written in Java to access tables in a database. This layer is usually called the Data Access Layer (DAL)
The biggest benefit of using DAL is that it simplifies the access operation of the database by directly using some methods like insert() and find(), rather than always doing links first and then executing some queries.
This layer handles all database-related calls and queries inside it.
Create a database
We want to create a simple table for the user, we can use these fields to create
id int
name varchar(200)
password varchar(200)
age int
Data transfer object
This layer should contain a simple class called Data Transfer Object (DTO). This class is just a simple map corresponding to the table in the database, and each column in the table corresponds to a member variable of the class.
Our goal is to use simple Java objects, rather than processing SQL statements and other database-related commands to add, delete, modify and check the database.
If we want to map the table into java code, we just need to create a class (bean) containing the same fields.
To better encapsulate, in addition to constructors we should declare all field variables as private, create accessors (getters and setters), one of which is the default constructor.
public class User { private Integer id; private String name; private String pass; private Integer age;}To map fields correctly, we should consider the NULL value in the database. For Java's original default values, such as int type, its default value is 0, so we should provide a new data type that can accommodate null values. We can replace INT by using a special type - encapsulation class, such as Integer.
Finally our class should look like this:
public class User { private Integer id; private String name; private String pass; private Integer age; public User() { } public User(String name, String pass, Integer age) { this.name = name; this.pass = pass; this.age = age; } public User(Integer id, String name, String pass, Integer age) { this.id = id; this.name = name; this.pass = pass; this.age = age; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPass() { return pass; } public void setPass(String pass) { this.pass = pass; }}A good practice is to provide the default empty constructor, a complete constructor and a complete constructor without id parameters.
Connect to the database
We can use an intermediate class to facilitate connection to the database. In this class, we will provide the database connection parameters such as database JDBC, URL, username and password, and define these variables as final (it will be better to get this data from properties or xml configuration files)
Provides a method to return a Connection object or return a null when the connection fails or throw a runtime exception.
public static final String URL = "jdbc:mysql://localhost:3306/testdb";public static final String USER = "testuser";public static final String PASS = "testpass";/** * Get connection object* @return Connection object*/public static Connection getConnection() { try { DriverManager.registerDriver(new Driver()); return DriverManager.getConnection(URL, USER, PASS); } catch (SQLException ex) { throw new RuntimeException("Error connecting to the database", ex); }}We can also include a main method in the class to test the connection. The complete class looks like this:
import com.mysql.jdbc.Driver;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;/** * Connect to Database * @author hany.sad */public class ConnectionFactory { public static final String URL = "jdbc:mysql://localhost:3306/testdb"; public static final String USER = "testuser"; public static final String PASS = "testpass"; /** * Get a connection to database * @return Connection object */ public static Connection getConnection() { try { DriverManager.registerDriver(new Driver()); return DriverManager.getConnection(URL, USER, PASS); } catch (SQLException ex) { throw new RuntimeException("Error connecting to the database", ex); } } /** * Test Connection */ public static void main(String[] args) { Connection connection = connectionFactory.getConnection(); }}Data access object
The DAO layer can perform CRUD operations. It can add, delete, modify and check our tables.
Our DAO layer interface should look like this:
public interface UserDao { User getUser(); Set<User> getAllUsers(); User getUserByUserNameAndPassword(); boolean insertUser(); boolean updateUser(); boolean deleteUser();}Find users
Users can query through any unique fields like ID, name, or email. In this example, we use ID to find the user. The first step is to create a connection through the connector class, and then execute the SELECT statement to get the user with its ID 7. We can use this statement to query the user:
SELECT * FROM user WHERE id=7
It's here that we make a dynamic statement to get the ID from the parameters.
By executing this query, a result set is obtained, where the user or null is saved. We can use the next() method of Resultset to detect whether there is a value. If true is returned, we will continue to use data getters to get user data from the ResultSet. When we encapsulate all the data into user, we return it. If a user with this ID does not exist or any other exception occurs (such as an invalid SQL statement), this method will return null.
public User getUser(int id) { Connection connection = connectionFactory.getConnection(); try { Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM user WHERE id=" + id); if(rs.next()) { User user = new User(); user.setId( rs.getInt("id") ); user.setName( rs.getString("name") ); user.setPass( rs.getString("pass") ); user.setAge( rs.getInt("age") ); return user; } } catch (SQLException ex) { ex.printStackTrace(); } return null;}It will be more convenient to use a separate method to extract data from the result set, because in many methods we will call it.
This new method will throw a SQLException and for the sake of limitations, it should be private:
private User extractUserFromResultSet(ResultSet rs) throws SQLException { User user = new User(); user.setId( rs.getInt("id") ); user.setName( rs.getString("name") ); user.setPass( rs.getString("pass") ); user.setAge( rs.getInt("age") ); return user;}Our above method should be modified into a new method:
public User getUser(int id) { Connection connection = connectionFactory.getConnection(); try { Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM user WHERE id=" + id); if(rs.next()) { return extractUserFromResultSet(rs); } } catch (SQLException ex) { ex.printStackTrace(); } return null;}Login method
The login operation is similar. We want to provide user and password alternative IDs, which will not affect the parameter list and query statements. If the username and password are correct, this method returns a valid user, otherwise null. Because there are many parameters, using PreparedStatement will be more useful.
public User getUserByUserNameAndPassword(String user, String pass) { Connector connector = new Connector(); Connection connection = connector.getConnection(); try { PreparedStatement ps = connection.prepareStatement("SELECT * FROM user WHERE user=? AND pass=?"); ps.setString(1, user); ps.setString(2, pass); ResultSet rs = ps.executeQuery(); if(rs.next()) { return extractUserFromResultSet(rs); } } catch (SQLException ex) { ex.printStackTrace(); } return null;}How to query all users
This method will return all users, so we should return them in an array-like container. But because we don't know how many records there are. It would be better to use a collection such as Set or List:
public Set getAllUsers() { Connector connector = new Connector(); Connection connection = connector.getConnection(); try { Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM user"); Set users = new HashSet(); while(rs.next()) { User user = extractUserFromResultSet(rs); users.add(user); } return users; } catch (SQLException ex) { ex.printStackTrace(); } return null;}Insert method
The Insert method will take the user as a parameter and use the PreparedStatement object to execute the SQL update statement. The executeUpdate method returns the number of rows affected. If we add a single line, it means that the method should return 1, if so, we return true, otherwise, we return false
public boolean insertUser(User user) { Connector connector = new Connector(); Connection connection = connector.getConnection(); try { PreparedStatement ps = connection.prepareStatement("INSERT INTO user VALUES (NULL, ?, ?, ?)"); ps.setString(1, user.getName()); ps.setString(2, user.getPass()); ps.setInt(3, user.getAge()); int i = ps.executeUpdate(); if(i == 1) { return true; } } catch (SQLException ex) { ex.printStackTrace(); } return false;}Update method
The update method is similar to the insert method. The only change is the SQL statement
public boolean updateUser(User user) { Connector connector = new Connector(); Connection connection = connector.getConnection(); try { PreparedStatement ps = connection.prepareStatement("UPDATE user SET name=?, pass=?, age=? WHERE id=?"); ps.setString(1, user.getName()); ps.setString(2, user.getPass()); ps.setInt(3, user.getAge()); ps.setInt(4, user.getId()); int i = ps.executeUpdate(); if(i == 1) { return true; } } catch (SQLException ex) { ex.printStackTrace(); } return false;}Delete method
The method to delete is to use a simple query like
DELETE FROM user WHERE ID = 7
Sending the query with the id parameter will delete this record. If successfully deleted, 1 will be returned
public boolean deleteUser(int id) { Connector connector = new Connector(); Connection connection = connector.getConnection(); try { Statement stmt = connection.createStatement(); int i = stmt.executeUpdate("DELETE FROM user WHERE id=" + id); if(i == 1) { return true; } } catch (SQLException ex) { ex.printStackTrace(); } return false;}Thank you for reading, I hope it can help you. Thank you for your support for this site!