How to insert a date into an SQL and Java tuple

Question

I tried to insert a date into a tuple in SQL but I couldn’t, using the Calendar.getInstance() I can only get the current date, but I want to choose another date to add.

public class Prova {

private Calendar data;

public Prova (         
        Calendar data){

    this.data = data;

}

public Calendar getData() {
    return data;
}

public void setData(Calendar data) {
    this.data = data;
}

DAO class

 public class ProvaDAO {

    public void inserirProva(Prova prova) throws SQLException {

    Connection conn = null;
    conn = DriverManager.getConnection(JDBCConection.getUrl(),
            JDBCConection.getUsuario(), JDBCConection.getSenha());

    String sql = "insert into prova (data) values (?)";
    PreparedStatement stmt = conn.prepareStatement(sql);

    java.sql.Date dataParaGravar = new java.sql.Date(
            Calendar.getInstance().getTimeInMillis());

    stmt.setDate(1, new java.sql.Date(Calendar.getInstance().getTimeInMillis()));

    stmt.execute();
    stmt.close();

    }

Main

// Não dá certo salvar a data assim 
Prova prova = new Prova(Calendar.getInstance().set(2018, 1, 1));
provaDAO.inserirProva(prova);

Answer 1

In doing:

stmt.setDate(3, new java.sql.Date(Calendar.getInstance().getTimeInMillis()));

You are creating another instance of Calendar containing the current date/time (as that is what getInstance() returns). Instead, you should use the Calendar who’s in class Prova.

Another detail is that to create the date of the proof you used the method set, that returns nothing (see documentation that this method is void, ie does not return any value), so this code even compiles:

Prova prova = new Prova(3, 4, Calendar.getInstance().set(2018, 1, 1));
provaDAO.inserirProva(prova);

The right thing is to first create the Calendar, set the values and then pass to the constructor Prova:

Calendar cal = Calendar.getInstance();
cal.set(2018, Calendar.JANUARY, 1);
Prova prova = new Prova(3, 4, cal);
provaDAO.inserirProva(prova);

And inside the DAO use the date of proof:

stmt.setDate(3, new java.sql.Date(prova.getData().getTimeInMillis()));

Notice I used Calendar.JANUARY, which makes the code a little clearer, since in this API the months start at zero (January is zero, February is 1, etc). That is to say, set(2018, 1, 1) arrow the date to February 1, 2018. If you want January, you must do set(2018, 0, 1), but using constants makes the code less confusing.

---

Java 8

From Java 8 there is a API java.time, that you can use instead of Calendar. For example, to create a specific date (only the day, month and year), you can use the class java.time.LocalDate:

// ao contrário de Calendar, nesta API janeiro é 1
LocalDate data = LocalDate.of(2018, 1, 1); // 1 de janeiro de 2018

And to convert to java.sql.Date, just use the method valueOf. To convert the java.sql.Date back to LocalDate, use the method toLocalDate

LocalDate data = ....
// converter para java.sql.Date
java.sql.Date sqlDate = java.sql.Date.valueOf(data);
// converter de volta para LocalDate
data = sqlDate.toLocalDate();

I mean, all you had to do was change the class Prova to have a LocalDate instead of a Calendar, and to save in bank use valueOf to obtain the java.sql.Date and toLocalDate() to get back the LocalDate.

---

If the database you are using has a JDBC 4.2 compliant driver, it is possible to work directly with the JDBC classes java.time, using the methods setObject class java.sql.PreparedStatement and getObject class java.sql.ResultSet. An example with LocalDate would be:

LocalDate data = ...
// gravar o LocalDate
PreparedStatement ps = ...
ps.setObject(1, data);

// obter o LocalDate do banco
ResultSet rs = ...
LocalDate data = rs.getObject(1, LocalDate.class);