[PostgreSQL] JDBC Driver 설치 후, 자바와 JDBC연동 (eclipse)

JDBC Driver 설치

공식사이트 : https://www.postgresql.org/

PostgreSQL

 

 

다운로드 클릭

 

Software 클릭

Drivers and interfaces 선택

psqlJDBC의 view클릭

다운로드 클릭

사용하는 자바의 버전에 맞게 다운로드 Download 버튼을 클릭

* 자바버전확인  cmd> java -version

파일다운완료

 

PostgreSQL JDBC 이클립스에 등록 후 자바 연동

자바에서는 JDBC 를 이용하여 Database 와 통신을 할 수 있다.

이클립스에서 PostgreSQL 전용 JDBC jar 파일을 등록하고 PostgreSQL과 연동하는 방법을 정리해두었다.

 

이클립스에 다운로드한 jar파일 등록

프로젝트 우클릭 > Build Path > Configure Build Path 

 

다운로드 받은 jar파일을 등록해줍니다.

 

연동 테스트 코드

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class PostgreSQL {
    public static void main(String[] args) throws ClassNotFoundException {
        Class.forName("org.postgresql.Driver");  
        
        String     connurl  = "jdbc:postgresql://localhost:5432/posgres";
        //					  "jdbc:postgresql://서버주소:포트/데이터베이스명";
        String     user     = "postgres"; //유저
        String     password = "postgres"; //비밀번호
 
        try (Connection connection = DriverManager.getConnection(connurl, user, password);) {
              Statement stmt = connection.createStatement();
              ResultSet rs = stmt.executeQuery("SELECT VERSION() AS version");
 
              while (rs.next()) {
                  String version = rs.getString("version");
                  
                  System.out.println(version);                  
              }           
            rs.close();
            stmt.close();
            connection.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

 

 

참고용 Vert.x JDBC연결 코드 > 더보기

package io.vertx.blog;

import io.vertx.core.AbstractVerticle;
import io.vertx.core.http.HttpServerResponse;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.ext.jdbc.JDBCClient;
import io.vertx.ext.sql.SQLConnection;
import io.vertx.ext.web.Router;
import io.vertx.ext.web.handler.BodyHandler;

public class PSQLVerticle extends AbstractVerticle {

  @Override
  public void start() {

    // This client expects a running PostgreSQL instance with a table named sales
    // e.g.: CREATE TABLE sales (sale JSON)
    final JDBCClient jdbc = JDBCClient.createNonShared(vertx, new JsonObject()
        .put("url", "jdbc:postgresql://localhost:5432/postgres")
        .put("user", "postgres")
        .put("password", "mysecretpassword")
        .put("driver_class", "org.postgresql.Driver"));

    final Router router = Router.router(vertx);

    // enable the body parser so we can handle JSON input
    router.route().handler(BodyHandler.create());

    // this might look a bit strange but all it does is get a DB connection
    // to PostgreSQL and makes sure that the connection is closed when
    // the request is done or there is an error in between.
    router.route("/sales*").handler(ctx -> jdbc.getConnection(res -> {
      if (res.failed()) {
        ctx.fail(res.cause());
      } else {
        SQLConnection conn = res.result();

        // save the connection on the context
        ctx.put("conn", conn);

        // we register a handler to close the connection at
        // the end of the request
        ctx.addHeadersEndHandler(done -> conn.close(close -> {
          if (close.failed()) {
            done.fail(close.cause());
          } else {
            done.complete();
          }
        }));

        ctx.next();
      }
    })).failureHandler(routingContext -> {
      // if for some reason thare is an error in the route also
      // make sure it closes any one DB connections
      SQLConnection conn = routingContext.get("conn");
      if (conn != null) {
        conn.close(v -> {
        });
      }
    });

    // Here we handle a incoming request to get JSON
    // from PostgreSQL
    router.get("/sales/:saleID").handler(ctx -> {
      String saleID = ctx.request().getParam("saleID");
      HttpServerResponse response = ctx.response();
      if (saleID == null) {
        ctx.fail(400);
        return;
      }

      SQLConnection conn = ctx.get("conn");

      // Note that I am querying the JSON document itself for the property ID
      conn.queryWithParams("SELECT sale FROM sales WHERE sale->>'id' = ?",
              new JsonArray().add(saleID), query -> {
        if (query.failed()) {
          ctx.fail(query.cause());
          return;
        }

        if (query.result().getNumRows() == 0) {
          response.setStatusCode(404).end();
        }

        // here we return the document and note that i don't need to convert from JsonObject to String
        // PostgreSQL returns JSON as strings so less conversions, better performance!
        response
            .putHeader("content-type", "application/json")
            .end(query.result().getResults().get(0).getString(0));
      });
    });

    // Here handle the creation of new sales
    router.post("/sales").handler(ctx -> {
      HttpServerResponse response = ctx.response();
      SQLConnection conn = ctx.get("conn");

      // We need to instruct PostgreSQL that the String (again no conversion needed) is a JSON document
      conn.updateWithParams("INSERT INTO sales (sale) VALUES (?::JSON)",
              new JsonArray().add(ctx.getBodyAsString()), query-> {
        if (query.failed()) {
          ctx.fail(query.cause());
          return;
        }

        response.setStatusCode(201).end();
      });
    });

    vertx.createHttpServer().requestHandler(router::accept).listen(8080);
  }
}