Method for automatically and efficiently adding, deleting, modifying and checking database data
By using the DataSourceMgr class to manage database connections in back-end development, and defining query and operation methods in SqlQueryUtils and SqlUtils classes, the problem of low database operation efficiency in the existing technology is solved, and efficient database addition, deletion, modification and query functions are realized.
Patent Information
- Application Number
- CN202410978387.1
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-07-22
- Publication Date
- 2024-12-06
AI Technical Summary
It is difficult for the existing technology to efficiently implement database addition, deletion, modification and query operations in back-end development, especially when using the Spring framework's automatic injection bean principle, there are problems of low database connection management and operation efficiency.
By establishing a DataSourceMgr class to manage database connections, use DruidDataSource, JdbcTemplate and DataSourceTransactionManager for cache management, and define methods in the SqlQueryUtils and SqlUtils classes for database query and operation, realizing the functions of adding, deleting, modifying and searching.
It realizes automated and efficient database data addition, deletion, modification and query operations, improves database connection management and operation efficiency, avoids the overhead of frequent connection creation, and improves the degree of automation of back-end development.
Smart Images

Figure CN119088811A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of backend development, and in particular to the principle of automatic bean injection of a Spring framework. Background Art
[0002] Spring's property injection is part of the spring bean life cycle. The bean life cycle first remembers two concepts: Spring bean: an object that ultimately exists in the spring container. Object: an instantiated object, but an object is not necessarily a spring bean. The principle of Spring framework's autowiring bean mainly involves the following key components and steps: Bean definition: define beans in a Spring configuration file or using a Java configuration class. Component scanning: Spring scans the specified package path, looks for classes with annotations (such as @Component, @Service, @Repository, @Controller), and registers them as beans. Dependency injection annotations: Use the @Autowired annotation on a class field or constructor to indicate the dependencies that need to be automatically wired. Container initialization: Initialize the Spring container and load all configured beans. Dependency injection: When creating a bean, Spring checks and injects the required dependencies, that is, if a bean requires another bean as a dependency, Spring will automatically inject it into the dependent bean. Summary of the invention
[0003] In order to solve one of the problems existing in the prior art, a method for automatically and efficiently adding, deleting, modifying and checking database data is disclosed, which uses the principle of automatic bean injection of the Spring framework to automatically implement the addition, deletion, modification and query operations of the database.
[0004] To solve the above problems, the technical solutions adopted by the present invention are as follows:
[0005] A method for automatically and efficiently adding, deleting, modifying and checking database data is as follows:
[0006] First, you need to establish a database connection module and create the DataSourceMgr class, which is mainly used to establish a connection with the database; the DataSourceMgr class has the following methods: DruidDataSource getDataSource(String dsId), JdbcTemplate getJdbcTemplate(String dsId), DataSourceTransactionManager getTxManager(String dsId);
[0007] The specific explanation is as follows:
[0008] DruidDataSource getDataSource(String dsId) is a method to obtain the database connection pool. dsId represents the ID of the current database. The returned DruidDataSource is the database connection information. After obtaining the database connection information, the relevant information will be stored in the ConcurrentHashMap of the DataSourceMgr class.<String,DruidDataSource> The dsMap is used as a cache. When actually called, the database connection information will be obtained from the cache first to avoid creating a database connection every time.
[0009] JdbcTemplate getJdbcTemplate(String dsId) is a method to obtain the database JdbcTemplate. dsId represents the ID of the current database. The returned JdbcTemplate is the database underlying processing template class. After obtaining the database underlying processing template, the relevant information will be stored in the ConcurrentHashMap of the DataSourceMgr class.<String,JdbcTemplate> The jdbcMap is used as a cache. When actually calling, the template information will be retrieved from the cache first.
[0010] DataSourceTransactionManager getTxManager(String dsId) is a method for obtaining database transaction processing. dsId represents the ID of the current database. The returned DataSourceTransactionManager is the underlying transaction management class of the database. After obtaining the underlying transaction management class, the relevant information will be stored in the ConcurrentHashMap of the DataSourceMgr class.<String,DataSourceTransactionManager> The txMap is used as a cache. When it is actually called, the transaction management class will be first retrieved from the cache, so that the database connection problem can be solved.
[0011] After the database connection module is established, two modules need to be established for the corresponding add, delete, modify and query operations: data query module and data operation module. The implementation is explained in detail below:
[0012] For the database query module, establish the SqlQueryUtils class, which is mainly used to query the relevant data of the database; the SqlQueryUtils class has the following methods: List <Map<String,Object> >getDataHashMapList(String databaseName,String querySql)、List <Map<String, Object> >getDataHashMapListWithLimit(String databaseName,String querySql,int limit)、 <t>List <t>getDataObjectList(String databaseName,String querySql,Class <t>z) and <t>List <t>getDataObjectListWithLimit(String databaseName,String querySql,intlimit,Class<>z);
[0013] The specific explanation is as follows:
[0014] List <Map<String,Object> >getDataHashMapList(String databaseName,StringquerySql) is a method for obtaining data. databaseName represents the name of the current database, querySql is the SQL statement for the query, and the returned List <Map<String,Object> > is the queried data information. Here, the JdbcTemplate getJdbcTemplate(String dsId) method in the DataSourceMgr class is called first according to the databaseName, so that the template class JdbcTemplate for database operations is obtained. Then the query method in JdbcTemplate is called to obtain the data information to be queried, and the returned content is assembled into a List <Map<String,Object> > type;
[0015] List <Map<String,Object> >getDataHashMapListWithLimit(StringdatabaseName,String querySql,int limit) is a method to obtain data with a limited number of entries. databaseName represents the name of the current database, querySql is the SQL statement for the query, and limit is the limit on the number of queries. The returned List <Map<String,Object> > is the queried data information. Here, the JdbcTemplate getJdbcTemplate(String dsId) method in the DataSourceMgr class is first called according to the databaseName, so that the template class JdbcTemplate for database operations is obtained. Then the query method in the JdbcTemplate is called to obtain the data information to be queried. When the number of queries exceeds the limit, the query will be stopped and the returned content will be assembled into a List <Map<String,Object> > type;
[0016] <t>List <t>getDataObjectList(String databaseName,String querySql,Class <t>z) is a method to obtain data and convert it into an object. databaseName represents the name of the current database, querySql is the SQL statement to be queried, z is the type parameter to be converted into an object, and the returned List <t>The queried data information is converted into an object list. Here, the JdbcTemplategetJdbcTemplate(String dsId) method in the DataSourceMgr class is first called according to the databaseName, so that the template class JdbcTemplate for database operations is obtained. Then the query method in the JdbcTemplate is called to obtain the data information to be queried. The obtained information is first converted into a JSON string, and then the JSON string is converted into an object, which is further added to the object list as a return parameter;
[0017] <t>List <t>getDataObjectListWithLimit(String databaseName,StringquerySql,Class <t>z, int limit) is a method to obtain data with a limited number of entries and convert it into an object. databaseName represents the name of the current database, querySql is the SQL statement for the query, z is the type parameter to be converted into an object, limit is the number of entries to limit the query, and the returned List <t>The queried data information is converted into an object list. Here, the JdbcTemplate getJdbcTemplate(String dsId) method in the DataSourceMgr class is first called according to the databaseName, so that the template class JdbcTemplate for database operations is obtained. Then the query method in JdbcTemplate is called to obtain the data information to be queried. When the number of queries exceeds the limit, the query will be stopped. The obtained information will be converted into a JSON string first, and then the JSON string will be converted into an object, and further added to the object list as a return parameter. At this point, the function of the data query module is realized;
[0018] For the database operation module, the SqlUtils class is established. This class is mainly used to operate the relevant data of the database; the SqlUtils class has the following methods: String getInsertSql(List<Object[]> params,List <Map<String,Object> >data,List <string>columns,String tableName,Map<String,TableInfo>tableInfoMap,boolean isWithIgnore),String getUpdateSql(List<Object[]>params,List<Map<String,Object>>data,List <string>columns,String tableName,List <string>where,Map<String,TableInfo>tableInfoMap,boolean isWithIgnore),Object getValue(String key,Map<String,Object>r,TableInfo tableInfo),intbatchInsertHashMapDataList(List<Map<String,Object>>data,String databaseName,String tableName), <t>int batchInsertObjectDataList(List <t>data,StringdatabaseName,String tableName),int batchUpdateHashMapDataList(List<Map<String,Object>>data,String databaseName,String tableName,List <string>where),int batchUpdateObjectDataList(List <t>data,String databaseName,StringtableName,List <string>where),int insertSingleHashMapData(Map<String,Object>data,String databaseName,String tableName),int insertSingleObjectData(T data,String databaseName,String tableName),int insertSingleHashMapDataReturnId(Map<String,Object>data,String databaseName,String tableName), <t>int insertSingleObjectDataReturnId(Tdata, String databaseName, String tableName);
[0019] The specific explanations are as follows:
[0020] String getInsertSql(List<Object[]> params, List<Map<String, Object>> data, List <string>columns,String tableName,Map<String,TableInfo> tableInfoMap, boolean isWithIgnore) is to get the SQL statement for inserting data. params represents the data to be assembled and inserted into the database, data represents the queried data, columns represents the database table field set, tableName represents the table name, tableInfoMap is the cached table field information, isWithIgnore represents whether to add ignore when inserting. This method mainly uses tableName and columns to splice the basic insert statement. isWithIgnore determines whether insert is followed by ignore. The insert parameter list params is assembled through the data list data to be inserted in preparation for subsequent database insertion operations. If a field value does not exist, it will be replaced by the database default value. The default value is obtained from the cached tableInfoMap. Finally, the Insert Sql statement is assembled: first traverse the data list, then traverse the columns inside each data, assemble the value of the inserted data, insert it into params, and splice the Sql statement at the same time. In this way, the functions of obtaining the Insert Sql statement and assembling the list of data to be inserted can be completed.
[0021] String getUpdateSql(List<Object[]> params,List <Map<String,Object> >data,List <string>columns,String tableName,List <string>where,Map<String,TableInfo> tableInfoMap, boolean isWithIgnore) is the SQL statement to get the updated data. params represents the data to be assembled to update the database, data represents the queried data, columns represents the database table field set, tableName represents the table name, where represents the where condition field list of the update statement, tableInfoMap is the cached table field information, isWithIgnore represents whether to add ignore when updating. This method mainly uses tableName and columns to splice the basic insert statement. isWithIgnore determines whether insert is followed by ignore. The update parameter list params is assembled through the data list data to be updated for subsequent database update operations. If a field value does not exist, it will be replaced by the database default value. The default value is obtained from the cached tableInfoMap. Finally, the Update Sql statement is assembled: first traverse the data list, then traverse the columns inside each data, assemble the value of the updated data, insert it into params, and splice the Sql statement at the same time. Finally, after traversing the columns, it is necessary to traverse the where list and splice the where condition behind the Sql statement. In this way, the Update Sql statements and the function of assembling the list of data to be updated;
[0022] Object getValue(String key,Map<String,Object> r, TableInfo tableInfo) is used to obtain and update the value of the current field. r represents the current data to be processed, and tableInfo represents the basic database information of the current field. This method mainly checks whether the field value matching the key in r exists. If it exists, it returns. If it does not exist, it takes the default value defined for the field in the database from tableInfo.
[0023] int batchInsertHashMapDataList(List <Map<String,Object> >data,StringdatabaseName,String tableName) is to batch insert HashMap type data into the database. data represents the current list of data to be inserted, databaseName represents the current database name, tableName represents the data table currently being processed, and returns the number of affected rows. This method mainly uses databaseName to obtain the JdbcTemplate template method in the DataSourceMgr cache, and then calls the above getInsertSql method to obtain the inserted Sql statement and the assembled list of data to be inserted. Finally, call the batchUpdate method of JdbcTemplate to complete the function of inserting data;
[0024] <t>int batchInsertObjectDataList(List <t>data,String databaseName,String tableName) is to batch insert generic data into the database. data represents the current list of data to be inserted, databaseName represents the current database name, tableName represents the data table currently being processed, and returns the number of affected rows. This method first removes data from the List <t>Convert to List <Map<String,Object> > type, and then directly call batchInsertHashMapDataList;
[0025] int batchUpdateHashMapDataList(List <Map<String,Object> >data,StringdatabaseName,String tableName,List <string>where) is to batch update HashMap type data to the database. data represents the current list of data to be updated, databaseName represents the name of the current database, tableName represents the data table currently being processed, where represents the where condition for the update, and returns the number of affected rows. This method mainly uses databaseName to obtain the JdbcTemplate template method in the DataSourceMgr cache, and then calls the above getUpdateSql method to obtain the inserted Sql statement and the assembled list of data to be updated. Finally, call the batchUpdate method of JdbcTemplate to complete the function of updating data.
[0026] batchUpdateObjectDataList(List <t>data,String databaseName,StringtableName,List <string>where) is to batch update generic data to the database. data represents the current list of data to be updated, databaseName represents the name of the current database, tableName represents the data table currently being processed, where represents the where condition for the update, and returns the number of affected rows. This method first updates data from the List <t>Convert to List <Map<String,Object> > type, and then directly call batchUpdateHashMapDataList;
[0027] int insertSingleHashMapData(Map<String,Object> data,StringdatabaseName,String tableName) is to insert a single data into the database. data represents the current data to be inserted, databaseName represents the current database name, and tableName represents the data table currently being processed. The number of affected rows is returned. This method first declares a List <Map<String,Object> >Variable dataList, then call the add method to add data, and finally directly call the batchInsertHashMapDataList method mentioned above;
[0028] int insertSingleObjectData(T data,String databaseName,StringtableName) is used to insert a single data into the database. data represents the generic data to be inserted, databaseName represents the name of the current database, and tableName represents the data table currently being processed. The number of affected rows is returned. This method first declares a List <t>Variable dataList, then call the add method to add data, and finally directly call the batchInsertObjectDataList method mentioned above;
[0029] int insertSingleHashMapDataReturnId(Map<String,Object> data,StringdatabaseName,String tableName) is to insert a single data into the database, data represents the generic data to be inserted, databaseName represents the current database name, tableName represents the data table currently being processed, and returns the primary key Id. This method will first call the above getInsertSql method to obtain the inserted SQL statement, and then use databaseName to obtain the JdbcTemplate template method in the DataSourceMgr cache, and then call its update method to create a GeneratedKeyHolder object keyHolder, which is a class that comes with the JDBC bottom layer. Finally, this parameter is passed in when executing the update method. After execution, the primary key Id is obtained by calling keyHolder.getKey().intValue();
[0030] <t>int insertSingleObjectDataReturnId(T data,String databaseName,String tableName) is used to insert a single generic data into the database. data represents the generic data to be inserted, databaseName represents the name of the current database, and tableName represents the data table currently being processed. The primary key Id is returned. This method first converts data from generic T to Map<String,Object> Type of data, and then directly call the above insertSingleHashMapDataReturnId method, so far all database related operation functions have been realized.
[0031] Compared with the prior art, the implementation effects of the present invention are as follows:
[0032] Back-end development work: It involves the field of back-end development technology. Back-end development work: It is necessary to use the principle of automatic bean injection of the Spring framework and relevant knowledge of the database to automatically read the database table information and complete the database addition, deletion, modification and query operations. BRIEF DESCRIPTION OF THE DRAWINGS
[0033] Figure 1 It is a schematic diagram of the process of the present invention. DETAILED DESCRIPTION
[0034] The content of the present invention will be described below with reference to specific embodiments.
[0035] like Figure 1 As shown, a method for automatically and efficiently adding, deleting, modifying and checking database data is as follows:
[0036] First, you need to establish a database connection module and create the DataSourceMgr class, which is mainly used to establish a connection with the database; the DataSourceMgr class has the following methods: DruidDataSource getDataSource(String dsId), JdbcTemplate getJdbcTemplate(String dsId), DataSourceTransactionManager getTxManager(String dsId);
[0037] The specific explanation is as follows:
[0038] DruidDataSource getDataSource(String dsId) is a method to obtain the database connection pool. dsId represents the ID of the current database. The returned DruidDataSource is the database connection information. After obtaining the database connection information, the relevant information will be stored in the ConcurrentHashMap of the DataSourceMgr class.<String,DruidDataSource> The dsMap is used as a cache. When actually called, the database connection information will be obtained from the cache first to avoid creating a database connection every time.
[0039] JdbcTemplate getJdbcTemplate(String dsId) is a method to obtain the database JdbcTemplate. dsId represents the ID of the current database. The returned JdbcTemplate is the database underlying processing template class. After obtaining the database underlying processing template, the relevant information will be stored in the ConcurrentHashMap of the DataSourceMgr class.<String,JdbcTemplate> The jdbcMap is used as a cache. When actually calling, the template information will be retrieved from the cache first.
[0040] DataSourceTransactionManager getTxManager(String dsId) is a method for obtaining database transaction processing. dsId represents the ID of the current database. The returned DataSourceTransactionManager is the underlying transaction management class of the database. After obtaining the underlying transaction management class, the relevant information will be stored in the ConcurrentHashMap of the DataSourceMgr class.<String,DataSourceTransactionManager> The txMap is used as a cache. When it is actually called, the transaction management class will be first retrieved from the cache, so that the database connection problem can be solved.
[0041] After the database connection module is established, two modules need to be established for the corresponding add, delete, modify and query operations: data query module and data operation module. The implementation is explained in detail below:
[0042] For the database query module, establish the SqlQueryUtils class, which is mainly used to query the relevant data of the database; the SqlQueryUtils class has the following methods: List <Map<String,Object> >getDataHashMapList(String databaseName,String querySql)、List <Map<String, Object> >getDataHashMapListWithLimit(String databaseName,String querySql,int limit)、 <t>List <t>getDataObjectList(String databaseName,String querySql,Class <t>z) and <t>List <t>getDataObjectListWithLimit(String databaseName,String querySql,intlimit,Class<>z);
[0043] The specific explanation is as follows:
[0044] List <Map<String,Object> >getDataHashMapList(String databaseName,StringquerySql) is a method for obtaining data. databaseName represents the name of the current database, querySql is the SQL statement for the query, and the returned List <Map<String,Object> > is the queried data information. Here, the JdbcTemplate getJdbcTemplate(String dsId) method in the DataSourceMgr class is called first according to the databaseName, so that the template class JdbcTemplate for database operations is obtained. Then the query method in JdbcTemplate is called to obtain the data information to be queried, and the returned content is assembled into a List <Map<String,Object> > type;
[0045] List <Map<String,Object> >getDataHashMapListWithLimit(StringdatabaseName,String querySql,int limit) is a method to obtain data with a limited number of entries. databaseName represents the name of the current database, querySql is the SQL statement for the query, and limit is the limit on the number of queries. The returned List <Map<String,Object> > is the queried data information. Here, the JdbcTemplate getJdbcTemplate(String dsId) method in the DataSourceMgr class is first called according to the databaseName, so that the template class JdbcTemplate for database operations is obtained. Then the query method in the JdbcTemplate is called to obtain the data information to be queried. When the number of queries exceeds the limit, the query will be stopped and the returned content will be assembled into a List <Map<String,Object> > type;
[0046] <t>List <t>getDataObjectList(String databaseName,String querySql,Class <t>z) is a method to obtain data and convert it into an object. databaseName represents the name of the current database, querySql is the SQL statement to be queried, z is the type parameter to be converted into an object, and the returned List <t>The queried data information is converted into an object list. Here, the JdbcTemplategetJdbcTemplate(String dsId) method in the DataSourceMgr class is first called according to the databaseName, so that the template class JdbcTemplate for database operations is obtained. Then the query method in the JdbcTemplate is called to obtain the data information to be queried. The obtained information is first converted into a JSON string, and then the JSON string is converted into an object, which is further added to the object list as a return parameter;
[0047] <t>List <t>getDataObjectListWithLimit(String databaseName,StringquerySql,Class <t>z, int limit) is a method to obtain data with a limited number of entries and convert it into an object. databaseName represents the name of the current database, querySql is the SQL statement for the query, z is the type parameter to be converted into an object, limit is the number of entries to limit the query, and the returned List <t>The queried data information is converted into an object list. Here, the JdbcTemplate getJdbcTemplate(String dsId) method in the DataSourceMgr class is first called according to the databaseName, so that the template class JdbcTemplate for database operations is obtained. Then the query method in JdbcTemplate is called to obtain the data information to be queried. When the number of queries exceeds the limit, the query will be stopped. The obtained information will be converted into a JSON string first, and then the JSON string will be converted into an object, and further added to the object list as a return parameter. At this point, the function of the data query module is realized;
[0048] For the database operation module, the SqlUtils class is established. This class is mainly used to operate the relevant data of the database; the SqlUtils class has the following methods: String getInsertSql(List<Object[]> params,List <Map<String,Object> >data,List <string>columns,String tableName,Map<String,TableInfo>tableInfoMap,boolean isWithIgnore),String getUpdateSql(List<Object[]>params,List<Map<String,Object>>data,List <string>columns,String tableName,List <string>where,Map<String,TableInfo>tableInfoMap,boolean isWithIgnore),Object getValue(String key,Map<String,Object>r,TableInfo tableInfo),intbatchInsertHashMapDataList(List<Map<String,Object>>data,String databaseName,String tableName), <t>int batchInsertObjectDataList(List <t>data,StringdatabaseName,String tableName),int batchUpdateHashMapDataList(List<Map<String,Object>>data,String databaseName,String tableName,List <string>where),int batchUpdateObjectDataList(List <t>data,String databaseName,StringtableName,List <string>where),int insertSingleHashMapData(Map<String,Object>data,String databaseName,String tableName),int insertSingleObjectData(T data,String databaseName,String tableName),int insertSingleHashMapDataReturnId(Map<String,Object>data,String databaseName,String tableName), <t>int insertSingleObjectDataReturnId(Tdata, String databaseName, String tableName);
[0049] The specific explanations are as follows:
[0050] String getInsertSql(List<Object[]> params, List<Map<String, Object>> data, List <string>columns,String tableName,Map<String,TableInfo> tableInfoMap, boolean isWithIgnore) is to get the SQL statement for inserting data. params represents the data to be assembled and inserted into the database, data represents the queried data, columns represents the database table field set, tableName represents the table name, tableInfoMap is the cached table field information, isWithIgnore represents whether to add ignore when inserting. This method mainly uses tableName and columns to splice the basic insert statement. isWithIgnore determines whether insert is followed by ignore. The insert parameter list params is assembled through the data list data to be inserted in preparation for subsequent database insertion operations. If a field value does not exist, it will be replaced by the database default value. The default value is obtained from the cached tableInfoMap. Finally, the Insert Sql statement is assembled: first traverse the data list, then traverse the columns inside each data, assemble the value of the inserted data, insert it into params, and splice the Sql statement at the same time. In this way, the functions of obtaining the Insert Sql statement and assembling the list of data to be inserted can be completed.
[0051] String getUpdateSql(List<Object[]> params,List <Map<String,Object> >data,List <string>columns,String tableName,List <string>where,Map<String,TableInfo> tableInfoMap, boolean isWithIgnore) is the SQL statement to get the updated data. params represents the data to be assembled to update the database, data represents the queried data, columns represents the database table field set, tableName represents the table name, where represents the where condition field list of the update statement, tableInfoMap is the cached table field information, isWithIgnore represents whether to add ignore when updating. This method mainly uses tableName and columns to splice the basic insert statement. isWithIgnore determines whether insert is followed by ignore. The update parameter list params is assembled through the data list data to be updated for subsequent database update operations. If a field value does not exist, it will be replaced by the database default value. The default value is obtained from the cached tableInfoMap. Finally, the Update Sql statement is assembled: first traverse the data list, then traverse the columns inside each data, assemble the value of the updated data, insert it into params, and splice the Sql statement at the same time. Finally, after traversing the columns, it is necessary to traverse the where list and splice the where condition behind the Sql statement. In this way, the Update Sql statements and the function of assembling the list of data to be updated;
[0052] Object getValue(String key,Map<String,Object> r, TableInfo tableInfo) is used to obtain and update the value of the current field. r represents the current data to be processed, and tableInfo represents the basic database information of the current field. This method mainly checks whether the field value matching the key in r exists. If it exists, it returns. If it does not exist, it takes the default value defined for the field in the database from tableInfo.
[0053] int batchInsertHashMapDataList(List <Map<String,Object> >data,StringdatabaseName,String tableName) is to batch insert HashMap type data into the database. data represents the current list of data to be inserted, databaseName represents the current database name, tableName represents the data table currently being processed, and returns the number of affected rows. This method mainly uses databaseName to obtain the JdbcTemplate template method in the DataSourceMgr cache, and then calls the above getInsertSql method to obtain the inserted Sql statement and the assembled list of data to be inserted. Finally, call the batchUpdate method of JdbcTemplate to complete the function of inserting data;
[0054] <t>int batchInsertObjectDataList(List <t>data,String databaseName,String tableName) is to batch insert generic data into the database. data represents the current list of data to be inserted, databaseName represents the current database name, tableName represents the data table currently being processed, and returns the number of affected rows. This method first removes data from the List <t>Convert to List <Map<String,Object> > type, and then directly call batchInsertHashMapDataList;
[0055] int batchUpdateHashMapDataList(List <Map<String,Object> >data,StringdatabaseName,String tableName,List <string>where) is to batch update HashMap type data to the database. data represents the current list of data to be updated, databaseName represents the name of the current database, tableName represents the data table currently being processed, where represents the where condition for the update, and returns the number of affected rows. This method mainly uses databaseName to obtain the JdbcTemplate template method in the DataSourceMgr cache, and then calls the above getUpdateSql method to obtain the inserted Sql statement and the assembled list of data to be updated. Finally, call the batchUpdate method of JdbcTemplate to complete the function of updating data.
[0056] batchUpdateObjectDataList(List <t>data,String databaseName,StringtableName,List <string>where) is to batch update generic data to the database. data represents the current list of data to be updated, databaseName represents the name of the current database, tableName represents the data table currently being processed, where represents the where condition for the update, and returns the number of affected rows. This method first updates data from the List <t>Convert to List <Map<String,Object> > type, and then directly call batchUpdateHashMapDataList;
[0057] int insertSingleHashMapData(Map<String,Object> data,StringdatabaseName,String tableName) is to insert a single data into the database. data represents the current data to be inserted, databaseName represents the current database name, and tableName represents the data table currently being processed. The number of affected rows is returned. This method first declares a List <Map<String,Object> >Variable dataList, then call the add method to add data, and finally directly call the batchInsertHashMapDataList method mentioned above;
[0058] int insertSingleObjectData(T data,String databaseName,StringtableName) is used to insert a single data into the database. data represents the generic data to be inserted, databaseName represents the name of the current database, and tableName represents the data table currently being processed. The number of affected rows is returned. This method first declares a List <t>Variable dataList, then call the add method to add data, and finally directly call the batchInsertObjectDataList method mentioned above;
[0059] int insertSingleHashMapDataReturnId(Map<String,Object> data,StringdatabaseName,String tableName) is to insert a single data into the database, data represents the generic data to be inserted, databaseName represents the current database name, tableName represents the data table currently being processed, and returns the primary key Id. This method will first call the above getInsertSql method to obtain the inserted SQL statement, and then use databaseName to obtain the JdbcTemplate template method in the DataSourceMgr cache, and then call its update method to create a GeneratedKeyHolder object keyHolder, which is a class that comes with the JDBC bottom layer. Finally, this parameter is passed in when executing the update method. After execution, the primary key Id is obtained by calling keyHolder.getKey().intValue();
[0060] <t>int insertSingleObjectDataReturnId(T data,String databaseName,String tableName) is used to insert a single generic data into the database. data represents the generic data to be inserted, databaseName represents the name of the current database, and tableName represents the data table currently being processed. The primary key Id is returned. This method first converts data from generic T to Map<String,Object> Type of data, and then directly call the above insertSingleHashMapDataReturnId method, so far all database related operation functions have been realized.
[0061] The above content is a detailed description of the present invention in combination with specific embodiments, and it cannot be determined that the specific implementation of the present invention is limited to these descriptions. For those skilled in the art of the present invention, without departing from the concept of the present invention, several simple deductions or substitutions can be made, which should be regarded as falling within the scope of protection of the present invention.< / t> < / t> < / t> < / string> < / t> < / string> < / t> < / t> < / t> < / string> < / string> < / string> < / t> < / string> < / t> < / string> < / t> < / t> < / string> < / string> < / string> < / t> < / t> < / t> < / t> < / t> < / t> < / t> < / t> < / t> < / t> < / t> < / t> < / t> < / t> < / t> < / t> < / string> < / t> < / string> < / t> < / t> < / t> < / string> < / string> < / string> < / t> < / string> < / t> < / string> < / t> < / t> < / string> < / string> < / string> < / t> < / t> < / t> < / t> < / t> < / t> < / t> < / t> < / t> < / t> < / t> < / t> < / t>
Claims
1. A method for automatically and efficiently adding, deleting, modifying and checking database data. Features: The method is as follows: First, you need to establish a database connection module and create the DataSourceMgr class, which is mainly used to establish a connection with the database; the DataSourceMgr class has the following methods: DruidDataSource getDataSource(String dsId), JdbcTemplate getJdbcTemplate(String dsId), DataSourceTransactionManagergetTxManager(String dsId); The specific explanation is as follows: DruidDataSource getDataSource(String dsId) is a method to obtain the database connection pool. dsId represents the ID of the current database. The returned DruidDataSource is the database connection information. After obtaining the database connection information, the relevant information will be stored in the ConcurrentHashMap of the DataSourceMgr class.<String,DruidDataSource> The dsMap is used as a cache. When actually called, the database connection information will be obtained from the cache first to avoid creating a database connection every time. JdbcTemplate getJdbcTemplate(String dsId) is a method to obtain the database JdbcTemplate. dsId represents the ID of the current database. The returned JdbcTemplate is the database underlying processing template class. After obtaining the database underlying processing template, the relevant information will be stored in the ConcurrentHashMap of the DataSourceMgr class.<String,JdbcTemplate> The jdbcMap is used as a cache. When actually calling, the template information will be retrieved from the cache first. DataSourceTransactionManager getTxManager(String dsId) is a method for obtaining database transaction processing. dsId represents the ID of the current database. The returned DataSourceTransactionManager is the underlying transaction management class of the database. After obtaining the underlying transaction management class, the relevant information will be stored in the ConcurrentHashMap of the DataSourceMgr class.<String,DataSourceTransactionManager> The txMap is used as a cache. When it is actually called, the transaction management class will be first retrieved from the cache, so that the database connection problem can be solved. After the database connection module is established, two modules need to be established for the corresponding add, delete, modify and query operations: data query module and data operation module. The implementation is explained in detail below: For the database query module, establish the SqlQueryUtils class, which is mainly used to query the relevant data of the database; the SqlQueryUtils class has the following methods: List <Map<String,Object> >getDataHashMapList(String databaseName,String querySql)、List <Map<String,Object> >getDataHashMapListWithLimit(String databaseName,String querySql,int limit)、 <t>List <t>getDataObjectList(String databaseName,String querySql,Class <t>z) and <t>List <t> getDataObjectListWithLimit(String databaseName,String querySql,intlimit,Class<>z);< / t> < / t> < / t> < / t> < / t> The specific explanation is as follows: List <Map<String,Object> >getDataHashMapList(String databaseName,StringquerySql) is a method for obtaining data. databaseName represents the name of the current database, querySql is the SQL statement for the query, and the returned List <Map<String,Object> > is the queried data information. Here, the JdbcTemplate getJdbcTemplate(String dsId) method in the DataSourceMgr class is called first according to the databaseName, so that the template class JdbcTemplate for database operations is obtained. Then the query method in JdbcTemplate is called to obtain the data information to be queried, and the returned content is assembled into a List <Map<String,Object> > type; List <Map<String,Object> >getDataHashMapListWithLimit(String databaseName,String querySql,int limit) is a method to obtain data with a limited number of entries. databaseName represents the name of the current database, querySql is the SQL statement for the query, and limit is the limit on the number of queries. The returned List <Map<String,Object> > is the queried data information. Here, the JdbcTemplate getJdbcTemplate(String dsId) method in the DataSourceMgr class is first called according to the databaseName, so that the template class JdbcTemplate for database operations is obtained. Then the query method in the JdbcTemplate is called to obtain the data information to be queried. When the number of queries exceeds the limit, the query will be stopped and the returned content will be assembled into a List <Map<String,Object> > type; <t>List <t>getDataObjectList(String databaseName,String querySql,Class <t>z) is a method to obtain data and convert it into an object. databaseName represents the name of the current database, querySql is the SQL statement to be queried, z is the type parameter to be converted into an object, and the returned List <t>The queried data information is converted into an object list. Here, the JdbcTemplategetJdbcTemplate(String dsId) method in the DataSourceMgr class is first called according to the databaseName, so that the template class JdbcTemplate for database operations is obtained. Then the query method in the JdbcTemplate is called to obtain the data information to be queried. The obtained information is first converted into a JSON string, and then the JSON string is converted into an object, which is further added to the object list as a return parameter;< / t> < / t> < / t> < / t> <t>List <t>getDataObjectListWithLimit(String databaseName,String querySql,Class <t>z, int limit) is a method to obtain data with a limited number of entries and convert it into an object. databaseName represents the name of the current database, querySql is the SQL statement for the query, z is the type parameter to be converted into an object, limit is the number of entries to limit the query, and the returned List <t> The queried data information is converted into an object list. Here, the JdbcTemplate getJdbcTemplate(String dsId) method in the DataSourceMgr class is first called according to the databaseName, so that the template class JdbcTemplate for database operations is obtained. Then the query method in JdbcTemplate is called to obtain the data information to be queried. When the number of queries exceeds the limit, the query will be stopped. The obtained information will be converted into a JSON string first, and then the JSON string will be converted into an object, and further added to the object list as a return parameter. At this point, the function of the data query module is realized;< / t> < / t> < / t> < / t> For the database operation module, the SqlUtils class is established. This class is mainly used to operate the relevant data of the database; the SqlUtils class has the following methods: String getInsertSql(List<Object[]> params,List <Map<String,Object> >data,List <string>columns,String tableName,Map<String,TableInfo>tableInfoMap,boolean isWithIgnore),String getUpdateSql(List<Object[]>params,List<Map<String,Object>>data,List <string>columns,String tableName,List <string>where,Map<String,TableInfo>tableInfoMap,boolean isWithIgnore),Object getValue(String key,Map<String,Object>r,TableInfo tableInfo),intbatchInsertHashMapDataList(List<Map<String,Object>>data,String databaseName,String tableName), <t>int batchInsertObjectDataList(List <t>data,StringdatabaseName,String tableName),int batchUpdateHashMapDataList(List<Map<String,Object>>data,String databaseName,String tableName,List <string>where),int batchUpdateObjectDataList(List <t>data,String databaseName,StringtableName,List <string>where),int insertSingleHashMapData(Map<String,Object>data,String databaseName,String tableName),int insertSingleObjectData(T data,String databaseName,String tableName),int insertSingleHashMapDataReturnId(Map<String,Object>data,String databaseName,String tableName), <t> intinsertSingleObjectDataReturnId(Tdata,String databaseName,String tableName);< / t> < / string> < / t> < / string> < / t> < / t> < / string> < / string> < / string> The specific explanation is as follows: String getInsertSql(List<Object[]>params,List<Map<String,Object>>data,List <string>columns,String tableName,Map<String,TableInfo> tableInfoMap, boolean isWithIgnore) is to get the SQL statement for inserting data. params represents the data to be assembled and inserted into the database, data represents the queried data, columns represents the database table field set, tableName represents the table name, tableInfoMap is the cached table field information, isWithIgnore represents whether to add ignore when inserting. This method mainly uses tableName and columns to splice the basic insert statement. isWithIgnore determines whether insert is followed by ignore. The insert parameter list params is assembled through the data list data to be inserted in preparation for subsequent database insertion operations. If a field value does not exist, it will be replaced by the database default value. The default value is obtained from the cached tableInfoMap. Finally, the Insert Sql statement is assembled: first traverse the data list, then traverse the columns inside each data, assemble the value of the inserted data, insert it into params, and splice the Sql statement at the same time. In this way, the functions of obtaining the Insert Sql statement and assembling the list of data to be inserted can be completed.< / string> String getUpdateSql(List<Object[]>params,List<Map<String,Object>>data,List <string>columns,String tableName,List <string>where,Map<String,TableInfo> tableInfoMap, boolean isWithIgnore) is the SQL statement to get the updated data. params represents the data to be assembled to update the database, data represents the queried data, columns represents the database table field set, tableName represents the table name, where represents the where condition field list of the update statement, tableInfoMap is the cached table field information, isWithIgnore represents whether to add ignore when updating. This method mainly uses tableName and columns to splice the basic insert statement. isWithIgnore determines whether insert is followed by ignore. The update parameter list params is assembled through the data list data to be updated for subsequent database update operations. If a field value does not exist, it will be replaced by the database default value. The default value is obtained from the cached tableInfoMap. Finally, the UpdateSql statement is assembled: first traverse the data list, then traverse the columns inside each data, assemble the value of the updated data, and insert it into params. At the same time, splice the Sql statement. Finally, after traversing the columns, it is necessary to traverse the where list and splice the where condition behind the Sql statement. In this way, the Update Sql statements and the function of assembling the list of data to be updated;< / string> < / string> Object getValue(String key,Map<String,Object> r, TableInfo tableInfo) is used to obtain and update the value of the current field. r represents the current data to be processed, and tableInfo represents the basic database information of the current field. This method mainly checks whether the field value matching the key in r exists. If it exists, it returns. If it does not exist, it takes the default value defined for the field in the database from tableInfo. int batchInsertHashMapDataList(List <Map<String,Object> >data,StringdatabaseName,String tableName) is to batch insert HashMap type data into the database. data represents the current list of data to be inserted, databaseName represents the current database name, tableName represents the data table currently being processed, and returns the number of affected rows. This method mainly uses databaseName to obtain the JdbcTemplate template method in the DataSourceMgr cache, and then calls the above getInsertSql method to obtain the inserted Sql statement and the assembled list of data to be inserted. Finally, call the batchUpdate method of JdbcTemplate to complete the function of inserting data; <t>int batchInsertObjectDataList(List <t>data,String databaseName,String tableName) is to batch insert generic data into the database. data represents the current list of data to be inserted, databaseName represents the current database name, tableName represents the data table currently being processed, and returns the number of affected rows. This method first removes data from the List <t> Convert to List <Map<String,Object> > type, and then directly call batchInsertHashMapDataList;< / t> < / t> < / t> int batchUpdateHashMapDataList(List<Map<String,Object>>data,StringdatabaseName,String tableName,List <string> where) is to batch update HashMap type data to the database. data represents the current list of data to be updated, databaseName represents the name of the current database, tableName represents the data table currently being processed, where represents the where condition for the update, and returns the number of affected rows. This method mainly uses databaseName to obtain the JdbcTemplate template method in the DataSourceMgr cache, and then calls the above getUpdateSql method to obtain the inserted Sql statement and the assembled list of data to be updated. Finally, call the batchUpdate method of JdbcTemplate to complete the function of updating data.< / string> batchUpdateObjectDataList(List <t>data,String databaseName,StringtableName,List <string>where) is to batch update generic data to the database. data represents the current list of data to be updated, databaseName represents the name of the current database, tableName represents the data table currently being processed, where represents the where condition for the update, and returns the number of affected rows. This method first updates data from the List <t> Convert to List <Map<String,Object> > type, and then directly call batchUpdateHashMapDataList;< / t> < / string> < / t> int insertSingleHashMapData(Map<String,Object> data,String databaseName,String tableName) is to insert a single data into the database. data represents the current data to be inserted, databaseName represents the current database name, and tableName represents the data table currently being processed. The number of affected rows is returned. This method first declares a List <Map<String,Object> >Variable dataList, then call the add method to add data, and finally directly call the batchInsertHashMapDataList method mentioned above; int insertSingleObjectData(T data,String databaseName,String tableName) is used to insert a single data into the database. data represents the generic data to be inserted, databaseName represents the name of the current database, and tableName represents the data table currently being processed. The number of affected rows is returned. This method first declares a List <t>Variable dataList, then call the add method to add data, and finally directly call the batchInsertObjectDataList method mentioned above;< / t> int insertSingleHashMapDataReturnId(Map<String,Object> data,StringdatabaseName,String tableName) is to insert a single data into the database, data represents the generic data to be inserted, databaseName represents the current database name, tableName represents the data table currently being processed, and returns the primary key Id. This method will first call the above getInsertSql method to obtain the inserted SQL statement, and then use databaseName to obtain the JdbcTemplate template method in the DataSourceMgr cache, and then call its update method to create a GeneratedKeyHolder object keyHolder, which is a class that comes with the JDBC bottom layer. Finally, this parameter is passed in when executing the update method. After execution, the primary key Id is obtained by calling keyHolder.getKey().intValue(); <t> int insertSingleObjectDataReturnId(T data,String databaseName,StringtableName) is used to insert a single generic data into the database. data represents the generic data to be inserted, databaseName represents the name of the current database, and tableName represents the data table currently being processed. The primary key Id is returned. This method first converts data from generic T to Map<String,Object> Type of data, and then directly call the above insertSingleHashMapDataReturnId method, so far all database related operation functions have been realized.< / t>
