spring jdbc之最简单的增删改查

首发地址： http://inmethetiger.iteye.com/blog/1686468

参考于：http://jinnianshilongnian.iteye.com/blog/1423897

 

JdbcTemplate提供以下几类方法：

1：execute方法：可以用于执行任何SQL语句，主要是ddl语句（create,drop ,alter,truncate）

2：update方法和batchUpdate方法：用户新增，修改，删除，批处理

3：query和queryForXXX：用于执行查询语句

4：call方法：用于回调。

以下实例包含了一个简单的增删改查实例：

 

import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class JdbcTemlateTest {
	private static JdbcTemplate jdbcTemplate;
	private Log log = LogFactory.getLog(JdbcTemlateTest.class);

	@BeforeClass
	public static void setUpClass() {
		String url = "jdbc:hsqldb:mem:test";
		String username = "sa";
		String password = "";
		DriverManagerDataSource dataSource = new DriverManagerDataSource(url,
				username, password);
		dataSource.setDriverClassName("org.hsqldb.jdbcDriver");
		jdbcTemplate = new JdbcTemplate(dataSource);
	}

	@Test
	public void test() {
		// 1.声明SQL
		String sql = "select * from INFORMATION_SCHEMA.SYSTEM_TABLES";
		jdbcTemplate.query(sql, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				// 2.处理结果集
				String value = rs.getString("TABLE_NAME");
				System.out.println("Column TABLENAME:" + value);
			}
		});
	}

	// 创建表结构
	@Before
	public void before() {
		String createTableSql = "create memory table test"
				+ "(id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, "
				+ "name varchar(100))";
		jdbcTemplate.update(createTableSql);
	}

	@After
	public void tearDown() {
		String dropTableSql = "drop table test";
		jdbcTemplate.execute(dropTableSql);
	}

	/*
	 * 1:新增测试
	 */
	@Test
	public void insert() {
		jdbcTemplate.update("insert into test(name) values ('name1')");
		jdbcTemplate.update("insert into test(name) values ('name2')");
		Assert.assertEquals(2,
				jdbcTemplate.queryForInt("select count(*) from test"));
	}

	/*
	 * 2：删除测试
	 */
	@Test
	public void delete() {
		// 先插入两条记录
		jdbcTemplate.update("insert into test(name) values ('name1')");
		jdbcTemplate.update("insert into test(name) values ('name2')");
		// 删除第一条记录
		jdbcTemplate.update("delete from test where name=?",
				new Object[] { "name1" });
		Assert.assertEquals(1,
				jdbcTemplate.queryForInt("select count(*) from test"));
	}

	/*
	 * 3:更新测试
	 */
	@Test
	public void update() {
		// 先插入两条记录
		jdbcTemplate.update("insert into test(name) values ('name1')");
		jdbcTemplate.update("insert into test(name) values ('name2')");

		// 更新第二条数据
		jdbcTemplate.update("update test set name='nameChange' where name=?",
				new Object[] { "name2" });

		Assert.assertEquals(
				1,
				jdbcTemplate
						.queryForInt("select count(*) from test where name='nameChange'"));
	}

	/*
	 * 4:选择测试
	 */
	@Test
	public void select() {
		jdbcTemplate.update("insert into test(name) values ('name1')");
		jdbcTemplate.update("insert into test(name) values ('name2')");
		jdbcTemplate.query("select * from test", new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				log.info("====id:" + rs.getInt("id"));
				log.info(",name:" + rs.getString("name"));
			}
		});
	}
}