SimpleJdbcTemplate 扩展

http://static.springsource.org/spring/docs/2.0.x/reference/jdbc.html

http://www.mkyong.com/spring/spring-named-parameters-examples-in-simplejdbctemplate/

 

 

 

package com.snailteam.team.model;

public interface Model {

}

 

 

 

package com.snailteam.team.model;

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;

@Table(name = "tuser")
public class User implements Model {
	@Id
	public int id;
	@Column(name = "username")
	public String username;
	@Column(name = "password")
	public String password;
	@Column(name = "reg_date")
	public Date reg_date;
	@Column(name = "version")
	public Integer version;
	public String lastIp;
}

 

 

package com.snailteam.team.model;

import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.sql.DataSource;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

public class SimpleJdbcTemplateExt extends SimpleJdbcTemplate {

	public SimpleJdbcTemplateExt(DataSource dataSource) {
		super(dataSource);
	}

	public int insert(Model model) throws IllegalArgumentException,
			IllegalAccessException, SQLException {
		String tableName = "";
		Map<String, Object> parameters = this.model2Parameters(model);
		if (model.getClass().isAnnotationPresent(Table.class)) {
			Table table = model.getClass().getAnnotation(Table.class);
			tableName = table.name();
		}
		StringBuffer sb = new StringBuffer();
		sb.append("(");
		for (String key : parameters.keySet()) {
			sb.append("," + key);
		}
		sb.append(")");

		MapSqlParameterSource paramSource = new MapSqlParameterSource();
		paramSource.addValues(parameters);
		KeyHolder generatedKeyHolder = new GeneratedKeyHolder();
		this.getNamedParameterJdbcOperations().update(
				"insert into " + tableName + sb.toString().replace("(,", "(")
						+ "values"
						+ sb.toString().replace(",", ",:").replace("(,", "("),
				paramSource, generatedKeyHolder);
		return generatedKeyHolder.getKey().intValue();
	}

	public Map<String, Object> model2Parameters(Model model)
			throws IllegalArgumentException, IllegalAccessException {
		Map<String, Object> parameters = new HashMap<String, Object>();
		for (Field f : model.getClass().getFields()) {
			f.setAccessible(true);
			if (f.isAnnotationPresent(Id.class)) {
				Object obj = f.get(model);
				if (obj != null)
					parameters.put("id", obj);
			} else if (f.isAnnotationPresent(Column.class)) {
				Column column = f.getAnnotation(Column.class);
				Object obj = f.get(model);
				if (obj != null)
					parameters.put(column.name(), obj);
			}

		}
		return parameters;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public <T> T select(final Class<T> classz, String sql,
			Map<String, Object> parameters) throws InstantiationException,
			IllegalAccessException {
		return (T) this.queryForObject(sql, new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				try {
					T object = classz.newInstance();
					for (Field f : object.getClass().getFields()) {
						f.setAccessible(true);
						if (f.isAnnotationPresent(Id.class)) {
							f.set(object, rs.getObject("id"));
						} else if (f.isAnnotationPresent(Column.class)) {
							Column column = f.getAnnotation(Column.class);
							f.set(object, rs.getObject(column.name()));
						}
					}
					return object;
				} catch (InstantiationException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return null;
			}

		}, parameters);

	}

	public <T> T parameters2Model(Class<T> clazz, Map<String, Object> parameters)
			throws IllegalArgumentException, IllegalAccessException,
			InstantiationException {
		T model = clazz.newInstance();
		for (Field f : model.getClass().getFields()) {
			f.setAccessible(true);
			if (f.isAnnotationPresent(Id.class)) {
				f.set(model, parameters.get("id"));
			} else if (f.isAnnotationPresent(Column.class)) {
				Column column = f.getAnnotation(Column.class);
				f.set(model, parameters.get(column.name()));
			}

		}
		return model;
	}
}

 

 

package com.snailteam.team.dao;

import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.hibernate.id.UUIDHexGenerator;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.snailteam.team.model.SimpleJdbcTemplateExt;
import com.snailteam.team.model.User;

@ContextConfiguration(locations = { "classpath*:/META-INF/spring/applicationContext*.xml" })
@RunWith(SpringJUnit4ClassRunner.class)
public class UserServiceTest {

	@Resource
	DataSource dataSource;

	@Test
	public void testInsert() throws SecurityException, NoSuchFieldException,
			IllegalArgumentException, IllegalAccessException, SQLException {
		User tUser = new User();
		tUser.username = "xiaofancn";
		tUser.password = "apsdf";
		tUser.version = 3L;
		tUser.id = 5;
		tUser.reg_date = new Date();
		SimpleJdbcTemplateExt simpleJdbcTemplateExt = new SimpleJdbcTemplateExt(
				dataSource);
		System.out.println(simpleJdbcTemplateExt.insert(tUser));
	}

	@Test
	public void testSelect() throws SecurityException, NoSuchFieldException,
			IllegalArgumentException, IllegalAccessException, SQLException,
			InstantiationException {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("id", 1);
		SimpleJdbcTemplateExt simpleJdbcTemplateExt = new SimpleJdbcTemplateExt(
				dataSource);
		User user = simpleJdbcTemplateExt.select(User.class,
				"select * from vuser where id=:id", params);
		System.out.println(user);
	}
}

 

 

再来一个工具累

import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;


import org.springframework.jdbc.InvalidResultSetAccessException;
import org.springframework.jdbc.support.rowset.SqlRowSet;

public class JdbcUntil {

	public static <T> T parameters2Model(Class<T> clazz,
			Map<String, Object> parameters) {
		T model = null;
		try {
			model = clazz.newInstance();
		} catch (InstantiationException e1) {
			println(e1.getMessage());
		} catch (IllegalAccessException e1) {
			println(e1.getMessage());
		}
		Field[] fields = model.getClass().getFields();
		for (Field f : fields) {
			if (parameters.get(f.getName()) == null
					|| parameters.get(f.getName()).equals("null"))
				continue;
			f.setAccessible(true);
			try {
				f.set(model, parameters.get(f.getName()));
			} catch (IllegalArgumentException e) {
				println(e.getMessage());
			} catch (IllegalAccessException e) {
				println(e.getMessage());
			}
		}
		return model;
	}

	public static <T> T resultSet2Model(Class<T> clazz, ResultSet rs) {
		T model = null;
		try {
			model = clazz.newInstance();
		} catch (InstantiationException e) {
			println(e.getMessage());
		} catch (IllegalAccessException e) {
			println(e.getMessage());
		} finally {
			if (model == null)
				return model;
		}
		Field[] fields = model.getClass().getFields();
		for (Field f : fields) {
			f.setAccessible(true);
			try {
				f.set(model, rs.getObject(f.getName()));
			} catch (IllegalArgumentException e) {
				try {
					if (f.getType().equals(Integer.class)) {
						f.set(model,
								Integer.valueOf("" + rs.getObject(f.getName())));
					} else if (f.getType().equals(Long.class)) {
						f.set(model,
								Long.valueOf("" + rs.getObject(f.getName())));
					}
				} catch (Exception e2) {
					println("转换" + f.getName() + "错误");
				}
			} catch (IllegalAccessException e) {
				println(e.getMessage());
			} catch (SQLException e) {
				println(e.getMessage());
			}
		}
		return model;
	}

	public static <T> T sqlRowSet2Model(Class<T> clazz, SqlRowSet parameters) {
		T model = null;
		try {
			model = clazz.newInstance();
		} catch (InstantiationException e) {
			println(e.getMessage());
		} catch (IllegalAccessException e) {
			println(e.getMessage());
		} finally {
			if (model == null)
				return model;
		}
		Field[] fields = model.getClass().getFields();
		for (Field f : fields) {
			f.setAccessible(true);
			try {
				f.set(model, parameters.getObject(f.getName()));
			} catch (InvalidResultSetAccessException e) {
				println(e.getMessage());
			} catch (IllegalArgumentException e) {
				println(e.getMessage());
			} catch (IllegalAccessException e) {
				println(e.getMessage());
			}
		}
		return model;
	}

	public static Map<String, Object> model2Parameters(Object obj,
			boolean needNull) {
		Map<String, Object> paraMap = new HashMap<String, Object>();
		for (Field f : obj.getClass().getFields()) {
			try {
				if (!needNull && !(f.get(obj) == null))
					paraMap.put(f.getName(), f.get(obj));
				else
					paraMap.put(f.getName(), f.get(obj));
			} catch (IllegalArgumentException e) {
				println(e.getMessage());
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}
		}
		return paraMap;
	}

	public static String rowsFetchIdString(List<Map<String, Object>> rows,
			String keyword) {
		Set<Object> filter = new HashSet<Object>();
		StringBuffer sb = new StringBuffer();
		for (Map<String, Object> row : rows) {
			if (row.get(keyword) == null || filter.contains(row.get(keyword)))
				continue;
			filter.add(row.get(keyword));
			sb.append(row.get(keyword) + ",");
		}
		sb.append("-9");
		return sb.toString();
	}

	public static void println(String info) {
		System.out.println(new Date() + "JdbcUntil -----" + "  " + info);
	}
}