JDBC operation data and DAO design pattern in actual combat

Using JDBC to manipulate data

1. Using JDBC to write large text data to the database

Purpose: to java and other text files are written to the corresponding fields of the corresponding table by connecting the database with JDBC.
1. Table structure

2. Documents

3. Implementation code for inserting text

package LoadFileTest;

import driver.JDBCUtils;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Use JDBC to insert the text file into the database and read it out
 * Text: file_ Test. Java type text type
 * The database data type used is: Text
 * Involved: I/O flow operation
 */
public class Text_Test {
    public static void main(String[]args){
        insert_Text();
    }
    private static void insert_Text() {
        Connection conn =null;
        PreparedStatement ps = null;
        ResultSet re = null;
        try{
            conn = JDBCUtils.getConnect();
            System.out.println("connect database ....");
            String sql = "insert into big_text(Big_text) Values(?)";
            ps = conn.prepareStatement(sql);
            File f = new File("src/LoadFileTest/Text_Test.java");//Create file object
            BufferedReader bufferedReader = new BufferedReader(new FileReader(f));//Decorative flow
            ps.setCharacterStream(1,bufferedReader,f.length());//read
            ps.executeUpdate();
            System.out.println("insert successfully!");
        } catch (SQLException | FileNotFoundException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.free(conn,ps,re);
        }
    }
}

4. Operation results

5. Read the text and save it to disk E and realize the code

private static void read_Text() {
        Connection conn =null;
        Statement st = null;
        ResultSet re = null;
        try{

            conn = JDBCUtils.getConnect();
            System.out.println("connect database ....");
            String sql = "select big_text from big_text where id=1";
            st = conn.createStatement();
            re = st.executeQuery(sql);
            File f = new File("E:\\Text_Test_bak.java");//Saved file object
            BufferedReader bf;
            BufferedWriter bw = new BufferedWriter(new FileWriter(f));
            while(re.next()){
                bf = new BufferedReader(re.getCharacterStream("big_text"));
                String buff;
                while((buff=bf.readLine())!=null){
                    bw.write(buff);
                    bw.newLine();
                }
                bw.close();//Refresh
                bf.close();
            }
            System.out.println("read successfully!");

        } catch (SQLException | IOException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.free(conn,st,re);
        }
    }

Summary:
The large TEXT types of different databases are different, so we need to be flexible. TEXT data type of MySQL. String types in MySQL include CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, ENUM, SET, etc.

Simultaneous learning method: setCharacterStream(), getCharacterStream()
Three read result sets (text type data)

 while(re.next()){
                //String s = re.getString("big_test");// This method also works
               // This is OK
               /*Clob clob =  re.getClob(1);//Get the first column of data
               bf = new BufferedReader( clob.getCharacterStream());*/
                bf = new BufferedReader(re.getCharacterStream("big_text"));
                String buff;
                while((buff=bf.readLine())!=null){
                    bw.write(buff);
                    bw.newLine();
                }
                bw.close();
                bf.close();
            }

2. Use JDBC to write large byte data to the database

Large byte data, such as pictures, videos, etc. The code process is similar to 1. The main reason is that the data type of the operation has changed. The data types used in MySQL are: BINARY strings, including BIT, BINARY, VARBINARY, TINYBLOB, BLOB, mediablob and LONGBLOB.

And the corresponding I/O stream is changed to byte stream.

3. Use of JDBC and DAO design idea

3.1 three tier architecture

Introduction to three-tier architecture: Baidu Encyclopedia - detailed explanation of three-tier architecture
In order to comply with the idea of "high cohesion and low coupling", the three-tier architecture divides each functional module into three-tier architecture: presentation layer (UI), business logic layer (BLL) and data access layer (DAL). Each layer uses interfaces to access each other, and uses the entity class (Model) of the object Model as the carrier of data transmission. The entity classes of different object models generally correspond to different tables of the database, The attribute of the entity class is consistent with the field name of the database table.

The purpose of the three-tier architecture is to "high cohesion and low coupling". Developers have a clearer division of labor and focus more on the analysis, design and development of the core business logic of the application system, so as to speed up the progress of the project, improve the development efficiency and facilitate the renewal and maintenance of the project.

Presentation layer
The presentation layer, also known as the presentation layer UI, is located at the top of the three-tier architecture and is in direct contact with users, mainly the Wed browsing page in the B/S information system. As a Wed browsing page, the main function of the presentation layer is to realize the input and output of system data. In this process, the data can be transmitted to the BBL system for data processing without the help of logical judgment operation. After processing, the processing results will be fed back to the presentation layer. In other words, the presentation layer is to realize the user interface function, convey and feed back the user's needs, and debug with BLL or Models to ensure the user experience.

Business logic layer
The function of the business logic layer BLL is to make logical judgment and execute operations on specific problems. After receiving the user instructions of the presentation layer UI, it will connect the data access layer DAL. The access layer is located in the middle of the presentation layer and the data layer in the three-tier architecture. At the same time, it is also a bridge between the presentation layer and the data layer to realize the data connection and instruction transmission between the three layers. It can logically process the received data and realize the modification, acquisition and Delete and other functions, and feed back the processing results to the UI of the presentation layer to realize the software functions.

Data access layer DAL
The data access layer DAL is the main control system of the database, which realizes the operations of data addition, deletion, modification and query, and feeds back the operation results to the business logic layer BBL. In the process of actual operation, the data access layer has no logical judgment ability. In order to realize the preciseness of code writing and improve the degree of code reading, general software developers will write Data AccessCommon in this layer to ensure the DAL data processing function of the data access layer.

3.2 DAO design mode (Data Access Object, data access interface)

definition
DAO mode is actually a combination of two modes, namely Data Accessor mode and Active Domain Object mode. Data Accessor mode realizes the separation of data access and business logic; Active Domain Object mode realizes the object-oriented encapsulation of business data.

It should be noted that DAO design patterns are the design patterns in Java EE, not the 23 design patterns in Java SE.

Scenarios and requirements
In Java programs, it is often necessary to persist data and obtain persistent data, but there are many problems in the process of data persistence (such as different data sources, different storage types, different suppliers, different access methods, etc.). How can we use a unified interface for data persistence?

terms of settlement

The jdbc operation is separated, that is, the database operation is separated from the business. javabean separates the view from the business, and dao separates the database operation from the business logic

Components of DAO
A typical DAO implementation has the following components:

  • 1. DAO interface
    One DAO interface; Define the interface of operation, which is used to declare the atomization operation of database, add, delete, query and modify
  • 2,Imple
    A concrete class that implements DAO interface; The function that really realizes the DAO interface is just to process data.
  • 3,DTO
    Data transfer object (DTO): sometimes called value object (VO) or domain. It corresponds to the table data of the database. VO(Value Object): it is used to store some data classes in the project that need to be operated on.
  • 4,Proxy
    Proxy implementation class is used to call the operation of real objects through proxy class
  • 5,Factory
    DAOFactory: factory class, containing getInstance() to create a DAOImpl class

The role of DAO in three-tier model
DAO plays an important role in the three-tier structure mode. The presentation layer calls DAO through the logic layer, and then lets DAO call the data layer. In this way, the data layer is well separated from the other two layers, and the security and maintainability are higher. Then, a new DAO2 can be established in the logic layer and DAO layer. The function of this DAO2 is to avoid the direct contact of the outside world with the internal interface, and realize the application of the interface mainly through the factory method. This purpose is also to improve the security and show a more hierarchical sense.

  • 1. DAO actually uses the combination factory mode to solve problems, and does not bring new functions, so what we learn is actually an idea.
  • 2. DAO theoretically has no limit on the number of layers.
  • 3. In theory, the layers of DAO have no sequence.

Reference blog
[detailed explanation] of DAO design mode and [application] of common design modes
DAO design pattern
MySQL database learning notes (11)

3.3. Complete the test code implementation of the whole Dao based on the DAO design idea

1) The database designed first - bank, has fields: id, name, wealth and money

2) Create the corresponding package with the following structure:

3) First create the User class, that is, DTO

package DAO_design.user;

/**
 * Create a corresponding table of data in a database
 * Data transfer object (DTO)
 */

public class User {
    private int id;
    private String name;
    private float money;

    public User() {
    }

    public float getMoney() {
        return money;
    }

    public void setMoney(float money) {
        this.money = money;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    @Override
    public String toString() {
        return "User [id=" + id + ", username=" + name + ", money=" + money + "]";
    }
}

4) Then create the DAO interface

package DAO_design.DAO;

import DAO_design.user.User;

/**
 * DAO Interface
 */
public interface Dao {
    void addUser(User user);//Add user
    void deleteUser(String userName);//Delete user
    void updateUser(User user);//Update user information
    User findUser(String userName);//Query user
    boolean toUser(User user,User toUser,float money);//Transfer business
}

5) Then create DAOImpl and implement the interface

package DAO_design.DAOImpl;

import DAO_design.DAO.Dao;
import DAO_design.DAO.DaoException;
import DAO_design.user.User;
import driver.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * The interface implementation class is a function that truly implements the DAO interface and only processes data.
 */

public class DaoImpl implements Dao {
    private User user;
    @Override
    public void addUser(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = JDBCUtils.getConnect();
            String sql = "insert into bank(name,money) Values(?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1,user.getName());
            ps.setFloat(2,user.getMoney());
            ps.executeUpdate();
        } catch (SQLException e) {
           throw new DaoException(e.getMessage());//Never print stack traces or throw compile time exceptions
            //It is not conducive to changing other databases or modifying various interfaces in the future. I don't know what's wrong there.
        }finally {
            JDBCUtils.free(conn,ps,rs);
        }

    }

    @Override
    public void deleteUser(String userName) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = JDBCUtils.getConnect();
            String sql = "delete from bank where name=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,userName);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new DaoException(e.getMessage());//Never print stack traces or throw compile time exceptions
            //It is not conducive to changing other databases or modifying various interfaces in the future. I don't know what's wrong there.
        }finally {
            JDBCUtils.free(conn,ps,rs);
        }

    }

    @Override
    public void updateUser(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = JDBCUtils.getConnect();
            String sql = "update bank set money=? where name=?";
            ps = conn.prepareStatement(sql);
            ps.setFloat(1,user.getMoney());
            ps.setString(2,user.getName());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new DaoException(e.getMessage());//Never print stack traces or throw compile time exceptions
            //It is not conducive to changing other databases or modifying various interfaces in the future. I don't know what's wrong there.
        }finally {
            JDBCUtils.free(conn,ps,rs);
        }
    }

    @Override
    public User findUser(String userName) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = JDBCUtils.getConnect();
            String sql = "select * from bank where name=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,userName);
            rs = ps.executeQuery();
            while(rs.next()){
                user = new User();
                user.setId(rs.getInt("id"));
                user.setMoney(rs.getFloat("money"));
                user.setName(rs.getString("name"));
            }
            return user;
        } catch (SQLException e) {
            throw new DaoException(e.getMessage());//Never print stack traces or throw compile time exceptions
            //It is not conducive to changing other databases or modifying various interfaces in the future. I don't know what's wrong there.
        }finally {
            JDBCUtils.free(conn,ps,rs);
        }

    }

    @Override
    public boolean toUser(User user,User toUser,float money) {
        if(money>0.0001){
            user.setMoney(user.getMoney()-money);
            toUser.setMoney(toUser.getMoney()+money);
            updateUser(user);
            updateUser(toUser);
            return true;
        }else
            throw new RuntimeException("Transfer amount cannot be negative!");
    }
}

5) Create custom runtime exception DaoException

package DAO_design.DAO;

/**
 * DAO How do design patterns handle exceptions? Don't just print or throw compile time exceptions, which will affect future maintenance
 */
public class DaoException extends RuntimeException {
    public DaoException(Throwable cause) {
        super(cause);
    }

    protected DaoException(String message, Throwable cause, boolean enableSuppression, boolean writableStackTrace) {
        super(message, cause, enableSuppression, writableStackTrace);
    }

    public DaoException(String message) {
        super(message);
    }

    public DaoException(String message, Throwable cause) {
        super(message, cause);
    }

    public DaoException() {
        super();
    }
}

6) Finally, create the factory class DaoFactory

package DAO_design.Factory;

import DAO_design.DAOImpl.DaoImpl;

/**
 * Create a factory class for producing DAO interface instances
 */
public class DaoFactory {
    public static DaoImpl getDaoImp(){
        return new DaoImpl();
    }
}

Test code:

package DAO_design.Test;

import DAO_design.DAOImpl.DaoImpl;
import DAO_design.Factory.DaoFactory;
import DAO_design.user.User;

public class DaoTest {
    public static void main(String[]args){
        User user = new User();
        user.setName("Wang erxiao");
        user.setMoney(1000.0f);
        System.out.println(user.toString());
        DaoImpl daoImpl = DaoFactory.getDaoImp();
        //insert data
        User toUser = new User();
        toUser.setName("Little fat boy");
        toUser.setMoney(1000000);
        //Wang erxiao transferred 100 yuan to little fat boy
        daoImpl.toUser(user,toUser,100);//
        //daoImpl.updateUser(user);
       // daoImpl.deleteUser("Wang erxiao");
        //User finuser = daoImpl.findUser("Wang erxiao");
       // System.out.println(finuser.toString());

    }
}

Add user

  User user = new User();
  user.setName("Wang erxiao");
user.setMoney(1000.0f);
System.out.println(user.toString());
DaoImpl daoImpl = DaoFactory.getDaoImp();
//insert data
daoImpl.addUser(user);

Delete user

daoImpl.deleteUser("Wang erxiao");

Tags: Java Programming Database MySQL Design Pattern

Posted by schoi on Wed, 25 May 2022 18:54:45 +0300