JOOQ初学-简单的增删改查demo

初学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