连接器
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(); } }
标签:直连,preparedStatement,java,String,setString,private,final,mysql,id From: https://www.cnblogs.com/ckfeng/p/18000797