425、Java框架79 -【Spring + Hibernate - 分页、查询总数、使用c3p0连接池、连接Oracle】 2020.12.01

目录

0、分页

1、使用DetachedCriteria进行分页查询

2、查询总数

3、条件查询

4、模糊查询

5、使用c3p0连接池

6、applicationContext.xml

7、连接Oracle

8、hbm

9、applicationContext.xml

10、sql 语句

11、TestSpring

12、参考链接

---

 

0、分页

Spring和Hibernate整合是借助HibernateTemplate进行的. 
分页查询比起直接使用hibernate有所区别 
主要会用到DetachedCriteria进行

 

1、使用DetachedCriteria进行分页查询

package com.how2java.test;
  
import java.util.List;
 
import org.hibernate.criterion.DetachedCriteria;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
 
import com.how2java.dao.CategoryDAO;
import com.how2java.pojo.Category;
  
public class TestSpring {
  
    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext(
                new String[] { "applicationContext.xml" });
        CategoryDAO dao = (CategoryDAO) context.getBean("dao");
         
        DetachedCriteria dc = DetachedCriteria.forClass(Category.class);
        int start =5;//从多少开始查询
        int count =10;//每页显示数量
        List<Category> cs= dao.findByCriteria(dc,start,count);
        System.out.println(cs);
    }
}

2、查询总数

使用HibernateTemplate查询总数

通过find方法执行select(*)，接着会返回一个List里面第一个元素即总数

package com.how2java.test;
  
import java.util.List;
 
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
 
import com.how2java.dao.CategoryDAO;
  
public class TestSpring {
  
    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext(
                new String[] { "applicationContext.xml" });
        CategoryDAO dao = (CategoryDAO) context.getBean("dao");
         
        List<Long> l =dao.find("select count(*) from Category c");
        long total = l.get(0);
        System.out.println(total);
    }
}

 

3、条件查询

使用HibernateTemplate进行模糊查询 
其思路和直接使用hibernate有所区别

4、模糊查询

分别使用Hql和Criteria进行模糊查询

package com.how2java.test;
   
import java.util.List;
  
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
  
import com.how2java.dao.CategoryDAO;
import com.how2java.pojo.Category;
   
public class TestSpring {
   
    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext(
                new String[] { "applicationContext.xml" });
        CategoryDAO dao = (CategoryDAO) context.getBean("dao");
          
        List<Category> cs =dao.find("from Category c where c.name like ?", "%c%");
  
        for (Category c : cs) {
            System.out.println(c.getName());
        }
  
        DetachedCriteria dc = DetachedCriteria.forClass(Category.class);
        dc.add(Restrictions.like("name", "%分类%"));
        cs =dao.findByCriteria(dc);
  
        for (Category c : cs) {
            System.out.println(c.getName());
        }
          
    }
}

5、使用c3p0连接池

配置applicationContext.xml,使得其支持c3p0数据库连接池

6、applicationContext.xml

调整applicationContext.xml以使得其支持c3p0数据库连接池
主要是修改数据源database
1. database class 
从 org.springframework.jdbc.datasource.DriverManagerDataSource
改为 com.mchange.v2.c3p0.ComboPooledDataSource
2. driverClassName 改为 driverClass
3. url 改为 jdbcUrl
4. username 改为 user
5. 增加c3p0相关配置

<?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:aop="http://www.springframework.org/schema/aop"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
   http://www.springframework.org/schema/beans
   http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
   http://www.springframework.org/schema/aop
   http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
   http://www.springframework.org/schema/tx
   http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
   http://www.springframework.org/schema/context     
   http://www.springframework.org/schema/context/spring-context-3.0.xsd">
  
    <bean name="c" class="com.how2java.pojo.Category">
        <property name="name" value="yyy" />
    </bean>
     
    <bean name="dao" class="com.how2java.dao.CategoryDAO">
        <property name="sessionFactory" ref="sf" />
    </bean>
 
    <bean name="sf"
        class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
        <property name="dataSource" ref="ds" />
        <property name="mappingResources">
            <list>
                <value>com/how2java/pojo/Category.hbm.xml</value>
            </list>
        </property>
        <property name="hibernateProperties">
            <value>
                hibernate.dialect=org.hibernate.dialect.MySQLDialect
                hibernate.show_sql=true
                hbm2ddl.auto=update
            </value>
        </property>
    </bean>   
         
    <bean name="ds"
        class="com.mchange.v2.c3p0.ComboPooledDataSource">
 
        <property name="driverClass" value="com.mysql.jdbc.Driver" />
        <property name="jdbcUrl"
            value="jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8" />
        <property name="user" value="root" />
        <property name="password" value="admin" />
         
        <!--连接池中保留的最小连接数。-->
        <property name="minPoolSize" value="10" />
        <!--连接池中保留的最大连接数。Default: 15 --> 
         
        <property name="maxPoolSize" value="100" />
        <!--最大空闲时间,1800秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 --> 
         
        <property name="maxIdleTime" value="1800" />
        <!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 --> 
         
        <property name="acquireIncrement" value="3" />
        <!--最大的Statements条数 -->
        <property name="maxStatements" value="1000" />
        <!--初始化10条连接 -->
        <property name="initialPoolSize" value="10" />
        <!--定义在从数据库获取新连接失败后重复尝试的次数。Default: 30 --> 
        <property name="acquireRetryAttempts" value="30" />
         
        <!--每隔60秒发一次心跳信号到数据库，以保持连接的活性 -->
        <property name="idleConnectionTestPeriod" value="60" />
         
    </bean>
</beans>

7、连接Oracle

在整合步骤的基础上，把本来连接Mysql改为连接oracle

8、hbm

设置自增长id为sequence

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
 
<hibernate-mapping package="com.how2java.pojo">
    <class name="Category" table="bigsword.category_">
        <id name="id" column="id">
            <generator class="native">        
                <param name="sequence">bigsword.category_sequence</param>
            </generator>
        </id>
        <property name="name" />
 
    </class>
     
</hibernate-mapping>

9、applicationContext.xml

设置连接oracle的 
驱动 
url 
方言 
账号密码

<?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:aop="http://www.springframework.org/schema/aop"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
   http://www.springframework.org/schema/beans
   http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
   http://www.springframework.org/schema/aop
   http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
   http://www.springframework.org/schema/tx
   http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
   http://www.springframework.org/schema/context     
   http://www.springframework.org/schema/context/spring-context-3.0.xsd">
  
    <bean name="c" class="com.how2java.pojo.Category">
        <property name="name" value="yyy" />
    </bean>
     
    <bean name="dao" class="com.how2java.dao.CategoryDAO">
        <property name="sessionFactory" ref="sf" />
    </bean>
 
    <bean name="sf"
        class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
        <property name="dataSource" ref="ds" />
        <property name="mappingResources">
            <list>
                <value>com/how2java/pojo/Category.hbm.xml</value>
            </list>
        </property>
        <property name="hibernateProperties">
            <value>
                hibernate.dialect=org.hibernate.dialect.OracleDialect
                hibernate.show_sql=true
                hbm2ddl.auto=update
            </value>
        </property>
    </bean>   
         
    <bean name="ds"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
        <property name="url" value="jdbc:oracle:thin:@localhost:1521:XE" />
        <property name="username" value="bigsword" />
        <property name="password" value="bigsword" />
    </bean>  
 
</beans>

10、sql 语句

首先用bigsword账号登录 
创建sequence 
创建table

create sequence bigsword.category_sequence
 
create table bigsword.category_(
id number,
name varchar2(30)
 
)

11、TestSpring

运行测试代码

package com.how2java.test;
  
import java.util.List;
 
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
 
import com.how2java.dao.CategoryDAO;
import com.how2java.pojo.Category;
  
public class TestSpring {
  
    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext(
                new String[] { "applicationContext.xml" });
        CategoryDAO dao = (CategoryDAO) context.getBean("dao");
        Category c =new Category();
        c.setName("category 1");
        dao.save(c);
         
        List<Category> cs= dao.find("from Category c");
        System.out.println(cs);
    }
}

12、参考链接

[01]How2j - Spring + Hibernate - 分页

[02]How2j - Spring + Hibernate - 查询总数

[03]How2j - Spring + Hibernate - 连接c3p0连接池

[04]How2j - Spring + Hibernate - 连接Oracle