JDBC 的简单封装,以sqlite数据库为例

* 小驼峰会自动转下划线
* Column 注解会覆盖其他策略,就取name名字作为字段名
* Table 注解可以重新标记表名

1.主要方法就是save和list两个方法,调用实例如下

package xin.tom.esui.db;
import org.junit.Test;
import xin.tom.esui.entity.ConnectionEntity;

import java.util.List;


public class SqliteDataSourceConfigTest
{
    @Test
    public void testSave()
    {
        ConnectionEntity connectionEntity = new ConnectionEntity("爱的色放",
                "127.0.0.1",8866,"root","1234556","notuse");

       SqliteDataSourceConfig.save(connectionEntity);
    }

    @Test
    public void testList()
    {
        List<ConnectionEntity> list = 
        SqliteDataSourceConfig.list("select * from connections", ConnectionEntity.class);
        System.out.println(list);
    }

}

2.核心代码如下,有需要自己改

package xin.tom.esui.db;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;

import javax.persistence.Column;
import javax.persistence.Table;
import java.io.File;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

import static xin.tom.esui.util.StringUtils.*;

/**
 * @author guolinyuan
 */
public class SqliteDataSourceConfig
{
    // 主配置的数据库地址
    private static final String mainDBUrl = System.getProperty("user.home") + "/Documents//esui/esui.db";
    // 连接数据库的数据源
    private static final DruidDataSource dataSource = new DruidDataSource();

    static
    {
        //检测是否存在数据库文件,若没有,创建新的数据库文件
        createDBIfNotExit();

        // 数据源配置
        dataSource.setUrl("jdbc:sqlite:" + mainDBUrl);
        dataSource.setDriverClassName("org.sqlite.JDBC");
        dataSource.setValidationQuery("select 1");
        // 失败后重连的次数置为0,本地文件不可能失败
        dataSource.setConnectionErrorRetryAttempts(0);
        // 请求失败之后中断
        dataSource.setBreakAfterAcquireFailure(true);
    }

    /**
     * 检测是否存在数据库文件,若没有,创建新的数据库文件
     * todo 应该复制初始化的sqlite文件来处理,而不是创建新的空文件
     */
    @SuppressWarnings("ResultOfMethodCallIgnored")
    private static void createDBIfNotExit()
    {
        File file = new File(mainDBUrl);
        if (!file.exists())
        {
            try
            {
                file.getParentFile().mkdirs();
                file.createNewFile();
            }
            catch (IOException e)
            {
                e.printStackTrace();
            }
        }
    }

    /**
     * 执行sql,将结果存到指定的类型list中,
     * 如果字段匹配不到,则抛弃,支持@Column改变字段名
     *
     * @param sql 要执行的sql
     * @param clazz 指明数据类型
     * @param <T> 指明数据类型
     * @return
     */
    public static <T> List<T> list(String sql, Class<T> clazz)
    {
        List<T> list = new ArrayList<>();
        // 会自动关闭
        try (DruidPooledConnection conn = dataSource.getConnection();
             Statement statement = conn.createStatement())
        {
            //执行sql返回结果
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next())
            {
                //创建对象实例
                T po = clazz.newInstance();
                //查出目标对象所有的属性
                Field[] fields = clazz.getDeclaredFields();
                for (Field field : fields)
                {
                    //目标对象的类型名称,会拼接成类似于getInteger一类的方法名
                    String poTypeName = field.getType().getSimpleName();
                    //目标对象的属性名
                    String poFieldName = field.getName();
                    //目标对象的设置数据的方法名
                    String poSetMethodName = "set" + upperFirst(poFieldName);

                    //sql的对应字段名,@Column注解有的话,按照注解name值,否则就是属性名
                    String dbFieldName = getDbFieldName(field);
                    //ResultSet获取查询结果对象的值的方法名
                    String dbGetMethodName = getDbGetMethodName(poTypeName);

                    //从结果中获取实体字段名的值,如果有注解@Column照注解name值,否则就是属性名
                    //如果实体类型为RS中不存在get方法的类型,比如Hashmap,因为数据库没有hashmap类型对应
                    //但字段又匹配上了,直接抛弃此字段的注入
                    Object value;
                    try
                    {
                        //获取数据库字段值
                        Method rsGetMethod = ResultSet.class.getMethod(dbGetMethodName, String.class);
                        value = rsGetMethod.invoke(rs, dbFieldName);
                        //调用实体的set方法注入
                        //value为null时获取类型会空指针,直接不注入就行
                        if (value != null)
                        {
                            Method method = clazz.getMethod(poSetMethodName, value.getClass());
                            method.invoke(po, value);
                        }
                    }
                    catch (NoSuchMethodException ex)
                    {
                        //此字段无法注入,不处理
                    }
                }
                list.add(po);
            }
        }
        catch (SQLException | InstantiationException | IllegalAccessException | InvocationTargetException throwable)
        {
            throwable.printStackTrace();
        }

        return list;
    }

    /**
     * 按照指定对象插入到数据库,名称为类名
     * 字段为字段名
     * @param object
     */
    public static void save(Object object)
    {
        // 解析实体内容
        Class<?> clazz = object.getClass();
        Field[] fields = clazz.getDeclaredFields();
        String className = clazz.getName();
        String tableName;
        Table clazzAnnotation = clazz.getAnnotation(Table.class);
        if (clazzAnnotation != null)
        {
            tableName = clazzAnnotation.name();
        }
        else
        {
            tableName = lowerFirst(className);
        }
        Map<String, Object> valueMap = new LinkedHashMap<>(fields.length);
        for (Field field : fields)
        {
            String poFieldName = field.getName();
            String dbFieldName = getDbFieldName(field);
            try
            {
                //fixme 这里不处理is的情况
                Method method = clazz.getMethod("get" + upperFirst(poFieldName));
                Object value = method.invoke(object);
                String key;
                Column fieldAnnotation = field.getAnnotation(Column.class);
                if (fieldAnnotation != null)
                {
                    key = fieldAnnotation.name();
                }
                else
                {
                    key = dbFieldName;
                }

                valueMap.put(key, value);
            }
            catch (NoSuchMethodException | InvocationTargetException | IllegalAccessException e)
            {
                System.out.println("已忽略字段" + field + e.getMessage());
            }
        }

        //执行sql
        try (DruidPooledConnection conn = dataSource.getConnection();
             Statement statement = conn.createStatement())
        {
            String sql = buildInsertSql(valueMap, tableName);
            System.out.println(sql);
            boolean b = statement.execute(sql);
            System.out.println("rs: " + b);
        }
        catch (SQLException throwable)
        {
            throwable.printStackTrace();
        }
    }

    private static String getDbFieldName(Field field)
    {
        Column fieldAnnotation = field.getAnnotation(Column.class);
        if (fieldAnnotation != null)
        {
            return fieldAnnotation.name();
        }
        else
        {
            return humpToUnderline(field.getName());
        }
    }

    private static String getDbGetMethodName(String poTypeName)
    {
        String typeSuffix;
        if (poTypeName.equals("Integer"))
        {
            typeSuffix = "Int";
        }
        else
        {
            typeSuffix = poTypeName;
        }
        return "get" + typeSuffix;
    }

    private static String buildInsertSql(Map<String, Object> valueMap, String tableName)
    {
        //处理数据为2个StringBuilder,获取tableName,构造sql
        StringBuilder keys = new StringBuilder();
        StringBuilder values = new StringBuilder();
        valueMap.forEach((s, o) ->
        {
            keys.append(s).append(",");
            if (o != null)
            {
                values.append("'").append(o).append("',");
            }
            else
            {
                values.append("'null',");
            }
        });
        keys.deleteCharAt(keys.length() - 1);
        values.deleteCharAt(values.length() - 1);
        return "INSERT INTO " + tableName + " (" + keys + ") VALUES (" + values + ")";
    }
}

3.StringUtils类

package xin.tom.esui.util;

import java.util.Locale;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author guolinyuan
 */
public class StringUtils
{
    public static String upperFirst(String name)
    {
        char[] cs = name.toCharArray();
        cs[0] -= 32;
        return String.valueOf(cs);
    }

    public static String lowerFirst(String name)
    {
        char[] cs = name.toCharArray();
        cs[0] += 32;
        return String.valueOf(cs);
    }
    /**
     * 驼峰转下划线
     * @param str   目标字符串
     * @return: java.lang.String
     */
    public static String humpToUnderline(String str) {
        String regex = "([A-Z])";
        Matcher matcher = Pattern.compile(regex).matcher(str);
        while (matcher.find()) {
            String target = matcher.group();
            str = str.replaceAll(target, "_"+target.toLowerCase());
        }
        return str;
    }

    /**
     * 下划线转驼峰
     * @param str   目标字符串
     * @return: java.lang.String
     */
    public static String underlineToHump(String str) {
        String regex = "_(.)";
        Matcher matcher = Pattern.compile(regex).matcher(str);
        while (matcher.find()) {
            String target = matcher.group(1);
            str = str.replaceAll("_"+target, target.toUpperCase());
        }
        return str;
    }


}

4.引入代码包

 <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.2.8</version>
    </dependency>
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.22</version>
      <scope>provided</scope>
    </dependency>
    <dependency>
      <groupId>javax.persistence</groupId>
      <artifactId>javax.persistence-api</artifactId>
      <version>2.2</version>
    </dependency>