一、引入依赖

 <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.23</version>
        </dependency>

二、示例代码

 DataSource dataSource = new SimpleDriverDataSource(
                BeanUtils.instantiateClass(org.postgresql.Driver.class),
                "jdbc:postgresql://127.0.0.1:5432/postgres?charSet=UTF-8&&allowEncodingChanges=true&serverTimezone=Asia/Shanghai",
                "postgres",
                "123321");
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        Map<String,Object> rs = jdbcTemplate.queryForMap("select * from a001 where pkid='03ef5d004aa8441f976889a5b08c7e51'");
        return rs;










@SpringBootTest
class JdbcTests {
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    @Test
    void getBean() {
        //查询对象
        String sql = "select * from ns_admin where id=:id";
        Map<String,Object> param = new HashMap<>();
        param.put("id",1);
        RowMapper<Admin> rowMapper=new BeanPropertyRowMapper<>(Admin.class);
        Admin user= namedParameterJdbcTemplate.queryForObject(sql,param,rowMapper);
        System.out.println(user.getNickname());
        // 查询列表
        String sql2 = "select * from ns_admin where id>:id";
        Map<String,Object> param2 = new HashMap<>();
        param2.put("id",0);
        List<Admin> list = namedParameterJdbcTemplate.query(sql2,param2,rowMapper);
        for(Admin admin:list){
            System.out.println(admin.getNickname());
        }
        //count、avg、sum
        String sql3 ="select count(*) from ns_admin";
        int count= namedParameterJdbcTemplate.queryForObject(sql3,new MapSqlParameterSource(),Integer.class);
        int sum= namedParameterJdbcTemplate.queryForObject("select sum(id) from ns_admin",new MapSqlParameterSource(),Integer.class);
        int avg= namedParameterJdbcTemplate.queryForObject("select avg(id) from ns_admin",new MapSqlParameterSource(),Integer.class);
        System.out.println(count);
        System.out.println(sum);
        System.out.println(avg);
        //like
        String sql4 ="select * from ns_admin where nickname like :nickname";
        Map<String,Object> param4 = new HashMap<>();
        //拼在变量上面
        param4.put("nickname","李%");
//        param4.put("nickname","%李%");
//        param4.put("nickname","%李");
        List<Admin> list4 = namedParameterJdbcTemplate.query(sql4,param4,rowMapper);
        for(Admin admin:list4){
            System.out.println(admin.getNickname());
        }
        //like
        String sql5 ="select * from ns_admin where id in (:ids)";
        Map<String,Object> param5 = new HashMap<>();
        //拼在变量上面
        param5.put("ids", Arrays.asList("1","2"));
        List<Admin> list5 = namedParameterJdbcTemplate.query(sql5,param5,rowMapper);
        for(Admin admin:list5){
            System.out.println(admin.getNickname());
        }
        //执行sql
        String rs = namedParameterJdbcTemplate.execute("CREATE TABLE sui_persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) )", preparedStatement -> {
            preparedStatement.execute();
            return "success";
        });
    }
    private static class Admin {
        private Long id;
        private String username;
        private String nickname;
        private String password;
        private Integer logintime;

        public Long getId() {
            return id;
        }

        public void setId(Long id) {
            this.id = id;
        }

        public String getUsername() {
            return username;
        }

        public void setUsername(String username) {
            this.username = username;
        }

        public String getNickname() {
            return nickname;
        }

        public void setNickname(String nickname) {
            this.nickname = nickname;
        }

        public String getPassword() {
            return password;
        }

        public void setPassword(String password) {
            this.password = password;
        }

        public Integer getLogintime() {
            return logintime;
        }

        public void setLogintime(Integer logintime) {
            this.logintime = logintime;
        }
    }
}






@Component
public class SimpleJdbcUtils {
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    /**
     * map形式插入数据；如果map中的key和数据库不对应，会抛出异常
     * @param tableName 表名
     * @param map       参数，key(数据库字段)-value（字段对应数据）
     * @return          影响数据行数
     */
    public int insertFun(String tableName,Map<String,Object> map){
        return this.insertFun(tableName,map,true);
    }
    /**
     * map形式插入数据；如果map中的key和数据库不对应，会抛出异常
     * @param tableName  表名
     * @param map        参数，key(数据库字段)-value（字段对应数据）
     * @param ignoreNull 是否忽略null（如果是值是null，直接忽略key-value）
     * @return           影响数据行数
     */
    public int insertFun(String tableName,Map<String,Object> map,boolean ignoreNull){
        if(map==null||map.isEmpty()){
            throw new BusinessException("数据为空");
        }
        //如果设置了忽略null，过滤掉null
        if(ignoreNull){
            Set<Map.Entry<String,Object>> realData = map.entrySet();
            // 过滤null
            map = realData.stream()
                    .filter(ObjEntry -> ObjEntry.getValue()!=null)
                    .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));
            //判断过滤后的值是不是空
            if(map.isEmpty()){
                throw new BusinessException("数据为空");
            }
        }
        //拼接insert语句
        Set<String> keys = map.keySet();
        int sizeIndex = keys.size()-1;
        StringBuilder insertSql = new StringBuilder("INSERT INTO " + tableName + "(");
        int index = 0;
        for (String k:keys){
            if(index<sizeIndex){
                insertSql.append(k).append(",");
            }else{
                insertSql.append(k);
                insertSql.append(") VALUES (");
            }
            index=index+1;
        }
        index = 0;
        for (String k:keys){
            if(index<sizeIndex){
                insertSql.append(":").append(k).append(",");
            }else{
                insertSql.append(":").append(k).append(")");
            }
            index=index+1;
        }
        return namedParameterJdbcTemplate.update(insertSql.toString(), map);
    }

    /**
     * map形式更新数据；如果map和where中的key和数据库不对应，会抛出异常
     * where条件只支持key=value形式
     * @param tableName   表名
     * @param map         数据，key(数据库字段)-value（字段对应数据）
     * @param where       where条件数据，key(数据库字段)-value（字段对应数据）
     * @return            影响数据行数
     */
    public int updateFun(String tableName,Map<String,Object> map,Map<String,Object> where){
        if(map==null||map.isEmpty()){
            throw new BusinessException("数据为空");
        }
        //拼接update set语句
        Set<String> keys = map.keySet();
        int sizeIndex = keys.size()-1;
        StringBuilder updateSql = new StringBuilder("UPDATE " + tableName + " SET ");
        int index = 0;
        for (String k:keys){
            if(index<sizeIndex){
                updateSql.append(k).append("=:").append(k).append(",");
            }else{
                updateSql.append(k).append("=:").append(k);
            }
            index=index+1;
        }
        //最终参数
        Map<String,Object> params = new HashMap<>(map);
        //拼接where
        if(where!=null&&!where.isEmpty()){
            index = 0;
            Set<Map.Entry<String,Object>> whereSet = where.entrySet();
            int whereSizeIndex = whereSet.size()-1;
            updateSql.append(" where ");
            for (Map.Entry<String,Object> w:whereSet){
                String k = w.getKey();
                //把where条件也放到最终参数中
                params.put(k,w.getValue());
                //拼接where参数
                if(index<whereSizeIndex){
                    updateSql.append(k).append("=:").append(k).append(",");
                }else{
                    updateSql.append(k).append("=:").append(k);
                }
                index=index+1;
            }
        }
        return namedParameterJdbcTemplate.update(updateSql.toString(), params);
    }
}

三、常见驱动名称

//oracle
driverClass：oracle.jdbc.driver.OracleDriver
jdbcUrl：jdbc:oracle:thin:@192.168.0.1:1521:yourDBName
//SQL Server
driverClass：com.microsoft.sqlserver.jdbc.SQLServerDriver（2005版本及以后）
             com.microsoft.jdbc.sqlserver.SQLServerDriver（2000版本）

jdbcUrl：jdbc:sqlserver://192.168.0.1:1433;databasename=yourDBName
//pgsql
driverClass：org.postgresql.Driver
jdbcUrl：jdbc:postgresql://127.0.0.1:5432/yourDBName
//MySQL
driverClass：com.mysql.jdbc.Driver
jdbcUrl：jdbc:mysql://192.168.0.1:3306/yourDBName

四、参考：

数据库连接driverClass和jdbcUrl大全 (opens new window)

初始化jdbcTemplate (opens new window)