* 小驼峰会自动转下划线
* 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>