SpringBoot 集成 sharp-database

添加依赖

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.7.9</version>
    <relativePath/>
</parent>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>

    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.2.24</version>
    </dependency>

    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
    </dependency>

    <dependency>
        <groupId>com.rick.db</groupId>
        <artifactId>sharp-database</artifactId>
        <version>2.0-SNAPSHOT</version>
    </dependency>
</dependencies>

数据库配置文件：

server:
  port: 8080
spring:
  datasource:
    name: druidDataSource
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
      username: root
      password: root
      initialSize: 1
      minIdle: 5
      maxActive: 10
      maxWait: 60000

sharp:
  database: # 多个包扫描用 , 隔开
    entity-base-package: com.rick.sharp.**.entity

测试

@SpringBootApplication
@DependsOn("entityDAOSupport")
@EnableScheduling
public class DemoApplication {

    @Autowired
    private EntityDAO<User, Long> userDAO;

    @Autowired
    private EntityDAO<CodeDescription, Long> codeDescriptionDAO;


    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);

        String ss = JsonUtils.toJson("ss");
        System.out.println(ss);
        System.out.println("running.....");
    }

    @Scheduled(fixedRate = 5000)
    public void find() {
        User user = userDAO.selectById(1L).get();
        System.out.println(user.getName());

        codeDescriptionDAO.insert(CodeDescription.builder()
                        .code("test-" + System.currentTimeMillis())
                        .description("hello")
                .category(CodeDescription.CategoryEnum.MATERIAL)
                .build());
    }

}

@Sql 根据对象的字段查询其他信息

假设对象有属性 物料id，联系人id，可以根据这个两个 id 分别获取 描述信息，并放到Map对象中。

将多行的 union all 进行「行转列」

select 
MAX(CASE WHEN c1 = 'material_name' THEN c2 END) as 'material_name',
MAX(CASE WHEN c1 = 'contact_person' THEN c2 END) as 'contact_person'
from (
select 'material_name' c1, name c2 from mm_material where id = 725446412870901760
union all
select 'contact_person', contact_person from core_contact where id = 738365393630253056)
t

创建查询对象 GridQuery.java

@Data
@Table
public class GridQuery {

    /**
     * 必须有一个主键字段
     */
    private Long id;

    private Long materialId;

    private Long contactId;

    @Transient
    @Sql(value = "select \n" +
            "MAX(CASE WHEN c1 = 'material_name' THEN c2 END) as 'material_name',\n" +
            "MAX(CASE WHEN c1 = 'contact_person' THEN c2 END) as 'contact_person'\n" +
            "from (\n" +
            "select 'material_name' c1, name c2 from mm_material where id = :materialId\n" 
            "union all\n" +
            "select 'contact_person', contact_person from core_contact where id = :contactId)\n" +
            "t", params="materialId@materialId, contactId@contactId")
    private Map<String, Object> additionalInfo;
}

测试信息

@Autowired
private EntityDAOSupport entityDAOSupport;

@Test
public void testQuery() {
    // 单个对象
    GridQuery query = new GridQuery();

    query.setMaterialId(725446412870901760L);
    query.setContactId(738365393630253056L);

    GridQuery query1 = entityDAOSupport.query(query);

    System.out.println(query1);

    // 对象数组
    GridQuery query2 = new GridQuery();
    query2.setMaterialId(725446412870901760L);

    GridQuery query3 = new GridQuery();
    query3.setContactId(738365393630253056L);

    List<GridQuery> query4 = entityDAOSupport.query(Arrays.asList(query2, query3));
}

Linux自动打包上传并运行SpringBoot程序

SSH 免密登录

生成公钥、私钥

ssh-keygen

发送公钥

ssh-copy-id -i /Users/rick/.ssh/id_rsa.pub root@172.31.1.2

配置别名

.ssh/config

Host xhope
  HostName 172.31.1.2
  User root
  IdentityFile ~/.ssh/id_rsa

Host dao
  HostName 172.31.1.3
  User root
  IdentityFile ~/.ssh/id_rsa

登录

ssh xhope

程序执行脚本

#!/bin/bash

cd /usr/local/projects/dao
PID=`ps -ef | pgrep -f "8082"`
if [  -n "$PID" ]
then
        kill -9 $PID
fi

rm -f product-manager-1.0-SNAPSHOT.jar
cp deploy/product-manager-1.0-SNAPSHOT.jar .

nohup /usr/local/jdk1.8.0_65/bin/java -Xms1024m -Xmx2048m -Dspring.profiles.active=prod -jar product-manager-1.0-SNAPSHOT.jar --server.port=8082 &

exit

mvn.sh

#!/bin/bash

mvn clean package -Dmaven.test.skip=true
scp /Users/rick/Space/Workspace/product-manager/target/product-manager-1.0-SNAPSHOT.jar root@172.31.1.2:/usr/local/projects/dao/deploy

ssh dao "bash -s < /usr/local/projects/dao/clean.sh"

执行 mvn.sh 后，自动打包上传并运行程序

sharp-generator

上篇文章《CRUD 正确姿势》步骤比较繁琐，可以通过 sharp-generator 生成固定代码

 @Test
 public void testGenerator() throws IOException {
     SQLUtils.execute("Drop TABLE t_student");

     generator.execute(Student.class,
             "/Users/rick/Space/Workspace/sharp-project/sharp-admin/src/main/java/com/rick/admin/module/student",
             Params.builder()
                     .pv(Generator.CONTROLLER, true) // 是否创建 controller
                     .pv(Generator.FORM_PAGE, "demos/student/edit") // 编辑页面路径

                     .pv(Generator.REPORT, true) // 是否创建 report
                     .pv(Generator.REPORT_TEST_PATH, "/Users/rick/Space/Workspace/sharp-project/sharp-admin/src/test/java/com/rick/admin/demo")
                     .pv(Generator.REPORT_TEST_PACKAGE, "com.rick.admin.demo")
                     .build());
 }