我想在一个参数中两次执行sql语句 得到结果凭借成json数组 但是报错 无法操作 请高手来看一下
函数代码:
public JSONArray check(work work) throws Exception {
try {
String sql = "select e_name,w_date,w_time,w_score,w_title,w_content,achieve1,achieve2,achieve3,achieve4 FROM WORK where w_number=?";
int k=work.getw_number();
pstmt = conn.prepareStatement(sql);// 实例化操作
pstmt.setInt(1, work.getw_number());
ResultSet rSet = pstmt.executeQuery();// 取得结果
JSONArray array = new JSONArray();//定义jsonarray
ResultSetMetaData metaData = rSet.getMetaData();
int columnCount= metaData.getColumnCount();
while (rSet.next()){
JSONObject jsonObj = new JSONObject();
//遍历ResultSetMetaData数据每一列
for(int i = 1; i <= columnCount;i++)
{
String columnName = metaData.getColumnLabel(i);
String value =rSet.getString(columnName);
jsonObj.put(columnName, value);
}
array.add(jsonObj);
}
System.out.println(array.toString()+"checkhoutai1");
String sql2 = "select r_number,r_content,r_employee,r_time FROM REVIEW where w_number=? ";
pstmt2 = conn.prepareStatement(sql2);// 实例化操作
pstmt2.setInt(1, k);
ResultSet rSet2 = pstmt2.executeQuery();// 取得结果
ResultSetMetaData metaData2 = rSet2.getMetaData();
int columnCount2= metaData2.getColumnCount();
while (rSet2.next())
{
JSONObject jsonObj = new JSONObject();
//遍历ResultSetMetaData数据每一列
for(int i = 1; i <= columnCount2;i++)
{
String columnName = metaData2.getColumnLabel(i);
String value =rSet2.getString(columnName);
jsonObj.put(columnName, value);
}
array.add(jsonObj);
}
System.out.println(array.toString()+"checkhoutai");
return array;
} catch (Exception e) {
throw e;
} finally {
// 关闭操作
if (pstmt2 != null) {
try {
pstmt2.close();
} catch (Exception e) {
throw e;
}
}
}
}
数据库为mysql 连接数据库的参数单另做在一个.java文件中
连接数据库:
/**
*功能 定义数据库操作类
*/
package com.zhisland.db;
import java.sql.Connection;
import java.sql.DriverManager;
public class DatabaseConnection {
//定义数据库驱动程序
private static final String DBDRIVER="com.mysql.jdbc.Driver";
//数据库连接地址
private static final String DBURL="jdbc:mysql://XXXXXX/zhisland";//company表示数据库
private static final String DBUSER="root";
private static final String DBPASS="XXXXXX";
private Connection connection=null;
public DatabaseConnection() throws Exception{
try{
//数据库操作可能出现异常
Class.forName(DBDRIVER);
connection=DriverManager.getConnection(DBURL,DBUSER,DBPASS);
}catch(Exception exception ){
throw exception;
} finally {
}
}
public Connection getConnection(){
return connection;
}
public void close() throws Exception{
if(connection!=null){
try {
connection.close();
} catch (Exception e) {
throw e;
}
}
}
}
报错信息:
java.sql.SQLException: Column 'r_number' not found.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1166)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5732)
at com.zhisland.dao.DAOImpI.check(DAOImpI.java:324)
at com.zhisland.dao.proxy.DAOProxy.check(DAOProxy.java:98)
at com.zhisland.servlet.checkwork.doPost(checkwork.java:52)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:475)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:341)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:498)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:796)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1368)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)