Vertx Mysql数据库优化 (七)

项目github地址：https://github.com/fotocj007/VertxWebApi

前一章节完成了mysql客户端的链接和基本重新，每次重新都需要写sql语句，这章优化一下mysql的一些基本处理。

优先将实体加载到内存中，需要进行mysql操作时直接读取，不需要每次都写sql语句。

一：定义一些注解

1 //定义主键
2 @Documented
3 @Target({ElementType.FIELD})
4 @Retention(RetentionPolicy.RUNTIME)
5 public @interface Id {
6 }

View Code

1 //定义表
2 @Documented
3 @Target({ElementType.TYPE})
4 @Retention(RetentionPolicy.RUNTIME)
5 public @interface Table {
6     String name() default "";
7 }

View Code

1 //定义需要忽略的字段--实体类有,数据库没有的字段
2 @Documented
3 @Target({ElementType.FIELD})
4 @Retention(RetentionPolicy.RUNTIME)
5 public @interface Transient {
6 }

View Code

1 //定义主键是否自动递增
2 @Documented
3 @Target({ElementType.FIELD})
4 @Retention(RetentionPolicy.RUNTIME)
5 public @interface GeneratedValue {
6 }

View Code

二：定义实体类的抽象基类

 1 /**
 2  *
 3  * @param <ID> 主键
 4  * @param <K>  查询key名称
 5  */
 6 
 7 public abstract class BaseEntity<ID extends Serializable,K extends Serializable> {
 8     public abstract ID getId();
 9 
10     public abstract void setId(ID id);
11     // 分库分表依赖id
12     public abstract K splitId();
13 }

View Code

三：解析注解--重要类

  1 public class MysqlDbDao {
  2     protected Logger logger = LoggerFactory.getLogger(MysqlDbDao.class);
  3 
  4     protected MySQLUtil mySQLPool;
  5 
  6     protected String DB_SPLIT = "";
  7     // 表名
  8     protected Map<Class,String> tableMap;
  9     // 主键名
 10     protected Map<Class,String> primaryKeyMap;
 11     // insert字段列表
 12     protected Map<Class, List<String>> insertFieldMap;
 13     // insert sql
 14     protected Map<Class,String> insertSqlMap;
 15     //  update字段列表
 16     protected Map<Class,List<String>> updateFieldMap;
 17     //  update sql
 18     protected Map<Class,String> updateSqlMap;
 19 
 20     public MysqlDbDao(String DB_SPLIT,MySQLUtil mySQLPool){
 21         this.DB_SPLIT = DB_SPLIT;
 22         this.mySQLPool = mySQLPool;
 23         tableMap = new HashMap<>();
 24         primaryKeyMap = new HashMap<>();
 25         insertFieldMap = new HashMap<>();
 26         insertSqlMap = new HashMap<>();
 27         updateFieldMap = new HashMap<>();
 28         updateSqlMap = new HashMap<>();
 29     }
 30 
 31     /*************************
 32      * 加载分库分表的一些基本信息
 33      * 实体类的表,主键,更新和插入的字段(实体类的字段)
 34      */
 35     public void loadAllDBInfo(List<String> fileList){
 36         try{
 37             for(String fileName : fileList){
 38                 Class classes = Class.forName(fileName);
 39                 tableMap.put(classes,getTableName(fileName));
 40                 primaryKeyMap.put(classes,getUpdatePrimaryKey(fileName));
 41                 insertFieldMap.put(classes,getInsertFiled(fileName));
 42                 insertSqlMap.put(classes,getInsertSql(fileName));
 43                 updateFieldMap.put(classes,getUpdateFiled(fileName));
 44                 updateSqlMap.put(classes,getUpdateSql(fileName));
 45             }
 46         }catch (Exception ex){
 47             throw new RuntimeException(ex);
 48         }
 49     }
 50 
 51     /*************************
 52      * 插入新数据
 53      */
 54     public void saveBaseEntity(BaseEntity entity, Handler<AsyncResult<Long>> handler){
 55         StringBuffer sql = new StringBuffer("insert into ");
 56         sql.append(DB_SPLIT).append(".").append(tableMap.get(entity.getClass())).append(" ")
 57                 .append(insertSqlMap.get(entity.getClass()));
 58         Tuple insertParams = genInsertFieldValues(entity);
 59 
 60         mySQLPool.getConfigClient()
 61                 .preparedQuery(sql.toString())
 62                 .execute(insertParams, ar -> {
 63                     if(ar.succeeded()){
 64                         RowSet<Row> rows = ar.result();
 65                         if(rows.size() > 0){
 66                             long lastInsertId = rows.property(MySQLClient.LAST_INSERTED_ID);
 67                             handler.handle(Future.succeededFuture(lastInsertId));
 68                             return;
 69                         }
 70 
 71                         handler.handle(Future.succeededFuture(0L));
 72                     }else {
 73                         handler.handle(Future.failedFuture(ar.cause()));
 74                         logger.error("saveBaseEntity:"+ JsonObject.mapFrom(entity)+",sql="+sql.toString(),ar.cause());
 75                     }
 76                 });
 77     }
 78 
 79     /*************************
 80      * 更新一条数据
 81      */
 82     public void updateBaseEntity(BaseEntity entity,Handler<AsyncResult<Long>> handler){
 83         StringBuffer sql = new StringBuffer("update ");
 84         sql.append(DB_SPLIT).append(".").append(tableMap.get(entity.getClass())).append(" ")
 85                 .append(updateSqlMap.get(entity.getClass()));
 86         Tuple updateParams = genUpdateFieldValues(entity);
 87 
 88         mySQLPool.getConfigClient()
 89                 .preparedQuery(sql.toString())
 90                 .execute(updateParams,saveRes -> {
 91                     if(saveRes.succeeded()){
 92                         long num = saveRes.result().size();
 93                         handler.handle(Future.succeededFuture(num));
 94                     }else {
 95                         handler.handle(Future.failedFuture(saveRes.cause()));
 96                         logger.error("updateBaseEntity:"+JsonObject.mapFrom(entity)+",sql="+sql.toString(),saveRes.cause());
 97                     }
 98                 });
 99     }
100 
101     @SuppressWarnings("unchecked")
102     private String getTableName(String bean) {
103         try {
104             Class clz = Class.forName(bean);
105             boolean annotationPresent = clz.isAnnotationPresent(Table.class);
106             if (annotationPresent) {
107                 Table table = (Table) clz.getAnnotation(Table.class);
108                 return table.name();
109             }
110         } catch (ClassNotFoundException e) {
111             e.printStackTrace();
112             return null;
113         }
114         return "";
115     }
116 
117     /*************************
118      * 获取更新sql的字符串
119      */
120     private String getUpdateSql(String bean) {
121         StringBuilder sb = new StringBuilder();
122         sb.append(" set ");
123         List<String> fieldList = getUpdateFiled(bean);
124         for(String str : fieldList) {
125             sb.append(str.split(",")[0]).append("=?,");
126         }
127         sb.deleteCharAt(sb.toString().lastIndexOf(","));
128         sb.append(" where ");
129         String primaryKey = getUpdatePrimaryKey(bean);
130         sb.append(primaryKey.split(",")[0]).append("=? ");
131         return sb.toString();
132     }
133 
134     /*************************
135      * 获取跟新sql的字段
136      */
137     private List<String> getUpdateFiled(String bean){
138         List<String> list = new ArrayList<>();
139         try {
140             Class clz = Class.forName(bean);
141             Field[] strs = clz.getDeclaredFields();
142             for(Field field : strs) {
143                 if(field.isAnnotationPresent(Transient.class)){
144                     continue;
145                 }
146                 String protype =  field.getType().toString();
147                 boolean annotationPresent  = field.isAnnotationPresent(Id.class);
148                 if (!annotationPresent) {
149                     list.add(field.getName()+","+protype.substring(protype.lastIndexOf(".")+1));
150                 }
151             }
152         }catch (ClassNotFoundException e) {
153             e.printStackTrace();
154         }
155         return list;
156     }
157 
158     /*************************
159      * 跟新sql的主键
160      */
161     private String getUpdatePrimaryKey(String bean) {
162         try {
163             Class clz = Class.forName(bean);
164             Field[] strs = clz.getDeclaredFields();
165             for(Field field : strs) {
166                 String protype =  field.getType().toString();
167                 boolean annotationPresent  = field.isAnnotationPresent(Id.class);
168                 if (annotationPresent) {
169                     return field.getName()+","+protype.substring(protype.lastIndexOf(".")+1);
170                 }
171             }
172         }catch (ClassNotFoundException e) {
173             e.printStackTrace();
174         }
175 
176         return "";
177     }
178 
179     /*************************
180      * 插入新数据的sql字符串
181      */
182     private  String getInsertSql(String bean){
183         StringBuilder sb = new StringBuilder();
184         sb.append(" (");
185         List<String> fieldList = getInsertFiled(bean);
186         for(String str : fieldList) {
187             sb.append(str.split(",")[0]).append(",");
188         }
189         sb.deleteCharAt(sb.toString().lastIndexOf(","));
190         sb.append(" ) value (");
191         for(int i = 0;i <fieldList.size();i++) {
192             if(i == fieldList.size() -1) {
193                 sb.append("? ");
194             }else {
195                 sb.append("?,");
196             }
197         }
198         sb.append(") ");
199         return sb.toString();
200     }
201 
202     /*************************
203      * 插入新数据sql的插入字段
204      */
205     private List<String> getInsertFiled(String bean){
206         List<String> list = new ArrayList<>();
207         try {
208             Class clz = Class.forName(bean);
209             Field[] strs = clz.getDeclaredFields();
210             for(Field field : strs) {
211                 if(field.isAnnotationPresent(Transient.class)){
212                     continue;
213                 }
214 
215                 String protype =  field.getType().toString();
216                 boolean annotationPresent  = field.isAnnotationPresent(Id.class);
217                 if (annotationPresent) {
218                     boolean generateAnnotation  = field.isAnnotationPresent(GeneratedValue.class);
219                     if(!generateAnnotation) {
220                         list.add(field.getName()+","+protype.substring(protype.lastIndexOf(".")+1));
221                     }
222                 }else {
223                     list.add(field.getName()+","+protype.substring(protype.lastIndexOf(".")+1));
224                 }
225             }
226         }catch (ClassNotFoundException e) {
227             e.printStackTrace();
228         }
229         return list;
230     }
231 
232     /*************************
233      * 插入新数据sql的值(实体类)
234      */
235     protected Tuple genInsertFieldValues(BaseEntity info) {
236         JsonObject json = JsonObject.mapFrom(info);
237         String name = "";
238         String type = "";
239         List<Object> sb = new ArrayList<>(5);
240         List<String> fieldList = insertFieldMap.get(info.getClass());
241         for(String str : fieldList) {
242             name = str.split(",")[0];
243             type = str.split(",")[1];
244             switch (type) {
245                 case "long":
246                     sb.add(json.getLong(name,0L));
247                     break;
248                 case "int":
249                 case "byte":
250                     sb.add(json.getInteger(name,0));
251                     break;
252                 case "short":
253                     sb.add(json.getInteger(name,0).shortValue());
254                     break;
255                 case "float":
256                     sb.add(json.getFloat(name,0f));
257                     break;
258                 case "String":
259                     sb.add(json.getString(name,""));
260                     break;
261                 default:
262                     if(json.getValue(name) != null){
263                         sb.add(json.getValue(name).toString());
264                     }else {
265                         sb.add(json.getValue(""));
266                     }
267                     break;
268             }
269         }
270         json = null;
271 
272         return Tuple.tuple(sb);
273     }
274 
275     /*************************
276      * 跟新新数据sql的值(实体类)
277      */
278     protected Tuple genUpdateFieldValues(BaseEntity info) {
279         JsonObject json = JsonObject.mapFrom(info);
280         String name = "";
281         String type = "";
282         List<Object> sb = new ArrayList<>(5);
283         List<String> fieldList = updateFieldMap.get(info.getClass());
284         for(String str : fieldList) {
285             name = str.split(",")[0];
286             type = str.split(",")[1];
287             switch (type) {
288                 case "long":
289                     sb.add(json.getLong(name,0L));
290                     break;
291                 case "int":
292                 case "byte":
293                     sb.add(json.getInteger(name,0));
294                     break;
295                 case "short":
296                     sb.add(json.getInteger(name,0).shortValue());
297                     break;
298                 case "float":
299                     sb.add(json.getFloat(name,0f));
300                     break;
301                 case "String":
302                     sb.add(json.getString(name,""));
303                     break;
304                 default:
305                     if(json.getValue(name) != null){
306                         sb.add(json.getValue(name).toString());
307                     }else {
308                         sb.add(json.getValue(""));
309                     }
310                     break;
311             }
312         }
313         String primaryKey = primaryKeyMap.get(info.getClass());
314         name = primaryKey.split(",")[0];
315         type = primaryKey.split(",")[1];
316         if(type.equals("long")) {
317             sb.add(json.getLong(name,0L));
318         }else if(type.equals("int")) {
319             sb.add(json.getInteger(name,0));
320         }else {
321             sb.add(json.getString(name,""));
322         }
323         json = null;
324         return Tuple.tuple(sb);
325     }
326 }

View Code

四：调整PlayerInfo实体类

 1 @Table(name = "player_info")
 2 public class PlayerInfo extends BaseEntity<Long,Long>{
 3     @Id
 4     @GeneratedValue
 5     private long id;
 6 
 7     private String userName;
 8 
 9     private int age;
10 
11     @Override
12     public Long getId() {
13         return id;
14     }
15 
16     @Override
17     public void setId(Long aLong) {
18         id = aLong;
19     }
20 
21     @Override
22     public Long splitId() {
23         return id;
24     }
25 
26     public void setId(long id) {
27         this.id = id;
28     }
29 
30     public String getUserName() {
31         return userName;
32     }
33 
34     public void setUserName(String userName) {
35         this.userName = userName;
36     }
37 
38     public int getAge() {
39         return age;
40     }
41 
42     public void setAge(int age) {
43         this.age = age;
44     }
45 }

View Code

五：修改PlayerDao帮助类

 1 public class PlayerDao extends MysqlDbDao{
 2     protected Logger logger = LoggerFactory.getLogger(PlayerDao.class);
 3 
 4     public PlayerDao(String DB_SPLIT, MySQLUtil mySQLPool) {
 5         super(DB_SPLIT,mySQLPool);
 6 
 7         loadAllDBInfo();
 8     }
 9 
10     /*************************
11      * 加载需要分库分表的实体类
12      */
13     private void loadAllDBInfo(){
14         List<String> classList = new ArrayList<>();
15         classList.add("com.webser.db.PlayerInfo");
16 
17         super.loadAllDBInfo(classList);
18     }
19 
20     /*************************
21      * 查询数据
22      * 根据 实体类T获取数据并实例化
23      */
24     public <T> void queryConfigList(String sql, Class<T> classes, Handler<AsyncResult<List<T>>> handler){
25         mySQLPool.getConfigClient().query(sql)
26                 .execute(qRes -> {
27                     if(qRes.succeeded()){
28                         List<T> lists = new ArrayList<>();
29 
30                         RowSet<Row> result = qRes.result();
31                         List<String> col = qRes.result().columnsNames();
32 
33                         for (Row row : result) {
34                             JsonObject json = new JsonObject();
35                             for (String str : col) {
36                                 json.put(str,row.getValue(str));
37                             }
38                             T entity = new JsonObject(json.toString()).mapTo(classes);
39                             lists.add(entity);
40                         }
41 
42                         handler.handle(Future.succeededFuture(lists));
43                     }else {
44                         handler.handle(Future.failedFuture(qRes.cause()));
45                         logger.error("--error queryConfigList----- " + sql, qRes.cause());
46                     }
47                 });
48     }
49 }

View Code

六：测试一下

1：修改DemoHandler

 1 public class DemoHandler implements InterHandler {
 2     @Override
 3     public void handler(AbstractUpMessage up, HttpServerResponse resp) {
 4         //上传参数
 5         DemoRequest request = (DemoRequest)up;
 6         System.out.println("上传参数:"+ request.name + "-" + request.age);
 7 
 8 
 9 //        String sql = "select * from " + Configure.getInstance().mysqlConfig.configDbName + ".player_info ";
10 //        PlayerDao client = Configure.getInstance().daoManager.getPlayerDao();
11 //        client.queryConfigList(sql, PlayerInfo.class, res -> {
12 //            List<PlayerInfo> lists = res.result();
13 //            for(PlayerInfo item : lists){
14 //                System.out.println(item.getUserName() + "---" + item.getAge());
15 //            }
16 //        });
17 
18 
19         PlayerInfo info = new PlayerInfo();
20         info.setUserName("kkkkkdd");
21         info.setAge(100);
22 
23         PlayerDao client = Configure.getInstance().daoManager.getPlayerDao();
24         client.saveBaseEntity(info,res -> {
25 
26         });
27 
28 
29         //返回数据
30         String n = "cscscs---";
31         String in = "info ---";
32         //编码返回json
33         DemoResponse response = new DemoResponse(getMessageId(),n,in);
34         response.encode();
35         resp.end(response.SendMessage());
36     }
37 
38     @Override
39     public short getMessageId() {
40         return HandlerCode.DEMO_V1;
41     }
42 }

View Code

2：运行服务端，调用接口

项目结构