英文:
SQL date format() not working in JTable while retrieving data
问题
String dquery = "SELECT date_format(emp_date, '%d-%b-%Y') as emp_date, emp_name, emp_accno, emp_ifsc, sal_month, emp_amt, pay_mode, e_comp, e_remark FROM emppayment;";
pst = con.prepareStatement(dquery);
rs = pst.executeQuery();
DefaultTableModel tm = (DefaultTableModel) jTable1.getModel();
tm.setRowCount(0);
while (rs.next()) {
Object obj[] = {
rs.getString("emp_date"),
rs.getString("emp_name"),
rs.getString("emp_accno"),
rs.getString("emp_ifsc"),
rs.getString("sal_month"),
rs.getString("emp_amt"),
rs.getString("pay_mode"),
rs.getString("e_comp"),
rs.getString("e_remark")
};
tm2.addRow(obj);
}
英文:
MySQL query to retrieve data in JTable from MySQL DB is failing in Java Code but work fine in SQL Workbench
Following query :
SELECT date_format(emp_date,'%d-%b-%Y') as emp_date,emp_name,emp_accno,emp_amt,pay_mode,e_comp,e_remark FROM emppayment";
While retrieving i need "dd-mmm-yyyy" (04-Apr-2020) format so i am using above date_format to convert as per requirement.
While executing in JTable i getting following error.
Error : java.sql.SQLException: Bad format for DATE '20-Mar-2020' in column 1.
String dquery = "SELECT date_format(emp_date,'%d-%b-%Y') as emp_date, emp_name,emp_accno,emp_ifsc,sal_month,emp_amt,pay_mode,e_comp,e_remark FROM emppayment;
pst = con.prepareStatement(dquery);
rs = pst.executeQuery();
DefaultTableModel tm = (DefaultTableModel) jTable1.getModel();
tm.setRowCount(0);
while (rs.next()) {
Object obj[]= {
rs.getString("emp_date"),
rs.getString("emp_name"),
rs.getString("emp_accno"),
rs.getString("emp_ifsc"),
rs.getString("sal_month"),
rs.getString("emp_amt"),
rs.getString(pay_mode),
rs.getString(e_comp),
rs.getString(e_remark)};
tm2.addRow(obj);
}
Please guide me
答案1
得分: 0
数据类型不匹配
关于您的错误，我想知道您的数据模型中支持JTable的列的类型是否指定为日期时间数据类型，而您的代码提供的是一个String对象。所以，这是一种数据类型不匹配。
使用Java，而不是SQL
我会在您的Java应用程序中进行此类格式设置，而不是在您的SQL中进行。
LocalDate
使用符合JDBC 4.2或更高版本的JDBC驱动程序从您的DATE列中检索值作为LocalDate。这些驱动程序能够直接在数据库中使用java.time对象进行交换。
LocalDate ld = myResultSet.getObject( … , LocalDate.class ) ;
使用DateTimeFormatter进行本地化
自动进行本地化以供用户查看。
Locale locale = Locale.CANADA_FRENCH ; // 或者 Locale.US 等等。
DateTimeFormatter f = DateTimeFormatter.ofLocalizedDate( FormatStyle.MEDIUM ).withLocale( locale ) ;
String output = ld.format( f ) ;
硬编码格式
如果您坚持要硬编码特定格式，请指定您的格式化模式。
DateTimeFormatter.ofPattern( "dd-MMM-uuuu" , Locale.UK ) ;
英文:
Data type mismatch
As to your error, I wonder if the type of the column in the data model backing your JTable specifies a date-time data type while your code is providing a String object. So, a data type mismatch.
Use Java, not SQL
I would do such formatting in your Java app rather than in your SQL.
LocalDate
Retrieve the value from your DATE column as a LocalDate using a JDBC driver compliant with JDBC 4.2 or later. Such drivers are able to directly exchange java.time objects with the database.
LocalDate ld = myResultSet.getObject( … , LocalDate.class ) ;
Localize with DateTimeFormatter
Automatically localize for presentation to the user.
Locale locale = Locale.CANADA_FRENCH ; // Or Locale.US and so on.
DateTimeFormatter f = DateTimeFormatter.ofLocalizedDate( FormatStyle.MEDIUM ).withLocale( locale ) ;
String output = ld.format( f ) ;
Hard-coding a format
If you insist on hard-coding a specific format, specify your formatting pattern.
DateTimeFormatter.ofPattern( "dd-MMM-uuuu" , Locale.UK ) ;
专注分享java语言的经验与见解，让所有开发者获益！
评论