老总提出新的需求,要直接用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);
}
}