项目的准备
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.17</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
样例表
之所以要使用jdbcTemplate来实现需要对数据库的一些的操作，是因为当我们不使用它时，我们难免会写很多重复且多余的代码，易使我们感到疲惫，乏味。
编写代码
依据数据库表的设计，先写项目的pojo
package com.tutorialspoint.pojo;
public class Department {
private Long DEPT_ID;
private String DEPT_NAME;
private String DEPT_NO;
private String LOCATION;
@Override
public String toString() {
return "Department{" +
"DEPT_ID=" + DEPT_ID +
", DEPT_NAME='" + DEPT_NAME + '\'' +
", DEPT_NO='" + DEPT_NO + '\'' +
", LOCATION='" + LOCATION + '\'' +
'}';
}
public Department() {
}
public Department(Long DEPT_ID, String DEPT_NAME, String DEPT_NO, String LOCATION) {
this.DEPT_ID = DEPT_ID;
this.DEPT_NAME = DEPT_NAME;
this.DEPT_NO = DEPT_NO;
this.LOCATION = LOCATION;
}
public Long getDEPT_ID() {
return DEPT_ID;
}
public void setDEPT_ID(Long DEPT_ID) {
this.DEPT_ID = DEPT_ID;
}
public String getDEPT_NAME() {
return DEPT_NAME;
}
public void setDEPT_NAME(String DEPT_NAME) {
this.DEPT_NAME = DEPT_NAME;
}
public String getDEPT_NO() {
return DEPT_NO;
}
public void setDEPT_NO(String DEPT_NO) {
this.DEPT_NO = DEPT_NO;
}
public String getLOCATION() {
return LOCATION;
}
public void setLOCATION(String LOCATION) {
this.LOCATION = LOCATION;
}
}
编写DAO接口
package com.tutorialspoint.DAO;
import com.tutorialspoint.pojo.Department;
import java.util.List;
public interface DepartmentDAO {
//用于查询数据库表中的所有数据
List<Department> findAll();
//返回数据库的大小（有多少条数据）
int size();
Department findById(Long id);
//批量插入数据
void insertBatch(List<Department> departments);
}
来实现DAO接口
package com.tutorialspoint.Impl;
import com.tutorialspoint.DAO.DepartmentDAO;
import com.tutorialspoint.Util.DepartmentRowMappr;
import com.tutorialspoint.pojo.Department;
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
//继承JbdcDaoSupport获取JdbcTemplate支持
public class DepartmentDAOImpl extends JdbcDaoSupport implements DepartmentDAO {
@Override
public List<Department> findAll() {
String sql = "SELECT * FROM department";
List<Department> departments = new ArrayList<Department>();
//返回的是一个带有map的集合 根据得到的数据
List<Map<String,Object>> departmentrows = getJdbcTemplate().queryForList(sql);
for(Map row:departmentrows){
Department department = new Department();
Integer a = (Integer) row.get("DEPT_ID");
Long b = a.longValue();
department.setDEPT_ID(b);
department.setDEPT_NO((String)row.get("DEPT_NO"));
department.setDEPT_NAME((String)row.get("DEPT_NAME"));
department.setLOCATION((String)row.get("LOCATION"));
departments.add(department);
}
return departments;
}
@Override
public int size() {
String sql ="SELECT COUNT(*) FROM department";
int total = getJdbcTemplate().queryForObject(sql,Integer.class);
return total;
}
@Override
public Department findById(Long id) {
String sql ="select * from department where DEPT_ID = ?";
Department department = (Department) getJdbcTemplate().queryForObject(sql,new Object[]{id},new DepartmentRowMappr());
return department;
}
@Override
public void insertBatch(List<Department> departments) {
String sql ="insert into department "+"VALUES(?,?,?,?)";
getJdbcTemplate().batchUpdate(sql, departments, departments.size(), new ParameterizedPreparedStatementSetter<Department>() {
@Override
public void setValues(PreparedStatement preparedStatement, Department department) throws SQLException {
preparedStatement.setLong(1,department.getDEPT_ID());
preparedStatement.setString(2,department.getDEPT_NAME());
preparedStatement.setString(3,department.getDEPT_NO());
preparedStatement.setString(4,department.getLOCATION());
}
});
}
}
编写配置文件
Spring-Department.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
">
<bean id="departmentDAO" class="com.tutorialspoint.Impl.DepartmentDAOImpl">
<property name="dataSource" ref="dataSource" />
</bean>
</beans>
Spring-database.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
<property name="url" value="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&userSSL=false &serverTimezone=GMT%2B8 " />
<property name="username" value="root" />
<property name="password" value="19991214" />
</bean>
</beans>
整合在一起
ab.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">
<import resource="Spring-Department.xml" />
<import resource="./Spring-database.xml" />
</beans>
编写测试例子
package com.tutorialspoint;
import com.tutorialspoint.DAO.DepartmentDAO;
import com.tutorialspoint.pojo.Department;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.ArrayList;
import java.util.List;
public class Application {
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext("ab.xml");
DepartmentDAO departmentDAO = (DepartmentDAO) context.getBean("departmentDAO");
List<Department> departments = departmentDAO.findAll();
for (Department department:departments){
System.out.println(department);
}
System.out.println(departmentDAO.size());
System.out.println(departmentDAO.findById(30L));
List<Department> departments1 = new ArrayList<Department>();
departments1.add(new Department(45L,"销售","D56","中国"));
departments1.add(new Department(78L,"消费者","D69","中国"));
departments1.add(new Department(56L,"shengchanzhe","D77","Chinese"));
departmentDAO.insertBatch(departments1);
System.out.println("----------------------------------------------------------");
System.out.println(departmentDAO.size());
List<Department> departments2 = departmentDAO.findAll();
for (Department department:departments2){
System.out.println(department);
}
}
}
运行结果
Department{DEPT_ID=0, DEPT_NAME='', DEPT_NO='', LOCATION='null'}
Department{DEPT_ID=10, DEPT_NAME='ACCOUNTING', DEPT_NO='D10', LOCATION='NEW YORK'}
Department{DEPT_ID=20, DEPT_NAME='RESEARCH', DEPT_NO='D20', LOCATION='DALLAS'}
Department{DEPT_ID=30, DEPT_NAME='SALES', DEPT_NO='D30', LOCATION='CHICAGO'}
4
Department{DEPT_ID=30, DEPT_NAME='SALES', DEPT_NO='D30', LOCATION='CHICAGO'}
----------------------------------------------------------
7
Department{DEPT_ID=0, DEPT_NAME='', DEPT_NO='', LOCATION='null'}
Department{DEPT_ID=10, DEPT_NAME='ACCOUNTING', DEPT_NO='D10', LOCATION='NEW YORK'}
Department{DEPT_ID=20, DEPT_NAME='RESEARCH', DEPT_NO='D20', LOCATION='DALLAS'}
Department{DEPT_ID=30, DEPT_NAME='SALES', DEPT_NO='D30', LOCATION='CHICAGO'}
Department{DEPT_ID=45, DEPT_NAME='销售', DEPT_NO='D56', LOCATION='中国'}
Department{DEPT_ID=56, DEPT_NAME='shengchanzhe', DEPT_NO='D77', LOCATION='Chinese'}
Department{DEPT_ID=78, DEPT_NAME='消费者', DEPT_NO='D69', LOCATION='中国'}
