首页 > 代码库 > Spring3 JDBC
Spring3 JDBC
Spring3 JDBC
Spring JDBC抽象框架
- 定义数据库连接参数
- 打开数据库连接
- 声明SQL语句
- 预编译并执行SQL语句
- 遍历查询结果(如果需要的话)
- 处理每一次遍历操作
- 处理抛出的任何异常
- 处理事务
- 关闭数据库连接
配置数据源
-
数据库连接
-
数据库连接池
-
数据源
- 由JDBC驱动程序定义的数据源
- 基于连接池的JNDI数据源
- 基于连接池的本地数据源
-
DBCP数据库连接池
-
BasicDataSource
-
initialSize :初始化连接数量,默认值为0
-
maxActive :最大连接数,默认值为8
-
maxIdle:闲置连接的最大数量,默认为8
-
minIdle:闲置连接的最小数量,默认为0
-
maxWait:最大等待时间,以毫秒为单位。
-
minEvictableIdleTimeMillis :闲置连接被逐出连接池的时间期限,以毫秒为单位,默认为30分钟
-
在Spring中使用基于连接池的本地数据源
直接在Spring应用容器内部以bean的形式定义基于连接池
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" scope="singleton"> <property name="driverClassName" value="oracle.jdbc.OracleDriver"/> <property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl" /> <property name="username" value="scott" /> <property name="password" value="tiger" /> <property name="initialSize" value="10"/> <property name="maxActive" value="50"/> <property name="maxIdle" value="10"/> <property name="minIdle" value="5"/> <property name="maxWait" value="10000"/> </bean>
直接在非web应用程序中使用
mian方法中执行下列代码可测试数据源
@Test public void test2() throws Exception { //通过spring容器获取dao EmpDaoJdbc dao = (EmpDaoJdbc) applicationContext.getBean("empDaoJdbc"); //执行查询 List<Emp> list = dao.search(); for (Emp emp : list) { System.out.println(emp.getEmpno()+"\t" +emp.getEname()+"\t" +emp.getDept().getDname()); } }
使用Spring JDBC 模板
使用Spring对JDBC的Dao支持类
个人测试代码
层次图
pom文件
<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/maven-v4_0_0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>cn.yunhe</groupId> <artifactId>spring3</artifactId> <packaging>war</packaging> <version>1.0-SNAPSHOT</version> <name>spring3 Maven Webapp</name> <url>http://maven.apache.org</url> <dependencies> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>3.0.5.RELEASE</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.11</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>3.0.5.RELEASE</version> </dependency> <dependency> <groupId>org.aspectj</groupId> <artifactId>aspectjrt</artifactId> <version>1.6.11</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aspects</artifactId> <version>3.0.5.RELEASE</version> </dependency> <dependency> <groupId>org.aspectj</groupId> <artifactId>aspectjweaver</artifactId> <version>1.6.11</version> </dependency> <dependency> <groupId>cglib</groupId> <artifactId>cglib</artifactId> <version>2.1</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.6</version> </dependency> <dependency> <groupId>commons-dbcp</groupId> <artifactId>commons-dbcp</artifactId> <version>1.4</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>3.0.5.RELEASE</version> </dependency> </dependencies> <build> <finalName>spring3</finalName> </build> </project>
Dao
package cn.yunhe.dao; import cn.yunhe.entity.User; import java.sql.SQLException; import java.util.List; /** * Created by Administrator on 2017/7/14. */ public interface UserDao { List<User> search() throws SQLException; List<User> search(User user); User findById(int uid); void save(User user); void update(User user); void delete(int uid); }
package cn.yunhe.dao; import cn.yunhe.entity.Dept; import cn.yunhe.entity.User; import org.springframework.stereotype.Component; import javax.annotation.Resource; import javax.sql.DataSource; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; /** * Created by Administrator on 2017/7/14. */ public class UserDaoImpl implements UserDao { Connection conn=null; PreparedStatement ps=null; ResultSet rs=null; private DataSource dataSource; public List<User> search() throws SQLException { conn=dataSource.getConnection(); String sql="select u.*,d.* from t_user u inner join t_dept d on u.deptid=d.did"; ps=conn.prepareStatement(sql); rs= ps.executeQuery(); List<User> list=new ArrayList<User>(); boolean flag=rs.next(); System.out.println(flag+"======================="); while (rs.next()){ User user=new User(); user.setUid(rs.getInt("uid")); user.setUname(rs.getString("uname")); user.setUpassword(rs.getString("upassword")); user.setUage(rs.getInt("uage")); Dept dept=new Dept(); dept.setDid(rs.getInt("did")); dept.setDname(rs.getString("dname")); user.setDept(dept); list.add(user); } return list; } public List<User> search(User user) { return null; } public User findById(int uid) { return null; } public void save(User user) { } public void update(User user) { } public void delete(int uid) { } public DataSource getDataSource() { return dataSource; } public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; } }
package cn.yunhe.dao; import cn.yunhe.entity.Dept; import cn.yunhe.entity.User; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.simple.ParameterizedRowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; /** * Created by Administrator on 2017/7/17. *Spring 的 JdbcDaoSupport 就是用于编写基于JDBC 的DAO 类的基类 自己的DAO类继承它即可 *使用JdbcDaoSupport 来获取 JdbcTemplate */ public class UserDaoJdbcSupport extends JdbcDaoSupport implements UserDao{ public static final RowMapper rowMapper=new ParameterizedRowMapper<User>() { public User mapRow(ResultSet rs, int rowNum) throws SQLException { User user=new User(); user.setUid(rs.getInt("uid")); user.setUname(rs.getString("uname")); user.setUpassword(rs.getString("upassword")); user.setUage(rs.getInt("uage")); Dept dept=new Dept(); dept.setDid(rs.getInt("did")); dept.setDname(rs.getString("dname")); user.setDept(dept); return user; } }; public List<User> search() throws SQLException { String sql="select u.*,d.* from t_user u inner join t_dept d on u.deptid=d.did"; List<User> list=this.getJdbcTemplate().query(sql,rowMapper); return list; } public List<User> search(User user) { return null; } public User findById(int uid) { return null; } public void save(User user) { } public void update(User user) { } public void delete(int uid) { } }
package cn.yunhe.dao; import cn.yunhe.entity.User; import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport; import java.sql.SQLException; import java.util.HashMap; import java.util.List; import java.util.Map; /** * Created by Administrator on 2017/7/17. */ public class UserDaoNamedSupport extends NamedParameterJdbcDaoSupport implements UserDao { public List<User> search() throws SQLException { return null; } public List<User> search(User user) { return null; } public User findById(int uid) { return null; } public void save(User user) { String sql = "insert into t_user (uname,upassword,uage,deptid) values(:uname,:upassword,:uage,:deptid)"; Map params = new HashMap(); params.put("uname", user.getUname()); params.put("upassword",user.getUpassword()); params.put("uage",user.getUage()); params.put("deptid",user.getDept().getDid()); int flag= getNamedParameterJdbcTemplate().update(sql,params); System.out.println("================="+flag); } public void update(User user) { } public void delete(int uid) { } }
package cn.yunhe.dao; import cn.yunhe.entity.Dept; import cn.yunhe.entity.User; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; import org.springframework.jdbc.core.simple.ParameterizedRowMapper; import org.springframework.stereotype.Component; import javax.annotation.Resource; import java.sql.ResultSet; import java.sql.SQLException; import java.util.HashMap; import java.util.List; import java.util.Map; /** * Created by Administrator on 2017/7/14. */ @Component public class UserDaoNamedTemplate implements UserDao { @Resource private NamedParameterJdbcTemplate namedParameterJdbcTemplate; public static final RowMapper rowMapper =new ParameterizedRowMapper() { public Object mapRow(ResultSet rs, int rowNum) throws SQLException { User user=new User(); user.setUid(rs.getInt("uid")); user.setUname(rs.getString("uname")); user.setUpassword(rs.getString("upassword")); user.setUage(rs.getInt("uage")); Dept dept=new Dept(); dept.setDid(rs.getInt("did")); dept.setDname(rs.getString("dname")); user.setDept(dept); return user; } }; public List<User> search() throws SQLException { return null; } public List<User> search(User user) { String sql="select u.*,d.* from t_user u inner join t_dept d on u.deptid=d.did where u.uname like :uname"; Map params=new HashMap(); params.put("uname","%"+user.getUname()+"%"); List<User> list= namedParameterJdbcTemplate.query(sql,params,rowMapper); return list; } public User findById(int uid) { return null; } public void save(User user) { } public void update(User user) { } public void delete(int uid) { } public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() { return namedParameterJdbcTemplate; } public void setNamedParameterJdbcTemplate(NamedParameterJdbcTemplate namedParameterJdbcTemplate) { this.namedParameterJdbcTemplate = namedParameterJdbcTemplate; } }
package cn.yunhe.dao; import cn.yunhe.entity.Dept; import cn.yunhe.entity.User; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.simple.ParameterizedRowMapper; import org.springframework.stereotype.Component; import javax.annotation.Resource; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; /** * Created by Administrator on 2017/7/14. */ @Component public class UserDaoTemplate implements UserDao{ @Resource private JdbcTemplate jdbcTemplate; public static final RowMapper rowMapper= new ParameterizedRowMapper<User>() { public User mapRow(ResultSet rs, int rowNum) throws SQLException { User user=new User(); user.setUid(rs.getInt("uid")); user.setUname(rs.getString("uname")); user.setUpassword(rs.getString("upassword")); user.setUage(rs.getInt("uage")); Dept dept=new Dept(); dept.setDid(rs.getInt("did")); dept.setDname(rs.getString("dname")); user.setDept(dept); return user; } }; public List<User> search() throws SQLException { String sql="select u.*,d.* from t_user u inner join t_dept d on u.deptid=d.did"; List<User> list= jdbcTemplate.query(sql,rowMapper); return list; } public List<User> search(User user) { return null; } public User findById(int uid) { return null; } public void save(User user) { } public void update(User user) { } public void delete(int uid) { } public JdbcTemplate getJdbcTemplate() { return jdbcTemplate; } public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } }
Entity
package cn.yunhe.entity; /** * Created by Administrator on 2017/7/14. */ public class User { private int uid; private String uname; private String upassword; private int uage; private Dept dept; public int getUid() { return uid; } public void setUid(int uid) { this.uid = uid; } public String getUname() { return uname; } public void setUname(String uname) { this.uname = uname; } public String getUpassword() { return upassword; } public void setUpassword(String upassword) { this.upassword = upassword; } public int getUage() { return uage; } public void setUage(int uage) { this.uage = uage; } public Dept getDept() { return dept; } public void setDept(Dept dept) { this.dept = dept; } }
package cn.yunhe.entity; /** * Created by Administrator on 2017/7/14. */ public class Dept { private int did; private String dname; public int getDid() { return did; } public void setDid(int did) { this.did = did; } public String getDname() { return dname; } public void setDname(String dname) { this.dname = dname; } }
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:p="http://www.springframework.org/schema/p" xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd"> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" scope="singleton" > <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/spring"/> <property name="username" value="root"/> <property name="password" value="1234"/> <!--初始化连接数--> <property name="initialSize" value="5"/> <!--最大连接数--> <property name="maxActive" value="200"/> <!--最大闲置连接数--> <property name="maxIdle" value="10"/> <!--最小闲置连接数--> <property name="minIdle" value="1"/> <!--最大等待时间--> <property name="maxWait" value="30000"/> </bean> <!--自动扫描--> <context:component-scan base-package="cn.yunhe"/> <bean id="userDaoImpl" class="cn.yunhe.dao.UserDaoImpl"> <property name="dataSource" ref="dataSource"/> </bean> <!--使用jdbcTemplate--> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"/> </bean> <!--使用NamedParameterJdbcTemplate--> <bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate"> <constructor-arg ref="dataSource"/> </bean> <!--使用JdbcDaoSupport:使用父类获得jdbcTemplate 这样就不用在每个实体类中创建jdbcTemplate属性了 --> <bean id="userDaoJdbcSupport" class="cn.yunhe.dao.UserDaoJdbcSupport"> <!--其实是将jdbcTempate注入到了JdbcDaoSupport中--> <property name="jdbcTemplate" ref="jdbcTemplate"/> </bean> <!--使用NamedParameterJdbcDaoSupport: 及使用父类 ,有使用命名传参的方式--> <bean id="userDaoNamedSupport" class="cn.yunhe.dao.UserDaoNamedSupport"> <!--其实是将jdbcTemplate注入到了NamedParameterJdbcDaoSupport中去了--> <property name="jdbcTemplate" ref="jdbcTemplate"/> </bean> </beans>
Demo
package cn.yunhe.test; import cn.yunhe.dao.*; import cn.yunhe.entity.Dept; import cn.yunhe.entity.User; import org.junit.Before; import org.junit.Test; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import java.sql.SQLException; import java.util.List; /** * Created by Administrator on 2017/7/14. */ public class Demo { ApplicationContext ac=null; @Before public void init(){ ac=new ClassPathXmlApplicationContext("spring.xml"); } @Test//普通方式查询 public void test1() throws SQLException { UserDaoImpl userDao= (UserDaoImpl) ac.getBean("userDaoImpl"); List<User> list= userDao.search(); for (User user:list){ System.out.println(user.getUname()+"\t"+user.getDept().getDname()); } } @Test//jdbcTemplate方式查询 public void test2() throws SQLException { UserDaoTemplate template= (UserDaoTemplate) ac.getBean("userDaoTemplate"); List<User> list=template.search(); for (User user:list){ System.out.println(user.getUname()+"\t"+user.getDept().getDname()); } } @Test//namedParameterJdbcTemplate public void test3(){ UserDaoNamedTemplate userDaoNamedTemplate= (UserDaoNamedTemplate) ac.getBean("userDaoNamedTemplate"); User user=new User(); user.setUname("飞"); List<User> list=userDaoNamedTemplate.search(user); for (User u:list){ System.out.println(u.getUname()+"\t"+u.getDept().getDname()); } } @Test //使用jdbcDaoSupport public void test4() throws SQLException { UserDaoJdbcSupport userDaoJdbcSupport= (UserDaoJdbcSupport) ac.getBean("userDaoJdbcSupport"); List<User> list=userDaoJdbcSupport.search(); for (User user:list){ System.out.println(user.getUname()+"\t"+user.getDept().getDname()); } } @Test//使用NamedParameterJdbcDaoSupport方式 public void test5(){ UserDaoNamedSupport userDaoNamedSupport= (UserDaoNamedSupport) ac.getBean("userDaoNamedSupport"); User user=new User(); user.setUname("诸葛亮"); user.setUpassword("1234"); user.setUage(123); Dept dept =new Dept(); dept.setDid(3); user.setDept(dept); userDaoNamedSupport.save(user); } }
Spring3 JDBC
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。