Java PreparedStatement.setDate方法代码示例

import java.sql.PreparedStatement; //导入方法依赖的package包/类
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) {}
    }
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * 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();
    
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@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;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
* 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));
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
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;}
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * @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");
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@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;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@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;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
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;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
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();}
	
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
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;}
	
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * 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));
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@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;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * 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);
		}
	}
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
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);
	}
	
}