JOOQ系列(一)实现CRUD操作

骑猪看日落 2022-05-28 03:17 365阅读 0赞

**一、什么是JOOQ**

JOOQ 是基于Java访问关系型数据库的工具包。JOOQ 既吸取了传统ORM操作数据的简单性和安全性，又保留了原生sql的灵活性，它更像是介于 ORMS和JDBC的中间层。对于喜欢写sql的码农来说，JOOQ可以完全满足你控制欲，可以是用Java代码写出sql的感觉来。就像官网说的那样 :

get back in control of your sql (重新控制SQL)

**二、JOOQ优点**  
  
  
DSL(Domain Specific Language )风格，代码够简单和清晰。遇到不会写的sql可以充分利用IDEA代码提示功能轻松完成。  
保留了传统ORM 的优点，简单操作性，安全性，类型安全等。不需要复杂的配置，并且可以利用Java 8 Stream API 做更加复杂的数据转换。  
支持主流的RDMS和更多的特性，如self-joins，union，存储过程，复杂的子查询等等。  
丰富的Fluent API和完善文档。  
runtime schema mapping 可以支持多个数据库schema访问。简单来说使用一个连接池可以访问N个DB schema，使用比较多的就是SaaS应用的多租户场景。

**三、示例**

原生SQL为：

select * from `product` as `prod` 
    left outer join
      (select  `comment`.`product_id`,count(*) as `comment_num` from `comment` 
       where `commment`.`product_id`=?
       group by `comment`.`product_id`
      ) 
    as `c1`
    on `prod`.`id`=`c1`.`product_id`
    where `prod`.`id`=?;

JOOQ的语法为：

List<MyProduct> products = dslContext.select()
          .from(Tables.PRODUCT)
          .leftJoin(DSL.table(
              DSL.select(Comment.COMMENT.PRODUCT_ID, DSL.count().as("comment_num"))
                .from(Tables.COMMENT) 
                .where(Comment.COMMENT.PRODUCT_ID.in(ids))
                .groupBy(Comment.COMMENT.PRODUCT_ID)
            ).as("c1")
          )
          .on(Product.PRODUCT.ID.eq(DSL.field(DSL.name("c1",  
              Comment.COMMENT.PRODUCT_ID.getName()),UInteger.class))) 
          .where(Product.PRODUCT.ID.in(ids))
          .fetch()
          .map(record -> {        
            MyProduct product = record.into(MyProduct.class); 
            return product;
          });

**四、JOOQ实现的增删改查 **

POM.XML加入以下依赖:

<dependency>
    	<groupId>com.alibaba</groupId>
    	<artifactId>druid</artifactId>
    	<version>1.1.8</version>
    </dependency>
    
    <dependency>
    	<groupId>org.jooq</groupId>
    	<artifactId>jooq</artifactId>
    	<version>3.10.5</version>
    </dependency>

druid.properties数据库连接信息:

#============================#
    #=====    数据库连接信息          =====#
    #============================#
    
    # MySQL
    #driverClassName=com.mysql.jdbc.Driver
    #url=jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf-8&useSSL=false&useInformationSchema=true
    #username=root
    #password=root
    
    # Oracle
    driverClassName=oracle.jdbc.driver.OracleDriver
    url=jdbc:oracle:thin:@106.14.160.67:1521:test
    username=test
    password=Eru43wPo
    
    
    filters=stat
    initialSize=2
    maxActive=300
    maxWait=60000
    timeBetweenEvictionRunsMillis=60000
    minEvictableIdleTimeMillis=300000
    #validationQuery=SELECT 1
    testWhileIdle=true
    testOnBorrow=false
    testOnReturn=false
    poolPreparedStatements=false
    maxPoolPreparedStatementPerConnectionSize=200

Druid连接类:

/**   
     * projectName: mybatis-generator-oracle   
     * fileName: DruidConnection.java   
     * packageName: com.fendo.gui.util   
     * date: 2018年2月27日上午9:16:36   
     * copyright(c) 2017-2020 fendo公司  
     */
    package com.fendo.gui.util;
    
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.Properties;
    
    import javax.sql.DataSource;
    
    import com.alibaba.druid.pool.DruidDataSourceFactory;
    
    /**
     * @title: DruidConnection.java
     * @package com.fendo.gui.util
     * @description: Druid连接类
     * @author: fendo
     * @date: 2018年2月27日 上午9:16:36
     * @version: V1.0
     */
    public class DruidConnection {
    
    	private static Properties properties = null;
    	private static DataSource dataSource = null;
    	private volatile static DruidConnection instatce = null;
    	private Connection connection = null;
    	public static String dbType = null;
    
    	// 私有构造函数,防止实例化对象
    	private DruidConnection() {
    
    	}
    
    	static {
    		try {
    			properties = new Properties();
    						
    			// 1.加载properties文件
    			InputStream is = DruidConnection.class.getClassLoader().getResourceAsStream("druid.properties");
    			
    			// 2.加载输入流
    			properties.load(is);
    			
    			String propertiesString = properties.toString();
    			
    			if(propertiesString.contains("oracle")) {
    				dbType = "oracle";
    				properties.put("remarksReporting","true");
    			}else {
    				dbType = "mysql";
    			}			
    			// 3.获取数据源
    			dataSource = getDatasource();
    
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    	}
    
    	/**
    	 * 用简单单例模式确保只返回一个链接对象
    	 * 
    	 * @return
    	 */
    	public static DruidConnection getInstace() {
    		if (instatce == null) {
    			synchronized (DruidConnection.class) {
    				if (instatce == null) {
    					instatce = new DruidConnection();
    				}
    			}
    		}
    		return instatce;
    	}
    
    	// 返回一个数据源
    	public DataSource getDataSource() {
    		return dataSource;
    	}
    
    	// 返回一个链接
    	public Connection getConnection() {
    		try {
    			connection = dataSource.getConnection();
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    		return connection;
    	}
    
    	// 加载数据源
    	private static DataSource getDatasource() {
    		DataSource source = null;
    		try {
    			source = DruidDataSourceFactory.createDataSource(properties);
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    		return source;
    	}
    
    	/**
    	 * 配置文件获取
    	 * @param string
    	 *            配置文件名
    	 * @return Properties对象
    	 */
    	private static Properties loadPropertiesFile(String fullFile) {
    		String webRootPath = null;
    		if (null == fullFile || fullFile.equals("")) {
    			throw new IllegalArgumentException("Properties file path can not be null" + fullFile);
    		}
    		webRootPath = DruidConnection.class.getClassLoader().getResource("").getPath();
    		webRootPath = new File(webRootPath).getParent();
    		InputStream inputStream = null;
    		Properties p = null;
    		try {
    			inputStream = new FileInputStream(new File(webRootPath + File.separator + fullFile));
    			p = new Properties();
    			p.load(inputStream);
    		} catch (Exception e) {
    			e.printStackTrace();
    		} finally {
    			try {
    				if (null != inputStream) {
    					inputStream.close();
    				}
    			} catch (Exception e) {
    				e.printStackTrace();
    			}
    		}
    
    		return p;
    	}
    
    }

Druid工具类:

/**   
     * projectName: mybatis-generator-oracle   
     * fileName: DruidUtil.java   
     * packageName: com.fendo.gui.util   
     * date: 2018年2月27日上午9:15:55   
     * copyright(c) 2017-2020 fendo公司  
     */
    package com.fendo.gui.util;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    import javax.sql.DataSource;
    
    /**     
     * @title: DruidUtil.java   
     * @package com.fendo.gui.util   
     * @description: Druid工具类  
     * @author: fendo  
     * @date: 2018年2月27日 上午9:15:55   
     * @version: V1.0     
    */
    public class DruidUtil {
    
        private static Connection connection = null;
        
        //获取元数据
        public static DataSource getDatasource() {
            DataSource dataSource = DruidConnection.getInstace().getDataSource();
            return dataSource;
        }
    
        //获取链接
        public static Connection getConnection() {
            connection = DruidConnection.getInstace().getConnection();
            return connection;
        }
    
        //归还资源
        public void close() {
            try {
                if(connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        public static void close(ResultSet rs,Connection conn){    
            close(rs);     
            close(conn);    
        }  
        
        public static void close(ResultSet rs, Statement stmt,Connection conn){    
            close(rs);    
            close(stmt);    
            close(conn);    
        }  
        
        //关闭连接  
        public static void close(Object o){    
            if (o == null){    
                return;    
            }    
            if (o instanceof ResultSet){    
                try {    
                    ((ResultSet)o).close();    
                } catch (SQLException e) {    
                    e.printStackTrace();    
                }    
            } else if(o instanceof Statement){    
                try {    
                    ((Statement)o).close();    
                } catch (SQLException e) {    
                    e.printStackTrace();    
                }    
            } else if (o instanceof Connection){    
                Connection c = (Connection)o;    
                try {    
                    if (!c.isClosed()){    
                        c.close();    
                    }    
                } catch (SQLException e) {    
                    e.printStackTrace();    
                }    
            }      
        }    
    	
    }

JOOQ测试类:

/**   
     * projectName: mybatis-generator-oracle   
     * fileName: JooqDao.java   
     * packageName: com.fendo.gui.jooq   
     * date: 2018年2月27日上午9:27:02   
     * copyright(c) 2017-2020 fendo公司  
     */
    package com.fendo.gui.jooq;
    
    import java.util.Map;
    import java.util.Set;
    
    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.fendo.gui.util.DruidUtil;
    
    /**     
     * @title: JooqDao.java   
     * @package com.fendo.gui.jooq   
     * @description: Jooq简单的增删改查  
     * @author: fendo  
     * @date: 2018年2月27日 上午9:27:02   
     * @version: V1.0     
    */
    public class JooqDao {
    
        private DSLContext dslContext= null; 
        
        /**
         * 
         *@title getdslContext   
         *@description: 获取DSLContext对象  
         *@author: fendo  
         *@date: 2018年2月27日 上午9:30:55  
         *@return  
         *@throws
         */
        private DSLContext getdslContext()  
        {  
        	//获取连接
            dslContext = DSL.using(DruidUtil.getConnection());  
            return dslContext;  
        }
        
        /**
         * 
         *@title select   
         *@description: 简单实体查询 - 根据用户名
         *@author: fendo  
         *@date: 2018年2月27日 上午10:01:35  
         *@param add  
         *@throws
         */
        public void select(String add)  
        {  
            DSLContext getdslContext = getdslContext();  
            Table<Record> table = DSL.table("user");  
            SelectQuery<Record> selectQuery = getdslContext.selectQuery(table);//获取查询对象  
            Condition eq = DSL.field("name").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("name"));  
            }  
          }  
        
        
        /**
         * 
         *@title update   
         *@description: 实体更新   
         *@author: fendo  
         *@date: 2018年2月27日 上午10:01:07  
         *@param name  
         *@throws
         */
        public void update(String name)  
        {  
            DSLContext getdslContext = getdslContext();  
            Table<Record> table = DSL.table("user");  
            UpdateQuery<Record> updateQuery = getdslContext.updateQuery(table);//获取更新对象  
            updateQuery.addValue(DSL.field("name"), "new-name");//更新email字段的值为new-email  
            Condition eq = DSL.field("name").eq(name);//更新username为name的email字段  
            updateQuery.addConditions(eq);  
            int execute = updateQuery.execute();  
            System.out.println(execute);  
            select("dreamlu");  
        }  
        
        
        /**
         * 
         *@title getVal   
         *@description: 原生态的sql查询  
         *@author: fendo  
         *@date: 2018年2月27日 上午10:01:15    
         *@throws
         */
        public void getVal()  
        {  
            DSLContext getdslContext = getdslContext();  
            Table<Record> table = DSL.table("user");//表名  
    //        Result<Record> fetch = getdslContext.select().from(table).where("status = 0").and("id > 1").orderBy(DSL.field("create_time").asc()).fetch();  
    //        for (Object aResult : fetch) {  
    //            Record record = (Record) aResult;  
    //            System.out.println(record);  
    //        }  
            Map<String, Object> fetchAnyMap = getdslContext.select().from(table).where("status = 0").and("id > 1").orderBy(DSL.field("create_time").asc()).fetchAnyMap(); 
            Set<String> keySet = fetchAnyMap.keySet(); 
            for(String s:keySet) 
            { 
                System.out.println("key--"+s+"--val:"+fetchAnyMap.get(s)); 
            } 
        }  
        
        
        /**
         * 
         *@title exits   
         *@description: 验证DSL.exists方法  
         *@author: fendo  
         *@date: 2018年2月27日 上午10:01:24    
         *@throws
         */
        public void exits()  
        {  
            DSLContext getdslContext = getdslContext();  
            
            Condition condition = DSL.exists(DSL.select(DSL.field("name")));  
            Table<Record> table = DSL.table("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("name"));  
            }  
        }  
        public static void main(String[] args) {  
            JooqDao jooqDao = new JooqDao();  
            jooqDao.select("admin");  
    //        jooqDao.update("shangfox1");  
    //        jooqDao.exits();  
    //        jooqDao.getVal();  
        }  
    	
    }