初学JOOQ,写个blog为了mark一下,也方便大家交流。直接上代码了。在网上搜不到太详细的demo和文档,都是英文的。哎,忧桑、、、在这里写几个demo,大家看看,有不足望指教。
初步的数据库连接,在这里我用了bonecp连接池来管理
import java.sql.Connection;
import java.sql.SQLException;
import org.jooq.DSLContext;
import org.jooq.impl.DSL;
import com.jolbox.bonecp.BoneCP;
import com.jolbox.bonecp.BoneCPConfig;
/**
*
* @author zhoudong
* WinterChou连接池管理数据库连接
*/
public class BoneCpPool {
private static BoneCP boneCp = null;
private static BoneCPConfig boneCPConfig = null;
// 静态代码块加载配置文件
static {
// 加载JDBC驱动
try {
Class.forName("com.mysql.jdbc.Driver");// 注册数据库
boneCPConfig = new BoneCPConfig();// bonecp数据库连接池配置
String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/wish";// jdbc:mysql://10.10.0.215:3306/che001
boneCPConfig.setJdbcUrl(jdbcUrl);
boneCPConfig.setUser("root");
boneCPConfig.setPassword("tiger");
// 数据库连接池的最小连接数
boneCPConfig.setMinConnectionsPerPartition(5);
// 数据库连接池的最大连接数
boneCPConfig.setMaxConnectionsPerPartition(10);
boneCPConfig.setPartitionCount(1);
// System.out.println("boneCPConfig"+boneCPConfig);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// 获取连接池
public static BoneCP getBoneCP() {
try {
boneCp = new BoneCP(boneCPConfig);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return boneCp;
}
// 获取连接
public static Connection getConnection(BoneCP boneCpP) {
if (boneCpP != null) {
try {
return boneCpP.getConnection();
} catch (SQLException e) {
return null;
}
} else {
return null;
}
}
// 关闭连接池
public static void closeBoneCP(BoneCP bc) {
bc.close();
}
// 关闭连接
public static void closeConnection(Connection con) throws SQLException {
con.close();
}
//
public static DSLContext getContext(Connection conDsl) {
return DSL.using(conDsl);
}
}
</span>
简单的初步demo
<span style="font-size:18px;">import java.sql.Connection;
import org.jooq.Condition;
import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.Result;
import org.jooq.SelectQuery;
import org.jooq.Table;
import org.jooq.UpdateQuery;
import org.jooq.impl.DSL;
import com.dfb.jooq.datapool.BoneCpPool;
import com.jolbox.bonecp.BoneCP;
/**
*
* @author zhoudong
* 简单的增删改查
*/
public class JooqDao {
private DSLContext dslContext= null;
//获取DSLContext对象
private DSLContext getdslContext()
{
BoneCP boneCP = BoneCpPool.getBoneCP();
Connection connection = BoneCpPool.getConnection(boneCP);
dslContext = DSL.using(connection);
return dslContext;
}
//简单实体查询
public void select(String add)
{
DSLContext getdslContext = getdslContext();
Table<Record> table = DSL.table("shangfox_user");
SelectQuery<Record> selectQuery = getdslContext.selectQuery(table);//获取查询对象
Condition eq = DSL.field("username").eq(add);//查询条件
selectQuery.addConditions(eq);//添加查询条件
Result<Record> fetch = selectQuery.fetch();
for (Object aResult : fetch) {
Record record = (Record) aResult;
System.out.println(record);
System.out.println(record.getValue("username"));
}
}
//实体更新
public void update(String name)
{
DSLContext getdslContext = getdslContext();
Table<Record> table = DSL.table("shangfox_user");
UpdateQuery<Record> updateQuery = getdslContext.updateQuery(table);//获取更新对象
updateQuery.addValue(DSL.field("email"), "new-email");//更新email字段的值为new-email
Condition eq = DSL.field("username").eq(name);//更新username为name的email字段
updateQuery.addConditions(eq);
int execute = updateQuery.execute();
System.out.println(execute);
select("shangfox1");
}
//原生态的sql查询
public void getVal()
{
DSLContext getdslContext = getdslContext();
Table<Record> table = DSL.table("shangfox_wish");//表名
Result<Record> fetch = getdslContext.select().from(table).where("statu = 0").and("id > 4340").orderBy(DSL.field("time").asc()).fetch();
for (Object aResult : fetch) {
Record record = (Record) aResult;
System.out.println(record);
}
/*Map<String, Object> fetchAnyMap = orderBy.fetchAnyMap();
Set<String> keySet = fetchAnyMap.keySet();
for(String s:keySet)
{
System.out.println("key--"+s+"--val:"+fetchAnyMap.get(s));
}*/
}
//验证DSL.exists方法
public void exits()
{
DSLContext getdslContext = getdslContext();
Condition condition = DSL.exists(DSL.select(DSL.field("username1")));
Table<Record> table = DSL.table("shangfox_user");
SelectQuery<Record> selectQuery = getdslContext.selectQuery(table);
selectQuery.addConditions(condition);
Result<Record> fetch = selectQuery.fetch();
for (Object aResult : fetch) {
Record record = (Record) aResult;
System.out.println(record);
System.out.println(record.getValue("username"));
}
}
public static void main(String[] args) {
JooqDao jooqDao = new JooqDao();
// jooqDao.select("shangfox");
// jooqDao.update("shangfox1");
// jooqDao.exits();
jooqDao.getVal();
}
}
demo的jar包下载地址jooq和bonecp的jar