开发步骤
「1」导入spring-jdbc和spring-tx坐标
<dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>5.0.0.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>5.0.0.RELEASE</version> </dependency>「2」创建数据库和实体对象
package com.Itheima.domain; public class Account { private String name; private Double balance; public String getName() { return name; } public void setName(String name) { this.name = name; } public Double getBalance() { return balance; } public void setBalance(Double balance) { this.balance = balance; } @Override public String toString() { return super.toString(); } }public void testJdbcTemplate(){ // 创建数据源 ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml"); JdbcTemplate jdbcTemplate = applicationContext.getBean(JdbcTemplate.class); int rows = jdbcTemplate.queryForObject("select count(*) from person", Integer.class); int count = jdbcTemplate.update("insert into account values (?,?)", "zhangsan",5000); }「3」创建JdbcTemplate对象
<!-- 加载外部的properties文件--> <context:property-placeholder location="classpath:jdbc.properties"/> <!-- 数据源对象--> <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="driverClass" value="${jdbc.driver}"/> <property name="jdbcUrl" value="${jdbc.url}"/> <property name="user" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </bean> <!-- jdbc模板对象--> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"/> </bean>jdbc.url=jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&useSSL=false jdbc.username=root jdbc.password=root jdbc.driver=com.mysql.cj.jdbc.Driver「4」执行数据库操作
增
//=========1.新增数据========= int insertRow = jdbcTemplate.update("insert into account values (?,?)","zhangsan",5000); System.out.println("新增影响行数："+insertRow);删
@Test public void testDelete(){ jdbcTemplate.update("delete from account where name=?","jerry"); }改
@Test public void testUpdate(){ jdbcTemplate.update("update account set balance=? where name=?",10000,"jerry"); }查
此处的泛型就是你要封装的实体的泛型就是数据库对应的Java类
@Test public void testQueryAll(){ List<Account> query = jdbcTemplate.query("select * from account", new BeanPropertyRowMapper<Account>(Account.class)); System.out.println("query = " + query); }查某一条数据
@Test public void testQueryOne(){ Account account = jdbcTemplate.queryForObject("select * from account where name=?", new BeanPropertyRowMapper<Account>(Account.class), "jerry"); System.out.println("account = " + account); }聚合查询用到的 (下面的SQL用于辅助回忆)
查询全班总人数、总分、平均分、最高分、最低分
SELECT COUNT(*) AS 总人数, SUM(score) AS 总分, AVG(score) AS 平均分, MAX(score) AS 最高分, MIN(score) AS 最低分 FROM student;// 聚合查询 @Test public void testQueryAccount(){ Account account = jdbcTemplate.queryForObject("select count(*) from account", Account.class); System.out.println("account = " + account); }
