Caveat: I do not use MySQL. I did read the doc for version 8.
Java offers the Year class. MySQL offers the YEAR data type. By the way, contrary to what your Question implies, the SQL standard does not define a YEAR data type as far as I know.
You might be able to pass the Java object directly. Support for the Year class is not defined in JDBC 4.2. So such behavior would be an optional feature specific to your particular JDBC driver. If your driver did offer this, I would expect it to use the PreparedStatement#setObject and ResultSet#getObject methods.
myPreparedStatement.setObject( … , Year.now() ) ;
And…
Year y = myResultSet.getObject( … , Year.class ) ;
If your JDBC driver does not offer that support for Year, the doc for MySQL says you should be able to pass either text or an integer. Here is example code for passing/receiving an integer.
Year year = Year.of( 2020 ) ;
…
pstmt.setInt( 2 , year.getValue() );
…and:
Year year = Year.of( rs.getInt( "year_" ) );
Regarding your code:
pstmt.setYear(1,business_year);
…there is no setYear method on PreparedStatement. That code will not compile.
Example app
Further down is the source code for an entire example app.
This example has commented-out code trying to use PreparedStatement#setObject and ResultSet#getObject in conjunction with the java.time.Year class.
This code fails with a lack of support for Year class when run with this JDBC driver via Maven dependency:
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.23</version>
</dependency>
…on MySQL 8 hosted on DigitalOcean.com as a managed database service.
The two key lines in this example app are marked with // <-- java.time.Year not supported by this JDBC driver..
package work.basil.example.mysqlstuff;
import javax.sql.DataSource;
import java.sql.*;
import java.time.Instant;
import java.time.Year;
import java.util.Objects;
public class App
{
// Member fields.
public static void main ( String[] args )
{
System.out.println( "Hello world. " );
App app = new App();
app.demo();
}
private void demo ( )
{
System.out.println( "INFO - Starting demo method. " + Instant.now() );
DataSource dataSource = this.configureDataSource();
this.dropTable( dataSource );
this.createTable( dataSource );
this.insertRow( dataSource );
this.dumpTable( dataSource );
System.out.println( "INFO - Done with demo method. " + Instant.now() );
}
private void dropTable ( DataSource dataSource )
{
System.out.println( "INFO - `dropTable` method. " + Instant.now() );
try ( Connection conn = dataSource.getConnection() )
{
String sql = """
DROP TABLE IF EXISTS event_
;
""";
System.out.println( "sql: \n" + sql );
try ( Statement stmt = conn.createStatement() )
{
stmt.execute( sql );
}
}
catch ( SQLException e )
{
e.printStackTrace();
}
}
private void createTable ( DataSource dataSource )
{
System.out.println( "INFO - `createTable` method. " + Instant.now() );
try ( Connection conn = dataSource.getConnection() )
{
String sql = """
CREATE TABLE IF NOT EXISTS event_
(
id_ INT NOT NULL AUTO_INCREMENT PRIMARY KEY , -- ⬅ `identity` = auto-incrementing integer number.
title_ VARCHAR ( 30 ) NOT NULL ,
year_ YEAR NOT NULL
)
;
""";
System.out.println( "sql: \n" + sql );
try ( Statement stmt = conn.createStatement() ; )
{
stmt.execute( sql );
}
}
catch ( SQLException e )
{
e.printStackTrace();
}
}
private void insertRow ( DataSource dataSource )
{
System.out.println( "INFO - `insertRow` method. " + Instant.now() );
String sql = """
INSERT INTO event_ ( title_ , year_ )
VALUES ( ? , ? )
;
""";
try (
Connection conn = dataSource.getConnection() ;
PreparedStatement pstmt = conn.prepareStatement( sql ) ;
)
{
pstmt.setString( 1 , "zero" );
// pstmt.setObject( 2 , Year.of( 2020 ) ); // <-- java.time.Year not supported by this JDBC driver.
pstmt.setInt( 2 , Year.of( 2020 ).getValue() );
pstmt.executeUpdate();
pstmt.setString( 1 , "one" );
pstmt.setInt( 2 , Year.of( 2021 ).getValue() );
pstmt.executeUpdate();
pstmt.setString( 1 , "two" );
pstmt.setInt( 2 , Year.of( 2022 ).getValue() );
pstmt.executeUpdate();
}
catch ( SQLException e )
{
e.printStackTrace();
}
}
private void dumpTable ( DataSource dataSource )
{
System.out.println( "INFO - `dumpTable` method. " + Instant.now() );
String sql = "SELECT * FROM event_ ;";
try (
Connection conn = dataSource.getConnection() ;
Statement stmt = conn.createStatement() ;
ResultSet rs = stmt.executeQuery( sql ) ;
)
{
System.out.println( "-------| event_ table |--------------------" );
while ( rs.next() )
{
//Retrieve by column name
int id = rs.getInt( "id_" );
String title = rs.getString( "title_" );
// Year year = rs.getObject( "year_" , Year.class ); // <-- java.time.Year not supported by this JDBC driver.
Year year = Year.of( rs.getInt( "year_" ) );
System.out.println( "id_=" + id + " | title_=" + title + " | year_=" + year );
}
}
catch ( SQLException e )
{
e.printStackTrace();
}
}
private DataSource configureDataSource ( )
{
System.out.println( "INFO - `configureDataSource` method. " + Instant.now() );
com.mysql.cj.jdbc.MysqlDataSource dataSource = Objects.requireNonNull( new com.mysql.cj.jdbc.MysqlDataSource() ); // Implementation of `DataSource` bundled with H2.
dataSource.setServerName( "db-mysql-sfo3-422-do-user-8982-1.x.db.ondigitalocean.com" );
dataSource.setPortNumber( 24_090 );
dataSource.setDatabaseName( "defaultdb" );
dataSource.setUser( "scott" );
dataSource.setPassword( "tiger" );
return dataSource;
}
}
