JdbcTemplate常见的增删改查操作

文件结构与Jar包

员工类Emp

package com.atguigu.jdbctemplate;

public class Emp {
	private Integer aid;

	private String account;

	private String password;

	public Integer getAid() {
		return aid;
	}

	public void setAid(Integer aid) {
		this.aid = aid;
	}

	public String getAccount() {
		return account;
	}

	public void setAccount(String account) {
		this.account = account;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	@Override
	public String toString() {
		return "Emp [aid=" + aid + ", account=" + account + ", password="
				+ password + "]";
	}

}

TestJdbcTemplate测试类：

创建过程

内容

package com.atguigu.jdbctemplate;

import static org.junit.Assert.*;

import java.util.ArrayList;
import java.util.List;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

public class TestJdbcTemplate {

	ApplicationContext ac = new ClassPathXmlApplicationContext("jdbc.xml");
	JdbcTemplate jdbcTemplate = ac.getBean("jdbcTemplate", JdbcTemplate.class);

	// testUpdate实现增删改
	@Test
	public void testUpdate() {
//		
		/*
		 * //update能够进行增删改
		 * jdbcTemplate.update("insert into admin values(3,'admin03','123456')"
		 * );
		 */

		/*
		 * //长度可变的参数列表 jdbcTemplate.update(sql, args) String
		 * sql="insert into admin values(?,?,?)";
		 * jdbcTemplate.update(sql,4,"admin04","123456");
		 */

		/*
		// 批量删除批量修改不要用通配符 只能对第一个进行操作 所以采用拼接的方式
		String aids = "5,6,7";
		String sql = "delete from admin where aid in (" + aids + ")";
		jdbcTemplate.update(sql);
		*/
		/*
		String mohu="a";
//		String sqls="select * from admin where account like '%?%'"; 错误用法
		
		//可以使用
		String sqls="select * from admin where account like concat('%',?,'%')";
	    jdbcTemplate.update(sqls,'a');
	    */
	}

//	 testBatchUpdate实现批量增删改
	@Test
	public void testBatchUpdate() {
		/*
		String sql = "insert into admin values(?,?,?)";
		List<Object[]> list = new ArrayList<>();
		list.add(new Object[] { 5, "admin05", "123456" });
		list.add(new Object[] { 6, "admin06", "123456" });
		list.add(new Object[] { 7, "admin07", "123456" });
		jdbcTemplate.batchUpdate(sql, list);
		*/
	}
	@Test
    public void testQueryForObject(){
//    	jdbcTemplate.queryForObject(sql, requiredType)  //用来获取单个的值
 //   	jdbcTemplate.queryForObject(sql, rowMapper)  用来获取单条数据
    /*
		String sql="select aid,account from admin where aid= ?";
    	//将列名（字段名或字段名的别名）与属性名自动映射
    	RowMapper<Emp> rowMapper =new BeanPropertyRowMapper<>(Emp.class);
    	Emp emp=jdbcTemplate.queryForObject(sql,new Object[]{3} , rowMapper);
    	System.out.println(emp);
    	*/
		
		/*
		String sql="select count(*) from admin";
		Integer count= jdbcTemplate.queryForObject(sql, Integer.class);
		System.out.println(count);
		*/
    }
	
	@Test
	public void testQuery(){
		String sql ="select aid,account,password from admin";
		RowMapper<Emp> rowMapper=new BeanPropertyRowMapper<>(Emp.class);
		List<Emp> list=jdbcTemplate.query(sql, rowMapper);
	    for (Emp emp:list){
	    	System.out.println(emp);
	    }
	}
}

 数据库属性文件

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/eshop
jdbc.username=root
jdbc.password=360421

xml配置文件jdbc.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:aop="http://www.springframework.org/schema/aop" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:p="http://www.springframework.org/schema/p" xmlns:util="http://www.springframework.org/schema/util"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd
 http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd">

	<!-- 加载属性文件 方法一 -->
	<!-- <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> 
		<property name="location" value="db.properties"></property> </bean> -->
	<!-- 加载属性文件 方法二 -->
	<context:property-placeholder location="db.properties" />

	<!-- 通过druid创建一个数据源 -->
	<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
		<property name="driverClassName" value="${jdbc.driver}"></property>
		<property name="url" value="${jdbc.url}"></property>
		<property name="username" value="${jdbc.username}"></property>
		<property name="password" value="${jdbc.password}"></property>
	</bean>
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>

</beans>