Spring Boot JAP + MySQL practice

Reference Using STS to create a Spring Boot project Create a new Spring Boot project, and select Web, JPA, MySQL and other options when creating the project.

  • Configuration information in application.yml
  • shitibean based on annotation
  • Simple application based on JPA interface
  • Multi table and multi condition dynamic combined query and paging and sorting

Configuration information in application.yml

spring:
  datasource:
    url: jdbc:mysql://192.168.10.58:3306/jianshu?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
    username: root
    password: infcn123
    platform: mysql
  jpa:
    database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
    hibernate:
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
    database: mysql
    show-sql: true
    
  http:
    encoding:
      force: true
      charset: UTF-8
      enabled: true
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    joda-date-time-format: yyyy-MM-dd HH:mm:ss
    time-zone:
      GMT+8
    serialization:
      fail-on-empty-beans: false
security:
  basic:
    enabled: false
server:
  tomcat:
    uri-encoding: UTF-8
  port: 8080

Entity Bean configuration

User configuration

import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.validation.constraints.Size;
import org.springframework.data.annotation.CreatedDate;
import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Data;
import lombok.experimental.Accessors;

/**
 * System users
 * 
 * @author Li Qinghai
 */
@Data
@Accessors(chain = true)
@Entity
@Table(name = "SYS_USER")
public class User implements java.io.Serializable {
    private static final long serialVersionUID = 1L;
    /***/
    @Id
    @Column(name = "ID", unique = true, nullable = false, length = 32)
    private String id;

    /** Login account */
    @Column(name = "LOGIN_NAME", nullable = false, length = 50)
    @Size(max = 50)
    private String loginName;

    /** User name */
    @Column(name = "USER_NAME", nullable = true, length = 50)
    private String userName;

    /** Login password */
    @Column(name = "PASSWORD", nullable = false, length = 32)
    @JsonIgnore
    private String password;

    /** Registration date */
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "CREATE_DATE", nullable = false)
    @CreatedDate
    private Date registDate;

    /** User status */
    @Column(name = "ENABLED", nullable = false, length = 1)
    private Integer enabled;

    /**
     * User's role
     */
    @ManyToOne(cascade = {}, fetch = FetchType.EAGER)
    @JoinColumn(name = "ROLE_ID")
    private Role role;

}

Role configuration

import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import lombok.Data;
import lombok.experimental.Accessors;

/**
 * System role
 * 
 * @author Li Qinghai
 *
 */
@Data
@Accessors(chain = true)
@Entity
@Table(name = "SYS_ROLE")
public class Role implements Serializable {
    private static final long serialVersionUID = -2139369027015540299L;

    /** Primary key */
    @Id
    @Column(name = "ID", unique = true, length = 32, nullable = false)
    private String id;

    /** Role description */
    @Column(name = "DES", length = 255, nullable = false)
    private String des;

    /** Role name */
    @Column(name = "TITLE", length = 20, nullable = true)
    private String title;

    /** Role sorting */
    @Column(name = "SORTER", length = 3, nullable = false)
    private Integer sorter;

}

Simple application based on JPA interface

JpaRepository implements PagingAndSortingRepository interface, PagingAndSortingRepository interface implements CrudRepository interface, and CrudRepository interface implements Repository interface. The save method can be added or updated. It only adds an instance object with the specified id when updating an object that does not exist in the database. findBy series can only implement simple, fixed parameter queries, and can't do anything for dynamic queries.

RoleRepository role interface inherited from JpaRepository

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import cn.com.infcn.jianshu.model.Role;
/**
 * System role data access class
 * @author Li Qinghai
 *
 */
public interface RoleRepository extends JpaRepository<Role, String> {
    @Query("select max(t.sorter) from Role t")
    Integer getMaxSorter();
}

RoleService service class

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Example;
import org.springframework.data.domain.ExampleMatcher;
import org.springframework.data.domain.ExampleMatcher.GenericPropertyMatchers;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import cn.com.infcn.jianshu.dao.RoleRepository;
import cn.com.infcn.jianshu.model.Role;
import cn.com.infcn.jianshu.util.StringUtil;

/**
 * System role Service class
 * 
 * @author Li Qinghai
 *
 */
@Service
public class RoleService {
    @Autowired
    private RoleRepository roleRepository;

    /**
     * Create role
     * SORTER Field is the largest sorter field in the database plus 1
     * @param title Role name
     * @param des Role description
     * @return Role
     */
    @Transactional
    public Role create(String title,String des) {
        Integer sorter = roleRepository.getMaxSorter();
        if(null==sorter){
            sorter=0;
        }
        Role model =new Role()
            .setId(StringUtil.generateUUID())
            .setTitle(title)
            .setDes(des)
            .setSorter(sorter+1);
        return this.roleRepository.save(model);
    }

    /**
     * Delete role
     * 
     * @param id  Role primary key
     */
    @Transactional
    public void delete(String id) {
        this.roleRepository.delete(id);
    }

    /**
     * Get Model from primary key
     * 
     * @param id  Role primary key
     * @return Role
     */
    public Role read(String id) {
        return this.roleRepository.findOne(id);
    }

    /**
     * Determine whether there is a role with the same name
     * 
     * @param name
     *            Name
     * @return true Or false
     */
    public boolean exists(String name) {
        ExampleMatcher matcher = ExampleMatcher.matching().withMatcher("title", GenericPropertyMatchers.exact());
        Example<Role> ex = Example.of(new Role().setTitle(name), matcher);
        return this.roleRepository.exists(ex);
    }

    /**
     * Query all roles
     * 
     * @return
     */
    public List<Role> findRoles() {
        return this.roleRepository.findAll();
    }
}

Multi table and multi condition dynamic combined query

In order to realize the dynamic combination query of multiple tables and conditions, it is necessary to inherit one more interface, jpaspecification executor.

User repository user interface inheriting JpaRepository and jpaspecification executor

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import cn.com.infcn.jianshu.model.User;

/**
 * System user data access class
 * @author Li Qinghai
 *
 */
public interface UserRepository extends JpaRepository<User, String>,JpaSpecificationExecutor<User> {
    /**
     * Query users according to login account and password
     * @param loginName Login account
     * @param password Login password
     * @return User
     */
    @Query("from User t where t.loginName=:loginName and t.password=:password and t.enabled=1")
    public User findUser(@Param(value = "loginName") String loginName, @Param(value = "password") String password);

    /**
     * Query user information according to login account
     * @param loginName Login account
     * @return User
     */
    @Query("from User t where t.loginName=:loginName")
    public User findUserByLoginName(@Param(value = "loginName")String loginName);

    /**
     * reset password 
     * @param id User number
     * @param password New password
     */
    @Modifying
    @Query("update User u set u.password=:password where u.id=:id")
    public void updatePassword(@Param(value = "id") String id, @Param(value = "password") String password);

    
    /**
     * Update the status of system users
     * @param id User number
     * @param enabled User status
     */
    @Modifying
    @Query("update User u set u.enabled=:enabled where u.id=:id")
    public void updateEnabled(@Param(value = "id") String id, @Param(value = "enabled") int enabled);
}

UserService service service class

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.JoinType;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Example;
import org.springframework.data.domain.ExampleMatcher;
import org.springframework.data.domain.ExampleMatcher.GenericPropertyMatchers;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import cn.com.infcn.jianshu.dao.UserRepository;
import cn.com.infcn.jianshu.model.Role;
import cn.com.infcn.jianshu.model.User;
import cn.com.infcn.jianshu.util.StringUtil;

@Service
public class UserService {
    
    @Autowired
    private UserRepository userRepository;
    
    /**
     * Add user, register
     * 
     * @param loginName
     *            Login account
     * @param password
     *            Login password
     * @param userName
     *            User name
     * @param roleId
     *            User role
     * @return
     */
    @Transactional
    public User create(String loginName, String password, String userName,String roleId) {
        User user = new User()
            .setLoginName(loginName)
            .setUserName(userName)
            .setPassword(password)
            .setRegistDate(new Date())
            .setEnabled(1)
            .setRole(new Role().setId(roleId))
            .setId(StringUtil.generateUUID());
        return this.userRepository.saveAndFlush(user);
    }

    /**
     * Query users according to login account and password
     * 
     * @param loginName
     *            Login account
     * @param password
     *            Login password
     * @return
     */
    public User login(String loginName, String password) {
        return this.userRepository.findUser(loginName, password);
    }

    /**
     * Query user information according to login account
     * 
     * @param loginName
     *            Login account
     * @return
     */
    public User getUser(String loginName) {
        return this.userRepository.findUserByLoginName(loginName);
    }

    /**
     * reset password 
     * 
     * @param id
     *            User number
     * @param password
     *            New password
     */
    @Transactional
    public void updatePassword(String id, String password) {
        this.userRepository.updatePassword(id, password);
    }

    /**
     * Log off user, not delete
     * 
     * @param id
     *            User number
     */
    @Transactional
    public void cancel(String id) {
        this.userRepository.updateEnabled(id, 0);
    }

    /**
     * Judge whether the login account already exists, and the login account is unique
     * 
     * @param loginName
     *            Login account
     * @return
     */
    public boolean exists(String loginName) {
        ExampleMatcher matcher = ExampleMatcher.matching().withMatcher("loginName", GenericPropertyMatchers.exact());
        Example<User> ex = Example.of(new User().setLoginName(loginName), matcher);
        return this.userRepository.exists(ex);
    }

    /**
     * Get user information according to the primary key
     * 
     * @param id
     *            User number
     * @return
     */
    public User getOne(String id) {
        return this.userRepository.getOne(id);
    }

    /**
     * Multi criteria query user
     * 
     * @param userName
     *            User name
     * @param roleId
     *            User role number
     * @param start
     *            Registration start date
     * @param end
     *            Registration end date
     * @param page
     *            Paging, starting at 0
     * @param size
     *            Lines per page
     * @return
     */
    public Page<User> findDatas(String userName, String roleId, Date start, Date end, int page, int size) {
        Pageable pr = new PageRequest(page, size, new Sort(Direction.DESC, "registDate"));
        return this.userRepository.findAll(new Specification<User>() {
            @Override
            public Predicate toPredicate(Root<User> root, CriteriaQuery<?> criteriaQuery,
                    CriteriaBuilder criteriaBuilder) {
                List<Predicate> list = new ArrayList<Predicate>();
                // root.get("xx attribute") indicates to get the name of xx attribute, like indicates to execute like query, and zt% indicates value
                if (null != userName) {
                    Predicate p = criteriaBuilder.like(root.get("userName"), "%" + userName + "%");
                    list.add(p);
                }
                if (null != roleId) {
                    //Associated query, User associated Role
                    Predicate p = criteriaBuilder.equal(root.join("role", JoinType.INNER).get("id"), roleId);
                    list.add(p);
                }
                if (null != start) {
                    Predicate p = criteriaBuilder.greaterThanOrEqualTo(root.get("registDate"), start);
                    list.add(p);
                }
                if (null != end) {
                    Predicate p = criteriaBuilder.lessThanOrEqualTo(root.get("registDate"), end);
                    list.add(p);
                }

                Predicate[] ps = new Predicate[list.size()];
                // Combine the query criteria and return the Predicate object
                return criteriaBuilder.and(list.toArray(ps));
            }
        }, pr);
    }
}

Tags: Java MySQL Database Lombok

Posted on Sat, 02 May 2020 00:50:20 -0700 by gloeilamp