Of Stack

java USES JDBC to build simple data access layer instance details

  • 2020-05-17 05:24:52
  • OfStack

The purpose of this tutorial is to access tables in the database using a separate layer written by Java, commonly known as the data access layer (DAL)

The biggest benefit of using DAL is that it simplifies database access by directly using 1 method like insert() and find() instead of always linking first and then executing some queries.

This layer handles all database-related calls and queries internally.

Create a database

We want to create a simple table for the user that we can create using these fields

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 mapping to a table in the database, with each column in the table corresponding to a member variable of the class.

Our goal is to use a simple Java object instead of processing SQL statements and other database-related commands to add, delete, and change the database.

We want to map the table to the java code by simply creating a class that contains the same fields (bean)

For better encapsulation, we should declare all field variables private except constructors, creating accessors (getter and setter), one of which is the default constructor. 


public class User {
  private Integer id;
  private String name;
  private String pass;
  private Integer age;
}

To map the fields correctly, we should consider the NULL value in the database. The original default value for Java, such as the int type, is 0, so we should provide a new data type that can hold 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 full constructor, and a full constructor without the id argument.

Connect to database

We can use an intermediate class to easily connect to the database. In this class, we will provide database connection parameters such as JDBC, URL, username and password, and define these variables as final (it would be better to get these data from properties or xml configuration files).

Provide a method that returns an Connection object, or an null object when a 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";
/**
 *  To obtain 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 master method in the class to test the connection. The full 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.said
 */
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 do CRUD operations. It can add, delete and change to our table.

Our DAO layer interface should look like this: 


public interface UserDao {
  User getUser();
  Set<User> getAllUsers();
  User getUserByUserNameAndPassword();
  boolean insertUser();
  boolean updateUser();
  boolean deleteUser();
}

To find the user

Users can query through any unique 1 field such as ID, name or mailbox. In this example, we use ID to find the user. Step 1 is to create an connection through the connector class, and then execute the SELECT statement to get the user whose ID is 7. We can query the user with this statement:

SELECT * FROM user WHERE id=7

Right here, we made a dynamic statement to get ID from the parameter.

By executing this query, you get a result set with either the user or null saved. We can detect the presence of a value by Resultset's next() method. If we return true, we will continue to retrieve user data from ResultSet using data getters. When we have encapsulated all the data in user, we return it. This method returns null if no user of this ID exists or if any other exception (such as an invalid SQL statement) occurs. 


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 is more convenient to use a separate method to extract data from the result set, because we will call it in many methods.

This new method will throw SQLException and, to limit its use within the class, 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 to the 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 replacement ID, which will not affect parameter lists and query statements. If the username and password are correct, this method returns a valid user, otherwise null. Because there are so many parameters, PreparedStatement is 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;
}

Query the method for 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 takes the user as an argument and USES the PreparedStatement object to execute the SQL update statement. The executeUpdate method returns the number of affected rows. If we add a single line, that means the method should return 1, if so, we return true, otherwise, we return false 


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;
  }
}
0

Update method

The update method is similar to the insert method. The only thing that changes with 1 is the SQL statement 


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;
  }
}
1

Delete methods

Delete by using a simple query like

DELETE FROM user WHERE ID = 7

Sending the query with the id parameter will delete the record. If successfully deleted, 1 will be returned 


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;
  }
}
2

Thank you for reading, I hope to help you, thank you for your support of this site!

Related articles: