前面博客把bean、aop简单了解了一下，今天主要是了解Spring中DAO层，如果使用传统的JDBC时需要创建连接、打开、执行sql、关闭连接这一系列的步骤，Spring框架对JDBC进行了封装，我们只需使用封装好的JdbcTemplate执行sql语句。主要还是继承了Spring提供的JdbcDaoSupport。下面主要是用来演示怎么使用JdbcDaoSupport。
一、创建Model
这里创建一个User类，作为model。
package Cuiyw.Spring.Model; public class User { private int userId; public int getUserId() { return userId; } public void setUserId(int userId) { this.userId = userId; } public String getName() { return name; } public void setName(String name) { this.name = name; } private String name; private float money; public float getMoney() { return money; } public void setMoney(float money) { this.money = money; } }
二、创建DAO接口并实现接口
1.这里创建IUserDAO接口，声明了两个方法，一个是获取所有的User，一个是新增User。
package Cuiyw.Spring.IDao; import java.util.*; import Cuiyw.Spring.Model.User; public interface IUserDAO { public List<User>QueryAllUser(); public Boolean AddUser(String name,float money); }
2.这里实现接口IUserDAO,并且继承JdbcDaoSupport。在下面的代码中能找到getJdbcTemplate()，其实我们右键选择Open declaration 时可以定位到抽象类JdbcDaoSupport，JdbcDaoSupport里面有一个JdbcTemplate属性，而且还有get或set JDBC DataSource，然后选中JdbcTemplate，再Open declaration,发现JdbcTemplate的构造函数需要DataSource，JdbcDaoSupport提供JdbcTemplate对象，并维护着JdbcTemplate对象需要的DataSource。
package Cuiyw.Spring.Dao; import java.util.*; import org.springframework.jdbc.core.support.JdbcDaoSupport; import Cuiyw.Spring.IDao.IUserDAO; import Cuiyw.Spring.Model.User; public class UserDAO extends JdbcDaoSupport implements IUserDAO { public List<User> QueryAllUser() { String sql="select id,name,money from tb_user"; List<Map<String,Object>> list=getJdbcTemplate().queryForList(sql); List<User> userList=new ArrayList<User>(); for(Map<String,Object> row:list) { User user=new User(); user.setUserId((Integer)row.get("id")); user.setName((String)row.get("name")); user.setMoney((Float)row.get("money")); userList.add(user); } return userList; } public Boolean AddUser(String name,float money) { String sql="insert into tb_user (name,money) values (?,?)"; int row=getJdbcTemplate().update(sql, new Object[]{name,money}); if(row>0) { return true; } return false; } }
public JdbcTemplate(DataSource dataSource) { setDataSource(dataSource); afterPropertiesSet(); }
3.上下文配置
上面DataSource从哪里来呢?就需要在上下文中配置。首先配置DataSource，然后由于UserDAO继承了抽象类JdbcDaoSupport，所以也需要设置属性dataSource
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context" xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop" 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/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd "> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"> <property name="driverClassName"> <value>com.mysql.jdbc.Driver</value> </property> <property name="url"> <value>jdbc:mysql://localhost:3306/spring</value> </property> <property name="username"> <value>root</value> </property> <property name="password"> <value>123456</value> </property> </bean> <bean id="userDao" class="Cuiyw.Spring.Dao.UserDAO" depends-on="dataSource"> <property name="dataSource" ref="dataSource"></property> </bean> </beans>
4.Mysql新增数据库和表
上面2中UserDAO执行了两个sql，涉及到表tb_user，字段id、name、money。在3上下文配置mysql时指定了数据库名spring。所以按照上面的创建。
5.实验
ApplicationContext context=new ClassPathXmlApplicationContext(new String[]{"ApplicationContext.xml"}); BeanFactory factory=context; IUserDAO userDao=(IUserDAO)factory.getBean("userDao"); userDao.AddUser("cuiywA",888); userDao.AddUser("cuiywB",666); List<User> list=userDao.QueryAllUser(); for(User u:list) { System.out.println("name:"+u.getName()+" money: "+u.getMoney()); }
6.错误
在实现上面的步骤时报了好几个错误，有因为我疏忽的，还有数据类型和数据库不一直的，最重要的两个我这里也记录了下来。
1.下面的错误是由于缺少commons-dbcp.jar、commons-pool.jar导致的
Cannot find class [org.apache.commons.dbcp.BasicDataSource] for bean with name ‘dataSource‘ defined in class path resource [ApplicationContext.xml]; nested exception is java.lang.ClassNotFoundException: org.apache.commons.dbcp.BasicDataSource
2.还有就是我在UserDAO中继承了JdbcDaoSupport，但就是没自动提示要引入的包，这个第一次没经验，百度了才指定需要引入spring-jdbc.jar.
3.忘记引入mysql驱动
Failed to obtain JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class ‘com.mysql.jdbc.Driver‘
上面的错误都是没有引入jar包，这里把pom.xml也放进来，留着以后好用。
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>Cuiyw</groupId> <artifactId>SpringAop</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>SpringAop</name> <url>http://maven.apache.org</url> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <spring.version>5.0.0.RELEASE</spring.version> </properties> <dependencies> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>commons-dbcp</groupId> <artifactId>commons-dbcp</artifactId> <version>1.4</version> </dependency> <dependency> <groupId>commons-pool</groupId> <artifactId>commons-pool</artifactId> <version>1.6</version> </dependency> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.6</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>3.8.1</version> <scope>test</scope> </dependency> <!-- https://mvnrepository.com/artifact/aspectj/aspectjweaver --> <dependency> <groupId>aspectj</groupId> <artifactId>aspectjweaver</artifactId> <version>1.5.4</version> </dependency> <!-- https://mvnrepository.com/artifact/aspectj/aspectjrt --> <dependency> <groupId>aspectj</groupId> <artifactId>aspectjrt</artifactId> <version>1.5.4</version> </dependency> </dependencies> </project>