Java Spring框架中JdbcTemplate的CURD操作

1、创建数据库spring，数据表tb_user

spring.sql

--
-- Database: `spring`
--
CREATE DATABASE IF NOT EXISTS `spring` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `spring`;

CREATE TABLE IF NOT EXISTS `tb_user` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `loginName` varchar(20) DEFAULT NULL,
  `loginPwd` varchar(20) DEFAULT NULL,
  `age` int(5) DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

2、新建web项目，项目名JdbcTemplateTest

3、pom.xml添加依赖，保存

    <dependency>
	    <groupId>junit</groupId>
	    <artifactId>junit</artifactId>
	    <version>3.8.1</version>
	    <scope>test</scope>
    </dependency>
    
    <dependency>
	    <groupId>org.springframework</groupId>
	    <artifactId>spring-core</artifactId>
	    <version>4.3.8.RELEASE</version>
	</dependency>
	
	<dependency>
	    <groupId>org.springframework</groupId>
	    <artifactId>spring-context</artifactId>
	    <version>4.3.8.RELEASE</version>
	</dependency>
    
    <dependency>
	    <groupId>org.springframework</groupId>
	    <artifactId>spring-jdbc</artifactId>
	    <version>4.3.8.RELEASE</version>
    </dependency>
    
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.21</version>
    </dependency>
    
    <dependency>
	    <groupId>com.alibaba</groupId>
	    <artifactId>druid</artifactId>
	    <version>1.0.12</version>
    </dependency>

4、右键src/main/java，依次新建UserBean实体，UserDao接口，实现UserDao接口的类UserDaoImpl.java（注意包名top.k10000）

UserBean.java

package top.k10000;

public class UserBean {
	private int id;
	private String loginName;
	private String loginPwd;
	private int age;
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getLoginName() {
		return loginName;
	}
	public void setLoginName(String loginName) {
		this.loginName = loginName;
	}
	public String getLoginPwd() {
		return loginPwd;
	}
	public void setLoginPwd(String loginPwd) {
		this.loginPwd = loginPwd;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	
}

UserDao.java

package top.k10000;
import java.util.List;

public interface UserDao {
	public boolean addUser(UserBean user);
	public boolean delUser(int id);
	public boolean updateUser(UserBean user);
	public List<UserBean> allUser();
}

UserDaoImpl.java

package top.k10000;
import java.util.List;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

public class UserDaoImpl implements UserDao {

	private JdbcTemplate template;
	
	@Override
	public boolean delUser(int id) {
		String sql = "delete from tb_user where id=?";
		int count  = template.update(sql,new Object[]{id});
		return count !=0? true:false;
	}

	@Override
	public boolean addUser(UserBean user) {
		String sql = "insert into tb_user(loginName,loginPwd,age) values(?,?,?)";
		int count = template.update(sql,new Object[]{user.getLoginName(),user.getLoginPwd(),user.getAge()});
		return count !=0? true:false;
	}

	@Override
	public boolean updateUser(UserBean user) {
		String sql = "update tb_user set loginName=?,loginPwd=?,age=? where id=?";
		int count = template.update(sql,new Object[]{user.getLoginName(),user.getLoginPwd(),user.getAge(),user.getId()});
		return count !=0? true:false;
	}

	@Override
	public List<UserBean> allUser() {
		String sql = "select * from tb_user";
		return template.query(sql,new BeanPropertyRowMapper<UserBean>(UserBean.class));
	}

	public JdbcTemplate getTemplate() {
		return template;
	}

	public void setTemplate(JdbcTemplate template) {
		this.template = template;
	}

}

5、创建applicationContext.xml，右键项目，Myeclipse>Add Spring Capabilities…（注意Spring Core不打勾，只添加applicationContext.xml）

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:p="http://www.springframework.org/schema/p"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd">

	<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
		<property name="url" value="jdbc:mysql://127.0.0.1:3306/spring?useUnicode=true&characterEncoding=utf-8"/>
		<property name="username" value="root"/>
		<property name="password" value=""/>
		
		<property name="initialSize" value="3"/>
		<property name="minIdle" value="3"/>
		<property name="maxActive" value="20"/>
		<property name="maxWait" value="60000"/>
		
		<property name="timeBetweenEvictionRunsMillis" value="300000"/>
		
		<property name="validationQuery" value="select 'x'"/>
		<property name="testWhileIdle" value="true" />
		<property name="testOnBorrow" value="false" />
		<property name="testOnReturn" value="false" />
		<property name="poolPreparedStatements" value="true"/>
		<property name="maxPoolPreparedStatementPerConnectionSize" value="20"/>
		
		<property name="filters" value="stat"/>
	</bean>
	
	<!-- 使用jdbcTemplate -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">  
          <property name="dataSource">
            <ref local="dataSource" />  
          </property>  
        </bean> 
		
	<!-- userDaoImpl -->
	<bean id="userDaoImpl" class="top.k10000.UserDaoImpl">
	  <property name="template" ref="jdbcTemplate"/>
	</bean>
</beans>

6、新建Test.java实现增删改查操作

package top.k10000;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class Test {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		ApplicationContext f = new ClassPathXmlApplicationContext("applicationContext.xml");
		UserDaoImpl s = (UserDaoImpl)f.getBean("userDaoImpl");
		
//		----delete----
//		s.delUser(6);
		
//		----insert----
//		UserBean bean = new UserBean();
//		bean.setLoginName("k10001");
//		bean.setLoginPwd("123456");
//		bean.setAge(100);
//		s.addUser(bean);
//		System.out.println("----插入成功----");
		
//		----query----
		List<UserBean> list = s.allUser();
		for(UserBean u:list){
			System.out.println(u.getLoginName()+"\t"+u.getLoginPwd());
		}
		
//		----update----
//		UserBean bean = new UserBean();
//		bean.setLoginName("暮歌");
//		bean.setLoginPwd("K9999");
//		bean.setAge(6);
//		bean.setId(7);
//		s.updateUser(bean);
//		System.out.println("----更新成功----");
		
		
	}

}

7、运行Test.java，效果截图：