public static void addAction(
      String description,
      String notes,
      String context,
      String status,
      int projectid,
      String datum) {
    try {
      java.sql.Connection con = Database.getConnection();
      java.sql.Statement s =
          con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

      ResultSet statuses;
      statuses = s.executeQuery("select * from statuses");
      statuses.first();
      boolean klaar = false;
      int statusid = 0;
      while (!klaar) {
        if (statuses.getString("name").equals(status)) {
          statusid = statuses.getInt("id");
          klaar = true;
        } else {
          statuses.next();
        }
      }

      int actionid = aantalID("actions");
      int context_id = getContextID(context);

      ResultSet actions;
      actions = s.executeQuery("select * from actions");
      actions.moveToInsertRow();
      actions.updateInt("id", actionid);
      actions.updateString("description", description);
      actions.updateString("notes", notes);
      actions.updateInt("contexts_id", context_id);
      actions.updateInt("status_id", statusid);
      actions.updateInt("project_id", projectid);
      actions.updateDate("action_date", Date.valueOf(datum));
      actions.updateDate("statuschange_date", null);
      actions.updateBoolean("done", false);
      actions.insertRow();

    } catch (SQLException ex) {
      Logger.getLogger(DataLayer.class.getName()).log(Level.SEVERE, null, ex);
    }
  }
 @Override
 public void updateDate(int columnIndex, Date x) throws SQLException {
   try {
     _res.updateDate(columnIndex, x);
   } catch (SQLException e) {
     handleException(e);
   }
 }
 @Override
 public void updateDate(String columnName, Date x) throws SQLException {
   try {
     _res.updateDate(columnName, x);
   } catch (SQLException e) {
     handleException(e);
   }
 }
示例#4
0
  public int saveAlphaRecord(Properties props) throws SQLException {
    String demographic_no = props.getProperty("demographic_no");

    String sql = "SELECT * FROM formAlpha WHERE demographic_no=" + demographic_no + " AND ID=0";
    ResultSet rs = DBHandler.GetSQL(sql, true);
    rs.moveToInsertRow();
    ResultSetMetaData md = rs.getMetaData();
    for (int i = 1; i <= md.getColumnCount(); i++) {
      String name = md.getColumnName(i);
      if (name.equalsIgnoreCase("ID")) {
        rs.updateNull(name);
        continue;
      }
      String value = props.getProperty(name, null);
      if (md.getColumnTypeName(i).equalsIgnoreCase("TINY") && md.getScale(i) == 1) {
        if (value != null) {
          if (value.equalsIgnoreCase("on")) rs.updateInt(name, 1);
          else rs.updateInt(name, 0);
        } else {
          rs.updateInt(name, 0);
        }
        continue;
      }
      if (md.getColumnTypeName(i).equalsIgnoreCase("date")) {
        java.util.Date d;
        if (md.getColumnName(i).equalsIgnoreCase("formEdited")) d = UtilDateUtilities.Today();
        else d = UtilDateUtilities.StringToDate(value, "yyyy/MM/dd");
        if (d == null) rs.updateNull(name);
        else rs.updateDate(name, new Date(d.getTime()));
        continue;
      }
      if (value == null) rs.updateNull(name);
      else rs.updateString(name, value);
    }

    rs.insertRow();
    rs.close();
    int ret = 0;
    sql = "SELECT LAST_INSERT_ID()";
    rs = DBHandler.GetSQL(sql);
    if (rs.next()) ret = rs.getInt(1);
    rs.close();
    return ret;
  }
  public static void main(String[] args) throws SQLException, ParseException {
    String url = "jdbc:mysql://localhost/employees";
    String userName = "******";
    String password = "******";

    Connection connection = DriverManager.getConnection(url, userName, password);

    Statement statement =
        connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

    // ### Primary key is important otherwise com.mysql.jdbc.NotUpdatable error throws
    String query = "select emp_no, first_name, hire_date from employees where first_name='Balaji'";
    String date = "2020-12-26";
    Date futureHireDate = new Date(new SimpleDateFormat("yyyy-mm-dd").parse(date).getTime());

    ResultSet resultSet = statement.executeQuery(query);

    resultSet.next(); // move to first row

    System.out.println("Name\t Joining Date");
    System.out.println("------------------------");
    System.out.println(resultSet.getString(2) + "\t " + resultSet.getDate(3));

    // update resultset
    resultSet.updateDate(3, futureHireDate);
    // this updates the resultset to the database
    resultSet.updateRow();

    // move to first row
    resultSet.absolute(1);

    System.out.println("\n\n#########--UPDATED--#########");
    System.out.println("Name\t Joining Date");
    System.out.println("------------------------");
    System.out.println(resultSet.getString(2) + "\t " + resultSet.getDate(3));
  }
	public void updateDate(String columnName, Date x) throws SQLException {
		rs.updateDate(columnName, x);
	}
	public void updateDate(int columnIndex, Date x) throws SQLException {
		rs.updateDate(columnIndex, x);
	}
  private void testUpdateDataType() throws Exception {
    Connection conn = getConnection();
    Statement stat = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    stat.execute(
        "CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255), "
            + "DEC DECIMAL(10,2), BOO BIT, BYE TINYINT, BIN BINARY(100), "
            + "D DATE, T TIME, TS TIMESTAMP, DB DOUBLE, R REAL, L BIGINT, "
            + "O_I INT, SH SMALLINT, CL CLOB, BL BLOB)");
    ResultSet rs = stat.executeQuery("SELECT * FROM TEST");
    ResultSetMetaData meta = rs.getMetaData();
    assertEquals("java.lang.Integer", meta.getColumnClassName(1));
    assertEquals("java.lang.String", meta.getColumnClassName(2));
    assertEquals("java.math.BigDecimal", meta.getColumnClassName(3));
    assertEquals("java.lang.Boolean", meta.getColumnClassName(4));
    assertEquals("java.lang.Byte", meta.getColumnClassName(5));
    assertEquals("[B", meta.getColumnClassName(6));
    assertEquals("java.sql.Date", meta.getColumnClassName(7));
    assertEquals("java.sql.Time", meta.getColumnClassName(8));
    assertEquals("java.sql.Timestamp", meta.getColumnClassName(9));
    assertEquals("java.lang.Double", meta.getColumnClassName(10));
    assertEquals("java.lang.Float", meta.getColumnClassName(11));
    assertEquals("java.lang.Long", meta.getColumnClassName(12));
    assertEquals("java.lang.Integer", meta.getColumnClassName(13));
    assertEquals("java.lang.Short", meta.getColumnClassName(14));
    assertEquals("java.sql.Clob", meta.getColumnClassName(15));
    assertEquals("java.sql.Blob", meta.getColumnClassName(16));
    rs.moveToInsertRow();
    rs.updateInt(1, 0);
    rs.updateNull(2);
    rs.updateNull("DEC");
    // 'not set' values are set to null
    assertThrows(ErrorCode.NO_DATA_AVAILABLE, rs).cancelRowUpdates();
    rs.insertRow();

    rs.moveToInsertRow();
    rs.updateInt(1, 1);
    rs.updateString(2, null);
    rs.updateBigDecimal(3, null);
    rs.updateBoolean(4, false);
    rs.updateByte(5, (byte) 0);
    rs.updateBytes(6, null);
    rs.updateDate(7, null);
    rs.updateTime(8, null);
    rs.updateTimestamp(9, null);
    rs.updateDouble(10, 0.0);
    rs.updateFloat(11, (float) 0.0);
    rs.updateLong(12, 0L);
    rs.updateObject(13, null);
    rs.updateShort(14, (short) 0);
    rs.updateCharacterStream(15, new StringReader("test"), 0);
    rs.updateBinaryStream(16, new ByteArrayInputStream(new byte[] {(byte) 0xff, 0x00}), 0);
    rs.insertRow();

    rs.moveToInsertRow();
    rs.updateInt("ID", 2);
    rs.updateString("NAME", "+");
    rs.updateBigDecimal("DEC", new BigDecimal("1.2"));
    rs.updateBoolean("BOO", true);
    rs.updateByte("BYE", (byte) 0xff);
    rs.updateBytes("BIN", new byte[] {0x00, (byte) 0xff});
    rs.updateDate("D", Date.valueOf("2005-09-21"));
    rs.updateTime("T", Time.valueOf("21:46:28"));
    rs.updateTimestamp("TS", Timestamp.valueOf("2005-09-21 21:47:09.567890123"));
    rs.updateDouble("DB", 1.725);
    rs.updateFloat("R", (float) 2.5);
    rs.updateLong("L", Long.MAX_VALUE);
    rs.updateObject("O_I", 10);
    rs.updateShort("SH", Short.MIN_VALUE);
    // auml, ouml, uuml
    rs.updateCharacterStream("CL", new StringReader("\u00ef\u00f6\u00fc"), 0);
    rs.updateBinaryStream("BL", new ByteArrayInputStream(new byte[] {(byte) 0xab, 0x12}), 0);
    rs.insertRow();

    rs.moveToInsertRow();
    rs.updateInt("ID", 3);
    rs.updateCharacterStream("CL", new StringReader("\u00ef\u00f6\u00fc"));
    rs.updateBinaryStream("BL", new ByteArrayInputStream(new byte[] {(byte) 0xab, 0x12}));
    rs.insertRow();

    rs.moveToInsertRow();
    rs.updateInt("ID", 4);
    rs.updateCharacterStream(15, new StringReader("\u00ef\u00f6\u00fc"));
    rs.updateBinaryStream(16, new ByteArrayInputStream(new byte[] {(byte) 0xab, 0x12}));
    rs.insertRow();

    rs.moveToInsertRow();
    rs.updateInt("ID", 5);
    rs.updateClob("CL", new StringReader("\u00ef\u00f6\u00fc"));
    rs.updateBlob("BL", new ByteArrayInputStream(new byte[] {(byte) 0xab, 0x12}));
    rs.insertRow();

    rs.moveToInsertRow();
    rs.updateInt("ID", 6);
    rs.updateClob(15, new StringReader("\u00ef\u00f6\u00fc"));
    rs.updateBlob(16, new ByteArrayInputStream(new byte[] {(byte) 0xab, 0x12}));
    rs.insertRow();

    rs.moveToInsertRow();
    rs.updateInt("ID", 7);
    rs.updateNClob("CL", new StringReader("\u00ef\u00f6\u00fc"));
    Blob b = conn.createBlob();
    OutputStream out = b.setBinaryStream(1);
    out.write(new byte[] {(byte) 0xab, 0x12});
    out.close();
    rs.updateBlob("BL", b);
    rs.insertRow();

    rs.moveToInsertRow();
    rs.updateInt("ID", 8);
    rs.updateNClob(15, new StringReader("\u00ef\u00f6\u00fc"));
    rs.updateBlob(16, b);
    rs.insertRow();

    rs.moveToInsertRow();
    rs.updateInt("ID", 9);
    rs.updateNClob("CL", new StringReader("\u00ef\u00f6\u00fc"), -1);
    rs.updateBlob("BL", b);
    rs.insertRow();

    rs.moveToInsertRow();
    rs.updateInt("ID", 10);
    rs.updateNClob(15, new StringReader("\u00ef\u00f6\u00fc"), -1);
    rs.updateBlob(16, b);
    rs.insertRow();

    rs.moveToInsertRow();
    rs.updateInt("ID", 11);
    rs.updateNCharacterStream("CL", new StringReader("\u00ef\u00f6\u00fc"), -1);
    rs.updateBlob("BL", b);
    rs.insertRow();

    rs.moveToInsertRow();
    rs.updateInt("ID", 12);
    rs.updateNCharacterStream(15, new StringReader("\u00ef\u00f6\u00fc"), -1);
    rs.updateBlob(16, b);
    rs.insertRow();

    rs.moveToInsertRow();
    rs.updateInt("ID", 13);
    rs.updateNCharacterStream("CL", new StringReader("\u00ef\u00f6\u00fc"));
    rs.updateBlob("BL", b);
    rs.insertRow();

    rs.moveToInsertRow();
    rs.updateInt("ID", 14);
    rs.updateNCharacterStream(15, new StringReader("\u00ef\u00f6\u00fc"));
    rs.updateBlob(16, b);
    rs.insertRow();

    rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID NULLS FIRST");
    rs.next();
    assertTrue(rs.getInt(1) == 0);
    assertTrue(rs.getString(2) == null && rs.wasNull());
    assertTrue(rs.getBigDecimal(3) == null && rs.wasNull());
    assertTrue(!rs.getBoolean(4) && rs.wasNull());
    assertTrue(rs.getByte(5) == 0 && rs.wasNull());
    assertTrue(rs.getBytes(6) == null && rs.wasNull());
    assertTrue(rs.getDate(7) == null && rs.wasNull());
    assertTrue(rs.getTime(8) == null && rs.wasNull());
    assertTrue(rs.getTimestamp(9) == null && rs.wasNull());
    assertTrue(rs.getDouble(10) == 0.0 && rs.wasNull());
    assertTrue(rs.getFloat(11) == 0.0 && rs.wasNull());
    assertTrue(rs.getLong(12) == 0 && rs.wasNull());
    assertTrue(rs.getObject(13) == null && rs.wasNull());
    assertTrue(rs.getShort(14) == 0 && rs.wasNull());
    assertTrue(rs.getCharacterStream(15) == null && rs.wasNull());
    assertTrue(rs.getBinaryStream(16) == null && rs.wasNull());

    rs.next();
    assertTrue(rs.getInt(1) == 1);
    assertTrue(rs.getString(2) == null && rs.wasNull());
    assertTrue(rs.getBigDecimal(3) == null && rs.wasNull());
    assertTrue(!rs.getBoolean(4) && !rs.wasNull());
    assertTrue(rs.getByte(5) == 0 && !rs.wasNull());
    assertTrue(rs.getBytes(6) == null && rs.wasNull());
    assertTrue(rs.getDate(7) == null && rs.wasNull());
    assertTrue(rs.getTime(8) == null && rs.wasNull());
    assertTrue(rs.getTimestamp(9) == null && rs.wasNull());
    assertTrue(rs.getDouble(10) == 0.0 && !rs.wasNull());
    assertTrue(rs.getFloat(11) == 0.0 && !rs.wasNull());
    assertTrue(rs.getLong(12) == 0 && !rs.wasNull());
    assertTrue(rs.getObject(13) == null && rs.wasNull());
    assertTrue(rs.getShort(14) == 0 && !rs.wasNull());
    assertEquals("test", rs.getString(15));
    assertEquals(new byte[] {(byte) 0xff, 0x00}, rs.getBytes(16));

    rs.next();
    assertTrue(rs.getInt(1) == 2);
    assertEquals("+", rs.getString(2));
    assertEquals("1.20", rs.getBigDecimal(3).toString());
    assertTrue(rs.getBoolean(4));
    assertTrue((rs.getByte(5) & 0xff) == 0xff);
    assertEquals(new byte[] {0x00, (byte) 0xff}, rs.getBytes(6));
    assertEquals("2005-09-21", rs.getDate(7).toString());
    assertEquals("21:46:28", rs.getTime(8).toString());
    assertEquals("2005-09-21 21:47:09.567890123", rs.getTimestamp(9).toString());
    assertTrue(rs.getDouble(10) == 1.725);
    assertTrue(rs.getFloat(11) == (float) 2.5);
    assertTrue(rs.getLong(12) == Long.MAX_VALUE);
    assertEquals(10, ((Integer) rs.getObject(13)).intValue());
    assertTrue(rs.getShort(14) == Short.MIN_VALUE);
    // auml ouml uuml
    assertEquals("\u00ef\u00f6\u00fc", rs.getString(15));
    assertEquals(new byte[] {(byte) 0xab, 0x12}, rs.getBytes(16));

    for (int i = 3; i <= 14; i++) {
      rs.next();
      assertEquals(i, rs.getInt(1));
      assertEquals("\u00ef\u00f6\u00fc", rs.getString(15));
      assertEquals(new byte[] {(byte) 0xab, 0x12}, rs.getBytes(16));
    }
    assertFalse(rs.next());

    stat.execute("DROP TABLE TEST");
    conn.close();
  }