Java uses JDBC to build a simple example of a data access layer _java

The purpose of this tutorial is to use a separate layer written in Java to access the tables in the database, which is often called the data Access Layer (DAL)

The biggest advantage of using the DAL is that it simplifies access to the database by using several methods like insert () and find (), rather than always doing a link first and then executing some queries.

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

Creating a Database

We want to create a simple table for the user that we can use to create

ID int
Name varchar (200)
Password varchar (200)
Age int

Data Transfer Objects

This layer should contain a simple class called a data Transfer object (DTO). This class is just a simple map corresponding to a 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 instead of processing SQL statements and other database-related commands to make database additions and deletions.

We want to map the table to Java code, just create a class (bean) that contains the same fields

To better encapsulate, in addition to the constructor we should declare that all field variables are private, create accessors (getter and setter), and one of them is the default constructor.

public class User {
  private Integer ID;
  private String name;
  Private String Pass;
  Private Integer age;
}

In order to map the fields correctly, we should consider the null values in the database. For Java's original default values, such as the int type, whose default value is 0, we should provide a new data type that can hold a null value. We can replace INT by using special types--encapsulating classes, such as integers.

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 a default null constructor, a complete constructor, and a complete constructor without an ID parameter.

Connecting to a database

We can use an intermediate class to facilitate the connection to the database, in which 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 the data from properties or XML configuration files).

Provides a method to return a connection object or to return a null when a connection fails, or to throw a Run-time 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 { C10/>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 is 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 GetConnect Ion ()
  {
   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 Objects

The DAO layer can do crud operations. It can be added to our table to check.

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 by any unique field, such as ID, name, or mailbox. In this example, we use IDs to find users. The first step is to create a connection through the connector class, and then execute the SELECT statement to obtain a user with an ID of 7, which we can use to query the user:

SELECT * from user WHERE id=7

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

By executing this query, you get a result set that holds the user or null. We can use the resultset next () method to detect if there is a value. If true, we will continue to use data getters to get user data from resultset. When we encapsulate all the data into user, we return it. This method returns null if no user with this ID exists or any other exception occurs, such as an invalid SQL statement.

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 a result set, because in many ways we will call it.

This new method will throw SqlException and should be private in order to limit its use only within the class:

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

The method above 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;
}

Landing method

The login operation is similar. We want to provide user and password substitution IDs, 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 a lot of 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;
}

Ways 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 takes the user as a parameter and uses 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, and 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 insertion method. The only thing that changes 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 of deletion is to use a simple query like

DELETE from user WHERE ID = 7

Sending the query with an ID parameter deletes the record. If successful deletion will return 1

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 to help you, thank you for your support for this site!