PostgreSQL

PostgreSQLへのJDBC接続サンプル

デフォルトスキーマの指定は接続URLの最後に ?currentSchema=sooniとパラメータ追加する事で可能となります。

package testconn;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class PostgreConnectionTest {

	public static void main(String[] args) {
		PreparedStatement ps    = null;
		Connection        pconn = null;
		String selectSQL="select rid,name,kanji from ex02 "
				+ "where rid >=? and name like ?;";
		pconn = getPOSConnection();
		try {
			ps = pconn.prepareStatement(selectSQL);
			ps.setInt(1, 10);
			ps.setString(2,"sooni");

			ResultSet rsv = ps.executeQuery() ;
			while (rsv.next()) {
				System.out.println(String.format("%d,%8s,%8s"
				, rsv.getInt("rid"),rsv.getString("name"),rsv.getString("kanji") ) );
			}
			ps.close();
			pconn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			System.exit(1);
		}
		System.exit(0);
	}

	public static Connection getPOSConnection()  {
		Connection pconn = null;
		final String URL  = "jdbc:postgresql://vm022:5432/myposdb";
		final String USER = "sooni";
		final String PASS = "soopass";
		try {
			pconn = DriverManager.getConnection(URL, USER, PASS);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pconn;
	}
}
接続時にカレントスキーマを指定する場合
	public static Connection getPOSConnection()  {
		Connection pconn = null;
		final String URL  = "jdbc:postgresql://vm022:5432/myposdb?currentSchema=sooni";
		final String USER = "online_user";
		final String PASS = "online_pass";
		try {
				pconn = DriverManager.getConnection(URL, USER, PASS);
		} catch (SQLException e) {
				e.printStackTrace();
		}
		return pconn;
	}

接続確認用プログラム

GitHub - oha-yo/PostgreSQLConnectionTest: PostgreSQLへの接続確認
PostgreSQLへの接続確認. Contribute to oha-yo/PostgreSQLConnectionTest development by creating an account on GitHub.
java1.6用が必要になったので、、
package postgresConnection;

import java.io.FileInputStream;
import java.io.IOException;
import java.net.InetSocketAddress;
import java.net.Socket;
import java.net.UnknownHostException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class PostgreSQLConnectionTest {

    public static void main(String[] args) {
        Connection connection = null;
        try {
            // JDBCドライバを手動でロード
            Class.forName("org.postgresql.Driver");


            Calendar now = Calendar.getInstance();
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            String formattedDateTime = formatter.format(now.getTime());
            System.out.println("---------------------------------------------------");
            System.out.println("Connection test start time : " + formattedDateTime);

            Properties props = loadProperties("database.properties");

            String url = props.getProperty("db.url");
            System.out.println("---------------------------------------------------");
            System.out.println("url   : " + url);

            Pattern pattern = Pattern.compile("jdbc:postgresql://([^:/]+)(?::(\\d+))?/.*");
            Matcher matcher = pattern.matcher(url);
            if (matcher.find()) {
                String phost = matcher.group(1);
                int defaultPort = 5432;
                int pport = matcher.group(2) != null ? Integer.parseInt(matcher.group(2)) : defaultPort;
                if (pport <= 0 || pport > 65535) {
                    throw new IllegalArgumentException("Port number out of range: " + pport);
                }

                System.out.println("Host  : " + phost);
                System.out.println("Port  : " + pport);

                if (!isHostReachable(phost, pport)) {
                    throw new IOException("Host " + phost + " is not reachable.");
                }

            } else {
                throw new IllegalArgumentException("URL parsing failed");
            }

            String user = props.getProperty("db.user");
            String password = props.getProperty("db.password");
            String query = props.getProperty("sql.query");
            connection = DriverManager.getConnection(url, user, password);

            ResultSet resultSet = executeQuery(connection, query);
            System.out.println("---------------------------------------------------");
            printResultSet(resultSet);

            System.out.println("---------------------------------------------------");
            System.out.println("Database connection established successfully.");

        } catch (ClassNotFoundException e) {
            System.err.println("PostgreSQL JDBC Driverが見つかりません。");
        } catch (IOException e) {
            System.err.println("Error: " + e.getMessage());
        } catch (SQLException e) {
            System.err.println("SQL Error: " + e.getMessage());
        } catch (IllegalArgumentException e) {
            System.err.println("Error: " + e.getMessage());
        } finally {
            closeConnection(connection);
        }
    }

    private static Properties loadProperties(String fileName) throws IOException {
        Properties props = new Properties();
        FileInputStream in = null;
        try {
            in = new FileInputStream(fileName);
            props.load(in);
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return props;
    }


    private static boolean isHostReachable(String host, int port) {
        Socket socket = null;
        try {
            socket = new Socket();
            socket.setSoTimeout(2000); // 2秒間のタイムアウトを設定
            socket.connect(new InetSocketAddress(host, port)); // 接続を試みる
            return true; // ホストとポートが両方とも到達可能である場合
        } catch (UnknownHostException e) {
            System.err.println("Error: Host " + host + " is unknown.");
        } catch (IOException e) {
            System.err.println("Error: Host " + host + " is reachable, but port " + port + " is not open.");
        } finally {
            if (socket != null) {
                try {
                    socket.close();
                } catch (IOException e) {
                    // ignore
                }
            }
        }
        return false; // ホストまたはポートのどちらかが到達不能な場合
    }



    private static ResultSet executeQuery(Connection connection, String query) throws SQLException {
        Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        return statement.executeQuery(query);
    }

    private static void printResultSet(ResultSet resultSet) throws SQLException {
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        int[] columnWidths = new int[columnCount];

        while (resultSet.next()) {
            for (int i = 0; i < columnCount; i++) {
                String value = resultSet.getString(i + 1);
                if (value != null) {
                    columnWidths[i] = Math.max(columnWidths[i], value.length());
                } else {
                    columnWidths[i] = Math.max(columnWidths[i], metaData.getColumnLabel(i + 1).length());
                }
            }
        }

        resultSet.beforeFirst();
        while (resultSet.next()) {
            for (int i = 0; i < columnCount; i++) {
                String value = resultSet.getString(i + 1);
                if (value != null) {
                    System.out.print(String.format("%-" + columnWidths[i] + "s", value) + " ");
                } else {
                    System.out.print(String.format("%-" + columnWidths[i] + "s", "") + " ");
                }
            }
            System.out.println();
        }
    }

    private static void closeConnection(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                System.err.println("Error while closing connection: " + e.getMessage());
            }
        }
    }
}
スポンサーリンク