GyJdbc基于jdbctemplate的类似JPA的持久层框架封装，使用优势：
- Dao层0代码，再也不需要为Dao层的方法名称命名掉头发。
- 链式SQL配合lambda表达式，既装B又简洁。
- 强悍的SQL拼接，支持作者已知的所有SQL语法。
- 学习成本极低，靠近SQL语法，开发者使用起来会像平时一样写SQL一样简单。
- 提供类JPA语法，类MongoTemplate的SQL拼接语法
- 支持多数据源，多数据源的负载均衡，仅需一个注解或者一个方法调用。
step1.添加maven坐标
<dependency>
<groupId>io.github.springstudent</groupId>
<artifactId>GyJdbc</artifactId>
<version>1.4.1.RELEASE</version>
</dependency>step2.定义Pojo类，对应数据库中的一张表。
@Table注解，name定义pojo类与数据库表的关系，pk指定表的主键
@Table(name = "tb_user", pk = "id")
public class TbUser {
private String id;
private String name;
private String realName;
private String pwd;
private String email;
private String mobile;
private Date birth;
private Integer age;
private String career;
private Integer isActive = 0;
private Integer roleId;
} step3.定义Dao与DaoImpl，分别继承自EntityDao和EntityDaoImpl
public interface TbUserDao extends EntityDao<TbUser, String> {
}
@Repository
public class TbUserDaoImpl extends EntityDaoImpl<TbUser, String> implements TbUserDao {
}step4.在Service层注入Dao，使用EntityDao提供的方法完成增、删、改、查
增
void save(T t)throws Exception;
void batchSave(List<T> list)throws Exception;
int insertWithSql(SQL sql)throws Exception;删
void delete(Id id)throws Exception;
void deleteWithCriteria(Criteria criteria)throws Exception;
void batchDelete(List<Id> ids)throws Exception;
int deleteWithSql(SQL sql)throws Exception;
void truncate()throws Exception;
void drop()throws Exception;
void drunk(SQL sql)throws Exception;改
void update(T t)throws Exception;
void batchUpdate(List<T> list)throws Exception;
int updateWithSql(SQL sql)throws Exception;查
T queryOne(Id id)throws Exception;
List<T> queryAll()throws Exception;
PageResult<T> pageQuery(Page page)throws Exception;
PageResult<T> pageQueryWithCriteria(Page page,Criteria criteria)throws Exception;
List<T> queryWithCriteria(Criteria criteria)throws Exception;
<E> Result<E> queryWithSql(Class<E> clss,SQL sql)throws Exception;
List<Map<String, Object>>queryMapsWithSql(SQL sql)throws Exception;
<K, V> Map<K, V> queryMapWithSql(SQL sql,ResultSetExtractor<Map<K, V>>resultSetExtractor)throws Exception;Criteria语法示例:
//where name = 'zhouning'
new Criteria().where(TbUser::getName,"zhouning").andIfAbsent(TbUser::getName,null);
//where name in ('zhouning','yinhw')
new Criteria().in(TbUser::getName,Arrays.asList("zhouning","yinhw"));
//where age < 28 order by age desc
new Criteria().lt(TbUser::getAge,28).orderBy(new Sort(TbUser::getAge);
//where age < 20 and (name like '%zhou%' or realName like 'zhouning')
new Criteria().lt(TbUser::getAge,20).andCriteria(new Criteria().like(TbUser::getName,"zhou").orLike(TbUser::getRealName,"周"));SQL语法示例:
new SQL().select(TbUser::getName,TbUser::getEmail,TbUser::getRealName,TbUser::getMobile).from(TbUser.class).where(TbUser::getIsActive,1);
new SQL().select("age",countAs("age").as("num")).from(TbUser.class).orderBy(new Sort(TbUser::getAge)).groupBy(TbUser::getAge);
new SQL().update(TbUser.class).set(TbUser::getRealName,"元林").set(TbUser::getEmail,"[email protected]").where(TbUser::getName,"Smith");
new SQL().insertInto(TbAccount.class,"userName","realName").values("test","测试").values("test2","测试2");
new SQL().delete().from(TbUser.class).gt(TbUser::getAge,20);
//类似MongoTemplate的Criteria拼接
new SQL().select("*").from("table1").where("f1",1).and(Where.where("f2").like("a").or("f3").gte(1).and("f4").in(Arrays.asList(2,3,4)))
//类似JPA的Predict使用
List<WhereParam> params=new ArrayList<>();
params.add(WhereParam.where("f1").like("v1"));
params.add(WhereParam.where("f2").in(Arrays.asList(1,2,3)));
sql=new SQL().select("*").from("table2").and(Opt.AND,params);最终拦截的方法的签名为entityDao.xxxSql，即方法参数传入的是SQL，beforeBuild在构建sql和参数之前执行，而afterSql在构建sql之后执行。可以通过实现该接口方便的给sql批量添加一些通用的查询字段、更新字段sql审计的逻辑，以下是一个具体点的demo
@Component
public class SQLInterceptorImpl implements SQLInterceptor {
@Override
public void beforeBuild(SQLType sqlType, SqlModifier sqlModifier) throws Exception {
//粗粒度 通过sql类型统一添加需要更新和插入的字段
if (sqlType.equals(SQLType.Update)) {
if (sqlModifier.tableName().startsWith("sys_tb_")) {
sqlModifier.addUpdate("updateTime", new Date());
sqlModifier.addUpdate("updateUser", "admin");
}
} else if (sqlType.equals(SQLType.Insert)) {
if (sqlModifier.tableName().startsWith("sys_tb_")) {
sqlModifier.addInsert("createTime", new Date());
sqlModifier.addInsert("createUser", "admin");
sqlModifier.addInsert("updateTime", new Date());
sqlModifier.addInsert("updateUser", "admin");
}
}
//精细粒度 根据sqlId添加相应的更新字段和查询条件
if (sqlModifier.sqlId().equals("updateBirthAuto")) {
sqlModifier.addUpdate("isActive", 0);
} else if (sqlModifier.sqlId().equals("isDelete1")) {
sqlModifier.addAnd(Where.where("isActive").equal(1));
}
}
@Override
public void afterBuild(String sql, Object[] args) throws Exception {
//sql审计
System.out.println("sql:" sql " args:" ArrayUtils.toString(args));
}
}数据源配置文件
<bean id="sourceDs" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
...省略
</bean>
<bean id="targetDs" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
...省略
</bean>
<bean id="nightDs" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
...省略
</bean>
<bean id="dataSource" class="com.gysoft.jdbc.multi.JdbcRoutingDataSource">
<property name="targetDataSources">
<map>
<entry key="master" value-ref="sourceDs"/>
<entry key="slave" value-ref="targetDs"/>
<entry key="slave2" value-ref="nightDs"/>
</map>
</property>
<property name="dataSourceKeysGroup">
<map>
<entry key="masterGroup" value="master"/>
<entry key="slaveGroup" value="slave,slave2"/>
</map>
</property>
<property name="defaultLookUpKey" value="master"/>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>@Bindpoint注解绑定数据源(依赖于spring aop)
绑定方法或者类级别的数据源
//绑定数据源slaveGroup组，采用RandomLoadBalance策略（随机）的负载均衡策略选取数据源
@BindPoint(group = "slaveGroup", loadBalance = RandomLoadBalance.class)
//绑定指定slave2数据源
@BindPoint(key = "slave2")EntityDao.binxxx方法绑定数据源
绑定Sql级别的数据源
//SELECT * FROM tb_user where name in('zhouning','yinhw')将会在slave数据源上执行
List<TbUser> tbUsers=tbUserDao.bindKey("slave").queryWithCriteria(new Criteria().in(TbUser::getName,Arrays.asList("zhouning","yinhw")));
//UPDATE tb_user set realName = "元林",email = "[email protected]" WHERE name = "Smith"
//采用轮询负载均衡策略在masterGroup组中选择一个数据源执行update操作
tbUserDao.bindGroup("masterGroup",RoundbinLoadBalance.class).updateWithSql(new SQL().update(TbUser.class).set(TbUser::getRealName,"元林").set(TbUser::getEmail,"[email protected]").where(TbUser::getName,"Smith"));FAQ
数据源选择的优先级顺序：
entityDao.bindXxx > 方法上@BindPoint > 类上@BindPoint > JdbcRoutingDataSource.defaultLookUpKey