对order表的两种查询操作的对比（仍然是查询的结果是：一行多列）
注意：order是mysql中的一个关键字
这里故意mysql的字段名和javabean的属性名不一样
其中一些通用的类和属性配置文件和上一篇文章一样
参考：https://blog.csdn.net/qq_41709577/article/details/110733977
使用getMetaData
1、新建表order
新建javabean，
注意：导包一定要导sql下的包
java.sql.Date下的包
order类的代码：
package myself_test.entity;
import java.sql.Date;
public class Order {
private Integer orderId;
private String orderName;
private Date orderDate;
public Order() {
}
public Order(Integer orderId, String orderName, Date orderDate) {
this.orderId = orderId;
this.orderName = orderName;
this.orderDate = orderDate;
}
public Integer getOrderId() {
return orderId;
}
public void setOrderId(Integer orderId) {
this.orderId = orderId;
}
public String getOrderName() {
return orderName;
}
public void setOrderName(String orderName) {
this.orderName = orderName;
}
public Date getOrderDate() {
return orderDate;
}
public void setOrderDate(Date orderDate) {
this.orderDate = orderDate;
}
@Override
public String toString() {
return "Order{" +
"orderId=" + orderId +
", orderName='" + orderName + '\'' +
", orderDate=" + orderDate +
'}';
}
}
普通查询的代码：
//普通查询Order表的操作
@Test
public void queryOrder() {
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
connection = JDBCUtils.getConnection();
String sql="select order_id,order_name,order_date from `order` where order_id=?";
ps = connection.prepareStatement(sql);
ps.setObject(1,1);
rs = ps.executeQuery();
if(rs.next()) {
int orderID = rs.getInt(1);
String order_name = rs.getString(2);
Date date = (Date) rs.getObject(3);
Order order = new Order(orderID, order_name, date);
System.out.println(order);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,ps,rs);
}
}
结果：
通用查询的代码：
注意这里的order表的字段名和java中order类的属性名是不一样的！
会出现两个问题
第一个问题：
错误显示：
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order where order_id=1' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
at com.mysql.jdbc.Util.getInstance(Util.java:383)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1901)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2002)
at myself_test.commonControl.Common.queryOrderCommon(Common.java:69)
at myself_test.utils_test.JDBCTest.testCommonQueryOrder(JDBCTest.java:41)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
null
这个错误的原因是因为order表，其中order就是关键字
String sql=“select order_id,order_name,order_date from order where order_id=?”;
这句话不应该这样写：
而应该在order这里加上飘号（esc下面的那个键）
String sql=“select order_id,order_name,order_date from order where order_id=?”;
但是加上之后，又会报错：
java.lang.NoSuchFieldException: order_id
at java.lang.Class.getDeclaredField(Class.java:2070)
at myself_test.commonControl.Common.queryOrderCommon(Common.java:77)
at myself_test.utils_test.JDBCTest.testCommonQueryOrder(JDBCTest.java:41)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Order{orderId=null, orderName='null', orderDate=null}
这个错误出现的原因就是java类order的属性名和mysql表的字段名不一致导致的。
代码改进：
有两个地方需要改进：
1、写sql的地方需要添加别名，而且切记：
别名一定要和java的属性名一致
String sql=“select order_id orderId,order_name orderName,order_date orderDate from order where order_id=?”;
2、不要使用getColumnName（）方法，这个方法仍然返回的是表的真实列名，应该使用getColumnLabel（）
其他的地方不变
运行结果截图：
全部代码：
//通用查询的操作
public static Order queryOrderCommon(String sql,Object... args) {
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
Order order= null;
try {
connection = JDBCUtils.getConnection();
ps = connection.prepareStatement(sql);
for(int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
order = new Order();
if(rs.next()) {
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
for (int i = 0; i < columnCount; i++) {
// String columnName = rsmd.getColumnName(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);
Object objectValue = rs.getObject(i + 1);
Field field = Order.class.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(order,objectValue);
}
}
} catch (SQLException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,ps,rs);
}
return order;
}
测试代码：
public void testCommonQueryOrder(){
String sql="select order_id orderId," +
"order_name orderName,order_date orderDate from `order` where order_id=?";
Order order = Common.queryOrderCommon(sql, 1);
System.out.println(order);
}
总结：以后全部使用getColumnLabel()，这个方法会好点，不管你有没有在sql中起别名。
1543
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
