pg数据库增删改查操作

pom.xml添加依赖：

<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.4.2</version>
</dependency>

 

package demo.pg;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

 

public class PGtest {

public static void main(String[] args) {
// TODO Auto-generated method stub
try {
Connection c = Connect();
// createTable(c);
// insertTable(c);
// selectTable(c);
selectADVTable(c);
c.close();
} catch (Exception e) {
e.printStackTrace();
System.err.println(e.getClass().getName() + ": " + e.getMessage());
System.exit(0);
}
}

public static Connection Connect() {
Connection c = null;
try {

Class.forName("org.postgresql.Driver");
c = DriverManager.getConnection("jdbc:postgresql://xxxx:5555/qrdbutf", "postgres", "postgres");

} catch (Exception e) {
e.printStackTrace();
System.err.println(e.getClass().getName() + ": " + e.getMessage());
System.exit(0);
}
System.out.println("Opened database successfully");
return c; //
}

public static void createTable(Connection c) {
try {
String sqlDrop = "drop table if exists pg_test01";

String sql = "CREATE TABLE pg_test01 " + "(ID INT PRIMARY KEY NOT NULL,"
+ " NAME TEXT NOT NULL, " + " AGE INT NOT NULL, "
+ " ADDRESS CHAR(50), " + " SALARY REAL)";

Statement statement = c.createStatement();

statement.executeUpdate(sqlDrop);
statement.executeUpdate(sql);
statement.close();
} catch (Exception e) {
e.printStackTrace();
System.err.println(e.getClass().getName() + ": " + e.getMessage());
System.exit(0);
}
System.out.println("create table successfully");
}

public static void insertTable(Connection c) {
try {

String sql = "insert into pg_test01 values(0,'pg',18,'addr',888888888) ";

Statement statement = c.createStatement();

statement.executeUpdate(sql);
statement.close();
} catch (Exception e) {
e.printStackTrace();
System.err.println(e.getClass().getName() + ": " + e.getMessage());
System.exit(0);
}
System.out.println("insert table successfully");
}

public static void selectTable(Connection c) {
try {
Statement statement = c.createStatement();

ResultSet rs = statement.executeQuery("SELECT * FROM pg_test01;");

while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String address = rs.getString("address");
float salary = rs.getFloat("salary");
System.out.println("ID = " + id);
System.out.println("NAME = " + name);
System.out.println("AGE = " + age);
System.out.println("ADDRESS = " + address);
System.out.println("SALARY = " + salary);
System.out.println();
}
rs.close();
statement.close();
} catch (Exception e) {
e.printStackTrace();
System.err.println(e.getClass().getName() + ": " + e.getMessage());
System.exit(0);
}
System.out.println("select table successfully");
}

public static void selectADVTable(Connection c) {
try {
Statement statement = c.createStatement();
String s = "SELECT * FROM tmp_pg;";


ResultSet rs = statement.executeQuery(s);


ResultSetMetaData m=rs.getMetaData();
int columns=m.getColumnCount();
for(int i=1;i<=columns;i++) {
System.out.print(m.getColumnName(i)+" ");
}
System.out.println();
while(rs.next()){

for(int i=1;i<=columns;i++){
if(rs.getString(i)==null) {
System.out.print("|"+rs.getString(i));
}else {
// System.out.print("|"+rs.getString(i).replace(" ",""));
System.out.print("|"+rs.getString(i));
}
}
System.out.println();

}
rs.close();
statement.close();
} catch (Exception e) {
e.printStackTrace();
System.err.println(e.getClass().getName()+": "+e.getMessage());
System.exit(0);
}
System.out.println("select table successfully");
}
}