デフォルトスキーマの指定は接続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.
github.com
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());
}
}
}
}
