JdbcTemplate 研究

开发环境：eclipse+lomboz+mysql6.0
所需jar包：commons-dbcp.jar,commons-pool.jar,commons-logging.jar,mysql-connector-java-5.0.7-bin.jar,spring.jar
一、建立数据库(springdemo)和book表

CREATE TABLE `book` (
  `id` varchar(80) NOT NULL,
  `name` varchar(20) NOT NULL,
  `author` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

二、编写域(Book.java)和DAO访问层
域对象:Book.java

package test.jdbc;

public class Book {
	private String id;
	private String name;
	private String author;
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getAuthor() {
		return author;
	}
	public void setAuthor(String author) {
		this.author = author;
	}
}

DAO访问接口：BookDAO.java

package test.jdbc;

import java.util.List;

public interface BookDAO {
	List<Book> queryByAuthor(String author);

	List<Book> queryAll();

	void create(Book book);

	void update(Book book);

	void delete(String id);
}

DAO访问层：BookDAOImpl.java，继承JdbcDaoSupport，实现BookDAO

package test.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

public class BookDAOImpl extends JdbcDaoSupport implements BookDAO {
         //查询，简单的ORM映射
	class BookRowMapper implements RowMapper {
		@Override
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			// TODO Auto-generated method stub
			Book book = new Book();
			book.setId(rs.getString("id"));
			book.setName(rs.getString("name"));
			book.setAuthor(rs.getString("author"));
			return book;
		}

	}
          //查询所有记录
	@Override
	public List<Book> queryAll() {
		// TODO Auto-generated method stub
		return this.getJdbcTemplate().query("select * from book",
				new BookRowMapper());
	}
         //按作者查询
	@Override
	public List<Book> queryByAuthor(String author) {
		return this.getJdbcTemplate().query(
				"select * from book where author=?", new Object[] { author },
				new BookRowMapper());
	}
         //增加
	@Override
	public void create(Book book) {		
		this.getJdbcTemplate().update("insert into book(id,name,author) values(?,?,?)",new Object[]{book.getId(),book.getName(),book.getAuthor()});
	}
         //更新
	@Override
	public void update(Book book) {
		this.getJdbcTemplate().update("update book set name=?,author=? where id=?)",new Object[]{book.getId(),book.getName(),book.getAuthor()});
	}
         //删除
	@Override
	public void delete(String id) {
		// TODO Auto-generated method stub
		this.getJdbcTemplate().update("delete from book where id=?",new Object[]{id});
	}

}

三、编写配置文件:applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="
			http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
			http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
			http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">
	<!--使用apache的dbcp配置数据源-->
	<bean id="jdbcDataSource"
		class="org.apache.commons.dbcp.BasicDataSource"
		destroy-method="close">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url"
			value="jdbc:mysql://localhost:3306/springdemo" />
		<property name="username" value="root" />
		<property name="password" value="123456" />
	</bean>
	<bean id="jdbcTemplateBookDao" class="test.jdbc.BookDAOImpl">
		<property name="dataSource" ref="jdbcDataSource"/>
	</bean>	
</beans>

四、编写测试文件:Main.java

package test.jdbc;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.UUID;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class Main {

	/**
	 * @param args
	 * @throws SQLException
	 */
	public static void main(String[] args) throws SQLException {
		ApplicationContext ctx = new ClassPathXmlApplicationContext(
				"applicationContext.xml");
		BookDAO bookDao=(BookDAO)ctx.getBean("jdbcTemplateBookDao");		
		Book book;		
		//增加
		book= new Book();			
		book.setId(UUID.randomUUID().toString());
		book.setAuthor("lunzi2");
		book.setName("cow2");		
		bookDao.create(book);
		
		//查询所有
		List<Book> listAll=null;
		listAll=bookDao.queryAll();
		Iterator<Book> itor=listAll.iterator();
		while(itor.hasNext()){
			book=itor.next();
			System.out.println(book.getName());
		}
		//按作者查询
		listAll=bookDao.queryByAuthor("lunzi2");
		System.out.println("查询到记录为："+listAll.size()+"条");
		//删除
		bookDao.delete("16acc01f-0e6e-4d79-92ab-489f1a1cf457");
	}

}

ok,完成，简单。。。。