Data access and DAO mode of MySQL

Technology bloghttps://blog.ideaopen.cn/

Properties configuration file

In the actual development process of different business scenarios, the IP address of the database server and the user name or password accessing the database often change, which is troublesome to maintain and modify. In order to avoid this situation, there is an important Properties class in Java, which can read the Java configuration file, so that the commonly used configuration information can be unloaded into the configuration file for programmers to maintain and modify.

  1. add to. properties file

Select the src folder and right-click, New → File, and name it database properties

  1. Edit profile
 driver=com.mysql.cj.jdbc.Driver
 url=jdbc:mysql://localhost:3306/hospital?serverTimezone=GMT-8
 username=root
 password=123456

The format does not need to end with a semicolon. It is similar to the Map structure in Java. Before the equal sign is the key and after the equal sign is only. In a configuration file, the key should be unique, but the value can not be unique. The determined value can be found through a key.

  1. Read configuration file

Using Java The util Properties class under the util package reads the configuration file.

The Properties class inherits from the Hashtable class. Its common methods are as follows:

methoddescribe
String getProperty (String key)Search for attributes in this attribute list with the specified key
Object setProperty(String key, String value)Set the key value pair by calling the put() method of the base class Hashtable
void load(InputStream streamIn) throws IOExceptionRead the attribute list (key and element pairs) from the input stream
void clear()Clear the loaded key value pair, which is provided by the base class Hashtable

DAO

brief introduction

Dao (data access objects) refers to the working mode between business logic and persistent data to realize the access to persistent data.

Generally speaking, it is to encapsulate all database operations and provide corresponding interfaces to the outside world.

Advantages of DAO

  1. Improve code reusability

  2. Isolation

    1. Data access code and business logic code are isolated
    2. Isolated different database implementations
  3. Easy maintenance

Composition of DAO

  1. Database connection and closing tool class: it avoids the repeated use of database connection and closing code and is convenient for modification.
  2. Entity class: used to store and transfer object data.
  3. DAO interface: defines all operations on the database as abstract methods, which can provide a variety of implementations
  4. DAO implementation class: the specific implementation of DAO interface definition method is given for different databases.

Tool class connection database

 package cn.hospital.dao;
 ​
 import java.io.IOException;
 import java.io.InputStream;
 import java.sql.*;
 import java.util.Properties;
 ​
 /**
  * The common tool class of database is the base class
  */
 public class Dao {
     private Connection conn;
     private PreparedStatement ps;
     private static String driver;
     private static String url;
     private static String userName;
     private static String pwd;
     
     // Omit encapsulation
 ​
     static{
         init();
     }
 ​
     public static void init(){
         //Create Properties object
         Properties pt = new Properties();
         String filePath = "database.properties";
         InputStream is = Dao.class.getClassLoader().getResourceAsStream(filePath);
         try {
             pt.load(is);
             //Get the corresponding value according to the key
             driver = pt.getProperty("driver");
             url = pt.getProperty("url");
             userName = pt.getProperty("user");
             pwd = pt.getProperty("pwd");
         } catch (IOException e) {
             e.printStackTrace();
         }
     }
 ​
     public Connection getConnection(){
         if(conn == null) {
             try {
                 // 1. Load drive
                 Class.forName(getDriver());
                 // 2. Get connection
                 conn = DriverManager.getConnection(getUrl(),getUserName(),getPwd());
             } catch (ClassNotFoundException | SQLException e) {
                 e.printStackTrace();
             }
         }
         return conn;
     }
 ​
     /**
      * close
      * @param rs Result set
      * @param ps Interface
      * @param conn Database connection
      */
     public void closeAll(ResultSet rs , PreparedStatement ps , Connection conn){
         if(null!= rs){
             try {
                 rs.close();
             } catch (SQLException e) {
                 e.printStackTrace();
             }
         }
 ​
         if(null!= ps){
             try {
                 ps.close();
             } catch (SQLException e) {
                 e.printStackTrace();
             }
         }
 ​
         if(null!= conn){
             try {
                 conn.close();
             } catch (SQLException e) {
                 e.printStackTrace();
             }
         }
     }
 ​
     public int executeUpdate(String sql , Object ... prams){
         // Number of rows affected
         int result = 0;
         // 1. Get connection
         conn = getConnection();
         try {
             // 2. Send sql statement
             ps = conn.prepareStatement(sql);
             // 3. Judge whether there are parameters
             if (prams != null) {
                 // 4. Loop traversal parameters
                 for (int i = 0; i < prams.length;i++){
                     ps.setObject(i+1,prams[i]);
                 }
                 // 5. Execution returns the number of affected rows
                 result = ps.executeUpdate();
             }
         } catch (SQLException e) {
             e.printStackTrace();
         }
         return result;
     }
     
 }

perhaps

Rookie tutorial - connecting to the database

 public class BaseDao {
     private static String driver="com.mysql.jdbc.Driver";
     private static String url="jdbc:mysql://127.0.0.1:3306/epet";
     private static String user="root";
     private static String password="root";
         static {
             try {
                 Class.forName(driver);
             } catch (ClassNotFoundException e) {
                 e.printStackTrace();
             }
         }
         
     public static Connection getConnection() throws SQLException {
         return DriverManager.getConnection(url, user, password);    
     }
     
     public static void closeAll(Connection conn,Statement stmt,ResultSet rs) throws SQLException {
         if(rs!=null) {
             rs.close();
         }
         if(stmt!=null) {
             stmt.close();
         }
         if(conn!=null) {
             conn.close();
         }
     }
     
 ​
     public int executeSQL(String preparedSql, Object[] param) throws ClassNotFoundException {
         Connection conn = null;
         PreparedStatement pstmt = null;
         /* Process SQL, execute SQL */
         try {
             conn = getConnection(); // Get database connection
             pstmt = conn.prepareStatement(preparedSql); // Get PreparedStatement object
             if (param != null) {
                 for (int i = 0; i < param.length; i++) {
                     pstmt.setObject(i + 1, param[i]); // Setting parameters for precompiled sql
                 }
             }
         ResultSet num = pstmt.executeQuery(); // Execute SQL statement
         } catch (SQLException e) {
             e.printStackTrace(); // Handling SQLException
         } finally {
             try {
                 BaseDao.closeAll(conn, pstmt, null);
             } catch (SQLException e) {    
                 e.printStackTrace();
             }
         }
         return 0;
     }
     
 }

Entity class

 package entity;
 ​
 import java.util.Date;
 ​
 /**
  * Entity class
  */
 public class Hospital {
     private int patientID;
     private String patientName;
     private String password;
     private Date birthDate;
     private String gender;
     private String phoneNum;
     private String email;
     private String identityNum;
     private String address;
     
     // Omit encapsulation, no parameters, with parameters
 }

DAO interface

 package cn.hospital.dao;
 ​
 import entity.Hospital;
 import java.util.List;
 ​
 public interface HospitalDao {
     /**
      * Add data
      */
     public int addPaitent(Hospital h);
     
     /**
      * Delete data in the table according to id
      */
     public int deletePaitent(int id);
 ​
     /**
      * Update value
      */
     public int updatePaitent(Hospital h);
 ​
     /**
      * Query the information in the checklist
      */
     public List<Hospital> getHospitalList();
 }
 ​

DAO implementation class

 package cn.hospital.dao.impl;
 ​
 import cn.hospital.dao.Dao;
 import cn.hospital.dao.HospitalDao;
 import entity.Hospital;
 ​
 import java.sql.Connection;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.Date;
 import java.sql.SQLException;
 import java.util.ArrayList;
 ​
 import java.util.List;
 ​
 /**
  * DAO Implementation class
  */
 public class HospitalDemo extends Dao implements HospitalDao {
 ​
     @Override
     public int addPaitent(Hospital h) {
         String sql = "insert into patient(patientName, password, birthDate, gender, phoneNum, email, identityNum, address) " +
                      "values(?,?,?,?,?,?,?,?)";
         return this.executeUpdate(sql,h.getPatientName(),h.getPassword(),h.getBirthDate(),h.getGender(),h.getPhoneNum(),h.getEmail(),h.getIdentityNum(),h.getAddress());
     }
 ​
     @Override
     public int deletePaitent(int id) {
         String sql = "delete from patient where patientID = ?";
         return this.executeUpdate(sql,id);
     }
 ​
     @Override
     public int updatePaitent(Hospital h) {
         String sql = "update patient set patientName = ? where patientName = 'Zhang San'";
         return this.executeUpdate(sql,h.getPatientName());
     }
 ​
     /**
      * Query the information in the checklist
      */
     @Override
     public List<Hospital> getHospitalList() {
         Connection conn = null;
         PreparedStatement ps = null;
         ResultSet rs = null;
         List<Hospital> list = new ArrayList<>();
         String sql = "select * from patient";
         try {
             // 1. Get connection
             conn = getConnection();
             // 2. Send sql
             ps = conn.prepareStatement(sql);
             // 3. Execute sql
             rs = ps.executeQuery();
             // 4. Loop traversal output
             while (rs.next()){
                 //Create a hospital object to store query information
                 Hospital hospital = new Hospital();
                 hospital.setPatientID(rs.getInt("patientID"));
                 hospital.setPatientName(rs.getString("patientName"));
                 hospital.setBirthDate(rs.getDate("birthDate"));
                 hospital.setPassword(rs.getString("password"));
                 hospital.setGender(rs.getString("gender"));
                 hospital.setPhoneNum(rs.getString("phoneNum"));
                 hospital.setEmail(rs.getString("email"));
                 hospital.setIdentityNum(rs.getString("identityNum"));
                 hospital.setAddress(rs.getString("address"));
                 //Add objects to the collection
                 list.add(hospital);
             }
         } catch (SQLException e) {
             e.printStackTrace();
         } finally {
             this.closeAll(rs,ps,conn);
         }
         return list;
     }
 }

 

Tags: Java Apache

Posted by randomthinker on Sat, 21 May 2022 03:56:24 +0300