public void getT_STU_INFO_LIST() throws Exception {
String sql = null;
/**
* 查处MS SQL2000的T_STU_INFO_LIST表数据
*
*/
BaseCon ba = new BaseCon();
Connection conMS = ba.getMSCon(); // 获得MS SQL2000数据库连接
sql = "select res.id,res.fileName,res.resourceDeliverTypeID,res.status,res.fileDate,recnt.cnt,"
+ " res.sendDepartmentID,restu.studentid,stu.name,stu.signDate,stu.fpstudyType,"
+ " stu.FPStudyTime,stu.fpstudyTimeType,stu.fpExam,restu.re"
+ " from driver.dbo.resourceDeliver res"
+ " left outer join "
+ " (select deliverid,studentid,re from driver.dbo.resourceDeliverStudent) restu"
+ " on res.id=restu.deliverid"
+ " left outer join "
+ " (select deliverid,count(studentid)as cnt from driver.dbo.resourceDeliverStudent group by deliverID) recnt"
+ " on res.id=recnt.deliverid"
+ " left outer join "
+ " (select st.id,st.name,st.signDate,st.fpstudyType,st.FPStudyTime,st.fpstudyTimeType,st.fpExam from driver.dbo.student st)stu on stu.id=restu.studentid"
+ " where resourceDeliverTypeID='bmhz'"; PreparedStatement preMS = conMS.prepareStatement(sql);
ResultSet rsMS = preMS.executeQuery(); /**
* 将数据插入Oracle数据库T_STU_INFO_LIST表
*
*/
Connection conORA = ba.getORACon();
sql = "insert into T_STU_INFO_LIST"
+ "('I_LIST_NO','I_LIST_NAME','I_LIST_TYPE','I_LIST_STATE','I_LIST_DATE',"
+ "'I_LIST_COUNT','I_LIST_DEPT','I_STU_NO','I_STU_NAME','I_STU_APP_TIME',"
+ "'I_STU_ISJOIN','I_STU_TIME','I_STU_HOUR','I_STU_EXAM','I_STU_REMARK',"
+ ") values(?,?,?,?,?," + "?,?,?,?,?," +
"?,?,?,?,?)"; PreparedStatement preORA = null;
while (rsMS.next()) {
preORA = conORA.prepareStatement(sql); preORA.setString(1, rsMS.getString(1)); if (null == rsMS.getString(2)) {
preORA.setNull(2, java.sql.Types.VARCHAR);
} else {
preORA.setString(2, rsMS.getString(2));
} if (null == rsMS.getString(3)) {
preORA.setNull(3, java.sql.Types.VARCHAR);
} else {
preORA.setString(3, rsMS.getString(3));
} if (null == rsMS.getString(4)) {
preORA.setNull(4, java.sql.Types.VARCHAR);
} else {
preORA.setString(4, rsMS.getString(4));
} if (null == rsMS.getString(5)) {
preORA.setNull(5, java.sql.Types.VARCHAR);
} else {
preORA.setString(5, rsMS.getString(5));
} if (null == rsMS.getString(6)) {
preORA.setNull(6, java.sql.Types.VARCHAR);
} else {
preORA.setString(6, rsMS.getString(6));
} if (null == rsMS.getString(7)) {
preORA.setNull(7, java.sql.Types.VARCHAR);
} else {
preORA.setString(7, rsMS.getString(7));
} preORA.setString(8, rsMS.getString(8)); if (null == rsMS.getString(9)) {
preORA.setNull(9, java.sql.Types.VARCHAR);
} else {
preORA.setString(9, rsMS.getString(9));
} if (null == rsMS.getString(10)) {
preORA.setNull(10, java.sql.Types.VARCHAR);
} else {
preORA.setString(10, rsMS.getString(10));
} if (null == rsMS.getString(11)) {
preORA.setNull(11, java.sql.Types.VARCHAR);
} else {
preORA.setString(11, rsMS.getString(11));
} if (null == rsMS.getString(12)) {
preORA.setNull(12, java.sql.Types.VARCHAR);
} else {
preORA.setString(12, rsMS.getString(12));
} if (null == rsMS.getString(13)) {
preORA.setNull(13, java.sql.Types.VARCHAR);
} else {
preORA.setString(13, rsMS.getString(13));
} if (null == rsMS.getString(14)) {
preORA.setNull(14, java.sql.Types.VARCHAR);
} else {
preORA.setString(14, rsMS.getString(14));
} if (null == rsMS.getString(15)) {
preORA.setNull(15, java.sql.Types.VARCHAR);
} else {
preORA.setString(15, rsMS.getString(15));
} preORA.executeUpdate();
preORA.close();
}
conORA.close(); rsMS.close();
preMS.close();
conMS.close();
System.out.println("suc"); }
String sql = null;
/**
* 查处MS SQL2000的T_STU_INFO_LIST表数据
*
*/
BaseCon ba = new BaseCon();
Connection conMS = ba.getMSCon(); // 获得MS SQL2000数据库连接
sql = "select res.id,res.fileName,res.resourceDeliverTypeID,res.status,res.fileDate,recnt.cnt,"
+ " res.sendDepartmentID,restu.studentid,stu.name,stu.signDate,stu.fpstudyType,"
+ " stu.FPStudyTime,stu.fpstudyTimeType,stu.fpExam,restu.re"
+ " from driver.dbo.resourceDeliver res"
+ " left outer join "
+ " (select deliverid,studentid,re from driver.dbo.resourceDeliverStudent) restu"
+ " on res.id=restu.deliverid"
+ " left outer join "
+ " (select deliverid,count(studentid)as cnt from driver.dbo.resourceDeliverStudent group by deliverID) recnt"
+ " on res.id=recnt.deliverid"
+ " left outer join "
+ " (select st.id,st.name,st.signDate,st.fpstudyType,st.FPStudyTime,st.fpstudyTimeType,st.fpExam from driver.dbo.student st)stu on stu.id=restu.studentid"
+ " where resourceDeliverTypeID='bmhz'"; PreparedStatement preMS = conMS.prepareStatement(sql);
ResultSet rsMS = preMS.executeQuery(); /**
* 将数据插入Oracle数据库T_STU_INFO_LIST表
*
*/
Connection conORA = ba.getORACon();
sql = "insert into T_STU_INFO_LIST"
+ "('I_LIST_NO','I_LIST_NAME','I_LIST_TYPE','I_LIST_STATE','I_LIST_DATE',"
+ "'I_LIST_COUNT','I_LIST_DEPT','I_STU_NO','I_STU_NAME','I_STU_APP_TIME',"
+ "'I_STU_ISJOIN','I_STU_TIME','I_STU_HOUR','I_STU_EXAM','I_STU_REMARK',"
+ ") values(?,?,?,?,?," + "?,?,?,?,?," +
"?,?,?,?,?)"; PreparedStatement preORA = null;
while (rsMS.next()) {
preORA = conORA.prepareStatement(sql); preORA.setString(1, rsMS.getString(1)); if (null == rsMS.getString(2)) {
preORA.setNull(2, java.sql.Types.VARCHAR);
} else {
preORA.setString(2, rsMS.getString(2));
} if (null == rsMS.getString(3)) {
preORA.setNull(3, java.sql.Types.VARCHAR);
} else {
preORA.setString(3, rsMS.getString(3));
} if (null == rsMS.getString(4)) {
preORA.setNull(4, java.sql.Types.VARCHAR);
} else {
preORA.setString(4, rsMS.getString(4));
} if (null == rsMS.getString(5)) {
preORA.setNull(5, java.sql.Types.VARCHAR);
} else {
preORA.setString(5, rsMS.getString(5));
} if (null == rsMS.getString(6)) {
preORA.setNull(6, java.sql.Types.VARCHAR);
} else {
preORA.setString(6, rsMS.getString(6));
} if (null == rsMS.getString(7)) {
preORA.setNull(7, java.sql.Types.VARCHAR);
} else {
preORA.setString(7, rsMS.getString(7));
} preORA.setString(8, rsMS.getString(8)); if (null == rsMS.getString(9)) {
preORA.setNull(9, java.sql.Types.VARCHAR);
} else {
preORA.setString(9, rsMS.getString(9));
} if (null == rsMS.getString(10)) {
preORA.setNull(10, java.sql.Types.VARCHAR);
} else {
preORA.setString(10, rsMS.getString(10));
} if (null == rsMS.getString(11)) {
preORA.setNull(11, java.sql.Types.VARCHAR);
} else {
preORA.setString(11, rsMS.getString(11));
} if (null == rsMS.getString(12)) {
preORA.setNull(12, java.sql.Types.VARCHAR);
} else {
preORA.setString(12, rsMS.getString(12));
} if (null == rsMS.getString(13)) {
preORA.setNull(13, java.sql.Types.VARCHAR);
} else {
preORA.setString(13, rsMS.getString(13));
} if (null == rsMS.getString(14)) {
preORA.setNull(14, java.sql.Types.VARCHAR);
} else {
preORA.setString(14, rsMS.getString(14));
} if (null == rsMS.getString(15)) {
preORA.setNull(15, java.sql.Types.VARCHAR);
} else {
preORA.setString(15, rsMS.getString(15));
} preORA.executeUpdate();
preORA.close();
}
conORA.close(); rsMS.close();
preMS.close();
conMS.close();
System.out.println("suc"); }
driver.dbo.resourceDeliverStudent
select res.id,res.fileName,res.resourceDeliverTypeID,res.status,res.fileDate,recnt.cnt,
res.sendDepartmentID,restu.studentid,stu.name,stu.signDate,stu.fpstudyType,
stu.FPStudyTime,stu.fpstudyTimeType,stu.fpExam,restu.re
from driver.dbo.resourceDeliver res
left outer join (
select deliverid,studentid,re from driver.dbo.resourceDeliverStudent
) restu on res.id=restu.deliverid
left outer join (
select deliverid,count(studentid)as cnt from driver.dbo.resourceDeliverStudent group by deliverID
) recnt on res.id=recnt.deliverid
left outer join (
select st.id,st.name,st.signDate,st.fpstudyType,st.FPStudyTime,st.fpstudyTimeType,st.fpExam from driver.dbo.student st
)stu on stu.id=restu.studentid
where resourceDeliverTypeID='bmhz';
因该是联合主键吧,但这个你的错误确失Select 应该没有关系呀缺失select 应该是SQL 语句本身的问题吧
mssql 和 ORA主键概念是一样的,都是一个主键,一个主键可以由多个字段构成,不清楚lz说的意思
似乎就是单引号的问题。我用单引号把列名包起来无法建表啊?楼主的oracle单引号做字段名的表怎么弄的?SQL> create table test1('a' varchar2(10));
create table test1('a' varchar2(10))
*
ERROR 位于第 1 行:
ORA-00904: : 标识符无效
SQL> create table test1(a varchar2(10));表已创建。SQL>
明白了,你的oracle数据库字段名上都有双引号,你把单引号改为双引号再试试估计能插入。
+ "('I_LIST_NO','I_LIST_NAME','I_LIST_TYPE','I_LIST_STATE','I_LIST_DATE',"
+ "'I_LIST_COUNT','I_LIST_DEPT','I_STU_NO','I_STU_NAME','I_STU_APP_TIME',"
+ "'I_STU_ISJOIN','I_STU_TIME','I_STU_HOUR','I_STU_EXAM','I_STU_REMARK',"
+ ") values(?,?,?,?,?," + "?,?,?,?,?," +
"?,?,?,?,?)";
**************************************************************************把单引号去掉把
+ "'I_STU_ISJOIN','I_STU_TIME','I_STU_HOUR','I_STU_EXAM','I_STU_REMARK',"
最后一个逗号去掉
+ "('I_LIST_NO','I_LIST_NAME','I_LIST_TYPE','I_LIST_STATE','I_LIST_DATE',"
+ "'I_LIST_COUNT','I_LIST_DEPT','I_STU_NO','I_STU_NAME','I_STU_APP_TIME',"
+ "'I_STU_ISJOIN','I_STU_TIME','I_STU_HOUR','I_STU_EXAM','I_STU_REMARK',"
+ ") values(?,?,?,?,?," + "?,?,?,?,?," +
"?,?,?,?,?)";
***************************************************************************
1。
'I_LIST_NO'这些字段两边的单引号去掉2。
括号前多了一个逗号
'I_STU_REMARK'," 这个逗号看到了吗?——————————————————————————————————————————
修改后
sql = "insert into T_STU_INFO_LIST"
+ "(I_LIST_NO,I_LIST_NAME,I_LIST_TYPE,I_LIST_STATE,I_LIST_DATE,"
+ "I_LIST_COUNT,I_LIST_DEPT,I_STU_NO,I_STU_NAME,I_STU_APP_TIME,"
+ "I_STU_ISJOIN,I_STU_TIME,I_STU_HOUR,I_STU_EXAM,I_STU_REMARK"
+ ") values(?,?,?,?,?," + "?,?,?,?,?," +
"?,?,?,?,?)";