1.概念
Spring JDBC是Sping框架对JDBC的简单封装,提供了JDBCTemplate对象简化JDBC的开发
2.步骤
1)导入jar包,set as library
jar包名称 | |
---|---|
spring-beans-5.2.6.RELEASE.jar | 前面4个在spring网站上搜 |
spring-core-5.2.6.RELEASE.jar | |
spring-jdbc-5.2.6.RELEASE.jar | |
spring-tx-5.2.6.RELEASE.jar | https://repo.spring.io/webapp/#/artifacts/browse/tree/General/libs-release-local/org/springframework/ |
commons-logging-1.2.jar | http://commons.apache.org/proper/commons-logging/download_logging.cgi |
2)创建JdbcTemplate对象。需要DateSource作为参数
JdbcTemplate template = new JdbcTemplate(ds)
3)调用JdbcTemplate的方法来完成CRUD的操作
(1)update():执行DML,增、删、改语句【test1()】
(2)queryForMap():sql查询结果只有一条时,将结果封装成Map对象【test2()】
(3)queryForList():查询结果有多条时,将结果封装成map,存储到list对象【 test3()】
(4)query():查询结果,将结果封装成JavaBean对象【test5、6()】
(5)queryForObject():聚合函数的查询结果,将结果封装成对象【test7()】
3.代码实现
package jdbctemplate;
import jdcp.JDPCUtiles;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.testng.annotations.AfterTest;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.Test;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
public class TemplateTest {
/**
* 1.修改一条记录
* 2.添加一条记录
* 3.删除刚添加的记录
* 4.查询记录,封装成Map集合
* 5.查询所有记录,封装成List集合
* 6.查询所有记录,封装成Emp对象的List集合
* 7.查询所有记录数
*/
//TestNG测试,可以让方法独立的运行,不需要依赖main()方法
private JdbcTemplate template = null ;
@BeforeTest
public void beforeTest(){
//1.获取JdbcTemplate
template = new JdbcTemplate(JDPCUtiles.getDataSource());
}
@Test
public void test1(){
//2.定义sql
String sql = "insert into book values(?,?);";
//3.执行sql
int update = template.update(sql,"p","e");
System.out.println(update);
}
@Test
public void test2(){
String sql = "select * from book where author = ?";
//queryForMap查询结果存储到map集合中,集合中只能存储一条。若查询结果有多条,存储失败。
Map<String, Object> bb = template.queryForMap(sql, "niuren");
System.out.println(bb);
}
@Test
public void test3(){
String sql = "select * from book where author = ?";
//将每一条记录封装成Map集合,再将Map集合装载到List集合中
List<Map<String, Object>> bb = template.queryForList(sql, "bb");
System.out.println(bb);
}
@Test
public void test4(){
String sql = "select * from book";
//自己实现:不建议使用
// 将每一条记录封装成Book对象(JavaBean),再将Book装载到List集合中
List<Book> list = template.query(sql, new RowMapper<Book>() {
@Override
public Book mapRow(ResultSet resultSet, int i) throws SQLException {
Book book = new Book();
String name = resultSet.getString("name");
String author = resultSet.getString("author");
book.setName(name);
book.setAuthor(author);
return book;
}
});
for (Book book : list) {
System.out.println(book);
}
}
@Test
public void test5(){
String sql = "select * from book";
//使用已经实现的类
// 将每一条记录封装成Book对象(JavaBean),再将Book装载到List集合中
//注意:Book类中属性要用封装类定义,例如int的要用Integer.
List<Book> list = template.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
for (Book book : list) {
System.out.println(book);
}
}
@Test
public void test6(){
String sql = "select * from book where author = ?";
//使用已经实现的类
// 将每一条记录封装成Book对象(JavaBean),再将Book装载到List集合中
//注意:Book类中属性要用封装类定义,例如int的要用Integer.
List<Book> list = template.query(sql, new BeanPropertyRowMapper<Book>(Book.class),"bb");
for (Book book : list) {
System.out.println(book);
}
}
@Test
public void test7(){
String sql = "select count(*) from book";
//聚合函数
Long aLong = template.queryForObject(sql, Long.class);
System.out.println(aLong);
}
}