首先引入响应的包
spring-core
spring-beans
spring-context
spring-aop
spring-jdbc
spring-tx
mysql-connector-java
然后配置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"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
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.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd">
<!--配置数据库-->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/selection_course?useUnicode=true&characterEncoding=utf-8&useSSL=false"></property>
<property name="username" value="root"></property>
<property name="password" value="root"></property>
</bean>
<!--配置bean-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
demo
package com.shunli.jdbctemplate;
import com.shunli.entity.Student;
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;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public class JdbcTemplateDemo1 {
private JdbcTemplate jdbcTemplate;
{
//获取JdbcTemplate 对象
ApplicationContext applicationContext =new ClassPathXmlApplicationContext("spring.xml");
jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
}
@org.junit.Test
public void demo0(){
//execute一般执行ddl
jdbcTemplate.execute("create table user1 (id int , name char(20))");
}
@org.junit.Test
public void demo1(){
String sql = "INSERT INTO student(student_id, student_name, sex) values (?, ?, ?)";
//update函数可以执行insert update delete操作
//第一种
jdbcTemplate.update(sql, new Object[]{"1000000032","张三","男"});
//第二种
jdbcTemplate.update(sql, "1000000031","李四","男");
}
@org.junit.Test
public void demo2(){
//批量执行
//第一种
String[] sqls = {
"INSERT INTO student(student_id, student_name, sex) values (1000000011, '张飞', '男')",
"INSERT INTO student(student_id, student_name, sex) values (1000000012, '关羽', '男')",
"INSERT INTO student(student_id, student_name, sex) values (1000000013, '刘备', '男')",
};
jdbcTemplate.batchUpdate(sqls);
//第二种
String sql = "INSERT INTO student(student_id, student_name, sex) values (?, ?, ?)";
List<Object[]> list = new ArrayList<Object[]>();
list.add(new Object[]{1000000021, "曹操", "男"});
list.add(new Object[]{1000000022, "许褚", "男"});
list.add(new Object[]{1000000023, "典韦", "男"});
jdbcTemplate.batchUpdate(sql, list);
}
@Test
public void demo3(){
//简单查询
//1.查询一个返回值
String sql = "select count(*) from student";
Integer integer = jdbcTemplate.queryForObject(sql,Integer.class);
System.out.println(integer);
sql = "select student_id from student where student_name = ? and sex = ?";
String student_id = jdbcTemplate.queryForObject(sql, new Object[]{"张飞", "男"}, String.class);
System.out.println(student_id);
sql = "select student_id from student where student_name = ? and sex = ?";
student_id = jdbcTemplate.queryForObject(sql, String.class, "张飞","男");
System.out.println(student_id);
//2.查询多条
sql = "select student_id from student where sex = ?";
List<String> list = jdbcTemplate.queryForList(sql, String.class, "男");
System.out.println(list);
}
@Test
public void demo4(){
//复杂查询(封装Map)
//获取一个
String sql = "select * from student where student_id = ? ";
Map<String,Object> stu = jdbcTemplate.queryForMap(sql,1000000000);
System.out.println(stu);
sql = "select * from student where sex = ?";
List<Map<String,Object>> stus = jdbcTemplate.queryForList(sql, "男");
System.out.println(stus);
}
@Test
public void demo5(){
//实体类查询
String sql = "select * from student where student_id = ?";
Student student = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Student.class), 1000000001);
System.out.println(student);
}
@Test
public void demo6(){
//实体类查询
String sql = "select * from student where student_id = ?";
final Student student = jdbcTemplate.queryForObject(sql, new RowMapper<Student>() {
@Override
public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
Student student1 = new Student();
student1.setStudent_id(rs.getInt("student_id"));
student1.setStudent_name(rs.getString("student_name"));
student1.setSex(rs.getString("sex"));
student1.setBirth(rs.getDate("birth"));
return student1;
}
}, 1000000001);
System.out.println(student);
}
@Test
public void demo7(){
//实体类查询
String sql = "select * from student where student_id = ?";
final Student student = jdbcTemplate.queryForObject(sql, new StudentRowMapper(), 1000000001);
System.out.println(student);
}
@Test
public void demo8(){
String sql = "select * from student";
List<Student> stus = jdbcTemplate.query(sql,new StudentRowMapper());
System.out.println(stus);
}
private class StudentRowMapper implements RowMapper<Student>{
@Override
public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
Student student1 = new Student();
student1.setStudent_id(rs.getInt("student_id"));
student1.setStudent_name(rs.getString("student_name"));
student1.setSex(rs.getString("sex"));
student1.setBirth(rs.getDate("birth"));
return student1;
}
}
}
封装的实体类
package com.shunli.entity;
import java.util.Date;
public class Student {
private int student_id;
private String student_name;
private String sex;
private Date birth;
public int getStudent_id() {
return student_id;
}
public void setStudent_id(int student_id) {
this.student_id = student_id;
}
public String getStudent_name() {
return student_name;
}
public void setStudent_name(String student_name) {
this.student_name = student_name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
@Override
public String toString() {
return "Student{" +
"student_id=" + student_id +
", student_name='" + student_name + '\'' +
", sex='" + sex + '\'' +
", birth=" + birth +
'}';
}
}