做程序开发,难免要操作数据库。在使用Spring框架的时候,Spring已经集成了传统的JDBC的使用,Hibernate,JPA,Ibatis等ORM框架。传统的JDBC的使用最让人心烦的就是随便写一个sql都要一大堆样板式代码;像获取数据库连接,准备参数,提交sql,关闭连接,关闭参数。这让程序员都不能专注于业务逻辑的开发,严重的影响了开发效率,代码臃肿。现在就来学习Spring集成JDBC的使用。
1、首先新建一个Maven项目(需要先安装好Maven),项目结构如下
2、在pom.xml(有几个jar包是aop,定时器用到的jar报,与本次内容无关)文件中配置如下:
<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>com.csii.spring</groupId>
<artifactId>SpringBean_01</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>SpringBean_01</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<springframework.version>3.2.3.RELEASE</springframework.version>
</properties>
<repositories>
<repository>
<snapshots>
<enabled>false</enabled>
</snapshots>
<id>central</id>
<name>Maven Repository Switchboard</name>
<url>http://repo2.maven.org/maven2</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.0</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${springframework.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>${springframework.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${springframework.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>${springframework.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>${springframework.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>${springframework.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>${springframework.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${springframework.version}</version>
</dependency>
<dependency>
<groupId>org.aspectjweaver</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.7.2</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.0</version>
</dependency>
<dependency>
<groupId>commons-collections</groupId>
<artifactId>commons-collections</artifactId>
<version>3.1</version>
</dependency>
<dependency>
<groupId>org.quartz-scheduler</groupId>
<artifactId>quartz</artifactId>
<version>1.8.5</version>
</dependency>
<dependency>
<groupId>commons-pool</groupId>
<artifactId>commons-pool</artifactId>
<version>1.5.4</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.2.2</version>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.20</version>
</dependency>
</dependencies>
<build>
<finalName>${project.artifactId}</finalName>
<plugins>
<plugin>
<groupId>org.mortbay.jetty</groupId>
<artifactId>jetty-maven-plugin</artifactId>
<version>7.5.4.v20111024</version>
<configuration>
<scanIntervalSeconds>10</scanIntervalSeconds>
<webApp>
<contextPath>/${project.artifactId}</contextPath>
</webApp>
</configuration>
</plugin>
</plugins>
</build>
<!-- <properties>
<springframework.version>3.2.3.RELEASE</springframework.version>
</properties> -->
</project>
上面通过maven引入的jar报有:
Spring核心jar报:Spring-core-3.2.2.Release.jar,Spring-beans-3.2.3.Release.jar,Spring-context-support-3.2.3.Release.jar。
Spring集成第三方orm需要用到的jar包:Spring-orm--3.2.2.Release.jar,Spring-jdbc-3.2.2.Release.jar。
数据库连接需要用到的jar包:commons-dbcp-1.2.2.jar,comoons-pool-1.5.4.jar,commons-collection-3.1.jar,mysql-connector-java-5.1.20.jar
(其它jar包现在用不到)
3、配置applicationContext.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"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.2.xsd">
<import resource="bean/bean.xml"/>
<import resource="datasource.xml"/>
</beans>
其中datasource.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"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.2.xsd">
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/dbtest?useUnicode=true&characterEncoding=utf8"></property>
<property name="username" value="root"></property>
<property name="password" value="root"></property>
<!--maxActive: 最大连接数量-->
<property name="maxActive" value="20"/>
<!--minIdle: 最小空闲连接-->
<property name="minIdle" value="0"/>
<!--maxIdle: 最大空闲连接-->
<property name="maxIdle" value="2"/>
<!--initialSize: 初始化连接-->
<property name="initialSize" value="10"/>
<!-- 连接被泄露时是否打印 -->
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<constructor-arg ref="dataSource"></constructor-arg>
</bean>
<bean id="userDao" class="com.csii.dao.impl.UserDAOImpl">
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>
</beans>
这里面主要是配置数据库连接池,jdbcTemplate模板。配置datasource的时候,一般最好是用数据库连接池BasicDataSource;像DriverManagerDataSource每个连接请求都要新建一个数据库连接,没有进行池化管理,对服务器性能消耗太大;SingleConnectionDataSource每个连接都会返回同一个连接,无法同时处理多个请求。JdbcTemplate封装了基本的样板式代码像打开数据库连接,关闭连接,返回参数映射。
4、接下来我们新建一个数据库dbtest,新建一个表user。然后新建一个User实体类,UserDAO接口类,UserDAOImpl实现类。
User.java
package com.csii.entity;
public class User {
private int id;
private String username;
private String password;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
// TODO Auto-generated method stub
return "user{id="+getId()+",username="+getUsername()+",password="+getPassword()+"}";
}
}
UserDAO.java
package com.csii.dao;
import java.util.List;
import java.util.Map;
import com.csii.entity.User;
public interface UserDAO {
void addUser(User user);
Map<String,Object> queryByUserName(String username);
User queryById(int id);
List<User> getUserList();
}
UserDAOImpl.java
package com.csii.dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import com.csii.dao.UserDAO;
import com.csii.entity.User;
public class UserDAOImpl implements UserDAO{
public static final String SQL_INSERT="insert into user(username,password) values(?,?)";
public static final String SQL_QUERY_BY_USERNAME="select * from user where username=?";
public static final String SQL_QUERY_ALL_USER="select * from user";
public static final String SQL_QUERY_BY_ID="select * from user where id=?";
private JdbcTemplate jdbcTemplate;
public void addUser(final User user) {
getJdbcTemplate().update(SQL_INSERT, user.getUsername(),user.getPassword());
}
public List<User> getUserList() {
//getJdbcTemplate().query(SQL_QUERY_ALL_USER, null, rowMapper)
return null;
}
public Map<String,Object> queryByUserName(String username) {
Map<String,Object> map=getJdbcTemplate().queryForMap(SQL_QUERY_BY_USERNAME, username);
return map;
}
public User queryById(int id) {
User user=getJdbcTemplate().queryForObject(SQL_QUERY_BY_ID, new Object[]{id},
new RowMapper<User>(){public User mapRow(ResultSet resultset, int i)
throws SQLException {
User user=new User();
user.setId(resultset.getInt("id"));
user.setUsername(resultset.getString("username"));
user.setPassword(resultset.getString("password"));
return user;
}});
return user;
}
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
}
bean.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"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.2.xsd">
<bean id="user" class="com.csii.entity.User"></bean>
</beans>
5、写一个测试类
package com.csii.spring;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.csii.aop.SoccerGame;
import com.csii.aopwithparam.MindReader;
import com.csii.aopwithparam.Thinker;
import com.csii.aopwithparam.Volunteer;
import com.csii.dao.UserDAO;
import com.csii.entity.User;
public class Test {
public static void main(String[] args) throws Exception {
ApplicationContext ctx=
new ClassPathXmlApplicationContext("applicationContext.xml");
UserDAO userDao=(UserDAO)ctx.getBean("userDao");
/* User user=new User();
user.setUsername("李四");
user.setPassword("123456");
userDao.addUser(user);*/
//System.out.println(userDao.queryByUserName("张三"));
System.out.println(userDao.queryById(1));
}
}
输出结果:
user{id=1,username=张三,password=123456}
说明:
UserDAOImpl中我们配置了一个JdbcTemplate的属性,然后通过set方法进行注入;这里假如我们有很多dao类的话,那么将会出现很多重复代码,因此另一种处理方式是写一个通用的父类,注入jdbcTemplet属性,来避免重复代码。Spring提供了一个JdbcDaoSupport来做这个工作。这样UserDAOImpl不需要再申明JdbcTemplate属性,set方法。
package com.csii.dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import com.csii.dao.UserDAO;
import com.csii.entity.User;
public class UserDAOImpl extends JdbcDaoSupport implements UserDAO{
public static final String SQL_INSERT="insert into user(username,password) values(?,?)";
public static final String SQL_QUERY_BY_USERNAME="select * from user where username=?";
public static final String SQL_QUERY_ALL_USER="select * from user";
public static final String SQL_QUERY_BY_ID="select * from user where id=?";
//private JdbcTemplate jdbcTemplate;
public void addUser(final User user) {
getJdbcTemplate().update(SQL_INSERT, user.getUsername(),user.getPassword());
}
public List<User> getUserList() {
//getJdbcTemplate().query(SQL_QUERY_ALL_USER, null, rowMapper)
return null;
}
public Map<String,Object> queryByUserName(String username) {
Map<String,Object> map=getJdbcTemplate().queryForMap(SQL_QUERY_BY_USERNAME, username);
return map;
}
public User queryById(int id) {
User user=getJdbcTemplate().queryForObject(SQL_QUERY_BY_ID, new Object[]{id},
new RowMapper<User>(){public User mapRow(ResultSet resultset, int i)
throws SQLException {
User user=new User();
user.setId(resultset.getInt("id"));
user.setUsername(resultset.getString("username"));
user.setPassword(resultset.getString("password"));
return user;
}});
return user;
}
/*public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
*/
}