postgreSQL连接 java接口

1.下载PostgreSQL JDBC驱动：

    http://jdbc.postgresql.org/download.html

2. 新建一个java项目，导入下载的jar包Add External JARs

3. 连接代码如下：

import java.sql.*;
import java.io.*;
import java.util.StringTokenizer;
import java.util.Properties;
import java.util.Random;

/**
 * Class <b>DBWrapper</b> contains 
 * wrapper routines for using JDBC
 * to access the database.
 *
 * @author luwei
 * @version 1.0
 */
public class DBWrapper
{
    
    private static int CONNECTION_RETRIES = 10;
    private static int QUERY_RETRIES = 10;
    
    private String dbUrl;
    private String username;
    private String password;
    private String jdbcClassName;
    private Connection dbCon;    

    private boolean hasError = false;
    private String errorString = null;
    private static DBWrapper myInstance = null;
    
    /**
     * DBWrapper constructor
     */
    public DBWrapper() {}

    /**
     * DBWrapper conscrutor
     * @param inUrl String url of database
     * @param inJdbcClassName String containing name of jdbc driver
     * @param inUserName String containing database username
     * @param inPassWord String containing database password
     */

    public DBWrapper( String inUrl, String inJdbcClassName, String inUserName, String inPassWord )
    throws Exception {
        dbUrl = inUrl;
        jdbcClassName = inJdbcClassName;
        username = inUserName;
        password = inPassWord;
        connect();
    }
    
    /**
     * connectAsDefaultCteLibrary()
     * Create a connection to the CTE library using the default connection parameters.
     * @return void
     */
    public void connectAsDefaultDatabase()
    throws Exception {
        myInstance.connect("jdbc:postgresql://localhost:5432/postgres",
                "org.postgresql.Driver", "postgres", "741613551");

    }

    /**
     * closeConnections closes any currently open connections
     * @return void
     */
    private void closeConnections() 
    throws Exception {
        if (dbCon!=null) {
            dbCon.close();
        }
    }
    
    /**
     * DBWrapper Instance()
     * Get a singleton instance of the DBWrapper object.
     * @return DBWrapper
     */
    public static DBWrapper Instance() 
    throws Exception {
        if (myInstance == null) {
            myInstance = new DBWrapper();
            myInstance.connectAsDefaultDatabase();
        }
        return myInstance;
    }

    /**
     * boolean connect()
     * Connect to a database using the parameters supplied in the constructor.
     * @return boolean
     */
    private boolean connect() 
    throws Exception {
    
        boolean opened = false;

        DriverManager.registerDriver(new org.postgresql.Driver());
        // Try to open a connection the database.
        int retry = 0; 
        while(retry++ < CONNECTION_RETRIES){
            Class.forName(jdbcClassName);
            dbCon = DriverManager.getConnection(dbUrl,username,password);
            break;
            }
        if(retry < CONNECTION_RETRIES)opened=true;
        return opened;
    }

    
    /**
     * boolean connect()
     * Connect to a JDBC datasource without using the parameters supplied in the constructor.
     * @param inUrl String url of database
     * @param inJdbcClassName String containing name of jdbc driver
     * @param inUserName String containing database username
     * @param inPassWord String containing database password
     * @return boolean
     */
    public boolean connect( String inUrl, String inJdbcClassName, String inUserName, String inPassWord ) 
    throws Exception {
        dbUrl = inUrl;
        jdbcClassName = inJdbcClassName;
        username = inUserName;
        password = inPassWord;
        closeConnections();
    
        return connect();
    }
    
    /**
     * ResultSet runQuery()
     * Executes a query and returns a resultset.  
     *
     * @param String containing a SQL statement
     * @return ResultSet
     */
    public ResultSet runQuery( String sqlQuery ) 
    throws Exception {
        Statement statement=dbCon.createStatement();
        ResultSet resultSet=statement.executeQuery(sqlQuery);
        return resultSet;
    }
    
    /**
     * boolean runUpdate()
     * Executes an update and returns true of successfully executed.  
     *
     * @param String containing a SQL statement
     * @return boolean
     */    
    public boolean runUpdate( String sqlQuery ) 
    throws Exception {
        Statement ps=dbCon.createStatement();
        boolean wasExecuted = false;
        int retry = 0; 
        while(retry++ < CONNECTION_RETRIES){
            ps.executeUpdate(sqlQuery);
            break;
        }
        if(retry < CONNECTION_RETRIES)wasExecuted=true;
        
        
        return wasExecuted;
    }
    
    /**
     * ResultSet runChainedQuery()
     * Executes a chained mode transaction query.  
     *
     * @param <b>String</b> containing a SQL statement
     * @param <b>String</b> containing the isolation level to run the transaction.
     * @return ResultSet
     */  
    public ResultSet runChainedQuery( String sqlQuery, String isolationLevel ) 
    throws Exception {
    
        int retry = 0;
    
        //Create the resultset and statement object.
        ResultSet resultSet = null;
        Statement dbStatement = null;
        // Connect to the database.
        dbStatement = dbCon.createStatement();
        // Retry the query until complete or timeout.
        while (retry++ < QUERY_RETRIES) {
        // Begin a transaction.
        dbStatement.executeUpdate( "Begin Transaction" );
        // Set the isolation level.
        dbStatement.executeUpdate( new String( "Set Transaction Isolation level " + isolationLevel ) );
        // Execute the query.
        resultSet = dbStatement.executeQuery( sqlQuery );
        // Commit the transaction.
        dbStatement.executeUpdate( "commit" );
        // Close the connection.
        dbStatement.close();
        break;
        }
        return resultSet;
    }
    
    /**
     * boolean runChainedUpdate()
     * Executes a chained mode transaction query.  
     *
     * @param String[] containing a series of SQL statments
     * @param String containing the isolation level to run the transaction.
     * @return boolean
     */  
    public boolean runChainedUpdate( String [] sqlQuery, String isolationLevel ) 
    throws Exception {
        int retry = 0;
    
        // Create the statement object.
        Statement dbStatement = null;
        boolean wasExecuted = false;
        // Connect to the database.
        dbStatement = dbCon.createStatement();
    
        while (retry++ < QUERY_RETRIES) {
            // Begin a new transaction.
            try {
                dbStatement.executeUpdate( "Begin Transaction" );
                // Set the isolation level.
                dbStatement.executeUpdate( new String( "Set Transaction Isolation level " + isolationLevel ) );
                // For each sql statement, perform the update.
                for( int i=0; i<sqlQuery.length; i++ ) {
                dbStatement.executeUpdate( sqlQuery[i] );
                }
                // Commit the transaction and close.
                dbStatement.executeUpdate( "commit" );
                dbStatement.close();
                wasExecuted = true;
            } catch (Exception e) {
                errorString = new String( "Error executing: " + sqlQuery + "\nCause: " + e.toString() );
                hasError = true;
                // Rollback if an error has occured.
                dbStatement.executeUpdate( "rollback" );
                dbStatement.close();
            }
        }
        return wasExecuted;
    }
}

4. 测试代码

import java.sql.*;

public class DBConnectionTest {
    public static void main(String[] args) {
        /*
         * if(args.length < 3){
         * System.out.println("Syntax:DBConnection [url][username][password]");
         * //url = jdbc:postgresql:database or
         * jdbc:postgresql://host:port/database System.exit(-1); }
         */

        String url = "jdbc:postgresql://localhost:5432/postgres";
        String username = "postgres";
        String password = "741613551";
        String sql = "select sName from student";
        String s;

        try {
            System.out.println("Step 01: Registering JDBC Driver");

            /* There are three ways to registe driver. */
            // write your code here for Registering JDBC Driver
            // 先注册JDBC驱动 org.postgresql.Driver
            Class.forName("org.postgresql.Driver");
            System.out.println("Step 02: Establishing connection to: \n\t"
                    + url);

            // write your code here to get a connection
            // url = jdbc:postgresql:database or
            // jdbc:postgresql://host:port/database
            Connection conn = DriverManager.getConnection(url, username,
                    password);
            System.out.println("Step 03: Creating SQL statement.");

            // write your code here to create a SQL statement
            Statement state = conn.createStatement();
            System.out.println("Step 04: Executing SQL statement.");

            // write your code here to execute your SQL statement and recieve
            // the result
            ResultSet resultSet = state.executeQuery(sql);
            System.out.println("Step 05: Printing result.");

            // write your code here to print the result
            while (resultSet.next()) {
                System.out.println(resultSet.getString("sName"));
            }
            System.out.println("Step 06: Closing JDBC objects.");

            // write your code here to close all JDBC objects.

            resultSet.close();
            state.close();
            conn.close();
            System.out.println("End.");
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}

5. JDBC教程：http://www.yiibai.com/jdbc/jdbc-introduction.html