携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第12天，点击查看活动详情
哈喽，大家好！我是Why，一名在读学生，目前刚刚开始进入自己的编程学习生涯。虽然学习起步较晚，但我坚信做了才有0或1的可能。学了一段时间以后也是选择在掘金上分享自己的日常笔记，也希望能够在众多道友的大家庭中打成一片。 本文主要讲解Spring整合JDBC，如果大家读后觉得有用的话，还请大家多多支持博主：欢迎 ❤️点赞👍、收藏⭐、留言💬 ✨✨✨个人主页：JinHuan
Spring整合JDBC
使用spring-jdbc操作数据库
配置pom文件
<dependencies
<!--JDBC依赖-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.13.RELEASE</version>
</dependency>
<!--注意数据库版本-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
<!--配置数据源-->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>5.2.13.RELEASE</version>
</dependency>
</dependency>
</dependencies>
测试连JDBC连接
@Test
public void test01() throws PropertyVetoException {
//创建数据源【c3p0的数据源】
ComboPooledDataSource dataSource=new ComboPooledDataSource();
//Mysql5
dataSource.setDriverClass("com.mysql.jdbc.Driver");
/**
* mysql5
* 【数据源】 dataSource.setDriverClass("com.mysql.jdbc.Driver");
* 【URL】 dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/spring_test?
* useUnicode=true
* &characterEncoding=utf-8");
*
* mysql8
* 【数据源】多一个cj dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
* 【url】多一个时区配置 dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/spring_test?
* serverTimezone=UTC【时区设置】
* &useUnicode=true
* &characterEncoding=utf-8
* &userSSL=false");
* */
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/spring_test?useUnicode=true&characterEncoding=utf-8");
dataSource.setUser("root");
dataSource.setPassword("666");
/**
* 使用spring提供的JDBCTemplete
* */
JdbcTemplate template=new JdbcTemplate(dataSource);
String sql="insert into team(name,location) value (?,?)";
int update = template.update(sql,"森林狼","明尼苏达");
System.out.println("插入数据的结果："+update);
}
增删改关键字都是update
package com.jinhuan.dao;
import com.jinhuan.pojo.Team;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
/**
* @Author jinhuan
* @Date 2022/5/19 9:17
* Description: 使用继承JdbcDaoSupport可以代替JdbcTemplete
*/
public class TeamDao extends JdbcDaoSupport {
/**
* 向数据库中插入数据
* */
public int insert(Team team){
String sql = "insert into team(name,location) value(?,?)";
return this.getJdbcTemplate().update(sql,team.getName(),team.getLocation());
}
/**
* 向数据库中删除数据
* */
public int delete(Integer id){
String sql = "delete from team where id=?";
return this.getJdbcTemplate().update(sql,id);
}
/**
* Update Date
* */
public int update(Team team){
String sql = "update team set name=?,location=? where id = ?";
return this.getJdbcTemplate().update(sql,team.getName(),team.getLocation(),team.getId());
}
}
测试类
package com.jinhuan.test;
import com.jinhuan.dao.TeamDao;
import com.jinhuan.pojo.Team;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import java.beans.PropertyVetoException;
import java.util.List;
import java.util.Map;
/**
* @Author jinhuan
* @Date 2022/5/17 11:01
* Description:
*/
public class Test01 {
ApplicationContext ac = new ClassPathXmlApplicationContext("application.xml");
TeamDao teamDao = (TeamDao) ac.getBean("teamDao");
/**
* 测试更新语句
* */
@Test
public void testUpdate(){
System.out.println(teamDao.update(new Team(1,"灰熊","孟菲斯")));
}
/**
* 测试删除语句
* */
@Test
public void testDelete(){
System.out.println(teamDao.delete(1));
}
/**
* 测试insert
* */
@Test
public void testInsert(){
Team team = new Team("灰熊", "孟菲斯");
System.out.println(teamDao.insert(team));
}
@Test
public void test01() throws PropertyVetoException {
//创建数据源【c3p0的数据源】
ComboPooledDataSource dataSource=new ComboPooledDataSource();
//Mysql5
dataSource.setDriverClass("com.mysql.jdbc.Driver");
/**
* mysql5
* 【数据源】 dataSource.setDriverClass("com.mysql.jdbc.Driver");
* 【URL】 dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/spring_test?
* useUnicode=true
* &characterEncoding=utf-8");
*
* mysql8
* 【数据源】多一个cj dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
* 【url】多一个时区配置 dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/spring_test?
* serverTimezone=UTC【时区设置】
* &useUnicode=true
* &characterEncoding=utf-8
* &userSSL=false");
* */
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/spring_test?useUnicode=true&characterEncoding=utf-8");
dataSource.setUser("root");
dataSource.setPassword("666");
/**
* 使用spring提供的JDBCTemplete
* */
JdbcTemplate template=new JdbcTemplate(dataSource);
String sql="insert into team(name,location) value (?,?)";
int update = template.update(sql,"森林狼1","明尼苏达");
System.out.println("插入数据的结果："+update);
}
}
查询select
package com.jinhuan.dao;
import com.jinhuan.pojo.Team;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
/**
* @Author jinhuan
* @Date 2022/5/19 9:17
* Description: 使用继承JdbcDaoSupport可以代替JdbcTemplete
*/
public class TeamDao extends JdbcDaoSupport {
/**
* 处理返回结果集
* */
public Team handleResult(ResultSet resultSet) throws SQLException {
Team team = new Team();
team.setId(resultSet.getInt("id"));
team.setName(resultSet.getString("name"));
team.setLocation(resultSet.getString("location"));
return team;
}
/**
* 查询单行数据
* */
public Team findById(Integer id){
String sql = "select * from team where id = ?";
return this.getJdbcTemplate().queryForObject(sql, new Object[]{id}, new RowMapper<Team>() {
@Override
public Team mapRow(ResultSet resultSet, int i) throws SQLException {
return handleResult(resultSet);
}
});
}
/**
* 查询所有数据
* */
public List<Team> findAll(){
String sql = "select * from team ";
return this.getJdbcTemplate().query(sql, new RowMapper<Team>() {
@Override
public Team mapRow(ResultSet resultSet, int i) throws SQLException {
return handleResult(resultSet); }
});
}
/**
* 测试查询指定数据
* */
public Integer getCount(){
String sql = "select count(*) from team";
return this.getJdbcTemplate().queryForObject(sql,Integer.class);
}
/**
* 测试查询多数据
* */
public Map getMany(){
String sql = "select max(id),min(id) from team";
return this.getJdbcTemplate().queryForObject(sql,Map.class);
}
}
测试类
package com.jinhuan.test;
import com.jinhuan.dao.TeamDao;
import com.jinhuan.pojo.Team;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import java.beans.PropertyVetoException;
import java.util.List;
import java.util.Map;
/**
* @Author jinhuan
* @Date 2022/5/17 11:01
* Description:
*/
public class Test01 {
ApplicationContext ac = new ClassPathXmlApplicationContext("application.xml");
TeamDao teamDao = (TeamDao) ac.getBean("teamDao");
/**
* 测试单行查询
* */
@Test
public void testSelect(){
System.out.println(teamDao.findById(2));
}
/**
* 测试多行查询
* */
@Test
public void testSelectAll(){
List<Team> teams = teamDao.findAll();
for (Team team : teams) {
System.out.println(team);
}
}
/**
* 测试查询单一数据
* */
@Test
public void testCount(){
System.out.println(teamDao.getCount());
}
/**
* 测试查询多个数据
* */
@Test
public void testMany(){
Map many = teamDao.getMany();
for (Object o : many.keySet()) {
System.out.println(o);
}
}
}
