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>

          <dependency>
            <groupId>net.sf.json-lib</groupId>
            <artifactId>json-lib</artifactId>
          <version>2.4</version>

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

JfinalDbUtil  

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();
    }

     /**
     * 单层json转 record
     * @param json
     * @return
     */
    private Record parseSimpleJson(String json){
        JSONObject josub = JSONObject.fromObject(json);
        Set<String> s = josub.keySet();
        Record r = new Record();
        for (String each : s) {
            Object value = josub.get(each);
            if(value!=null && !"null".equals(value.toString())){
                r.set(each, value);
            }
        }
        return r;
    }


}