JDBC Template（简化持久化操作）
（一）创建项目
（1）Maven配置
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.41</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>5.1.5.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>5.1.5.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.1.5.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-aop -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>5.1.5.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.1.5.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.1.5.RELEASE</version>
</dependency>
</dependencies>
（2）spring配置
在resource中创建一个spring.xml文件
spring.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"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd">
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/zml?useUnicode=true&characterEncoding=utf-8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource" ></property>
</bean>
</beans>
（二）JDBC Template基本使用
（三）update和batchupdate
（1）使用方法
（2）实例
public class UserTest {
//创建jdbcTemplate对象，调用方法
private JdbcTemplate jdbcTemplate;
{
ApplicationContext context=new ClassPathXmlApplicationContext("spring.xml");
jdbcTemplate=(JdbcTemplate)context.getBean("jdbcTemplate");
}
//增加
public void add1(){
String sql="insert into users values(null,?,?)";
jdbcTemplate.update(sql,"小木","123");
}
//修改
public void update1(){
String sql="update users set password=? where username=?";
jdbcTemplate.update(sql,new Object[]{"123456","张三"});
}
//删除
public void delete1(){
String sql="delete from users where username=?";
jdbcTemplate.update(sql,"李四");
}
//批量增加,修改，删除
public void batchUpdate(){
String[] sql={
"insert into users values(null,'小花','123')",
"insert into users values(null,'喵喵','123')",
"update users set password='123456' where username='小木'",
"delete from users where username='zhang'"
};
jdbcTemplate.batchUpdate(sql);
}
//批量增加
@Test
public void batchUpdate2(){
String sql="insert into users values(null,?,?)";
List<Object[]> list=new ArrayList<Object[]>();
list.add(new Object[]{"哈哈","123"});
list.add(new Object[]{"小黑","123"});
list.add(new Object[]{"沐沐","123"});
jdbcTemplate.batchUpdate(sql,list);
}
}
（四）查询
（1）查询简单数据项
（a）使用方法
（b）实例
//查询密码为123的所有用户的用户名
public void select(){
String sql="select username from users where password=?";
List<String> li=jdbcTemplate.queryForList(sql, String.class,"123");
System.out.println(li);
}
//查询用户有几人
public void selectcount(){
String sql="select count(*) from users ";
int list=jdbcTemplate.queryForObject(sql,Integer.class);
System.out.println(list);
}
（2）查询复杂对象
（a）使用方法
（b）实例
//查询用户名为喵喵的用户的所有信息
public void selectname(){
String sql="select * from users where username=?";
Map<String ,Object> map=jdbcTemplate.queryForMap(sql, "喵喵");
System.out.println(map);
}
//查询所有用户的所有信息
public void selectall(){
String sql="select * from users ";
List<Map<String ,Object>> map=jdbcTemplate.queryForList(sql);
System.out.println(map);
}
//查询后返回一个实体对象
public void selectone(){
String sql="select * from users where username=?";
Users users=jdbcTemplate.queryForObject(sql,new UserRowMapper(),"小花");
System.out.println(users);
}
//查询所有用户信息,实体封装
@Test
public void selectAll(){
String sql="select * from users ";
List<Users> us=jdbcTemplate.query(sql,new UserRowMapper());
System.out.println(us);
}
//将RowMapper分离，简化代码
private class UserRowMapper implements RowMapper<Users> {
@Override
public Users mapRow(ResultSet resultSet, int i) throws SQLException {
Users u = new Users();
u.setId(resultSet.getInt("id"));
u.setUsername(resultSet.getString("username"));
u.setPassword(resultSet.getString("password"));
return u;
}
}