在Java程序中使用PostgreSQL之前，我们需要确保在机器上安装了PostgreSQL JDBC和Java。 您可以在机器上检查是否正确安装了Java：Java教程。 现在我们来看一下如何设置PostgreSQL JDBC驱动。
从postgresql-jdbc存储库下载最新版本的
postgresql-(VERSION).jdbc.jar。在类路径中添加下载的
jar文件postgresql-(VERSION).jdbc.jar，或者您可以使用-classpath选项，如下面的例子所述。
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")
}
}
在编译并运行上述程序之前，请在PostgreSQL安装目录中找到pg_hba.conf文件并添加以下行：
# IPv4 local connections:
host all all 127.0.0.1/32 md5
您可以启动/重新启动postgres服务器，使用以下命令运行：
[root@host]# service postgresql restart
Stopping postgresql service: [ OK ]
Starting postgresql service: [ OK ]
现在，我们来编译并运行上面的程序来获得与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.jarC:\JavaPostgresIntegration PostgreSQLJDBC
Open database successfully
创建表
以下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")
}
}
编译和执行程序时，将在testdb数据库中创建COMPANY表，并显示以下两行：
Opened database successfully
Table created successfully
插入数据操作
以下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, &aposPaul&apos, 32, &aposCalifornia&apos, 20000.00 )"
stmt.executeUpdate(sql)
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
+ "VALUES (2, &aposAllen&apos, 25, &aposTexas&apos, 15000.00 )"
stmt.executeUpdate(sql)
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
+ "VALUES (3, &aposTeddy&apos, 23, &aposNorway&apos, 20000.00 )"
stmt.executeUpdate(sql)
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
+ "VALUES (4, &aposMark&apos, 25, &aposRich-Mond &apos, 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")
}
}
程序编译执行后，将在COMPANY表中创建/插入给定的记录，并显示以下两行：
Opened database successfully
Records created successfully
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")
}
}
程序编译执行时，会产生以下结果：
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
更新操作
以下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")
}
}
程序编译执行时，会产生以下结果：
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
删除操作
以下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")
}
}
程序编译执行时，会产生以下结果：
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