数据库各种写法

jdbctemplate

public Map<String,Object> getAndroidVersion(){
        String sql = "select * from zhao_android_version";
        return jdbcTemplate.queryForMap(sql, new Object[]{});
    }

public List<VipPackageEntity> getMessageActivity(){
        String sql = "select * from zhao_vip_package";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(VipPackageEntity.class));
    }

public List<VipPackageEntity> getMessageActivityIOS(){
        String sql = "select id,if(lineation_price = 3,5,lineation_price) as lineation_price,if(activity_price = 1,3,activity_price) as activity_price,vip_day,auto_price,intro_message,type,voucher,last_days from zhao_vip_package";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(VipPackageEntity.class));
    }

public int getNumber(int userId){
        String sql = "select count(*) from tb_vip_user_info where user_id = ?";
        return jdbcTemplate.queryForObject(sql,new Object[]{userId},int.class);
    }

public List<Integer> getUserEndtime(int userId){
        String sql = "select count(*) from tb_vip_user_info where user_id = ?";
        return  jdbcTemplate.queryForList(sql,new Object[]{userId},int.class);
    }

public Date getUserEndtime(int userId){
        String sql = "select createTime from tb_activity_info where userId = ?";
        return jdbcTemplate.queryForObject(sql,new Object[]{userId},Date.class);
    }

public int updateVoucher(Double giftBlance){
        String sql = "insert into tb_user_account(giftBlance) value (?)";
        return jdbcTemplate.update(sql,new Object[]{giftBlance},int.class);
    }

namedjdbctemplate

public Map<String,Object> queryGiveVoucher(Double price){
        String sql = "SELECT IFNULL(SUM(voucher),0) AS voucher,IFNULL(sum(last_days),0) as days FROM zhao_vip_package WHERE activity_price = :price ";
        return dbLinkTemplate.queryForMap(sql, new MapSqlParameterSource().addValue("price",price));
    }

复杂SQL

UPDATE tb_novel_info a,tb_autor_novel b SET a.chapter_num = b.chapterNums,a.total_word = b.totalWords WHERE a.id = b.id

insert into tb_novel_content_sk (NOVEL_ID,CHAPTER_ID,CHAPTER_NAME,content,CREATE_DATE,UPDATE_STATUS,SORT_NO)  select novelId,concat(id,''),REPLACE(REPLACE(capterName,' ',''),'　',''),REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(content,'　',''),'\\t\\t',''),'\\n\\n','<br>　　'),'\\r\\n','<br>　　'),'\\n','<br>　　'),' ',''),createTime,status,sortNum from tb_autor_content where novelId = ?

insert into tb_novel_chapter_sk (novel_id,chapter_id,chapter_name,update_time_str,status,sort_no,ccid,chapterSort,freeStatus,vipflag)  select novelId,concat(id,''),REPLACE(REPLACE(capterName,' ',''),'　',''),DATE_FORMAT(createTime,'%Y-%m-%d %T'),status,sortNum,0,0,0,0 from tb_autor_content where novelId = ?