Connect to openGauss database (JAVA)
Tags: opngauss
Published: 2023-03-28 03:33:57

Java
JDBC (Java Database Connectivity, Java database connection) is a Java API for executing SQL statements, which can provide a unified access interface for various relational databases, and applications can operate data based on it. The openGauss library provides support for JDBC 4.0 features. It needs to use JDK1.8 version to compile the program code, and does not support the JDBC bridging ODBC method.

Execute build.sh in the source code directory of the Linux server to obtain the driver jar package postgresql.jar, which is located in the source code directory. Obtained from the release package, the package name is openGauss-xxxx-operating system version number-64bit-Jdbc.tar.gz.

The driver package is compatible with PostgreSQL, and the class name and class structure are completely consistent with the PostgreSQL driver. Applications that used to run on PostgreSQL can be directly transplanted to the current system for use.

load driver
Before creating a database connection, the database driver class needs to be loaded, and the package locations of different driver classes are different. The driver of openGauss jdbc is "org.opengauss.Driver", and the url prefix is ​​"jdbc:opengauss".

Connect to the database
Before connecting to the database, add the host address (bold and underlined) to pg_hba.conf (this file is under the datanode folder in the installation directory), and the format is as follows:

host    all    all    127.0.0.1/32    sha256
JDBC provides three methods for creating database connections.

There are two common ways to execute statements: Statement and PreparedStatement:

Statement:

execute: The return value is boolean type, and the return value cannot be seen when the query statement is executed.
executeQuery: The return value is the ResultSet type, which is usually used for query, and the method of use is to directly write SQL statements in it.
PreparedStatement:

Contains two methods of Statement, but they are used differently. You must first write the statement in conn.prepareStatement(), and then execute execute or executeQuery as needed. The input parameters in these two methods are empty. If there are variables in the SQL statement, it is recommended to use PreparedStatement to effectively prevent SQL injection. Use ? to replace the variable, and then use the setObject method to assign it.

Example:

 Note:  In the example below, the bold underlined fields need to be replaced with the user's own information, among which:

127.0.0.1 : The host address where the database is located.
8080 : The port for the database connection.
test : The name of the database to connect to. If the database does not exist, an error will be reported. Please confirm whether the database exists before using it.
myuser : The username to connect to the database.
myPassWord : The password of the user connecting to the database.
DriverManager.getConnection(String url);

This connection method requires splicing the username and password on the url.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcConn {
  public static void main(String[] args) {
        getConnect();
  }
  public static Connection getConnect() {
        String driver = "org.opengauss.Driver";
        String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test?user=myuser&password=myPassWord";
        Properties info = new Properties();
        Connection conn = null;
        try {
            Class.forName(driver);
        } catch (Exception var9) {
            var9.printStackTrace();
            return null;
        }
        try {
            conn = DriverManager.getConnection(sourceURL);
            System.out.println("连接成功！");
            return conn;
        } catch (Exception var8) {
            var8.printStackTrace();
            return null;
        }
    }
}
DriverManager.getConnection(String url, Properties info);

Parameters such as user name and password of this method are added through setProperty in the instance of the Properties object.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcConn {
  public static void main(String[] args) {
        getConnect();
  }
 public static Connection getConnect() {
        String driver = "org.opengauss.Driver";
        String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test";
        Properties info = new Properties();
        info.setProperty("user","myuser");
        info.setProperty("password","myPassWord");
        Connection conn = null;
        try {
            Class.forName(driver);
        } catch (Exception var9) {
            var9.printStackTrace();
            return null;
        }
        try {
            conn = DriverManager.getConnection(sourceURL, info);
            System.out.println("连接成功！");
            return conn;
        } catch (Exception var8) {
            var8.printStackTrace();
            return null;
        }
    }
}
DriverManager.getConnection(String url, String user, String password);

This method requires username and password to be entered as variables.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcConn {
  public static void main(String[] args) {
        getConnect();
  }
 public static Connection getConnect() {
        String driver = "org.opengauss.Driver";
        String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test";
        String username="myuser";
        String passwd="myPassWord";
        Connection conn = null;
        try {
            Class.forName(driver);
        } catch (Exception var9) {
            var9.printStackTrace();
            return null;
        }
        try {
            conn = DriverManager.getConnection(sourceURL, username, passwd);
            System.out.println("连接成功！");
            return conn;
        } catch (Exception var8) {
            var8.printStackTrace();
            return null;
        }
    }
}