[About JDBC that connects Java and SQL]

Introduction

Since it was difficult to implement JDBC (Java Database Connectivity) that connects Java and SQL, I will leave it as a memorandum.

Development environment

・ OS 　Windows10 ·editor 　Ecripse ・ Java 　OpenJDK 8 ・ SQL 　MySQL

1, What is JDBC

JDBC (Java Database Connectivity) is an API for connecting Java and RD and operating from Java. You can use the concept of JDBC to register, update, and delete data from Java to SQL, and to retrieve data from SQL. There are various types of DB such as Oracle, MySQL, SQL Server, etc., and the method of connecting to each DB depends on the type of DB. It will change, but if you connect to a DB via JDBC, you will be able to access it without worrying about the individuality of each DB.

There was an easy-to-understand article such as the history of JDBC, so I will post it. https://data.wingarc.com/what-is-api-16084

2, implementation

For implementation, follow the steps below to implement JDBC. (1) Loading the JDBC driver ② Establishing a connection with SQL ③ Send SQL statement ④ Acquisition of execution result ⑤ Disconnect

2-① (1) Loading the JDBC driver

Load the driver (create a Class object in memory) using the Class.forName method. -A method for loading Java bytecode files (extensions are class files). -Specify a character string (package name.class name) that represents the class as an argument. -If loading fails, a ClassNotFoundException will occur, so It is necessary to try ~ catch when using it. First declare the variable in the field so that you can use it as a variable later.

MessageDao.java

public class MessageDao {
  //Declare variables needed for connection
  private static final String DRIVER_NAME = "oracle.jdbc.driver.OracleDriver";
  private static final String JDBC_URL  = "jdbc:oracle:thin:@localhost:1521:ORCL";
  private static final String USER_ID   = "imuser";
  private static final String USER_PASS = "impass";

  MessageDao (){
        //JDBC driver loading
        try {
            Class.forName(DRIVER_NAME);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
}

If you run it in this state, you will get a java.lang.ClassNotFonudException error. To access DB from Java application First, you need to get the RDBMS-specific JDBC driver provided by the vendor. In addition, it is necessary to make settings so that the obtained JDBC driver can be used from a Java program. The JDBC driver can be downloaded from the Oracle website. https://www.oracle.com/technetwork/database/features/jdbc/jdbc-drivers-12c-download-1958347.htm

After the download is complete, there is a file called "ojdbc8.jar", which is the JDBC driver. Place "ojdbc8.jar" around C :.

To make the JDBC driver available to Java programs, it needs to be added to the build path, so follow the steps below.

1. 1. Right click on the Eclipse project.
2. [Properties] → [Java Build Path] → [Library]
3. 3. Click [Add External Jar] and select the ojdbc8.jar file of C :.
4. Click [Apply and Close].

If you press Execute and no exception occurs, the JDBC driver has been successfully loaded.

2-② ② Establishing a connection with SQL

Create a method getConnection () in the DriverManager class to establish a connection to the database. Update the connection URL, DB username and DB password to point to the database.

MessageDao.java

public class MessageDao {
  //Declare variables needed for connection
  private static final String DRIVER_NAME = "oracle.jdbc.driver.OracleDriver";
  private static final String JDBC_URL  = "jdbc:oracle:thin:@localhost:1521:ORCL";
  private static final String USER_ID   = "imuser";
  private static final String USER_PASS = "impass";

  MessageDao (){
        //JDBC driver loading
        try {
            Class.forName(DRIVER_NAME);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

　 public ArrayList<MessageDto> select() {
　　ArrayList<MessageDto> list = new ArrayList<>();

    //Connection class declaration
    Connection con = null;
    //Statement class declaration
    PreparedStatement ps = null;
    //Declaration of result set class
    ResultSet rs = null;

    //Access database
    try {
      //Establish a connection with the database.
      con = DriverManager.getConnection(JDBC_URL, USER_ID, USER_PASS);

　  }
  }
}
  

What is the DriverManager class? -JDBC driver manager class. Various functions for accessing the JDBC driver and operating the RDBMS are provided. If the getConnection method connects to the RDBMS and the connection is successful, a Connection object with information about the DB connection is returned as the return value. The connection is executed by specifying the connection information to the DB (connection destination DB, user ID, password) in the argument.

A box that will be used later to store the results extracted from SQL, ArrayList list = new ArrayList<>(); Create an empty box with MessageDto type ArrayList as list.

Since we need a DTO to define the data, create a MessageDto.java class so that the data can be defined. 2-③ ③ Send SQL statement Ask RDB to execute the SQL statement with the executeQuery method and executeUpdate method of the Statement type object.

MessageDao.java

public class MessageDao {
  //Declare variables needed for connection
  private static final String DRIVER_NAME = "oracle.jdbc.driver.OracleDriver";
  private static final String JDBC_URL  = "jdbc:oracle:thin:@localhost:1521:ORCL";
  private static final String USER_ID   = "imuser";
  private static final String USER_PASS = "impass";

  MessageDao (){
        //JDBC driver loading
        try {
            Class.forName(DRIVER_NAME);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

　 public int insert(MessageDto dto) {

    //Connection class declaration
    Connection con = null;
    //Statement class declaration
    PreparedStatement ps = null;
    //Variable for processing result (number of cases)
    int result = 0;
    //Access database
    try {
      //Establish a connection with the database.
      con = DriverManager.getConnection(JDBC_URL, USER_ID, USER_PASS);

//SQL statement generation (SELECT statement)
   StringBuilder builder = new StringBuilder();
      //SQL statement SELECT*Get all cases with
      builder.append("SELECT *");
      builder.append("FROM ");
      builder.append("  MESSAGE_BOARD ");
      builder.append("ORDER BY ");
      builder.append("  ID DESC ");

      //Store SQL statement in statement class
      ps = con.prepareStatement(builder.toString());

　  }
  }
}
  

The reason why the StringBuilder type builder is declared as a variable is to combine strings with the append method. Strings can be combined with the "+ operator", but since the processing speed of the program is faster using the append method of StringBuilder, a variable of type StringBuilder is declared and combined using the append method. Append.

2-④ ④ Acquisition of execution result

Gets the data of the extraction result of the SELECT statement sent using the ResultSet object.

MessageDao.java

public class MessageDao {
  //Declare variables needed for connection
  private static final String DRIVER_NAME = "oracle.jdbc.driver.OracleDriver";
  private static final String JDBC_URL  = "jdbc:oracle:thin:@localhost:1521:ORCL";
  private static final String USER_ID   = "imuser";
  private static final String USER_PASS = "impass";

  MessageDao (){
        //JDBC driver loading
        try {
            Class.forName(DRIVER_NAME);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

　 public int insert(MessageDto dto) {

    //Connection class declaration
    Connection con = null;
    //Statement class declaration
    PreparedStatement ps = null;
    //Variable for processing result (number of cases)
    int result = 0;
    //Access database
    try {
      //Establish a connection with the database.
      con = DriverManager.getConnection(JDBC_URL, USER_ID, USER_PASS);

//SQL statement generation (SELECT statement)
   StringBuilder builder = new StringBuilder();
      //SQL statement SELECT*Get all cases with
      builder.append("SELECT *");
      builder.append("FROM ");
      builder.append("  MESSAGE_BOARD ");
      builder.append("ORDER BY ");
      builder.append("  ID DESC ");

      //Store SQL statement in statement class
      ps = con.prepareStatement(builder.toString());

      //Execute SQL and store the acquisition result in the result set (rs)
      rs = ps.executeQuery();

　　　while (rs.next()) {
       //Instantiate Dto to store acquisition result
       MessageDto dto = new MessageDto();
       //Store acquisition result in dto
       dto.setId       (rs.getInt("id"));
       dto.setName     (rs.getString("name"));
       dto.setMessage  (rs.getString("message"));
       dto.setCreatedAt(rs.getTimestamp("created_at"));
       //Fill the list with the data for one record stored in Dto
       list.add(dto);
     }


　  }
  }
}
  

ResultSet object is received as the return value of executeQuery method of Statement class It is an object that can be, and the data of the extraction result of the sent SELECT statement is stored in rs. The execution result received from SQL can be looped and fetched one record at a time. After the setting is completed, add the value set in the empty list generated earlier.

2-⑤ ⑤ Disconnect

Since it is necessary to explicitly disconnect when the access to the database is completed, use the close method for each of the Connection object, Statement object, and ResultSet object to disconnect (close).

• If you do not close the connection, the connected state will continue, and the problem will not become apparent during development, but when the operation starts and the number of accesses and the amount of data increase unexpectedly, the load will be applied and the problem will only occur It may lead to a situation where it occurs. Be sure to write a process to close.

MessageDao.java

public ArrayList<MessageDto> select() {

    ArrayList<MessageDto> list = new ArrayList<>();

    //Connection class declaration
    Connection con = null;
    //Statement class declaration
    PreparedStatement ps = null;
    //Declaration of result set class
    ResultSet rs = null;

    //Access database
    try {
      //Connect to the database
      con = DriverManager.getConnection(JDBC_URL, USER_ID, USER_PASS);

      StringBuilder builder = new StringBuilder();
      builder.append("SELECT ");
      builder.append("   id ");
      builder.append("  ,name ");
      builder.append("  ,message ");
      builder.append("  ,created_at ");
      builder.append("FROM ");
      builder.append("  message_board ");
      builder.append("ORDER BY ");
      builder.append("  ID DESC ");
      //Store SQL statement in statement class
      ps = con.prepareStatement(builder.toString());
      //Execute SQL and store the acquisition result in the result set
      rs = ps.executeQuery();
      //Extract data one record at a time from the result set
      while (rs.next()) {
        //Instantiate Dto to store acquisition result
        MessageDto dto = new MessageDto();
        //Store the acquisition result in Dto
        dto.setId       (rs.getInt("id"));
        dto.setName     (rs.getString("name"));
        dto.setMessage  (rs.getString("message"));
        dto.setCreatedAt(rs.getTimestamp("created_at"));
        //Fill the list with the data for one record stored in Dto
        list.add(dto);
      }

    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      try {
        if (rs != null) {
          rs.close();
        }
        if (ps != null) {
          ps.close();
        }
        if (con != null) {
          con.close();
        }
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    //Return the acquisition result to the caller
    return list;
  }
  

This completes the JDBC implementation. Adding, updating, and deleting SQL changes the way of writing connections a little, so I will summarize about that and the JDBC template. I hope it will be of some help to those who implement JDBC in the future.

References

-Https://docs.oracle.com/cd/E96517_01/tdpjd/creating-java-bean-implementation-jdbc-connection.html ・ Https://data.wingarc.com/what-is-api-16084