Java JDBC 实例

import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringEscapeUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.epicsaas.app.yop.dao.gen.AnnouncementGeneratedMapper;
import com.epicsaas.app.yop.dao.gen.AnnouncementReadGeneratedMapper;

/**
* @author RayWang
* @date 2016年3月30日 下午6:43:36
*/
@Controller
@RequestMapping(“/pc/annsync”)
public class AnnouncementSyncController {

@Resource
private AnnouncementGeneratedMapper announceMapper;
@Resource
private AnnouncementReadGeneratedMapper announceReadMapper;

public static final String MYSQL_DB_DEST = "jdbc:mysql://10.180.120.183:3306/yop?characterEncoding=utf8";
public static final String USER_NAME_DEST = "root";
public static final String PASSWORD_DEST = "123456";

public static final String MYSQL_DB_LOCAL = "jdbc:mysql://127.0.0.1:3306/yop_sync?characterEncoding=utf8";
public static final String USER_NAME_LOCAL = "root";
public static final String PASSWORD_LOCAL = "root";

public static final String DRIVER_NAME = "com.mysql.jdbc.Driver";

private Connection destConn = null;
private Connection localConn = null;

private Connection getLocalConn() {
    try {
        if (this.localConn == null) {
            Class.forName(DRIVER_NAME); 
            this.localConn = DriverManager.getConnection(MYSQL_DB_LOCAL, USER_NAME_LOCAL, PASSWORD_LOCAL); 
        }
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    } 
    return this.localConn;
}

private Connection getDestConn()  {
    try {
        if (this.destConn == null) {
            Class.forName(DRIVER_NAME); 
            this.destConn = DriverManager.getConnection(MYSQL_DB_DEST, USER_NAME_DEST, PASSWORD_DEST); 
        }
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return this.destConn;
}

@RequestMapping("/init")
public Object initSync() {
    Map<String, Object> retMap = new HashMap<String, Object>();

    return retMap;
}

@RequestMapping("/sync")
@ResponseBody
public Object sync() {
    Map<String, Object> retMap = new HashMap<String, Object>();
    System.out.println("建立数据库连接...");
    Connection conn0 = this.getLocalConn();
    Connection conn1 = this.getDestConn();
    if (conn0 != null && conn1 != null) {
        System.out.println("数据库连接建立成功！\n开始创建数据同步临时表...");
        if (createSyncTables(conn0)) {
            System.out.println("公告数据同步临时表创建成功！\n准备同步数据...");
            if (syncCopy(conn0, conn1)) {
                System.out.println("同步数据复制成功！");
                if (doSync()) {
                    System.out.println("数据同步成功！");
                    retMap.put("success", true);
                    retMap.put("msg", "公告数据同步成功！");
                    this.releaseDestConn();
                    this.releaseLocalConn();
                } else {
                    retMap.put("success", false);
                    retMap.put("msg", "公告数据同步失败！");
                }
            } else {
                retMap.put("success", false);
                retMap.put("msg", "公告数据同步失败！");
            }
        } else {
            retMap.put("success", false);
            retMap.put("msg", "公告数据同步失败！");
        }
    } else {
        retMap.put("success", false);
        retMap.put("msg", "公告数据同步失败！");
    }
    return retMap;
}

private boolean createSyncTables(Connection conn) {
    String delAnnounceSyncTabSQL = "DROP TABLE IF EXISTS `t_announcement_sync`";
    String delAnnounceReadSyncTabSQL = "DROP TABLE IF EXISTS `t_announcement_read_sync`";
    String announceSyncTabSQL = "CREATE TABLE `t_announcement_sync` ("
          + "`id` bigint(20) NOT NULL,"
          + "`name` varchar(150) NOT NULL COMMENT '公告名称',"
          + "`publish_user_id` bigint(20) DEFAULT NULL COMMENT '发布人名称',"
          + "`publish_department_id` bigint(20) DEFAULT NULL COMMENT '发布人所属部门',"
          + "`issue_user_id` bigint(20) DEFAULT NULL COMMENT '签发人',"
          + "`time` datetime NOT NULL COMMENT '公告发布时间',"
          + "`content` text COMMENT '公告内容',"
          + "`state` varchar(2) DEFAULT NULL COMMENT '公告状态 0：草稿   1：发布   2:已删除',"
          + "`tenant_id` bigint(20) NOT NULL,"
          + "`template_id` bigint(20) DEFAULT NULL COMMENT '公告模板标识',"
          + "`type` varchar(128) DEFAULT NULL COMMENT '公告类型-公司公告,部门公告',"
          + "`create_time` datetime DEFAULT NULL,"
          + "`modify_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',"
          + "`start_time` datetime DEFAULT NULL COMMENT '公示开始时间',"
          + "`is_issue` tinyint(1) DEFAULT NULL COMMENT '是否需要签发',"
          + "`end_time` datetime DEFAULT NULL COMMENT '公示结束日期',"
          + "`issue_batch_id` varchar(128) DEFAULT NULL COMMENT '签发审批批次标识',"
          + "PRIMARY KEY (`id`)"
        + ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";

    String announceReadSyncTabSQL = "CREATE TABLE `t_announcement_read_sync` ("
          + "`id` bigint(20) NOT NULL,"
          + "`user_id` bigint(20) NOT NULL COMMENT '用户ID',"
          + "`announcement_id` bigint(20) NOT NULL COMMENT '公告ID',"
          + "`state` varchar(2) NOT NULL COMMENT '公告状态(初始状态0)   0 已读  1 已删除',"
          + "PRIMARY KEY (`id`)"
        + ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";

    try {
        Statement stmt = conn.createStatement();
        stmt.execute(delAnnounceSyncTabSQL);
        stmt.execute(delAnnounceReadSyncTabSQL);
        System.out.println("删除已有公告数据同步表成功！");
    } catch (Exception e) {
        e.printStackTrace();
        System.out.println("删除临时同步表出错！");
        return false;
    }

    try {
        Statement stmt = conn.createStatement();
        stmt.executeUpdate(announceSyncTabSQL);
        System.out.println("创建公告同步表成功!");
    } catch (Exception e) {
        System.out.println("创建公告同步表出错!");
        e.printStackTrace();
        return false;
    }

    try {
        Statement stmt = conn.createStatement();
        stmt.executeUpdate(announceReadSyncTabSQL);
        System.out.println("创建公告阅读状态表成功！");
    } catch (Exception e) {
        System.out.println("创建公告阅读状态表出错！");
        e.printStackTrace();
        return false;
    } 

    return true;
}

private boolean syncCopy(Connection connLocal, Connection connDest) {
    System.out.println("正在复制同步数据...");
    String destAnnounceQuerySQL = "select * from t_announcement";
    String destAnnounceReadQuerySQL = "select * from t_announcement_read";
    Statement stmt = null;
    ResultSet rs = null;

    try {
        stmt = connDest.createStatement();
        stmt.executeQuery(destAnnounceQuerySQL);
        rs = stmt.getResultSet();
        while (rs.next()) {
            StringBuilder sb = new StringBuilder();
            sb.append("insert into t_announcement_sync(id, name, publish_user_id, publish_department_id, issue_user_id, time, content, state, tenant_id) values(");
            sb.append(rs.getString("id") + ", ");
            sb.append("'" + rs.getString("name") + "', ");
            sb.append(rs.getString("publish_user_id") + ", ");
            sb.append(rs.getString("publish_department_id") + ", ");
            sb.append(rs.getString("issue_user_id") + ", ");
            sb.append("'" + rs.getString("time") + "', ");
            sb.append(StringEscapeUtils.escapeHtml(URLDecoder.decode("'" + rs.getString("content") + "', ", "UTF-8")));
            sb.append(rs.getString("state") + ", ");
            sb.append(rs.getString("tenant_id"));
            sb.append(")");
            System.out.println(sb.toString());
            Statement annInsertStmt = connLocal.createStatement();
            annInsertStmt.executeUpdate(sb.toString());
        }
    } catch (Exception e) {
        e.printStackTrace();
        return false;
    } finally {
        try {
            rs.close();
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } 
    }

    try {
        stmt = connDest.createStatement();
        stmt.executeQuery(destAnnounceReadQuerySQL);
        rs = stmt.getResultSet();
        while (rs.next()) {
            StringBuilder sb = new StringBuilder();
            sb.append("insert into t_announcement_read_sync values(");
            sb.append(rs.getString("id") + ", ");
            sb.append(rs.getString("user_id") + ", ");
            sb.append(rs.getString("announcement_id") + ", ");
            sb.append(rs.getString("state"));
            sb.append(")");
            System.out.println(sb.toString());
            Statement annReadInsertStmt = connLocal.createStatement();
            annReadInsertStmt.executeUpdate(sb.toString());
        }
    } catch (Exception e) {
        e.printStackTrace();
        return false;
    } finally {
        try {
            rs.close();
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    return true;
}

private boolean doSync() {
    System.out.println("数据同步中...");
    return true;
}

private void releaseLocalConn() {
    try {
        if (this.localConn != null) {
            this.localConn.close();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
 }

 private void releaseDestConn() {
     try {
         if (this.destConn != null) {
             this.destConn.close();
         }
     } catch (SQLException e) {
         e.printStackTrace();
     }
  }

 public static void main(String[] args) {
     AnnouncementSyncController asc =  new AnnouncementSyncController();
     asc.sync();
 }

}

“`