Jfinal操作資料庫的基本封裝

來源

JFinal v4.9 開發手冊

maven依賴

<!--jfinal配置開始-->
        <!--mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.8</version>
        </dependency>
        <!-- jfinal -->
        <dependency>
            <groupId>com.jfinal</groupId>
            <artifactId>jfinal</artifactId>
            <version>3.5</version>
        </dependency>
        <!-- druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.20</version>
        </dependency>

db.properties配置

#test2
jdbc.url=jdbc:mysql://192.168.xx.xx:3307/gd_data_facebook?characterEncoding=utf8
jdbc.user=xx
jdbc.pwd=xx
maxActive=10

JfinalDbUtil

package com.isi.utils;

import com.jfinal.plugin.activerecord.*;
import com.jfinal.plugin.activerecord.dialect.AnsiSqlDialect;
import com.jfinal.plugin.druid.DruidPlugin;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.*;

public class JfinalDbUtil {
    public static final String source_name = "db1";

    static {
        initSource("./conf/db.properties",source_name);
    }

    public static DbPro getDb(String source_name) {
        return Db.use(source_name);
    }

    private static void initSource(String sourcePath,String source_name) {
//        PropKit.use(new File(sourcePath));
        Properties prop = loadOutProp(sourcePath);
        String jdbc_url = prop.getProperty("jdbc.url");
        String jdbc_user = prop.getProperty("jdbc.user");
        String jdbc_password = prop.getProperty("jdbc.pwd");
        DruidPlugin dp = new DruidPlugin(jdbc_url, jdbc_user, jdbc_password);
        dp.setDriverClass("com.mysql.jdbc.Driver");
        ActiveRecordPlugin arp = new ActiveRecordPlugin(source_name, dp);
        arp.setShowSql(true);//列印出執行的sql
        arp.setDialect(new AnsiSqlDialect());
        // 不區分大小寫
        arp.setContainerFactory(new CaseInsensitiveContainerFactory(true));
        dp.start();
        arp.start();

    }


    /**
     * 加載外部檔案
     *
     * @param filepath
     * @return
     */
    public static Properties loadOutProp(String filepath) {
        Properties properties = new Properties();
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(filepath);
            properties.load(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return properties;
    }



    /**
     * Record 轉 任意執行個體類
     *滿足資料庫列名和實體名除了下劃線無差別的映射
     * @param
     * @param record
     * @return
     */
    public static <T> T recordToObj(Class<T> clazz, Record record) {
        // 擷取JavaBean裡面的所有屬性
        Field[] field = clazz.getDeclaredFields();
//        Set<String> colunms = formatNames(record.getColumns().keySet());

        Map<String, String> nameMaps = getNameMaps(record.getColumns().keySet());

        try {
            T obj = clazz.newInstance();
            for (Field f : field) {
                // 擷取該字段名稱
                String name = f.getName();
                String typeName = f.getType().getSimpleName();
//                    System.out.println("typeName======"+typeName);
                // 判斷該字段是否在ResultSet的字段裡，在的話才去進行指派操作
                // 如果不進行判斷的話，在JavaBean字段比ResultSet字段多的情況下，會抛異常
                String formatKey = formatStr(name);
                if (nameMaps.containsKey(formatKey)) {
                    // 判斷是否查詢到對應的值
                    name=nameMaps.get(formatKey);
                    if (record.get(name) != null) {
                        // 跳過檢查，這裡通路的時私有屬性
                        f.setAccessible(true);
                        if ("String".equals(typeName)) {
                            // 将查詢到的值付給對應的屬性
                            f.set(obj, record.getStr(name));
                        } else if ("Long".equals(typeName)) {
                            // 将查詢到的值付給對應的屬性
                            f.set(obj, record.getLong(name));
                        } else if ("Integer".equals(typeName)) {
                            // 将查詢到的值付給對應的屬性
                            f.set(obj, record.getInt(name));
                        } else if ("Date".equals(typeName)) {
                            // 将查詢到的值付給對應的屬性
                            f.set(obj, new Date(record.getTimestamp(name).getTime()));
                        } else if ("Boolean".equals(typeName)) {
                            // 将查詢到的值付給對應的屬性
                            f.set(obj, record.getBoolean(name));
                        } else {
                            f.set(obj, record.get(name));
                        }
                    }
                }
            }
            return obj;
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("Record 轉 T 異常... " + e);
        }

        return null;
    }

    //Object轉record
    public static Record objToRecord(Object obj) {
        Record record = new Record();
        try {
            Class<?> clazz = obj.getClass();
            System.out.println(clazz);
            for (Field field : clazz.getDeclaredFields()) {
                field.setAccessible(true);
                String fieldName = field.getName();
                Object value = field.get(obj);
                if (value != null) {
                    record.set(fieldName, value);
                }
            }
        } catch (Exception e) {
            System.out.println("object to map error " + e.getMessage());
            return null;
        }
        return record;
    }

    /**
     * @param obj 滿足資料庫列名和實體名除了下劃線無差別的映射
     * @param columns 資料庫列名
     * @return
     */
    public static Record objToRecordStrict(Object obj, Set<String> columns) {
        Record record = new Record();
        Map<String, String> nameMaps = getNameMaps(columns);
        try {
            Class<?> clazz = obj.getClass();
            System.out.println(clazz);
            for (Field field : clazz.getDeclaredFields()) {
                field.setAccessible(true);
                String fieldName = field.getName();
                Object value = field.get(obj);
                String formatField = formatStr(fieldName);
                if (value != null && nameMaps.containsKey(formatField)) {
                    record.set(nameMaps.get(formatField), value);
                }
            }
        } catch (Exception e) {
            System.out.println("object to map error " + e.getMessage());
            return null;
        }
        return record;
    }

    public static Set<String> getColumns(String source_name, String table_name) {
        List<Record> records = Db.use(source_name).find("show columns from "+ table_name);
        Set<String> columns=new HashSet<>();
        for (Record record : records) {
            columns.add(record.getStr("Field"));
        }
        return columns;

    }

   /* private static Set<String> formatNames(Set<String> names) {
        Set<String> resultSet = new HashSet<>();
        for (String name : names) {
            resultSet.add(formatStr(name));
        }
        return resultSet;
    }*/

    /**
     * 擷取格式化後的字段名和原始字段名之間的映射
     * @param names
     * @return
     */
    private static Map<String,String> getNameMaps(Set<String> names) {
        Map<String,String> map=new HashMap<>();
        for (String name : names) {
          map.put(formatStr(name),name);
        }
        return map;
    }

    private static String formatStr(String str) {
        return str.replace("_", "").toLowerCase();
    }


}

jfinal+hutool的setting實作簡潔多資料源配置

額外引入hutool依賴

<!--hutool-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.6.0</version>
        </dependency>

db.setting檔案

[internet_data]
jdbc.url=jdbc:mysql://x.x.x.x:3307/internet_data?characterEncoding=utf8
jdbc.user=xx
jdbc.pwd=xx
maxActive=10

[event_caiji]
jdbc.url=jdbc:mysql://x.x.x.x:3307/event_caiji?characterEncoding=utf8
jdbc.user=xx
jdbc.pwd=xx
maxActive=10

package com.wg.utils;

import cn.hutool.core.util.CharsetUtil;
import cn.hutool.setting.Setting;
import cn.hutool.setting.dialect.Props;
import com.jfinal.plugin.activerecord.*;
import com.jfinal.plugin.activerecord.dialect.AnsiSqlDialect;
import com.jfinal.plugin.druid.DruidPlugin;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.*;

public class JfinalDbUtil {
    public static final String internet_data = "internet_data"; //和setting的group name保持一緻
    public static final String event_caiji = "event_caiji";
    static {
        initSource("./conf/db.setting");
    }
    public static DbPro getDb(String source_name) {
        return Db.use(source_name);
    }
    private static void initSource(String sourcePath) {
        Setting setting = new Setting(new File(sourcePath), CharsetUtil.CHARSET_UTF_8,false);
        List<String> groups = setting.getGroups();
        for (String group : groups) {
            Props prop = setting.getProps(group);
            String jdbc_url = prop.getProperty("jdbc.url");
            String jdbc_user = prop.getProperty("jdbc.user");
            String jdbc_password = prop.getProperty("jdbc.pwd");
            DruidPlugin dp = new DruidPlugin(jdbc_url, jdbc_user, jdbc_password);
            dp.setDriverClass("com.mysql.jdbc.Driver");
            ActiveRecordPlugin arp = new ActiveRecordPlugin(group, dp);
            arp.setShowSql(true);//列印出執行的sql
            arp.setDialect(new AnsiSqlDialect());
            arp.setContainerFactory(new CaseInsensitiveContainerFactory(true));// 不區分大小寫
            dp.start();
            arp.start();
        }
    }


  /*  private static void initSource(String sourcePath,String source_name) {
        Properties prop = loadOutProp(sourcePath);
        String jdbc_url = prop.getProperty("jdbc.url");
        String jdbc_user = prop.getProperty("jdbc.user");
        String jdbc_password = prop.getProperty("jdbc.pwd");
        DruidPlugin dp = new DruidPlugin(jdbc_url, jdbc_user, jdbc_password);
        dp.setDriverClass("com.mysql.jdbc.Driver");
        ActiveRecordPlugin arp = new ActiveRecordPlugin(source_name, dp);
        arp.setShowSql(true);//列印出執行的sql
        arp.setDialect(new AnsiSqlDialect());
        arp.setContainerFactory(new CaseInsensitiveContainerFactory(true));// 不區分大小寫
        dp.start();
        arp.start();
    }*/


    /**
     * 加載外部檔案
     *
     * @param filepath
     * @return
     */
    public static Properties loadOutProp(String filepath) {
        Properties properties = new Properties();
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(filepath);
            properties.load(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return properties;
    }



    /**
     * Record 轉 任意執行個體類
     *滿足資料庫列名和實體名除了下劃線無差別的映射
     * @param
     * @param record
     * @return
     */
    public static <T> T recordToObj(Class<T> clazz, Record record) {
        // 擷取JavaBean裡面的所有屬性
        Field[] field = clazz.getDeclaredFields();
//        Set<String> colunms = formatNames(record.getColumns().keySet());

        Map<String, String> nameMaps = getNameMaps(record.getColumns().keySet());

        try {
            T obj = clazz.newInstance();
            for (Field f : field) {
                // 擷取該字段名稱
                String name = f.getName();
                String typeName = f.getType().getSimpleName();
//                    System.out.println("typeName======"+typeName);
                // 判斷該字段是否在ResultSet的字段裡，在的話才去進行指派操作
                // 如果不進行判斷的話，在JavaBean字段比ResultSet字段多的情況下，會抛異常
                String formatKey = formatStr(name);
                if (nameMaps.containsKey(formatKey)) {
                    // 判斷是否查詢到對應的值
                    name=nameMaps.get(formatKey);
                    if (record.get(name) != null) {
                        // 跳過檢查，這裡通路的時私有屬性
                        f.setAccessible(true);
                        if ("String".equals(typeName)) {
                            // 将查詢到的值付給對應的屬性
                            f.set(obj, record.getStr(name));
                        } else if ("Long".equals(typeName)) {
                            // 将查詢到的值付給對應的屬性
                            f.set(obj, record.getLong(name));
                        } else if ("Integer".equals(typeName)) {
                            // 将查詢到的值付給對應的屬性
                            f.set(obj, record.getInt(name));
                        } else if ("Date".equals(typeName)) {
                            // 将查詢到的值付給對應的屬性
                            f.set(obj, new Date(record.getTimestamp(name).getTime()));
                        } else if ("Boolean".equals(typeName)) {
                            // 将查詢到的值付給對應的屬性
                            f.set(obj, record.getBoolean(name));
                        } else {
                            f.set(obj, record.get(name));
                        }
                    }
                }
            }
            return obj;
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("Record 轉 T 異常... " + e);
        }

        return null;
    }

    //Object轉record
    public static Record objToRecord(Object obj) {
        Record record = new Record();
        try {
            Class<?> clazz = obj.getClass();
            System.out.println(clazz);
            for (Field field : clazz.getDeclaredFields()) {
                field.setAccessible(true);
                String fieldName = field.getName();
                Object value = field.get(obj);
                if (value != null) {
                    record.set(fieldName, value);
                }
            }
        } catch (Exception e) {
            System.out.println("object to map error " + e.getMessage());
            return null;
        }
        return record;
    }

    /**
     * @param obj 滿足資料庫列名和實體名除了下劃線無差別的映射
     * @param columns 資料庫列名
     * @return
     */
    public static Record objToRecordStrict(Object obj, Set<String> columns) {
        Record record = new Record();
        Map<String, String> nameMaps = getNameMaps(columns);
        try {
            Class<?> clazz = obj.getClass();
            System.out.println(clazz);
            for (Field field : clazz.getDeclaredFields()) {
                field.setAccessible(true);
                String fieldName = field.getName();
                Object value = field.get(obj);
                String formatField = formatStr(fieldName);
                if (value != null && nameMaps.containsKey(formatField)) {
                    record.set(nameMaps.get(formatField), value);
                }
            }
        } catch (Exception e) {
            System.out.println("object to map error " + e.getMessage());
            return null;
        }
        return record;
    }

    public static Set<String> getColumns(String source_name, String table_name) {
        List<Record> records = Db.use(source_name).find("show columns from "+ table_name);
        Set<String> columns=new HashSet<>();
        for (Record record : records) {
            columns.add(record.getStr("Field"));
        }
        return columns;

    }

   /* private static Set<String> formatNames(Set<String> names) {
        Set<String> resultSet = new HashSet<>();
        for (String name : names) {
            resultSet.add(formatStr(name));
        }
        return resultSet;
    }*/

    /**
     * 擷取格式化後的字段名和原始字段名之間的映射
     * @param names
     * @return
     */
    private static Map<String,String> getNameMaps(Set<String> names) {
        Map<String,String> map=new HashMap<>();
        for (String name : names) {
          map.put(formatStr(name),name);
        }
        return map;
    }

    private static String formatStr(String str) {
        return str.replace("_", "").toLowerCase();
    }


}

Jfinal操作資料庫的基本封裝

jfinal+hutool的setting實作簡潔多資料源配置

繼續閱讀

Java小案例——随機數猜測随機數猜測

nginx location中斜線的位置的重要性

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

Java String.format方法的簡單使用

neo4j之cypher使用文檔

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

spark/scala關于【資源檔案】加載方法概述外部檔案加載方案測試資源檔案打包入jar包中小結

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

AOP程式設計_Android優雅權限架構(1)概念基礎，2021金三銀四前言正文大綱正文

Effective Java 8:通用程式設計

OOM三種類型

工廠模式-三種類型

【遞歸】高效率求2的n次幂

win10本地scala和spark安裝安裝scala安裝spark

scala (3) Function 和 Method