spring jdbcTemplate的使用

最新推荐文章于 2022-10-09 19:42:04 发布

jasnet_u

最新推荐文章于 2022-10-09 19:42:04 发布

阅读量632

点赞数

分类专栏： java编程 javaWeb框架文章标签： jdbc spring jdbcTemplate

本文链接：https://blog.csdn.net/jasnet_u/article/details/81038557

版权

java编程同时被 2 个专栏收录

91 篇文章 0 订阅

订阅专栏

javaWeb框架

10 篇文章 0 订阅

订阅专栏

如题，spring框架中spring-orm包含了众多集成dao层的框架，如jdbcTemplate 封装对jdbc的操作， hibernateTemplate封装了对hibernate的操作等。本篇，我们介绍下spring中使用JdbcTemplate封装的api操作底层的jdbc数据库操作。主要使用的是DriverManagerDataSource 类，暂未涉及到数据库连接池。

以下是示例代码：

JdbcTemplate.java

package com.tingcream.springJdbc.jdbcTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
 
 /**
  * jdbcTemplate工厂
  *
  * @author jelly
  *
  */
public class JdbcTemplateFactory {
    private DriverManagerDataSource dataSource;
    private  JdbcTemplate jdbcTemplate;
    {
          dataSource = new DriverManagerDataSource();
          dataSource.setDriverClassName("com.mysql.jdbc.Driver");
          dataSource.setUrl("jdbc:mysql://localhost:3306/myDB?useUnicode=true&characterEncoding=UTF8&useSSL=false");
          dataSource.setUsername("root");
          dataSource.setPassword("123456");
           
          jdbcTemplate=new JdbcTemplate(dataSource);
    }
    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }
    public DriverManagerDataSource getDataSource() {
        return dataSource;
    }
     
}

spring.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:context="http://www.springframework.org/schema/context"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:cache="http://www.springframework.org/schema/cache"
    xsi:schemaLocation="
          http://www.springframework.org/schema/beans
          http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
          http://www.springframework.org/schema/context
          http://www.springframework.org/schema/context/spring-context-4.3.xsd
          http://www.springframework.org/schema/aop
          http://www.springframework.org/schema/aop/spring-aop-4.3.xsd
          http://www.springframework.org/schema/tx
          http://www.springframework.org/schema/tx/spring-tx-4.3.xsd
          http://www.springframework.org/schema/cache
          http://www.springframework.org/schema/cache/spring-cache-4.3.xsd">
     

   <bean id="jdbcTemplateFactory" class="com.tingcream.springJdbc.jdbcTemplate.JdbcTemplateFactory">
   </bean>
      
    <bean id="jdbcTemplate" factory-bean="jdbcTemplateFactory"     factory-method="getJdbcTemplate">
    </bean>
    
</beans>

Servlet中使用spring 容器中的bean，并使用jdbcTemplate查询用户列表
UserFindServlet.java

package com.tingcream.springJdbc.web;
 
import java.io.IOException;
import java.util.List;
 
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import org.springframework.jdbc.core.JdbcTemplate;
 
import com.alibaba.fastjson.JSON;
import com.tingcream.springJdbc.common.SpringContextHelper;
import com.tingcream.springJdbc.model.User;
import com.tingcream.springJdbc.model.UserRowMapper;
 
 
@WebServlet("/userFindServlet")
public class UserFindServlet extends HttpServlet{
  
    private static final long serialVersionUID = 1L;
 
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        this.doPost(req, resp);
    }
 
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
         request.setCharacterEncoding("utf-8");
         response.setContentType("text/html;charset=utf-8");
          
         JdbcTemplate jdbcTemplate= SpringContextHelper.getBean("jdbcTemplate", JdbcTemplate.class);
          
             String sql ="select * from t_user ";
             List<User> list = jdbcTemplate.query(sql, new UserRowMapper());
            //System.out.println(list);
            //输出json
            response.getWriter().write(JSON.toJSONString(list));
          
    }
 
}

User.java

package com.tingcream.springJdbc.model;
 
import java.io.Serializable;
 
public class User implements Serializable{
  
    private static final long serialVersionUID = 1L;
    private Integer id;//id主键自增
    private String  name;//姓名
    private Integer sex;//性别 1男 2女
    private Integer age;//年龄
    private String city;//城市
    private String describe;//描述
    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;
    }
    public Integer getSex() {
        return sex;
    }
    public void setSex(Integer sex) {
        this.sex = sex;
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
    public String getCity() {
        return city;
    }
    public void setCity(String city) {
        this.city = city;
    }
    public String getDescribe() {
        return describe;
    }
    public void setDescribe(String describe) {
        this.describe = describe;
    }
 
}

UserRowMapper.java

package com.tingcream.springJdbc.model;
 
import java.sql.ResultSet;
import java.sql.SQLException;
 
import org.springframework.jdbc.core.RowMapper;
 
public class UserRowMapper implements RowMapper<user> {
 
    @Override
    public User mapRow(ResultSet rs, int rowNum) throws SQLException {
          User user =new User();
          user.setId(rs.getInt("id"));
          user.setName(rs.getString("name"));
          user.setSex(rs.getInt("sex"));
          user.setAge(rs.getInt("age"));
          user.setCity(rs.getString("city"));
          user.setDescribe(rs.getString("describe"));
          return user;
    }
 
}

SpringContextHelper.java

package com.tingcream.springJdbc.common;
 
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;
 
/**
 * Spring 上下文辅助类
 * @author jelly
 *
 */
@Component
public class SpringContextHelper implements ApplicationContextAware {
       private static ApplicationContext applicationContext; 
        
       @Override 
       public void setApplicationContext(ApplicationContext applicationContext) 
               throws BeansException { 
           SpringContextHelper.applicationContext = applicationContext; 
       } 
          
       public static ApplicationContext getApplicationContext(){ 
           return applicationContext; 
       } 
          
       public static Object getBean(String name){ 
           return applicationContext.getBean(name); 
       } 
        /**
         * 从spring 上下文中获取bean
         * @param name
         * @param requiredClass
         * @return
         */
       public static <T> T getBean(String name, Class<T>  requiredClass){ 
           return applicationContext.getBean(name, requiredClass); 
       } 
       public  static <T> T getBean(Class<T> requiredType){
           return applicationContext.getBean(requiredType);
       }
   
      
}

其他测试代码 JdbcTemplateTest.java

package com.tingcream.springJdbc.test;
 
import java.util.List;
 
import javax.annotation.Resource;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import com.tingcream.springJdbc.model.User;
import com.tingcream.springJdbc.model.UserRowMapper;
  
 
/**
 * spring4测试
 *
 * jdbcTemplate CRUD 使用
 * @author jelly
 *
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration({"/spring.xml"})
public class JdbcTemplateTest {
    //@Resource
    //private  JdbcTemplateFactory jdbcTemplateFactory;
     
    @Resource
    private JdbcTemplate  jdbcTemplate ;
    /**
     * 根据id查询一个用户
     */
    @Test
    public void test1(){
        String sql ="select * from t_user where id=?";
        User user=   jdbcTemplate.queryForObject(sql, new UserRowMapper(), 2);
       System.out.println(user);
    }
     
    /**
     * 查询列表
     */
    @Test
    public void test2(){
        //根据条件查询列表
            String sql ="select * from t_user where age>?";
              List<user> list = jdbcTemplate.query(sql,  new UserRowMapper(),new Object[]{20});
               System.out.println(list);
         
 //查询所有列表
//          String sql ="select * from t_user ";
//          List<user> list = jdbcTemplate.query(sql, new UserRowMapper());
//          System.out.println(list);
          
    }
     
    /**
     * 添加用户
     */
    @Test
    public void  test3(){
          String sql ="INSERT INTO t_user(`name`,sex,age,city,`describe`) VALUES(?,?,?,?,?)";
         int count =jdbcTemplate.update(sql, new Object[]{"陈六",1,24,"成都","aaaa"});
        System.out.println("受影响的行数:"+count);
         
    }
     
    /**
     * 修改用户
     */
    @Test
    public void test4(){
          String sql ="UPDATE t_user a SET a.`age`=? ,a.`city`=? WHERE a.`id`=?";
          int count = jdbcTemplate.update(sql, new Object[]{33,"广州",4});
        System.out.println("受影响的行数:"+count);
    }
     
    /**
     * 删除用户
     */
    @Test
    public void test5(){
          String sql ="DELETE  FROM t_user  WHERE id=?";
          int count = jdbcTemplate.update(sql, new Object[]{1});
        System.out.println("受影响的行数:"+count);
         
    }
}

pom.xml

<!-- spring 事务管理jar包 -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>4.3.2.RELEASE</version>
</dependency>
  
<!-- spring 容器上下文 jar包 -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>4.3.2.RELEASE</version>
</dependency>
  
      <!-- spring-jdbc 事务支持jar包 -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>4.3.2.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-orm</artifactId>
    <version>4.3.2.RELEASE</version>
</dependency>
  
  <!-- spring 测试支持jar包 -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-test</artifactId>
    <version>4.3.2.RELEASE</version>
</dependency>
   
 <!-- mysql jdbc 驱动 -->
 <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.40</version>
</dependency>
 
<!-- 阿里巴巴 fastJson 解析器 jar包 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.31</version>
</dependency>

ok!!