代码拉取完成,页面将自动刷新
#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或Oracle
dataSource.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:on
query.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
*
*/
@Service
public 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));
}
}
#最近跟新
#*注意,使用前提
#附件中有一个编译好的版本,可以直接使用
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。
1. 开源生态
2. 协作、人、软件
3. 评估模型