【ActiveJdbc】01 入门

官方快速上手文档：

https://javalite.io/activejdbc

https://javalite.io/getting_started

完整介绍：

https://javalite.io/documentation

环境搭建：

1、在数据库中创建该表，注意主键设置为空移除

CREATE TABLE people (
  id  int(11) NOT NULL auto_increment PRIMARY KEY,
  first_name VARCHAR(56) NOT NULL,
  last_name VARCHAR(56),
  created_at DATETIME,
  updated_at DATETIME
);

2、创建一个空的Maven工程，添加必要的包：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>cn.dzz</groupId>
    <artifactId>ActiveJDBC</artifactId>
    <version>1.0-SNAPSHOT</version>



    <dependencies>
        <dependency>
            <groupId>org.javalite</groupId>
            <artifactId>activejdbc</artifactId>
            <version>2.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.25</version>
        </dependency>
    </dependencies>


    <build>
        <plugins>
            <plugin>
                <groupId>org.javalite</groupId>
                <artifactId>activejdbc-instrumentation</artifactId>
                <version>2.2</version>
                <executions>
                    <execution>
                        <phase>process-classes</phase>
                        <goals>
                            <goal>instrument</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

3、编写Model继承类，如果名称不一致，可使用ActiveJDBC提供的table注解标注

package cn.dzz.model;

import org.javalite.activejdbc.Model;
import org.javalite.activejdbc.annotations.Table;


@Table("employees")
public class Employee extends Model {

}

4、创建连接并执行SQL操作

【注意连接高版本MySQL，com.mysql.cj.jdbc.Driver，连接地址需要加上参数】

package cn.dzz.instance;

import cn.dzz.model.Employee;
import org.javalite.activejdbc.Base;

public class ConnectTest {

    public static void main(String[] args) {
        Base.open("com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3307/devbase", "root", "123456");

        Employee e = new Employee();
        e.set("first_name", "John");
        e.set("last_name", "Doe");
        e.saveIt();

        // new Employee().set("first_name", "John", "last_name", "Doe").saveIt();
    }
}

首次操作一定出现这个问题：

Caused by: activejdbc.InitException: you are trying to work with models, but no models are found. Maybe you have no models in project, or you did not instrument the models. It is expected that you have a file activejdbc_models.properties on classpath

解决参考：

https://www.dazhuanlan.com/2019/12/17/5df8089723b56/#配置参考

通过IDEA找到这个Maven安装的植入插件：

执行一次插件，再次允许即可：

可以看到是插入成功的

API阅读：

单记录查询：

// 子查询？ 和对应的条件 ,条件是可变参数，支持多条件
Employee e = Employee.findFirst("first_name = ?", "John");
System.out.println(e);

Object first_name = e.get("first_name");
System.out.println(first_name);
Object id = e.get("id");
System.out.println(id);
Object last_name = e.get("last_name");
System.out.println(last_name);

问题在于返回的属性值是一个Object，需要自己转换？

多记录直接调用where方法：

List<Employee> employees = Employee.where("first_name = ?", "John");

更新记录类同JPA，先查再改：

Employee e = Employee.findFirst("first_name = ?", "John");
e.set("last_name", "Steinbeck").saveIt();

删除记录同理：

Employee e = Employee.findFirst("first_name = ?", "John");
e.delete();

也支持类静态方法：

Person.delete("age > ?", "10");

复杂的批处理操作：

PreparedStatement ps = Base.startBatch("insert into people (NAME, LAST_NAME, DOB) values(?, ?, ?)");
 Base.addBatch(ps, "Mic", "Jagger", "1962-01-01");
 Base.addBatch(ps, "Marilyn", "Monroe", "1932-01-01");
 Base.executeBatch(ps);
 ps.close();

全删和全找：

Employee.deleteAll();
List<Employee> employees = Employee.findAll();

支持建造者模式方式赋值多个字段：

new Person().set("first_name", "Marilyn").set("last_name", "Monroe").set("dob", "1935-12-06").saveIt();

也可以通过类静态方法：

Person.createIt("first_name", "Marilyn", "last_name", "Monroe", "dob", "1935-12-06");

读取数量不多的数据集：

    List<Map> usersList = Base.findAll("select * from users where company_id = ? ", companyId);
    for(Map  record: userList){
        System.out.println("first_name: " + record.get("first_name"));
        System.out.println("last_name: " + record.get("last_name")); 
    }

百万级数据读取：

Base.find("select first_name, last_name from really_large_table", ....).with(new RowListenerAdapter() {
         public void onNext(Map row) {
                 ///write your code here
                 Object o1 = row.get("first_name");
                 Object o2 = row.get("last_name");
             }
         });

时间转换成毫秒值：

Long  lastLoginTime = Convert.toLong(Base.firstCell?("select time from logins  where user_id  ? order by created_at limit 1", 123));

取表的单个列记录：

  List ssns = Base.firstColumn("select ssn from people where first_name = ?", "John");
  for(Object ssn: ssns)
      System.out.println(ssn);

SETTER & GETTER 包装处理：

public class Person extends Model{
   public void setFirstName(String firstName){
      set("first_name", firstName);
   }
   public String getFirstName(){
      return getString("first_name");
   }
}

所以ActiveJDBC也提供了对应的包装处理，方便开发者直接获取正确的数据类型：

【原始get方法得到的是Object】

String  name = p.getString("name");
Timestamp dob = p.getTimestamp("dob");

文档中提及ActiveJDBC会尽可能的帮你转换类型，根据表设计的字段类型来返回，然而上面的读取并不会这样

如果不行，还可以调用Convert工具类的方法实现转换

http://javalite.github.io/activejdbc/snapshot/org/javalite/common/Convert.html

单复数命名映射约定：

ActiveJDBC的创造者约定：表名称统一使用复数形式，而映射的Java模型类则用单数形式命名

对于工地英语的我来说简直费解，所以创造者又提供了@Table注解，指定表名称即可

@Table("Operator")
public class Operator  extends Model{}