Example usage for java.sql PreparedStatement setString

List of usage examples for java.sql PreparedStatement setString

Introduction

In this page you can find the example usage for java.sql PreparedStatement setString.

Prototype

void setString(int parameterIndex, String x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given Java String value.

Usage

From source file:Main.java

public static void main(String[] argv) throws Exception {
    Connection con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/old", "user", "pass");
    Connection con1 = DriverManager.getConnection("jdbc:postgresql://localhost:5432/new", "user", "pass");

    String sql = "INSERT INTO users(" + "name," + "active," + "login," + "password)" + "VALUES(?,?,?,?)";

    Statement statement = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

    PreparedStatement pstmt = con1.prepareStatement(sql);

    ResultSet rs = statement.executeQuery("SELECT * FROM users");
    while (rs.next()) {
        String nm = rs.getString(2);
        Boolean ac = rs.getBoolean(3);
        String log = rs.getString(4);
        String pass = rs.getString(5);
        pstmt.setString(1, nm);
        pstmt.setBoolean(2, ac);//from  w w  w .j  a va 2 s. c  o m
        pstmt.setString(3, log);
        pstmt.setString(4, pass);
        pstmt.executeUpdate();
    }
    con.close();
    con1.close();
}

From source file:DemoPreparedStatementSetBlob.java

public static void main(String[] args) throws Exception {
    Connection conn = null;//from   ww w  .j a v  a2s. co m
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    java.sql.Blob blob = null;
    try {
        conn = getConnection();
        // prepare blob object from an existing binary column
        pstmt = conn.prepareStatement("select photo from my_pictures where id = ?");
        pstmt.setString(1, "0001");
        rs = pstmt.executeQuery();
        rs.next();
        blob = rs.getBlob(1);

        // prepare SQL query for inserting a new row using setBlob()
        String query = "insert into blob_table(id, blob_column) values(?, ?)";
        // begin transaction
        conn.setAutoCommit(false);

        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, "0002");
        pstmt.setBlob(2, blob);

        int rowCount = pstmt.executeUpdate();
        System.out.println("rowCount=" + rowCount);
        // end transaction
        conn.commit();
    } finally {
        rs.close();
        pstmt.close();
        conn.close();
    }
}

From source file:DemoPreparedStatementSetIntegers.java

public static void main(String[] args) throws Exception {
    String id = "0001";
    byte byteValue = 1;
    short shortValue = 1;
    int intValue = 12345;
    long longValue = 100000000L;

    Connection conn = null;//from   w  w w .  jav a2s  .  c  om
    PreparedStatement pstmt = null;
    try {
        conn = getConnection();
        String query = "insert into integer_table(id, byte_column, "
                + "short_column, int_column, long_column) values(?, ?, ?, ?, ?)";

        // create PrepareStatement object
        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, id);
        pstmt.setByte(2, byteValue);
        pstmt.setShort(3, shortValue);
        pstmt.setInt(4, intValue);
        pstmt.setLong(5, longValue);

        // execute query, and return number of rows created
        int rowCount = pstmt.executeUpdate();
        System.out.println("rowCount=" + rowCount);
    } finally {
        pstmt.close();
        conn.close();
    }
}

From source file:InsertTextFileToOracle.java

public static void main(String[] args) throws Exception {
    String id = "001";
    String fileName = "fileName.txt";

    FileInputStream fis = null;//from ww w . j  a  va 2  s  .c o  m
    PreparedStatement pstmt = null;
    Connection conn = null;
    try {
        conn = getConnection();
        conn.setAutoCommit(false);
        File file = new File(fileName);
        fis = new FileInputStream(file);
        pstmt = conn.prepareStatement("insert into DataFiles(id, fileName, fileBody) values (?, ?, ?)");
        pstmt.setString(1, id);
        pstmt.setString(2, fileName);
        pstmt.setAsciiStream(3, fis, (int) file.length());
        pstmt.executeUpdate();
        conn.commit();
    } catch (Exception e) {
        System.err.println("Error: " + e.getMessage());
        e.printStackTrace();
    } finally {
        pstmt.close();
        fis.close();
        conn.close();
    }
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    String deptName = "oldName";
    String newDeptName = "newName";

    ResultSet rs = null;//from w ww  .j  av a 2s.co m
    Connection conn = null;
    PreparedStatement pstmt = null;
    PreparedStatement pstmt2 = null;
    try {
        conn = getConnection();
        // prepare query for getting a REF object and PrepareStatement object
        String refQuery = "select manager from dept_table where dept_name=?";
        pstmt = conn.prepareStatement(refQuery);
        pstmt.setString(1, deptName);
        rs = pstmt.executeQuery();
        java.sql.Ref ref = null;
        if (rs.next()) {
            ref = rs.getRef(1);
        }
        if (ref == null) {
            System.out.println("error: could not get a reference for manager.");
            System.exit(1);
        }
        String query = "INSERT INTO dept_table(dept_name, manager)values(?, ?)";
        pstmt2 = conn.prepareStatement(query);
        pstmt2.setString(1, newDeptName);
        pstmt2.setRef(2, ref);
        // execute query, and return number of rows created
        int rowCount = pstmt2.executeUpdate();
        System.out.println("rowCount=" + rowCount);
    } finally {
        pstmt.close();
        pstmt2.close();
        conn.close();
    }
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    String deptName = "oldName";
    String newDeptName = "newName";

    ResultSet rs = null;//from  www .  j  a v  a  2 s. c om
    Connection conn = null;
    PreparedStatement pstmt = null;
    PreparedStatement pstmt2 = null;
    try {
        conn = getConnection();
        // prepare query for getting a REF object and PrepareStatement object
        String refQuery = "select manager from dept_table where dept_name=?";
        pstmt = conn.prepareStatement(refQuery);
        pstmt.setString(1, deptName);
        rs = pstmt.executeQuery();
        java.sql.Ref ref = null;
        if (rs.next()) {
            ref = rs.getRef("manager");
        }
        if (ref == null) {
            System.out.println("error: could not get a reference for manager.");
            System.exit(1);
        }
        String query = "INSERT INTO dept_table(dept_name, manager)values(?, ?)";
        pstmt2 = conn.prepareStatement(query);
        pstmt2.setString(1, newDeptName);
        pstmt2.setRef(2, ref);
        // execute query, and return number of rows created
        int rowCount = pstmt2.executeUpdate();
        System.out.println("rowCount=" + rowCount);
    } finally {
        pstmt.close();
        pstmt2.close();
        conn.close();
    }
}

From source file:Main.java

public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);/* www.j  a  va2 s .  c o m*/

    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";

    Connection connection = DriverManager.getConnection(url, username, password);
    String sql = "INSERT INTO mysql_all_table(" + "col_boolean," + "col_byte," + "col_short," + "col_int,"
            + "col_long," + "col_float," + "col_double," + "col_bigdecimal," + "col_string," + "col_date,"
            + "col_time," + "col_timestamp," + "col_asciistream," + "col_binarystream," + "col_blob) "
            + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    PreparedStatement pstmt = connection.prepareStatement(sql);

    pstmt.setBoolean(1, true);
    pstmt.setByte(2, (byte) 123);
    pstmt.setShort(3, (short) 123);
    pstmt.setInt(4, 123);
    pstmt.setLong(5, 123L);
    pstmt.setFloat(6, 1.23F);
    pstmt.setDouble(7, 1.23D);
    pstmt.setBigDecimal(8, new BigDecimal(1.23));
    pstmt.setString(9, "a string");
    pstmt.setDate(10, new java.sql.Date(System.currentTimeMillis()));
    pstmt.setTime(11, new Time(System.currentTimeMillis()));
    pstmt.setTimestamp(12, new Timestamp(System.currentTimeMillis()));

    File file = new File("infilename1");
    FileInputStream is = new FileInputStream(file);
    pstmt.setAsciiStream(13, is, (int) file.length());

    file = new File("infilename2");
    is = new FileInputStream(file);
    pstmt.setBinaryStream(14, is, (int) file.length());

    file = new File("infilename3");
    is = new FileInputStream(file);
    pstmt.setBinaryStream(15, is, (int) file.length());

    pstmt.executeUpdate();
}

From source file:Main.java

public static void main(String[] argv) throws Exception {
    Connection dbConnection = null;
    PreparedStatement preparedStatement = null;
    Class.forName(DB_DRIVER);/*from  ww w.ja v  a 2 s.  c  o m*/
    dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);

    String insertTableSQL = "INSERT INTO Person" + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
            + "(?,?,?,?)";

    preparedStatement = dbConnection.prepareStatement(insertTableSQL);

    preparedStatement.setInt(1, 11);
    preparedStatement.setString(2, "yourName");
    preparedStatement.setString(3, "system");
    java.util.Date today = new java.util.Date();

    preparedStatement.setTimestamp(4, new java.sql.Timestamp(today.getTime()));

    preparedStatement.executeUpdate();

    preparedStatement.close();
    dbConnection.close();

}

From source file:MainClass.java

public static void main(String[] args) {
    Connection connection = null;
    PreparedStatement statement = null;
    try {// ww  w .  j  a v a2s .  co m
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        String url = "jdbc:mysql://localhost/database";
        connection = DriverManager.getConnection(url, "username", "password");

        String sql = "UPDATE employees SET email = ? WHERE employee_id = ?";
        statement = connection.prepareStatement(sql);

        statement.setString(1, "a@a.com");
        statement.setLong(2, 1);
        statement.addBatch();

        statement.setString(1, "b@b.com");
        statement.setLong(2, 2);
        statement.addBatch();

        statement.setString(1, "c@c.com");
        statement.setLong(2, 3);
        statement.addBatch();

        statement.executeBatch();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
            } // nothing we can do
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
            } // nothing we can do
        }
    }
}

From source file:Blobs.java

public static void main(String args[]) {
    if (args.length != 1) {
        System.err.println("Syntax: <java Blobs [driver] [url] " + "[uid] [pass] [file]");
        return;//w w w . j a va  2 s .c  om
    }
    try {
        Class.forName(args[0]).newInstance();
        Connection con = DriverManager.getConnection(args[1], args[2], args[3]);
        File f = new File(args[4]);
        PreparedStatement stmt;

        if (!f.exists()) {
            // if the file does not exist
            // retrieve it from the database and write it to the named file
            ResultSet rs;

            stmt = con.prepareStatement("SELECT blobData " + "FROM BlobTest " + "WHERE fileName = ?");

            stmt.setString(1, args[0]);
            rs = stmt.executeQuery();
            if (!rs.next()) {
                System.out.println("No such file stored.");
            } else {
                Blob b = rs.getBlob(1);
                BufferedOutputStream os;

                os = new BufferedOutputStream(new FileOutputStream(f));
                os.write(b.getBytes(0, (int) b.length()), 0, (int) b.length());
                os.flush();
                os.close();
            }
        } else {
            // otherwise read it and save it to the database
            FileInputStream fis = new FileInputStream(f);
            byte[] tmp = new byte[1024];
            byte[] data = null;
            int sz, len = 0;

            while ((sz = fis.read(tmp)) != -1) {
                if (data == null) {
                    len = sz;
                    data = tmp;
                } else {
                    byte[] narr;
                    int nlen;

                    nlen = len + sz;
                    narr = new byte[nlen];
                    System.arraycopy(data, 0, narr, 0, len);
                    System.arraycopy(tmp, 0, narr, len, sz);
                    data = narr;
                    len = nlen;
                }
            }
            if (len != data.length) {
                byte[] narr = new byte[len];

                System.arraycopy(data, 0, narr, 0, len);
                data = narr;
            }
            stmt = con.prepareStatement("INSERT INTO BlobTest(fileName, " + "blobData) VALUES(?, ?)");
            stmt.setString(1, args[0]);
            stmt.setObject(2, data);
            stmt.executeUpdate();
            f.delete();
        }
        con.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}