《项目实战》使用JDBC手写分库

Myth丶恋晨 2023-10-12 16:43 2阅读 0赞

#### 文章目录 ####

*  1、概要
 *  2、整体架构流程
 *  3、技术名词解释
 *  4、技术细节
 *   *  4.1、指定分库规则
     *  4.2、安装Mysql数据库以及建库建表
     *  4.3、创建Java项目
     *   *  4.3.1、使用 Idea创建Maven项目
         *   *  4.3.1.1、修改pom.xml配置
         *  4.3.2、编写分库/路由规则 DbRouter
         *  4.3.3、编写数据库交互工具 DaoUtil
         *  4.3.4、编写数据库操作接口 BaseDAO
         *  4.3.5、编写数据库操作类 BaseDAOImpl
         *  4.3.6、测试并检查分库结果
         *   *  4.3.6.1、从主库迁移数据到分库
             *  4.3.6.2、通过ID查询数据，并查看路由的数据库信息
 *  5、小结

![在这里插入图片描述][18aae07479434b3381d65d9e1892837c.png]

## 1、概要 ##

在Mysql上手写数据库分库，技术栈：Java、JDBC、反射

## 2、整体架构流程 ##

分库的重点是要制定出合适的分库规则，以及数据操作时，需要使用对应的路由规则。  
![在这里插入图片描述][7a1068ed9ec64337a9cc7c5c0cc63e0e.png]

## 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项目 ####

**创建空项目**  
![在这里插入图片描述][2d01ed180c9a44bbadb091340bb07b1b.png]

##### 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>
    
            
            <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>
    
    		  
            <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。

[18aae07479434b3381d65d9e1892837c.png]: https://img-blog.csdnimg.cn/18aae07479434b3381d65d9e1892837c.png
[7a1068ed9ec64337a9cc7c5c0cc63e0e.png]: https://img-blog.csdnimg.cn/7a1068ed9ec64337a9cc7c5c0cc63e0e.png
[2d01ed180c9a44bbadb091340bb07b1b.png]: https://img-blog.csdnimg.cn/2d01ed180c9a44bbadb091340bb07b1b.png