JdbcTemplate使用

JdbcTemplate

简介

JdbcTemplate 是spring对JDBC的封装

自定义SqlString类对JdbcTemplate再次进行封装，以便使用：

用到的方法

• execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句；
• update方法及batchUpdate方法：update方法用于执行新增、修改、删除等语句；batchUpdate方法用于执行批处理相关语句；
• query方法及queryForXXX方法：用于执行查询相关语句；

/**
 * @Description:
 *          简单的自定义sql拼接类，用来明确描述拼接sql的行为
 */
@Component
@Scope(value = "prototype")
public class SQLString implements Serializable ,Cloneable{

	static final long serialVersionUID = 1213885877147921107L;

	private StringBuffer sqlSentence;

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Autowired
	private OracleDialect oracleDialect;

	@Autowired
	private MysqlDialect mysqlDialect;

	/** 默认数据库类型*/
	@Value("${sqlDialect.databaseType}")
	private String databaseType;

	private SqlDialect sqlDialect;

	public SQLString() {
		this.sqlSentence = new StringBuffer();
	}

	public SQLString append(String str){
		this.sqlSentence.append(str);
		return this;
	}

	public SQLString before(String str){
		this.sqlSentence.insert(0,str);
		return this;
	}

	public SQLString arround(String prefix,String suffix){
		this.sqlSentence.insert(0,prefix);
		this.sqlSentence.insert(this.sqlSentence.length(),suffix);
		return this;
	}

	public SQLString count() {
		setSqlModel();
		sqlDialect.count(this.clone());
		return this;
	}

	public SQLString limit(Integer offset, Integer startIndex) {
		return null;
	}

	public SQLString paging(Integer pageNo, Integer pageSize) {
		System.out.println("--------------------"+databaseType);
		setSqlModel();
		sqlDialect.paging(this.clone(),pageNo,pageSize);
		return this;
	}

	public SQLString countIf(String where) {
		return null;
	}

	/**
	 * @param rowMapper
	 * @return List<T>
	 * @notice
	 *       映射实体查询方法
	 */
	public <T> List<T> query(BeanPropertyRowMapper<T> rowMapper){
		List<T> result = jdbcTemplate.query(getSqlSentence().toString(), rowMapper);
		return result;
	}

	/**
	 * @param
	 * @return List<Map<String,Object>>
	 * @notice
	 *       直接查询List方法
	 */
	public List<Map<String,Object>> queryForList(){
		return  jdbcTemplate.queryForList(getSqlSentence().toString());
	}


	public int queryForCount(){
		Map<String, Object> map = jdbcTemplate.queryForMap(getSqlSentence().toString());
		Integer count = Integer.valueOf(map.get("count")+"");
		return count;
	}

	public void setSqlModel(){
		if(databaseType.equals(DatabaseType.ORACLE)){
			this.sqlDialect = oracleDialect;
		}
		if(databaseType.equals(DatabaseType.MYSQL)){
			this.sqlDialect = mysqlDialect;
		}
	}

	@Override
	public String toString(){
		return this.sqlSentence.toString();
	}

	public int length(){
		return this.sqlSentence.length();
	}

	public boolean isEmpty(){
		return this.sqlSentence==null||this.sqlSentence.length()==0;
	}

	public void clean(){
		setSqlSentence(new StringBuffer());
	}

	public SQLString where(String... conditions){
		if(conditions == null||conditions.length==0)
			throw new IllegalParametersException("conditions is null or empty");
		String con = "";
		for (String condition :conditions) {
			con += "  "+condition+"  and";
		}
		String connew = con.substring(0, con.length() - 3);
		this.sqlSentence.append("where   "+connew);
		return this;
	}

	/**深拷贝，利用序列化和反序列化实现  使用 apache的jar 的工具类实现
	 *   对象中使用了 jdbcTemplate 此对象没有实现序列化接口 故不可以深拷贝*/
	public SQLString deepClone() {
		SQLString clone = null;
		try{
			clone = SerializationUtils.clone(this);
		}catch(Exception e) {
			e.printStackTrace();
		}
		return clone;
	}

	@Override
	public SQLString clone(){
		SQLString clone = null;
		try{
			clone = (SQLString)super.clone();
		}catch(Exception e) {
			e.printStackTrace();
		}
		return clone;
	}

	public void execute(){
		jdbcTemplate.execute(getSqlSentence().toString());
	}


	public void changeDatabaseType(String databaseType){
		setDatabaseType(databaseType);
	}

	public StringBuffer getSqlSentence() {
		return sqlSentence;
	}

	public void setSqlSentence(StringBuffer sqlSentence) {
		this.sqlSentence = sqlSentence;
	}

	public String getDatabaseType() {
		return databaseType;
	}

	public void setDatabaseType(String databaseType) {
		this.databaseType = databaseType;
	}

	public SqlDialect getSqlDialect() {
		return sqlDialect;
	}

	public void setSqlDialect(SqlDialect sqlDialect) {
		this.sqlDialect = sqlDialect;
	}

}

具体使用时在将此类聚合到具体类型的类中即可