Aplicação Java para ligar e executar comandos SQL no Azure Cosmos DB para PostgreSQL
APLICA-SE A: Azure Cosmos DB para PostgreSQL (com tecnologia da extensão da base de dados Citus para PostgreSQL)
Este início rápido mostra-lhe como utilizar código Java para ligar a um cluster e utilizar instruções SQL para criar uma tabela. Em seguida, irá inserir, consultar, atualizar e eliminar dados na base de dados. Os passos neste artigo partem do princípio de que está familiarizado com o desenvolvimento java e o JDBC e que nunca trabalhou com o Azure Cosmos DB para PostgreSQL.
Configurar o projeto Java e a ligação
Crie um novo projeto Java e um ficheiro de configuração para ligar ao Azure Cosmos DB para PostgreSQL.
Criar um novo projeto Java
Com o seu ambiente de desenvolvimento integrado (IDE) favorito, crie um novo projeto Java com groupId test
e artifactId crud
. No diretório de raiz do projeto, adicione um ficheiro pom.xml com os seguintes conteúdos. Este ficheiro configura o Apache Maven para utilizar o Java 8 e um controlador PostgreSQL recente para Java.
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>test</groupId>
<artifactId>crud</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>crud</name>
<url>http://www.example.com</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-engine</artifactId>
<version>5.7.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.12</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.zaxxer/HikariCP -->
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>5.0.0</version>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-params</artifactId>
<version>5.7.1</version>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-surefire-plugin</artifactId>
<version>3.0.0-M5</version>
</plugin>
</plugins>
</build>
</project>
Configurar a ligação à base de dados
Em src/main/resources/, crie um ficheiro application.properties com os seguintes conteúdos. Substitua cluster <> pelo nome do cluster e substitua <a palavra-passe> pela palavra-passe administrativa.
driver.class.name=org.postgresql.Driver
db.url=jdbc:postgresql://c-<cluster>.<uniqueID>.postgres.cosmos.azure.com:5432/citus?ssl=true&sslmode=require
db.username=citus
db.password=<password>
A ?ssl=true&sslmode=require
cadeia na db.url
propriedade indica ao controlador JDBC para utilizar o Transport Layer Security (TLS) ao ligar à base de dados. É obrigatório utilizar o TLS com o Azure Cosmos DB para PostgreSQL e é uma boa prática de segurança.
Criar tabelas
Configure um esquema de base de dados que tenha tabelas distribuídas. Ligue-se à base de dados para criar o esquema e as tabelas.
Gerar o esquema da base de dados
Em src/main/resources/, crie um ficheiro schema.sql com os seguintes conteúdos:
DROP TABLE IF EXISTS public.pharmacy;
CREATE TABLE public.pharmacy(pharmacy_id integer,pharmacy_name text ,city text ,state text ,zip_code integer);
CREATE INDEX idx_pharmacy_id ON public.pharmacy(pharmacy_id);
Distribuir tabelas
O Azure Cosmos DB para PostgreSQL dá-lhe a capacidade de distribuir tabelas por vários nós para escalabilidade. O comando abaixo permite-lhe distribuir uma tabela. Pode saber mais sobre create_distributed_table
e a coluna de distribuição aqui.
Nota
A distribuição de tabelas permite-lhes crescer em todos os nós de trabalho adicionados ao cluster.
Para distribuir tabelas, acrescente a seguinte linha ao ficheiro schema.sql que criou na secção anterior.
select create_distributed_table('public.pharmacy','pharmacy_id');
Ligar à base de dados e criar o esquema
Em seguida, adicione o código Java que utiliza JDBC para armazenar e obter dados do cluster. O código utiliza os ficheiros application.properties e schema.sql para ligar ao cluster e criar o esquema.
Crie um ficheiro DButil.java com o seguinte código, que contém a
DButil
classe . ADBUtil
classe configura um conjunto de ligações para PostgreSQL com HikariCP. Utilize esta classe para ligar ao PostgreSQL e iniciar a consulta.Dica
O código de exemplo abaixo utiliza um conjunto de ligações para criar e gerir ligações ao PostgreSQL. O conjunto de ligações do lado da aplicação é altamente recomendado porque:
- Garante que a aplicação não gera demasiadas ligações à base de dados, pelo que evita exceder os limites de ligação.
- Pode ajudar a melhorar drasticamente o desempenho, tanto a latência como o débito. O processo do servidor PostgreSQL tem de fork para processar cada nova ligação e reutilizar uma ligação evita essa sobrecarga.
//DButil.java package test.crud; import java.io.FileInputStream; import java.io.IOException; import java.sql.SQLException; import java.util.Properties; import javax.sql.DataSource; import com.zaxxer.hikari.HikariDataSource; public class DButil { private static final String DB_USERNAME = "db.username"; private static final String DB_PASSWORD = "db.password"; private static final String DB_URL = "db.url"; private static final String DB_DRIVER_CLASS = "driver.class.name"; private static Properties properties = null; private static HikariDataSource datasource; static { try { properties = new Properties(); properties.load(new FileInputStream("src/main/java/application.properties")); datasource = new HikariDataSource(); datasource.setDriverClassName(properties.getProperty(DB_DRIVER_CLASS )); datasource.setJdbcUrl(properties.getProperty(DB_URL)); datasource.setUsername(properties.getProperty(DB_USERNAME)); datasource.setPassword(properties.getProperty(DB_PASSWORD)); datasource.setMinimumIdle(100); datasource.setMaximumPoolSize(1000000000); datasource.setAutoCommit(true); datasource.setLoginTimeout(3); } catch (IOException | SQLException e) { e.printStackTrace(); } } public static DataSource getDataSource() { return datasource; } }
Em src/main/java/, crie um ficheiro DemoApplication.java que contenha o seguinte código:
package test.crud; import java.io.IOException; import java.sql.*; import java.util.*; import java.util.logging.Logger; import java.io.FileInputStream; import java.io.FileOutputStream; import org.postgresql.copy.CopyManager; import org.postgresql.core.BaseConnection; import java.io.IOException; import java.io.Reader; import java.io.StringReader; public class DemoApplication { private static final Logger log; static { System.setProperty("java.util.logging.SimpleFormatter.format", "[%4$-7s] %5$s %n"); log =Logger.getLogger(DemoApplication.class.getName()); } public static void main(String[] args)throws Exception { log.info("Connecting to the database"); Connection connection = DButil.getDataSource().getConnection(); System.out.println("The Connection Object is of Class: " + connection.getClass()); log.info("Database connection test: " + connection.getCatalog()); log.info("Creating table"); log.info("Creating index"); log.info("distributing table"); Scanner scanner = new Scanner(DemoApplication.class.getClassLoader().getResourceAsStream("schema.sql")); Statement statement = connection.createStatement(); while (scanner.hasNextLine()) { statement.execute(scanner.nextLine()); } log.info("Closing database connection"); connection.close(); } }
Nota
A base de dados
user
epassword
as credenciais são utilizadas ao executarDriverManager.getConnection(properties.getProperty("url"), properties);
. As credenciais são armazenadas no ficheiro application.properties , que é transmitido como um argumento.Agora pode executar esta classe principal com a sua ferramenta favorita:
- Com o IDE, deverá conseguir clicar com o botão direito do
DemoApplication
rato na classe e executá-la. - Com o Maven, pode executar a aplicação ao executar:
mvn exec:java -Dexec.mainClass="com.example.demo.DemoApplication"
.
- Com o IDE, deverá conseguir clicar com o botão direito do
A aplicação deve ligar-se ao Azure Cosmos DB para PostgreSQL, criar um esquema de base de dados e, em seguida, fechar a ligação, como deverá ver nos registos da consola:
[INFO ] Loading application properties
[INFO ] Connecting to the database
[INFO ] Database connection test: citus
[INFO ] Create database schema
[INFO ] Closing database connection
Criar uma classe de domínio
Crie uma nova Pharmacy
classe Java, junto à DemoApplication
classe e adicione o seguinte código:
public class Pharmacy {
private Integer pharmacy_id;
private String pharmacy_name;
private String city;
private String state;
private Integer zip_code;
public Pharmacy() { }
public Pharmacy(Integer pharmacy_id, String pharmacy_name, String city,String state,Integer zip_code)
{
this.pharmacy_id = pharmacy_id;
this.pharmacy_name = pharmacy_name;
this.city = city;
this.state = state;
this.zip_code = zip_code;
}
public Integer getpharmacy_id() {
return pharmacy_id;
}
public void setpharmacy_id(Integer pharmacy_id) {
this.pharmacy_id = pharmacy_id;
}
public String getpharmacy_name() {
return pharmacy_name;
}
public void setpharmacy_name(String pharmacy_name) {
this.pharmacy_name = pharmacy_name;
}
public String getcity() {
return city;
}
public void setcity(String city) {
this.city = city;
}
public String getstate() {
return state;
}
public void setstate(String state) {
this.state = state;
}
public Integer getzip_code() {
return zip_code;
}
public void setzip_code(Integer zip_code) {
this.zip_code = zip_code;
}
@Override
public String toString() {
return "TPharmacy{" +
"pharmacy_id=" + pharmacy_id +
", pharmacy_name='" + pharmacy_name + '\'' +
", city='" + city + '\'' +
", state='" + state + '\'' +
", zip_code='" + zip_code + '\'' +
'}';
}
}
Esta classe é um modelo de domínio mapeado na tabela que criou ao Pharmacy
executar o script schema.sql .
Inserir dados
No ficheiro DemoApplication.java , depois do main
método , adicione o seguinte método que utiliza a instrução SQL INSERT INTO para inserir dados na base de dados:
private static void insertData(Pharmacy todo, Connection connection) throws SQLException {
log.info("Insert data");
PreparedStatement insertStatement = connection
.prepareStatement("INSERT INTO pharmacy (pharmacy_id,pharmacy_name,city,state,zip_code) VALUES (?, ?, ?, ?, ?);");
insertStatement.setInt(1, todo.getpharmacy_id());
insertStatement.setString(2, todo.getpharmacy_name());
insertStatement.setString(3, todo.getcity());
insertStatement.setString(4, todo.getstate());
insertStatement.setInt(5, todo.getzip_code());
insertStatement.executeUpdate();
}
Adicione as duas linhas seguintes no método principal:
Pharmacy todo = new Pharmacy(0,"Target","Sunnyvale","California",94001);
insertData(todo, connection);
Executar a classe principal deve agora produzir o seguinte resultado:
[INFO ] Loading application properties
[INFO ] Connecting to the database
[INFO ] Database connection test: citus
[INFO ] Creating table
[INFO ] Creating index
[INFO ] distributing table
[INFO ] Insert data
[INFO ] Closing database connection
Ler dados
Leia os dados que inseriu anteriormente para confirmar que o código funciona corretamente.
No ficheiro DemoApplication.java , depois do insertData
método , adicione o seguinte método que utiliza a instrução SQL SELECT para ler dados da base de dados:
private static Pharmacy readData(Connection connection) throws SQLException {
log.info("Read data");
PreparedStatement readStatement = connection.prepareStatement("SELECT * FROM Pharmacy;");
ResultSet resultSet = readStatement.executeQuery();
if (!resultSet.next()) {
log.info("There is no data in the database!");
return null;
}
Pharmacy todo = new Pharmacy();
todo.setpharmacy_id(resultSet.getInt("pharmacy_id"));
todo.setpharmacy_name(resultSet.getString("pharmacy_name"));
todo.setcity(resultSet.getString("city"));
todo.setstate(resultSet.getString("state"));
todo.setzip_code(resultSet.getInt("zip_code"));
log.info("Data read from the database: " + todo.toString());
return todo;
}
Adicione a seguinte linha no método principal:
todo = readData(connection);
Executar a classe principal deve agora produzir o seguinte resultado:
[INFO ] Loading application properties
[INFO ] Connecting to the database
[INFO ] Database connection test: citus
[INFO ] Creating table
[INFO ] Creating index
[INFO ] distributing table
[INFO ] Insert data
[INFO ] Read data
[INFO ] Data read from the database: Pharmacy{pharmacy_id=0, pharmacy_name='Target', city='Sunnyvale', state='California', zip_code='94001'}
[INFO ] Closing database connection
Atualizar dados
Atualize os dados que inseriu anteriormente.
Ainda no ficheiro DemoApplication.java , após o readData
método , adicione o seguinte método para atualizar dados dentro da base de dados com a instrução SQL UPDATE:
private static void updateData(Pharmacy todo, Connection connection) throws SQLException {
log.info("Update data");
PreparedStatement updateStatement = connection
.prepareStatement("UPDATE pharmacy SET city = ? WHERE pharmacy_id = ?;");
updateStatement.setString(1, todo.getcity());
updateStatement.setInt(2, todo.getpharmacy_id());
updateStatement.executeUpdate();
readData(connection);
}
Adicione as duas linhas seguintes no método principal:
todo.setcity("Guntur");
updateData(todo, connection);
Executar a classe principal deve agora produzir o seguinte resultado:
[INFO ] Loading application properties
[INFO ] Connecting to the database
[INFO ] Database connection test: citus
[INFO ] Creating table
[INFO ] Creating index
[INFO ] distributing table
[INFO ] Insert data
[INFO ] Read data
[INFO ] Data read from the database: Pharmacy{pharmacy_id=0, pharmacy_name='Target', city='Sunnyvale', state='California', zip_code='94001'}
[INFO ] Update data
[INFO ] Read data
[INFO ] Data read from the database: Pharmacy{pharmacy_id=0, pharmacy_name='Target', city='Guntur', state='California', zip_code='94001'}
[INFO ] Closing database connection
Eliminar dados
Por fim, elimine os dados que inseriu anteriormente. Ainda no ficheiro DemoApplication.java , após o método , adicione o updateData
seguinte método para eliminar dados dentro da base de dados com a instrução SQL DELETE:
private static void deleteData(Pharmacy todo, Connection connection) throws SQLException {
log.info("Delete data");
PreparedStatement deleteStatement = connection.prepareStatement("DELETE FROM pharmacy WHERE pharmacy_id = ?;");
deleteStatement.setLong(1, todo.getpharmacy_id());
deleteStatement.executeUpdate();
readData(connection);
}
Agora, pode adicionar a seguinte linha no método principal:
deleteData(todo, connection);
Executar a classe principal deve agora produzir o seguinte resultado:
[INFO ] Loading application properties
[INFO ] Connecting to the database
[INFO ] Database connection test: citus
[INFO ] Creating table
[INFO ] Creating index
[INFO ] distributing table
[INFO ] Insert data
[INFO ] Read data
[INFO ] Data read from the database: Pharmacy{pharmacy_id=0, pharmacy_name='Target', city='Sunnyvale', state='California', zip_code='94001'}
[INFO ] Update data
[INFO ] Read data
[INFO ] Data read from the database: Pharmacy{pharmacy_id=0, pharmacy_name='Target', city='Guntur', state='California', zip_code='94001'}
[INFO ] Delete data
[INFO ] Read data
[INFO ] There is no data in the database!
[INFO ] Closing database connection
Comando COPY para ingestão rápida
O comando COPY pode gerar um débito tremendo ao ingerir dados no Azure Cosmos DB para PostgreSQL. O comando COPY pode ingerir dados em ficheiros ou a partir de micro lotes de dados na memória para ingestão em tempo real.
Comando COPY para carregar dados de um ficheiro
O código seguinte copia dados de um ficheiro CSV para uma tabela de base de dados. O exemplo de código requer o ficheiro pharmacies.csv.
public static long
copyFromFile(Connection connection, String filePath, String tableName)
throws SQLException, IOException {
long count = 0;
FileInputStream fileInputStream = null;
try {
Connection unwrap = connection.unwrap(Connection.class);
BaseConnection connSec = (BaseConnection) unwrap;
CopyManager copyManager = new CopyManager((BaseConnection) connSec);
fileInputStream = new FileInputStream(filePath);
count = copyManager.copyIn("COPY " + tableName + " FROM STDIN delimiter ',' csv", fileInputStream);
} finally {
if (fileInputStream != null) {
try {
fileInputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return count;
}
Agora, pode adicionar a seguinte linha no método principal:
int c = (int) copyFromFile(connection,"C:\\Users\\pharmacies.csv", "pharmacy");
log.info("Copied "+ c +" rows using COPY command");
A execução da main
classe deverá agora produzir o seguinte resultado:
[INFO ] Loading application properties
[INFO ] Connecting to the database
[INFO ] Database connection test: citus
[INFO ] Creating table
[INFO ] Creating index
[INFO ] distributing table
[INFO ] Insert data
[INFO ] Read data
[INFO ] Data read from the database: Pharmacy{pharmacy_id=0, pharmacy_name='Target', city='Sunnyvale', state='California', zip_code='94001'}
[INFO ] Update data
[INFO ] Read data
[INFO ] Data read from the database: Pharmacy{pharmacy_id=0, pharmacy_name='Target', city='Guntur', state='California', zip_code='94001'}
[INFO ] Delete data
[INFO ] Read data
[INFO ] There is no data in the database!
[INFO ] Copied 5000 rows using COPY command
[INFO ] Closing database connection
Comando COPY para carregar dados dentro da memória
O código seguinte copia dados dentro da memória para uma tabela.
private static void inMemory(Connection connection) throws SQLException,IOException
{
log.info("Copying inmemory data into table");
final List<String> rows = new ArrayList<>();
rows.add("0,Target,Sunnyvale,California,94001");
rows.add("1,Apollo,Guntur,Andhra,94003");
final BaseConnection baseConnection = (BaseConnection) connection.unwrap(Connection.class);
final CopyManager copyManager = new CopyManager(baseConnection);
// COPY command can change based on the format of rows. This COPY command is for above rows.
final String copyCommand = "COPY pharmacy FROM STDIN with csv";
try (final Reader reader = new StringReader(String.join("\n", rows))) {
copyManager.copyIn(copyCommand, reader);
}
}
Agora, pode adicionar a seguinte linha no método principal:
inMemory(connection);
Executar a classe principal deve agora produzir o seguinte resultado:
[INFO ] Loading application properties
[INFO ] Connecting to the database
[INFO ] Database connection test: citus
[INFO ] Creating table
[INFO ] Creating index
[INFO ] distributing table
[INFO ] Insert data
[INFO ] Read data
[INFO ] Data read from the database: Pharmacy{pharmacy_id=0, pharmacy_name='Target', city='Sunnyvale', state='California', zip_code='94001'}
[INFO ] Update data
[INFO ] Read data
[INFO ] Data read from the database: Pharmacy{pharmacy_id=0, pharmacy_name='Target', city='Guntur', state='California', zip_code='94001'}
[INFO ] Delete data
[INFO ] Read data
[INFO ] There is no data in the database!
5000
[INFO ] Copying in-memory data into table
[INFO ] Closing database connection
Repetição da aplicação para falhas de pedidos de base de dados
Por vezes, é possível que os pedidos de base de dados da sua aplicação falhem. Estes problemas podem ocorrer em diferentes cenários, como a falha de rede entre a aplicação e a base de dados, palavra-passe incorreta, etc. Alguns problemas podem ser transitórios e resolver-se dentro de alguns segundos a minutos. Pode configurar a lógica de repetição na sua aplicação para ultrapassar os erros transitórios.
Configurar a lógica de repetição na sua aplicação ajuda a melhorar a experiência do utilizador final. Em cenários de falha, os utilizadores apenas aguardarão um pouco mais para que a aplicação sirva pedidos, em vez de deparar-se com erros.
O exemplo abaixo mostra como implementar a lógica de repetição na sua aplicação. O fragmento de código de exemplo tenta um pedido de base de dados a cada 60 segundos (até cinco vezes) até ter êxito. O número e a frequência das repetições podem ser configurados com base nas necessidades da sua aplicação.
Neste código, substitua cluster> pelo <nome do cluster e <palavra-passe pela palavra-passe> de administrador.
package test.crud;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.logging.Logger;
import com.zaxxer.hikari.HikariDataSource;
public class DemoApplication
{
private static final Logger log;
static
{
System.setProperty("java.util.logging.SimpleFormatter.format", "[%4$-7s] %5$s %n");
log = Logger.getLogger(DemoApplication.class.getName());
}
private static final String DB_USERNAME = "citus";
private static final String DB_PASSWORD = "<password>";
private static final String DB_URL = "jdbc:postgresql://c-<cluster>.<uniqueID>.postgres.cosmos.azure.com:5432/citus?sslmode=require";
private static final String DB_DRIVER_CLASS = "org.postgresql.Driver";
private static HikariDataSource datasource;
private static String executeRetry(String sql, int retryCount) throws InterruptedException
{
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
for (int i = 1; i <= retryCount; i++)
{
try
{
datasource = new HikariDataSource();
datasource.setDriverClassName(DB_DRIVER_CLASS);
datasource.setJdbcUrl(DB_URL);
datasource.setUsername(DB_USERNAME);
datasource.setPassword(DB_PASSWORD);
datasource.setMinimumIdle(10);
datasource.setMaximumPoolSize(1000);
datasource.setAutoCommit(true);
datasource.setLoginTimeout(3);
log.info("Connecting to the database");
con = datasource.getConnection();
log.info("Connection established");
log.info("Read data");
pst = con.prepareStatement(sql);
rs = pst.executeQuery();
StringBuilder builder = new StringBuilder();
int columnCount = rs.getMetaData().getColumnCount();
while (rs.next())
{
for (int j = 0; j < columnCount;)
{
builder.append(rs.getString(j + 1));
if (++j < columnCount)
builder.append(",");
}
builder.append("\r\n");
}
return builder.toString();
}
catch (Exception e)
{
Thread.sleep(60000);
System.out.println(e.getMessage());
}
}
return null;
}
public static void main(String[] args) throws Exception
{
String result = executeRetry("select 1", 5);
System.out.print(result);
}
}
Passos seguintes
- Veja como a API do Azure Cosmos DB para PostgreSQL expande o PostgreSQL e experimente consultas de diagnóstico úteis
- Escolha o melhor tamanho de cluster para a carga de trabalho
- Monitorizar o desempenho do cluster