Spring JDBC框架操作数据库
Spring中的JDBC为我们省去连接和关闭数据库的代码,我们着重关注对数据库的操作。Spring框架在数据库开发中的应用主要使用的是JDBCTemplate类,该类作为Spring对JDBC支持的核心,提供了所有对数据库操作功能的支持。我们将使用JDBCTemplate类完成对mysql数据库的增、删、改、查等操作。
Spring框架提供JDBC支持主要由4个包组成,分别是core(核心包)、object(对象包)、dataSource(数据源包)和support(支持包),JdbcTemplate类就在核心包中,该类包含所有数据库操作的基本方法。
- core核心包:包含了JDBC的核心功能,重要的类:JdbcTemplate类、SimpleJdbcInsert类、SimpleJdbcCall类以及NamedParameterJdbcTemplate类;
- dataSource:数据源包,访问数据源的实用工具类,他有多种数据源的实现,可以在JavaEE容器外部测试JDBC代码;
- object:对象包,以面向对象的方式访问数据库,它允许执行查询并返回结果作为业务对象,可以在数据表的列和业务对象的属性之间映射查询结果;
- support支持包:core和object包的支持类,例如,提供异常转换功能的SQLException类。
1、用户实体类
package cn.su.domain;
public class User {
private int id;
private String name;
private String sex;
private String address;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public User(int id, String name, String sex, String address) {
this.id = id;
this.name = name;
this.sex = sex;
this.address = address;
}
public User(int id, String address) {
this.id = id;
this.address = address;
}
public User() {
}
}
2、接口
package cn.su.dao;
import cn.su.domain.User;
import java.util.List;
public interface UserDao {
// 查询所有用户
public List<User> findAll();
// 修改用户
public void update(User user);
}
3、接口实现类
package cn.su.dao.impl;
import cn.su.dao.UserDao;
import cn.su.domain.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.lang.Nullable;
import org.springframework.stereotype.Repository;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
// 注解:数据访问
@Repository
public class UserDaoImpl implements UserDao {
// 注入JdbcTemplate 并自动装配
@Autowired
private JdbcTemplate jdbcTemplate;
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@Override
public List<User> findAll() {
String sql = "select * from user";
// RowMapper<User>() 是一个接口 包含下面未实现的方法
return jdbcTemplate.query(sql, new RowMapper<User>() {
@Nullable
@Override
public User mapRow(ResultSet rs, int i) throws SQLException {
User user = new User();
user.setId(rs.getInt(1));
user.setName(rs.getString(2));
user.setSex(rs.getString(3));
user.setAddress(rs.getString(4));
return user;
}
});
}
@Override
public void update(User user) {
String sql = "update user set address=? where id=?";
Object [] parms = {user.getAddress(),user.getId()};
jdbcTemplate.update(sql,parms);
}
}
4、service层
package cn.su.service;
import cn.su.dao.UserDao;
import cn.su.domain.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserService {
@Autowired
private UserDao userDao;
public UserDao getUserDao() {
return userDao;
}
public void setUserDao(UserDao userDao) {
this.userDao = userDao;
}
public List<User> findAll(){
return userDao.findAll();
}
public void update(User user){
userDao.update(user);
}
}
5、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:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context"
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
">
<!--自动扫描上下文包-->
<context:component-scan base-package="cn.su.*"></context:component-scan>
<!--配置数据源-->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/school"></property>
<property name="username" value="root"></property>
<property name="password" value="123456"></property>
</bean>
<!--配置jdbc-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!--事物管理-->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!--事物通知-->
<tx:advice id="txadvice" transaction-manager="transactionManager">
<tx:attributes>
<tx:method name="find*" read-only="true"/>
<tx:method name="update*" propagation="REQUIRED"/>
<tx:method name="*"/>
</tx:attributes>
</tx:advice>
<aop:config>
<aop:pointcut id="services" expression="execution(* cn.su.dao.impl.*.*(..))"></aop:pointcut>
<!--引用通知-->
<aop:advisor advice-ref="txadvice" pointcut-ref="services"></aop:advisor>
</aop:config>
</beans>
6、测试类
package cn.su.test;
import cn.su.domain.User;
import cn.su.service.UserService;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.List;
public class Test {
public static void main(String[] args) {
test01();
}
private static void test01() {
ApplicationContext ac = new ClassPathXmlApplicationContext("spring.xml");
UserService userService = ac.getBean("userService", UserService.class);
List<User> lists = userService.findAll();
for(User list:lists){
System.out.println(list.getId()+":"+list.getName()+":"+list.getAddress());
}
// int id = 201704;
// String address = "安阳";
//
// User user = new User(id,address);
// userService.update(user);
}
}
结果如下: