使用 ApiTable 连接 Postgres

postgresql 简介

略

整合

简单整合示例

创建表,插入数据

创建一张简单的 student 表

CREATE TABLE "public"."student" (
  "id" int8 NOT NULL,
  "name" varchar(255),
  "grade" varchar(255),
  PRIMARY KEY ("id")
);

INSERT INTO student VALUES (1, '沈', '一年级');
INSERT INTO student VALUES (2, '李', '一年级');
INSERT INTO student VALUES (3,'张', '二年级');

添加依赖

新建工程 tio-boot-postgresql-demo

<dependency>
  <groupId>com.litongjava</groupId>
  <artifactId>api-table</artifactId>
  <version>${api-table.version}</version>
</dependency>

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>42.2.24</version>
</dependency>

<dependency>
  <groupId>com.zaxxer</groupId>
  <artifactId>HikariCP</artifactId>
  <version>4.0.3</version>
</dependency>

配置文件 app.properties

jdbc.url=jdbc:postgresql://192.168.3.9/student
jdbc.user=postgres
jdbc.pswd=robot_1234546

编写启动类

import com.litongjava.hotswap.wrapper.tio.boot.TioApplicationWrapper;
import com.litongjava.jfinal.aop.annotation.AComponentScan;

@AComponentScan
public class PostgresqlApp {

  public static void main(String[] args) throws Exception {
    long start = System.currentTimeMillis();
    TioApplicationWrapper.run(PostgresqlApp.class, args);
    long end = System.currentTimeMillis();
    System.out.println("started:" + (end - start) + "(ms)");
  }
}

编写配置类

DbConfig.java, 注意观察

• DataSource 的 priority 是 1,priority 表示 bean 启动的优先级,值越小,启动的优先级越高
• arp.setDialect(new PostgreSqlDialect()); 设置了数据库方言为 PostgreSQLDialect

配置方式 1

import javax.sql.DataSource;

import com.jfinal.template.Engine;
import com.jfinal.template.source.ClassPathSourceFactory;
import com.litongjava.jfinal.aop.Aop;
import com.litongjava.jfinal.aop.annotation.ABean;
import com.litongjava.jfinal.aop.annotation.AConfiguration;
import com.litongjava.jfinal.plugin.activerecord.ActiveRecordPlugin;
import com.litongjava.jfinal.plugin.activerecord.OrderedFieldContainerFactory;
import com.litongjava.tio.boot.constatns.ConfigKeys;
import com.litongjava.tio.utils.environment.EnvUtils;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

@AConfiguration
public class DbConfig {

  @ABean(priority = 1, destroyMethod = "close")
  public DataSource dataSource() {
    String jdbcUrl = EnvUtils.get("jdbc.url");
    String jdbcUser = EnvUtils.get("jdbc.user");

    String jdbcPswd = EnvUtils.get("jdbc.pswd");
    int maximumPoolSize = EnvUtils.getInt("jdbc.MaximumPoolSize", 2);

    HikariConfig config = new HikariConfig();
    // 设定基本参数
    config.setJdbcUrl(jdbcUrl);
    config.setUsername(jdbcUser);
    config.setPassword(jdbcPswd);
    config.setMaximumPoolSize(maximumPoolSize);

    HikariDataSource hikariDataSource = new HikariDataSource(config);
    return hikariDataSource;
  }

  /*
   *
   * config ActiveRecordPlugin
   */

  @ABean(destroyMethod = "stop", initMethod = "start")
  public ActiveRecordPlugin activeRecordPlugin() throws Exception {
    DataSource dataSource = Aop.get(DataSource.class);
    String property = EnvUtils.get(ConfigKeys.APP_ENV);

    ActiveRecordPlugin arp = new ActiveRecordPlugin(dataSource);
    arp.setContainerFactory(new OrderedFieldContainerFactory());
    if ("dev".equals(property)) {
      arp.setDevMode(true);
    }

    Engine engine = arp.getEngine();
    engine.setSourceFactory(new ClassPathSourceFactory());
    engine.setCompressorOn(' ');
    engine.setCompressorOn('\n');
    arp.addSqlTemplate("/sql/all_sqls.sql");
	  //arp.start();
    return arp;
  }
}

配置方式 2:不添加类到 AOP 容器

package com.litongjava.tio.boot.admin.config;
import javax.sql.DataSource;

import com.jfinal.template.Engine;
import com.jfinal.template.source.ClassPathSourceFactory;
import com.litongjava.jfinal.aop.annotation.AConfiguration;
import com.litongjava.jfinal.aop.annotation.AInitialization;
import com.litongjava.jfinal.plugin.activerecord.ActiveRecordPlugin;
import com.litongjava.jfinal.plugin.activerecord.OrderedFieldContainerFactory;
import com.litongjava.jfinal.plugin.activerecord.dialect.PostgreSqlDialect;
import com.litongjava.jfinal.plugin.hikaricp.DsContainer;
import com.litongjava.tio.boot.constatns.TioBootConfigKeys;
import com.litongjava.tio.boot.server.TioBootServer;
import com.litongjava.tio.utils.environment.EnvUtils;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

@AConfiguration
public class DbConfig {

  public DataSource dataSource() {
    String jdbcUrl = EnvUtils.get("jdbc.url");
    String jdbcUser = EnvUtils.get("jdbc.user");

    String jdbcPswd = EnvUtils.get("jdbc.pswd");
    int maximumPoolSize = EnvUtils.getInt("jdbc.MaximumPoolSize", 2);

    HikariConfig config = new HikariConfig();
    // config
    config.setJdbcUrl(jdbcUrl);
    config.setUsername(jdbcUser);
    config.setPassword(jdbcPswd);
    config.setMaximumPoolSize(maximumPoolSize);

    HikariDataSource hikariDataSource = new HikariDataSource(config);

    // set datasource
    DsContainer.setDataSource(hikariDataSource);
    // add destroy
    TioBootServer.me().addDestroyMethod(hikariDataSource::close);
    return hikariDataSource;

  }

  /*
   *
   * config ActiveRecordPlugin
   */

  @Initialization
  public void activeRecordPlugin() throws Exception {
    // get dataSource
    DataSource dataSource = dataSource();
    // get env key
    String property = EnvUtils.get(TioBootConfigKeys.APP_ENV);

    // create arp
    ActiveRecordPlugin arp = new ActiveRecordPlugin(dataSource);
    arp.setContainerFactory(new OrderedFieldContainerFactory());
    if ("dev".equals(property)) {
      arp.setDevMode(true);
    }

    arp.setDialect(new PostgreSqlDialect());

    // config engine
    Engine engine = arp.getEngine();
    engine.setSourceFactory(new ClassPathSourceFactory());
    engine.setCompressorOn(' ');
    engine.setCompressorOn('\n');
    // add sql file
    arp.addSqlTemplate("/sql/all_sqls.sql");
    // start
    arp.start();
    // add stop
    TioBootServer.me().addDestroyMethod(arp::stop);
  }
}

编写 Controller

查询 student 表中的所有数据,代码如下

package com.litongjava.tio.boot.hello.AController;

import java.util.List;

import org.tio.http.common.HttpRequest;
import org.tio.http.server.annotation.RequestPath;

import com.jfinal.kit.Kv;
import com.litongjava.data.model.DbJsonBean;
import com.litongjava.data.services.DbJsonService;
import com.litongjava.data.utils.DbJsonBeanUtils;
import com.litongjava.jfinal.aop.Aop;
@AController
@RequestPath("/db/student")
public class DbTestController {

  DbJsonService dbJsonService = DbJsonService.getInstance();

  @RequestPath("/list")
  public DbJsonBean<List<Kv>> list(HttpRequest request) {
    String tableName = "student";
    DbJsonBean<List<Kv>> jsonBean = DbJsonBeanUtils.recordsToKv(dbJsonService.listAll(tableName));
    return jsonBean;
  }
}

访问 http://localhost/db/student/list 输出如下

{"code":0,"data":[{"grade":"一年级","name":"沈","id":"1"},{"grade":"一年级","name":"李","id":"2"},{"grade":"二年级","name":"张","id":"3"}],"msg":""}

Junit 单元测试

使用 Junit 对连接数据库的部分进行单元测试

import java.util.List;

import org.junit.Before;
import org.junit.Test;

import com.litongjava.data.model.DbJsonBean;
import com.litongjava.data.services.DbJsonService;
import com.litongjava.jfinal.aop.Aop;
import com.litongjava.jfinal.plugin.activerecord.Record;
import com.litongjava.tio.boot.tesing.TioBootTest;

public class SelectDbTest {

  DbJsonService dbJsonService = DbJsonService.getInstance();
  @Before
  public void before() throws Exception {
    TioBootTest.before(DbConfig.class);
  }

  @Test
  public void test() {
    String tableName = "student";
    DbJsonBean<List<Record>> listAll = dbJsonService.listAll(tableName);
    System.out.println(listAll.getData().size());
  }
}

解析 DSN 方式

配置 dsn

DATABASE_DSN=postgresql://postgres:robot_1234546@192.168.3.9/student

解析并配置

package com.sejie.config;

import javax.sql.DataSource;

import com.jfinal.template.Engine;
import com.jfinal.template.source.ClassPathSourceFactory;
import com.litongjava.jfinal.aop.annotation.AConfiguration;
import com.litongjava.jfinal.aop.annotation.AInitialization;
import com.litongjava.jfinal.plugin.activerecord.ActiveRecordPlugin;
import com.litongjava.jfinal.plugin.activerecord.OrderedFieldContainerFactory;
import com.litongjava.jfinal.plugin.activerecord.dialect.PostgreSqlDialect;
import com.litongjava.jfinal.plugin.hikaricp.DsContainer;
import com.litongjava.tio.boot.server.TioBootServer;
import com.litongjava.tio.utils.dsn.DbDSNParser;
import com.litongjava.tio.utils.dsn.JdbcInfo;
import com.litongjava.tio.utils.environment.EnvUtils;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

@AConfiguration
public class DbConfig {

  public DataSource dataSource() {
    String dsn = EnvUtils.get("DATABASE_DSN");
    if (dsn == null) {
      return null;
    }

    JdbcInfo jdbc = new DbDSNParser().parse(dsn);

    int maximumPoolSize = EnvUtils.getInt("jdbc.MaximumPoolSize", 2);

    HikariConfig config = new HikariConfig();
    // config
    config.setJdbcUrl(jdbc.getUrl());
    config.setUsername(jdbc.getUser());
    config.setPassword(jdbc.getPswd());
    config.setMaximumPoolSize(maximumPoolSize);

    HikariDataSource hikariDataSource = new HikariDataSource(config);

    // set datasource
    DsContainer.setDataSource(hikariDataSource);
    // add destroy
    TioBootServer.me().addDestroyMethod(hikariDataSource::close);
    return hikariDataSource;
  }

  /*
   *
   * config ActiveRecordPlugin
   */
  @Initialization
  public void config() {
    // get dataSource
    DataSource dataSource = dataSource();
    if (dataSource == null) {
      return;
    }
    // create arp
    ActiveRecordPlugin arp = new ActiveRecordPlugin(dataSource);
    arp.setContainerFactory(new OrderedFieldContainerFactory());
    if (EnvUtils.isDev()) {
      arp.setDevMode(true);
    }

    arp.setDialect(new PostgreSqlDialect());

    // config engine
    Engine engine = arp.getEngine();
    engine.setSourceFactory(new ClassPathSourceFactory());
    engine.setCompressorOn(' ');
    engine.setCompressorOn('\n');
    // add sql file
    // arp.addSqlTemplate("/sql/all_sqls.sql");
    // start
    arp.start();
    // add stop
    TioBootServer.me().addDestroyMethod(arp::stop);
  }
}