Example usage for java.sql Connection prepareStatement

List of usage examples for java.sql Connection prepareStatement

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Connection
  6. prepareStatement

Introduction

In this page you can find the example usage for java.sql Connection prepareStatement.

Prototype

PreparedStatement prepareStatement(String sql) throws SQLException;

Source Link

Document

Creates a PreparedStatement object for sending parameterized SQL statements to the database.

Usage

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = null;
    PreparedStatement pstmt = null;
    java.sql.Array sqlArray = null;
    conn = getOracleConnection();// w w  w  .  j av  a  2  s . co m
    // For oracle you need an array descriptor specifying
    // the type of the array and a connection to the database
    // the first parameter must match with the SQL ARRAY type created
    ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("CHAR_ARRAY", conn);
    // then obtain an Array filled with the content below
    String[] content = { "v1", "v2", "v3", "v4" };
    sqlArray = new oracle.sql.ARRAY(arrayDescriptor, conn, content);

    String query = "insert into CHAR_ARRAY_TABLE(id, array) values(?, ?)";

    pstmt = conn.prepareStatement(query);
    pstmt.setString(1, "0001");
    pstmt.setArray(2, sqlArray);

    int rowCount = pstmt.executeUpdate();
    System.out.println("rowCount=" + rowCount);
    System.out.println("--Demo_PreparedStatement_SetArray end--");
    pstmt.close();
    conn.close();
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    ResultSet rs = null;/*w w  w  . j  a v  a  2 s . c om*/
    Connection conn = null;
    PreparedStatement pstmt = null;
    PreparedStatement pstmt2 = null;
    conn = getOracleConnection();
    String[] columnNames = { "id", "name", "content", "date_created" };
    Object[] inputValues = new Object[columnNames.length];
    inputValues[0] = new java.math.BigDecimal(100);
    inputValues[1] = new String("String Value");
    inputValues[2] = new String("This is my resume.");
    inputValues[3] = new Timestamp((new java.util.Date()).getTime());

    // prepare blob object from an existing binary column
    String insert = "insert into resume (id, name, content, date_created ) values(?, ?, ?, ?)";
    pstmt = conn.prepareStatement(insert);

    pstmt.setObject(1, inputValues[0]);
    pstmt.setObject(2, inputValues[1]);
    pstmt.setObject(3, inputValues[2]);
    pstmt.setObject(4, inputValues[3]);

    pstmt.executeUpdate();
    String query = "select id, name, content, date_created from resume where id=?";

    pstmt2 = conn.prepareStatement(query);
    pstmt2.setObject(1, inputValues[0]);
    rs = pstmt2.executeQuery();
    Object[] outputValues = new Object[columnNames.length];
    if (rs.next()) {
        for (int i = 0; i < columnNames.length; i++) {
            outputValues[i] = rs.getObject(i + 1);
        }
    }
    System.out.println("id=" + ((java.math.BigDecimal) outputValues[0]).toString());
    System.out.println("name=" + ((String) outputValues[1]));
    System.out.println("content=" + ((Clob) outputValues[2]));
    System.out.println("date_created=" + ((java.sql.Date) outputValues[3]).toString());

    rs.close();
    pstmt.close();
    pstmt2.close();
    conn.close();

}

From source file:TransactionPairs.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";
    Connection con = null;
    Statement stmt;/*from   w w  w.jav a 2s. c o m*/
    PreparedStatement updateSales;
    PreparedStatement updateTotal;
    String updateString = "update COFFEES " + "set SALES = ? where COF_NAME = ?";

    String updateStatement = "update COFFEES " + "set TOTAL = TOTAL + ? where COF_NAME = ?";
    String query = "select COF_NAME, SALES, TOTAL from COFFEES";

    try {
        Class.forName("myDriver.ClassName");

    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {

        con = DriverManager.getConnection(url, "myLogin", "myPassword");

        updateSales = con.prepareStatement(updateString);
        updateTotal = con.prepareStatement(updateStatement);
        int[] salesForWeek = { 175, 150, 60, 155, 90 };
        String[] coffees = { "Colombian", "French_Roast", "Espresso", "Colombian_Decaf", "French_Roast_Decaf" };
        int len = coffees.length;
        con.setAutoCommit(false);
        for (int i = 0; i < len; i++) {
            updateSales.setInt(1, salesForWeek[i]);
            updateSales.setString(2, coffees[i]);
            updateSales.executeUpdate();

            updateTotal.setInt(1, salesForWeek[i]);
            updateTotal.setString(2, coffees[i]);
            updateTotal.executeUpdate();
            con.commit();
        }

        con.setAutoCommit(true);

        updateSales.close();
        updateTotal.close();

        stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        while (rs.next()) {
            String c = rs.getString("COF_NAME");
            int s = rs.getInt("SALES");
            int t = rs.getInt("TOTAL");
            System.out.println(c + "     " + s + "    " + t);
        }

        stmt.close();
        con.close();

    } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        if (con != null) {
            try {
                System.err.print("Transaction is being ");
                System.err.println("rolled back");
                con.rollback();
            } catch (SQLException excep) {
                System.err.print("SQLException: ");
                System.err.println(excep.getMessage());
            }
        }
    }
}

From source file:TransactionPairs.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";
    Connection con = null;
    Statement stmt;//www.  j  a  v a2 s .  c om
    PreparedStatement updateSales;
    PreparedStatement updateTotal;
    String updateString = "update COFFEES " + "set SALES = ? where COF_NAME like ?";

    String updateStatement = "update COFFEES " + "set TOTAL = TOTAL + ? where COF_NAME like ?";
    String query = "select COF_NAME, SALES, TOTAL from COFFEES";

    try {
        Class.forName("myDriver.ClassName");

    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {

        con = DriverManager.getConnection(url, "myLogin", "myPassword");

        updateSales = con.prepareStatement(updateString);
        updateTotal = con.prepareStatement(updateStatement);
        int[] salesForWeek = { 175, 150, 60, 155, 90 };
        String[] coffees = { "Colombian", "French_Roast", "Espresso", "Colombian_Decaf", "French_Roast_Decaf" };
        int len = coffees.length;
        con.setAutoCommit(false);
        for (int i = 0; i < len; i++) {
            updateSales.setInt(1, salesForWeek[i]);
            updateSales.setString(2, coffees[i]);
            updateSales.executeUpdate();

            updateTotal.setInt(1, salesForWeek[i]);
            updateTotal.setString(2, coffees[i]);
            updateTotal.executeUpdate();
            con.commit();
        }

        con.setAutoCommit(true);

        updateSales.close();
        updateTotal.close();

        stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        while (rs.next()) {
            String c = rs.getString("COF_NAME");
            int s = rs.getInt("SALES");
            int t = rs.getInt("TOTAL");
            System.out.println(c + "     " + s + "    " + t);
        }

        stmt.close();
        con.close();

    } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        if (con != null) {
            try {
                System.err.print("Transaction is being ");
                System.err.println("rolled back");
                con.rollback();
            } catch (SQLException excep) {
                System.err.print("SQLException: ");
                System.err.println(excep.getMessage());
            }
        }
    }
}

From source file:com.l2jfree.loginserver.tools.L2AccountManager.java

/**
 * Launches the interactive account manager.
 * //w  ww . ja  v  a  2  s .c om
 * @param args ignored
 */
public static void main(String[] args) {
    // LOW rework this crap
    Util.printSection("Account Management");

    _log.info("Please choose:");
    //_log.info("list - list registered accounts");
    _log.info("reg - register a new account");
    _log.info("rem - remove a registered account");
    _log.info("prom - promote a registered account");
    _log.info("dem - demote a registered account");
    _log.info("ban - ban a registered account");
    _log.info("unban - unban a registered account");
    _log.info("quit - exit this application");

    BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
    L2AccountManager acm = new L2AccountManager();

    String line;
    try {
        while ((line = br.readLine()) != null) {
            line = line.trim();
            Connection con = null;
            switch (acm.getState()) {
            case USER_NAME:
                line = line.toLowerCase();
                try {
                    con = L2Database.getConnection();
                    PreparedStatement ps = con
                            .prepareStatement("SELECT superuser FROM account WHERE username LIKE ?");
                    ps.setString(1, line);
                    ResultSet rs = ps.executeQuery();
                    if (!rs.next()) {
                        acm.setUser(line);

                        _log.info("Desired password:");
                        acm.setState(ManagerState.PASSWORD);
                    } else {
                        _log.info("User name already in use.");
                        acm.setState(ManagerState.INITIAL_CHOICE);
                    }
                    rs.close();
                    ps.close();
                } catch (SQLException e) {
                    _log.error("Could not access database!", e);
                    acm.setState(ManagerState.INITIAL_CHOICE);
                } finally {
                    L2Database.close(con);
                }
                break;
            case PASSWORD:
                try {
                    MessageDigest sha = MessageDigest.getInstance("SHA");
                    byte[] pass = sha.digest(line.getBytes("US-ASCII"));
                    acm.setPass(HexUtil.bytesToHexString(pass));
                } catch (NoSuchAlgorithmException e) {
                    _log.fatal("SHA1 is not available!", e);
                    Shutdown.exit(TerminationStatus.ENVIRONMENT_MISSING_COMPONENT_OR_SERVICE);
                } catch (UnsupportedEncodingException e) {
                    _log.fatal("ASCII is not available!", e);
                    Shutdown.exit(TerminationStatus.ENVIRONMENT_MISSING_COMPONENT_OR_SERVICE);
                }
                _log.info("Super user: [y/n]");
                acm.setState(ManagerState.SUPERUSER);
                break;
            case SUPERUSER:
                try {
                    if (line.length() != 1)
                        throw new IllegalArgumentException("One char required.");
                    else if (line.charAt(0) == 'y')
                        acm.setSuper(true);
                    else if (line.charAt(0) == 'n')
                        acm.setSuper(false);
                    else
                        throw new IllegalArgumentException("Invalid choice.");

                    _log.info("Date of birth: [yyyy-mm-dd]");
                    acm.setState(ManagerState.DOB);
                } catch (IllegalArgumentException e) {
                    _log.info("[y/n]?");
                }
                break;
            case DOB:
                try {
                    Date d = Date.valueOf(line);
                    if (d.after(new Date(System.currentTimeMillis())))
                        throw new IllegalArgumentException("Future date specified.");
                    acm.setDob(d);

                    _log.info("Ban reason ID or nothing:");
                    acm.setState(ManagerState.SUSPENDED);
                } catch (IllegalArgumentException e) {
                    _log.info("[yyyy-mm-dd] in the past:");
                }
                break;
            case SUSPENDED:
                try {
                    if (line.length() > 0) {
                        int id = Integer.parseInt(line);
                        acm.setBan(L2BanReason.getById(id));
                    } else
                        acm.setBan(null);

                    try {
                        con = L2Database.getConnection();
                        PreparedStatement ps = con.prepareStatement(
                                "INSERT INTO account (username, password, superuser, birthDate, banReason) VALUES (?, ?, ?, ?, ?)");
                        ps.setString(1, acm.getUser());
                        ps.setString(2, acm.getPass());
                        ps.setBoolean(3, acm.isSuper());
                        ps.setDate(4, acm.getDob());
                        L2BanReason lbr = acm.getBan();
                        if (lbr == null)
                            ps.setNull(5, Types.INTEGER);
                        else
                            ps.setInt(5, lbr.getId());
                        ps.executeUpdate();
                        _log.info("Account " + acm.getUser() + " has been registered.");
                        ps.close();
                    } catch (SQLException e) {
                        _log.error("Could not register an account!", e);
                    } finally {
                        L2Database.close(con);
                    }
                    acm.setState(ManagerState.INITIAL_CHOICE);
                } catch (NumberFormatException e) {
                    _log.info("Ban reason ID or nothing:");
                }
                break;
            case REMOVE:
                acm.setUser(line.toLowerCase());
                try {
                    con = L2Database.getConnection();
                    PreparedStatement ps = con.prepareStatement("DELETE FROM account WHERE username LIKE ?");
                    ps.setString(1, acm.getUser());
                    int cnt = ps.executeUpdate();
                    if (cnt > 0)
                        _log.info("Account " + acm.getUser() + " has been removed.");
                    else
                        _log.info("Account " + acm.getUser() + " does not exist!");
                    ps.close();
                } catch (SQLException e) {
                    _log.error("Could not remove an account!", e);
                } finally {
                    L2Database.close(con);
                }
                acm.setState(ManagerState.INITIAL_CHOICE);
                break;
            case PROMOTE:
                acm.setUser(line.toLowerCase());
                try {
                    con = L2Database.getConnection();
                    PreparedStatement ps = con
                            .prepareStatement("UPDATE account SET superuser = ? WHERE username LIKE ?");
                    ps.setBoolean(1, true);
                    ps.setString(2, acm.getUser());
                    int cnt = ps.executeUpdate();
                    if (cnt > 0)
                        _log.info("Account " + acm.getUser() + " has been promoted.");
                    else
                        _log.info("Account " + acm.getUser() + " does not exist!");
                    ps.close();
                } catch (SQLException e) {
                    _log.error("Could not promote an account!", e);
                } finally {
                    L2Database.close(con);
                }
                acm.setState(ManagerState.INITIAL_CHOICE);
                break;
            case DEMOTE:
                acm.setUser(line.toLowerCase());
                try {
                    con = L2Database.getConnection();
                    PreparedStatement ps = con
                            .prepareStatement("UPDATE account SET superuser = ? WHERE username LIKE ?");
                    ps.setBoolean(1, false);
                    ps.setString(2, acm.getUser());
                    int cnt = ps.executeUpdate();
                    if (cnt > 0)
                        _log.info("Account " + acm.getUser() + " has been demoted.");
                    else
                        _log.info("Account " + acm.getUser() + " does not exist!");
                    ps.close();
                } catch (SQLException e) {
                    _log.error("Could not demote an account!", e);
                } finally {
                    L2Database.close(con);
                }
                acm.setState(ManagerState.INITIAL_CHOICE);
                break;
            case UNBAN:
                acm.setUser(line.toLowerCase());
                try {
                    con = L2Database.getConnection();
                    PreparedStatement ps = con
                            .prepareStatement("UPDATE account SET banReason = ? WHERE username LIKE ?");
                    ps.setNull(1, Types.INTEGER);
                    ps.setString(2, acm.getUser());
                    int cnt = ps.executeUpdate();
                    if (cnt > 0)
                        _log.info("Account " + acm.getUser() + " has been unbanned.");
                    else
                        _log.info("Account " + acm.getUser() + " does not exist!");
                    ps.close();
                } catch (SQLException e) {
                    _log.error("Could not demote an account!", e);
                } finally {
                    L2Database.close(con);
                }
                acm.setState(ManagerState.INITIAL_CHOICE);
                break;
            case BAN:
                line = line.toLowerCase();
                try {
                    con = L2Database.getConnection();
                    PreparedStatement ps = con
                            .prepareStatement("SELECT superuser FROM account WHERE username LIKE ?");
                    ps.setString(1, line);
                    ResultSet rs = ps.executeQuery();
                    if (rs.next()) {
                        acm.setUser(line);

                        _log.info("Ban reason ID:");
                        acm.setState(ManagerState.REASON);
                    } else {
                        _log.info("Account does not exist.");
                        acm.setState(ManagerState.INITIAL_CHOICE);
                    }
                    rs.close();
                    ps.close();
                } catch (SQLException e) {
                    _log.error("Could not access database!", e);
                    acm.setState(ManagerState.INITIAL_CHOICE);
                } finally {
                    L2Database.close(con);
                }
                break;
            case REASON:
                try {
                    int ban = Integer.parseInt(line);
                    con = L2Database.getConnection();
                    PreparedStatement ps = con
                            .prepareStatement("UPDATE account SET banReason = ? WHERE username LIKE ?");
                    ps.setInt(1, ban);
                    ps.setString(2, acm.getUser());
                    ps.executeUpdate();
                    _log.info("Account " + acm.getUser() + " has been banned.");
                    ps.close();
                } catch (NumberFormatException e) {
                    _log.info("Ban reason ID:");
                } catch (SQLException e) {
                    _log.error("Could not ban an account!", e);
                } finally {
                    L2Database.close(con);
                }
                acm.setState(ManagerState.INITIAL_CHOICE);
                break;
            default:
                line = line.toLowerCase();
                if (line.equals("reg")) {
                    _log.info("Desired user name:");
                    acm.setState(ManagerState.USER_NAME);
                } else if (line.equals("rem")) {
                    _log.info("User name:");
                    acm.setState(ManagerState.REMOVE);
                } else if (line.equals("prom")) {
                    _log.info("User name:");
                    acm.setState(ManagerState.PROMOTE);
                } else if (line.equals("dem")) {
                    _log.info("User name:");
                    acm.setState(ManagerState.DEMOTE);
                } else if (line.equals("unban")) {
                    _log.info("User name:");
                    acm.setState(ManagerState.UNBAN);
                } else if (line.equals("ban")) {
                    _log.info("User name:");
                    acm.setState(ManagerState.BAN);
                } else if (line.equals("quit"))
                    Shutdown.exit(TerminationStatus.MANUAL_SHUTDOWN);
                else
                    _log.info("Incorrect command.");
                break;
            }
        }
    } catch (IOException e) {
        _log.fatal("Could not process input!", e);
    } finally {
        IOUtils.closeQuietly(br);
    }
}

From source file:com.l2jserver.model.template.NPCTemplateConverter.java

public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException, JAXBException {
    controllers.put("L2Teleporter", TeleporterController.class);
    controllers.put("L2CastleTeleporter", TeleporterController.class);
    controllers.put("L2Npc", BaseNPCController.class);
    controllers.put("L2Monster", MonsterController.class);
    controllers.put("L2FlyMonster", MonsterController.class);
    Class.forName("com.mysql.jdbc.Driver");

    final File target = new File("generated/template/npc");

    System.out.println("Scaning legacy HTML files...");
    htmlScannedFiles = FileUtils.listFiles(L2J_HTML_FOLDER, new String[] { "html", "htm" }, true);

    final JAXBContext c = JAXBContext.newInstance(NPCTemplate.class, Teleports.class);

    final Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD);
    {/*from w  w w  . j  av a  2s. c o  m*/
        System.out.println("Converting teleport templates...");
        teleportation.teleport = CollectionFactory.newList();

        final Marshaller m = c.createMarshaller();
        m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, true);

        final PreparedStatement st = conn.prepareStatement("SELECT * FROM teleport");
        st.execute();
        final ResultSet rs = st.getResultSet();
        while (rs.next()) {
            final TeleportationTemplate template = new TeleportationTemplate();

            template.id = new TeleportationTemplateID(rs.getInt("id"), null);
            template.name = rs.getString("Description");
            TemplateCoordinate coord = new TemplateCoordinate();
            coord.x = rs.getInt("loc_x");
            coord.y = rs.getInt("loc_y");
            coord.z = rs.getInt("loc_z");
            template.point = coord;
            template.price = rs.getInt("price");
            template.item = rs.getInt("itemId");
            if (rs.getBoolean("fornoble")) {
                template.restrictions = new Restrictions();
                template.restrictions.restriction = Arrays.asList("NOBLE");
            }
            teleportation.teleport.add(template);
        }
        m.marshal(teleportation, getXMLSerializer(new FileOutputStream(new File(target, "../teleports.xml"))));
        // System.exit(0);
    }

    System.out.println("Generating template XML files...");
    // c.generateSchema(new SchemaOutputResolver() {
    // @Override
    // public Result createOutput(String namespaceUri,
    // String suggestedFileName) throws IOException {
    // // System.out.println(new File(target, suggestedFileName));
    // // return null;
    // return new StreamResult(new File(target, suggestedFileName));
    // }
    // });

    try {
        final Marshaller m = c.createMarshaller();
        m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, true);

        final PreparedStatement st = conn.prepareStatement(
                "SELECT npc.*, npcskills.level AS race " + "FROM npc " + "LEFT JOIN npcskills "
                        + "ON(npc.idTemplate = npcskills.npcid AND npcskills.skillid = ?)");
        st.setInt(1, 4416);
        st.execute();
        final ResultSet rs = st.getResultSet();
        while (rs.next()) {
            Object[] result = fillNPC(rs);
            NPCTemplate t = (NPCTemplate) result[0];
            String type = (String) result[1];

            String folder = createFolder(type);
            if (folder.isEmpty()) {
                m.setProperty(Marshaller.JAXB_SCHEMA_LOCATION, "npc ../npc.xsd");
            } else {
                m.setProperty(Marshaller.JAXB_SCHEMA_LOCATION, "npc ../../npc.xsd");
            }

            final File file = new File(target, "npc/" + folder + "/" + t.getID().getID()
                    + (t.getInfo().getName() != null ? "-" + camelCase(t.getInfo().getName().getValue()) : "")
                    + ".xml");
            file.getParentFile().mkdirs();
            templates.add(t);

            try {
                m.marshal(t, getXMLSerializer(new FileOutputStream(file)));
            } catch (MarshalException e) {
                System.err.println("Could not generate XML template file for "
                        + t.getInfo().getName().getValue() + " - " + t.getID());
                file.delete();
            }
        }

        System.out.println("Generated " + templates.size() + " templates");

        System.gc();
        System.out.println("Free: " + FileUtils.byteCountToDisplaySize(Runtime.getRuntime().freeMemory()));
        System.out.println("Total: " + FileUtils.byteCountToDisplaySize(Runtime.getRuntime().totalMemory()));
        System.out.println("Used: " + FileUtils.byteCountToDisplaySize(
                Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory()));
        System.out.println("Max: " + FileUtils.byteCountToDisplaySize(Runtime.getRuntime().maxMemory()));
    } finally {
        conn.close();
    }
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    String WRITE_OBJECT_SQL = "BEGIN " + "  INSERT INTO java_objects(object_id, object_name, object_value) "
            + "  VALUES (?, ?, empty_blob()) " + "  RETURN object_value INTO ?; " + "END;";
    String READ_OBJECT_SQL = "SELECT object_value FROM java_objects WHERE object_id = ?";

    Connection conn = getOracleConnection();
    conn.setAutoCommit(false);/*  www  .  j  ava 2  s  .c  om*/
    List<Object> list = new ArrayList<Object>();
    list.add("This is a short string.");
    list.add(new Integer(1234));
    list.add(new java.util.Date());

    // write object to Oracle
    long id = 0001;
    String className = list.getClass().getName();
    CallableStatement cstmt = conn.prepareCall(WRITE_OBJECT_SQL);

    cstmt.setLong(1, id);
    cstmt.setString(2, className);

    cstmt.registerOutParameter(3, java.sql.Types.BLOB);

    cstmt.executeUpdate();
    BLOB blob = (BLOB) cstmt.getBlob(3);
    OutputStream os = blob.getBinaryOutputStream();
    ObjectOutputStream oop = new ObjectOutputStream(os);
    oop.writeObject(list);
    oop.flush();
    oop.close();
    os.close();

    // Read object from oracle
    PreparedStatement pstmt = conn.prepareStatement(READ_OBJECT_SQL);
    pstmt.setLong(1, id);
    ResultSet rs = pstmt.executeQuery();
    rs.next();
    InputStream is = rs.getBlob(1).getBinaryStream();
    ObjectInputStream oip = new ObjectInputStream(is);
    Object object = oip.readObject();
    className = object.getClass().getName();
    oip.close();
    is.close();
    rs.close();
    pstmt.close();
    conn.commit();

    // de-serialize list a java object from a given objectID
    List listFromDatabase = (List) object;
    System.out.println("[After De-Serialization] list=" + listFromDatabase);
    conn.close();
}

From source file:ImageStringToBlob.java

public static void main(String[] args) {
    Connection conn = null;

    if (args.length != 1) {
        System.out.println("Missing argument: full path to <oscar.properties>");
        return;/*from  w  w  w.  jav a2  s  . c  o m*/
    }

    try {

        FileInputStream fin = new FileInputStream(args[0]);
        Properties prop = new Properties();
        prop.load(fin);

        String driver = prop.getProperty("db_driver");
        String uri = prop.getProperty("db_uri");
        String db = prop.getProperty("db_name");
        String username = prop.getProperty("db_username");
        String password = prop.getProperty("db_password");

        Class.forName(driver);
        conn = DriverManager.getConnection(uri + db, username, password);
        conn.setAutoCommit(true); // no transactions

        /*
         * select all records ids with image_data not null and contents is null
         * for each id fetch record
         * migrate data from image_data to contents
         */
        String sql = "select image_id from client_image where image_data is not null and contents is null";
        PreparedStatement pst = conn.prepareStatement(sql);
        ResultSet rs = pst.executeQuery();
        List<Long> ids = new ArrayList<Long>();

        while (rs.next()) {
            ids.add(rs.getLong("image_id"));
        }

        rs.close();

        sql = "select image_data from client_image where image_id = ?";
        pst = conn.prepareStatement(sql);

        System.out.println("Migrating image data for " + ids.size() + " images...");
        for (Long id : ids) {
            pst.setLong(1, id);
            ResultSet imagesRS = pst.executeQuery();
            while (imagesRS.next()) {
                String dataString = imagesRS.getString("image_data");
                Blob dataBlob = fromStringToBlob(dataString);
                if (writeBlobToDb(conn, id, dataBlob) == 1) {
                    System.out.println("Image data migrated for image_id: " + id);
                }
            }
            imagesRS.close();
        }
        System.out.println("Migration completed.");

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

From source file:CreateTableWithAllDataTypesInMySQL.java

public static void main(String[] args) throws Exception {
    PreparedStatement pstmt = null;
    Connection conn = null;
    try {//from   w  ww. j  a va 2 s  . c o m
        StringBuffer sql = new StringBuffer("CREATE TABLE tableWithAllTypes(");
        sql.append("column_boolean       BOOL, "); // boolean
        sql.append("column_byte          TINYINT, "); // byte
        sql.append("column_short         SMALLINT, "); // short
        sql.append("column_int           INTEGER, "); // int
        sql.append("column_long          BIGINT, "); // long
        sql.append("column_float         FLOAT, "); // float
        sql.append("column_double        DOUBLE PRECISION, "); // double
        sql.append("column_bigdecimal    DECIMAL(13,0), "); // BigDecimal
        sql.append("column_string        VARCHAR(254), "); // String
        sql.append("column_date          DATE, "); // Date
        sql.append("column_time          TIME, "); // Time
        sql.append("column_timestamp     TIMESTAMP, "); // Timestamp
        sql.append("column_asciistream1  TINYTEXT, "); // Clob ( 2^8 bytes)
        sql.append("column_asciistream2  TEXT, "); // Clob ( 2^16 bytes)
        sql.append("column_asciistream3  MEDIUMTEXT, "); // Clob (2^24 bytes)
        sql.append("column_asciistream4  LONGTEXT, "); // Clob ( 2^32 bytes)
        sql.append("column_blob1         TINYBLOB, "); // Blob ( 2^8 bytes)
        sql.append("column_blob2         BLOB, "); // Blob ( 2^16 bytes)
        sql.append("column_blob3         MEDIUMBLOB, "); // Blob ( 2^24 bytes)
        sql.append("column_blob4         LONGBLOB)"); // Blob ( 2^32 bytes)

        conn = getConnection();
        pstmt = conn.prepareStatement(sql.toString());
        pstmt.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        conn.close();
    }
}

From source file:my.yelp.populate.java

public static void main(String[] args)
        throws FileNotFoundException, ParseException, IOException, java.text.ParseException {
    try {/*from  ww  w .j  a v a 2 s.co  m*/

        DbConnection A1 = new DbConnection();
        Connection con = A1.getConnection();

        JSONParser jsonParser;
        jsonParser = new JSONParser();

        Object obj1 = jsonParser.parse(new FileReader("C:\\Users\\Sanjay Desai\\Desktop\\yelp_user.json"));
        Object obj2 = jsonParser.parse(new FileReader("C:\\Users\\Sanjay Desai\\Desktop\\yelp_business.json"));
        Object obj3 = jsonParser.parse(new FileReader("C:\\Users\\Sanjay Desai\\Desktop\\yelp_review.json"));
        Object obj4 = jsonParser.parse(new FileReader("C:\\Users\\Sanjay Desai\\Desktop\\yelp_checkin.json"));

        JSONArray jsonArray1;
        jsonArray1 = (JSONArray) obj1;

        JSONArray jsonArray2;
        jsonArray2 = (JSONArray) obj2;

        JSONArray jsonArray3;
        jsonArray3 = (JSONArray) obj3;

        JSONArray jsonArray4;
        jsonArray4 = (JSONArray) obj4;

        // yelp_user
        String yelping_since, name1, user_id, type1;
        Long review_count1, fans;
        Double average_stars;
        Statement stmt;

        stmt = con.createStatement();
        stmt.executeUpdate("Delete from N_User");

        for (int i = 0; i < (jsonArray1.size()); i++)

        {
            JSONObject jsonObject = (JSONObject) jsonArray1.get(i);
            yelping_since = (String) jsonObject.get("yelping_since") + "-01";

            JSONArray friends = (JSONArray) jsonObject.get("friends");
            int friends_size = friends.size();

            review_count1 = (Long) jsonObject.get("review_count");
            name1 = (String) jsonObject.get("name");
            user_id = (String) jsonObject.get("user_id");
            fans = (Long) jsonObject.get("fans");
            average_stars = (Double) jsonObject.get("average_stars");
            type1 = (String) jsonObject.get("type");

            try (PreparedStatement pstmt1 = con.prepareStatement(
                    "Insert INTO N_User(yelping_since,friends_size,review_count,name,user_id,fans,average_stars,type) VALUES(?,?,?,?,?,?,?,?)")) {

                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                java.util.Date myDate = format.parse(yelping_since);

                pstmt1.setDate(1, new java.sql.Date(myDate.getTime()));
                pstmt1.setInt(2, friends_size);
                pstmt1.setLong(3, review_count1);
                pstmt1.setString(4, name1);
                pstmt1.setString(5, user_id);
                pstmt1.setLong(6, fans);
                pstmt1.setDouble(7, average_stars);
                pstmt1.setString(8, type1);
                pstmt1.executeUpdate();
            } catch (java.text.ParseException ex) {
                Logger.getLogger(populate.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        //yelp_business

        String business_id, address, city, state, name, type_business;

        Double stars;

        for (int i = 0; i < jsonArray2.size(); i++) {
            JSONObject jsonObject = (JSONObject) jsonArray2.get(i);
            business_id = (String) jsonObject.get("business_id");
            address = (String) jsonObject.get("full_address");
            city = (String) jsonObject.get("city");
            state = (String) jsonObject.get("state");
            name = (String) jsonObject.get("name");
            stars = (Double) jsonObject.get("stars");
            type_business = (String) jsonObject.get("type");

            try (PreparedStatement pstmt2 = con.prepareStatement(
                    "Insert INTO N_Business(business_id,address,city,state,name,stars,type_business) VALUES(?,?,?,?,?,?,?)")) {
                pstmt2.setString(1, business_id);
                pstmt2.setString(2, address);
                pstmt2.setString(3, city);
                pstmt2.setString(4, state);
                pstmt2.setString(5, name);
                pstmt2.setDouble(6, stars);
                pstmt2.setString(7, type_business);
                pstmt2.executeUpdate();
                pstmt2.close();
            }

        }

        //Category Table
        String[] categories = { "Active Life", "Arts & Entertainment", "Automotive", "Car Rental", "Cafes",
                "Beauty & Spas", "Convenience Stores", "Dentists", "Doctors", "Drugstores", "Department Stores",
                "Education", "Event Planning & Services", "Flowers & Gifts", "Food", "Health & Medical",
                "Home Services", "Home & Garden", "Hospitals", "Hotels & travel", "Hardware stores", "Grocery",
                "Medical Centers", "Nurseries & Gardening", "Nightlife", "Restaurants", "Shopping",
                "Transportation" };

        JSONArray category;
        String[] individual_category = new String[100];
        int count = 0, flag = 0, m = 0, n = 0;
        String[] business_category = new String[50];
        String[] subcategory = new String[50];

        for (int i = 0; i < jsonArray2.size(); i++) {
            JSONObject jsonObject3 = (JSONObject) jsonArray2.get(i);
            String business_id2 = (String) jsonObject3.get("business_id");
            category = (JSONArray) jsonObject3.get("categories");
            for (int j = 0; j < category.size(); j++) {
                individual_category[j] = (String) category.get(j);
                count = count + 1;
            }
            for (int k = 0; k < count; k++) {
                for (String categorie : categories) {

                    if (individual_category[k].equals(categorie)) {
                        flag = 1;
                        break;
                    }
                }
                if (flag == 1) {
                    business_category[m] = individual_category[k];
                    m = m + 1;
                    flag = 0;
                } else {
                    subcategory[n] = individual_category[k];
                    n = n + 1;
                }
            }
            for (int p = 0; p < m; p++) {
                for (int q = 0; q < n; q++) {
                    try (PreparedStatement pstmt3 = con.prepareStatement(
                            "INSERT INTO N_Category(business_id,category,subcategory) VALUES(?,?,?)")) {
                        pstmt3.setString(1, business_id2);
                        pstmt3.setString(2, business_category[p]);
                        pstmt3.setString(3, subcategory[q]);
                        pstmt3.executeUpdate();

                    }
                }
            }
            count = 0;
            m = 0;
            n = 0;
        }

        //yelp_review

        String user_id3, review_id, type3, business_id3, text, text1, review_date;
        Long stars3;
        int votes = 0;
        Integer no_votes;

        JSONObject votes_info;
        Set<String> keys;

        for (int i = 0; i < jsonArray3.size(); i++) {
            JSONObject jsonObject = (JSONObject) jsonArray3.get(i);

            votes_info = (JSONObject) jsonObject.get("votes");
            keys = votes_info.keySet();
            for (String r_key : keys) {
                votes = (int) (votes + (Long) votes_info.get(r_key));
            }
            no_votes = toIntExact(votes);
            user_id3 = (String) jsonObject.get("user_id");

            review_id = (String) jsonObject.get("review_id");
            business_id3 = (String) jsonObject.get("business_id");
            review_date = (String) jsonObject.get("date");
            text1 = (String) jsonObject.get("text");
            text = text1.substring(0, Math.min(1000, text1.length()));
            stars3 = (Long) jsonObject.get("stars");
            type3 = (String) jsonObject.get("type");

            try (PreparedStatement pstmt4 = con.prepareStatement(
                    "Insert INTO N_Review(no_votes,user_id,review_id,business_id,review_date,text,stars,type) VALUES(?,?,?,?,?,?,?,?)")) {
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                java.util.Date myDate = format.parse(review_date);

                pstmt4.setInt(1, no_votes);
                pstmt4.setString(2, user_id3);
                pstmt4.setString(3, review_id);
                pstmt4.setString(4, business_id3);
                pstmt4.setDate(5, new java.sql.Date(myDate.getTime()));
                pstmt4.setString(6, text);
                pstmt4.setLong(7, stars3);
                pstmt4.setString(8, type3);
                pstmt4.executeUpdate();
                pstmt4.close();
            }

        }

        //Checkin_Info
        JSONObject checkin_info;
        String business_id4;
        Long check_in_count;
        Set<String> keys1;
        String[] timing = new String[10];
        int n1 = 0, time, hour;

        //Inserting into checkin_info
        for (int i = 0; i < jsonArray4.size(); i++) {
            JSONObject jsonObject4 = (JSONObject) jsonArray4.get(i);
            checkin_info = (JSONObject) jsonObject4.get("checkin_info");
            business_id4 = (String) jsonObject4.get("business_id");
            keys1 = checkin_info.keySet();

            for (String key : keys1) {
                check_in_count = (Long) checkin_info.get(key);
                for (String x : key.split("-")) {
                    timing[n1] = x;
                    n1 = n1 + 1;
                }
                n1 = 0;
                hour = Integer.parseInt(timing[0]);
                time = Integer.parseInt(timing[1]);

                try (PreparedStatement pstmt5 = con.prepareStatement(
                        "INSERT INTO check_info(business_id,hour,day,check_in_count)VALUES(?,?,?,?)")) {
                    pstmt5.setString(1, business_id4);
                    pstmt5.setInt(2, hour);
                    pstmt5.setInt(3, time);
                    pstmt5.setLong(4, check_in_count);
                    pstmt5.executeUpdate();
                }
            }

        }

        con.close();

    } catch (SQLException ex) {
        Logger.getLogger(populate.class.getName()).log(Level.SEVERE, null, ex);
    }

}