简单的数据库接口插入到另一个数据库时报错

钟跃民 发布于 2012/02/16 16:17
阅读 742
收藏 0
package com.lpx.db;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;


public class dbExcuse {

	
	/**
	 * 查询对方数据库的sql
	 * 
	 */		
	public String theirSql(){
		String theirsql = "select * from LeaveMessage";
		return theirsql;
	}
	
	
	/**
	 * 连接对方数据库执行对方sql
	 * 
	 */
	public ArrayList theirData(String theirsql){
		theirDbConn dbconn = new theirDbConn();
		Statement stat = dbconn.getTheirDbStat();
		ArrayList theirdatalist = new ArrayList();
		ResultSet rs = null;
		
		try {
			rs = stat.executeQuery(theirsql);
			while (rs.next()) {
				HashMap theirdatamap = new HashMap();
				String id = rs.getString("Id");
				String Username = rs.getString("Username");
				String Sex = rs.getString("Sex");
				String Age = rs.getString("Age");
				String Matter = (String)rs.getString("Matter");
				//System.out.println(Id));//------------测试
			
				theirdatamap.put("Id",id);
				theirdatamap.put("Username",Username);
				theirdatamap.put("Sex",Sex);
				theirdatamap.put("Age",Age);
				theirdatamap.put("Matter",Matter);				
				//System.out.println(theirdatamap.get("Id"));//------------测试
				
				theirdatalist.add((HashMap)theirdatamap);
			}
			//HashMap ap = (HashMap) theirdatalist.get(2);//------------测试
			//System.out.println(ap.get("Id"));//------------测试

			rs.close();
			stat.close();
			
			dbconn.getTheirDbStat().close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return theirdatalist;
	}
	

	/**
	 * 数据处理
	 * 根据唯一标识ID 查询我方数据库是否存在该设备
	 * 生成写入 更新等sql
	 */
	public ArrayList dataAffirm(ArrayList theirdatalist){
		ourDbConn second = new ourDbConn();
		Statement stat = second.getOurDbStat();
		ResultSet rs = null;
		String insertsql = null;
		String updatesql = null;
		ArrayList writelist = new ArrayList();

		for(int i=0;i<theirdatalist.size();i++){
			HashMap datamap = (HashMap) theirdatalist.get(i);
			//System.out.println(datamap.get("Id"));//------------测试
			String ID = (String) datamap.get("Id");
			String USERNAME = (String) datamap.get("Username");
			String SEX = (String) datamap.get("Sex");
			String AGE =  (String) datamap.get("Age");
			String MATTER = (String) datamap.get("Matter");
			//System.out.println(ID);//------------测试
			String affsql = "SELECT ID,USERNAME,SEX,AGE,MATTER FROM LM WHERE ID='"+ID+"'";	

			try {
				rs = stat.executeQuery(affsql);//-------------!!!!!报错的根源
				while(rs.next()){
					if(rs.first()){
						updatesql = "update LM set USERNAME='"+USERNAME+"',SEX='"+SEX+"',AGE='"+AGE+"',MATTER='"+MATTER+"' where ID='"+ID+"'";
						writelist.add(updatesql.toString());
					}else if(!rs.first()){
						insertsql = "insert into LM (ID,USERNAME,SEX,AGE,MATTER) values ("+ID+","+USERNAME+","+SEX+","+AGE+","+MATTER+")";
						writelist.add(insertsql.toString());
					}else{System.out.println("2种操作sql都没有生成");}
				}
				rs.close();
				stat.close();
				second.getOurDbStat().close();
				//System.out.println(ID);//------------测试			
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		return writelist;
	}


	/**
	 * 执行我方数据库的sql写入信息
	 * 
	 */
	public void dataWrite(ArrayList list){
		ourDbConn third = new ourDbConn();
		Statement stat = third.getOurDbStat();
		for(int i=0;i<list.size();i++){
			String sql = (String) list.get(i);
			try {
				stat.execute(sql);
				
				stat.close();
				third.getOurDbStat().close();
			} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			}
		}
	} 
	

	/**
	 * 调度台
	 * 
	 */
	public void doall(){
		String sql = theirSql();
		ArrayList data = theirData(sql);
		ArrayList list = dataAffirm(data);
		dataWrite(list);
	}

	
	/**
	 * 类实例化
	 * 
	 */
	public static void main(String[] args) {
		dbExcuse lets = new dbExcuse();
		lets.doall();
	}
	

}

 我在练习简单的数据库借口

用的是我自己的数据库  建了2张表

从一张表差数据然后 处理后在另一张表里面插入 如果已存在 就更新

 

报错如下:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access 驱动程序] 标准表达式中数据类型不匹配。

at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)

at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)

at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)

at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)

at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(Unknown Source)

at com.lpx.db.dbExcuse.dataAffirm(dbExcuse.java:95)

at com.lpx.db.dbExcuse.doall(dbExcuse.java:148)

at com.lpx.db.dbExcuse.main(dbExcuse.java:159)

java.sql.SQLException: [Microsoft][ODBC Microsoft Access 驱动程序] 标准表达式中数据类型不匹配。

at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)

at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)

at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)

at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)

at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(Unknown Source)

at com.lpx.db.dbExcuse.dataAffirm(dbExcuse.java:95)

at com.lpx.db.dbExcuse.doall(dbExcuse.java:148)

at com.lpx.db.dbExcuse.main(dbExcuse.java:159)

java.sql.SQLException: [Microsoft][ODBC Microsoft Access 驱动程序] 标准表达式中数据类型不匹配。

at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)

at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)

at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)

at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)

at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(Unknown Source)

at com.lpx.db.dbExcuse.dataAffirm(dbExcuse.java:95)

at com.lpx.db.dbExcuse.doall(dbExcuse.java:148)

at com.lpx.db.dbExcuse.main(dbExcuse.java:159)

java.sql.SQLException: [Microsoft][ODBC Microsoft Access 驱动程序] 标准表达式中数据类型不匹配。

at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)

at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)

at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)

at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)

at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(Unknown Source)

at com.lpx.db.dbExcuse.dataAffirm(dbExcuse.java:95)

at com.lpx.db.dbExcuse.doall(dbExcuse.java:148)

at com.lpx.db.dbExcuse.main(dbExcuse.java:159)

java.sql.SQLException: [Microsoft][ODBC Microsoft Access 驱动程序] 标准表达式中数据类型不匹配。

at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)

at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)

at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)

at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)

at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(Unknown Source)

at com.lpx.db.dbExcuse.dataAffirm(dbExcuse.java:95)

at com.lpx.db.dbExcuse.doall(dbExcuse.java:148)

at com.lpx.db.dbExcuse.main(dbExcuse.java:159)

java.sql.SQLException: [Microsoft][ODBC Microsoft Access 驱动程序] 标准表达式中数据类型不匹配。

at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)

at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)

at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)

at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)

at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(Unknown Source)

at com.lpx.db.dbExcuse.dataAffirm(dbExcuse.java:95)

at com.lpx.db.dbExcuse.doall(dbExcuse.java:148)

at com.lpx.db.dbExcuse.main(dbExcuse.java:159)

报错的地方我标志出来了  但是我不知道为什么错了  百度了下 没找到
那个仁兄帮我看看

加载中
1
antipro
antipro
LM 表的ID是字符类型吗?
钟跃民
钟跃民
又发现一个错误 insert的时候 不能把字段ID插入 应该去掉
钟跃民
钟跃民
哈哈 谢谢您了 我刚才发现错误了 原来是SQL写错了 ID是自动编号类型的 应该是INT的 不用在SQL中写'' 我已经改过来了 但是又报错 java.sql.SQLException: Invalid handle 这个是什么错误?还是SQL错误?
0
antipro
antipro
把错误的那一行贴出来。
钟跃民
钟跃民
rs = stat.executeQuery(affsql); 报错始终都指在这个地方 开始也是
钟跃民
钟跃民
String affsql = "SELECT ID,USERNAME,SEX,AGE,MATTER FROM LM WHERE ID='"+ID+"'"; 改为 String affsql = "SELECT ID,USERNAME,SEX,AGE,MATTER FROM LM WHERE ID="+ID;
0
钟跃民
钟跃民
 insertsql = "insert into LM (ID,USERNAME,SEX,AGE,MATTER) values ("+ID+","+USERNAME+","+SEX+","+AGE+","+MATTER+")";
改为
 insertsql = "insert into LM (USERNAME,SEX,AGE,MATTER) values ("+USERNAME+","+SEX+","+AGE+","+MATTER+")";

@ antipro
0
antipro
antipro
到底是那一句报错啊,是affsql还是insertsql?
钟跃民
钟跃民
我发帖是因为affsql有错误 然后按照您说的改正了 现在又报错了 然后我百度了下 说insert里面不能有ID这个自动编号的 我就删除了 然后还是报错 java.sql.SQLException: Invalid handle
0
antipro
antipro
为什么你的循环里面会把数据库连接关闭,应该循环完之后再关闭吧。
antipro
antipro
@钟跃民 : 嗯,是循环到第二次。
antipro
antipro
@钟跃民 : 那第二次循环的时候不就没连接了吗?还查询数据呢?
钟跃民
钟跃民
这个是判断 要插入的要插入的数据库B是否有一样的数据了 然后有就生成UPDATESQL 没有就生成INSERTSQL 然后都存到LIST里面 之后关闭连接了就 在另一个方法里面 把LIST里面去出来 然后执行的是
返回顶部
顶部