java直连mysql操作数据

连接器

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
 * @author : chenKeFeng
 * @date : 2024/1/30 10:21
 */
public class MySQLConnector {
    
    private final String url;
    private final String userName;
    private final String passWord;

    public MySQLConnector(String url, String username, String password) {
        this.url = url;
        this.userName = username;
        this.passWord = password;
    }

    public Connection connect() throws SQLException {
        return DriverManager.getConnection(url, userName, passWord);
    }

    public ResultSet executeQuery(Connection connection, String query) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(query);
        return preparedStatement.executeQuery();
    }

    public void close(Connection connection) throws SQLException {
        if (connection != null) {
            connection.close();
        }
    }
}

 

这里演示把用户的数据迁移到另一个数据库

定义变量

    //用户默认头像
    private static final String AVATAR = "xxx";
    //crm用户数据库
    private static final String URL = "jdbc:mysql://xxx:3306/user";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "xxx";

    //渠道数据库
    private static final String URL2 = "jdbc:mysql://xxx:3306/channel";
    private static final String USERNAME2 = "root";
    private static final String PASSWORD2 = "xxx";

    //需要变更到部门的部门id
    private static final String DEPTID = "xxx";
    //变更的部门初始化权值
    private static final BigDecimal INITVALUE = new BigDecimal("xxx");

    //角色id
    private static final String ROLEID = "xxx";
    private static final String ROLEName = "xxx";

 

原始方法实现crud

    public static void main(String[] args) {
        MySQLConnector connector = new MySQLConnector(URL, USERNAME, PASSWORD);
        MySQLConnector connector2 = new MySQLConnector(URL2, USERNAME2, PASSWORD2);
        try {
            Connection connection = connector.connect();
            Connection connection2 = connector2.connect();

            String query = "SELECT sui.id, sui.user_name, sui.phone_number FROM sys_user_info as sui join sys_staff_info as ssi on ssi.user_id = sui.id where ssi.user_type in(0, 2, 3) and ssi.deleted =0 and ssi.status in (0, 3) and sui.is_deleted = 0";
            ResultSet resultSet = connector.executeQuery(connection, query);

            List<CrmUserVo> userList = new ArrayList<>();
            //boolean numberExists = false;
            while (resultSet.next()) {
                CrmUserVo crmUserVo = new CrmUserVo();
                String id = resultSet.getString("id");
                String name = resultSet.getString("user_name");
                String mobile = resultSet.getString("phone_number");

                crmUserVo.setId(id);
                crmUserVo.setUserName(name);
                crmUserVo.setMobile(mobile);
                System.out.println("crm用户：" + crmUserVo);
                //查询渠道用户是否存在
                String sql1 = "SELECT COUNT(*) FROM channel_user WHERE phone_number = ? and deleted =0";
                PreparedStatement preparedStatement = connection2.prepareStatement(sql1);
                preparedStatement.setString(1, mobile);
                try (ResultSet result = preparedStatement.executeQuery()) {
                    if (result.next()) {
                        int count = result.getInt(1);
                        if (count == 0) {
                            //numberExists = true;
                            userList.add(crmUserVo);
                        }
                    }
                }
            }
            System.out.println("过滤后的数据条数" + userList.size());

            for (CrmUserVo crmUserVo : userList) {
                //获取部门详情
                Dept dept = new Dept();
                String deptSql = "SELECT * FROM sys_department_info WHERE id = ?";
                PreparedStatement deptPreparedStatement = connection2.prepareStatement(deptSql);
                deptPreparedStatement.setString(1, DEPTID);
                try (ResultSet results = deptPreparedStatement.executeQuery()) {
                    if (results.next()) {
                        String deptId = results.getString("id");
                        String deptName = results.getString("depart_name");
                        BigDecimal maxPowerNum = results.getBigDecimal("max_power_num");
                        Integer level = results.getInt("level");
                        String companyId = results.getString("company_id");
                        dept.setId(deptId);
                        dept.setDepartName(deptName);
                        dept.setMaxPowerNum(maxPowerNum);
                        dept.setLevel(level);
                        dept.setCompanyId(companyId);
                    }
                }
                
                BigDecimal maxValue = dept.getMaxPowerNum().add(INITVALUE);
                String insertQuery = "INSERT INTO channel_user (id, user_name, phone_number, avatar, invite_code, create_time, date_join, company_id, depart_id, status, power_num) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                PreparedStatement preparedStatement = connection2.prepareStatement(insertQuery);
                String channelUserId = UUIDutils.getUUID32();
                preparedStatement.setString(1, channelUserId);
                preparedStatement.setString(2, crmUserVo.getUserName());
                preparedStatement.setString(3, crmUserVo.getMobile());
                preparedStatement.setString(4, AVATAR);
                preparedStatement.setString(5, createChannelUserInvite());
                preparedStatement.setTimestamp(6, Timestamp.valueOf(LocalDateTime.now()));
                preparedStatement.setDate(7, Date.valueOf(LocalDate.now()));
                preparedStatement.setString(8, dept.getCompanyId());
                preparedStatement.setString(9, dept.getId());
                preparedStatement.setInt(10, 0);
                preparedStatement.setBigDecimal(11, maxValue);
                int rowsAffected = preparedStatement.executeUpdate();
                System.out.println("插入用户数据：" + rowsAffected);

                if (rowsAffected > 0) {
                    //新增渠道用户和角色绑定关联
                    String insertRole = "INSERT INTO union_role_user (id, user_id, role_id, role_name) VALUES (?, ?, ?, ?)";
                    PreparedStatement rolePreparedStatement = connection2.prepareStatement(insertRole);
                    rolePreparedStatement.setString(1, UUIDutils.getUUID32());
                    rolePreparedStatement.setString(2, channelUserId);
                    rolePreparedStatement.setString(3, ROLEID);
                    rolePreparedStatement.setString(4, ROLEName);
                    int roleRows = rolePreparedStatement.executeUpdate();
                    System.out.println("插入角色" + roleRows);

                    //修改部门权值
                    String updateSql = "UPDATE sys_department_info SET max_power_num = ? WHERE id = ?";
                    try (PreparedStatement updatePreparedStatement = connection2.prepareStatement(updateSql)) {
                        updatePreparedStatement.setBigDecimal(1, maxValue);
                        updatePreparedStatement.setString(2, dept.getId());

                        int rowsAffected1 = updatePreparedStatement.executeUpdate();
                        System.out.println("修改部门权值： " + rowsAffected1);
                    }
                }
            }
            connector.close(connection);
            connector2.close(connection2);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }