JdbcTemplate 详解一

  • JdbcTemplate 入门示例

pom

复制代码
<dependencies>
        <!--Spring核心包-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>
        <!--Spring的操作数据库坐标-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>
        <!--Spring测试坐标-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>5.2.6.RELEASE</version>
        </dependency>
        <!--Mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.32</version>
        </dependency>
        <!--测试包-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>
复制代码

JdbcTemplate使用很简单，注入一个数据源就可以使用了

复制代码
public class A001SpringJdbcJdbcTemplateTest {
  private JdbcTemplate jdbcTemplate;

  @Before
  public void init() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://localhost:3306/blogsrc?useUnicode=true&characterEncoding=UTF-8");
    dataSource.setUsername("root");
    dataSource.setPassword("zhao");
    jdbcTemplate = new JdbcTemplate(dataSource);
  }

  @Test
  public void queryTest() {
    String sql = "select * from user";
    List<Map<String, Object>> users = jdbcTemplate.queryForList(sql);
    System.out.println(users);
  }

}
复制代码

 

对于参数赋值，可以采用占位符的方式

 @Test
  public void queryByParameterTest() {
    String sql = "select * from user where id =?";
    List<Map<String, Object>> users = jdbcTemplate.queryForList(sql, 1L);
    List<Map<String, Object>> users1 = jdbcTemplate.queryForList(sql, new Object[] {1L});
  }
  • mapper映射

Spring JDBC 通过mapper接口把resultSet对象中的数据映射为java对象，例如上述例子中返回 List<Map<String, Object>>，其实使用的是ColumnMapRowMapper的mapper实现。我们自己可以通过实现RowMapper接口的方式自定义从resultSet到java对象的映射关系。

先创建一个table

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `password` varchar(255) NOT NULL,
  `user_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

User实体类

public class User implements Serializable {
  private Long id;
  private String userName;
  private String password;
  // 省略 getter setter
}

自定义mapper映射

复制代码
 @Test
  public void simpleMapperTest() {
    String sql = "select * from user";
    List<User> users = jdbcTemplate.query(sql, new RowMapper<User>() {
      public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        User user = new User();
        user.setId(rs.getObject("id") == null ? null : rs.getLong("id"));
        user.setUserName(rs.getString("user_name"));
        user.setPassword(rs.getString("password"));
        return user;
      }
    });
  }
复制代码

 

  • NamedParameterJdbcTemplate

NamedParameterJdbcTemplate的使用基本上和JdbcTemplate类似，只不过参数的赋值方式由占位符变成了命名参数，命名参数优势在于，如果一个相同的参数出现了多次，只需要进行一次赋值即可。
创建NamedParameterJdbcTemplate对象的两种方式

// 方式1
    namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
    // 方式2
    namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
@Test
  public void namedParameterJdbcTemplateTest() {
    String sql = "select * from user where id =:id";
    Map<String, Object> parameters = new HashMap<String, Object>();
    parameters.put("id", 1L);
    List<Map<String, Object>> users = namedParameterJdbcTemplate.queryForList(sql, parameters);
    System.out.println(users);
  }
  • batchUpdate

对于大数据量的数据更新，可以采用batchUpdate接口

复制代码
@Test
  public void batchUpdateTest() {
    String sql = "insert into user (user_name,password) VALUES (?, ?)";
    List<User> users = Lists.newArrayList();
    for (int i = 0; i <= 10; i++) {
      User user = new User();
      user.setUserName("xiaoming");
      user.setPassword("123456");
      users.add(user);
    }
    jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
      @Override
      public void setValues(PreparedStatement ps, int i) throws SQLException {
        User user = users.get(i);
        int count = 0;
        ps.setString(++count, user.getUserName());// 索引从1开始
        ps.setString(++count, user.getPassword());
      }

      @Override
      public int getBatchSize() {
        return users.size();
      }
    });
  }
复制代码

  • 与spring整合

只需要注入数据源即可

复制代码
   <!-- 省略dataSource相关配置 -->
    
    <!-- 配置 Spirng 的 JdbcTemplate -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    <!-- 配置 NamedParameterJdbcTemplate -->
    <bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
        <constructor-arg ref="dataSource"></constructor-arg>
    </bean>
复制代码
  • 创建配置类

SpringConfig.java

复制代码
package com.day02Jdbc.config;
 
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
 
/**
 * @ Author     ：ShaoWei Sun.
 * @ Date       ：Created in 9:25 2018/11/10
 */
@Configuration//指定该类是一个配置类、等价于一个spring的配置文件
@ComponentScan(basePackages="com.day02Jdbc")//指定扫包范围
@Import(JdbcConfig.class) //引入JdbcConfig.class文件
public class SpringConfig {
 
}
复制代码

 引入jdbc属性文件

创建Jdbc属性文件 resources/jdbc.properties

jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis
jdbc.username=root
jdbc.password=sswqzx

在jdbcConfig中引入jdbc属性资源文件

复制代码
package com.day02Jdbc.config;
 
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.PropertySource;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
 
//import javax.sql.DataSource;
 
 
/**
 * @ Author     ：ShaoWei Sun.
 * @ Date       ：Created in 9:28 2018/11/10
 */
@PropertySource("classpath:jdbc.properties")
public class JdbcConfig {
 
    @Value("${jdbc.driverClass}")
    private String driver;
 
    @Value("${jdbc.url}")
    private String url;
 
    @Value("${jdbc.username}")
    private String user;
 
    @Value("${jdbc.password}")
    private String password;
 
 
 
    /**
     * Bean注解：该注解只能写在方法上，表明使用此方法创建一个对象，并且放入spring容器。
     * name属性：给当前@Bean注解方法创建的对象指定一个名称(即bean的id）。
     * @return
     */
    @Bean(name="dataSource")
    public DataSource createDataSource(){
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driver);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }
 
 
    @Bean(name="jdbcTemplate")
    public JdbcTemplate createJdbcTemplate(DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }
}
复制代码

注意：@Bean注解也可以不用指定name,因为基本上都是按照类型注入;如果不指定name,默认是@Bean注解所在的方法的名字。

在SpringConfig中引入JdbcConfig

复制代码
package com.day02Jdbc.config;
 
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
 
/**
 * @ Author     ：ShaoWei Sun.
 * @ Date       ：Created in 9:25 2018/11/10
 */
@Configuration//指定该类是一个配置类、等价于一个spring的配置文件
@ComponentScan(basePackages="com.day02Jdbc")//指定扫包范围
@Import(JdbcConfig.class) //引入JdbcConfig.class文件
public class SpringConfig {
 
}
复制代码

  • springboot整合

@Autowired
private JdbcTemplate jdbcTemplate;

 

项目创建完后，接下来只需要在application.properties中提供数据的基本配置即可，如下：

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.username=root
spring.datasource.password=123
spring.datasource.url=jdbc:mysql:///test01?useUnicode=true&characterEncoding=UTF-8
  • 封装工具类
复制代码
import com.google.common.base.Preconditions;
import com.google.common.base.Throwables;
import com.google.common.collect.Lists;
import com.zxsoft.spider.exception.ServiceException;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.RowProcessor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import java.util.*;


@Repository
public class JDBCDao {

    private static final Logger log = LoggerFactory.getLogger(JDBCDao.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private RowProcessor processor = new BasicRowProcessor();


   
    public <T> List<T> select(String table, String column, Map<String, Object> valueMap, Class<T> clazz) {
        StringBuilder sql = new StringBuilder("select ");
        sql.append(column).append(" from ").append(table).append(" where 1=1 ");
        if (null != valueMap) {
            Iterator it = valueMap.entrySet().iterator();
            while (it.hasNext()) {
                Map.Entry pair = (Map.Entry) it.next();
                sql.append(" and ").append(pair.getKey()).append(" = '").append(pair.getValue()).append("'");
            }
        }
        log.debug("select sql ==" + sql.toString());
        List<T> list = null;
        try {
            list = jdbcTemplate.query(sql.toString(), (rs, rowNum) -> processor.toBean(rs, clazz));
        } catch (ServiceException e) {
            log.error(Throwables.getStackTraceAsString(e));
        }
        return list == null ? Lists.newArrayList() : list;
    }

    /**
     *
     * @param sql
     * @param clazz
     * @param <T>
     * @return
     */
    public <T> List<T> select(String sql, Class<T> clazz) {
        List<T> list = null;
        try {
            list = jdbcTemplate.query(sql, (rs, rowNum) -> processor.toBean(rs, clazz));
        } catch (ServiceException e) {
            log.error(Throwables.getStackTraceAsString(e));
        }
        return list == null ? Lists.newArrayList() : list;
    }

    public List<Map<String, Object>> select(String sql) {
        List<Map<String, Object>> list = null;
        try {
            list = jdbcTemplate.queryForList(sql);
        } catch (ServiceException e) {
            log.error(Throwables.getStackTraceAsString(e));
        }
        return list == null ? Lists.newArrayList() : list;
    }

    public List<Map<String, Object>> select(String sql, Object[] args) {
        List<Map<String, Object>> list = null;
        try {
            list = jdbcTemplate.queryForList(sql, args);
        } catch (ServiceException e) {
            log.error(Throwables.getStackTraceAsString(e));
        }
        return list == null ? Lists.newArrayList() : list;
    }

    public int queryCount(String table, Map<String, Object> valueMap) {
        StringBuilder sql = new StringBuilder("select count(1) as count from ");
        sql.append(table);
        sql.append(" where 1=1");
        Object[] bindArgs = new Object[valueMap.size()];
        int i = 0;
        Iterator it = valueMap.entrySet().iterator();
        while (it.hasNext()) {
            Map.Entry pair = (Map.Entry) it.next();
            sql.append(" and ").append(pair.getKey()).append(" = ?");
            bindArgs[i] = pair.getValue();
            i++;
        }
        log.debug("sql ==== " + sql.toString() + " " + Arrays.toString(bindArgs));
        return jdbcTemplate.queryForObject(sql.toString(), bindArgs, Integer.class);
    }

    /**
     * 根据表名，字段插入
     *
     * @param tableName
     * @param valueMap
     * @return
     */
    public int insert(String tableName, Map<String, Object> valueMap) {
        Preconditions.checkNotNull(tableName, "table name can not be empty");
        Set<String> keySet = valueMap.keySet();
        Iterator<String> iterator = keySet.iterator();
        StringBuilder columnSql = new StringBuilder();
        StringBuilder unknownMarkSql = new StringBuilder();
        Object[] bindArgs = new Object[valueMap.size()];
        int i = 0;
        while (iterator.hasNext()) {
            String key = iterator.next();
            columnSql.append(i == 0 ? "" : ",");
            columnSql.append(key);
            unknownMarkSql.append(i == 0 ? "" : ",");
            unknownMarkSql.append("?");
            bindArgs[i] = valueMap.get(key);
            i++;
        }
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ");
        sql.append(tableName);
        sql.append(" (");
        sql.append(columnSql);
        sql.append(" )  VALUES (");
        sql.append(unknownMarkSql);
        sql.append(" )");
        log.debug("insert sql ==== " + sql.toString() + " " + Arrays.toString(bindArgs));
        return jdbcTemplate.update(sql.toString(), bindArgs);
    }

    /**
     * 批量插入
     *
     * @param list
     * @param tableName
     * @return
     * @throws Exception
     */
    public int[] batchInsert(String tableName, List<Map<String, Object>> list) throws Exception {
        if (CollectionUtils.isEmpty(list)) {
            return new int[1];
        }
        Map<String, Object>[] maps = new Map[list.size()];
        for(int i = 0;i< list.size(); i++) {
            maps[i] = list.get(i);
        }
        SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
        return simpleJdbcInsert.withTableName(tableName).executeBatch(maps);
    }

    /**
     * 根据表名，字段更新
     *
     * @param tableName
     * @param valueMap
     * @return
     */
    public int update(String tableName, Map<String, Object> valueMap) {
        if (valueMap.size() < 1) {
            return -1;
        }
        Set<String> keySet = valueMap.keySet();
        Iterator<String> iterator = keySet.iterator();
        int i = 0;
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE ");
        sql.append(tableName);
        sql.append(" SET ");
        Object[] bindArgs = new Object[valueMap.size() - 1];
        StringBuilder columnSql = new StringBuilder();
        while (iterator.hasNext()) {
            String key = iterator.next();
            if ("id".equals(key.toLowerCase())) {
                continue;
            }
            columnSql.append(i == 0 ? "" : ",");
            columnSql.append(key + " = ?");
            bindArgs[i] = valueMap.get(key);
            i++;
        }
        sql.append(columnSql).append(" WHERE ID = " + valueMap.get("id"));
        log.debug("update sql ==== " + sql.toString() + " " + Arrays.toString(bindArgs));
        return jdbcTemplate.update(sql.toString(), bindArgs);
    }

   
    public int update(String sql, Object[] bindArgs) throws ServiceException {
        return jdbcTemplate.update(sql, bindArgs);
    }
}
复制代码
  •  表之间的数据导入

 

复制代码
　　public static void main(String[] args) {

        List<Map<String, Object>> zxCrawlerDbList = zxCrawlerDbJdbcTemplate.queryForList("SELECT * FROM `application_electronic_contract`;");
        List<Map<String, Object>> anHuiDbList = anHuiJdbcTemplate.queryForList("SELECT * FROM `application_electronic_contract`;");
        Map<String, String> map = new HashMap<>();
        for (Map<String, Object> stringObjectMap : anHuiDbList) {
            String url = stringObjectMap.get("url").toString();
            String id = stringObjectMap.get("id").toString();
            map.put(url, id);
        }

        for (Map<String, Object> objectMap : zxCrawlerDbList) {
            String url = objectMap.get("url").toString();
            if (map.containsKey(url)) {
                continue;
            }
            Iterator<String> iterator = objectMap.keySet().iterator();
            while (iterator.hasNext()) {// 循环取键值进行判断
                String key = iterator.next();// 键
                if (key.startsWith("id")) {
                    iterator.remove();// 移除map中以week字符开头的键对应的键值对
                }
                if (key.startsWith("digest")) {
                    iterator.remove();// 移除map中以lessonNo字符开头的键对应的键值对
                }
            }
            objectMap.put("type", "3");
            objectMap.put("platformCode", "3");
            insert("application_electronic_contract", objectMap);
        }
    }
复制代码

 

posted @   Bonnie_ξ  阅读(193)  评论(0)  编辑  收藏  举报
编辑推荐：
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
阅读排行：
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· ollama系列01：轻松3步本地部署deepseek，普通电脑可用
· 按钮权限的设计及实现
· 25岁的心里话
点击右上角即可分享
微信分享提示
AI IDE Trae