Java PreparedStatement.setTimestamp方法代码示例

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private void checkPreparedStatementForTestBug50348(Connection testConn, Timestamp timestamp, Time time, String expectedTimestamp, String expectedTime)
        throws SQLException {
    PreparedStatement testPstmt = testConn.prepareStatement("SELECT ?, ?");
    testPstmt.setTimestamp(1, timestamp);
    testPstmt.setTime(2, time);

    this.rs = testPstmt.executeQuery();
    this.rs.next();
    String timestampAsString = new String(this.rs.getBytes(1));
    String timeAsString = new String(this.rs.getBytes(2));
    String alert = expectedTimestamp.equals(timestampAsString) && expectedTime.equals(timeAsString) ? "" : " <-- (!)";
    System.out.printf("[PS] expected: '%s' | '%s'%n", expectedTimestamp, expectedTime);
    System.out.printf("       actual: '%s' | '%s' %s%n", timestampAsString, timeAsString, alert);
    assertEquals(expectedTimestamp, timestampAsString);
    assertEquals(expectedTime, timeAsString);
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#54095 - Unnecessary call in newSetTimestampInternal.
 *
 * This bug was fixed as a consequence of the patch for Bug#71084.
 *
 * @throws Exception
 *             if the test fails.
 */
public void testBug54095() throws Exception {
    Connection testConn = getConnectionWithProps("useLegacyDatetimeCode=false");

    Calendar testCal = Calendar.getInstance();
    java.util.Date origDate = testCal.getTime();

    PreparedStatement testPstmt = testConn.prepareStatement("SELECT ?");
    testPstmt.setTimestamp(1, new Timestamp(0), testCal);
    assertEquals("Calendar object shouldn't have changed after PreparedStatement.setTimestamp().", origDate, testCal.getTime());

    ResultSet testRs = testPstmt.executeQuery();
    testRs.next();
    assertEquals("Calendar object shouldn't have changed after PreparedStatement.executeQuery().", origDate, testCal.getTime());

    testRs.getTimestamp(1, testCal);
    assertEquals("Calendar object shouldn't have changed after ResultSet.getTimestamp().", origDate, testCal.getTime());

    testRs.close();
    testPstmt.close();
    testConn.close();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private void setBindValue(final PreparedStatement preparedStatement, final Collection<String> tableFields, final Condition condition) throws SQLException {
    int index = 1;
    if (null != condition.getFields() && !condition.getFields().isEmpty()) {
        for (Map.Entry<String, Object> entry : condition.getFields().entrySet()) {
            String lowerUnderscore = CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, entry.getKey());
            if (null != entry.getValue() && tableFields.contains(lowerUnderscore)) {
                preparedStatement.setString(index++, String.valueOf(entry.getValue()));
            }
        }
    }
    if (null != condition.getStartTime()) {
        preparedStatement.setTimestamp(index++, new Timestamp(condition.getStartTime().getTime()));
    }
    if (null != condition.getEndTime()) {
        preparedStatement.setTimestamp(index, new Timestamp(condition.getEndTime().getTime()));
    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Método responsável pela inserção de uma multimídia no banco de dados
 * @param m da multimídia a ser inserida
 * @throws Exception possíveis exceções que podem acontecer
 */
@Override
public void inserir(Multimidia m) throws Exception {
    Connection conexao = getConexao();
    
    //Arrumar as validações
    if (m.getMidia().equals("")){
        throw new Exception("A mídia não pode estar vazia!");
    }
            
    PreparedStatement pstmt;
    pstmt = conexao.prepareStatement("insert into multimidias(midia, tipo_conteudo, data, album) values(?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);

    pstmt.setBytes (1, m.getMidia());
    pstmt.setString (2, m.getTipoConteudo());
    pstmt.setTimestamp(3, new java.sql.Timestamp(m.getData().getTime()));
    pstmt.setInt (4, m.getAlbum().getId());
    
    pstmt.executeUpdate();
    
    m.setId(getId(pstmt));
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
* Método que Altera todas as multimídias  no banco de dados
* @author Jose Gilvan Jacinto Junior
* @param m as multimídias a serem alteradas
* @throws Exception possíveis exceções que podem acontecer
*/
@Override
public void alterar(Multimidia m) throws Exception { 
    Connection conexao = getConexao();
    
    PreparedStatement pstmt;
    pstmt = conexao.prepareStatement("update multimidias set midia = ?, tipo_conteudo = ?, data = ?, album = ?, where id = ?");
    
    
    pstmt.setBytes(1, m.getMidia());
    pstmt.setString(2, m.getTipoConteudo());
    pstmt.setTimestamp(3, new java.sql.Timestamp(m.getData().getTime()));
    pstmt.setInt(4, m.getAlbum().getId());
    
    
    //executa uma atualização/alteração
    pstmt.executeUpdate();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private void insertTestData() throws Exception {

//        conn.createStatement().executeUpdate("INSERT INTO \"SITE_LIST_SITE\" VALUES(23,1,'ru','RU',NULL,'\u041f\u0440\u043e\u0431\u043d\u044b\u0439 \u0441\u0430\u0439\u0442',NULL,0,'''/front_styles.css''',1,NULL,0)");
//        conn.createStatement().executeUpdate("INSERT INTO \"SITE_VIRTUAL_HOST\" VALUES(36,23,'test-host')");
//        conn.createStatement().executeUpdate("INSERT INTO \"SITE_SUPPORT_LANGUAGE\" VALUES(115,23,1,'ru_RU','ru_RU')");
//        conn.createStatement().executeUpdate("INSERT INTO \"CASH_CURRENCY\" VALUES(134,'\u0420\u0443\u0431',1,'\u0420\u0443\u0431',0,3,23,0.0)");
//        conn.createStatement().executeUpdate("INSERT INTO \"CASH_CURRENCY\" VALUES(135,'EURO',1,'EURO',0,7,23,0.0)");
        conn.createStatement().executeUpdate(
            "INSERT INTO \"CASH_CURRENCY\" VALUES(134,23)");
        conn.createStatement().executeUpdate(
            "INSERT INTO \"CASH_CURRENCY\" VALUES(135,23)");

        PreparedStatement ps = conn.prepareStatement("insert into "
            + nameTable + "(T, ID) values (?, ?)");

        ps.setTimestamp(1, testTS);
        ps.setLong(2, id);
        ps.executeUpdate();
        ps.close();

        ps = null;

        conn.commit();
    }
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public boolean saveEventNameDetails(String eventName, double latitude, double longitude, Date time) throws SQLException {
	Connection con = null;
	PreparedStatement ps = null;
	int result = 0;
	String query = "insert into event_info(eventName, latitude, longitude, time) values(?,?,?,?)";

	con = DBConnection.getConnection();
	ps = con.prepareStatement(query);

	ps.setString(1, eventName);
	ps.setDouble(2, latitude);
	ps.setDouble(3, longitude);
	ps.setTimestamp(4, new Timestamp(time.getTime()));

	result = ps.executeUpdate();

	System.out.println("Result : " + result);

	con.close();

	if (result > 0) {
		return true;
	}

	return false;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#4119 -- misbehavior in a managed environment from
 * MVCSoft JDO
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug4119() throws Exception {
    try {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4119");
        this.stmt.executeUpdate("CREATE TABLE `testBug4119` (`field1` varchar(255) NOT NULL default '', `field2` bigint(20) default NULL,"
                + "`field3` int(11) default NULL, `field4` datetime default NULL, `field5` varchar(75) default NULL,"
                + "`field6` varchar(75) default NULL, `field7` varchar(75) default NULL, `field8` datetime default NULL,"
                + " PRIMARY KEY  (`field1`(100)))");

        PreparedStatement pStmt = this.conn.prepareStatement(
                "insert into testBug4119 (field2, field3, field4, field5, field6, field7, field8, field1) values (?, ?, ?, ?, ?, ?, ?, ?)");

        pStmt.setString(1, "0");
        pStmt.setString(2, "0");
        pStmt.setTimestamp(3, new java.sql.Timestamp(System.currentTimeMillis()));
        pStmt.setString(4, "ABC");
        pStmt.setString(5, "DEF");
        pStmt.setString(6, "AA");
        pStmt.setTimestamp(7, new java.sql.Timestamp(System.currentTimeMillis()));
        pStmt.setString(8, "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA");
        pStmt.executeUpdate();
    } finally {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4119");
    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public void storeNews()
{
    PreparedStatement st;
    try
    {
        st = conn.prepareStatement("INSERT INTO news(tstamp, title_en, title_de, text_en, text_de, active) "
                + "VALUES( ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
        
        if (timeZone == null)
            st.setTimestamp(1, tstamp);
        else
            st.setTimestamp(1, tstamp, timeZone);
        
        st.setString(2, title_en);
        st.setString(3, title_de);
        st.setString(4, text_en);
        st.setString(5, text_de);
        st.setBoolean(6, active);
        
        // System.out.println(st2.toString());
        
        final int affectedRows1 = st.executeUpdate();
        if (affectedRows1 == 0)
            setError("ERROR_DB_STORE_NEWS");
        else
        {
            final ResultSet rs = st.getGeneratedKeys();
            if (rs.next())
                // Retrieve the auto generated key(s).
                uid = rs.getInt(1);
        }
        st.close();
    }
    catch (final SQLException e)
    {
        setError("ERROR_DB_STORE_NEWS_SQL");
        e.printStackTrace();
    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public String InsertEmployee(HashMap user) throws Exception {
  String id = (user.get("id") == null || user.get("id").toString().equals("")) ? UUID.randomUUID().toString() : user.get("id").toString();
  user.put("id", id);

  if (user.get("name") == null) user.put("name", "");
  if (StringUtil.isNullOrEmpty(user.get("gender"))) user.put("gender", 0);

  Connection conn = getConn();

  String sql = "INSERT INTO t_employee (id, loginname, name, age, married, gender, birthday, country, city, dept_id, position, createtime, salary, educational, school, email, remarks)"
      + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

  PreparedStatement stmt = conn.prepareStatement(sql);

  stmt.setString(1, ToString(user.get("id")));
  stmt.setString(2, ToString(user.get("loginname")));
  stmt.setString(3, ToString(user.get("name")));
  stmt.setInt(4, ToInt(user.get("age")));
  stmt.setInt(5, ToInt(user.get("married")));
  stmt.setInt(6, ToInt(user.get("gender")));
  stmt.setTimestamp(7, ToDate(user.get("birthday")));
  stmt.setString(8, ToString(user.get("country")));
  stmt.setString(9, ToString(user.get("city")));
  stmt.setString(10, ToString(user.get("dept_id")));
  stmt.setString(11, ToString(user.get("position")));
  stmt.setTimestamp(12, ToDate(user.get("createtime")));
  stmt.setString(13, ToString(user.get("salary")));
  stmt.setString(14, ToString(user.get("educational")));
  stmt.setString(15, ToString(user.get("school")));
  stmt.setString(16, ToString(user.get("email")));
  stmt.setString(17, ToString(user.get("remarks")));

  stmt.executeUpdate();

  stmt.close();
  conn.close();

  return id;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public void insertCentroide(Deque<Centroide> centroider, Integer elavsKode) throws Exception {
    createTable(CENTROIDE);
    String rel = configuration.getSchema() + "." + "centroide";
    deleteElav(rel, elavsKode);
    Connection c = Connect.getConnection();
    PreparedStatement pstmt = c.prepareStatement("INSERT INTO " + rel + " VALUES(default,?,?,?,?,?,?,?,?,?,?,?,ST_GeomFromGML(?,25832))");
    int count = 1;

    Field[] fields = Centroide.class.getDeclaredFields();

    for (Centroide item : centroider) {
        int n = 0;
        System.out.print("\rIndsætter centroider... " + count);
        System.out.flush();
        for (Field f : fields) {
            if (f.getType() == (Class.forName("java.lang.String"))) {
                pstmt.setString(++n, (String) f.get(item));
            } else if (f.getType() == (Class.forName("java.lang.Integer"))) {
                pstmt.setInt(++n, (Integer) f.get(item));
            } else if (f.getType() == (Class.forName("java.lang.Double"))) {
                pstmt.setDouble(++n, (Double) f.get(item));
            } else if (f.getType() == (Class.forName("java.sql.Timestamp"))) {
                pstmt.setTimestamp(++n, (Timestamp) f.get(item));
            } else {
                pstmt.setString(++n, (String) f.get(item));
            }
        }
        pstmt.executeUpdate();
        count++;
    }
    System.out.print("\n");
    pstmt.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包/类
@Override
public void eventToStatement(String category, LoggingEvent event, UserInfo userInfo, PreparedStatement stmt) throws SQLException {
    stmt.setString(1, userInfo == null ? null : userInfo.getName());
    stmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
    stmt.setString(3, category);
    stmt.setString(4, event.getLevel().toString());
    stmt.setString(5, event.getMessage().toString());
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Override
protected PreparedStatement updateSnapshot(Connection connection, String entityId, long stateVersion,
        int payloadVersion, String payload) throws SQLException {
    PreparedStatement ps = connection.prepareStatement("UPDATE " + getSnapshotTable()
            + " SET VERSION=?, TIMESTAMP=?, PAYLOAD_VERSION=?, PAYLOAD=? WHERE ID = ?");
    ps.setLong(1, stateVersion);
    ps.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
    ps.setInt(3, payloadVersion);
    ps.setString(4, payload);
    ps.setString(5, entityId);
    return ps;
}
 

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();
}