java jdbc 链接pg_Java连接PostgreSQL数据库

Java连接到PostgreSQL数据库

以下Java代码显示如何连接到现有数据库。 如果数据库不存在，那么它将被创建，最后将返回一个数据库对象。

import java.sql.Connection;

import java.sql.DriverManager;

public class PostgreSQLJDBC {

public static void main(String args[]) {

Connection c = null;

try {

Class.forName("org.postgresql.Driver");

c = DriverManager

.getConnection("jdbc:postgresql://localhost:5432/testdb",

"postgres", "123");

} catch (Exception e) {

e.printStackTrace();

System.err.println(e.getClass().getName()+": "+e.getMessage());

System.exit(0);

}

System.out.println("Opened database successfully");

}

}

Java

在编译并运行上述程序之前，请在PostgreSQL安装目录中找到pg_hba.conf文件并添加以下行：

# IPv4 local connections:

host all all 127.0.0.1/32 md5

Shell

您可以启动/重新启动postgres服务器，使用以下命令运行：

[root@host]# service postgresql restart

Stopping postgresql service: [ OK ]

Starting postgresql service: [ OK ]

Shell

现在，我们来编译并运行上面的程序来获得与testdb的连接。 在这里使用用户ID为postgres和密码为123来访问数据库。 您可以根据数据库配置和设置进行更改。 我们还假定当前版本的JDBC驱动程序postgresql-9.2-1002.jdbc3.jar在当前路径中(c:\tools\)可用。

C:\JavaPostgresIntegration>javac PostgreSQLJDBC.java

C:\JavaPostgresIntegration>java -cp c:\tools\postgresql-9.2-1002.jdbc3.jar;C:\JavaPostgresIntegration PostgreSQLJDBC

Open database successfully

Shell

创建表

以下Java程序将用于在之前打开的数据库中创建一个表。确保目标数据库中没有此表。

import java.sql.*;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Statement;

public class PostgreSQLJDBC {

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/testdb",

"manisha", "123");

System.out.println("Opened database successfully");

stmt = c.createStatement();

String sql = "CREATE TABLE COMPANY " +

"(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 ) {

System.err.println( e.getClass().getName()+": "+ e.getMessage() );

System.exit(0);

}

System.out.println("Table created successfully");

}

}

Java

编译和执行程序时，将在testdb数据库中创建COMPANY表，并显示以下两行：

Opened database successfully

Table created successfully

Shell

插入数据操作

以下Java程序显示了如何在上述示例中创建的COMPANY表中创建/插入数据记录：

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Statement;

public class PostgreSQLJDBC {

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/testdb",

"manisha", "123");

c.setAutoCommit(false);

System.out.println("Opened database successfully");

stmt = c.createStatement();

String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "

+ "VALUES (1, 'Paul', 32, 'California', 20000.00 );";

stmt.executeUpdate(sql);

sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "

+ "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";

stmt.executeUpdate(sql);

sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "

+ "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";

stmt.executeUpdate(sql);

sql = "INSERT INTO COMPANY (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) {

System.err.println( e.getClass().getName()+": "+ e.getMessage() );

System.exit(0);

}

System.out.println("Records created successfully");

}

}

Java

程序编译执行后，将在COMPANY表中创建/插入给定的记录，并显示以下两行：

Opened database successfully

Records created successfully

Shell

SELECT操作

以下Java程序显示了如何从上述示例中创建的COMPANY表中获取和显示记录：

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

public class PostgreSQLJDBC {

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/testdb",

"manisha", "123");

c.setAutoCommit(false);

System.out.println("Opened database successfully");

stmt = c.createStatement();

ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );

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();

stmt.close();

c.close();

} catch ( Exception e ) {

System.err.println( e.getClass().getName()+": "+ e.getMessage() );

System.exit(0);

}

System.out.println("Operation done successfully");

}

}

Java

程序编译执行时，会产生以下结果：

Opened database successfully

ID = 1

NAME = Paul

AGE = 32

ADDRESS = California

SALARY = 20000.0

ID = 2

NAME = Allen

AGE = 25

ADDRESS = Texas

SALARY = 15000.0

ID = 3

NAME = Teddy

AGE = 23

ADDRESS = Norway

SALARY = 20000.0

ID = 4

NAME = Mark

AGE = 25

ADDRESS = Rich-Mond

SALARY = 65000.0

Operation done successfully

Shell

更新操作

以下Java代码显示了如何使用UPDATE语句来更新指定记录，然后从COMPANY表中获取和显示更新的记录：

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

public class PostgreSQLJDBC {

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/testdb",

"manisha", "123");

c.setAutoCommit(false);

System.out.println("Opened database successfully");

stmt = c.createStatement();

String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";

stmt.executeUpdate(sql);

c.commit();

ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );

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();

stmt.close();

c.close();

} catch ( Exception e ) {

System.err.println( e.getClass().getName()+": "+ e.getMessage() );

System.exit(0);

}

System.out.println("Operation done successfully");

}

}

SQL

程序编译执行时，会产生以下结果：

Opened database successfully

ID = 2

NAME = Allen

AGE = 25

ADDRESS = Texas

SALARY = 15000.0

ID = 3

NAME = Teddy

AGE = 23

ADDRESS = Norway

SALARY = 20000.0

ID = 4

NAME = Mark

AGE = 25

ADDRESS = Rich-Mond

SALARY = 65000.0

ID = 1

NAME = Paul

AGE = 32

ADDRESS = California

SALARY = 25000.0

Operation done successfully

Shell

删除操作

以下Java代码显示了如何使用DELETE语句删除指定记录，然后从COMPANY表中获取并显示剩余的记录：

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

public class PostgreSQLJDBC6 {

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/testdb",

"manisha", "123");

c.setAutoCommit(false);

System.out.println("Opened database successfully");

stmt = c.createStatement();

String sql = "DELETE from COMPANY where ID=2;";

stmt.executeUpdate(sql);

c.commit();

ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );

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();

stmt.close();

c.close();

} catch ( Exception e ) {

System.err.println( e.getClass().getName()+": "+ e.getMessage() );

System.exit(0);

}

System.out.println("Operation done successfully");

}

}

Java

程序编译执行时，会产生以下结果：

Opened database successfully

ID = 3

NAME = Teddy

AGE = 23

ADDRESS = Norway

SALARY = 20000.0

ID = 4

NAME = Mark

AGE = 25

ADDRESS = Rich-Mond

SALARY = 65000.0

ID = 1

NAME = Paul

AGE = 32

ADDRESS = California

SALARY = 25000.0

Operation done successfully

转正网站：http://www.yiibai.com/postgresql/postgresql_java.html