数据中心与对外平台的积分同步

最新推荐文章于 2025-04-30 20:58:57 发布

紫枫叶520

最新推荐文章于 2025-04-30 20:58:57 发布

阅读量696

点赞数

CC 4.0 BY-SA版权

分类专栏：小功能综合模块文章标签： java mysql 数据同步

本文链接：https://blog.youkuaiyun.com/zifengye520/article/details/46278663

小功能综合模块专栏收录该内容

5 篇文章

订阅专栏

本文介绍了一个在两个不同服务器间同步用户积分的解决方案。通过配置文件、数据库连接工具类及定时任务实现了数据中心与对外平台间的积分数据同步。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在公司做的项目，有两个不同的服务器，一个是数据中心，一个是对外平台。数据中心和对外平台中的用户是同步的。也就是说同一个用户可以在对外平台登录，也可以在数据中心登录。但是在给用户增加积分时，只保存在对外平台的数据库中。因此，同一个用户在登录对外平台是可以看到等级（根据积分来的），在数据中心里却看不见，因此要定时的同步对外平台和数据中心的积分数据。

1、写一个配置文件config.properties，用来保存两个数据库的连接信息。

jdbcDriverMysqlFY=com.mysql.jdbc.Driver

jdbcUrlMysqlFY=jdbc:mysql://172.18.10.146:3306/shfy_dwpt?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull

jdbcUserMysqlFY=root
jdbcPasswordMysqlFY=12344

jdbcDriverMysqlSJZX=com.mysql.jdbc.Driver

jdbcUrlMysqlSJZX=jdbc:mysql://172.18.10.146:3306/shfy_sjzx?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull

jdbcUserMysqlSJZX=root
jdbcPasswordMysqlSJZX=12344

2、写一个获得配置属性的工具类PropertiesUtil.java。

package com.sudytech.plugs.shfy.jifentongbu;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;


public class PropertiesUtil {
    private static Properties props = new Properties();
    static {
            ClassLoader loader = PropertiesUtil.class.getClassLoader();
            InputStream is = loader.getResourceAsStream("com/sudytech/plugs/shfy/jifentongbu/config.properties");
            try {
                    props.load(is);
            } catch (IOException e) {
                    Logger.getLogger(PropertiesUtil.class.getName()).log(Level.SEVERE, null, e);
            }
    }
    public static String getValue(String key) {
            return props.getProperty(key);
    }
}

3、创建对外平台和数据中心的数据库的连接工具MysqlDaoFY.java和MysqlDaoSJZX.java。

MysqlDaoFY.java

package com.sudytech.plugs.shfy.jifentongbu;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MysqlDaoFY {
    public static String DRIVER =PropertiesUtil.getValue("jdbcDriverMysqlFY");
    public static String URL = PropertiesUtil.getValue("jdbcUrlMysqlFY");
    public static String USER =PropertiesUtil.getValue("jdbcUserMysqlFY");
    public static String PASSWORD = PropertiesUtil.getValue("jdbcPasswordMysqlFY");
    
    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    
    public static void closeAll(ResultSet rs, Statement stmt, Connection conn) {
        try {
            rs.close();
        } catch (Exception ex) {
            //ex.printStackTrace();
        }
        try {
            stmt.close();
        } catch (Exception ex) {
            //ex.printStackTrace();
        }
        try {
            conn.close();
        } catch (Exception ex) {
            //ex.printStackTrace();
        }
    }

    public static int update(Connection conn,String sql, Object[] params) throws Exception {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; params != null && i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
            return pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
    }

    public static ResultSet query(Connection conn, String sql, Object[] params) throws Exception {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; params != null && i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
            rs = pstmt.executeQuery();
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
        return rs;
    }
}

MysqlDaoSJZX.java

package com.sudytech.plugs.shfy.jifentongbu;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MysqlDaoSJZX {
    public static String DRIVER =PropertiesUtil.getValue("jdbcDriverMysqlSJZX");
    public static String URL = PropertiesUtil.getValue("jdbcUrlMysqlSJZX");
    public static String USER =PropertiesUtil.getValue("jdbcUserMysqlSJZX");
    public static String PASSWORD = PropertiesUtil.getValue("jdbcPasswordMysqlSJZX");
    
    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    
    public static void closeAll(ResultSet rs, Statement stmt, Connection conn) {
        try {
            rs.close();
        } catch (Exception ex) {
            //ex.printStackTrace();
        }
        try {
            stmt.close();
        } catch (Exception ex) {
            //ex.printStackTrace();
        }
        try {
            conn.close();
        } catch (Exception ex) {
            //ex.printStackTrace();
        }
    }

    public static int update(Connection conn,String sql, Object[] params) throws Exception {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; params != null && i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
            return pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
    }

    public static ResultSet query(Connection conn, String sql, Object[] params) throws Exception {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; params != null && i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
            rs = pstmt.executeQuery();
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
        return rs;
    }
}

4、开始做积分同步了。

package com.sudytech.plugs.shfy.jifentongbu;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class T_POINTS_USERPOINTS {
    
    private static String TABLE_NAME = "T_POINTS_USERPOINTS";
    private static List<String> sqlString = new ArrayList<String>();
    
    public static void doMethod() {
        Connection con1 = null, con2 = null;
        ResultSet rs1 = null,rs2 = null;
        try {
            con1 = MysqlDaoFY.getConnection();
            StringBuffer querySql1 = new StringBuffer().append("SELECT * FROM " + TABLE_NAME);
            rs1 = MysqlDaoFY.query(con1, querySql1.toString(), null);
            con2 = MysqlDaoSJZX.getConnection();
            con2.setAutoCommit(false);
            StringBuffer querySql2 = new StringBuffer();
            StringBuffer querySql3 = new StringBuffer();
            while(rs1.next()) {
            	querySql2 = new StringBuffer().append("SELECT * FROM " + TABLE_NAME);
            	querySql2.append(" WHERE USERID="+rs1.getInt(3)+" AND MODULEID="+rs1.getInt(4));
            	//先查看数据中心是否存在符合条件的数据，存在则更新，否则新增加一条
            	rs2=MysqlDaoSJZX.query(con2, querySql2.toString(), null);
            	if(rs2.next()){//存在，更新
            		querySql3 = new StringBuffer().append("UPDATE "+TABLE_NAME+" SET SCORE = "+rs1.getInt(2));
                	querySql3.append(" WHERE USERID = "+rs1.getInt(3));
					querySql3.append(" AND MODULEID = "+rs1.getInt(4));
            	}else{//不存在，插入
            		querySql3 = new StringBuffer().append("INSERT INTO "+TABLE_NAME);
            		querySql3.append(" (SCORE,USERID,MODULEID) VALUES (");
            		querySql3.append(rs1.getInt(2)+","+rs1.getInt(3)+","+rs1.getInt(4)).append(")");
            	}
                sqlString.add(querySql3.toString());
            }
            Statement st = con2.createStatement();
            for (String sql : sqlString) {
                st.execute(sql);
                System.out.println(sqlString);
            }
            con2.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                con2.rollback();
            } catch (SQLException ex1) {
            }
        } finally {
            MysqlDaoFY.closeAll(null, null, con1);
        }
    }
    
    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        doMethod();
    }
    
}

5、因为是要定时实现积分同步，要写个线程，过一个小时触发一次方法，执行一次同步。

PointsDataSynServlet.java

package com.sudytech.plugs.shfy.jifentongbu;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import com.sudytech.log.ILog;
import com.sudytech.log.LogFactory;


public class PointsDataSynServlet extends HttpServlet{
	  private static ILog _log = LogFactory.getInstance().getLog(PointsDataSynServlet.class);
	    private PointsDataSynThread _PointsThread;
	    
	    @Override
	    public void init() throws ServletException {
		try {
		    System.out.println("开启积分同步线程。。。");
			_PointsThread = new PointsDataSynThread();
			_PointsThread.start();
		} catch (Exception e) {
		    _log.error("servlet初始化失败", e);
		}
	    }
}

PointsDataSynThread.java

package com.sudytech.plugs.shfy.jifentongbu;

import java.util.Timer;
import com.sudytech.log.ILog;
import com.sudytech.log.LogFactory;


public class PointsDataSynThread extends Thread{
	 private static ILog _log = LogFactory.getInstance().getLog(PointsDataSynThread.class);

	    public PointsDataSynThread() {  
	        openTimer();
	    }

	    private void openTimer() {
	        new Timer().schedule(new MyTask(), 1 * 60, 1 * 60 * 1000 * 60 * 24);
	    }

	    public class MyTask extends java.util.TimerTask {

	        @Override
	        public void run() {
	        	T_POINTS_USERPOINTS.doMethod();
	        }
	    }

	    
	}

6、最后要在配置文件中加上如下代码。

<!--纺院与数据中心积分同步线程-->
    <servlet>
        <servlet-name>PointsDataSynServlet</servlet-name>
        <servlet-class>com.sudytech.plugs.shfy.pointsSynchronous.PointsDataSynServlet</servlet-class>
        <load-on-startup>3</load-on-startup>
    </servlet>