如何使用java在mysql服务器中插入年份数据类型

Question

我有sql服务器上的sql模式，我试图在表中插入值，但当我试图插入年时，我得到错误SQL中的数据类型我已经将字符串解析为年份数据类型，例如

double c=Double.parseDouble("2019.0");
int yt=(int) c;
Year business_year; 
business_year=Year.parse(String.format("%04d", yt));
String qq="INSERT INTO invoice_details VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(qq);
pstmt.setYear(1,business_year);

pstmt.setYear(1,business_year);在这行中，我得到以下错误

PreparedStatement类型中的setDate(int, Date)方法不适用于参数(int, Year)。

有人能帮帮忙吗?

Answer 1

警告:我不使用MySQL。我确实看过版本8的文档。

Java提供Year类。MySQL提供YEAR数据类型。顺便说一下，与您的问题所暗示的相反，据我所知，SQL标准确实不定义YEAR数据类型。

可以直接传递Java对象。对Year类的支持是在JDBC 4.2中定义的而不是。因此，这种行为是特定于您的特定JDBC驱动程序的可选特性。如果您的驱动程序确实提供了这个，我希望它使用PreparedStatement#setObject和ResultSet#getObject方法。

myPreparedStatement.setObject( … , Year.now() ) ;

和

Year y = myResultSet.getObject( … , Year.class ) ;

如果您的JDBC驱动程序不提供对Year的支持，MySQL的文档说您应该能够传递文本或整数。下面是传递/接收整数的示例代码。

Year year = Year.of( 2020 ) ;
…
pstmt.setInt( 2 , year.getValue() );

…:

Year year = Year.of( rs.getInt( "year_" ) );

关于你的代码:

pstmt.setYear(1,business_year);

…在PreparedStatement上没有setYear方法。该代码无法编译。

<标题>

下面是整个示例应用程序的源代码。

这个例子有注释掉的代码，试图将PreparedStatement#setObject和ResultSet#getObject与java.time.Year类一起使用。

当通过Maven依赖项使用JDBC驱动程序运行时，由于缺乏对Year类的支持，此代码失败:

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.23</version>
</dependency>

…MySQL 8托管在DigitalOcean.com作为一个托管数据库服务

这个示例应用程序中的两个关键行被标记为// <-- 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;
}
}