Java Code Examples for java.sql.PreparedStatement.setDate()

The following are Jave code examples for showing how to use setDate() of the java.sql.PreparedStatement class. You can vote up the examples you like. Your votes will be used in our system to get more good examples.
Example 1
Project: OpenDiabetes   File: TestDatetimeSimple.java   Source Code and License Vote up 7 votes
public void testDateRangeCheck() throws SQLException {

        Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:db", "sa",
            "");
        Statement stmt = c.createStatement();

        stmt.execute("create table testdate (d date)");
        stmt.executeUpdate("insert into testdate values DATE'2017-01-19'");

        PreparedStatement pstmt =
            c.prepareStatement("insert into testdate values ?");

        try {
            pstmt.setDate(1, new Date(25000, 1, 1));
            pstmt.executeUpdate();
            fail("invalid date beyond 9999CE accepted");
        } catch (SQLException e) {}
    }
 
Example 2
Project: redesocial   File: ArtigoDAO.java   Source Code and License Vote up 7 votes
/**
 * Método que Altera um Artigo já cadastrado no banco de dados
 * @author Eduardo Oliveira Silva
 * @param a artigo a ser alterado
 * @throws Exception possíveis exceções que podem acontecer
 */
@Override
public void alterar(Artigo a) throws Exception {
    Connection conexao = getConexao();
    
    PreparedStatement pstmt;
    pstmt = conexao.prepareStatement("update artigos set  idioma = ?, revista = ?, issn = ?, data = ?, area_conhecimento = ?, titulo = ?, resumo = ?, url = ?, artigo = ?, categoria = ? where id = ?");
    
    pstmt.setString(1, a.getIdioma());
    pstmt.setString(2, a.getRevista());
    pstmt.setString(3, a.getISSN());
    pstmt.setDate(4, new java.sql.Date(a.getData().getTime()));
    pstmt.setString(5, a.getAreaConhecimento());
    pstmt.setString(6, a.getTitulo());
    pstmt.setString(7, a.getResumo());
    pstmt.setString(8, a.getURL());
    pstmt.setBytes(9, a.getArtigo());
    pstmt.setInt(10, a.getCategoria().getId());
    pstmt.setInt(11, a.getId());
    
    //executa uma atualização/alteração
    pstmt.executeUpdate();
    
}
 
Example 3
Project: AdvancedDataProfilingSeminar   File: PostgreSQLDataAccessObject.java   Source Code and License Vote up 6 votes
@Override
public PreparedStatement insertValuesIntoStatement(PreparedStatement statement, String[] values, String[] valueTypes, int offset) throws NumberFormatException, SQLException {
	for (int i = 0; i < values.length; i++) {
		String valueType = valueTypes[i + offset].toLowerCase();
		
		if ((values[i] == null) || (values[i].equals("")))  {
			if (valueType.contains("long") || valueType.contains("big") || valueType.contains("int8"))
				statement.setNull(i + 1, java.sql.Types.BIGINT);
			else if (valueType.contains("int") || valueType.contains("serial"))
				statement.setNull(i + 1, java.sql.Types.INTEGER);
			else if (valueType.contains("float") || valueType.contains("numeric") || valueType.contains("decimal") || valueType.contains("real") || valueType.contains("precision"))
				statement.setNull(i + 1, java.sql.Types.FLOAT);
			else if (valueType.contains("bool"))
				statement.setNull(i + 1, java.sql.Types.BOOLEAN);
			else if (valueType.contains("date"))
				statement.setNull(i + 1, java.sql.Types.DATE);
			else 
				statement.setNull(i + 1, java.sql.Types.VARCHAR);
		}
		else {
			if (valueType.contains("long") || valueType.contains("big") || valueType.contains("int8"))
				statement.setLong(i + 1, Long.valueOf(values[i]).longValue());
			else if (valueType.contains("int") || valueType.contains("serial"))
				statement.setInt(i + 1, Integer.valueOf(values[i]).intValue());
			else if (valueType.contains("float") || valueType.contains("numeric") || valueType.contains("decimal") || valueType.contains("real") || valueType.contains("precision"))
				statement.setFloat(i + 1, Float.valueOf(values[i]).shortValue());
			else if (valueType.contains("bool"))
				statement.setBoolean(i + 1, Boolean.valueOf(values[i]).booleanValue());
			else if (valueType.contains("date"))
				statement.setDate(i + 1, Date.valueOf(values[i]));
			else 
				statement.setString(i + 1, values[i]);
		}
	}
	return statement;
}
 
Example 4
Project: redesocial   File: GrupoDAO.java   Source Code and License Vote up 6 votes
/**
* Método responsável pela inserção de um objeto no banco de dados na tabela grupos
* @param g objeto com os dados de grupo já preenchido
* @throws Exception
*/
@Override
public void inserir(Grupo g) throws Exception {
    Connection conexao = getConexao();        

    PreparedStatement pstmt;
    pstmt = conexao.prepareStatement("insert into grupos (nome, data_criacao, descricao, privacidade, tipo) values(?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);

    pstmt.setString(1, g.getNome());
    pstmt.setDate(2, new java.sql.Date(g.getDataCriacao().getTime()));
    pstmt.setString(3,g.getDescricao());
    pstmt.setInt(4, g.getPrivacidade());
    pstmt.setString(5, g.getTipo());        
    
    pstmt.executeUpdate();

    g.setId(getId(pstmt));
}
 
Example 5
Project: tcp   File: SqlUpdates.java   Source Code and License Vote up 5 votes
public static boolean updatePeriodo(Periodo objeto, String usuarioResponsable){
 	try {             
		Connection conect=ConnectionConfiguration.conectar();
		String	query = "update periodo set ";
		PreparedStatement update =null;

		
		if(objeto.getNombre()!=null)	      		query+= "nombre= ?";
		if(objeto.getDescripcion()!=null)			query+= ", descripcion= ?";
		if(objeto.getFechaInicio()!=null)			query+= ", fecha_inicio= ?";
		if(objeto.getFechaFin()!=null)				query+= ", fecha_fin= ?";
													query += ", usuario_responsable = ? ";
			
		query+=" where id = ?";

		int cantCampos =0;
		update = conect.prepareStatement(query);
		if (objeto.getNombre()!=null)  				{    cantCampos++;update.setString (cantCampos, objeto.getNombre());}
		if (objeto.getDescripcion()!=null)			{    cantCampos++;update.setString (cantCampos, objeto.getDescripcion());}
		if (objeto.getFechaInicio()!=null)			{    cantCampos++;update.setDate (cantCampos, objeto.getFechaInicio());}
		if (objeto.getFechaFin()!=null)				{    cantCampos++;update.setDate (cantCampos, objeto.getFechaFin());}

			cantCampos++;
			update.setString (cantCampos, usuarioResponsable);
		
            cantCampos++;
            update.setInt    (cantCampos , objeto.getId());

            update.execute();
		    conect.close();
		    return true;
	} catch (SQLException e) {e.printStackTrace(); return false;}
}
 
Example 6
Project: dev-courses   File: TestKarl.java   Source Code and License Vote up 5 votes
/**
 * @param p_connection
 * @throws SQLException
 */
private static void doUpdateInsertDeleteWaehler(Connection p_connection)
throws SQLException {

    System.out.println("UPDATE WAEHLER START ...");

    PreparedStatement p = p_connection.prepareStatement(
        "UPDATE WAEHLER SET AUSTRITTSDATUM=? WHERE NAME=?");

    p.setDate(1, null);
    p.setString(2, "Muster1");
    p.execute();
    p.close();
    System.out.println("END UPDATE WAEHLER");
    System.out.println("INSERT INTO WAEHLER START ...");

    p = p_connection.prepareStatement(
        "INSERT INTO WAEHLER (NAME, AUSTRITTSDATUM) VALUES (?,?)");

    Calendar cal = GregorianCalendar.getInstance();

    p.setString(1, "Muster3");
    p.setDate(2, new Date(cal.getTimeInMillis()), cal);
    p.execute();
    p.close();
    System.out.println("END INSERT INTO WAEHLER");
    System.out.println("DELETE FROM WAEHLER START ...");

    p = p_connection.prepareStatement(
        "DELETE FROM WAEHLER WHERE NAME = ?");

    p.setString(1, "Muster2");
    p.execute();
    p.close();
    System.out.println("END DELETE FROM WAEHLER");
}
 
Example 7
Project: Homework   File: DAOEx02.java   Source Code and License Vote up 5 votes
@Override
public int update(Pet pet)
{
    PreparedStatement psmt = null;
    int result = 0;

    try
    {
        String sql = "UPDATE pet SET master_username = ?, name = ?, type_id = ?, health = ?, love = ?, adopt_time = ?, status = ? WHERE id = ?";
        psmt = conn.prepareStatement(sql);
        psmt.setString(1, pet.getMasterId());
        psmt.setString(2, pet.getName());
        psmt.setInt(3, pet.getPetType().getId());
        psmt.setInt(4, pet.getHealth());
        psmt.setInt(5, pet.getLove());
        psmt.setDate(6, pet.getAdoptTime());
        psmt.setString(7, pet.getStatus());
        psmt.setInt(8, pet.getId());

        result = psmt.executeUpdate();
    }
    catch(SQLException e)
    {
        LOGGER.catching(e);
    }
    finally
    {
        DBHelper.closeStatement(psmt);
    }

    return result;
}
 
Example 8
Project: Homework   File: DAOEx02.java   Source Code and License Vote up 5 votes
@Override
public int save(Pet pet)
{
    PreparedStatement psmt = null;
    int result = 0;

    try
    {
        String sql = "INSERT INTO pet VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
        psmt = conn.prepareStatement(sql);
        psmt.setInt(1, pet.getId());
        psmt.setString(2, pet.getMasterId());
        psmt.setString(3, pet.getName());
        psmt.setInt(4, pet.getPetType().getId());
        psmt.setInt(5, pet.getHealth());
        psmt.setInt(6, pet.getLove());
        psmt.setDate(7, pet.getAdoptTime());
        psmt.setString(8, pet.getStatus());

        result = psmt.executeUpdate();

    }
    catch(SQLException e)
    {
        LOGGER.catching(e);
    }
    finally
    {
        DBHelper.closeStatement(psmt);
    }

    return result;
}
 
Example 9
Project: ralasafe   File: Query.java   Source Code and License Vote up 5 votes
private ResultSet getResultSet(User user, Map context,
		PreparedStatement pstmt) throws SQLException {
	int valueCount = values.size();
	for (int i = 0; i < valueCount; i++) {
		Value value = (Value) values.get(i);
		Object setValue = value.getValue(user, context);
		if (value.isBehindLike()) {
			if (setValue instanceof java.util.Date) {
				SimpleDateFormat format = new SimpleDateFormat(
						"yyyy-MM-dd HH:mm");
				setValue = format.format((java.util.Date) setValue);
			}
			pstmt.setObject(i + 1, "%" + setValue + "%");
		} else {
			if (setValue instanceof java.util.Date) {
				java.util.Date utilDate = (java.util.Date) setValue;
				java.sql.Date sqlDate = new java.sql.Date(utilDate
						.getTime());
				pstmt.setDate(i + 1, sqlDate);
			} else {
				pstmt.setObject(i + 1, setValue);
			}
		}
	}
	// long start=System.currentTimeMillis();
	ResultSet executeQuery = pstmt.executeQuery();
	// long end=System.currentTimeMillis();
	// System.out.println( "Oracle Query Cost Time(ms):" + (end-start) );

	return executeQuery;
}
 
Example 10
Project: spr   File: SqlInserts.java   Source Code and License Vote up 5 votes
public static void insertInstitucion(Institucion institucion, String usuarioResponsable) {
	try {
		Connection conn=ConnectionConfiguration.conectar();
	   	
		String query = "insert into Institucion(id serial, nombre, descripcion, sigla, orden, nivel_id, entidad_id, unidad_jerarquica_id, unidad_responsable_id, version, borrado, abrev, base_legal, mision, vision, diagnostico, ruc, anho,fecha_creacion, politica, objetivo, nro_fila, usuario_responsable)"
		+ " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		
		PreparedStatement insert = conn.prepareStatement(query);
		
		insert.setInt (1, institucion.getId());
		insert.setString (2, institucion.getNombre());
		insert.setString (3, institucion.getDescripcion());
		insert.setString (4, institucion.getSigla());
		insert.setInt (5, institucion.getOrden());
		insert.setInt (6, institucion.getNivelId());
		insert.setInt (7, institucion.getEntidadId());
		insert.setInt (8, institucion.getUnidadJerarquicaId());
		insert.setInt (9, institucion.getUnidadResponsableId());
		insert.setInt (10, institucion.getVersion());
		insert.setBoolean (11, institucion.isBorrado());
		insert.setString (12, institucion.getAbrev());
		insert.setString (13, institucion.getBaseLegal());
		insert.setString (14, institucion.getMision());
		insert.setString (15, institucion.getVision());
		insert.setString (16, institucion.getDiagnostico());
		insert.setString (17, institucion.getRuc());
		insert.setInt (18, institucion.getAnho());
		insert.setDate (19, (java.sql.Date) institucion.getFechaCreacion());
		insert.setString (20, institucion.getPolitica());
		insert.setString (21, institucion.getObjetivo());
		insert.setInt (22, institucion.getNroFila());
		insert.setString (23, usuarioResponsable);
		
		insert.execute();
		   
		conn.close();
	} catch (SQLException e) {e.printStackTrace();}
	
}
 
Example 11
Project: tcp   File: SqlInserts.java   Source Code and License Vote up 5 votes
public static boolean insertAvance(Avance avance, String usuarioResponsable) throws ParseException{
	try {
		Connection conn=ConnectionConfiguration.conectar();
	   	
		String query = " insert into avance (departamento_id, distrito_avance, justificacion,cantidad,fecha_entrega,actividad_id,version, usuario_responsable)"
	+ " values (?, ?, ?, ?, ?, ?, ?, ?)";
		
		PreparedStatement insert = conn.prepareStatement(query);
		
		String inicio = avance.getFechaEntrega();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		java.util.Date date1 = sdf.parse(inicio);
		java.sql.Date sqlStart = new java.sql.Date(date1.getTime());
		
		insert.setInt (1, avance.getDepartamentoId());
		insert.setInt (2, avance.getDistritoAvance());
		insert.setString (3, avance.getJustificacion());
		insert.setDouble(4, avance.getCantidad());
		insert.setDate (5, sqlStart); 
		insert.setInt (6, avance.getActividadId());
		insert.setInt (7, avance.getVersion());
		insert.setString (8, usuarioResponsable);
		
		insert.execute();
		   
		conn.close();
		return true;
	} catch (SQLException e) {e.printStackTrace(); return false;}
	
}
 
Example 12
Project: BibliotecaPS   File: StatementRegressionTest.java   Source Code and License Vote up 5 votes
/**
 * Tests that binary dates/times are encoded/decoded correctly.
 * 
 * @throws Exception
 *             if the test fails.
 * 
 * @deprecated because we need to use this particular constructor for the
 *             date class, as Calendar-constructed dates don't pass the
 *             .equals() test :(
 */
@Deprecated
public void testServerPrepStmtAndDate() throws Exception {
    createTable("testServerPrepStmtAndDate",
            "(`P_ID` int(10) NOT NULL default '0', `R_Date` date default NULL, UNIQUE KEY `P_ID` (`P_ID`), KEY `R_Date` (`R_Date`))");
    Date dt = new java.sql.Date(102, 1, 2); // Note, this represents the date 2002-02-02

    PreparedStatement pStmt2 = this.conn.prepareStatement("INSERT INTO testServerPrepStmtAndDate (P_ID, R_Date) VALUES (171576, ?)");
    pStmt2.setDate(1, dt);
    pStmt2.executeUpdate();
    pStmt2.close();

    this.rs = this.stmt.executeQuery("SELECT R_Date FROM testServerPrepStmtAndDate");
    this.rs.next();

    System.out.println("Date that was stored (as String) " + this.rs.getString(1)); // comes back as 2002-02-02

    PreparedStatement pStmt = this.conn.prepareStatement("Select P_ID,R_Date from testServerPrepStmtAndDate Where R_Date = ?   and P_ID = 171576");
    pStmt.setDate(1, dt);

    this.rs = pStmt.executeQuery();

    assertTrue(this.rs.next());

    assertEquals("171576", this.rs.getString(1));

    assertEquals(dt, this.rs.getDate(2));
}
 
Example 13
Project: ProyectoPacientes   File: StatementRegressionTest.java   Source Code and License Vote up 5 votes
/**
 * Tests that binary dates/times are encoded/decoded correctly.
 * 
 * @throws Exception
 *             if the test fails.
 * 
 * @deprecated because we need to use this particular constructor for the
 *             date class, as Calendar-constructed dates don't pass the
 *             .equals() test :(
 */
@Deprecated
public void testServerPrepStmtAndDate() throws Exception {
    createTable("testServerPrepStmtAndDate",
            "(`P_ID` int(10) NOT NULL default '0', `R_Date` date default NULL, UNIQUE KEY `P_ID` (`P_ID`), KEY `R_Date` (`R_Date`))");
    Date dt = new java.sql.Date(102, 1, 2); // Note, this represents the date 2002-02-02

    PreparedStatement pStmt2 = this.conn.prepareStatement("INSERT INTO testServerPrepStmtAndDate (P_ID, R_Date) VALUES (171576, ?)");
    pStmt2.setDate(1, dt);
    pStmt2.executeUpdate();
    pStmt2.close();

    this.rs = this.stmt.executeQuery("SELECT R_Date FROM testServerPrepStmtAndDate");
    this.rs.next();

    System.out.println("Date that was stored (as String) " + this.rs.getString(1)); // comes back as 2002-02-02

    PreparedStatement pStmt = this.conn.prepareStatement("Select P_ID,R_Date from testServerPrepStmtAndDate Where R_Date = ?   and P_ID = 171576");
    pStmt.setDate(1, dt);

    this.rs = pStmt.executeQuery();

    assertTrue(this.rs.next());

    assertEquals("171576", this.rs.getString(1));

    assertEquals(dt, this.rs.getDate(2));
}
 
Example 14
Project: Homework   File: DAOEx03.java   Source Code and License Vote up 5 votes
@Override
public int save(Pet pet)
{
    LOGGER.info("开始查询数据库...");
    PreparedStatement stat = null;
    ResultSet rs = null;
    int result = 0;
    try
    {
        String sql = "INSERT INTO tbl_pet VALUES(?, ?, ?, ?, ?, ?, ?, ?);";
        stat = conn.prepareStatement(sql);

        stat.setInt(1, pet.getId());
        stat.setInt(2, pet.getMasterID());
        stat.setString(3, pet.getName());
        stat.setInt(4, pet.getTypeID());
        stat.setInt(5, pet.getHealth());
        stat.setInt(6, pet.getLove());
        stat.setDate(7, pet.getAdoptTime());
        stat.setString(8, pet.getStatus());

        result = stat.executeUpdate();
        LOGGER.debug("插入{}行记录!", result);
    }
    catch(SQLException e)
    {
        LOGGER.catching(e);
    }
    finally
    {
        DBHelper.closeResultSet(rs);
        DBHelper.closeStatement(stat);
    }
    
    return result;
}
 
Example 15
Project: jigsaw-payment   File: JdbcProtobufTemplate.java   Source Code and License Vote up 5 votes
/**
 * set preparedstatement params
 * 
 * @param ps
 * @param args
 * @return
 * @throws SQLException
 */
private void populate(PreparedStatement ps, List<?> args)
		throws SQLException {
	for (int i = 0; i < args.size(); i++) {
		Object o = args.get(i);
		if (o instanceof Integer) {
			ps.setInt(i + 1, (int) o);
		} else if (o instanceof Long) {
			ps.setLong(i + 1, (long) o);
		} else if (o instanceof String) {
			ps.setString(i + 1, (String) o);
		} else if (o instanceof Date) {
			ps.setDate(i + 1, (Date) o);
		} else if (o instanceof Float) {
			ps.setFloat(i + 1, (Float) o);
		} else if (o instanceof Double) {
			ps.setDouble(i + 1, (Double) o);
		} else if (o instanceof Date) {
			ps.setDate(i + 1, (Date) o);
		} else if (o instanceof Timestamp) {
			ps.setTimestamp(i + 1, (Timestamp) o);
		} else if (o instanceof Descriptors.EnumValueDescriptor) {
			ps.setInt(i + 1,
					((Descriptors.EnumValueDescriptor) o).getNumber());
		} else if(o instanceof Boolean){
			ps.setBoolean(i+1, (Boolean)o);
		} else {
			ps.setObject(i+1, o);
		}
	}
}
 
Example 16
Project: iDataBaseConnection   File: SQLPreparedParamUtil.java   Source Code and License Vote up 4 votes
private static void setValue(PreparedStatement preStmt,int index,Value value)
{
	try 
	{
		if(value.isStringValue())
		{
			preStmt.setString(index, value.getString_value());
		}
		else if(value.isIntValue())
		{
			preStmt.setInt(index, value.getInt_value());
		}
		else if(value.isDoubleValue())
		{
			preStmt.setDouble(index, value.getDouble_value());
		}
		else if(value.isBooleanValue())
		{
			preStmt.setBoolean(index, value.getBoolean_value());
		}
		else if(value.isBlobValue())
		{
			preStmt.setBlob(index, value.getBlob_value());
		}
		else if(value.isBytesValue())
		{
			preStmt.setBytes(index, value.getBytes_value());
		}
		else if(value.isLongValue())
		{
			preStmt.setLong(index, value.getLong_value());
		}
		else if(value.isFloatValue())
		{
			preStmt.setFloat(index, value.getFloat_value());
		}
		else if(value.isBigdecimalValue())
		{
			preStmt.setBigDecimal(index, value.getBigdecimal_value());
		}
		else if(value.isByteValue())
		{
			preStmt.setByte(index, value.getByte_value());
		}
		else if(value.isDateValue())
		{
			preStmt.setDate(index, value.getDate_value());
		}
		else if(value.isTimeValue())
		{
			preStmt.setTime(index, value.getTime_value());
		}
		else
		{
			preStmt.setObject(index, value.getObject_value());
		}
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		DBCException.logException(DBCException.E_PreparedStatement, e);
	}
	
}
 
Example 17
Project: calcite-avatica   File: RemoteDriverTest.java   Source Code and License Vote up 4 votes
private void checkPrepareBindExecuteFetchDate(Connection connection) throws Exception {
  final String sql0 =
      "select cast(? as varchar(20)) as c\n"
          + "from (values (1, 'a'))";
  final String sql1 = "select ? + interval '2' day as c from (values (1, 'a'))";

  final Date date = Date.valueOf("2015-04-08");
  final long time = date.getTime();

  PreparedStatement ps;
  ParameterMetaData parameterMetaData;
  ResultSet resultSet;

  ps = connection.prepareStatement(sql0);
  parameterMetaData = ps.getParameterMetaData();
  assertThat(parameterMetaData.getParameterCount(), equalTo(1));
  ps.setDate(1, date);
  resultSet = ps.executeQuery();
  assertThat(resultSet.next(), is(true));
  assertThat(resultSet.getString(1), is("2015-04-08"));

  ps.setTimestamp(1, new Timestamp(time));
  resultSet = ps.executeQuery();
  assertThat(resultSet.next(), is(true));
  assertThat(resultSet.getString(1), is("2015-04-08 00:00:00.0"));

  ps.setTime(1, new Time(time));
  resultSet = ps.executeQuery();
  assertThat(resultSet.next(), is(true));
  assertThat(resultSet.getString(1), is("00:00:00"));
  ps.close();

  ps = connection.prepareStatement(sql1);
  parameterMetaData = ps.getParameterMetaData();
  assertThat(parameterMetaData.getParameterCount(), equalTo(1));

  ps.setDate(1, date);
  resultSet = ps.executeQuery();
  assertTrue(resultSet.next());
  assertThat(resultSet.getDate(1),
      equalTo(new Date(time + TimeUnit.DAYS.toMillis(2))));
  assertThat(resultSet.getTimestamp(1),
      equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));

  ps.setTimestamp(1, new Timestamp(time));
  resultSet = ps.executeQuery();
  assertTrue(resultSet.next());
  assertThat(resultSet.getTimestamp(1),
      equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));
  assertThat(resultSet.getTimestamp(1),
      equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));

  ps.setObject(1, new java.util.Date(time));
  resultSet = ps.executeQuery();
  assertTrue(resultSet.next());
  assertThat(resultSet.getDate(1),
      equalTo(new Date(time + TimeUnit.DAYS.toMillis(2))));
  assertThat(resultSet.getTimestamp(1),
      equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2))));

  resultSet.close();
  ps.close();
  connection.close();
}
 
Example 18
Project: lams   File: Patch0016FixWorkspacePublicFolder.java   Source Code and License Vote up 4 votes
@Override
   public void migrate(MigrationContext context) throws MigrationException {

// using data source defined in application container
DataSourceMigrationContext ctx = (DataSourceMigrationContext) context;

Connection conn = null;

try {
    conn = ctx.getConnection();
    conn.setAutoCommit(false);

    // add workspace public folder
    String i18nMessage = getI18nMessage(conn);
    PreparedStatement query = conn.prepareStatement(insertWorkspacePublicFolder,
	    Statement.RETURN_GENERATED_KEYS);
    query.setLong(1, new Long(1));
    query.setString(2, i18nMessage);
    query.setLong(3, new Long(1));
    query.setDate(4, new java.sql.Date(System.currentTimeMillis()));
    query.setDate(5, new java.sql.Date(System.currentTimeMillis()));
    query.setInt(6, new Integer(3));

    int numUpdatedWorkspaces = query.executeUpdate();
    ResultSet results = query.getGeneratedKeys();

    log.info("Inserted " + numUpdatedWorkspaces + " new workspace folder.");
    long wkspcFolderId = -1;
    if (results.next()) {
	wkspcFolderId = results.getLong(1);
    }

    // insert new workspace
    query = conn.prepareStatement(insertNewWorkspace, Statement.RETURN_GENERATED_KEYS);
    query.setString(1, i18nMessage);
    query.setLong(2, wkspcFolderId);

    numUpdatedWorkspaces = query.executeUpdate();
    results = query.getGeneratedKeys();

    log.info("Inserted " + numUpdatedWorkspaces + " new workspace.");
    long wkspcId = -1;
    if (results.next()) {
	wkspcId = results.getLong(1);
    }

    // insert new wkspc to wkspc folder mapping
    query = conn.prepareStatement(insertNewWkspcWkspcFolder);
    query.setLong(1, wkspcId);
    query.setLong(2, wkspcFolderId);

    numUpdatedWorkspaces = query.executeUpdate();
    log.info("Inserted " + numUpdatedWorkspaces + " wkspc_wkspc_folder links.");

    ctx.commit();

    // conn.close(); // container managed data source
} catch (Exception e) {
    ctx.rollback();
    throw new MigrationException("Problem running update; ", e);
}
   }
 
Example 19
Project: LojaDeInstrumentosMusicais   File: VendaDAO.java   Source Code and License Vote up 4 votes
public List<Venda> listavendas(String codigoempresa, Date datainicial, Date datafinal) throws Exception{
     String query = "Select v.codigo as codigo, c.nome || ' ' || c.SOBRENOME as cliente, v.datavenda as datavenda,\n" +
                    "      v.valortotal as valortoal, e.nome || ' - ' || e.cidade || ' - ' || e.tipo as nomeempresa from venda v\n" +
                    "inner join clientes c on c.id = v.CODIGOCLIENTE \n" +
                    "inner join empresas e on e.CODIGO = v.CODIGOEMPRESA " +
                    " where datavenda between ? and ? ";
     
     List<Venda> listadevendas = new ArrayList<>();
     
     
     boolean vempresas = false;
     
     if ((codigoempresa.equals("1"))||(codigoempresa.equals("2"))||(codigoempresa.equals("3"))){
         vempresas = true;
         query = query + " and v.codigoempresa = ?";
     }
     
    try {
        PreparedStatement preparedStatement = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        
        if(!vempresas){
           preparedStatement.setDate(1, datainicial);
           preparedStatement.setDate(2, datafinal);
        }else{
           preparedStatement.setDate(1, datainicial);
           preparedStatement.setDate(2, datafinal);
           preparedStatement.setInt(3, Integer.parseInt(codigoempresa));
        }
        
        ResultSet rs = preparedStatement.executeQuery();
        
        while (rs.next()){
            Venda venda = new Venda();
            
            venda.setCodigo(rs.getInt(1));
            venda.setNomecliente(rs.getString(2));
            venda.setData(rs.getDate(3));
            venda.setValorTotal(rs.getDouble(4));
            venda.setNomeempresa(rs.getString(5));
            listadevendas.add(venda);
        }
        
    } catch (SQLException ex) {
        throw new Exception("Erro ao listar carrinho", ex);
    }
      
    return listadevendas;
   
}
 
Example 20
Project: tcp   File: SqlUpdates.java   Source Code and License Vote up 4 votes
public static boolean updateAccion(Accion objeto, String usuarioResponsable) throws ParseException{
	try {             
		Connection conect=ConnectionConfiguration.conectar();
		String	query = "update accion set ";
		PreparedStatement update =null;
		java.sql.Date sqlStartDate = null;
		java.sql.Date sqlEndDate = null;
		
		if(objeto.getFechaInicio()!=null || objeto.getFechaFin()!=null){
			String startDate = objeto.getFechaInicio();
			SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
			java.util.Date date1 = sdf1.parse(startDate);
			sqlStartDate = new java.sql.Date(date1.getTime());
			String endDate = objeto.getFechaFin();
			SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd");
			java.util.Date date2 = sdf2.parse(endDate);
			sqlEndDate = new java.sql.Date(date2.getTime());
		}
		

		if(objeto.getCosto() != 0)      			query+= "costo= ?";
		if(objeto.getPeso() != 0)	    			query+= ", peso= ?";
		if(objeto.getFechaInicio() != null)		 	query+= ", fecha_inicio= ?";
		if(objeto.getFechaFin() != null)    		query+= ", fecha_fin= ?";
		if(objeto.getVersion() != 0)				query+= ", version= ?";
		/*if(objeto.getMeta1() !=0)*/				query+= ", meta1= ?";
		/*if(objeto.getMeta2() !=0)*/				query+= ", meta2= ?";
		/*if(objeto.getMeta3() !=0)*/				query+= ", meta3= ?";
		/*if(objeto.getMeta4() !=0)*/				query+= ", meta4= ?";
		if(objeto.getInsLineaAccionId() != 0)		query+= ", ins_linea_accion_id= ?";
		/*if(objeto.getDepartamentoId() != 0)*/	 	query+= ", depto_id= ?";
		/*if(objeto.getDistritoId() != 0)*/			query+= ", dist_id= ?";
		if(objeto.getAccionCatalogoId() != 0)		query+= ", id_accion_catalogo= ?";
													query += ", usuario_responsable = ? ";
		
		query+=" where id = ?";

		int cantCampos =0;
		update = conect.prepareStatement(query);
		if (objeto.getCosto() != 0)  				{    cantCampos++;update.setDouble (cantCampos, objeto.getCosto());}
		if (objeto.getPeso() != 0)	   				{    cantCampos++;update.setDouble (cantCampos, objeto.getPeso());}
		if (objeto.getFechaInicio() != null)		{    cantCampos++;update.setDate (cantCampos, sqlStartDate);}
		if (objeto.getFechaFin() != null)	    	{    cantCampos++;update.setDate (cantCampos, sqlEndDate);}
		if (objeto.getVersion() != 0)	    		{    cantCampos++;update.setInt (cantCampos, objeto.getVersion());}
		/*if (objeto.getMeta1() != 0)*/	    		{    cantCampos++;update.setDouble (cantCampos, objeto.getMeta1());}
		/*if (objeto.getMeta2() != 0)*/	    		{    cantCampos++;update.setDouble (cantCampos, objeto.getMeta2());}
		/*if (objeto.getMeta3() != 0)*/	    		{    cantCampos++;update.setDouble (cantCampos, objeto.getMeta3());}
		/*if (objeto.getMeta4() != 0)*/	    		{    cantCampos++;update.setDouble (cantCampos, objeto.getMeta4());}
		if (objeto.getInsLineaAccionId() != 0)		{    cantCampos++;update.setInt (cantCampos, objeto.getInsLineaAccionId());}
		/*if (objeto.getDepartamentoId() != 0)*/	{    cantCampos++;update.setInt (cantCampos, objeto.getDepartamentoId());}
		/*if (objeto.getDistritoId() != 0)*/		{    cantCampos++;update.setInt (cantCampos, objeto.getDistritoId());}
		if (objeto.getAccionCatalogoId() != 0)		{    cantCampos++;update.setInt (cantCampos, objeto.getAccionCatalogoId());}
		
			cantCampos++;
			update.setString (cantCampos, usuarioResponsable);
		
            cantCampos++;
            update.setInt    (cantCampos , objeto.getId());

            update.execute();
		    conect.close();
		    return true;
		} catch (SQLException e) {e.printStackTrace(); return false;}
}