当前位置:Gxlcms > 数据库问题 > Spring之JDBC Template

Spring之JDBC Template

时间:2021-07-01 10:21:17 帮助过:14人阅读

 

时间:2017-2-5 18:16

 

——Spring对不同持久化技术的支持

Spring为各种支持的持久化技术都提供了简单操作的模板和回调。

ORM持久化技术:
    JDBC:
        org.springframework.jdbc.core.JdbcTemplate
 

    Hibernate3.0:
        org.springframework.orm.hibernate3.HibernateTemplate
      IBatis(MyBatis):
        org.springframework.orm.ibatis.SqlMapClientTemplate
      JPA:
        org.springframework.orm.jpa.JpaTemplate   ——JDBC Template

1、Spring JDBC是Spring提供的持久层技术。
2、简化JDBC API的开发,使用上和Apache公司的DBUtils框架非常相似。
3、导入必要jar包到工程目录。
4、导入Spring核心开发包
    >   spring-beans-3.2.0.RELEASE.jar
    >   spring-context-3.2.0.RELEASE.jar     >   spring-core-3.2.0.RELEASE.jar     >   spring-expression-3.2.0.RELEASE.jar 5、导入commons-logging日志包     >   commons-logging-1.1.1.jar 6、导入JDBC模板开发包
    >   spring-jdbc-3.2.0.RELEASE.jar         JDBC工具。     >   spring-tx-3.2.0.RELEASE.jar
        事务管理。 7、导入MySQL驱动
——JDBC Template入门

1、导入相应jar包
    *   spring-jdbc-3.2.0.RELEASE.jar
    *   spring-tx-3.2.0.RELEASE.jar
    *   mysql-connector-java-5.1.35-bin.jar
    *   mchange-commons-java-0.2.3.4.jar

2、创建applicationContext.xml配置文件

3、编写一个测试类
import org.junit.Test; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.datasource.DriverManagerDataSource;   public class SpringDemo1 {       @Test     public void fun1(){         // 创建连接池         DriverManagerDataSource dataSource = new DriverManagerDataSource();

        // 设置参数         dataSource.setDriverClassName("com.mysql.jdbc.Driver");         dataSource.setUrl("jdbc:mysql:///spring3_day02");         dataSource.setUsername("root");         dataSource.setPassword("Admin123");           // 使用JDBC的模板         JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        // 执行SQL语句         jdbcTemplate.execute("create table User (id int primary key auto_increment, name varchar(20))");     } }
——使用Spring默认连接池

从刚刚的示例中已经看出,Spring JDBC模板的使用,必须依赖DataSource数据库连接池。

在实际开发中,通常使用Spring配置文件来配置JDBC Template。

常用数据源:
    1)Spring数据源实现类:DriverManagerDataSource
    2)DBCP数据源:BasicDataSource
    3)C3P0数据源:ComboPooledDataSource

1、创建applicationContext.xml配置文件

<?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"     xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">       <!-- Spring默认连接池 -->     <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">         <property name="url" value="jdbc:mysql:///spring3_day02" />         <property name="username" value="root" />         <property name="password" value="Admin123" />     </bean>
        <!-- 定义JDBCTemplate类 -->     <!-- 为JDBC模板注入连接池属性 -->     <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" >         <property name="dataSource" ref="dataSource" />     </bean> </beans>
2、测试代码:

    @Test     public void fun(){         ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");         jdbcTemplate.execute("create table User3 (id int primary key auto_increment, name varchar(20))");     }
——DBCP连接池

1、导入相应jar包
    *   com.springsource.org.apache.commons.dbcp-1.2.2.osgi.jar
    *   com.springsource.org.apache.commons.pool-1.5.3.jar

2、创建配置文件

<?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"     xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">       <!-- 配置DBCP连接池 -->     <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">         <property name="driverClassName" value="com.mysql.jdbc.Driver" />         <property name="url" value="jdbc:mysql:///spring3_day02" />         <property name="username" value="root" />         <property name="password" value="Admin123" />     </bean>
    <!-- 定义JDBCTemplate类 -->     <!-- 为JDBC模板注入连接池属性 -->     <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" >         <property name="dataSource" ref="dataSource" />     </bean>   </beans>
3、测试代码

    @Test     public void fun(){         ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");         jdbcTemplate.execute("create table User4 (id int primary key auto_increment, name varchar(20))");     }

——C3P0连接池

1、导入相应jar包
    *   com.springsource.com.mchange.v2.c3p0-0.9.1.2.jar

2、创建配置文件

<?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"     xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

    <!-- 配置C3P0连接池 -->     <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">         <property name="driverClass" value="com.mysql.jdbc.Driver" />         <property name="jdbcUrl" value="jdbc:mysql:///spring3_day02" />         <property name="user" value="root" />         <property name="password" value="Admin123" />       </bean>       <!-- 定义JDBCTemplate类 -->     <!-- 为JDBC模板注入连接池属性 -->     <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" >         <property name="dataSource" ref="dataSource" />     </bean>
  </beans>   3、测试代码

    public void fun(){         ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");         jdbcTemplate.execute("create table User5 (id int primary key auto_increment, name varchar(20))");     }
——将参数配置到属性文件中

1、在src下创建jdbc.properties属性文件
    jdbc.driver = com.mysql.jdbc.Driver     jdbc.url = jdbc:mysql:///spring3_day02     jdbc.username = root     jdbc.password = Admin123
2、在applicationContext.xml中使用属性文件配置的内容
    1)第一种写法
        <?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:context="http://www.springframework.org/schema/context"             xsi:schemaLocation="http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/contexthttp://www.springframework.org/schema/context/spring-context.xsd">               <bean                 class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">                 <property name="location" value="classpath:jdbc.properties" />             </bean>                 <!-- 配置C3P0连接池 -->             <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">                 <property name="driverClass" value="${jdbc.driver}" />                 <property name="jdbcUrl" value="${jdbc.url}" />                 <property name="user" value="${jdbc.username}" />                 <property name="password" value="${jdbc.password}" />             </bean>

            <!-- 定义JDBCTemplate类 -->             <!-- 为JDBC模板注入连接池属性 -->             <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">                 <property name="dataSource" ref="dataSource" />             </bean>           </beans>
    2)第二种写法
        <?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:context="http://www.springframework.org/schema/context"             xsi:schemaLocation="http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/contexthttp://www.springframework.org/schema/context/spring-context.xsd">               <context:property-placeholder location="classpath:jdbc.properties"/>               <!-- 配置C3P0连接池 -->             <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">                 <property name="driverClass" value="${jdbc.driver}" />                 <property name="jdbcUrl" value="${jdbc.url}" />                 <property name="user" value="${jdbc.username}" />                 <property name="password" value="${jdbc.password}" />             </bean>               <!-- 定义JDBCTemplate类 -->             <!-- 为JDBC模板注入连接池属性 -->             <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">                 <property name="dataSource" ref="dataSource" />             </bean>         </beans>   ——JDBCTemplate的CRUD操作


1、为了方便Dao中注入JDBC Template,Spring为每一个持久化技术都提供了支持类。
    1)JDBC:
        org.springframework.jdbc.core.support.JdbcDaoSupport     2)Hibernate 3.0:
        org.springframework.orm.hibernate3.support.HibernateDaoSupport     3)iBatis:
        org.springframework.orm.ibatis.support.SqlMapClientDaoSupport
2、编写Dao,并继承JdbcDaoSupport类。
    JdbcDaoSupport中提供了JDBCTemplate实例,所以在自己的Dao中不需要再定义JDBCTemplate并且提供注入了,可以直接使用getJdbcTemplate()方法来获取实例。

3、进行CUD操作
    1)保存:update(String sql, Object ... args)
    2)修改:update(String sql, Object ... args)
    3)删除:update(String sql, Object ... args)

4、查询操作
    1)简单查询
        *   select count(*) from user
            >   queryForLong(String sql)
            >   返回一个简单类型 
        *   select name from user where id = ?
            >   queryForObject(String sql, Class, Object...args)
            >   返回单个对象 

    2)复杂查询
        返回对象或集合。
        *   select * from user where id = ?
            >   queryForObject(String sql, RowMapper<T> rowMapper, Object...args)
            >   返回单个对象 
        *   select * from user
            >   query(String sql, RowMapper<T> rowMapper, Object ... args)
            >   返回多个对象 

5、示例代码


User实体类:

public class User {     private Integer id;     private String name;       public Integer getId() {         return id;     }       public void setId(Integer id) {         this.id = id;     }       public String getName() {         return name;     }       public void setName(String name) {         this.name = name;     }       @Override     public String toString() {         return "User [id=" + id + ", name=" + name + "]";     } }
----------------------------------------------------------------------------------------------------------------------------

UserDao:

import java.sql.ResultSet; import java.sql.SQLException; import java.util.List;   import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport;   public class UserDao extends JdbcDaoSupport {         public void add(User user) {         String sql = "insert into user values (null, ?)";
        // 因为JdbcDaoSupport中提供了JdbcTemplate,所以可以直接get获取         getJdbcTemplate().update(sql, user.getName());     }       public void update(User user) {         String sql = "update user set name = ? where id = ?";         getJdbcTemplate().update(sql, user.getName(), user.getId());     }       public void delete(User user) {         String sql = "delete from user where id = ?";         getJdbcTemplate().update(sql, user.getId());     }       public long findCount(){         String sql = "select count(*) from user";         return getJdbcTemplate().queryForLong(sql);     }       public String findNameById(int id){         String sql = "select name from user where id = ?";         return getJdbcTemplate().queryForObject(sql, String.class, id);     }       public User findById(int id){         String sql = "select * from user where id = ?";         return (User) getJdbcTemplate().queryForObject(sql,new UserRowMapper(), id);     }       class UserRowMapper implements RowMapper<User>{         /**          * rs:结果集          * rowNum:行号
         * 需要自己提供RowMapper实现,用来将记录封装成对象           */         public User mapRow(ResultSet rs, int rowNum) throws SQLException {             User user = new User();             user.setId(rs.getInt("id"));             user.setName(rs.getString("name"));             return user;         }     }       public List<User> findAll(){         String sql = "select * from user";         return getJdbcTemplate().query(sql, new UserRowMapper());     } }
----------------------------------------------------------------------------------------------------------------------------

XML配置文件:

<?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:context="http://www.springframework.org/schema/context"     xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">       <context:property-placeholder location="classpath:jdbc.properties" />         <!-- 配置C3P0连接池 -->     <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">         <property name="driverClass" value="${jdbc.driver}" />         <property name="jdbcUrl" value="${jdbc.url}" />         <property name="user" value="${jdbc.username}" />         <property name="password" value="${jdbc.password}" />     </bean>       <!-- 定义JDBCTemplate类 -->     <!-- 为JDBC模板注入连接池属性 -->     <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">         <property name="dataSource" ref="dataSource" />     </bean>       <bean id="userDao" class="com.wyc.spring3.demo2.UserDao">         <property name="jdbcTemplate" ref="jdbcTemplate" />     </bean>   </beans>   ----------------------------------------------------------------------------------------------------------------------------

测试代码:

import java.util.List;   import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;   @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration("classpath:applicationContext.xml") public class SpringDemo2 {       @Autowired     @Qualifier("userDao")     private UserDao userDao;       @Test     // 添加     public void fun1() {         User user = new User();         user.setName("王五");         userDao.add(user);     }       @Test     // 修改     public void fun2() {         User user = new User();         user.setId(1);         user.setName("李四");         userDao.update(user);     }       @Test     // 删除     public void fun3() {         User user = new User();         user.setId(1);         userDao.delete(user);     }       @Test     // 查询记录数量     public void fun4() {         System.out.println(userDao.findCount());     }       @Test     // 查询姓名     public void fun5() {         System.out.println(userDao.findNameById(2));     }       @Test     // 查询单个对象     public void fun6() {         System.out.println(userDao.findById(3));     }       @Test     // 查询全部对象     public void fun7() {         List<User> list = userDao.findAll();         for (User user : list) {             System.out.println(user);         }     } }
——总结

1、配置连接池:
     1)默认连接池
    2)DBCP连接池
    3)C3P0连接池
        *   提取properties文件
2、JdbcTemplate的CRUD操作 

    Dao继承JdbcDaoSupport之后,可以不在Dao中注入JdbcTemplate对象了,因为JdbcDaoSupport中已经定义了JdbcTemplate实例。

Spring之JDBC Template

标签:.property   apach   roo   template   add   2.3   print   1.0   管理   

人气教程排行