客服电话
APP下载
迪恩网络APP
随时随地掌握行业动态
官方微信
扫描二维码
关注迪恩网络微信公众号
开源软件名称：codefinger-dao开源软件地址：https://gitee.com/codefinger/codefinger-dao开源软件介绍：#codefinger-dao #简介 数据库访问工具，功能强大、简洁、高效、零配置，支持MySQL、SQLServer、Oracle。 #特性（完整Demo，请看org.codefinger.test.DaoUtilDemo） ###1.语法分析，智能展开、拼接SQL语句 // 前面这里采用阿里巴巴的数据库连接池，主要是为了演示执行的SQL语句(您可以采用其它数据连接池)DruidDataSource dataSource = new DruidDataSource();Log4jFilter log4jFilter = new Log4jFilter();log4jFilter.setResultSetLogEnabled(false);log4jFilter.setStatementLogEnabled(false);log4jFilter.setStatementExecutableSqlLogEnable(true);Filter filter = log4jFilter;dataSource.setProxyFilters(Arrays.asList(filter));// 这里采用MySQL进行演示，您也可以选择SQLServer或OracledataSource.setUrl("jdbc:mysql://192.168.189.135:3306/codefinger?useUnicode=true&characterEncoding=utf-8");dataSource.setUsername("root");dataSource.setPassword("root");// 1.这里是真正开始，创建数据库工具DaoUtil daoUtil = new DaoUtil();daoUtil.setDataSource(dataSource);// 2.然后您就可以创建查询对象了Query query = daoUtil.createQuery("select * from customer where f_name left like :name and f_age > :age");// 3.进行查询query.putParam("name", "张三").getMapList();/* * 这时，工具自动进行了如下查询（只用了其中一个查询条件）： * * SELECT * CUSTOMER.F_ID, * CUSTOMER.F_NAME, * CUSTOMER.F_AGE, * CUSTOMER.F_MONEY * FROM * CUSTOMER * WHERE * F_NAME LIKE '张三%' */query.putParam("age", 15).getMapList();/* * 这时，工具自动进行了如下查询（这次用了另一个查询条件）： * * SELECT * CUSTOMER.F_ID, * CUSTOMER.F_NAME, * CUSTOMER.F_AGE, * CUSTOMER.F_MONEY * FROM * CUSTOMER * WHERE * F_AGE > 15 */query.putParam("name", "张三").putParam("age", 15).getMapList();/* * 这时，工具自动进行了如下查询（这次两个查询条件都同时利用了）： * * SELECT * CUSTOMER.F_ID, * CUSTOMER.F_NAME, * CUSTOMER.F_AGE, * CUSTOMER.F_MONEY * FROM * CUSTOMER * WHERE * F_NAME LIKE '张三%' * AND F_AGE > 15 */// 4.您可以试试更复杂的查询条件query = daoUtil.createQuery("select * from customer where f_name left like :name and (f_age >= :minAge or f_age <= :maxAge) order by f_age,f_name desc");query.putParam("minAge", 15)// 只根据最小年龄进行筛选 .setOrders(0)// 只根据年龄进行排序 .getMapList();/* * 这时，工具自动进行了如下查询（是不是很智能）： * * SELECT * CUSTOMER.F_ID, * CUSTOMER.F_NAME, * CUSTOMER.F_AGE, * CUSTOMER.F_MONEY * FROM * CUSTOMER * WHERE * F_AGE >= 15 * ORDER BY * F_AGE ASC */###2.支持命名参数和顺序参数 // 刚才上面的掩饰采用的就是命名参数，这里采用顺序参数query = daoUtil.createQuery("select * from customer where f_name all like ? and (f_age >= ? or f_age <= ?) order by f_age,f_name desc");query.setParams("张三", null, 20)// 只根据姓名和最大年龄进行筛选 .setOrders(1)// 只根据姓名进行排序 .getMapList();/* * 这时，工具自动进行了如下查询（真的很智能！）： * * SELECT * CUSTOMER.F_ID, * CUSTOMER.F_NAME, * CUSTOMER.F_AGE, * CUSTOMER.F_MONEY * FROM * CUSTOMER * WHERE * F_NAME LIKE '%张三%' * AND F_AGE <= 20 * ORDER BY * F_NAME DESC */###3.支持复杂的查询（左、内连接查询、子查询、Union、各种条件表达式like、in、exists、any、all等） // @formatter:off// 刚才上面的SQL语句还是太简单了，来点复杂的看看query = daoUtil.createQuery( "select " + "A.f_id id," + "B.f_name as name," + "C.f_age age," + "D.f_money as money " + "from " + "customer A " + "inner join customer B on B.f_id = A.f_id " + "left join customer C on C.f_id = B.f_id " + "inner join customer D on D.f_id = C.f_id " + "where " + "(A.f_age >= :minAge or A.f_age <= :minAge) " + "and B.f_name left like :leftName " + "and C.f_name all like :allName " + "and D.f_money not in (3.5,3.6,:moneyNotIn) " + "and D.f_age is not null " + "and D.f_age = (select f_age from customer where f_age != :notAge) " + "group by " + "A.*,B.*,C.*,D.* " + "having " + "AVG(D.f_money) > 1000 " + "union all " + "select " + "f_id id," + "f_name name," + "f_age as age," + "f_money money " + "from customer " + "order by " + "money desc,age asc");// @formatter:onquery.putParam("notAge", 25) // 年龄不等于25 .putParam("moneyNotIn", Arrays.asList(3.7, 9.125, 10000.9)) // Money不包含列表中的值 .putParam("allName", "王") // 模糊查询，姓名中包含‘王’的 .putParam("minAge", 10)// 最小年龄 .setOrders(0) // 只按照Money排序 .getMapSet();/* * 就算是这么复杂的SQL语句，也能够被智能分析出来： * * SELECT * A.F_ID AS ID, * B.F_NAME AS NAME, * C.F_AGE AS AGE, * D.F_MONEY AS MONEY * FROM * CUSTOMER A * INNER JOIN CUSTOMER B ON B.F_ID = A.F_ID * LEFT JOIN CUSTOMER C ON C.F_ID = B.F_ID * INNER JOIN CUSTOMER D ON D.F_ID = C.F_ID * WHERE * (A.F_AGE >= 10 OR A.F_AGE <= 10) * AND C.F_NAME LIKE '%王%' * AND D.F_MONEY NOT IN (3.5, 3.6, 3.7, 9.125, 10000.9) * AND D.F_AGE IS NOT NULL * AND D.F_AGE = (SELECT F_AGE FROM CUSTOMER WHERE F_AGE != 25) * GROUP BY * A.F_ID, A.F_NAME, A.F_AGE, A.F_MONEY, B.F_ID, B.F_NAME, * B.F_AGE, B.F_MONEY, C.F_ID, C.F_NAME, C.F_AGE, C.F_MONEY, D.F_ID, * D.F_NAME, D.F_AGE, D.F_MONEY * HAVING * AVG(DISTINCT D.F_MONEY) > 1000 * UNION ALL * SELECT * F_ID AS ID, * F_NAME AS NAME, * F_AGE AS AGE, * F_MONEY AS MONEY * FROM CUSTOMER * ORDER BY * MONEY DESC */###4.支持总量（Count）和分页查询（总量查询能够查询出本次查询结果（不分页）的总记录数） // 让我们看看查询Count和分页有多简单QueryChain queryChain = query.putParam("notAge", 25) // 年龄不等于25 .putParam("moneyNotIn", Arrays.asList(3.7, 9.125, 10000.9)) // Money不包含列表中的值 .putParam("allName", "王") // 模糊查询，姓名中包含‘王’的 .putParam("minAge", 10)// 最小年龄 .setOrders(0) // 只按照Money排序 .setPage(5, 10); // 查第5页，每页显示10条// Count查询queryChain.queryCount();/* * SELECT * COUNT(1) * FROM * ( * SELECT * A.F_ID AS ID, * B.F_NAME AS NAME, * C.F_AGE AS AGE, * D.F_MONEY AS MONEY * FROM * CUSTOMER A * INNER JOIN CUSTOMER B ON B.F_ID = A.F_ID * LEFT JOIN CUSTOMER C ON C.F_ID = B.F_ID * INNER JOIN CUSTOMER D ON D.F_ID = C.F_ID * WHERE * (A.F_AGE >= 10 OR A.F_AGE <= 10) * AND C.F_NAME LIKE '%王%' * AND D.F_MONEY NOT IN (3.5, 3.6, 3.7, 9.125, 10000.9) * AND D.F_AGE IS NOT NULL * AND D.F_AGE = (SELECT F_AGE FROM CUSTOMER WHERE F_AGE != 25) * GROUP BY * A.F_ID, A.F_NAME, A.F_AGE, A.F_MONEY, B.F_ID, B.F_NAME, * B.F_AGE, B.F_MONEY, C.F_ID, C.F_NAME, C.F_AGE, C.F_MONEY, D.F_ID, * D.F_NAME, D.F_AGE, D.F_MONEY * HAVING * AVG(DISTINCT D.F_MONEY) > 1000 * UNION ALL * SELECT * F_ID AS ID, F_NAME AS NAME, F_AGE AS AGE, F_MONEY AS MONEY * FROM * CUSTOMER * ) ALL_CONTENT */// 分页查询queryChain.getMapList();/* * SELECT * A.F_ID AS ID, * B.F_NAME AS NAME, * C.F_AGE AS AGE, * D.F_MONEY AS MONEY * FROM * CUSTOMER A * INNER JOIN CUSTOMER B ON B.F_ID = A.F_ID * LEFT JOIN CUSTOMER C ON C.F_ID = B.F_ID * INNER JOIN CUSTOMER D ON D.F_ID = C.F_ID * WHERE * (A.F_AGE >= 10 OR A.F_AGE <= 10) * AND C.F_NAME LIKE '%王%' * AND D.F_MONEY NOT IN (3.5, 3.6, 3.7, 9.125, 10000.9) * AND D.F_AGE IS NOT NULL * AND D.F_AGE = (SELECT F_AGE FROM CUSTOMER WHERE F_AGE != 25) * GROUP BY * A.F_ID, A.F_NAME, A.F_AGE, A.F_MONEY, B.F_ID, B.F_NAME, * B.F_AGE, B.F_MONEY, C.F_ID, C.F_NAME, C.F_AGE, C.F_MONEY, D.F_ID, * D.F_NAME, D.F_AGE, D.F_MONEY * HAVING * AVG(DISTINCT D.F_MONEY) > 1000 * UNION ALL * SELECT * F_ID AS ID, F_NAME AS NAME, F_AGE AS AGE, F_MONEY AS MONEY * FROM * CUSTOMER * ORDER BY * MONEY DESC * LIMIT 40, 10 */###5.多样化的返回结果 // 除了上面用到过的getMapList,还有其它可选的返回值类型// 您可以返回List泛型queryChain.getList(Customer.class);// 当您确定返回值只有一行数据时，您可以返回Pojo类型queryChain.fetch(Customer.class);// 当您确定返回值只有一行一列的时候，您可以这样int avg = daoUtil.createQuery("select sum(f_age) ageSum from customer").getUnique(DaoType.INT);System.out.println(avg);// 如果说您想要自己封装返回值类型，您可以这样queryChain.getResult(new QueryCallback<List<Customer>>() { @Override public List<Customer> getResult(ResultSet resultSet, String[] names) throws SQLException { List<Customer> customers = new ArrayList<Customer>(); PojoBuilder<Customer> builder = QueryUtil.getPojoBuilder(Customer.class, resultSet, names); while (resultSet.next()) { Customer customer = builder.nextPojo(); customer.setF_name("我想自己修改返回值"); customers.add(customer); } return customers; }});###6.这里几乎包含所有您需要用到的增删改操作 // 首先试试新增操作Customer customer = new Customer("张三", 19, 100);daoUtil.insert("customer", customer);System.out.println(customer.getF_id()); // 自动为Pojo对象生成了主键// 批量新增Customer[] customers = new Customer[] { //// 两个对象 new Customer("李四", 18, 100), // new Customer("王五", 17, 99) //};daoUtil.insert("customer", customers);daoUtil.insert("customer", Arrays.asList(customers)); // 也可以使用集合// 也可以采用链式调用的方式做新增daoUtil.insertInto("customer").set("f_name = ?, f_age = 16,f_money = ?").execute("赵六", 105);daoUtil.insertInto("customer").set("f_name = :name, f_age = :age")// .putParam("name", "田七")// .putParam("age", 15)// .execute();// 然后试试修改操作customer.setF_money(200);daoUtil.update("customer", customer);daoUtil.update("customer", Arrays.asList(customer, customer)); // 同样支持批量修改daoUtil.updateFrom("customer")// .set("f_name = :newName, f_age = :newAge, f_money = 100")// .where("f_id = :oldName and f_age > :oldAge")// .putParam("newName", "新名字")// .putParam("newAge", 20).putParam("oldName", "旧名字")// .putParam("oldAge", 18)// .execute();// 删除操作daoUtil.deleteByID("customer", 18); // 通过主键删除daoUtil.deleteByID("customer", 18, 19, 20); // 批量删除daoUtil.deleteFrom("customer").where("f_name left like ?").execute("张三");// 名称像“张三%”的将被删除daoUtil.deleteFrom("customer").execute();// 删除“customer”表的所有记录#轻松与Spring进行集成 ###Spring的设计的确是相当精妙，目前大多数项目都基于是Spring的容器来做的###Spring中提供了声明式事物、注解等等特性使得我们开发更加简单、容易###下面将演示如何在Spring中进行完美的集成（以下示例的所有源代码都在org.codefinger.test.spring包中能找到） ####首先是Spring的配置文件 <?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd"> <!-- 阿里巴巴数据库连接池的SQL日志配置 --> <bean id="log-filter" class="com.alibaba.druid.filter.logging.Log4jFilter"> <property name="resultSetLogEnabled" value="false" /> <property name="statementExecutableSqlLogEnable" value="true" /> <property name="statementLogEnabled" value="false" /> </bean> <!-- 配置一下阿里巴巴的数据连接池，您可以采用其它的数据库连接池 --> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> <!-- <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:orcl"></property> --> <!-- <property name="url" value="jdbc:sqlserver://127.0.0.1:1433;databaseName=dcode"></property> --> <property name="url" value="jdbc:mysql://192.168.189.135:3306/codefinger?useUnicode=true&&characterEncoding=utf-8"></property> <property name="username" value="root"></property> <property name="password" value="root"></property> <property name="proxyFilters"> <list> <ref bean="log-filter" /> </list> </property> <!-- 配置初始化大小、最小、最大 --> <property name="initialSize" value="1" /> <property name="minIdle" value="1" /> <property name="maxActive" value="20" /> <!-- 配置获取连接等待超时的时间 --> <property name="maxWait" value="60000" /> <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 --> <property name="timeBetweenEvictionRunsMillis" value="60000" /> <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 --> <property name="minEvictableIdleTimeMillis" value="300000" /> <property name="testWhileIdle" value="true" /> <property name="testOnBorrow" value="false" /> <property name="testOnReturn" value="false" /> <!-- 打开PSCache，并且指定每个连接上PSCache的大小 --> <property name="poolPreparedStatements" value="true" /> <property name="maxPoolPreparedStatementPerConnectionSize" value="20" /> <!-- 配置监控统计拦截的filters --> <!-- <property name="filters" value="stat" /> --> </bean> <!-- 事物管理对象 --> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource"></property> </bean> <!-- 数据源代理对象 --> <bean id="transactionAwareDataSourceProxy" class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy"> <property name="targetDataSource" ref="dataSource"></property> </bean> <!-- 这样您就可以使用注解式的事物 --> <tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true" /> <!-- 这是声明式事物配置,配置事务传播特性 --> <tx:advice id="TestAdvice" transaction-manager="transactionManager"> <tx:attributes> <tx:method name="save*" propagation="REQUIRED" /> <tx:method name="del*" propagation="REQUIRED" /> <tx:method name="update*" propagation="REQUIRED" /> <tx:method name="add*" propagation="REQUIRED" /> <tx:method name="find*" propagation="REQUIRED" /> <tx:method name="get*" propagation="REQUIRED" /> <tx:method name="apply*" propagation="REQUIRED" /> </tx:attributes> </tx:advice> <!-- 配置参与事务的类 --> <aop:config> <aop:pointcut id="allTestServiceMethod" expression="execution(* org.codefinger.test.spring.*.*(..))" /> <aop:advisor pointcut-ref="allTestServiceMethod" advice-ref="TestAdvice" /> </aop:config> <!-- 数据库工具配置 --> <bean id="daoUtil" class="org.codefinger.dao.DaoUtil"> <!-- 如需事物生效，必须使用Spring的DataSource代理才行（网上好多教程中的事物没配置代理，其实根本就不生效） --> <property name="dataSource" ref="transactionAwareDataSourceProxy"></property> </bean> <!-- 这时，您可以将复杂的查询配置在Spring的配置文件中（您也可以将SQL配置到单独的Spring配置文件中，然后import进来，这是不是像MyBatis） --> <bean id="queryCustomer" class="org.codefinger.dao.SpringQuery"> <property name="sql"> <value> <![CDATA[ select A.f_id id, B.f_name as name, C.f_age age, D.f_money as money from customer A inner join customer B on B.f_id = A.f_id left join customer C on C.f_id = B.f_id inner join customer D on D.f_id = C.f_id where (A.f_age >= :minAge or A.f_age <= :minAge) and B.f_name left like :leftName and C.f_name all like :allName and D.f_money not in (3.5,3.6,:moneyNotIn) and D.f_age is not null and D.f_age = (select f_age from customer where f_age != :notAge) group by A.*,B.*,C.*,D.* having AVG(D.f_money) > 1000 union all select f_id id, f_name name, f_age as age, f_money money from customer order by money desc,age asc ]]> </value> </property> </bean> <!-- 让Spring扫描我们的控制层 --> <context:component-scan base-package="org.codefinger.test.spring" /></beans>####然后我们可以写个Service试试 package org.codefinger.test.spring;import java.util.List;import java.util.Map;import org.codefinger.dao.DaoUtil;import org.codefinger.dao.Query;import org.codefinger.test.Customer;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.beans.factory.annotation.Qualifier;import org.springframework.stereotype.Service;/*** * <p> * 您可以把{@link MyService}当作为您项目中Action、Controller、Service等等<br/> * </p> * * @author jack * */@Servicepublic class MyService { /** * 从Spring获取配置好的SQL */ @Autowired @Qualifier("queryCustomer") private Query queryCustomer; /** * 自动注入数据库工具 */ @Autowired private DaoUtil daoUtil; /** * 根据最小年龄和名称做模糊查询 * * @param minAge * @param allName * @return */ public List<Map<String, Object>> getMapList(int minAge, String allName) { return queryCustomer.putParam("minAge", minAge).putParam("allName", allName).getMapList(); } /** * 添加 * * @param customer * @return */ public boolean add(Customer customer) { return daoUtil.insert("customer", customer); } /** * 修改 * * @param customer * @return */ public boolean update(Customer customer) { return daoUtil.update("customer", customer); } /* * 当然，您还能用它做更多的数据访问工作（复杂的修改、删除、分页等等），这就又您自由发挥了...... */}####最后看看成果 package org.codefinger.test.spring;import org.codefinger.test.Customer;import org.springframework.context.ApplicationContext;import org.springframework.context.support.ClassPathXmlApplicationContext;public class SpringDemo { @SuppressWarnings("resource") public static void main(String[] args) { // 这里做演示，手动加载Spring文件（实际项目中Spring配置文件可能在Web容器中做加载，但原理都是一样） ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml"); // 从容器中获取我们的控制层做测试 MyService myService = applicationContext.getBean(MyService.class); myService.getMapList(15, "张三"); myService.add(new Customer("李四", 20, 100)); myService.update(new Customer("王五", 17, 30)); }}#最近跟新
#*注意，使用前提
#附件中有一个编译好的版本，可以直接使用  |
请发表评论