注：使用maven工程的pom.xml文件添加依赖
-
使用jdbcTemplate工具类必要的jar包依赖
<dependency> <groupId>cn.danielw</groupId> <artifactId>spring-jdbc-template</artifactId> <version>0.2.1</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId><!-- 因为使用了JDBCUtils工具类，使用到了Druid类--> <version>1.2.4</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.6</version> <scope>runtime</scope> </dependency> - JdbcTemplate.class类常用的方法
注：JdbcTemplate.class不需要再手动释放资源，其会自动释放资源，将connection对象归还给连接池
- update() :执行DML语句，即增删改操作，返回int类型
@Test public void update(){ String sql = "update student set sage=20 where sno = ?"; int i = temp.update(sql, 201215121);//指定参数，不需要双引号 System.out.println(); }
- queryForMap() :将查询结果封装成Map集合，将数据库列名作为key，将值作为value，将这条记录封装为一个map集合
注：这个方法查询的结果集长度只能是1。即，只能查询返回一条记录的sql语句。
@Test public void queryForMap(){ String sql = "select * from student where sno = ?"; Map<String, Object> map = temp.queryForMap(sql, 201215121); System.out.println(map); } //当sql语句执行返回结果是两条记录时，会报错 //org.springframework.dao.IncorrectResultSizeDataAccessException: Incorrect result size: expected 1, actual 2
- queryForList() :查询结果封装成List集合
注：将每一条记录封装成一个Map集合，将Map集合封装到List集合中:List<Map<String, Object>>
@Test public void queryForList(){ String sql = "select * from student limit 5"; List<Map<String, Object>> mapList = temp.queryForList(sql); for (Map<String, Object> stringObjectMap : mapList) { System.out.println(stringObjectMap); } }
- query() ：执行查询sql，将结果封装成为JavaBean对象
注：query()的参数：RowMapper
- 一般的，我们使用BeanPropertyRowMapper实现类，可以完成数据到JavaBean的封装
- new BeanBeanPropertyRowMapper<类型>(类型.class) 。类型就是要封装的JavaBean类型
- JavaBean类中必须是引用类型变量(Integer...)，不能是普通类型变量(int...)
- 具体看下面实例代码
@Test public void query(){ String sql = "select * from student limit 5"; List<Student> studentList = temp.query(sql, new BeanPropertyRowMapper<Student>(Student.class)); for (Student student : studentList) { System.out.println(student); } } //List<Student> studentList = //temp.query(sql, new BeanPropertyRowMapper<Student>(Student.class)); //BeanPropertyRowMapper中的参数写法一定要记忆
- queryForObject() ：查询结果，将结果封装成对象
注：一般此方法适用于sql语句为聚合函数的情况下。
- 返回值是Lang类型
- 第二个参数类型是：Lang.class 如：
@Test public void queryForObject(){ String sql = "select count(sno) from student"; Long count = temp.queryForObject(sql, Long.class); System.out.println(count); } //Long count = temp.queryForObject(sql, Long.class);
- JdbcTemplate测试类完整代码
package zhi.itlearn; import cn.danielw.spring.jdbc.JdbcTemplate; import org.junit.Test; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.RowMapper; import zhi.itlearn.domain.Student; import java.util.List; import java.util.Map; import java.util.PriorityQueue; public class JdbcTemplateTest1 { //将JdbcTemplate对象定义在方法外，简化代码 //JdbcTemplate类实例化方式：new JdbcTemplate(JDBCUtils.getDateSource()) //参数是：获得连接池对象，在此使用的是自定义的JDBCUtils工具类 private static JdbcTemplate temp = new JdbcTemplate(JDBCUtils.getDateSource()); @Test public void update(){ String sql = "update student set sage=20 where sno = ?"; int i = temp.update(sql, 201215121);//指定参数，不需要双引号 System.out.println(); } @Test public void queryForMap(){ String sql = "select * from student where sno = ? "; Map<String, Object> map = temp.queryForMap(sql, 201215121); System.out.println(map); } @Test public void queryForList(){ String sql = "select * from student limit 5"; List<Map<String, Object>> mapList = temp.queryForList(sql); for (Map<String, Object> stringObjectMap : mapList) { System.out.println(stringObjectMap); } } @Test public void query(){ String sql = "select * from student limit 5"; List<Student> studentList = temp.query(sql, new BeanPropertyRowMapper<Student>(Student.class)); for (Student student : studentList) { System.out.println(student); } } @Test public void queryForObject(){ String sql = "select count(sno) from student"; Long count = temp.queryForObject(sql, Long.class); System.out.println(count); } }
- JDBCUtils工具类
package zhi.itlearn; import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource; import java.io.IOException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; public class JDBCUtils { private static DataSource ds; static { try { //加载配置文件 Properties pro = new Properties(); pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("duril-config.properties")); //获得DataSource连接池对象 //使用Durid工具类中DruidDataSourceFactory类获得连接池对象【重点】 ds = DruidDataSourceFactory.createDataSource(pro); } catch (IOException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } } public static DataSource getDateSource(){ return ds; } public static Connection getConnection() throws SQLException { return ds.getConnection(); } public static void close(Statement stat,Connection conn){ close(null,stat,conn); } public static void close(ResultSet rs, Statement stat, Connection conn){ if (rs!=null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (stat!=null){ try { stat.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn!=null){ try { conn.close(); //归还连接 } catch (SQLException e) { e.printStackTrace(); } } } }
- properties配置文件(durid-config.properties)
driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/xskc username=root password=root initialSize=5 maxActive=10 maxWait=3000