Java-app för att ansluta och köra SQL-kommandon på Azure Cosmos DB for PostgreSQL

GÄLLER FÖR: Azure Cosmos DB for PostgreSQL (drivs av Citus-databastillägget till PostgreSQL)

• Python
• Node.js
• C#
• Java
• Ruby

Den här snabbstarten visar hur du använder Java-kod för att ansluta till ett kluster och använder SQL-instruktioner för att skapa en tabell. Sedan infogar, frågar, uppdaterar och tar du bort data i databasen. Stegen i den här artikeln förutsätter att du är bekant med Java-utveckling och JDBC och är nybörjare på att arbeta med Azure Cosmos DB for PostgreSQL.

Konfigurera Java-projektet och anslutningen

Skapa ett nytt Java-projekt och en konfigurationsfil för att ansluta till Azure Cosmos DB for PostgreSQL.

Skapa ett nytt Java-projekt

Använd din favoritmiljö för integrerad utveckling (IDE) och skapa ett nytt Java-projekt med groupId test och artifactId crud. I projektets rotkatalog lägger du till en pom.xml fil med följande innehåll. Den här filen konfigurerar Apache Maven för att använda Java 8 och en ny PostgreSQL-drivrutin för 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>

Konfigurera databasanslutningen

Skapa en application.properties-fil med följande innehåll i src/main/resources/. Ersätt <klustret> med klusternamnet och ersätt <lösenordet> med ditt administrativa lösenord.

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>

Strängen ?ssl=true&sslmode=requiredb.url i egenskapen instruerar JDBC-drivrutinen att använda TLS (Transport Layer Security) vid anslutning till databasen. Det är obligatoriskt att använda TLS med Azure Cosmos DB for PostgreSQL och är en bra säkerhetspraxis.

Skapa tabeller

Konfigurera ett databasschema som har distribuerade tabeller. Anslut till databasen för att skapa schemat och tabellerna.

Generera databasschemat

I src/main/resources/skapar du en schema.sql-fil med följande innehåll:

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);

Distribuera tabeller

Azure Cosmos DB for PostgreSQL ger dig superkraften att distribuera tabeller över flera noder för skalbarhet. Med kommandot nedan kan du distribuera en tabell. Du kan läsa mer om create_distributed_table och distributionskolumnen här.

Anteckning

Genom att distribuera tabeller kan de växa över alla arbetsnoder som läggs till i klustret.

Om du vill distribuera tabeller lägger du till följande rad i filen schema.sql som du skapade i föregående avsnitt.

select create_distributed_table('public.pharmacy','pharmacy_id');

Anslut till databasen och skapa schemat

Lägg sedan till Java-koden som använder JDBC för att lagra och hämta data från klustret. Koden använder filerna application.properties och schema.sql för att ansluta till klustret och skapa schemat.

1. Skapa en DButil.java-fil med följande kod, som innehåller DButil klassen . Klassen DBUtil konfigurerar en anslutningspool till PostgreSQL med hjälp av HikariCP. Du använder den här klassen för att ansluta till PostgreSQL och börja fråga.

   Tips

   Exempelkoden nedan använder en anslutningspool för att skapa och hantera anslutningar till PostgreSQL. Anslutningspooler på programsidan rekommenderas starkt eftersom:

   • Det säkerställer att programmet inte genererar för många anslutningar till databasen och undviker därför att överskrida anslutningsgränserna.
   • Det kan hjälpa dig att drastiskt förbättra prestanda – både svarstid och dataflöde. PostgreSQL-serverprocessen måste förgrenas för att hantera varje ny anslutning, och om du återanvänder en anslutning undviker du det arbetet.

   //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;
       }
   }
   

2. I src/main/java/skapar du en DemoApplication.java-fil som innehåller följande kod:

   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();
       }
   
   }
   

   Anteckning

   Databasen user och password autentiseringsuppgifterna används vid körning av DriverManager.getConnection(properties.getProperty("url"), properties);. Autentiseringsuppgifterna lagras i filen application.properties , som skickas som ett argument.

3. Nu kan du köra den här huvudklassen med ditt favoritverktyg:

   • Med din IDE bör du kunna högerklicka på klassen och köra den DemoApplication .
   • Med Maven kan du köra programmet genom att köra:
     mvn exec:java -Dexec.mainClass="com.example.demo.DemoApplication".

Programmet bör ansluta till Azure Cosmos DB for PostgreSQL, skapa ett databasschema och sedan stänga anslutningen, som du bör se i konsolloggarna:

[INFO   ] Loading application properties
[INFO   ] Connecting to the database
[INFO   ] Database connection test: citus
[INFO   ] Create database schema
[INFO   ] Closing database connection

Skapa en domänklass

Skapa en ny Pharmacy Java-klass bredvid DemoApplication klassen och lägg till följande kod:

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 + '\'' +
               '}';
    }
}

Den här klassen är en domänmodell som mappas i tabellen Pharmacy som du skapade när du körde skriptet schema.sql .

Infoga data

Efter -metoden i filen mainDemoApplication.java lägger du till följande metod som använder SQL-instruktionen INSERT INTO för att infoga data i databasen:

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();
}

Lägg till följande två rader i main-metoden:

Pharmacy todo = new Pharmacy(0,"Target","Sunnyvale","California",94001);
insertData(todo, connection);

Körningen av huvudklassen bör nu generera följande utdata:

[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

Läsa data

Läs de data som du infogade tidigare för att verifiera att koden fungerar korrekt.

Efter metoden i filen insertDataDemoApplication.java lägger du till följande metod som använder SELECT SQL-instruktionen för att läsa data från databasen:

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;
}

Lägg till följande rad i main-metoden:

todo = readData(connection);

Körningen av huvudklassen bör nu generera följande utdata:

[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

Uppdatera data

Uppdatera de data som du infogade tidigare.

Efter -metoden i filen readDataDemoApplication.java lägger du till följande metod för att uppdatera data i databasen med hjälp av SQL-instruktionen 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);
}

Lägg till följande två rader i main-metoden:

todo.setcity("Guntur");
updateData(todo, connection);

Körningen av huvudklassen bör nu generera följande utdata:

[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

Ta bort data

Slutligen tar du bort de data som du infogade tidigare. Efter -metoden i filen updateDataDemoApplication.java lägger du till följande metod för att ta bort data i databasen med hjälp av SQL-instruktionen 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);
}

Nu kan du lägga till följande rad i main-metoden:

deleteData(todo, connection);

Körningen av huvudklassen bör nu generera följande utdata:

[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

COPY-kommando för snabb inmatning

COPY-kommandot kan ge ett enormt dataflöde när data matas in i Azure Cosmos DB for PostgreSQL. Kommandot COPY kan mata in data i filer eller från mikrobatchar med data i minnet för inmatning i realtid.

COPY-kommandot för att läsa in data från en fil

Följande kod kopierar data från en CSV-fil till en databastabell. Kodexemplet kräver att filen 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;
}

Nu kan du lägga till följande rad i main-metoden:

int c = (int) copyFromFile(connection,"C:\\Users\\pharmacies.csv", "pharmacy");
log.info("Copied "+ c +" rows using COPY command");

Körningen av main klassen bör nu ge följande utdata:

[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

COPY-kommandot för att läsa in minnesintern data

Följande kod kopierar minnesintern data till en tabell.

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);
    }
}

Nu kan du lägga till följande rad i main-metoden:

inMemory(connection);

Körningen av huvudklassen bör nu generera följande utdata:

[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

Appåterförsök för databasbegärandefel

Det är ibland möjligt att databasbegäranden från ditt program misslyckas. Sådana problem kan inträffa i olika scenarier, till exempel nätverksfel mellan app och databas, felaktigt lösenord osv. Vissa problem kan vara tillfälliga och lösa sig själva inom några sekunder till minuter. Du kan konfigurera omprövningslogik i din app för att lösa de tillfälliga felen.

Genom att konfigurera omförsökslogik i din app kan du förbättra slutanvändarupplevelsen. Under felscenarier väntar användarna bara lite längre på att programmet ska hantera begäranden i stället för att uppleva fel.

Exemplet nedan visar hur du implementerar omprövningslogik i din app. Exempelkodfragmentet provar en databasbegäran var 60:e sekund (upp till fem gånger) tills den lyckas. Antalet och frekvensen för återförsök kan konfigureras baserat på programmets behov.

I den här koden ersätter du <klustret> med klustrets namn och <lösenord> med administratörslösenordet.

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);
    }
}

Nästa steg

• Se hur Azure Cosmos DB for PostgreSQL API utökar PostgreSQL och prova användbara diagnostikfrågor
• Välj den bästa klusterstorleken för din arbetsbelastning
• Övervaka klusterprestanda