abacus-jdbc

Hope it will bring you the programming experiences: coding with SQL/DB is just like coding with Collections.

Features:

This library is just about three things:

• Write/generate a sql script(if needed): SQLBuilder, DynamicSQLBuilder.

// Manually write the sql in plain string.
String query = "SELECT id, first_name, last_name, email FROM user WHERE first_Name = ?";

// Or by SQLBuilder
String query = PSC.select("id", "firstName, "lastName", "email").from(User.class).where(CF.eq("firstName")).sql();
// Or if select all columns from user:
String query = PSC.selectFrom(User.class).where(CF.eq("firstName")).sql();

// Sql scripts can also be placed in sql mapper xml file and then associated with a DAO object.
UserDao userDao =  JdbcUtil.createDao(UserDao.class, dataSource, sqlMapper);

userSqlMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<sqlMapper>
	<sql id="selectUserByFirstName">SELECT id, first_name, last_name, email FROM user WHERE first_Name = ?</sql>
</sqlMapper>

• Prepare Statement PreparedQuery, NamedQuery, CallableQuery with a sql or Dao mapped with sqls.

// sql can be used to create PreparedQuery/NamedQuery/CallableQuery
PreparedQuery preparedQuery = JdbcUtil.prepareQuery(dataSource, query...); 
			            //.prepareQuery(connection, query...)		
			            //.prepareNamedQuery(dataSource, namedQuery...)									   
			            //.prepareCallableQuery(dataSource, query...)									   
			            //....	
				      .setString(1, fistName) // Firstly set query parameters, if needed.
				    //.setLong(paramName, paramValue) // set named parameters for NamedQuery/CallableQuery.
				    //.setParameters(entity) // set named parameters by entity with getter/setter methods
				    //.setParameters(Map<String, ?>) // set named parameters by Map
				    //.setParameters(param1, param2...) // set several parameters in one line.
				    //.setParameters(Collection<?> parameters) // set parameters with a Collection.
				    //.setParameters(ParametersSetter parametersSetter) // set parameters by functional interface. 
				    //....  									   
																		   

// Sql can also be associated to a self-defined DAO method. (There are tens of most used predefined methods in DAO interfaces which be used without write single line of code).
public interface UserDao extends JdbcUtil.CrudDao<User, Long, SQLBuilder.PSC, UserDao>, JdbcUtil.JoinEntityHelper<User, SQLBuilder.PSC, UserDao> {
    // This is just a sample. Normally there are pre-defined methods available for this query: userDao.list(Condition cond).
    // Methods defined in Dao interface don't require implementation. Of course, Customized implemnetation is also supported by default method.
    @Select(sql = "SELECT id, first_name, last_name, email FROM user WHERE first_Name = ?")
    List<User> selectUserByFirstName(String firstName) throws SQLException;
    
    // Or id of the sql script defined in xml mapper file.
    @Select(id = "selectUserByFirstName")
    List<User> selectUserByFirstName(String firstName) throws SQLException;

    // Or id of the sql script defined in below nested static class.
    // Instead of writing sql scripts manually, you can also use SQLBuilder/DynamicSQLBuilder to write sql scripts.
    @Select(id = "selectUserByFirstName")
    List<User> selectUserByFirstName(String firstName) throws SQLException;
    
    // Multiple updates within transaction.
    @Transactional
    @Sqls({ "update user set first_name = ? where id = ?", 
            "update user set last_name = ? where id = :id" })
    default void updateFirstNameLastNameByIds(long idForUpdateFirstName, long idForUpdateLastName, String... sqls) throws SQLException { // Last parameter must be String[]. It will be automatically filled with sqls in @Sql.
        prepareQuery(sqls[0]).setLong(1, idForUpdateFirstName).update();
        prepareNamedQuery(sqls[1]).setLong(1, idForUpdateLastName).update();
    }

    // Refer classes in package com.landawn.abacus.jdbc.annotation for more supported annations
    @Select(sql = "SELECT * FROM {tableName} where id = :id ORDER BY {{orderBy}}")
    User selectByIdWithDefine(@Define("tableName") String tableName, @Define("{{orderBy}}") String orderBy, @Bind("id") long id);

    static final class SqlTable {
        @SqlField
        static final String selectUserByFirstName = PSC.select("id", "firstName, "lastName", "email").from(User.class).where(CF.eq("first")).sql();
    }
}

UserDao userDao =  JdbcUtil.createDao(UserDao.class, dataSource, ...);

• Call methods in the prepared statement/query or Dao and retrieve the result(If needed): Dao/CrudDao/JoinEntityHelper, Jdbc, DataSet, ConditionFactory(CF), JdbcUtil, JdbcUtils.

// Execute the sql by a PreparedQuery/NamedQuery/CallableQuery
preparedQuery.findFirst()
           //.findFirst(User.class)/findFirst(rowMapper)/...
           //.findOnlyOne()/findOnlyOne(User.class)/findOnlyOne(rowMapper)/...
           //.list()/list(User.class)/list(rowMapper)/...
           //.stream()/stream(User.class)/stream(rowMapper)/...
	   //.query()/qurey(resultExtractor)/queryForInt/queryForString/queryForSingleResult/...
           //.exists()/ifExists(rowConsumer)
	   //.update/batchUpdate/execute/...
																		   

// Sql can also be executed by directly calling DAO methods.
userDao.selectUserByFirstName(firstName)
     //.updateFirstNameLastNameByIds(100, 101)
     //.findFirst(Condition)
     //.findOnlyOne(Condition)
     //.list(Condition)
     //.stream(Condition)
     //.update(user)/deleteById(userId)/batchInsert(Collection<User>)/...

• Code Generation: CodeGenerationUtil, JdbcCodeGenerationUtil.

Why abacus-jdbc?

The biggest difference between this library and other data(database) access frameworks is the simplicity/consistency/integrity in the APIs design.

Download/Installation & Changes:

• Maven

<dependency>
	<groupId>com.landawn</groupId>
	<artifactId>abacus-jdbc</artifactId>
	<version>3.8.5</version> 
<dependency>

• Gradle:

// JDK 17 or above:
compile 'com.landawn:abacus-jdbc:3.8.5'

User Guide:

• Introduction to JDBC
• More samples/questions(?)

Also See: abacus-common, abacus-entity-manager.

Recommended Java programming libraries/frameworks:

lombok, Apache Druid, Sharding-JDBC ... awesome-java

Recommended Java programming tools:

Spotbugs, JaCoCo...