Skip to content

README.md

Latest commit

 

History

History
399 lines (370 loc) · 13.2 KB

README.md

File metadata and controls

399 lines (370 loc) · 13.2 KB

openlibs.sql

The lib of java database ORM simple implementation

Get it by Maven

<dependency>
  <groupId>com.openthinks.libs</groupId>
  <artifactId>openlibs.sql</artifactId>
  <version>2.0.1</version>
</dependency>

Session基本用法

// get session instance by its factory
Session session = SessionFactory.getSession();
// fetch a entity by its key
Message message = session.load(Message.class, "1000");
//TODO
session.close();// close session
// save a entity to table
Message message = new Message();
message.setLocale(Locale.CHINA.toString());
message.setContent("HELLO");
message.setMessageId("2000");
session.save(message);
session.close();

配置文件和类使用

  • 配置文件名称: dbconfig.properties
  • 配置文件类: Configurator
  • 配置文件工厂类: ConfiguratorFactory
数据库属性文件

格式如下:

#driver name
DRIVER=com.mysql.jdbc.Driver
#database url
URL=jdbc:mysql://localhost:3306/opendb
#database user
USERNAME=root
#database pass
USERPWD=123456
#databse dailect : MYSQL,ORACLE,SQLSERVER
DIALECT=MYSQL
配置类用法

Configurator的属性一一对应配置文件dbconfig.properties里的键 所以可以新建一个配置类实例，替代配置文件:

Configurator conf = new Configurator();
conf.setDriver("xxx");
conf.setUrl("xxx");
conf.setUserName("xxx");
conf.setUserPwd("xxx");
// option, default value is false
conf.setUsePool(true);
// option, default value is Dialect.MYSQL
conf.setDialect(Dialect.ORACLE);

ConfiguratorFactory类负责创建Configurator的实例

// 方法1: 获得默认的配置类实例,将从默认配置文件dbconfig.properties中读取配置并实例化
Configurator fileConf = ConfiguratorFactory.getDefaultInstance();
// 方法2: 获得新的配置类实例,将复制已有的配置实例
Configurator codeConf = ConfiguratorFactory.getInstance(conf);
// 方法3: 重新设置ConfiguratorFactory的默认配置实例,指向已有的配置类实例
ConfiguratorFactory.setConfigurator(conf);
Configurator overrideConf = ConfiguratorFactory.getDefaultInstance();
// 方法4: 读取外部文件实例化配置,配置文件名称可以自由命名
File file = new File("C:\path\filename.properties");
Configurator externalConf = ConfiguratorFactory.getInstance(file);

Session高级用法

实体类定义

目前支持两种类型的实体类: 继承自Entity类,带有JPA注解的POJO类

com.openthinks.libs.sql.entity.Entity实例简单对应数据库中的表的一条记录,因此要求该实体类定义时注意以下事项:

  1. 该实体类中的字段或属性名需要与对应表列名一样
  2. 该实体类中定义的第一个字段作为对应表的主键
  3. 该实体类的名称需与表名相同(仅在调用Session的高级API)

例子:

//table structure
create table message(
    message_id varchar(100),
    message_local varchar(100),
    message_content varchar(1000)
);

public class Message extends Entity {
	private String message_id;
	private String message_locale;
	private String message_content;
	public String getContent() {
		return message_content;
	}
	public String getLocale() {
		return message_locale;
	}
	public String getId() {
		return message_id;
	}
	public void setId(String messageId) {
		this.message_id = messageId;
	}
	public void setLocale(String locale) {
		this.message_locale = locale;
	}
	public void setContent(String content) {
		this.message_content = content;
	}
}

JPA注解的POJO类没有额外的限制

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name = "message")
public class MessageJPA {
	@Id
	@Column(name = "message_id")
	private String messageId;
	@Column(name = "message_locale")
	private String locale;
	@Column(name = "message_content")
	private String content;
	/*
	*getter/setter method
	*/
}
增删改

在Session的DAO API中分为两大类，一类是无需编写SQL(High level);另一类是需要自行构造SQL(Low level).

//<T> void com.openthinks.libs.sql.dhibernate.Session.save(T object)
// High level
Session session = SessionFactory.getSession();
MessageJPA messageJPA = new MessageJPA();
messageJPA.setLocale(Locale.US.toString());
messageJPA.setContent("HELLO");
messageJPA.setMessageId("2000");
session.save(messageJPA);
Message messageEntity = new Message();
messageEntity.setLocale(Locale.CHINA.toString());
messageEntity.setContent("你好");
messageEntity.setId("3000");
session.save(messageEntity);

//int com.openthinks.libs.sql.dhibernate.Session.add(String sql, String[] params)
// Low level
String saveSQL = "INSERT INTO message(message_id,message_locale,message_content) values(?,?,?)";
String[] params = {"4000","en_US","Hello again"};
int affectRow = session.add(saveSQL,params);
session.close();
//<T> void com.openthinks.libs.sql.dhibernate.Session.update(T object)
Session session = SessionFactory.getSession();
MessageJPA message = new MessageJPA();
message.setLocale(Locale.CHINA.toString());
message.setContent("中国你好");
message.setMessageId("2000");
session.update(message);

//int com.openthinks.libs.sql.dhibernate.Session.update(String sql, String[] params)
String updateSQL = "update message set message_content = ? where message_id =? and message_locale=?";
String[] params = {"您好","zh_CN","2000"};
session.update();
session.close();
//<T> void com.openthinks.libs.sql.dhibernate.Session.delete(T object)
MessageJPA messageJPA = new MessageJPA();
messageJPA.setLocale(Locale.US.toString());
messageJPA.setContent("HELLO");
messageJPA.setMessageId("2000");
Session session = SessionFactory.getSession();
session.delete(messageJPA);

//int com.openthinks.libs.sql.dhibernate.Session.delete(String sql, String[] params)
String deleteSQL = "delete message where message_id =? and message_locale=?";
String[] params = {"zh_CN","2000"};
session.delete(deleteSQL,params);
session.close();
条件查询

对于简单根据单一主键获取记录或获取所有表中记录,推荐使用High level API:

// 根据id值获取clz类型的实体对象
<T> T com.openthinks.libs.sql.dhibernate.Session.load(Class<T> clz, Serializable id)
// 获取所有相应实体类的集合列表
<T> List<T> com.openthinks.libs.sql.dhibernate.Session.list(Class<T> clz)

而对于一些复杂的查询，可以使用接下来的方式

直接SQL语句查询
Session session = SessionFactory.getSession();
String querySQL = "SELECT * FROM message WHERE message_id = ? and message_locale= ? ";
MessgaeJPA messageJPA = session.get(MessageJPA.class, querySQL,new String[]{"2000","zh_CN"});
//Messgae message = session.get(Message.class, querySQL,new String[]{"2000","zh_CN"});
List<MessageJPA> list = session.list(MessageJPA.class, "SELECT * FROM message",new String[]{});
//List<Message> list = session.list(Message.class, "SELECT * FROM message",new String[]{});
// 不指定实体类型时,将使用 openthinks.libs.sql.data.Row
List<Row> rows = session.list("SELECT * FROM message",new String[]{});
session.close();
简单条件类查询

简单条件类: com.openthinks.libs.sql.lang.Condition是非常底层的,也属于Low level. Condition对象可以如下创建:

Session session = SessionFactory.getSession();
session.createCondition(); // same as Condition.build()

// where 1=1
Condition condition1 = Condition.build(); 
// SELECT * FROM message where 1=1
Condition condition2 = Condition.build("SELECT * FROM message");
// SELECT * FROM message where 1=1
Condition condition3 = Condition.build(MessageJPA.class);

如何使用:(暂支持 绝对匹配 =,模糊匹配 like,开始于 >,结束于 <) 参考以下 test case:

public class ConditionTest {
	private static MessageJPA message = new MessageJPA();
	private static MessageJPA message2 = new MessageJPA();
	@BeforeClass
	public static void setUp() {
		Session session = SessionFactory.getSession();
		message.setMessageId("CONDITION_1");
		message.setLocale(Locale.US.toString());
		message.setContent("Condition class regular test");
		session.save(message);

		message2.setMessageId("CONDITION_2");
		message2.setLocale(Locale.US.toString());
		message2.setContent("Condition class other test");
		session.save(message2);
		session.close();
	}
	@Test
	public void regularTest() {
		Session session = SessionFactory.getSession();
		Condition condition = session.createCondition();
		condition.setSqlPart("SELECT * FROM message");
		// 等效于 Condition.build("SELECT * FROM message");
		
		//第一个参数为条件类型;第二个参数为表字段;第三参数为对应的条件值
		condition.addItem(Condition.ABSMATCH, "message_id", "CONDITION_1");
		MessageJPA msg = session.get(MessageJPA.class, condition);
		Assert.assertNotNull(msg);
		Assert.assertEquals(message.getContent(), msg.getContent());
		session.close();
	}
	@Test
	public void otherTest() {
		Condition condition = Condition.build(MessageJPA.class)
				.addItem(Condition.LIKEMATCH, "message_id", "CONDITION%")
				.addItem(Condition.ORDER, "message_id", Condition.Order.DESC);//排序
		Session session = SessionFactory.getSession();
		List<MessageJPA> list = session.list(MessageJPA.class, condition);
		Assert.assertTrue(list.size() == 2);
		Assert.assertEquals(message2.getMessageId(), list.get(0).getMessageId());
		session.close();
	}
	@AfterClass
	public static void tearDown() {
		Session session = SessionFactory.getSession();
		session.delete(message);
		session.delete(message2);
		session.close();
	}
}
智能QueryFilter查询

目前实现的filter有

Filters.eq, Filters.neq Filters.include, Filters.startWith, Filters.endWith Filters.and, Filters.or, Filters.group

Session session =  SessionFactory.getSession();
// 获取Query对象
Query<MessageJPA> query = session.createQuery(MessageJPA.class);
// 定义具体的QueryFilter
QueryFilterGroup group = Filters.group();
group.push(Filters.eq("messageId", "4000"));
group.push(Filters.or());
QueryFilterGroup embedGrp = Filters.group();
embedGrp.push(Filters.eq("messageId", "2000"));
embedGrp.push(Filters.eq("locale", "zh_CN"));
group.push(embedGrp);
// 最终SQL(MYSQL):
// select * from `message` where  ( `message_id` = '4000'  or  ( `message_id` = '2000'  and message_locale = 'zh_CN')  ) 
// 添加到Query对象
query.addFilter(group);
List<MessageJPA> list = query.execute();
session.close();
简单事务
  • 开启事务 session.beginTransaction(); 另外 session.beginTransaction(TransactionLevel.TRANSACTION_READ_UNCOMMITTED) 设置事务级别
  • 关闭事务 session.endTransaction(); 另外 session.close(); 也会默认关闭事务,如果开启的话.

例子:

public class TransactionTest {
	private static MessageJPA message = new MessageJPA();
	@BeforeClass
	public static void setUp() {
		Session session = SessionFactory.getSession();
		message.setMessageId("TRANSACTION");
		message.setLocale(Locale.US.toString());
		message.setContent("Transaction test");
		session.save(message);
		session.close();
	}
	@Test
	public void beginTransactionTest() throws TransactionException {
		Session session = SessionFactory.getSession();
		session.beginTransaction();
		message.setContent("Transaction test rollback");
		session.update(message);
		int result = session.add("insert into message");//错误语法,新增不成功
		if (result == 0) {
			session.rollback();
		} else {
			session.commit();
		}
		session.endTransaction();
        
		MessageJPA msg = session.load(MessageJPA.class, message.getMessageId());
		Assert.assertNotNull(msg);
		Assert.assertEquals("Transaction test", msg.getContent());
		session.close();
	}
	@AfterClass
	public static void tearDown() {
		Session session = SessionFactory.getSession();
		session.delete(message);
		session.close();
	}
}
连接池

目前支持简单的连接池实现 com.openthinks.libs.sql.dao.pool.impl.SimpleConnectionPool; 可以自行扩展 com.openthinks.libs.sql.dao.pool.ConnectionPool接口,并替换默认的实现:

MyConnectionPool connPool = new MyConnectionPool();// your implemented ConnectionPool
ConnectionPoolManager.setSingletonPoolInstance(connPool);// make it replace the default pool

当然使用连接池前,必须配置,在配置文件 dbconfig.properties 中, 加入:

USEPOOL=true
#可选,不配的话默认无限制
MAX_ACTIVE=100
#可选,不配的话默认无限制
MAX_IDLE=10

对于扩展的ConnectionPool, 除了如上所述使用Code替换默认实现, 亦可以在配置文件中指明实现类名称

USEPOOL=true
POOL_CLASS=com.openthinks.libs.sql.MyConnectionPool

需要注意事项是构造函数的定义:

  1. MyConnectionPool(Configuration) 和 MyConnectionPool() 至少需要定义一个
  2. MyConnectionPool() 需要自行获取配置类,以确保获取到数据库连接
  3. 优先使用 MyConnectionPool(Configuration) 构造函数