Spring JDBC不用以及使用 JdbcTemplate、JdbcDaoSupport


声明:本文转载自https://my.oschina.net/u/2245781/blog/1552110,转载目的在于传递更多信息,仅供学习交流之用。如有侵权行为,请联系我,我会及时删除。

一、表(这里用mysql,数据库名为yiibai)

CREATE TABLE `customer` (   `CUST_ID` int(10) UNSIGNED NOT NULL,   `NAME` varchar(100) NOT NULL,   `AGE` int(10) UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE `customer`   ADD PRIMARY KEY (`CUST_ID`);

二、不用JdbcTemplate的情况

表的映射类

package com.yiibai.springjdbc.bean;  public class Customer {  	int custId; 	String name; 	int age; 	 	public Customer(int custId, String name, int age) { 		super(); 		this.custId = custId; 		this.name = name; 		this.age = age; 	} 	public int getCustId() { 		return custId; 	} 	public void setCustId(int custId) { 		this.custId = custId; 	} 	public String getName() { 		return name; 	} 	public void setName(String name) { 		this.name = name; 	} 	public int getAge() { 		return age; 	} 	public void setAge(int age) { 		this.age = age; 	} 	@Override 	public String toString() { 		return "Customer [custId=" + custId + ", name=" + name + ", age=" + age + "]"; 	} } 

DAO接口

package com.yiibai.springjdbc.dao;  import java.util.List; import com.yiibai.springjdbc.bean.Customer;  public interface CustomerDAO { 	public void insert(Customer customer); 	public Customer findByCustomerId(int custId); 	public List<Customer> queryCustomer() throws Exception ; } 

DAO实现(不用JdbcTemplate)

package com.yiibai.springjdbc.daoimpl;  import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List;  import javax.sql.DataSource;  import com.yiibai.springjdbc.bean.Customer; import com.yiibai.springjdbc.dao.CustomerDAO;  public class CustomerImplDAO implements CustomerDAO { 	private DataSource dataSource;  	@Override 	public void insert(Customer customer) { 		// TODO 自动生成的方法存根 		String sql = "INSERT INTO customer " + "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)"; 		Connection conn = null;  		try { 			conn = dataSource.getConnection(); 			PreparedStatement ps = conn.prepareStatement(sql); 			ps.setInt(1, customer.getCustId()); 			ps.setString(2, customer.getName()); 			ps.setInt(3, customer.getAge()); 			ps.executeUpdate(); 			ps.close();  		} catch (SQLException e) { 			throw new RuntimeException(e);  		} finally { 			if (conn != null) { 				try { 					conn.close(); 				} catch (SQLException e) { 				} 			} 		} 	}  	@Override 	public Customer findByCustomerId(int custId) { 		// TODO 自动生成的方法存根 		String sql = "SELECT * FROM customer WHERE CUST_ID = ?"; 		Connection conn = null;  		try { 			conn = dataSource.getConnection(); 			PreparedStatement ps = conn.prepareStatement(sql); 			ps.setInt(1, custId); 			Customer customer = null; 			ResultSet rs = ps.executeQuery(); 			if (rs.next()) { 				customer = new Customer(rs.getInt("CUST_ID"), rs.getString("NAME"), rs.getInt("Age")); 			} 			rs.close(); 			ps.close(); 			return customer; 		} catch (SQLException e) { 			throw new RuntimeException(e); 		} finally { 			if (conn != null) { 				try { 					conn.close(); 				} catch (SQLException e) { 				} 			} 		} 	}  	public void setDataSource(DataSource dataSource) { 		this.dataSource = dataSource; 	}  	@Override 	public List<Customer> queryCustomer() throws Exception { 		// TODO 自动生成的方法存根 		Connection conn = dataSource.getConnection(); 		String sql = "Select c.CUST_ID, c.NAME, c.AGE from customer c"; 		System.out.println(sql); 		Statement smt = conn.createStatement(); 		ResultSet rs = smt.executeQuery(sql); 		List<Customer> list = new ArrayList<Customer>(); 	       while (rs.next()) { 	           int cID = rs.getInt("CUST_ID"); 	           String cName = rs.getString("NAME"); 	           int cAge = rs.getInt("AGE"); 	           Customer cust = new Customer(cID, cName, cAge); 	           list.add(cust); 	       } 	        		return list; 	}  } 

配置文件spring-dao.xml  spring-datasource.xml  spring-module.xml都放置在(特别重要)包com.yiibai.springjdbc下面:

spring-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"    xsi:schemaLocation="http://www.springframework.org/schema/beans    http://www.springframework.org/schema/beans/spring-beans.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/yiibai?useSSL=false" />        <property name="username" value="your-user" />        <property name="password" value="your-passwd" />    </bean>     </beans>

这里需要修改用户密码来适应你的数据库环境

spring-dao.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" 	xsi:schemaLocation="http://www.springframework.org/schema/beans    http://www.springframework.org/schema/beans/spring-beans.xsd">  	<bean id="customerDAO" class="com.yiibai.springjdbc.daoimpl.CustomerImplDAO"> 		<property name="dataSource" ref="dataSource" /> 	</bean>  </beans>

spring-module.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"    xsi:schemaLocation="http://www.springframework.org/schema/beans    http://www.springframework.org/schema/beans/spring-beans.xsd">      <!-- Using Mysql datasource -->    <import resource="spring-datasource.xml" />    <import resource="spring-dao.xml" />   </beans>

测试(主)类

package com.yiibai.springjdbc;  import java.util.List;  import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext;  import com.yiibai.springjdbc.bean.Customer; import com.yiibai.springjdbc.dao.CustomerDAO;  public class CustTest {  	private static ApplicationContext ctx;  	public static void main(String[] args) throws Exception { 		ctx = new ClassPathXmlApplicationContext("com/yiibai/springjdbc/spring-module.xml");         	              CustomerDAO customerDAO = (CustomerDAO) ctx.getBean("customerDAO");             Customer customer = new Customer(1, "yiibai",29);             customerDAO.insert(customer);          	             Customer customer1 = customerDAO.findByCustomerId(1);             System.out.println(customer1);                          List<Customer> custList = customerDAO.queryCustomer();             for(Customer cs : custList){                 System.out.println("Customer ID " + cs.getCustId());                 System.out.println("Customer Name " + cs.getName());                 System.out.println("Customer Age" + cs.getAge());                 System.out.println("----------------------------");             } 	}  } 

运行结果:表customer加了一条记录,并输出如下信息:

(执行前把表customer中id为1的记录删除,不然插入异常)

三、使用 JdbcTemplate、JdbcDaoSupport实现

Customer和DAO接口不变,主要变化是DAO实现:CustomerImplDAO类改为JdbcCustomerDAO

package com.yiibai.springjdbc.daoimpl;  import java.util.List;  import org.springframework.jdbc.core.support.JdbcDaoSupport;  import com.yiibai.springjdbc.bean.Customer; import com.yiibai.springjdbc.bean.CustomerRowMapper; import com.yiibai.springjdbc.dao.CustomerDAO;  public class JdbcCustomerDAO extends JdbcDaoSupport implements CustomerDAO {  	@Override 	public void insert(Customer customer) { 		// TODO 自动生成的方法存根 		String sql = "INSERT INTO customer " + 				"(CUST_ID, NAME, AGE) VALUES (?, ?, ?)"; 					  			getJdbcTemplate().update(sql, new Object[] { customer.getCustId(), 					customer.getName(),customer.getAge()   			}); 	}  	@Override 	public Customer findByCustomerId(int custId) { 		// TODO 自动生成的方法存根 		/* 		 * 	这种写法也可以	  		String sql = "SELECT * FROM customer WHERE CUST_ID =  '"+custId+"' "; 		return getJdbcTemplate().queryForObject(sql,new CustomerRowMapper()); 		*/ 		String sql = "SELECT * FROM customer WHERE CUST_ID = ?"; 		return getJdbcTemplate().queryForObject(sql,new Object[] { custId },new CustomerRowMapper()); 	}  	@Override 	public List<Customer> queryCustomer() throws Exception { 		// TODO 自动生成的方法存根 		String sql = "SELECT * FROM customer";  		return getJdbcTemplate().query(sql, new CustomerRowMapper()); 	}  } 

需要说明2点:

1、本实现继承JdbcDaoSupport,而 JdbcDaoSupport定义了 JdbcTemplate和DataSource 属性,只需在配置文件中注入DataSource 即可,然后会创建jdbcTemplate的实例,不必像前面的实现CustomerImplDAO那样,需要显式定义一个DataSource成员变量。

2、这里出现了CustomerRowMapper类:本来应该这样写的queryForObject(sql,Customer.class);但Spring并不知道如何将结果转成Customer.class。所以需要写一个CustomerRowMapper 继承RowMapper接口 ,其代码如下:

package com.yiibai.springjdbc.bean;  import java.sql.ResultSet; import java.sql.SQLException;  import org.springframework.jdbc.core.RowMapper;  public class CustomerRowMapper implements RowMapper<Customer> {  	@Override 	public Customer mapRow(ResultSet rs, int rowNum) throws SQLException { 		// TODO 自动生成的方法存根 		return new Customer(rs.getInt("CUST_ID"),rs.getString("NAME"),rs.getInt("AGE")); 	}  }

文件spring-dao.xml里bean的定义修改为(变化的是class):

    <bean id="customerDAO" class="com.yiibai.springjdbc.daoimpl.JdbcCustomerDAO">         <property name="dataSource" ref="dataSource" />     </bean>

其他配置文件和主类都不变、运行结果少了Select c.CUST_ID, c.NAME, c.AGE from customer c
,这是因为CustomerImplDAO版本人为地插入一句 System.out.println(sql);以示和JDBC模板实现版本JdbcCustomerDAO的区别。
可以看出采用JDBC模板大大简化代码。

参考:

Spring JdbcTemplate+JdbcDaoSupport实例

Spring与Dao-Jdbc模板实现增删改查

使用Jdbc Template的基本操作步骤

Spring进行面向切面编程的一个简单例子

 

 

 

 

本文发表于2017年10月18日 00:34
(c)注:本文转载自https://my.oschina.net/u/2245781/blog/1552110,转载目的在于传递更多信息,并不代表本网赞同其观点和对其真实性负责。如有侵权行为,请联系我们,我们会及时删除.

阅读 1606 讨论 0 喜欢 0

抢先体验

扫码体验
趣味小程序
文字表情生成器

闪念胶囊

你要过得好哇,这样我才能恨你啊,你要是过得不好,我都不知道该恨你还是拥抱你啊。

直抵黄龙府,与诸君痛饮尔。

那时陪伴我的人啊,你们如今在何方。

不出意外的话,我们再也不会见了,祝你前程似锦。

这世界真好,吃野东西也要留出这条命来看看

快捷链接
网站地图
提交友链
Copyright © 2016 - 2021 Cion.
All Rights Reserved.
京ICP备2021004668号-1