home *** CD-ROM | disk | FTP | other *** search
/ PC Extra 07 & 08 / pca1507.iso / intface / pca / special4 / software / eclipse.project / WEB-INF / classes / nl / pcactive / db / Jdbc.java < prev    next >
Encoding:
Java Source  |  2003-05-20  |  6.8 KB  |  270 lines
  1. package nl.pcactive.db;
  2.  
  3. import java.io.File;
  4. import java.io.FileInputStream;
  5. import java.io.FileNotFoundException;
  6. import java.io.IOException;
  7. import java.sql.Connection;
  8. import java.sql.Driver;
  9. import java.sql.PreparedStatement;
  10. import java.sql.ResultSet;
  11. import java.sql.SQLException;
  12. import java.sql.Statement;
  13. import java.util.Properties;
  14.  
  15. import nl.pcactive.bean.FamilyBean;
  16. import nl.pcactive.bean.IndividualBean;
  17. import nl.pcactive.bean.SessionBean;
  18.  
  19. /**
  20.  * <p>Description: GEDCOM 2 DATABASE</p>
  21.  * <p>Copyright: Copyright (c) 2002</p>
  22.  * <p>Company: Pc-Active</p>
  23.  * @author Benny Lootens
  24.  * @version 2.0
  25.  */
  26. public class Jdbc {
  27.  
  28.     private static Driver driver;
  29.     private static Properties props = new Properties();
  30.     private static String url, drv, user, pwd, gedcom, type;
  31.  
  32.     /** if you use other tables name, you can change them here */
  33.     public interface Tables {
  34.  
  35.         String INDIVIDUAL = "t_individual";
  36.         String FAMILY = "t_family";
  37.         String CHILD = "t_child";
  38.  
  39.         String SESSION = "t_session";
  40.     }
  41.  
  42.     static {
  43.         File f = null;
  44.  
  45.         try {
  46.  
  47.             System.err.println("Connecting to database...");
  48.  
  49.             f = new File("ged.ini");
  50.  
  51.             props.load(new FileInputStream(f));
  52.  
  53.             drv = props.getProperty("db.driver");
  54.             url = props.getProperty("db.url");
  55.             user = props.getProperty("user");
  56.             pwd = props.getProperty("password");
  57.             gedcom = props.getProperty("gedcom");
  58.             type = props.getProperty("db.type");
  59.  
  60.             driver = (Driver) Class.forName(drv).newInstance();
  61.  
  62.             //      factor = Integer.parseInt(properties.getProperty("factor"));
  63.             //      if (factor == 0) factor = 10;
  64.  
  65.             System.err.println("props=" + props.toString());
  66.         } catch (FileNotFoundException ff) {
  67.             try {
  68.                 System.err.println(
  69.                     "problems with ini-file " + f.getCanonicalPath());
  70.             } catch (IOException e) {
  71.             }
  72.         } catch (Throwable t) {
  73.             t.printStackTrace(System.err);
  74.         }
  75.     }
  76.  
  77.     /** @return a database connection */
  78.     public static Connection openConnection() throws SQLException {
  79.         return driver.connect(url, props);
  80.     }
  81.  
  82.     /** cleanup code for database objects */
  83.     public static void close(Object o) {
  84.  
  85.         if (o == null)
  86.             return;
  87.  
  88.         try {
  89.  
  90.             if (o instanceof ResultSet)
  91.                  ((ResultSet) o).close();
  92.  
  93.             else if (o instanceof Statement)
  94.                  ((Statement) o).close();
  95.  
  96.             else if (o instanceof Connection)
  97.                  ((Connection) o).close();
  98.  
  99.         } catch (Exception ignore) {
  100.         } finally {
  101.             o = null;
  102.         }
  103.     }
  104.  
  105.     /** create the database tables necessary to run ged2db */
  106.     public static void createDatabase() {
  107.  
  108.         Statement stmt = null;
  109.         Connection con = null;
  110.  
  111.         try {
  112.             con = Jdbc.openConnection();
  113.             stmt = con.createStatement();
  114.  
  115.             if ("mysql".equals(type)) {
  116.                 stmt.executeUpdate("drop table if exists " + Tables.INDIVIDUAL);
  117.                 stmt.executeUpdate("drop table if exists " + Tables.FAMILY);
  118.                 stmt.executeUpdate("drop table if exists " + Tables.CHILD);
  119.             }
  120.  
  121.             stmt.executeUpdate(
  122.                 " create table "
  123.                     + Tables.INDIVIDUAL
  124.                     + "("
  125.                     + " Individual int not null,"
  126.                     + " FirstName varchar(255) not null,"
  127.                     + " MiddleName varchar(255) not null,"
  128.                     + " LastName varchar(255) not null,"
  129.                     + " Sex varchar(6) null,"
  130.                     + " BirthDate varchar(255) null,"
  131.                     + " BirthPlace varchar(255) null,"
  132.                     + " BaptismDate varchar(255) null,"
  133.                     + " BaptismPlace varchar(255) null,"
  134.                     + " DeathDate varchar(255) null,"
  135.                     + " DeathPlace varchar(255) null,"
  136.                     + " BurriedDate varchar(255) null,"
  137.                     + " BurriedPlace varchar(255) null,"
  138.                     + " Notes "
  139.                     + ("mysql".equals(type) ? "blob" : "varchar(1000)")
  140.                     + " null)");
  141.             stmt.executeUpdate(
  142.                 "create table "
  143.                     + Tables.FAMILY
  144.                     + "("
  145.                     + " Family int  not null,"
  146.                     + " Husband int  not null,"
  147.                     + " Wife int  not null,"
  148.                     + " MarriedDate varchar(255) null,"
  149.                     + " MarriedPlace varchar(255) null)");
  150.             stmt.executeUpdate(
  151.                 "create table "
  152.                     + Tables.CHILD
  153.                     + "("
  154.                     + " Family int  not null,"
  155.                     + " Child int  not null)");
  156.         } catch (Exception e) {
  157.             e.printStackTrace(System.err);
  158.         } finally {
  159.             Jdbc.close(stmt);
  160.             Jdbc.close(con);
  161.         }
  162.     }
  163.  
  164.     /** add an individual to the database */
  165.     public static void addIndividual(IndividualBean individual, Statement stmt)
  166.         throws SQLException {
  167.         stmt.executeUpdate(
  168.             "insert into "
  169.                 + Tables.INDIVIDUAL
  170.                 + " values("
  171.                 + individual.getId()
  172.                 + ", '"
  173.                 + individual.getFirst()
  174.                 + "', '"
  175.                 + individual.getMiddle()
  176.                 + "', '"
  177.                 + individual.getLast()
  178.                 + "', '"
  179.                 + individual.getSex()
  180.                 + "', '"
  181.                 + individual.getBirth().getDate()
  182.                 + "', '"
  183.                 + individual.getBirth().getPlace()
  184.                 + "', '"
  185.                 + individual.getBaptism().getDate()
  186.                 + "', '"
  187.                 + individual.getBaptism().getPlace()
  188.                 + "', '"
  189.                 + individual.getDeath().getDate()
  190.                 + "', '"
  191.                 + individual.getDeath().getPlace()
  192.                 + "', '"
  193.                 + individual.getBurried().getDate()
  194.                 + "', '"
  195.                 + individual.getBurried().getPlace()
  196.                 + "', '"
  197.                 + individual.getNotes()
  198.                 + "')");
  199.     }
  200.  
  201.     /** functie voor het toevoegen van een familie in t_family */
  202.     public static void addFamily(FamilyBean family1, Statement stmt)
  203.         throws SQLException {
  204.         stmt.executeUpdate(
  205.             "insert into "
  206.                 + Tables.FAMILY
  207.                 + " values("
  208.                 + family1.getId()
  209.                 + ", "
  210.                 + family1.getHusband()
  211.                 + ", "
  212.                 + family1.getWife()
  213.                 + ", '"
  214.                 + family1.getMarried().getDate()
  215.                 + "', '"
  216.                 + family1.getMarried().getPlace()
  217.                 + "')");
  218.     }
  219.  
  220.     /** add a child to the database */
  221.     public static void addChild(String s, String s1, Statement stmt)
  222.         throws SQLException {
  223.         stmt.executeUpdate(
  224.             "insert into " + Tables.CHILD + " values(" + s + ", " + s1 + ")");
  225.     }
  226.  
  227.     /** @return the name of the input .gedcom file */
  228.     public static String getGedcom() {
  229.         return gedcom;
  230.     }
  231.     public String getType() {
  232.         return type;
  233.     }
  234.  
  235.     /** save the session in the database */
  236.     public static void store(SessionBean bean) {
  237.         Connection con = null;
  238.         PreparedStatement pstmt = null;
  239.  
  240.         try {
  241.             con = Jdbc.openConnection();
  242.             pstmt =
  243.                 con.prepareStatement(
  244.                 "insert into " + Tables.SESSION
  245.                     + "(sessionid,remotehost,remoteip,timestamp,"
  246.                     + "referer,useragent,xforwardedfor)"
  247.                   + " values(?,?,?,?,?,?,?)");
  248.             pstmt.setString(1, bean.getSessionId());
  249.             pstmt.setString(2, bean.getRemoteHost());
  250.             pstmt.setString(3, bean.getRemoteIp());
  251.             pstmt.setString(4, bean.getTimeStamp());
  252.             pstmt.setString(5, bean.getReferer());
  253.             pstmt.setString(6, bean.getUser_agent());
  254.             pstmt.setString(7, bean.getX_forwarded_for());
  255.             pstmt.executeUpdate();
  256.  
  257.             System.out.println(
  258.                 "session stored: "
  259.                     + bean.getTimeStamp()
  260.                     + "/"
  261.                     + bean.getSessionId());
  262.         } catch (Exception e) {
  263.             e.printStackTrace(System.err);
  264.         } finally {
  265.             Jdbc.close(pstmt);
  266.             Jdbc.close(con);
  267.         }
  268.     }
  269.  
  270. }