sql2o-plus

🍌sql2o-plus a simple object mapper for java

Java版本 1.8

Maven

pom.xml

releases 版本

<dependency>
    <groupId>io.github.cotide</groupId>
    <artifactId>sql2o-plus</artifactId>
    <version>1.0.3</version>
</dependency> 

snapshots 版本

<repository>
    <id>oss-snapshots</id>
    <url>https://oss.sonatype.org/content/repositories/snapshots</url>
    <releases>
        <enabled>false</enabled>
    </releases>
    <snapshots>
        <enabled>true</enabled>
    </snapshots>
</repository> 
<dependency>
    <groupId>io.github.cotide</groupId>
    <artifactId>sql2o-plus</artifactId>
    <version>1.0.4-SNAPSHOT</version>
</dependency>

特点

• sql2o驱动
• 集成SQL Lambda表达式
• 集成SQL分页
• CRUD 封装/简化调用方法
• 支持事务

实体映射

package com.sqltest.model;

import com.sqltest.model.enums.EnumGroup;
import com.sqltest.model.enums.EnumUserStatus;
import com.sqltest.model.enums.EnumVipLevel;
import io.github.cotide.dapper.basic.domain.Entity; 
import io.github.cotide.dapper.core.attr.Column;
import io.github.cotide.dapper.core.attr.Ignore;
import io.github.cotide.dapper.core.attr.PrimaryKey;
import io.github.cotide.dapper.core.attr.Table;

import java.util.Date;

/**
 * 用户信息
 */
@lombok.Getter
@lombok.Setter
@Table("user_info")
public class UserInfo extends Entity {

    @PrimaryKey("user_id")
    private int id;

    @Column("user_Name")
    private String name;

    @Column("password")
    private String pwd;

    @Column("status")
    private EnumUserStatus status;

    @Column("level")
    private EnumVipLevel level;

    @Column("`group`")
    private EnumGroup group;

    private int login;

    @Column("create_time")
    private Date createTime;

    @Ignore
    private String other;
}

枚举定义

• IntegerEnum 整数型枚举存储-> 例子
• StringEnum 字符串型枚举存储-> 例子

注解描述

• @Table (表名)
• @PrimaryKey (主键)
• @Column (字段名,与数据库字段名称一致可不标记)
• @Ignore (忽略字段)

Dto实体

package com.sqltest.dto;

import com.sqltest.model.enums.EnumGroup;
import com.sqltest.model.enums.EnumUserStatus;
import com.sqltest.model.enums.EnumVipLevel; 
import lombok.Data;
import java.util.Date;


@Data
public class UserInfoDto {
 
    @Column("user_id")
    private int id;

    @Column("user_name")
    private String name;

    private int login;

    private EnumUserStatus status;

    @Column("`group`")
    private EnumGroup group;

    private EnumVipLevel level;

    private Date createTime;

    @Ignore
    private String other;
}

初始化

String url = "jdbc:mysql://192.168.1.100:3307/g_main_test?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&useSSL=false";
String user ="test";
String pass ="123456";

// Mysql
protected Database getDatabase() { 
   return new Database(url,user,pass);
} 

// Druid DataSource
protected Database getDruidDatabase() {
    DruidDataSource dataSource = new DruidDataSource();
    dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
    dataSource.setUrl(url);
    dataSource.setUsername(user);
    dataSource.setPassword(pass);
    return new Database(dataSource);
}

查询

列表查询

Database db = getDatabase();
IRepository<UserInfo> userInfoRepository =  db.getRepository(UserInfo.class);

// getList()
List<UserInfo> result1 =  userInfoRepository.getList();

// getList(Sql sql)
List<UserInfo> result2 = userInfoRepository.getList(Sql.builder().select().from(UserInfo.class));

// getList(String sql,Object ... param)
String sql = "select * from user_info where user_id = ? ";
List<UserInfo> result3 = userInfoRepository.getList(sql,1);

// ** Dto getList **
Sql sql1 = Sql.builder().select(" user_id as id, user_Name as name ").from(UserInfo.class).where("user_id = ?",1);
List<UserInfoDto> result4 =  db.getSqlQuery().getDtoList(UserInfoDto.class,sql1);

String sql2 = "select user_id as id, user_Name as name from user_info where user_id = ? ";
List<UserInfoDto> result5 =  db.getSqlQuery().getDtoList(UserInfoDto.class,sql2,1);

获取对象

Database db = getDatabase();
IRepository<UserInfo> userInfoRepository =  db.getRepository(UserInfo.class);

// getById(Object primaryKey)
UserInfo result1 =  userInfoRepository.getById(1);

// get(Sql sql)
UserInfo result2 = userInfoRepository.get(
        Sql.builder().select().from(UserInfo.class).where("user_id  = ?",1));

// get(String sql, Object ...  param)
String sql = "select * from user_info where user_id = ? ";
UserInfo result3 = userInfoRepository.get(sql,1);

// ** Dto get **
// getDto(Class<TDto> returnType, Sql sql)
UserInfoDto result4 = db.getSqlQuery().getDto(
        UserInfoDto.class,Sql.builder().select("user_id as id, user_Name as name").from(UserInfo.class).where("user_id  = ?",1));

联表查询

Database db = getDatabase();
Sql sql = Sql.builder()
    .select("a.user_id as id," +
            "a.user_Name as name,"+
            "b.id as typeId,"+
            "b.name as typeName,"+
            "a.login,"+
            "a.status,"+
            "a.group,"+
            "a.level,"+
            "a.create_time as createTime")
    .from(UserInfo.class,"a")
    .join(UserType.class,"b")
    .on("a.user_type_id = b.id");
List<UserInfoDetailDto> result =  db.getSqlQuery(.getDtoList(UserInfoDetailDto.class,sql); 

分页

Database db = getDatabase();
IRepository<UserInfo> userInfoRepository =  db.getRepository(UserInfo.class);
// getPageList(int pageIndex, int pageSize, Sql sql)
PageList<UserInfo> result = userInfoRepository.getPageList(1,10,Sql.builder().select().from(UserInfo.class));

Sql Lambda

Sql sql = Sql.builder()
          .select(UserInfo::getId,UserInfo::getName)
          .from(UserInfo.class)
          .where(UserInfo::getName,"Test")
          .whereIn(UserInfo::getId,1,2)
          .orderBy(UserInfo::getCreateTime, OrderBy.DESC);
                
/*** [Sql语句] ***/
// select user_id,user_name 
// FROM user_info 
// where user_name  = :p0 and user_id in (:p1,:p2)
// order by create_time desc
/*** [参数值] ***/
// [Test],[1],[2]

Sql or

Sql sql = Sql.builder().select().from(UserInfo.class)
            .whereLike(UserInfo::getName,"Test_2")
            .or()
            .whereLike(UserInfo::getName,"Test_2")
            .orderBy(UserInfo::getId);
 
/*** [Sql语句] ***/
// select * 
// from user_info 
// where user_name like :p0 
// or 
// user_name like :p1 
// order by user_id asc  
/*** [参数值] ***/
// [Test_2],[Test_2]

Dto Mapper

ResultMap

ResultMap column  = new ResultMap();
column.put(UserInfo::getId, UserInfoDto::getId);
column.put(UserInfo::getName, "name");
column.put(UserInfo::getUserTypeId); 

Sql sql = Sql.builder().select(
         column)
        .from(UserInfo.class)
        .where(UserInfo::getName,"Test")
        .whereIn(UserInfo::getId,1,2)
        .orderBy(UserInfo::getCreateTime, OrderBy.DESC); 

/*** [Sql语句] ***/
// select user_id  as id,
// user_name  as name,
// user_type_id 
// from user_info 
// where user_name  = :p0 
// and user_id in (:p1,:p2)
// order by create_time DESC
/*** [参数值] ***/
// [Test],[1],[2]

SelectTo

Sql sql = Sql.builder()
                .selectTo(UserInfoDto.class)
                .from(UserInfo.class)
                .where(UserInfo::getName,"Test")
                .whereIn(UserInfo::getId,1,2)
                .orderBy(UserInfo::getCreateTime, OrderBy.DESC);

/*** [Sql语句] ***/
// select createTime,
// level,
// `group` as group,
// user_id as id,
// user_name as name,
// login,
// status 
// from user_info 
// where user_name  = :p0 
// and user_id in (:p1,:p2)
// order by create_time DESC
/*** [参数值] ***/
// [Test],[1],[2]

持久化

新增

Database db = getDatabase();
IRepository<UserInfo> userInfoRepository =  db.getRepository(UserInfo.class);
UserInfo domain = new UserInfo();
domain.setName("Test");
domain.setLogin(10086);
domain.setPwd("123456");
domain.setCreatTime(new Date());
UserInfo user =   userInfoRepository.create(domain);

修改

Database db = getDatabase();
IRepository<UserInfo> userInfoRepository =  db.getRepository(UserInfo.class);
// get 
UserInfo user =  userInfoRepository.get(Sql.builder().select().from(UserInfo.class).where("user_id = ?",3399));
// update
user.setName("Test_2 ## -- "); 
userInfoRepository.update(user); 

修改2

Database db = getDatabase();
IRepository<UserInfo> userInfoRepository =  db.getRepository(UserInfo.class);
// get 
UserInfo user =  userInfoRepository.get(Sql.builder().select().from(UserInfo.class).where("user_id = ?",3399));
// update
Update<UserInfo> userInfoUpdate =  userInfoRepository.createUpdate();
// 指定修改字段值
userInfoUpdate.set(UserInfo::getPwd,"6543421"); 
userInfoRepository.update(user,userInfoUpdate);

删除

Database db = getDatabase();
IRepository<UserInfo> userInfoRepository =  db.getRepository(UserInfo.class);
// get
UserInfo user =  userInfoRepository.get(Sql.builder().select().from(UserInfo.class).where("user_id = ?",3391));
// delete
userInfoRepository.delete(user);

事务

事务支持

try(Database db = getDatabase()){
   // 开启事务
   db.beginTransaction();
   IRepository<UserInfo> userInfoIRepository =
           db.getRepository(UserInfo.class); 
   UserInfo domain = new UserInfo();
   domain.setName("Test");
   domain.setLogin(10086);
   domain.setPwd("123456");
   domain.setCreateTime(new Date()); 
   // 新增
   UserInfo user = userInfoIRepository.create(domain); 
   user.setName("Test_Update");
   // 修改
   userInfoIRepository.update(user);  
   // 提交事务
   db.commit();
   assert(user.getId()>0):"database transaction is error";
}  

注意：如果使用事务请使用try(){} 用于释放数据库连接

SQL语句执行

try(Database db = getDatabase()){
   // 开启事务
   db.beginTransaction();
   final  String insertSql  =
           "INSERT INTO user_info (user_Name,password,login,create_time) VALUES (?,?,?,?)";
   // Create
   int id =  db.getSqlRun().execute(
           insertSql,
           "Execute Test",
           "123456",
           10086,
           new Date()).asInt();  
   // Update
   final String updateSql  =
           "UPDATE user_info set user_Name = ? WHERE user_id = ?";
   db.getSqlRun().execute(updateSql,"Execute Test2",id);
   // 事务提交
   db.commit();
   // Select
   Sql sql = Sql.builder()
           .select("user_id as id, user_Name as name")
           .from(UserInfo.class).where("user_id  = ?", id);
   UserInfoDto resultDto = db.getSqlQuery().getDto(UserInfoDto.class,sql);  
}

Debug模式

Database db = getDatabase();
db.isDebug(true); 

Database指定isDebug(true)后，查询结果属性不能匹配会抛出异常信息,例子

获取表字段名

Sql2oUtils.getColumnName(UserInfo::getId);
// 输出
user_id

其他

• 示例数据库脚本
• 示例项目

License

• Apache2