Spring JDBC应用

老总提出新的需求，要直接用xml文件来写CRUD，所以原先的SSI中的JavaBean不能再写了，ibatis也要废除，以后spring也可能要替换掉。所以，ibatis首当其冲，我自己要解析xml文件，其实是实现一部分ibatis的功能。

鉴于xml文件中直接写sql语句，所以若要利用好spring，则需要用spring JDBC来执行sql语句。因此，首先就要自己搭建一套Spring JDBC。流程如下：

1、配置文件中dataSource、JdbcTemplate配置好

	<bean id="defaultDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName">
			<value>com.mysql.jdbc.Driver</value>
		</property>
		<property name="url">
			<value>jdbc:mysql://localhost:3306/train?useUnicode=true&characterEncoding=gb2312</value>
		</property>
		<property name="username">
			<value>root</value>
		</property>
		<property name="password">
			<value>nowerbupt</value>
		</property>
	</bean>
	
	<bean id="defaultJdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">   
        <property name="dataSource">
        	<ref bean="defaultDataSource"/>
        </property>   
	</bean>
	
	<bean id="defaultJdbcDao" class="com.test.spring.jdbc.JdbcDao">   
    	<property name="jdbcTemplate">
    	 	<ref bean="defaultJdbcTemplate" />   
    	</property>
	</bean>

2、定义自己的JdbcDao，这里继承了JdbcDaoSupport，它对JdbcTemplate进行了封装

public class JdbcDao extends JdbcDaoSupport implements IJdbcDao{

	
	public Object executeQuery(String sql, Object[] paras){
		
		return getJdbcTemplate().query(sql, paras,
				new ResultSetExtractor() {
					public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
						while (rs.next()) {
							Object[] obj = new Object[20];
							for (int j = 1; j <= rs.getMetaData().getColumnCount(); j++) {
								String columnName = rs.getMetaData().getColumnName(j);
								obj[j - 1] = rs.getObject(j);
								
								System.out.println(columnName + ":" + obj[j-1]);
							}
						}
						return null;
					}
				});
	}

	
	public int update(String sql, Object[] paras){
		return getJdbcTemplate().update(sql, paras);
	}
}

3、测试

public class Main {

	public static void main(String [] args){
		ApplicationContext cont = new ClassPathXmlApplicationContext("com/test/spring/jdbc/test-db.xml");
//		String s= cont.getBean("&factorybean1",String.class);
		JdbcDao dao = (JdbcDao) cont.getBean("defaultJdbcDao");
		System.out.println(dao.getClass());
		//
		String sql = "select count(*) from cati_answer_20130913";// where stream_number='CCzLNHBFH20131008155535204NAvZuG'
		Object [] paras = new Object[]{};
		dao.executeQuery(sql, paras);
		
		String sql2 = "delete from cati_answer_20130913 where stream_number=?";
		Object [] paras2 = new Object[]{"oC8rHI6rD201310141736442470C27oI"};
		int res = dao.update(sql2, paras2);
		//
		System.out.println("update result:" + res);
	}
}