JdbcTemplate增删改查
1.项目目录
2.Emp.java
package com.atguigu.jdbctemplate;
public class Emp {
private Integer eid;
private String ename;
private Integer age;
private String sex;
public Integer getEid() {
return eid;
}
public void setEid(Integer eid) {
this.eid = eid;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
@Override
public String toString() {
return "Emp [eid=" + eid + ", ename=" + ename + ", age=" + age + ", sex=" + sex + "]";
}
}
3.ssm表
4.db.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/ssm
jdbc.username=root
jdbc.password=root
5.jdbc.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: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/context http://www.springframework.org/schema/context/spring-context-4.0.xsd">
<!-- 引入属性文件 -->
<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location" value="db.properties"></property>
</bean>
<!-- 引入属性文件 -->
<context:property-placeholder location="db.properties"/>
<!-- 创建数据源 -->
<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>
<!-- 通过数据源配置JdbcTemplate -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
6.TestJdbcTemplate.java
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);
@Test
public void test() {
//单个增删改
//System.out.println(jdbcTemplate);//org.springframework.jdbc.core.JdbcTemplate@6483f5ae
//jdbcTemplate.update("insert into emp values(1,'zhangsan',23,'男')");
//增加记录
//String sql = "insert into emp values(2, ?, ?, ?)";
//jdbcTemplate.update(sql, "lisi", 25, "男");
//删除记录
//String sql = "delete from emp where eid=?";
//jdbcTemplate.update(sql,2);
//修改记录
//String sql = "update emp set ename=? where eid=?";
//jdbcTemplate.update(sql, "lisi", 1);
//不能使用
//String sql = "delete from emp where eid in (?)";
//String eids = "3, 4, 5";
//jdbcTemplate.update(sql,eids);
//可以使用
//String eids = "3,4,5";
//String sql = "delete from emp where eid in ("+eids+")";
//jdbcTemplate.update(sql);
//String sql = "select * from emp where ename like '%?%'";//不能使用
//String sql = "select * from emp where ename like concat('%',?,'%')";//可以使用
}
@Test
public void testBatchUpdate(){
//批量增删改
// String sql = "insert into emp values(?,?,?,?)";
// List<Object[]> list = new ArrayList<>();
// list.add(new Object[]{3,"a1",1,"男"});
// list.add(new Object[]{4,"a2",2,"男"});
// list.add(new Object[]{5,"a3",3,"男"});
// jdbcTemplate.batchUpdate(sql, list);
}
@Test
public void testQueryForObject(){
//jdbcTemplate.queryForObject(sql, requiredType);//用来获取单个的值
//jdbcTemplate.queryForObject(sql, rowMapper);//用来获取单条数据
// String sql= "select eid,ename,age,sex from emp where eid=?";
// 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 emp";
Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
System.out.println(count);
}
@Test
public void testQuery(){
String sql = "select eid,ename,age,sex from emp";
RowMapper<Emp> rowMapper = new BeanPropertyRowMapper<>(Emp.class);
List<Emp> list = jdbcTemplate.query(sql, rowMapper);
for(Emp emp:list){
System.out.println(emp);
}
}
}
