  postgreSQL JDBC Ȱϱ

  ȿ nogadax@kldp.org
  2000 6 15

    "Apache-JServ  postgreSQL ϱ"   
  ,  ̴.   Ŭ̾Ʈ JDBC ϴ ׵
  ߰Ͽ.    Ʈ׷ JDBC Ȱϴ  ũ
  ΰ  Ѵ. ù  ° postgreSQL DB  ϰ
  (  Window 98 ) Ŭ̾Ʈ Ҷ JDBC ׿ 
  Ѵ.  ° Jserv ߰ Apache DB  μ postgreSQL 
  ϱ  JDBC  ׵  Ѵ.
  ______________________________________________________________________

  

  1. غ 
  2. Ʈ׷ JDBC ̹ 
     2.1 JDBC ٿޱ
     2.2 JDBC 
        2.2.1 postgreSQL 6.5.x ̹ 
        2.2.2 postgreSQL 7.0.x ̹ 
     2.3 JDBC ̹ ׽Ʈϱ

  3.  Ŭ̾Ʈ () 
  4. Apache-Jserv ϱ
  5. Ʈ׷ JDBC 
     5.1 JDBC  غ
     5.2 ̹ ε
        5.2.1 postgresql 6.5.X  Class.forName()  
        5.2.2 postgresql 7.0.X  Class.forName()  
     5.3 Connection to the Database
        5.3.1 Ÿ 
     5.4 Jserv ׽Ʈ ҽ

  6. Ʈ׷  

  ______________________________________________________________________

  1.  غ 

      ׽Ʈ ռ  ⺻  غǾ     ִ.
  ̿    http://www.kldp.org <http://www.kldp.org> 
  ϰų Ÿ  ϱ ٶ.   ̸ غǾ  
  ̴.

  o  ġ

  o  JServ

  o  JDK1.1.x ̻

  o  JSDK2.0 ̻

  o  postgreSQL

  o  Window 98 (Ŭ̾Ʈ)

  ̹ ׽Ʈ ģ  ȯ  .

  o  ׼  6.1

  o  ġ 1.3.9

  o  JServ 1.0

  o  JDK 1.2.2

  o  JSDK 2.0

  o  postgresql 6.5.3  postgresql 7.0.x

  o  Window 98 (Ŭ̾Ʈ)

  Ÿ   Ʈ  .

  o  http://apache.kr.net <http://www.apache.kr.net>

  o  http://www.apache.org <http://www.apache.org>

  o  http://www.postgresql.org <http://www.postgresql.org>

  o  http://www.blackdown.org <http://www.blackdown.org>

  o  http://java.apache.org <http://java.apache.org>

  o  http://java.sun.com <http://java.sun.com>

  o  http://database.sarang.net <http://database.sarang.net>

  Ÿ ణ Ʈ׷   Apache-JServ, Ʈ׷ 
    ҽ   Ȩ
  (http://user.chollian.net/~nogadax
  <http://user.chollian.net/~nogadax>)  ϱ ٶ.

  2.  Ʈ׷ JDBC ̹ 

  Ʈ׷ Type 4 JDBC ̹ Ѵ.  Type 4 
  ڹٷ ۼǾ ͺ̽ ü  ݷ Ѵ. ̴
  Ŭ̾Ʈ JDBC  DBMS    
  ǹѴ. Ʈũ󿡼 JDBC ̹   ϰ Ʈ׷
  DB     ̸ Ϸ ڴ DB   ɿ 
  ϸ ۾   ִ.

   Ʈ׷ JDBC ̹ ÷  ̹Ƿ ѹ
  ϵ ̹  ÷  ϴ.  ,
   Ǿ  Ʈ׷ JDBC  ̹ 쳪 
    ٷ  ϴ.

  2.1.  JDBC ٿޱ

  Ʈ׷  ġ ʾ Ʈ׷ ҽ  
  Ʈ׷ Ʈ (http://www.postgresql.org
  <http://www.postgresql.org>)  RPM  JDBC ̹ ٿ
   ִ. ׿ܿ http://www.retep.org.uk <http://www.retep.org.uk> 
  ٿ   ִ.

  ٿ    ִµ  "JDK1.1.x"   ִٸ "JDBC
  1" ̹ ٿ޾ƾ ϰ "JDK1.2" ̻  ִٸ "JDBC 2"
  ̹ ٿ ȴ.

  2.2.  JDBC 

  JDBC ̹ ٸ ̸  Ѵ. , Ʈ׷ ҽ
  ٿ޾  ġ  ̸ Ʈ׷ ҽ 丮
  interfaces 丮 JDBC ̹ ҽ ־  ϸ ȴ.

  JDBC ̹ Ͻ "JDK1.1.x" Ͽ JDBC ̹ 
  "JDBC 1"  ̴. JDK1.2   JDBC ̹ "JDBC
  2" ̴̹.

    Ʈ׷ ҽ 丮
  "/usr/local/src/pgsql_" ̶ ϰ Ʈ׷ ϵǾ
  ġ 丮 "/usr/local/pgsql" ̶ Ѵ. Ÿ, 
  Ʈ׷ "6.5.x"    Ʈ׷ "7.0.x"
   JDBC  ̹    ٸǷ Ͽ Ѵ.
   JDBC ̹ .

  2.2.1.  postgreSQL 6.5.x ̹ 

  postgreSQL 6.5.x    .

  /usr/local/src/pgsql6.5.3/src/interfaces/jdbc]$ make

    丮  "make" ָ ȴ. "make" Ϸ  Ŀ
    ̹  Ȯ . ̹ ̸ "postgresql.jar"
  ̴.  "/usr/local/pgsql"  jdbc  丮 
  "postgresql.jar"  . JDBC ̹  丮 ڽ
  ϴ  ٲپ .

  /usr/local/pgsql]$ mkdir  jdbc

  /usr/local/pgsql]$ cp
  ../src/pgsql6.5.3/src/interfaces/postgresql.jar  ./jdbc/

  2.2.2.  postgreSQL 7.0.x ̹ 

  "postgreSQL 7.0.x"   "6.5.x" ʹ   ٸ. , "6.5.x"
  ó "make"  Է ϸ ޽ µ     "JDK1.1.x" ̸
  "make  jdbc1   jar" Էϰ "JDK1.2.x" ̸ "make  jdbc2  jar"
  Է϶  ޽ ´. ⼭ "JDK1.2.x"  ϰ 
  غ.

  /usr/local/src/pgsql7.0.2/src/interfaces/jdbc]$ make    jdbc2    jar

     ̹ Ȯ  . ̹ ̸
  "postgresql.jar" ̴.   JDBC ̹ "/usr/local/pgsql" 
  jdbc  丮  JDBC ̹    ȴ.

  /usr/local/pgsql]$ mkdir  jdbc

  /usr/local/pgsql]$ cp
  ../src/pgsql7.0.2/src/interfaces/postgresql.jar  ./jdbc/

  2.3.  JDBC ̹ ׽Ʈϱ

   ϵǾ   JDBC ̹ ׽ƮϷ  ϴ 
  "JDK"   CLASSPATH  Ͽ Ѵ. CLASSPATH  
   .

  export   CLASSPATH="$CLASSPATH:/usr/local/pgsql/jdbc/postgresql.jar:."

   export  Ϲ 󿡼  Ͽ ǳ ̴ ȸ̹Ƿ
  Ź    export  ־ Ѵ. ȸ ƴ
   ϱ     α   
  "/etc/profile" ̳ "/root/.bash_profile"   CLASSPATH 
   ߰ϸ ȴ.

            JDBC ̹ Ȯ  
  ׽Ʈ  غ.  JDBC ̹ ׽Ʈ 
  "/usr/local/src/pgsql653/src/interfaces/jdbc/example"  丮 
  ҽ غ.  ׽Ʈ  example 丮
  basic.java ϴ ̴.

  /usr/local/src/pgsql653/src/interfaces/jdbc/example]$ javac
  basic.java

  3.   Ŭ̾Ʈ () 

   Ʈ׷ ġ  Ʈ׷ DB ϰ
  Ŭ̾Ʈ   JDBC   Ʈ׷ ̿Ϸ Ѵٰ
  . Ÿ   IP ּҴ 210.110.144.169 ̶ Ѵ.

  JDBC ̹    .

  1.

     Ʈ׷ JDBC ̹   丮 Ѵ.

  2.

      AUTOEXEC.BAT Ͽ JDBC ̹ θ CLASSPATH
     Ѵ.

     Ʒ AUTOEXEC.BAT Ͽ ̴.

     set  classpath=.;c:\postgres\jdbc_drv\postgresql.jar

  3.

     츦 Ѵ.

  4.

     쿡 DB   ϱ    ġ
     Ʈ׷ "pg_hba.conf"  Ѵ.

         Ʈ׷    ΰ    "/usr/local/pgsql"    ̸
     pg_hba.conf     "/usr/local/pgsql/data" 丮 ִ.

     Ʒ  μ ڽſ ȯ濡 °   pg_hba.conf
       ο  ϸ ȴ.

     host   all   210.110.144.169   255.255.255.255   trust

       .

  host  μ Internet Domain  ǹѴ. all   
  ͺ̽ ǹ   Ư DB ϰ Ѵٸ DB ̸
   ȴ.   Ϸ  ͺ̽ "nogadax"   ̸
      "all"    "nogadax"    ȴ.
  210.110.144.169 Ʈ׷ Ϸ Ŭ̾Ʈ  IPּҷμ
  ⼭  IP ̴ּ. 255.255.255.255  Net Mask ̴. trust
    Ŭ̾Ʈ ٿ   ޼ҵμ trust ٸ 
   access  ϴ.  crypt ̸  ID    尡
  ʿϴ. ̿  ڼ   "pg_hba.conf"  ٶ
  ̿   http://www.kldp.org <http://www.kldp.org>  
  ϱ ٶ.

  ׽Ʈ

  ׽Ʈ  Ʈ׷ JDBC  α׷ ҽ 쿡 
     Ѵ.     .

  javac  Basic.java

  4.  Apache-Jserv ϱ

   κ ϱ  JDK   CLASSPATH  Ǿ  ־
  Ѵ. ̸  տ   "JDBC ̹ ׽Ʈϱ"
  ϱٶ.

  Apache-JServ  ̹ ġǾ ִٸ JServ ȯ
  "jserv.properties"     .  ȭϳ postgresql.jar
  θ Խָ  ȴ.  Apache-JServ ȯ  
   .

        /usr/local/apache/conf]$ ls
           access.conf
           httpd.conf
           srm.conf
           jserv.conf
           jserv.properties
           servlets.properties

  Ʈ׷ JDBC ̹ "jserv.proeprties"   Ǹ
  Apache-JServ   ȯ  ̴. "jserv.properties" 
  ó ָ߰ ȴ.

  wrapper.classpath=/usr/local/pgsql/jdbc/postgresql.jar

  ó ߰ Ͽٸ Apache-JServ  ȯ漳 κ .

   " Ŭ̾Ʈ () " κп ޵Ǿ
  "pg_hba.conf"    ȴ. ⼭ Apache  ġ  IP
  ּҸ  210.110.144.168  . , Ʈ׷ ΰ
  "/usr/local/pgsql" ̸  pg_hba.conf  "/usr/local/pgsql/data"
  丮 ִ.

  Ʒ  μ ڽſ ȯ濡 °   pg_hba.conf 
   ο  ϸ ȴ.

       host  all  210.110.144.168  255.255.255.255  trust

       .

  host  μ Internet Domain  ǹѴ.  Internet Domain
   μ   Ʈũ   ̷ ϴ
  īμ    DB  Ϸ Host  
  Ѵ. all   ͺ̽ ǹϸ  Ư  DB
  ϰ Ѵٸ DB ̸   ȴ.   Ϸ
  ͺ̽  "nogadax" ̸    "all"  "nogadax"
   ȴ. "210.110.144.168"  Ʈ׷ Ϸ
  Ŭ̾Ʈ IP̴ּ. ⼭ Apache-Jserv ġ  IP
  ̴ּ. 255.255.255.255  Net Mask ̴. trust   Ŭ̾Ʈ
  ٿ   ޼ҵμ trust ٸ    access 
  ϴ.  crypt  ̸  ID  н尡 ʿϸ  ڼ
   pg_hba.conf     ϱ ٶ.

  5.  Ʈ׷ JDBC 

  5.1.  JDBC  غ

  JDBC ̹ Ʈ׷  α׷ ׽Ʈϱ 
  Ʈ׷   Ȱȭ Ǿ ־ ϸ   ɼ(i)
   ⵿Ǿ Ѵ.

  postmaster   -i &

  ̰ Ʈũ  Ϸ JDBC  ʼ ̴. 
  pg_hba.conf  Ȯϵ Ѵ. , Ϲ IPּҰ ƴ
  localhost(127.0.0.1) ϷѴٸ   (127.0.0.1) Ͽ
  pg_hba.conf  Ͽ Ѵ.  localhost  ̴.

       host  all  127.0.0.1  255.255.255.255  trust

  5.2.  ̹ ε

  JDBC ̹ ε  Class.forName()  ϸ  ȴ. 
   ִµ  Ʈ׷ 6.5.x  7.0.x  뿡 ణ ̰
  . ڰ  ׽Ʈ     Ҵ α׷
  7.0.x  JDBC ̹ ׽Ʈ غ    ߻Ͽ.
   ذϱ  7.0.x   ãƺ  ƹ  
   example 丮  ҽ   ߰Ͽ.

  Ʈ׷ 6.5.x  JDBC ̹
  Class.forName("postgresql.jar");  ϸ  7.0.x  JDBC
  ̹ Class.forName("org.postgresql.jar"); Ͽ  Ѵ. ̷
  ̸ ϱ ٶ.  Ʈ׷  뺰
  Class.forName()   . Ÿ  ó 
  ClassNotFoundException Ѵ.

  5.2.1.  postgresql 6.5.X  Class.forName()  

       public void  test()
       throws ClassNotFoundException, FileNotFoundException,
              IOException, SQLException
       {
          String url = "jdbc:postgresql:nogadax";
          String usr = "postgres";
          String pwd = "";

          Class.forName("postgresql.Driver");

          Connection db = DriverManager.getConnection(url, usr, pwd);
          ........
          ........
       }

  5.2.2.  postgresql 7.0.X  Class.forName()  

       public void  test()
       throws ClassNotFoundException, FileNotFoundException,
              IOException, SQLException
       {
          String url = "jdbc:postgresql:nogadax";
          String usr = "postgres";
          String pwd = "";

          Class.forName("org.postgresql.Driver");

          Connection db = DriverManager.getConnection(url, usr, pwd);
          ........
          ........
       }

  5.3.  Connection to the Database

  Ʈ׷ ͺ̽     .

        jdbc:postgresql:database_name
        jdbc:postgresql://host/database_name
        jdbc:postgresql://host:port/database_name

     host
        Ʈ׷ DB  ȣƮ ̴. IP ־ ȴ.  
         κ  Ǹ Ʈ localhost(127.0.0.1) ̴.

     port
        Ʈ׷ Ʈ Ʈ 5432 ̸  ϴ.

     database_name
        Ÿ̽

  5.3.1.  Ÿ 

  Ʈϱ  JDBC κ Connection νϽ  Ѵ. ̸
   DriverManager.getConnection() ޼ҵ带 ؾ Ѵ.

  Connection  db = DriverManager.getConnection(url,user,pwd);

  Ÿ Ʈ׷ ϳ ڵ ũⰡ 8K Ʈ ѵǾ ִ.
   ϳ  ڵ尡 8K Ʈ Ѿ Ѵٸ "Large Object" 
   Ͽ Ѵ. ̿   ؼ Ʈ׷ α׷
  ̵ JDBC κ  ϱ ٶ. , JDBC   
  ڼ κ Ϲ JDBC  ϱ ٶ.

  5.4.  Jserv ׽Ʈ ҽ

   Apache-Jserv  postgreSQL Ǿ  ׽Ʈ 
  ҽ̴.  ҽ Ʈ׷  ҽμ  servlet 
  ٲپ web 󿡼  ǰԲ  ̴.

    ϰ ϱ⿡  ռ Ʈ׷ Ÿ̽
   Ѵ.  ⼭ Ÿ̽ ̸ nogadax ̴. ̸ 
  ٲٱ ٶ.  Ʈ׷   postgres ̰ н
  . ̰ ҽ url, usr, pwd    . ̸
  Ͽ ڽ ȯ濡 ° ٲٱ ٶ.

        String url = "jdbc:postgresql:nogadax";
        String usr = "postgres";
        String pwd = "";

   ׽Ʈ ȯ Ѵ  PC  Ʈ׷ ġ  
  ġǾ ִ.  ׽ƮϷ ϴ ȯ  DB WEB  
  ٸ   ġǾ ִٸ,  , 3-Tier ȯ̸ URL  ʹ ٸ
  Ǿ Ѵ.

  ڽ ȯ ϱ    ϱ ٶ.  IP
  ּ 210.110.144.162  Ʈ׷ DB  IP̸ּ Ʈ ȣ
  ǾǷ Ʈ ȣ "5432" ڵ õ ̴.

    String  url= "jdbc:postgresql://210.110.144.162/nogadax";

  Ÿ Class.forName()     ϱ ٶ.  Ʒ
  ׽Ʈ ҽ Ʈ׷ 6.5.3  JDBC ̹ 
  ۼǾǷ Class.forName()  Ʒ  .

  Class.forName("postgresql.Driver");

  ׷Ƿ 7.0.x  JDBC ̹ ׽Ʈ Ϸ
  "org.postgresql.Driver"    ȴ.

   ϵ  丮 ־ Ѵ. ͸ 
    丮̴. ̰ zone  Ǿµ zone URL
  ÷Ǿ.  zone  ̸ servlet ̰  Ʒ ҽ
       ͸  ִٸ    URL
  â ó Էָ Ǵµ ڽſ ° ٲپָ ȴ.

  http://localhost/servlet/Hello

  Ȥ

  http://210.110.144.162/servlet/Hello

  Ȥ

  http://nogadax.tit.ac.kr/servlet/Hello

   "Hello.java"  ׽Ʈ ҽ̴.

  -------------------------------
  import java.io.*;
  import javax.servlet.*;
  import java.sql.*;
  import java.text.*;
  import javax.servlet.http.*;

  public class Hello extends HttpServlet
  {
    Connection db;
    Statement  st;
    PrintWriter out;

    public void doGet (HttpServletRequest request,
                       HttpServletResponse response)
    throws ServletException, IOException
    {
     String title = "Example Apache JServ Servlet";
     response.setContentType("text/html");
     out = response.getWriter();
     out.println("<HTML><HEAD><TITLE>");
     out.println(title);
     out.println("</TITLE></HEAD><BODY bgcolor=\"#FFFFFF\">");
     out.println("<H1>" + title + "</H1>");
     out.println("<H2>Congratulations, Apache JServ is working!</H2>");
     try {
          test();
         } catch(Exception ex){
            out.println("<h2>DB error!</h2>");
           }
     out.println("</BODY></HTML>");
     out.close();
     }

     public void cleanup()
     {
       try {
           st.executeUpdate("drop table basic");
         } catch(Exception ex) {  }
     }

     public void doexample() throws SQLException
     {
       out.println("<br><h2>Running tests:</h2>");
       st.executeUpdate("create table basic (a int2, b int2)");
       st.executeUpdate("insert into basic values (1,1)");
       st.executeUpdate("insert into basic values (2,1)");
       st.executeUpdate("insert into basic values (3,1)");
       st.executeUpdate("update basic set b=8");
       out.println("<h2>Updated "+st.getUpdateCount()+" rows</h2>");
       PreparedStatement ps = db.prepareStatement("insert into
                                                 basic values (?,?)");
       for(int i=2;i<5;i++) {
              ps.setInt(1,4);             // "column a" = 5
              ps.setInt(2,i);             // "column b" = i
              ps.executeUpdate();
        }
       ps.close();
       out.println("<h2>performing a query</h2>");
       ResultSet rs = st.executeQuery("select a, b from basic");
       if(rs!=null) {
           while(rs.next()) {
                 int a = rs.getInt("a");
                 int b = rs.getInt(2);
                 out.println("<h2>  a="+a+" b="+b+"</h2>");
            }
           rs.close();
       }
     }

     public void  test()
     throws ClassNotFoundException, FileNotFoundException,
                                   IOException,SQLException
     {
      String url = "jdbc:postgresql:nogadax";
      String usr = "postgres";
      String pwd = "";
      Class.forName("postgresql.Driver");
      db = DriverManager.getConnection(url, usr, pwd);
      out.println("<h2>Connecting to Database URL = " + url +"</h2>");
      out.println("<h2>Connected...Now creating a statement</h2>");
      st = db.createStatement();
      cleanup();
      doexample();
      cleanup();
      out.println("<h2>Now closing the connection</h2>");
      st.close();
      db.close();
     }//end test
   }

   

     . Ʒ  URL ٸ  ִ.

       Example Apache JServ Servlet
       Congratulations, Apache JServ is working!
       Connecting to Database URL = jdbc:postgresql:nogadax
       Connected...Now creating a statement

       Running tests:

       Updated 3 rows
       performing a query
       a=1 b=8
       a=2 b=8
       a=3 b=8
       a=4 b=2
       a=4 b=3
       a=4 b=4

       Now closing the connection

  6.  Ʈ׷  

  JDBC     ο  н带 ְ   
  ̴. ̸  Ʈ׷    н带 
    Ұ ϰڴ.

   Ʈ׷  (DBA) "postgres"  .

  ⼭ ͺ̽ ο  ϰ  
  ͺ̽  ̴.    .

  1. postgres  н带 Ѵ. ̸  Ʈ׷
  Ư ͺ̽ "template1"  Ѵ.  н带 "123qwe"
   Ѵ.

       postgres]$ psql template1
       .......
       template1=>alter user postgres with password '123qwe';
       ALTER USER
       template1=>\q
       postgres]$

  2. "pg_hba.conf"   Ѵ. ,  DB  Ϸ  ڽ
  Host   IP  "210.110.144.169" ̸ ó Ѵ.

        local  all                                     crypt
        host   all   127.0.0.1        255.255.255.255  crypt
        host   all   210.110.144.169  255.255.255.255  crypt

   host  ϴ  DB  Ϸ Ŭ̾Ʈ
  Ϸ Ѵ. ׷ Ƿ DB  IP  ִ  ƴ ϱ
  ٶ.  "crypt" DB     ޼ҵ̹Ƿ "crypt"
   ĺʹ  DB    н 尡 ʿϴ.

  3. ٽ Ʈ׷ ۰ "template1" ͺ̽  
  ο ͺ ̽  Ѵ.    "nogadax"
   ϸ   ͺ ̽   ִ  
  ̸  ɼ "createdb" ̴. Ÿ  "createuser"   ɼǵ
  ִµ  ɼ  ٸ    ִ   ش. ,
  "template1"    ɼ "-u"  ־ ϸ ɼ  ĺʹ
   н带 ´.   ɼ ִ  pg_hba.conf 
  "crypt"   ޼ҵ带 ־ ̴.

       postgres]$ psql -u template1
       Username : postgres
       Password :
       .........
       template1=> create user nogadax with password 'qwert' createdb ;
       CREATE  USER
       template1=>\q
       postgres]$

  4.  nogadax  template1 ͺ̽  
  "nogadax" ͺ̽  . ͺ̽ "test" 
  Ѵ.

       postgres]$ psql -u template1
       Username : nogadax
       Password :
       ..............
       template1=>create database test;
       CREATE DATABASE
       template1=>\q
       postgres]$ psql -u test
       Username : nogadax
       Password :
       ................
       test=> \q
       postgres]$

