Java连接PostgreSQL数据库，增删改查_jdbc postgresql 更新数据

通过eclipse工具，新建Maven项目：

添加：postgresql的jar包（我使用的是：版本：42.2.2）

修改pom.xml文件：

<!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
	<dependency>
	    <groupId>org.postgresql</groupId>
	    <artifactId>postgresql</artifactId>
	    <version>42.2.2</version>
	</dependency>

一、Java通过JDBC连接到PostgreSQL数据库

package com.accord;
 
import java.sql.Connection;
import java.sql.DriverManager;
 
public class PostgreSqlJdbcConn {
	 @SuppressWarnings("unused")
	public static void main(String args[]) {
	      Connection c = null;
	      try {
	         Class.forName("org.postgresql.Driver");
	         c = DriverManager
	            .getConnection("jdbc:postgresql://localhost:5432/db_person",
	            "postgres", "123456");
	      } catch (Exception e) {
	         e.printStackTrace();
	         System.err.println(e.getClass().getName()+": "+e.getMessage());
	         System.exit(0);
	      }
	      System.out.println("Opened database successfully");
	   }
}

运行结果：Opened database successfully

二、创建表

package com.accord;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
 
public class PostgreSqlJdbcConnCreateTable {
	public static void main(String args[]) {
	      Connection c = null;
	      Statement stmt = null;
	      try {
	         Class.forName("org.postgresql.Driver");
	         c = DriverManager
	            .getConnection("jdbc:postgresql://localhost:5432/db_person",
	            "postgres", "123456");
	         System.out.println("连接数据库成功！");
	         stmt = c.createStatement();
	         String sql = "CREATE TABLE COMPANY02 " +
                     "(ID INT PRIMARY KEY     NOT NULL," +
                     " NAME           TEXT    NOT NULL, " +
                     " AGE            INT     NOT NULL, " +
                     " ADDRESS        CHAR(50), " +
                     " SALARY         REAL)";
	         stmt.executeUpdate(sql);
	         stmt.close();
	         c.close();
	         
	      } catch (Exception e) {
	         e.printStackTrace();
	         System.err.println(e.getClass().getName()+": "+e.getMessage());
	         System.exit(0);
	      }
	      System.out.println("新表创建成功！");
	   }
}

运行结果：

连接数据库成功！

新表创建成功！

三、插入数据

package com.accord;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
 
public class PostgreSqlJdbcConnAddDatas {
	public static void main(String args[]) {
		Connection c = null;
		Statement stmt = null;
		try {
			Class.forName("org.postgresql.Driver");
			c = DriverManager.getConnection(
					"jdbc:postgresql://localhost:5432/db_person", "postgres",
					"123456");
			c.setAutoCommit(false);
 
			System.out.println("连接数据库成功！");
			stmt = c.createStatement();
 
			String sql = "INSERT INTO COMPANY02 (ID,NAME,AGE,ADDRESS,SALARY) "
					+ "VALUES (1, 'Paul', 32, 'California', 20000.00 );";
			stmt.executeUpdate(sql);
 
			sql = "INSERT INTO COMPANY02 (ID,NAME,AGE,ADDRESS,SALARY) "
					+ "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
			stmt.executeUpdate(sql);
 
			sql = "INSERT INTO COMPANY02 (ID,NAME,AGE,ADDRESS,SALARY) "
					+ "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
			stmt.executeUpdate(sql);
 
			sql = "INSERT INTO COMPANY02 (ID,NAME,AGE,ADDRESS,SALARY) "
					+ "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
			stmt.executeUpdate(sql);
 
			stmt.close();
			c.commit();
			c.close();
 
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			System.exit(0);
		}
		System.out.println("新增数据成功！");
	}
}

运行结果：

连接数据库成功！

新增数据成功！

四、查询数据

package com.accord;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
 
import javax.naming.spi.DirStateFactory.Result;
 
public class PostgreSqlJdbcConnSelectDatas {
	public static void main(String args[]) {
		Connection c = null;
		Statement stmt = null;
		try {
			Class.forName("org.postgresql.Driver");
			c = DriverManager.getConnection(
					"jdbc:postgresql://localhost:5432/db_person", "postgres",
					"123456");
			c.setAutoCommit(false);
 
			System.out.println("连接数据库成功！");
			stmt = c.createStatement();
 
			ResultSet rs = stmt.executeQuery("select * from company02");
			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 + "," + name + "," + age + "," + address.trim() + "," + salary);
			}
 
			rs.close();
			stmt.close();
			
			c.close();
 
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			System.exit(0);
		}
		System.out.println("查询数据成功！");
	}
}

运行结果：

连接数据库成功！

查询数据成功！

五、更新数据

package com.accord;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
 
import javax.naming.spi.DirStateFactory.Result;
 
public class PostgreSqlJdbcConnUpdateData {
	public static void main(String args[]) {
		Connection c = null;
		Statement stmt = null;
		try {
			Class.forName("org.postgresql.Driver");
			c = DriverManager.getConnection(
					"jdbc:postgresql://localhost:5432/db_person", "postgres",
					"123456");
			c.setAutoCommit(false);
 
			System.out.println("连接数据库成功！");
			stmt = c.createStatement();
			
			String sql = "Delete from COMPANY02 where ID=4 ";
			stmt.executeUpdate(sql);
			c.commit();
 
			ResultSet rs = stmt.executeQuery("select * from company02 order by id");
			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 + "," + name + "," + age + "," + address.trim() + "," + salary);
			}
 
			rs.close();
			stmt.close();
			
			c.close();
 
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			System.exit(0);
		}
		System.out.println("更新数据成功！");
	}
}

运行结果：

连接数据库成功！

更新数据成功！

六、删除数据

package com.accord;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
 
import javax.naming.spi.DirStateFactory.Result;
 
public class PostgreSqlJdbcConnDeleteData {
	public static void main(String args[]) {
		Connection c = null;
		Statement stmt = null;
		try {
			Class.forName("org.postgresql.Driver");
			c = DriverManager.getConnection(
					"jdbc:postgresql://localhost:5432/db_person", "postgres",
					"123456");
			c.setAutoCommit(false);
 
			System.out.println("连接数据库成功！");
			stmt = c.createStatement();
			
			String sql = "UPDATE COMPANY02 set SALARY = 250 where ID=1 ";
			stmt.executeUpdate(sql);
			c.commit();
 
			ResultSet rs = stmt.executeQuery("select * from company02 order by id");
			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 + "," + name + "," + age + "," + address.trim() + "," + salary);
			}
 
			rs.close();
			stmt.close();
			
			c.close();
 
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			System.exit(0);
		}
		System.out.println("删除数据成功！");
	}
}

运行结果：

连接数据库成功！

1,Paul,32,California,250.0
2,Allen,25,Texas,15000.0
3,Teddy,23,Norway,20000.0

删除数据成功！