高分求救(access数据迁移到mysql问题)
需要把access数据迁移到mysql，这个功能已经基本ＯＫ，但是要加个判断（如果mysql中已经存在该记录，则不添加，否则添加），可我的判断却不管用，因为我的添加语句是写在access中的循环读数据的while(rs１.next())中的，我是这样来通过插入所有的数据，可是这个判断该怎么改呢？
ConnectMysql 类如下，这个用来连接mysql
package myclass;
import java.sql.*;
public class ConnectMysql {
Connection conn = null;
Statement stmt = null;
ResultSet r = null;
String url = "";
public ConnectMysql() {
try {
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
url = "jdbc:mysql://192.168.1.229:3306/goodstart?user=root&password=root&useUnicode=true&characterEncoding=UTF-8";
conn = DriverManager.getConnection(url);
stmt = conn.createStatement();
} catch (Exception e) {
System.out.print("连接数据库发生错误：" + e.getMessage());
}
}
public void createDb(){
if (null == conn) {
try {
System.out.print("创建conn");
conn = DriverManager.getConnection(url);
} catch (Exception e) {
System.out.print("连接数据库发生错误：" + e.getMessage());
}
}
if(null==stmt){
try {
System.out.print("创建stmt");
stmt = conn.createStatement();
} catch (Exception e) {
System.out.print("连接数据库发生错误：" + e.getMessage());
}
}
}
public void closeDb(){
if(null!=stmt){
try {
System.out.print("关闭stmt");
stmt.close();
} catch (Exception e) {
System.out.print("连接数据库发生错误：" + e.getMessage());
}
}
if (null != conn) {
try {
System.out.print("关闭conn");
conn.close();
} catch (Exception e) {
System.out.print("连接数据库发生错误：" + e.getMessage());
}
}
}
// ResultSet Query
public ResultSet executeQuery(String sql) {
try {
r = stmt.executeQuery(sql);
} catch (Exception ex) {
System.out.print("查询发生错误：" + ex.getMessage());
}
return r;
}
// executeUpdate
public void executeUpdate(String sql) {
try {
stmt.executeUpdate(sql);
} catch (Exception ex) {
System.out.print("执行SQL发生错误:" + ex.getMessage());
}
}
}
IsExist 类如下，此类用来判断插入的数据是否是已经有的（主要是checktime时间字段）
package myclass;
import myclass.*;
import java.sql.*;
public class IsExist
{
ResultSet rs = null;
String sql11 = null;
ConnectMysql connectmysql = new ConnectMysql();
ResultSet rs1 = null;
boolean flag = false;
public boolean IsExistKaoqin()
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String dbUrl = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=D:\\myapp\\access\\attBackup.mdb";
Connection con = DriverManager.getConnection(dbUrl, "", "");
Statement stmt1 = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String sql1 = "select a.USERID,a.Name,b.CHECKTIME, b.CHECKTYPE from USERINFO a,CHECKINOUT b where a.USERID = b.USERID ";
rs1 = stmt1.executeQuery(sql1);
try
{
while (rs1.next())
{
sql11 = "select checktime from KQ_USER_SYSUSER where checktime='"
+ rs1.getString("CHECKTIME") + "';";
connectmysql.createDb();
rs = connectmysql.executeQuery(sql11);
//System.out.println("sql11="+sql11);
}
if (rs.next())
{
flag = true;
}
rs.close();
}
catch (Exception ex)
{
System.out.print("Kaoqin's error:"
+ ex.getMessage());
}
if(flag==true)
{
System.out.println("此记录已经有了,请勿重新插入");
}
}
catch (Exception ex)
{
System.out.print("IsExistKaoqin error:" + ex.getMessage());
}
return flag;
}
}
Insert 类如下，此类用来实现抽access数据插入到mysql中
package myclass;
import myclass.*;
import java.sql.*;
public class Insert {
public Insert insertdata = null;
ResultSet rs = null;
String sql = null;
String sql11 = null;
ConnectMysql connectmysql = new ConnectMysql();
IsExist isexist=new IsExist();
ResultSet rs1 = null;
public void addKaoqin()
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String dbUrl = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=D:\\myapp\\access\\attBackup.mdb";
Connection con = DriverManager.getConnection(dbUrl, "", "");
Statement stmt1 = con
.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
String sql1 = "select a.USERID,a.Name,b.CHECKTIME, b.CHECKTYPE from USERINFO a,CHECKINOUT b where a.USERID = b.USERID ";
rs1 = stmt1.executeQuery(sql1);
if(!isexist.IsExistKaoqin())
{
if (rs1.next())
{
sql = "insert into KQ_USER_SYSUSER(sysid,name,checktime,checktype) values ('"
+ rs1.getString("USERID")
+ "','"
+ rs1.getString("Name")
+ "','"
+ rs1.getString("CHECKTIME")
+ "','"
+ rs1.getString("CHECKTYPE") + "');";
System.out.println("sql=" + sql);
connectmysql.createDb();
connectmysql.executeUpdate(sql);
// connectmysql.closeDb();
}
stmt1.close();
con.close();
}
}
catch (Exception ex)
{
System.out.print("插入员工信息 error:" + ex.getMessage());
}
}
public static void main(String[] args)
{
Insert insertdata = new Insert();
insertdata.addKaoqin();
}
}
可是最后那个判断是否已经存在记录的类却没有用，改怎么改呢，每次一运行还是会把所有记录插入进去，帮帮我了，郁闷好多天了
[解决办法]
你的IsExistKaoqin函数似乎有问题
while (rs1.next()) { sql11 = "select checktime from KQ_USER_SYSUSER where checktime='" + rs1.getString("CHECKTIME") + "';"; connectmysql.createDb(); rs = connectmysql.executeQuery(sql11); // System.out.println("sql11="+sql11); } if (rs.next()) { flag = true; } rs.close();
[解决办法]
CHECKTIME是什么类型，就用什么类型