1、概要
在Mysql上手写数据库分库，技术栈：Java、JDBC、反射
2、整体架构流程
分库的重点是要制定出合适的分库规则，以及数据操作时，需要使用对应的路由规则。
3、技术名词解释
例如：
- Mysql：关系型数据库，java程序通过jdbc访问、操作数据库
- 分库：传统的关系型数据库，在表数据量增长到一定量级后（单表500万），查询速度显著下降，这时候需要把数据分散分拆到其他数据库，分散承担压力。
4、技术细节
4.1、指定分库规则
案例中分库规则：根据3取余，拆分为3个数据库（后缀0,1,2）
4.2、安装Mysql数据库以及建库建表
- 安装mysql数据库
- 按照分库规则创建数据库database，比如：store0，store1，store2
- 创建订单表：order，并创建模拟数据
4.3、创建Java项目
4.3.1、使用 Idea创建Maven项目
创建空项目
4.3.1.1、修改pom.xml配置
<dependencies> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>3.0.0.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>org.springframework.context</artifactId> <version>3.0.0.RELEASE</version> </dependency> <!-- https://mvnrepository.com/artifact/com.google.code.gson/gson --> <dependency> <groupId>com.google.code.gson</groupId> <artifactId>gson</artifactId> <version>2.10</version> </dependency> <dependency> <groupId>javax.servlet</groupId> <artifactId>javax.servlet-api</artifactId> <version>4.0.1</version> <scope>provided</scope> </dependency> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.30</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-war-plugin</artifactId> <version>3.3.2</version> </plugin> </plugins> </build>
4.3.2、编写分库/路由规则 DbRouter
/*** * @title DbRouter * @desctption <TODO description class purpose> * @author Administrator * @create 2023/6/16 14:23 **/ public class DbRouter { public static final String BASE_DARASOURCE_FIX = "cms_spring_db"; public static final String DARASOURCE_FIX = BASE_DARASOURCE_FIX + "_"; public static final int BASE_NUM = 3; /** * 根据code取余 * @return */ public static String getDBRouter(Long code) { int dataSourceNum = (int) (code % BASE_NUM); return DARASOURCE_FIX + dataSourceNum; } /** * 根据code取余 * @return */ public static String getDBRouter(Integer code) { int dataSourceNum = code % BASE_NUM; return DARASOURCE_FIX + dataSourceNum; } /** * 根据code取余 * @return */ public static String getDBRouter(Double code) { int dataSourceNum = code.intValue() % BASE_NUM; return DARASOURCE_FIX + dataSourceNum; } /** * 根据code取余 * @return */ public static String getDBRouter(Float code) { int dataSourceNum = code.intValue() % BASE_NUM; return DARASOURCE_FIX + dataSourceNum; } }
4.3.3、编写数据库交互工具 DaoUtil
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; /** * 数据库交互工具 */ public class DaoUtil { /** * 通过类获取类名 转sql格式(包含前后缀` `) * @param clazz * @return */ public static String HumpToSQL(Class clazz){ String simpleName = clazz.getSimpleName(); String str = "`"; for (int i = 0; i < simpleName.length(); i++) { char c = simpleName.charAt(i); if (i == 0 && Character.isUpperCase(c)){ c = Character.toLowerCase(c); } if ( i != 0 && Character.isUpperCase(c) ){ str += "_" + Character.toLowerCase(c); }else { str += c; } } str += "`"; return str; } /** * String转sql格式 * @param simpleName * @return */ public static String HumpToSQL(String simpleName){ String str = ""; for (int i = 0; i < simpleName.length(); i++) { char c = simpleName.charAt(i); if (i == 0 && Character.isUpperCase(c)){ c = Character.toLowerCase(c); } if ( i != 0 && Character.isUpperCase(c) ){ str += "_" + Character.toLowerCase(c); }else { str += c; } } return str; } /** * get方法名转对应sql字段名 * 例子:getTypeName -> type_name */ public static String getToSQL(String getMethodName){ String str = ""; String substring = getMethodName.substring(3); for (int i = 0; i < substring.length(); i++) { char c = substring.charAt(i); if (i == 0){ str += Character.toLowerCase(c); }else{ if (Character.isUpperCase(c)){ str += "_" + Character.toLowerCase(c); }else { str += c; } } } return str; } /** * 数据库连接 * @param database * @return * @throws ClassNotFoundException * @throws SQLException */ public static Connection getConnection(String database) throws ClassNotFoundException, SQLException { //1.注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2.获取数据库连接 database = "jdbc:mysql://127.0.0.1:3306/" + database; Connection conn = DriverManager.getConnection(database, "root", "root"); return conn; } }
4.3.4、编写数据库操作接口 BaseDAO
public interface BaseDAO { //根据id查询单条数据 public Object queryInfo(Class clazz, Long id , String dataSource) throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException ; //创建数据 public int createInfo(Class clazz, Object obj , String dataSource) throws SQLException, ClassNotFoundException, InvocationTargetException, IllegalAccessException; }
4.3.5、编写数据库操作类 BaseDAOImpl
public class BaseDAOImpl implements BaseDAO { /** * 根据id查询单条数据 * @param id */ @Override public Object queryInfo(Class clazz, Long id , String dataSource) throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException { //1.实例化自定义工具类 //2.获取连接 Connection conn = DaoUtil.getConnection(dataSource); //3.创建Statement\PreparedStatement对象 PreparedStatement ps; //4.初始化查询sql语句 String sql = "select * from " + DaoUtil.HumpToSQL(clazz) + " where " + clazz.getSimpleName().toLowerCase() + "_is_delete = 0 and " + clazz.getSimpleName().toLowerCase() + "_id = " + id; //5.执行sql ps = conn.prepareStatement(sql); ResultSet rs = ps.executeQuery(); Field[] declaredFields = clazz.getDeclaredFields(); //6.处理结果 while (rs.next()){ //实例化新对象 Object obj = clazz.newInstance(); //给对象set值 for (Field declaredField : declaredFields) { declaredField.setAccessible(true); declaredField.set(obj,rs.getObject(DaoUtil.HumpToSQL(declaredField.getName()))); declaredField.setAccessible(false); } // 7、释放资源 conn.close(); ps.close(); return obj; } // 7、释放资源 conn.close(); ps.close(); return null; } /** * Base创建数据 * @param clazz * @param obj */ @Override public int createInfo(Class clazz, Object obj , String dataSource) throws SQLException, ClassNotFoundException, InvocationTargetException, IllegalAccessException { //1.实例化自定义工具类 //2.获取连接 Connection conn = DaoUtil.getConnection(dataSource); //3.创建Statement\PreparedStatement对象 PreparedStatement ps; //4.初始化查询sql语句 String sql = "insert into " + DaoUtil.HumpToSQL(clazz); //6.字符串构造字段--完善sql语句 Field[] declaredFields = clazz.getDeclaredFields(); StringBuffer stringFields = new StringBuffer(" ("); StringBuffer stringSeats= new StringBuffer(" ("); //数据库字段集合 List<String> fildsList = new ArrayList<>(); //类属性集合 List<String> attributesList = new ArrayList<>(); for (Field declaredField : declaredFields) { if ( !declaredField.getName().contains("UpdateTime") && !declaredField.getName().contains("IsDelete") && !declaredField.getName().equals("newsViews")){ //如果是不用设置的属性就加入集合 fildsList.add(DaoUtil.HumpToSQL(declaredField.getName())); attributesList.add(declaredField.getName()); } } // System.out.println("字段集合：" + fildsList); for (int i = 0; i < fildsList.size(); i++) { stringFields.append(fildsList.get(i)); stringSeats.append("?"); if (i != fildsList.size() - 1){ //如果不是最后一个 stringFields.append(","); stringSeats.append(","); }else { //最后一个 stringFields.append(") "); stringSeats.append(") "); } } sql += stringFields + " value " + stringSeats; // System.out.println("sql语句:" + sql); //填充占位符 ps = conn.prepareStatement(sql); Method[] methods = clazz.getMethods(); //初始化n n用来标志占位符位置 int n = 0; for (Method method : methods) { //获取符合的get方法 枚举排除掉不用插入的属性（排除数据库部分自动生成的字段默认值） if ( method.getName().contains("get") && !method.getName().contains("UpdateTime") && !method.getName().contains("IsDelete") && !method.getName().equals("getClass") && !method.getName().equals("getNewsViews")) { //遍历属性集合，通过找到get方法对应上的属性，更新占位符索引 for (int i = 0; i < attributesList.size(); i++) { if (method.getName().toLowerCase().contains(attributesList.get(i).toLowerCase())){ n = i + 1; } } //用get方法获取对象的属性值 method.setAccessible(true); Object date = method.invoke(obj); //把获取到的值填充到sql的占位符 ps.setObject(n,date); // System.out.println(method.getName() + " :" + date + " 占位符位置" + n); method.setAccessible(false); } } int i = ps.executeUpdate(); //7、释放资源 conn.close(); ps.close(); return i; } }
4.3.6、测试并检查分库结果
4.3.6.1、从主库迁移数据到分库
List<News> list = baseDAO.queryInfo(News.class); for (News news : list) { Long newsId = news.getNewsId(); String dataSource = DbRouter.getDBRouter(newsId) ; System.out.println("newsId:" +newsId + " , 所属数据源：" + dataSource ); baseDAO.createInfo(News.class, news ,dataSource); }* System.out.println(list.size());
4.3.6.2、通过ID查询数据，并查看路由的数据库信息
Long newsId = 1007L; BaseDAO baseDAO = new BaseDAOImpl(); String dataSource = DbRouter.getDBRouter(newsId) ; System.out.println("数据源路由地址：" + dataSource); News news = (News) baseDAO.queryInfo(News.class ,newsId , dataSource); System.out.println( new Gson().toJson(news));
5、小结
通过上述实验，可以看出分库核心是分库/路由规则，基于此我们可以慢慢完善开发出分库分表组件，如开源框架：ShardingSphere-JDBC。
