Java PreparedStatement.clearParameters方法代码示例

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private void addUserRoles(Connection connection, String user, String[] roles)
  throws SQLException {
if(user==null || roles==null || roles.length == 0)
    return;

      //1=USER_NAME*, ROLE_NAME*
      //6=CREATED_ON, CREATED_BY, LAST_CHANGED_ON, LAST_CHANGED_BY
      String sql = "insert into USER_ROLE values (?,?)";
      PreparedStatement pstmnt = connection.prepareStatement(sql);

      // Create first record
      for(String role : roles) {
	pstmnt.setString(1, USER1);
	pstmnt.setString(2, role);
	pstmnt.execute();
	pstmnt.clearParameters();
}

      pstmnt.close();
  }
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * @throws SQLException
 */
static void fillPath(String path, String name,
                     PreparedStatement prep) throws SQLException {

    File f = new File(path);

    if (f.isFile()) {

        // Clear all Parameters of the PreparedStatement
        prep.clearParameters();

        // Fill the first parameter: Path
        prep.setString(1, path);

        // Fill the second parameter: Name
        prep.setString(2, name);

        // Its a file: add it to the table
        prep.execute();
    } else if (f.isDirectory()) {
        if (!path.endsWith(File.separator)) {
            path += File.separator;
        }

        String[] list = f.list();

        // Process all files recursivly
        for (int i = 0; (list != null) && (i < list.length); i++) {
            fillPath(path + list[i], list[i], prep);
        }
    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/** Returns the PreparedStatement object for the input sql.
 * @param sql the sql statement which needs to be precompiled.
 * @throws SQLException if any database error occurs.
 * @return the PreparedStatement object.
 */
public PreparedStatement getPreparedStatement(String sql) throws SQLException {
    PreparedStatement pstmt = null;
    // Use the regular prepared-statement if the logging is set to warn or higher, else use a proxy
    if (log.getEffectiveLevel().isGreaterOrEqual(Level.WARN))
        pstmt = m_connection.prepareStatement(sql);
    else
        pstmt = new PreparedStatementProxyForDebugging(m_connection, sql);
    pstmt.clearBatch();
    pstmt.clearParameters();
    pstmt.clearWarnings();
    registerStatement(pstmt, null);
    return pstmt;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private void addFormEvents(Connection connection)
throws SQLException {
    //1=EVENT_NAME, DESCRIPTION, CREATED_ON, CREATED_BY, LAST_CHANGED_ON,
    //6=LAST_CHANGED_BY
    String sql = "insert into J_FORM_EVENTS values (?,?,?,?,?,  ?)";
    PreparedStatement pstmnt = connection.prepareStatement(sql);
 
    // Create first record
    pstmnt.setString(1, "PO");
    pstmnt.setString(2, "Create Purchase Order");
    pstmnt.setTimestamp(3, DateTime.addMonth(new  DateTime(), -2).timestamp() );
    pstmnt.setString(4, "user2");
    pstmnt.setTimestamp(5,(new DateTime()).timestamp());
    pstmnt.setString(6, "user3");
    pstmnt.execute();
    pstmnt.clearParameters();
    
    // Create second record
    pstmnt.setString(1, "Ship");
    pstmnt.setString(2, "Ship Material");
    pstmnt.setTimestamp(3, DateTime.addMonth(new  DateTime(), -2).timestamp() );
    pstmnt.setString(4, "user2");
    pstmnt.setTimestamp(5,null);
    pstmnt.setString(6, null);
    pstmnt.execute();
    pstmnt.clearParameters();

    // Create thrid record
    pstmnt.setString(1, "Form Group");
    pstmnt.setString(2, "Complete Group Definition");
    pstmnt.setTimestamp(3, DateTime.addMonth(new  DateTime(), -2).timestamp() );
    pstmnt.setString(4, "user2");
    pstmnt.setTimestamp(5,(new DateTime()).timestamp());
    pstmnt.setString(6, "user3");
    pstmnt.execute();
    pstmnt.clearParameters();

    pstmnt.close();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
static void populateTable(Connection con) throws SQLException {

        long      startTime = System.currentTimeMillis();
        Timestamp now       = new Timestamp(startTime);

        con.setAutoCommit(false);

        String            sql  = createInsertSQL(true, false);
        PreparedStatement prep = con.prepareStatement(sql);

        prep.clearParameters();
        prep.setString(1, "xxx");
        prep.setTimestamp(2, now);    // last_update

        for (int ii = 0; ii < DECIMAL_FIELDS_PER_DATASET; ii++) {
            prep.setDouble(ii + 3, 0.123456789);    // Wert
        }

        prep.addBatch();
        prep.setString(1, "yyy");
        prep.setTimestamp(2, now);    // last_update

        for (int ii = 0; ii < DECIMAL_FIELDS_PER_DATASET; ii++) {
            prep.setDouble(ii + 3, 0.123456789);    // Wert
        }

        prep.addBatch();

        int[] updateCounts = prep.executeBatch();

        con.setAutoCommit(true);
        prep.close();
    }
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Method declaration
 *
 *
 * @param path
 * @param name
 * @param prep
 *
 * @throws SQLException
 */
static void fillPath(String path, String name,
                     PreparedStatement prep) throws SQLException {

    File f = new File(path);

    if (f.isFile()) {

        // Clear all Parameters of the PreparedStatement
        prep.clearParameters();

        // Fill the first parameter: Path
        prep.setString(1, path);

        // Fill the second parameter: Name
        prep.setString(2, name);

        // Its a file: add it to the table
        prep.execute();
    } else if (f.isDirectory()) {
        if (!path.endsWith(File.separator)) {
            path += File.separator;
        }

        String[] list = f.list();

        // Process all files recursivly
        for (int i = 0; (list != null) && (i < list.length); i++) {
            fillPath(path + list[i], list[i], prep);
        }
    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private void addOutputTypes(Connection connection)
throws SQLException {
    //1=OUTPUT_TYPE, DESCRIPTION, DIRECT_PRINTING, CREATED_ON, CREATED_BY
    //6=LAST_CHANGED_ON, LAST_CHANGED_BY
    String sql = "insert into J_OUTPUT_TYPES values (?,?,?,?,?,  ?,?)";
    PreparedStatement pstmnt = connection.prepareStatement(sql);
    
    // Create first record
    pstmnt.setString(1, "PDF");
    pstmnt.setString(2, "Portable Document Format");
    pstmnt.setBoolean(3, false);
    pstmnt.setTimestamp(4, DateTime.addMonth(new  DateTime(), -2).timestamp() );
    pstmnt.setString(5, "user2");
    pstmnt.setTimestamp(6,(new DateTime()).timestamp());
    pstmnt.setString(7, "user3");
    pstmnt.execute();
    pstmnt.clearParameters();
    
    // Create second record
    pstmnt.setString(1, "Intermec 3400D");
    pstmnt.setString(2, "Intermec 3400D Label Printer");
    pstmnt.setBoolean(3, true);
    pstmnt.setTimestamp(4, DateTime.addMonth(new  DateTime(), -2).timestamp() );
    pstmnt.setString(5, "user2");
    pstmnt.setTimestamp(6,null);
    pstmnt.setString(7, null);
    pstmnt.execute();
    pstmnt.close();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private void addOutputCommands(Connection connection)
throws SQLException {
    //1=OUTPUT_COMMAND_ID, OUTPUT_TYPE, SEQUENCE_NO, OS_PATTERN, COMMAND_LINE
    //6=CREATED_ON, CREATED_BY, LAST_CHANGED_ON, LAST_CHANGED_BY
    String sql = "insert into J_OUTPUT_COMMANDS values (?,?,?,?,?,  ?,?,?,?)";
    PreparedStatement pstmnt = connection.prepareStatement(sql);
    
    // Create first record
    pstmnt.setLong(1, 1000000);
    pstmnt.setString(2, "PDF");
    pstmnt.setLong(3, 1);
    pstmnt.setString(4, "Windows.*");
    //pstmnt.setString(5, "\"c:\\Program Files\\Adobe\\Acrobat 5.0\\Acrobat\\Acrobat.exe\" /t {0} {1} {2} {3}");
    pstmnt.setString(5, "\"C:\\Program Files\\Ghostgum\\gsview\\gsprint\" -printer \"{1}\" {2} {3} {0}");
    pstmnt.setTimestamp(6, DateTime.addMonth(new  DateTime(), -2).timestamp() );
    pstmnt.setString(7, "user2");
    pstmnt.setTimestamp(8,(new DateTime()).timestamp());
    pstmnt.setString(9, "user3");
    pstmnt.execute();
    pstmnt.clearParameters();
    
    // Create second record
    pstmnt.setLong(1, 1000001);
    pstmnt.setString(2, "PDF");
    pstmnt.setLong(3, 2);
    pstmnt.setString(4, ".*");
    pstmnt.setString(5, "/usr/gs/gsview \"{0}\" -p\"{1}\" {2} {3}");
    pstmnt.setTimestamp(6, DateTime.addMonth(new  DateTime(), -2).timestamp() );
    pstmnt.setString(7, "user2");
    pstmnt.setTimestamp(8,null);
    pstmnt.setString(9, null);
    pstmnt.execute();
    pstmnt.clearParameters();


    // Print on unix
    //pstmnt.setString(5, "lp -d\"{1}\" {2} {3} \"{0}\" ");

    pstmnt.close();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private void addFormGroups(Connection connection)
throws SQLException {
    String sql = "insert into J_FORM_GROUPS values (?,?,?,?,?,?,?)";
    PreparedStatement pstmnt = connection.prepareStatement(sql);
    
    // Create first record
    pstmnt.setString(1, FORM_NAME);
    pstmnt.setString(2, FORM_NAME + " Description");
    pstmnt.setString(3, FormPrintFactory.ENGINE_TYPE_ITEXT);
    pstmnt.setTimestamp(4, DateTime.addMonth(new  DateTime(), -2).timestamp() );
    pstmnt.setString(5, "user2");
    pstmnt.setTimestamp(6,(new DateTime()).timestamp());
    pstmnt.setString(7, "user3");
    pstmnt.execute();
    pstmnt.clearParameters();
    
    // Create second record
    pstmnt.setString(1, FORM_NAME2);
    pstmnt.setString(2, FORM_NAME2 + " Description");
    pstmnt.setString(3, FormPrintFactory.ENGINE_TYPE_VELOCITY);
    pstmnt.setTimestamp(4, DateTime.addMonth(new  DateTime(), -2).timestamp() );
    pstmnt.setString(5, "user2");
    pstmnt.setTimestamp(6,null);
    pstmnt.setString(7, null);
    pstmnt.execute();
    pstmnt.clearParameters();

    // Create thrid record
    pstmnt.setString(1, FORM_NAME3);
    pstmnt.setString(2, FORM_NAME3 + " Description");
    pstmnt.setString(3, FormPrintFactory.ENGINE_TYPE_ITEXT);
    pstmnt.setTimestamp(4, DateTime.addMonth(new  DateTime(), -2).timestamp() );
    pstmnt.setString(5, "user2");
    pstmnt.setTimestamp(6,(new DateTime()).timestamp());
    pstmnt.setString(7, "user3");
    pstmnt.execute();
    pstmnt.clearParameters();
    
    pstmnt.close();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private void addFormTemplates(Connection connection)
throws Exception {
    //1=FORM_ID, TEMPLATE_DATA, LAYOUT_DATA
    String sql = "insert into J_FORM_TEMPLATES values (?,?,?)";
    PreparedStatement pstmnt = connection.prepareStatement(sql);
    
    // Create first record 
    pstmnt.setLong(1, 1000000);
    URL u = URLHelper.newExtendedURL(PDF_TEMPLATE);
    InputStream is = u.openStream();
    pstmnt.setBinaryStream(2,is,is.available());
    u = URLHelper.newExtendedURL(PDF_TEMPLATE+".csv");
    is = u.openStream();
    pstmnt.setBinaryStream(3,is,is.available());
    pstmnt.execute();
    pstmnt.clearParameters();

    // Create second record 
    pstmnt.setLong(1, 1000001);
    u = URLHelper.newExtendedURL(VELOCITY_TEMPLATE);
    is = u.openStream();
    pstmnt.setBinaryStream(2,is,is.available());
    pstmnt.setObject(3,null);
    pstmnt.execute();
    pstmnt.clearParameters();

    // Create third record 
    pstmnt.setLong(1, 1000002);
    u = URLHelper.newExtendedURL(PDF_TEMPLATE3);
    is = u.openStream();
    pstmnt.setBinaryStream(2,is,is.available());
    u = URLHelper.newExtendedURL(PDF_TEMPLATE3+".csv");
    is = u.openStream();
    pstmnt.setBinaryStream(3,is,is.available());
    pstmnt.execute();
    pstmnt.clearParameters();

    pstmnt.close();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Test
public void testPopulateData() throws Exception {
    int count = 100000;
    int actual = testCheckData();
    if (actual>=count) {
        System.out.println("Test tables has "+actual+" rows of data. No need to populate.");
        return;
    }

    datasource.setJdbcInterceptors(ResetAbandonedTimer.class.getName());
    String insert = "insert into test values (?,?,?,?,?)";
    this.datasource.setRemoveAbandoned(false);
    Connection con = datasource.getConnection();

    boolean commit = con.getAutoCommit();
    con.setAutoCommit(false);
    if (recreate) {
        Statement st = con.createStatement();
        try {
            st.execute("drop table test");
        }catch (Exception ignore) {}
        st.execute("create table test(id int not null, val1 varchar(255), val2 varchar(255), val3 varchar(255), val4 varchar(255))");
        st.close();
    }


    PreparedStatement ps = con.prepareStatement(insert);
    ps.setQueryTimeout(0);
    for (int i=actual; i<count; i++) {
        ps.setInt(1,i);
        String s = getRandom();
        ps.setString(2, s);
        ps.setString(3, s);
        ps.setString(4, s);
        ps.setString(5, s);
        ps.addBatch();
        ps.clearParameters();
        if ((i+1) % 1000 == 0) {
            System.out.print(".");
        }
        if ((i+1) % 10000 == 0) {
            System.out.print("\n"+(i+1));
            ps.executeBatch();
            ps.close();
            con.commit();
            ps = con.prepareStatement(insert);
            ps.setQueryTimeout(0);
        }

    }
    ps.close();
    con.setAutoCommit(commit);
    con.close();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private void addFormUsages(Connection connection)
throws SQLException {
    //1=FORM_NAME, EVENT_NAME, FORM_ALTERNATE, COPIES, CREATED_ON
    //6=CREATED_BY, LAST_CHANGED_ON, LAST_CHANGED_BY
    String sql = "insert into J_FORM_USAGES values (?,?,?,?,?,  ?,?,?)";
    PreparedStatement pstmnt = connection.prepareStatement(sql);
 
    // Create first record
    pstmnt.setString(1, FORM_NAME);
    pstmnt.setString(2, "PO");
    pstmnt.setString(3, null);
    pstmnt.setLong(4, 1);
    pstmnt.setTimestamp(5, DateTime.addMonth(new  DateTime(), -2).timestamp() );
    pstmnt.setString(6, "user2");
    pstmnt.setTimestamp(7,(new DateTime()).timestamp());
    pstmnt.setString(8, "user3");
    pstmnt.execute();
    pstmnt.clearParameters();
    
    // Create second record
    pstmnt.setString(1, FORM_NAME2);
    pstmnt.setString(2, "Ship");
    pstmnt.setString(3, null);
    pstmnt.setLong(4, 2);
    pstmnt.setTimestamp(5, DateTime.addMonth(new  DateTime(), -2).timestamp() );
    pstmnt.setString(6, "user2");
    pstmnt.setTimestamp(7,null);
    pstmnt.setString(8, null);
    pstmnt.execute();
    pstmnt.clearParameters();

    // Create third record
    pstmnt.setString(1, FORM_NAME3);
    pstmnt.setString(2, "Form Group");
    pstmnt.setString(3, null);
    pstmnt.setLong(4, 1);
    pstmnt.setTimestamp(5, DateTime.addMonth(new  DateTime(), -2).timestamp() );
    pstmnt.setString(6, "user2");
    pstmnt.setTimestamp(7,(new DateTime()).timestamp());
    pstmnt.setString(8, "user3");
    pstmnt.execute();
    pstmnt.clearParameters();
    
    pstmnt.close();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private void addPrinterDefinitions(Connection connection)
throws SQLException {
    //1=PRINTER_ID, DESCRIPTION, SITE_CODE, LOCATION_CODE, REMOTE_PRINTER,
    //6=REAL_PRINTER_NAME, PRINTER_OPTION1, PRINTER_OPTION2, OUTPUT_TYPE, CREATED_ON
    //11=CREATED_BY, LAST_CHANGED_ON, LAST_CHANGED_BY
    String sql = "insert into J_PRINTER_DEFINITIONS values (?,?,?,?,?,  ?,?,?,?,?,  ?,?,?)";
    PreparedStatement pstmnt = connection.prepareStatement(sql);
 
    // Create first record
    pstmnt.setString(1, VAILD_PDF_PRINTER);
    pstmnt.setString(2, "Main Laser Printer");
    pstmnt.setString(3, "UTC");
    pstmnt.setString(4, "Copier Room");
    pstmnt.setBoolean(5, false);
    pstmnt.setString(6, REAL_PDF_PRINTER);
    pstmnt.setString(7, "-color");
    pstmnt.setString(8, null);
    pstmnt.setString(9, "PDF");
    pstmnt.setTimestamp(10, DateTime.addMonth(new  DateTime(), -2).timestamp() );
    pstmnt.setString(11, "user2");
    pstmnt.setTimestamp(12,(new DateTime()).timestamp());
    pstmnt.setString(13, "user3");
    pstmnt.execute();
    pstmnt.clearParameters();
    
    // Create second record
    pstmnt.setString(1, VAILD_LABEL_PRINTER);
    pstmnt.setString(2, "Intermec 3400D Label Printer");
    pstmnt.setString(3, "UTC");
    pstmnt.setString(4, "Main Hall");
    pstmnt.setBoolean(5, false);
    pstmnt.setString(6, "\\\\Hematite\\barcode1");
    pstmnt.setString(7, null);
    pstmnt.setString(8, null);
    pstmnt.setString(9, "Intermec 3400D");
    pstmnt.setTimestamp(10, DateTime.addMonth(new  DateTime(), -2).timestamp() );
    pstmnt.setString(11, "user2");
    pstmnt.setTimestamp(12, null);
    pstmnt.setString(13, null);
    pstmnt.execute();
    pstmnt.close();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private void addUsers(Connection connection)
throws SQLException {
    //1=USER_NAME*, FIRST_NAME*, LAST_NAME*, PASSWORD*, STATUS*,
    //6=E_MAIL_ADDRESS, CREATED_DATETIME, CREATED_USER, LAST_UPDATE_DATETIME, LAST_UPDATE_USER,
    //11=COMMENTS
    String sql = "insert into USERS values (?,?,?,?,?,  ?,?,?,?,?, ?)";
    PreparedStatement pstmnt = connection.prepareStatement(sql);

    // Create first record
    pstmnt.setString(1, USER1);
    pstmnt.setString(2, USER1);
    pstmnt.setString(3, USER1);
    pstmnt.setString(4, PASSWORD);
    pstmnt.setString(5, "A");

    pstmnt.setString(6, EMAIL);
    pstmnt.setTimestamp(7, DateTime.addMonth(new  DateTime(), -2).timestamp());
    pstmnt.setString(8, "user2");
    pstmnt.setTimestamp(9,(new DateTime()).timestamp());
    pstmnt.setString(10, "user3");

    pstmnt.setString(11, "No Comment!");
    pstmnt.execute();
    addUserRoles(connection, USER1, USER1_ROLES);
    pstmnt.clearParameters();

    // Create second record
    pstmnt.setString(1, USER2);
    pstmnt.setString(2, USER2);
    pstmnt.setString(3, USER2);
    pstmnt.setString(4, PASSWORD);
    pstmnt.setString(5, "A");

    pstmnt.setString(6, EMAIL);
    pstmnt.setTimestamp(7, DateTime.addMonth(new  DateTime(), -2).timestamp());
    pstmnt.setString(8, "user2");
    pstmnt.setTimestamp(9,null);
    pstmnt.setString(10, null);

    pstmnt.setString(11, "No Comment!");
    pstmnt.execute();
    addUserRoles(connection, USER2, USER2_ROLES);
    pstmnt.close();
}