如何使用Java在MySQL服务器中插入Year数据类型 [英] How to insert year data type in mysql server using java
问题描述
我在sql服务器上具有sql模式，我试图在表中插入值，但是当我尝试在sql中插入 Year 数据类型时遇到错误例如，我已经将字符串解析为year数据类型
i have the the sql schema on sql server i am trying to insert the values in the table but i am getting error when i am trying to insert Year data type in sql I have parsed the string to year data type for example
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);在这一行中，我收到以下错误
pstmt.setYear(1,business_year); in this line i am getting the following error
PreparedStatement类型的setDate(int，Date)方法不适用于参数(int，Year).
The method setDate(int, Date) in the type PreparedStatement is not applicable for the arguments (int, Year).
有人可以帮忙吗?
推荐答案
注意:我不使用MySQL.我确实阅读了版本8的文档.
Caveat: I do not use MySQL. I did read the doc for version 8.
Java提供了 Year 类.MySQL提供 YEAR 数据类型.顺便说一句，与您的问题所暗示的相反，据我所知，SQL标准没有 not 定义 YEAR 数据类型.
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.
您也许可以直接传递Java对象.在JDBC 4.2中定义了 not 对 Year 类的支持.因此，这种行为将是特定于您的特定JDBC驱动程序的可选功能.如果您的驱动程序确实提供了此功能，那么我希望它使用 PreparedStatement#setObject 和 ResultSet#getObject 方法.
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() ) ;
然后……
Year y = myResultSet.getObject( … , Year.class ) ;
如果您的JDBC驱动程序不提供对 Year 的支持，则 MySQL文档说，您应该可以传递文本或整数.这是用于传递/接收整数的示例代码.
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() );
…并且:
Year year = Year.of( rs.getInt( "year_" ) );
关于您的代码:
pstmt.setYear(1，business_year);
…在 PreparedStatement 上没有 setYear 方法.该代码将无法编译.
…there is no setYear method on PreparedStatement. That code will not compile.
进一步是整个示例应用程序的源代码.
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.
当通过Maven依赖项与此JDBC驱动程序一起运行时，此代码由于缺少对 Year 类的支持而失败:
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>
…在DigitalOcean.com上作为托管数据库服务托管的MySQL 8上.
…on MySQL 8 hosted on DigitalOcean.com as a managed database service.
此示例应用程序中的两个关键行标有//<-java.time.此JDBC驱动程序不支持的年份..
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;
}
}
这篇关于如何使用Java在MySQL服务器中插入Year数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
