Spring 中使用 Sql2O

Spring 中使用 Sql2O

什麼是Sql2O?

Sql2O是一個小型的Java資料庫，主要目的是為了讓我們在程式中對於資料庫的操作變得更佳簡單。在抓取資料時會自動的將資料映射到POJO中就如同其他ORM一樣，但是Sql2O並沒有自動生成SQL語法的功能。

為什麼要使用Sql2O?

Spring Data Jpa是在Spring Boot主推的一個ORM，相信有使用過Spring Boot的人一定對於Spring Data Jpa不是太陌生，主要是他對於Spring Boot的支持度很高，提供了許多方便的功能如 : 分頁、排序等等的。

ORM的好處是對於簡單的CURD不用自己寫SQL語法，這種做法對於資料庫系統的轉換也相當方便。

但是!!!

由於Jpa是一種標準，所以在一開始定義Entity的時候我覺得步驟相當繁瑣，對於資料表的關係定義 one-to-one、many-to-many、one-to-many等。如同一般資料庫的schema一般，這樣如果日後資料表有變動，程式內也必須相對做更改。

ORM對於複雜的查詢也相當不方便而且效能也不好，以下放上一張圖大家就可以知道Sql2O跟其他ORM的效能差異。

以上是執行完1000個Select所需要的時間，由此可見為什麼我會偏向Sql2O了，況且我個人到現在還是比較喜歡直接寫Sql語法。可能是我資歷太淺還感受不到Spring Data Jpa的好處QQ

如何使用Sql2O

因為Sql2O的Github上只有教如何在Spring Framework中使用並沒有Spring Boot的教學，加上目前Spring Boot資歷不足摸索了很久

一開始在建立專案時選擇JDBC和Web

然後再Project中新增資料庫的Config

@EnableTransactionManagement
@Configuration
public class config implements TransactionManagementConfigurer {


    @Bean(name = "dataSource")
    public DataSource dataSource() {

        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        dataSource.setUrl("jdbc:sqlserver://xxxx:xxxx;DatabaseName=xxxx");
        dataSource.setUsername("xxxxx");
        dataSource.setPassword("xxxxx");

        return dataSource;
    }

    @Bean
    @Override
    public PlatformTransactionManager annotationDrivenTransactionManager() {
        return new DataSourceTransactionManager(dataSource());
    }

    @Bean
    public Sql2o sql2o() {
        return new Sql2o(dataSource());
    }
}

建立Dao層

public interface  TestRepository {

    public int getStudentCount();

    public Student getStudent(int studentId);

}

建立Entity

public class Student {

    private int studentId;

    private String firstName;

    private String lastName;

     .....

}

實作Dao

@Repository

public class TestRepositoryImpl implements TestRepository {

    @Autowired

    private Sql2o sql2o;



    @Override

    public int getStudentCount(){

        String sql = "SELECT
count(id) FROM student";



        try (Connection con = sql2o.open()) {

            return con.createQuery(sql).executeScalar(Integer.class);

        }

    }



    @Override

    public Student getStudent(int studentId){

        String sql = "SELECT * FROM
student where studentId=:id";



        try (Connection con = sql2o.open()) {

            Student
result=con.createQuery(sql)

                    .addParameter("id", studentId)

                   
.executeAndFetchFirst(Student.class);

            return result;

        }

    }



    public List<Student> getStudentList(){

        String sql = "SELECT * FROM
student ";



        try (Connection con = sql2o.open()) {

            List<Student>
result=con.createQuery(sql)

                   
.executeScalarList(Student.class);

            return result;

        }

    }

在Contorller中實踐API

@RestController

@RequestMapping(value = "/api")

public class DbController {

    @Autowired

    private TestRepositoryImpl testRepositoryImpl;







    @ResponseStatus(HttpStatus.OK)

    @GetMapping(value = "/get", produces = MediaType.APPLICATION_JSON_UTF8_VALUE)

    public Student getStudent() {

        Student result=testRepositoryImpl.getStudent(1);

        return result;

    }





    @ResponseStatus(HttpStatus.OK)

    @GetMapping(value = "/get/list", produces = MediaType.APPLICATION_JSON_UTF8_VALUE)

    public List<Student> geStudenttList() {

        List<Student> result=testRepositoryImpl.getStudentList();

        return result;

    }

}