spring jdbc

jdbc 集成

org.springframework.jdbc

org.springframework.jdbc .core

org.springframework.jdbc .dataSource

org.springframework.jdbc .object

org.springframework.jdbc .support

 

spring jdbc集成 使用模板模式（Template ） 简化了jdbc的使用 初始化/释放数据库连接、捕捉异常、清理重复代码。。。

spring/jdbc/DBMain.java

...
public class DBMain {

	private static Log log = LogFactory.getLog(DBMain.class);

	public static void main(String[] args) {
		AbstractApplicationContext cxt = new FileSystemXmlApplicationContext("beans.xml");

		JdbcTemplate t = (JdbcTemplate) cxt.getBean("jdbcTemplate");
		String sql = "select * from book";
		List<Book> bookInfo = (List) t.query(sql, new RowMapper() {
			public Object mapRow(ResultSet rs, int roeNum) throws SQLException {
				return rs.getInt("id") + "; " + rs.getString("title") + "; "
						+ rs.getDate("pub_time") + "; " + rs.getFloat("prize")
						+ ";" + rs.getString("intro");
			}
		});
		for (String book : bookInfo) {
			log.debug(book);
		}
	}

}

 RowMapper将查询结果按行映射到了一个简单的Book对象，query的结果是一个Book的列表

 

beans.xml

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
  <property name="url" value="jdbc:mysql://127.0.0.1:3306/bookstore"></property>
  <property name="username" value="root"></property>
  <property name="password" value=""></property>
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
  <property name="dataSource" ref="dataSource"></property>
</bean>
 

 

 

object包 对jdbc操作进行了更高层次的封装 以更面向对象得方式执行查询、更新。。。

spring/jdbc/Book.java

...
public class Book {
	private int id;
	private String title;
	private Date pubTime;
	private float prize;
	private String intro;

	// setter们 和 getter们
}

 

spring/jdbc/BookQuery.java

...
public class BookQuery extends MappingSqlQuery {

	public BookQuery(DataSource dataSource) {
		super(dataSource, "select * from book");
		compile();
	}

	protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
		Book book = new Book();
		book.setId(rs.getInt("id"));
		book.setTitle(rs.getString("title"));
		book.setPubTime(rs.getDate("pub_time"));
		book.setPrize(rs.getFloat("prize"));
		book.setIntro(rs.getString("intro"));
		return book;
	}

}

 

spring/jdbc/BookInsert.java

...
public class BookInsert extends SqlUpdate {

	public BookInsert(DataSource ds) {
		super(ds, "insert into book(title,pub_time,prize,intro) values(?,?,?,?)");
		declareParameter(new SqlParameter(Types.VARCHAR));
		declareParameter(new SqlParameter(Types.DATE));
		declareParameter(new SqlParameter(Types.FLOAT));
		declareParameter(new SqlParameter(Types.VARCHAR));
		compile();
	}

	public void insert(Book book) {
		super.update(new Object[] { book.getTitle(), book.getPubTime(),
				book.getPrize(), book.getIntro() });
	}

}

 

spring.jdbc/JdbcMain.java

...
	public static void main(String[] args) {
		JdbcMain db = new JdbcMain();
		AbstractApplicationContext cxt = new FileSystemXmlApplicationContext(
				"jdbc.xml");

		DataSource dataSource = (DataSource) cxt.getBean("dataSource");
		db.addBook(dataSource);
		db.listBook(dataSource);

	}

	public void listBook(JdbcTemplate t) {
		String sql = "select * from book";
		List<String> bookInfo = (List<String>) t.query(sql, new RowMapper() {
			@Override
			public Object mapRow(ResultSet rs, int roeNum) throws SQLException {
				return rs.getInt("id") + "; " + rs.getString("title") + "; "
						+ rs.getDate("pub_time") + "; " + rs.getFloat("prize")
						+ ";" + rs.getString("intro");
			}
		});
		for (String book : bookInfo) {
			log.debug(book);
		}
	}

	public void listBook(DataSource dataSource) {
		BookQuery bookQuery = new BookQuery(dataSource);
		List<Book> bookList2 = bookQuery.execute();
		for (Book book : bookList2) {
			log.debug(book.getTitle() + "; " + book.getPrize() + "; "
					+ book.getPubTime() + "; " + book.getIntro());
		}
	}

	public void addBook(DataSource dataSource) {
		BookInsert bookInsert = new BookInsert(dataSource);
		Book book = new Book();
		book.setTitle("alice in wonder world");
		book.setPubTime(new Date());
		book.setPrize(20);
		book.setIntro("");
		bookInsert.insert(book);
	}
...