JDBC连接postgreSQL数据库的步骤

JDBC连接postgreSQL数据库的步骤

1.下载PostgreSQL JDBC驱动，通过以下链接下载
PostgreSQL JDBC driver : http://jdbc.postgresql.org/download.html

2.在工程中导入刚才下载的驱动类.jar，注意测试是否导入成功，成功导入后运行简单的JDBC会有通信信息

3.DBWapper类主要用于关联数据库，具体实现操作见代码

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 Chenyu Gao * @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:Selina_",                "org.postgresql.Driver", "postgres", "2015303199");    }    /**     * 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;        Class.forName(jdbcClassName);        dbCon=DriverManager.getConnection(dbUrl,username,password);        opened=true;        // Try to open a connection the database.        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 sqlQuery containing a SQL statement     * @return ResultSet     */    public ResultSet runQuery( String sqlQuery )            throws Exception {        Statement statement=dbCon.createStatement();        ResultSet rs=statement.executeQuery(sqlQuery);        return rs;    }    /**     * boolean runUpdate()     * Executes an update and returns true of successfully executed.     *     * @param sqlQuery containing a SQL statement     * @return boolean     */    public boolean runUpdate( String sqlQuery )            throws Exception {        Statement ps=dbCon.createStatement();        ps.executeUpdate(sqlQuery);        return true;    }    /**     * ResultSet runChainedQuery()     * Executes a chained mode transaction query.     *     * @param sqlQuery containing a SQL statement     * @param isolationLevel 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 sqlQuery  containing a series of SQL statments     * @param isolationLevel 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.接下来写测试类测试类的具体操作一共有六步，具体见代码注释，第4步的函数中可以直接输入String类型的字符串SQL语句，之后在第五步中输出查询结果。
注意：如果出现密码认证失败的问题的话，是因md5加密的结果，需要在data\pg_hba.conf文件中修改找到“# IPv4 local connections:”（不包括引号，下同）
在它上面添加“local pgsql all trust”，
在它下面的“host alsError = true;

import java.sql.*;import java.sql.*;public class DBConnectionTest{    public static void main(String[] args){        String url      = "jdbc:postgresql:Selina_";        String username = "postgres";        String password = "2015303199";        String sql = "select sName from student";        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            //从DriverManager中获得一个连接getConnection(url,username,password);            //注意：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 rs=state.executeQuery(sql);            System.out.println("Step 05: Printing result.");            //write your code here to print the result            while(rs.next()){                System.out.println(rs.getString("sName"));            }            System.out.println("Step 06: Closing JDBC objects.");            //write your code here to close all JDBC objects.            rs.close();            state.close();            conn.close();            System.out.println("End.");        }catch(Exception e){            System.out.println(e);        }    }}