import java.sql.*;
import java.io.*;

class BlobExample
{
    final static String jdbcClass = "com.mimer.jdbc.Driver";
    final static String defaultURL = "jdbc:mimer://SYSADM:SYSADM@gere/pesc91";

    static Connection con = null;
    static Statement stm = null;
    static PreparedStatement psins = null;
    static PreparedStatement psupd = null;
    static PreparedStatement pssel = null;
    static ResultSet rs = null;

    static void p(String s) { System.out.println(s); }

    static void usage()
    {
        p("Usage: BlobExample [-url url] {action...}");
        p("{action}: -init             Create BLOBTABLE");
        p("          -import path...   Copy file at path into a BLOB");
        p("          -export name [path]");
        p("                            Read BLOBs from database that match name");
        p("                            name can include LIKE wildcards (%_)");
        p("                            Create file(s) with BLOB contents");
        p("                            in current directory (or in specified path)");
        p("          -list             List what's stored in the database");
    }



    private static String nchar(char c, int n)
    {
        if (n <= 0) return "";
        StringBuffer sb = new StringBuffer(n);
        while (n-- > 0) sb.append(c);
        return new String(sb);
    }



    static void execUpd(String sql) throws SQLException
    {
        if (stm == null) stm = con.createStatement();
        stm.executeUpdate(sql);
    }


    static ResultSet execQuery(String sql) throws SQLException
    {
        if (stm == null) stm = con.createStatement();
        return stm.executeQuery(sql);
    }


    static void crepsins() throws SQLException
    {
        if (psins != null) return;
        psins = con.prepareStatement("insert into BLOBTABLE(name,type,cre_date,data)"+
                                     " values (?,'',CURRENT_TIMESTAMP,?)");
    }


    static void crepsupd() throws SQLException
    {
        if (psupd != null) return;
        psupd = con.prepareStatement("update BLOBTABLE set cre_date=CURRENT_TIMESTAMP,"+
                                     "       data = ? where name = ?");
    }


    static void crepssel() throws SQLException
    {
        if (pssel != null) return;
        pssel = con.prepareStatement("select name,octet_length(data),data"+
                                     "   from BLOBTABLE where name like ?");
    }



    static void importFile(String path) throws SQLException
    {
        FileInputStream fs = null;
        BufferedInputStream bs = null;

        try {
            File file = new File(path);
            String filename = file.getName();
            long filesize = file.length();
            p("Copy to database: "+filename+" "+filesize+" bytes");
            fs = new FileInputStream(file);
            bs = new BufferedInputStream(fs, 16384);
            crepsins();
            psins.setString(1, filename);
            psins.setBinaryStream(2, bs, (int)filesize);
            try {
                psins.executeUpdate();
            } catch (SQLException sqlex) {
                // INSERT failed, try UPDATE instead
                crepsupd();
                psupd.setBinaryStream(1, bs, (int)filesize);
                psupd.setString(2, filename);
                psupd.executeUpdate();
            }
        } catch (FileNotFoundException ex) {
            p(ex.toString());
        } finally {
            if (bs != null) try {bs.close();} catch (Exception ex) {}
            if (fs != null) try {fs.close();} catch (Exception ex) {}
        }
    }



    static void exportFiles(String name, File path) throws SQLException
    {
        crepssel();
        pssel.setString(1, name);
        rs = pssel.executeQuery();
        while (rs.next()) {
            File f = new File(path, rs.getString(1));
            FileOutputStream fs = null;
            InputStream bs = null;
            try {
                if (f.exists()) {
                    p("Skipping, file exists: "+f.getCanonicalPath());
                } else {
                    p("Write "+rs.getString(2)+" bytes to "+f.getCanonicalPath());
                    fs = new FileOutputStream(f);
                    bs = rs.getBinaryStream(3);
                    byte[] buf = new byte[16384];
                    int bytes;
                    while ((bytes = bs.read(buf)) != -1) {
                        fs.write(buf, 0, bytes);
                    }
                }
            } catch (IOException ex) {
                p(ex.toString());
            } finally {
                if (fs != null) try {fs.close();} catch (Exception ex) {}
                if (bs != null) try {bs.close();} catch (Exception ex) {}
            }
        }
    }



    static boolean parseArg(String args[])
    {
        try {
            int argpos = 0;
            String url = defaultURL;
            if (args.length == 0) return false;
            if (args[argpos].equals("-url")) {
                if (args.length < 2) return false;
                url = args[++argpos];
                ++argpos;
            }

            Class.forName(jdbcClass);
            con = DriverManager.getConnection(url);

            while (argpos < args.length) {
                if (args[argpos].equals("-init")) {
                    ++argpos;
                    p("Create example table");
                    execUpd("create table BLOBTABLE("+
                            "       NAME CHARACTER VARYING(64),"+
                            "       TYPE CHARACTER(10),"+
                            "       CRE_DATE TIMESTAMP(0),"+
                            "       DATA BINARY LARGE OBJECT(1G),"+
                            "       primary key(NAME))");
                } else if (args[argpos].equals("-list")) {
                    ++argpos;
                    p("List example databank contents");
                    rs = execQuery("select name,octet_length(data)" +
                                   " from BLOBTABLE");
                    p("Size       Name");
                    p("========== "+nchar('=', 64));
                    while (rs.next()) {
                        String name = rs.getString(1);
                        String siz = rs.getString(2);
                        p(nchar(' ', 10-siz.length())+siz+" "+name);
                    }
                    rs.close();
                    rs = null;        // so main() won't rs.close()
                } else if (args[argpos].equals("-import")) {
                    ++argpos;
                    while (argpos < args.length && !args[argpos].startsWith("-")) {
                        importFile(args[argpos++]);
                    }
                } else if (args[argpos].equals("-export")) {
                    ++argpos;
                    String name = args[argpos++];
                    File path;
                    if (argpos < args.length &&
			!args[argpos].startsWith("-")) {
                        path = new File(args[argpos++]);
                    } else {
                        path = new File(".");
                    }
                    p("Read BLOBs with name like "+name+
                      " to directory "+path.getCanonicalPath());
                    if (!path.isDirectory()) {
                        p("Directory not found");
                        return true;
                    }
                    exportFiles(name, path);
                } else {
                    p("Unrecognized switch: "+args[argpos]);
                    return true;
                }
            }
        } catch (Exception ex) {
            p(ex.toString());
        }
        return true;
    }


    static void main(String args[])
    {
        if (!parseArg(args)) usage();
        if (rs != null) try {rs.close();} catch (Exception ex) {}
        if (psins != null) try {psins.close();} catch (Exception ex) {}
        if (psupd != null) try {psupd.close();} catch (Exception ex) {}
        if (pssel != null) try {pssel.close();} catch (Exception ex) {}
        if (stm != null) try {stm.close();} catch (Exception ex) {}
        if (con != null) try {con.close();} catch (Exception ex) {}
    }
}