1, Tools
(1) Java development platform: IntelliJ IDEA 2018.3.6
(2) PgSQL database: postgresql-9.5.21 (other versions also work)
(3) Database management software: Navicat Premium 15
(4) PgSQL connection driver: postgresql-42.3.3 jar
2, Preparatory work
Before connecting, you should complete the following work:
(1) Create a new JAVA project file in IDEA
(2) Download and install PgSQL database
(3) Download and install Navicat and build PgSQL database
It is relatively easy to create a JAVA project file in the IDEA, download and install the PgSQL database, and download and install Navicat. I won't repeat it again. Next, let's introduce how to build PgSQL database in Navicat. The operation process is as follows:
(1) Hit Navicat, the database management software, click the connection in the upper left corner and select PostgreSQL.
(2) Fill in the connection information. Here, just fill in the connection name and password (the password set when installing PgSQL), and other defaults. localhost is the local address and 5432 is the port of PgSQL database. After filling in the connection information, click test connection. If the connection is successful, the dialog box indicates that PgSQL Server has been opened. Click OK to complete the connection of PgSQL database.
(3) After the connection is completed, you will see the connection information on the left. postgres under the Bolisen subdirectory is an initial PgSQL database, which can be used directly. You can also select postgres, right-click and select new database to create a new PgSQL database.
(4) In order to test the connection, create an employee information table in the postgres database to facilitate the later demonstration; Right click the table in the public subdirectory and select new table - > general.
.
(5) Add corresponding fields to the employee information table, such as id, job number, name, etc. Take the id job number as an example, where the name represents the field name; Type indicates the data type of the field; Length indicates the length of the field, which is generally the default; Not null means that the field cannot be empty; Key indicates that this field cannot be repeated. Here, the employee id and job number cannot be repeated; A comment indicates an explanation of the field. After adding fields, click save, fill in the name of the form and click Save to complete the creation of employee information form.
(6) Open the employee information table and add employee information to the table.
(7) Create a new query to query employee information by writing SQL statements, for example: query employee XiaoYang's id and job number. Since then, the construction of PgSQL database has been completed.
3, Java connection to PgSQL database
1. Create a Java project in the Java development platform IntelliJ IDEA. Right click File and select Project Structure. Click Modules on the left, and then click "+" on the right to import the driver package postgresql-42.3.3, which is necessary for IDEA to connect to PgSQL database jar. Click Apply - > OK.
2. Find and open the Database in the engineering interface, click "+" and select Database Source to find PgSQL. After filling in the Database name, user name and password to be accessed, click Test Connection to see Successful, indicating that the connection between IDEA and PgSQL Database is Successful.
3. After the connection is successful, use IDEA to query and operate the PgSQL database. The process is as follows:
(1) The code tests whether the database connection is successful
// Load driver
private static String url = "jdbc:postgresql://localhost:5432/postgres";//postgres is the database to which you want to connect
private static String user = "postgres";//PgSQL login
private static String pass = "<PgSQL Login password>";//Write your previous settings
private static Connection con;
public static void main(String[] args) throws Exception {
Connection con;
try {
//Register JDBC Driver
Class.forName("org.postgresql.Driver");
//Establish connection
con = DriverManager.getConnection(url, user, pass);
if (!con.isClosed()) {
System.out.println("Database connection succeeded");
}
con.close();
} catch (ClassNotFoundException e) {
System.out.println("The database driver is not installed");
} catch (SQLException e) {
e.printStackTrace();
System.out.println("Database connection failed");
}
}
(2) Write SQL statements to query the operation database, for example: query the id and job number of employee Xiaoming
import java.sql.*;
public class Main {
public static void main(String[] args) throws Exception {
//Load driver
Class.forName("org.postgresql.Driver");
String url="jdbc:postgresql://localhost:5432/postgres";
String user="postgres";
String password="<PgSQL Login password>";//Write your previous settings
//Establish connection
Connection conn = null;
Statement stmt = null;
conn= DriverManager.getConnection(url,user,password);
stmt=conn.createStatement();
//Compiling and executing SQL statements
String sql = "SELECT id FROM \"Yuangong\" WHERE name='XiaoYang';";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next())
{
int i=1;
for(i=1;i<=1;i++){ //Because the id length is 1, I < = 1 here
System.out.println(rs.getInt(i));
}
System.out.println();
}
}
}