Methods for building a distributed database access framework for Spring+Mybatis+Mysql

1. Preface

When using Java to develop enterprise application software, Spring+MyBatis+Mysql is often used to build a database framework. If the data volume is large, a MYSQL library stores data access efficiency very low, and it often uses the method of sub-repository storage management. This article describes how to build a multi-database access architecture through Spring+Mybatis and use multi-threading to improve the database access efficiency.

It should be noted that this method is only suitable for situations where the number of databases and names are fixed, and not particularly large. In response to the situation where the number of databases is not fixed, I will write another processing plan later.

2. Overall plan

3. Development environment preparation

3.1 Download Spring, Mybatis, Mysql components.

3.2 Eclipse: Java development IDE. The following jar packages are introduced:

The code structure is as follows:

4. Build a database cluster

Create 11 databases in MYSQL (test1/2/3/4/5/6/7/8/9/10/11) to create a simple table:

Insert 50 million pieces of data into the tbl_Demo table in test1, and 5 million pieces of data into the tbl_Demo table in the other 10 databases (using functions).

Insert 50 million pieces of data into the tbl_Demo table in test1, and 5 million pieces of data into the tbl_Demo table in the other 10 databases (using functions).

5. Create Mybatis database mapping interface

 /** * Mybatis mapping interface* * * @author elon * @version 1.0, October 23, 2015*/public interface IDemo { public void insertDemo(DemoDAO demo); public List<Integer> selectGroup();}/** * * Mybatis mapping service interface* * @author elon * @version 1.0, October 23, 2015*/public interface IDemoService{ public void insertDemo(DemoDAO demo); public List<Integer> selectGroup();}/** * * Mybatis mapping service implementation* * @author elon * @version 1.0, October 23, 2015*/public class DemoServiceImpl implements IDemoService{ private IDemo idea = null; public void setIdemo(IDemo idea) { this.idemo = idea; } @Override public void insertDemo(DemoDAO demo) { ideamo.insertDemo(demo); } @Override public List<Integer> selectGroup() { return idea.selectGroup(); }}

6. Create database identity management and dynamic data sources

 /** * * Save the database ID. Each thread is stored by an independent object* * @author elon * @version 1.0, October 23, 2015*/public class DBIndetifier{ private static ThreadLocal<String> dbKey = new ThreadLocal<String>(); public static void setDBKey(final String dbKeyPara) { dbKey.set(dbKeyPara); } public static String getDBKey() { return dbKey.get(); }}/** * Dynamic data source. Different databases can be connected according to different data indexes* * @author elon * @version 1.0, October 23, 2015*/public class DynamicDataSource extends AbstractRoutingDataSource{ @Override public Object determineCurrentLookupKey() { return DBIndetifier.getDBKey(); }}

7. Create database access object

 /** * * Database access object. Used to insert data. * * @author elon * @version 1.0, October 23, 2015*/public class DemoDAO{ private int a; private String b; private int c; public int getA() { return a; } public void setA(int a) { this.a = a; } public String getB() { return b; } public void setB(String b) { this.b = b; } public int getC() { return c; } public void setC(int c) { this.c = c; }}/** * Mapping result definition* * @author elon * @version 1.0, October 23, 2015*/public class DemoResult implements Serializable{ /** * Comment for <code>serialVersionUID</code><br> * */ private static final long serialVersionUID = -413001138792531448L; private long sum; public long getSum() { return sum; } public void setSum(long sum) { this.sum = sum; } @Override public String toString() { return String.valueOf(sum); }}

8. Create database access tasks

 /** * Database access task definition. Package each request to the database access into a task object, put it in task management, and then wait for the task execution to complete and retrieve the execution result. * * @author elon * @version 1.0, October 23, 2015*/public class DBTask implements Runnable{ // Operation database identity, used to specify the accessed database. Consistent with the data dynamic data source definition in the spring configuration file. private final String dbKey; // mybatis database access object private final Object dbAccessObject; // mysbatis database access method name, used to reflect the call private final String methodName; // store the value of variable parameters private final Object[] paraArray; // store the variable parameter type @SuppressWarnings("rawtypes") private final Class[] paraClassArray; // database operation result. The query operation returns the query result; the insert, delete, and modify operations return null. private Object operateResult; // Exception information thrown by the operation database private Exception exception; // Identify whether the task has been executed private boolean finish; /** * Constructor* @param dbKey Database ID* @param dbAccessObject Database access object* @param methodName Database access method name* @param paraArray Parameter list*/ public DBTask(final String dbKey, final Object dbAccessObject, final String methodName, final Object... paraArray) { this.dbKey = dbKey; this.dbAccessObject = dbAccessObject; this.methodName = methodName; this.paraArray = paraArray; finish = false; exception = null; paraClassArray = new Class[paraArray.length]; for (int index = 0; index < paraArray.length; ++index) { paraClassArray[index] = paraArray[index].getClass(); } operateResult = null; } /** * Task execution function* */ @Override public void run() { try { DBIndetifier.setDBKey(dbKey); Method method = dbAccessObject.getClass().getMethod(methodName, paraClassArray); // The query operation returns the query result; the insert, delete, and modify operations return null operateResult = method.invoke(dbAccessObject, paraArray); } catch (Exception e) { exception = e; e.printStackTrace(); } finish = true; } /** * * Return the operation result. Query operation returns query results; Insert, delete, and modify operations return null * * @return operation result*/ public Object getRetValue() { return operateResult; } /** * Throw database operation exception* * @return exception*/ public Exception getException() { return exception; } /** * * Return whether the task has been executed* * @return tag*/ public boolean isFinish() { return finish; }}

9. Create a database task manager

 /** * Database access task management. Put the database access task into the thread pool to execute. * * * @author elon * @version 1.0, October 23, 2015*/public class DBTaskMgr{ private static class DBTaskMgrInstance { public static final DBTaskMgr instance = new DBTaskMgr(); } public static DBTaskMgr instance() { return DBTaskMgrInstance.instance; } private ThreadPoolExecutor pool; public DBTaskMgr() { pool = new ThreadPoolExecutor(10, 50, 60, TimeUnit.SECONDS, new ArrayBlockingQueue<Runnable>(10000), new ThreadPoolExecutor.CallerRunsPolicy()); } public void excute(Runnable task) { pool.execute(task); }}

10. Create MyBatis configuration file

10.1 mybatis.xml

 <?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration> <mappers> <mapper resource="cfg/demoMapper.xml"/> </mappers></configuration>

10.2 demoMapper.xml

 <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.elon.IDemo"> <insert id="insertDemo" parameterType="com.elon.DemoDAO"> insert into tbl_demo(a, b, c) values(#{a}, #{b}, #{c}); </insert> <resultMap id="demoResult" type="com.elon.DemoResult"> <id property="sum" column="sumColum"/> </resultMap> <select id="selectGroup" resultMap="demoResult"> select sum(a) as sumColum from tbl_demo group by c; </select></mapper>

11. Create Spring Configuration File

11.1 spring.xml

 <?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd"> <bean id="dataSource_1"> <property name="driverClassName" value="com.mysql.jdbc.Driver"></property> <property name="url" value="jdbc:mysql://10.70.69.69:3306/test1"></property> <property name="username" value="user123"></property> <property name="password" value="user123"></property> <property name="maxActive" value="100"></property> <property name="maxIdle" value="30"></property> <property name="maxWait" value="500"></property> <property name="defaultAutoCommit" value="true"></property> </bean> <bean id="dataSource_2"> <property name="driverClassName" value="com.mysql.jdbc.Driver"></property> <property name="url" value="jdbc:mysql://10.70.69.69:3306/test2"></property> <property name="username" value="user123"></property> <property name="password" value="user123"></property> <property name="maxActive" value="100"></property> <property name="maxIdle" value="30"></property> <property name="maxWait" value="500"></property> <property name="defaultAutoCommit" value="true"></property> </bean> <bean id="dataSource_3"> <property name="driverClassName" value="com.mysql.jdbc.Driver"></property> <property name="url" value="jdbc:mysql://10.70.69.69:3306/test3"></property> <property name="username" value="user123"></property> <property name="password" value="user123"></property> <property name="maxActive" value="100"></property> <property name="maxIdle" value="30"></property> <property name="maxWait" value="500"></property> <property name="defaultAutoCommit" value="true"></property> </bean> <bean id="dataSource_4"> <property name="driverClassName" value="com.mysql.jdbc.Driver"></property> <property name="url" value="jdbc:mysql://10.70.69.69:3306/test4"></property> <property name="username" value="user123"></property> <property name="password" value="user123"></property> <property name="maxActive" value="100"></property> <property name="maxIdle" value="30"></property> <property name="maxWait" value="500"></property> <property name="defaultAutoCommit" value="true"></property> </bean> <bean id="dataSource_5"> <property name="driverClassName" value="com.mysql.jdbc.Driver"></property> <property name="url" value="jdbc:mysql://10.70.69.69:3306/test5"></property> <property name="username" value="user123"></property> <property name="password" value="user123"></property> <property name="maxActive" value="100"></property> <property name="maxIdle" value="30"></property> <property name="maxWait" value="500"></property> <property name="defaultAutoCommit" value="true"></property> </bean> <bean id="dataSource_6"> <property name="driverClassName" value="com.mysql.jdbc.Driver"></property> <property name="url" value="jdbc:mysql://10.70.69.69:3306/test6"></property> <property name="username" value="user123"></property> <property name="password" value="user123"></property> <property name="maxActive" value="100"></property> <property name="maxIdle" value="30"></property> <property name="maxWait" value="500"></property> <property name="defaultAutoCommit" value="true"></property> </bean> <bean id="dataSource_7"> <property name="driverClassName" value="com.mysql.jdbc.Driver"></property> <property name="url" value="jdbc:mysql://10.61.67.246:3306/test7"></property> <property name="username" value="user123"></property> <property name="password" value="user123"></property> <property name="maxActive" value="100"></property> <property name="maxIdle" value="30"></property> <property name="maxWait" value="500"></property> <property name="defaultAutoCommit" value="true"></property> </bean> <bean id="dataSource_8"> <property name="driverClassName" value="com.mysql.jdbc.Driver"></property> <property name="url" value="jdbc:mysql://10.61.67.246:3306/test8"></property> <property name="username" value="user123"></property> <property name="password" value="user123"></property> <property name="maxActive" value="100"></property> <property name="maxIdle" value="30"></property> <property name="maxWait" value="500"></property> <property name="defaultAutoCommit" value="true"></property> </bean> <bean id="dataSource_9"> <property name="driverClassName" value="com.mysql.jdbc.Driver"></property> <property name="url" value="jdbc:mysql://10.61.67.246:3306/test9"></property> <property name="username" value="user123"></property> <property name="password" value="user123"></property> <property name="maxActive" value="100"></property> <property name="maxIdle" value="30"></property> <property name="maxWait" value="500"></property> <property name="defaultAutoCommit" value="true"></property> </bean> <bean id="dataSource_10"> <property name="driverClassName" value="com.mysql.jdbc.Driver"></property> <property name="url" value="jdbc:mysql://10.61.67.246:3306/test10"></property> <property name="username" value="user123"></property> <property name="password" value="user123"></property> <property name="maxActive" value="100"></property> <property name="maxIdle" value="30"></property> <property name="maxWait" value="500"></property> <property name="defaultAutoCommit" value="true"></property> </bean> <bean id="dataSource_11"> <property name="driverClassName" value="com.mysql.jdbc.Driver"></property> <property name="url" value="jdbc:mysql://10.61.67.246:3306/test11"></property> <property name="username" value="user123"></property> <property name="password" value="user123"></property> <property name="maxActive" value="100"></property> <property name="maxIdle" value="30"></property> <property name="maxWait" value="500"></property> <property name="defaultAutoCommit" value="true"></property> </bean> <bean id="dataSource"> <property name="targetDataSources"> <map> <entry key="test1" value-ref="dataSource_1"/> <entry key="test2" value-ref="dataSource_2"/> <entry key="test3" value-ref="dataSource_3"/> <entry key="test4" value-ref="dataSource_4"/> <entry key="test5" value-ref="dataSource_5"/> <entry key="test6" value-ref="dataSource_6"/> <entry key="test7" value-ref="dataSource_7"/> <entry key="test8" value-ref="dataSource_8"/> <entry key="test9" value-ref="dataSource_9"/> <entry key="test10" value-ref="dataSource_10"/> <entry key="test11" value-ref="dataSource_11"/> </map> </property> </bean> <bean id="sqlSessionFactory"> <property name="configLocation" value="classpath:cfg/mybatis.xml"></property> <property name="dataSource" ref="dataSource" /> </bean> <bean id="iDemo"> <property name="mapperInterface" value="com.elon.IDemo"></property> <property name="sqlSessionFactory" ref="sqlSessionFactory"></property> </bean> <bean id="iDemoService"> <property name="idemo" ref="iDemo"></property> </bean></beans>

12. Test code

 public class TestMain{ /** * Test code* * * @param args */ public static void main(String[] args) { @SuppressWarnings("resource") ApplicationContext context = new ClassPathXmlApplicationContext("cfg/spring.xml"); IDemoService service1 = (IDemoService)context.getBean("iDemoService"); // Create task object DBTask task1 = new DBTask("test1", service1, "selectGroup"); DBTask task2 = new DBTask("test2", service1, "selectGroup"); DBTask task3 = new DBTask("test3", service1, "selectGroup"); DBTask task4 = new DBTask("test4", service1, "selectGroup"); DBTask task5 = new DBTask("test5", service1, "selectGroup"); DBTask task6 = new DBTask("test6", service1, "selectGroup"); DBTask task7 = new DBTask("test7", service1, "selectGroup"); DBTask task8 = new DBTask("test8", service1, "selectGroup"); DBTask task9 = new DBTask("test9", service1, "selectGroup"); DBTask task10 = new DBTask("test10", service1, "selectGroup"); DBTask task11 = new DBTask("test11", service1, "selectGroup"); DemoDAO demo = new DemoDAO(); demo.setA(10000000); demo.setB("12121212"); demo.setC(100); DBTask taskInsert = new DBTask("test2", service1, "insertDemo", demo); SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); System.out.println("Start insert data:" + format.format(new Date())); DBTaskMgr.instance().excute(taskInsert); while (true) { if (!taskInsert.isFinish()) { try { Thread.sleep(1000); } catch (InterruptedException e) { e.printStackTrace(); } } else { break; } } System.out.println("Insert data end:" + format.format(new Date())); System.out.println("Start querying the 50 million data table:" + format.format(new Date())); DBTaskMgr.instance().excute(task1); while (true) { if (!task1.isFinish()) { try { Thread.sleep(1000); } catch (InterruptedException e) { e.printStackTrace(); } } else { break; } } System.out.println(task1.getRetValue()); System.out.println("Query 50 million data table end:" + format.format(new Date())); List<DBTask> taskList = new ArrayList<DBTask>(); taskList.add(task2); taskList.add(task3); taskList.add(task4); taskList.add(task5); taskList.add(task6); taskList.add(task7); taskList.add(task8); taskList.add(task9); taskList.add(task10); taskList.add(task11); System.out.println("Start query 10 5 million data tables: " + format.format(new Date())); for (DBTask task: taskList) { DBTaskMgr.instance().excute(task); } while (true) { int success = 0; for (DBTask task : taskList) { if (!task.isFinish()) { try { Thread.sleep(1000); } catch (InterruptedException e) { e.printStackTrace(); } } else { ++success; } } if (success == 10) { break; } } for (DBTask task : taskList) { System.out.println(task.getRetValue());; } System.out.println("10 5 million data table query ends: " +format.format(new Date())); }}

13. Test results

It takes 45s to directly query a database of 50 million data.

It takes 22s to query 10 databases with 5 million data in a synchronous manner.

Since 10 databases are placed on two servers, one server has 5 databases. If 10 data are deployed to 10 servers separately, the efficiency will be even higher.

Summarize

The above is the editor’s introduction to Spring+Mybatis+Mysql to build a distributed database access framework. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to everyone in time. Thank you very much for your support to Wulin.com website!