Java JDBC 实例

来源：互联网发布：天猫的数据魔方在哪里编辑：程序博客网时间：2024/06/16 10:44
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 {
@Resourceprivate AnnouncementGeneratedMapper announceMapper;@Resourceprivate 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")@ResponseBodypublic 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(); }
}
“`
0 0