Help Center > > Developer Guide

Connecting to a Database

Updated at: Jul 14, 2021 GMT+08:00

After a database is connected, you can run SQL statements the database to perform operations on data.

Function Prototype

JDBC provides the following three database connection methods:

  • DriverManager.getConnection(String url);
  • DriverManager.getConnection(String url, Properties info);
  • DriverManager.getConnection(String url, String user, String password);

Parameter

Table 1 Database connection parameters

Parameter

Description

url

gsjdbc4.jar database connection descriptor. The descriptor format can be:

  • jdbc:postgresql:database
  • jdbc:postgresql://host/database
  • jdbc:postgresql://host:port/database
  • jdbc:postgresql://host:port[,host:port][...]/database
NOTE:

If gsjdbc200.jar is used, replace jdbc:postgresql with jdbc:gaussdb.

  • database: indicates the name of the database to be connected.
  • host: indicates the name or IP address of the database server.

    If the host is on a different network segment from the GaussDB(DWS) it connects to, set the IP address of the server specified by host to the value of mppdb.coo.cooListenIp2 (IP address accessed by applications) set on FusionInsight Manager.

    For security purposes, the CN forbids access from other nodes in the cluster without authentication. To access the CN from inside the cluster, deploy the JDBC program on the host where the CN is located and set host to 127.0.0.1. If you do not do so, the error message "FATAL: Forbid remote connection with trust method!" may be displayed.

    It is recommended that the service system be deployed outside the cluster. If it is deployed inside, the database performance may be affected.

  • port: indicates the port number of a database server.
  • Multiple IP addresses can be configured and separated by commas (,), for example, jdbc:postgresql://10.10.0.13:25308,10.10.0.14:25308/database.

By default, the database on port 8000 of the local host is connected.

info

Database connection properties. Common properties include:

  • user: string type. It indicates the database user establishing a connection.
  • password: string type. It indicates the password of a database user.
  • ssl: Boolean type. It indicates whether the Secure Socket Layer (SSL) is used.
  • loggerLevel: string type. It indicates the amount of information that the driver logs and prints to the LogStream or LogWriter specified in the DriverManager. Currently, OFF, DEBUG, and TRACE are supported. DEBUG indicates that only logs of the DEBUG or higher level are printed, generating a few log information. TRACE indicates that logs of the DEBUG and TRACE levels are printed, generating detailed log information. The default value is OFF, indicating that no information will be logged.
  • prepareThreshold: integer type. It indicates the number of PreparedStatement executions required before SQL statements are switched over to servers as prepared statements. The default value is 5.
  • batchMode: boolean type. It indicates whether to connect the database in batch mode.
  • fetchsize: integer type. It indicates the default fetchsize for statements in the created connection.
  • ApplicationName: string type. It indicates an application name. The default value is PostgreSQL JDBC Driver.
  • allowReadOnly: boolean type. It indicates whether to enable the read-only mode for connection. The default value is false. If the value is not changed to true, the execution of connnection.setReadOnly does not take effect.
  • blobMode: string type. It is used to set the setBinaryStream method to assign values to different data types. The value on indicates that values are assigned to the BLOB data type and off indicates that values are assigned to the bytea data type. The default value is on.
  • connectionExtraInfo: boolean type. It indicates whether the JDBC driver reports the driver deployment path and process owner to the database.
    NOTE:

    Valid value: true or false. The default value is false. If connectionExtraInfo is set to true, the JDBC driver reports the driver deployment path and process owner to the database and displays the information in the connection_info parameter (see connection_info). In this case, you can query the information from PG_STAT_ACTIVITY or PGXC_STAT_ACTIVITY.

user

Indicates a database user.

password

Indicates the password of a database user.

Examples

This sample code illustrates how to develop applications based on the JDBC interface provided by GaussDB(DWS).

Before completing the following example, you need to create a stored procedure.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
create or replace procedure testproc 
(
    psv_in1 in integer,
    psv_in2 in integer,
    psv_inout in out integer
)
as
begin
    psv_inout := psv_in1 + psv_in2 + psv_inout;
end;
/
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
//gsjdbc4.jar is used as an example. If gsjdbc200.jar is used, replace the class name org.postgresql with com.huawei.gauss200.jdbc and replace the URL prefix jdbc:postgresql with jdbc:gaussdb.
//The following code encapsulates database connection operations into an interface. The database can then be connected using an authorized user name and password.

public static Connection GetConnection(String username, String passwd)
    {
        //Set the driver class.
        String driver = "org.postgresql.Driver";
        //Database connection descriptor.
        String sourceURL = "jdbc:postgresql://10.10.0.13:8000/postgres?currentSchema=test";
        Connection conn = null;
        
        try
        {
            //Load the driver.
            Class.forName(driver);
        }
        catch( Exception e )
        {
            e.printStackTrace();
            return null;
        }
        
        try
        {
             //Establish a connection.
            conn = DriverManager.getConnection(sourceURL, username, passwd);
            System.out.println("Connection succeed!");
        }
        catch(Exception e)
        {
            e.printStackTrace();
            return null;
        }
        
        return conn;
    };

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel