The HasorDB recommended in this issue is a full-featured database access tool that provides object mapping, rich type handling, dynamic SQL, stored procedures, built-in pagination dialects of 20+, support for nested transactions, multiple data sources, condition constructors, POST strategies, and multiple statements/results.

introduce

HasorDB is a full-featured database access tool that provides object mapping, rich type handling, dynamic SQL, stored procedures, built-in pagination dialects of 20+, support for nested transactions, multiple data sources, conditional constructors, POST policies, and multiple statements/results. And compatible with Spring and MyBatis usage. It does not rely on any other framework, so it can be easily integrated with any framework for use.

function characteristics

Familiar way

• JdbcTemplate interface method (highly compatible with Spring JDBC)Mapper file format (highly compatible with MyBatis)LambdaTemplate (highly similar to MyBatis Plus, jOOQ, and BeetlSQL)@Insert, @ Update, @ Delete, @ Query, @ Callable annotations (similar to JPA)

transaction support

• Supports 5 transaction isolation levels and 7 transaction propagation behaviors (the same as Spring tx)Provide TransactionTemplate and TransactionManager interface with declarative transaction control capability (same usage as Spring)

Featured advantages

• Support pagination queries and provide multiple database dialects (20+)Support Insert strategy (Into, Update, IGNORE)More comprehensive TypeHandler options (MyBatis 40+, HasorDB 60+)Mapper XML supports multiple statements and multiple results

  Provide a unique @ {xxx, expr, xxxxx} rule extension mechanism to make dynamic SQL simpler

  Support stored procedures

  Supports time types in JDBC 4.2 and Java 8

  Support multiple data sources

quick start

Introducing Dependency

<dependency>
  <groupId>net.hasor</groupId>
  <artifactId>hasor-db</artifactId>
  <version>4.3.0</version><!-- Check out the latest version：https://mvnrepository.com/artifact/net.hasor/hasor-db -->
</dependency>

Introduce database drivers, such as MySQL drivers

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.22</version>
</dependency>

Using HasorDB does not rely on database connection pools, but having a database connection pool is standard for most projects. We use Alibaba’s Druid here

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid</artifactId>
  <version>1.1.23</version>
</dependency>

Finally, prepare a database table and initialize some data (CreateDB. sql file)

drop table if exists `test_user`;
create table `test_user` (
    `id`          int(11) auto_increment,
    `name`        varchar(255),
    `age`         int,
    `create_time` datetime,
    primary key (`id`)
);

insert into `test_user` values (1, 'mali', 26, now());
insert into `test_user` values (2, 'dative', 32, now());
insert into `test_user` values (3, 'jon wes', 41, now());
insert into `test_user` values (4, 'mary', 66, now());
insert into `test_user` values (5, 'matt', 25, now());

Using DAO

Using DAO can inherit the BaseMapper<T>generic DAO interface to complete some basic operations, still taking single table CRUD as an example

/Some interfaces of DAO need to recognize the ID attribute, so it is necessary to mark them on the DTO object with the @ Column annotation


@Table(mapUnderscoreToCamelCase = true)
public class TestUser {
@Column(primary = true)
private Integer id;
private String name;
private Integer age;
private Date createTime;

// getters and setters omitted
}

//Creating a data source data mining tutorial
DataSource dataSource = DsUtils.dsMySql();
//Create a universal DAO
DalSession session = new DalSession(dataSource);
BaseMapper<TestUser> baseMapper = session.createBaseMapper(TestUser.class);
// Initialize some data
baseMapper.template().loadSQL(“CreateDB.sql”);

// Query data
List<TestUser> dtoList = baseMapper.query().queryForList();
PrintUtils.printObjectList(dtoList);

// Insert new data
TestUser newUser = new TestUser();
newUser.setName(“new User”);
newUser.setAge(33);
newUser.setCreateTime(new Date());
int result = baseMapper.insert(newUser);

//Update, update the name from mali to mala.
TestUser sample = baseMapper.queryById(1);
sample.setName(“mala”);
int result = baseMapper.updateById(sample);

// Delete data with ID 2.
int result = baseMapper.deleteById(2);

Using Mapper

The best place for unified management of SQL is still the Mapper file, and HasorDB’s Mapper file is highly compatible with MyBatis, with extremely low learning costs

//Use @ RefMapper annotation to link Mapper files with interface classes (inheriting BaseMapper is optional)


@RefMapper("/mapper/quick_dao3/TestUserMapper.xml")
public interface TestUserDAO extends BaseMapper<TestUser> {
public int insertUser(@Param("name") String name, @Param("age") int age);

public int updateAge(@Param(“id”) int userId, @Param(“age”) int newAge);

public int deleteByAge(@Param(“age”) int age);

public List<TestUser> queryByAge(@Param(“beginAge”) int beginAge, @Param(“endAge”) int endAge);
}

To better understand and use HasorDB’s Mapper file, it is recommended to add DTD for validation. In addition, HasorDB is compatible with MyBatis3’s DTD and is compatible with the vast majority of MyBatis projects

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//hasor.net//DTD Mapper 1.0//EN" "https://www.hasor.net/schema/hasordb-mapper.dtd">
<mapper namespace="net.hasor.db.example.quick.dao3.TestUserDAO">
  <resultMap id="testuser_resultMap" type="net.hasor.db.example.quick.dao3.TestUser">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="age" property="age"/>
    <result column="create_time" property="createTime"/>
  </resultMap>
  
  <sql id="testuser_columns">
    name,age,create_time
  </sql>
  
  <insert id="insertUser">
    insert into `test_user` (
      <include refid="testuser_columns"/>
    ) values (
      #{name}, #{age}, now()
    )
  </insert>
  
  <update id="updateAge">
    update `test_user` set age = #{age} where id = #{id}
  </update>
  
  <delete id="deleteByAge"><![CDATA[
    delete from `test_user` where age > #{age}
  ]]></delete>
  
  <select id="queryByAge" resultMap="testuser_resultMap">
    select id,<include refid="testuser_columns"/>
    from `test_user`
    where  #{beginAge} < age and age < #{endAge}
  </select>
</mapper>

Using Transactions

HasorDB provides three ways to use transactions, namely:

• Declarative transactions implement transaction control by calling the TransactionManager interface.
• Template transactions are controlled through the TransactionTemplate interface.
• Annotation transaction, annotation transaction control based on @ Transaction (under development…)