Spring整合JDBC

1、引入依赖

 <properties><maven.compiler.source>8</maven.compiler.source><maven.compiler.target>8</maven.compiler.target><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding></properties><dependencies><!--  测试依赖   --><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version><scope>test</scope></dependency><!--核心依赖--><dependency><groupId>org.springframework</groupId><artifactId>spring-context</artifactId><version>5.2.13.RELEASE</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-jdbc</artifactId><version>5.2.13.RELEASE</version></dependency>
<!--        mysql依赖--><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.29</version></dependency>
<!--        数据源依赖--><dependency><groupId>com.mchange</groupId><artifactId>c3p0</artifactId><version>0.9.5.2</version></dependency></dependencies><build><plugins>
<!--            编译插件    --><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-compiler-plugin</artifactId><version>3.8.0</version><configuration><source>1.8</source><target>1.8</target></configuration></plugin></plugins></build>

2、测试连接

连接数据库并且操作的步骤如下 ，连接对应的数据库，前提是本机中存在mysql并且运行以及创建对应的数据库。

然后将四大参数放入，在DriverClass参数中mysql8以上才会由中间的.cj.，8以下没有。url中数据库问号后的内容为字符集的相关设置。

然后通过JdbcTemplate可以对数据库进行相关的操作

 @Testpublic void test01( ) throws PropertyVetoException {// 创建数据库ComboPooledDataSource dataSource = new ComboPooledDataSource();dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/springJDBC?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useUnicode=true&useSSL=false");dataSource.setUser("root");dataSource.setPassword("123456");// 使用JdbcTemplate template = new JdbcTemplate(dataSource);String sql = "INSERT INTO team (tname , location) VALUES (?, ?)";int update = template.update(sql, "AI2", "郑州2");System.out.println("插入结果: " + update);}

 3、spring管理JdbcTemplate

spring整合jdbc可以让dao层继承Spring提供的JdbcDaoSupport类，该类中提供了jdbcTemplate模板可以用来使用。

handlResult函数：是因为在查找的操作中，由重复性的操作，单独拿出来进行封装，用来简化代码。

非查找语句的执行中，都是通过调用JdbcTemplate中的update函数，第一个参数为sql语句，后面跟不定量的参数用来填补sql语句中占位符的位置。

查找语句：
        返回数据只有一行的时候使用qyeryForObject函数，第一个位置为sql语句，第二个Object数组内容为参数，用来填补占位符，第三个位置为RowMapper接口用来处理返回的每一行数据，处理结果为需要的数据类型。
        返回数据有多行的情况使用query函数，参数类型同上，区别就是query函数的返回值类型为list数组。
        返回数据只有一列的情况，第二个参数可以直接用对应类型的类。
        返回数据只有一行的情况，并且不是一个类等，可以用Map来存取返回值，使用qyeryForMay，第一个位置为sql语句，第二个Object数组内容为参数，用来填补占位符。

public class TeamDao extends JdbcDaoSupport {public Team handlResult(ResultSet resultSet) throws SQLException {Team team = new Team();team.settId(resultSet.getInt("tid"));team.setLocation(resultSet.getString("location"));team.settName(resultSet.getString("tname"));return team;}public int insert(Team team) {String sql = "insert team (tname, location) values (?, ?)";int update = this.getJdbcTemplate().update(sql, team.gettName(), team.getLocation());return update;}public int update(Team team) {String sql = "update team set tname=?, location=? where tid=?";return this.getJdbcTemplate().update(sql, team.gettName(), team.getLocation(), team.gettId());}public int del(int id) {String sql = "delete from team where tid=?";return this.getJdbcTemplate().update(sql, id);}public Team getTeamById(int id) {String sql = "select * from team where tid=?";Team team = (Team) this.getJdbcTemplate().queryForObject(sql, new Object[] {id}, new RowMapper<Object>() {@Overridepublic Object mapRow(ResultSet resultSet, int i) throws SQLException {return handlResult(resultSet);}});return team;}public List<Team> getTeamAll() {String sql = "select * from team";List<Team> list = this.getJdbcTemplate().query(sql, new RowMapper<Team>() {@Overridepublic Team mapRow(ResultSet resultSet, int i) throws SQLException {return handlResult(resultSet);}});return list;}public int getCount() {String sql = "select count(*) from team";// 如果查询的列只有唯一一列,queryForObject (sql语句,为一列的数据类型)return this.getJdbcTemplate().queryForObject(sql,  Integer.class);}public Map<String, Object> getMany() {String sql = "select max(tid) as max, min(tid) as min from team";// 如果查询的列只有唯一一列,queryForObject (sql语句,为一列的数据类型)return this.getJdbcTemplate().queryForMap(sql);}
}

spring的配置文件application.xml中需要创建数据源和给TeamDao中的jdbcTemplate赋值

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
"><!-- 创建数据源 --><bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"><property name="driverClass" value="com.mysql.cj.jdbc.Driver"/><property name="jdbcUrl" value="jdbc:mysql://127.0.0.1:3306/springJDBC?serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8&amp;useUnicode=true&amp;useSSL=false"/><property name="user" value="root"/><property name="password" value="123456"/></bean><!-- 创建jdbcTemplate对象，给类中dataSource赋值 --><bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"><property name="dataSource" ref="dataSource"></property></bean><!-- 创建teamDao对象，给类中jdbcTemplate赋值 --><bean id="teamDao" class="com.AE.dao.TeamDao"><property name="jdbcTemplate" ref="jdbcTemplate"/></bean>
</beans>

4、测试

public class test01 {
//    private TeamDao teamDao;@Testpublic void test01( ) throws PropertyVetoException {// 创建数据库ComboPooledDataSource dataSource = new ComboPooledDataSource();dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/springJDBC?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useUnicode=true&useSSL=false");dataSource.setUser("root");dataSource.setPassword("123456");// 使用JdbcTemplate template = new JdbcTemplate(dataSource);String sql = "INSERT INTO team (tname , location) VALUES (?, ?)";int update = template.update(sql, "AI2", "郑州2");System.out.println("插入结果: " + update);}@Testpublic void test02(){ApplicationContext ac = new ClassPathXmlApplicationContext("spring.xml");TeamDao teamDao = (TeamDao) ac.getBean("teamDao");Team team = new Team();team.setLocation("南阳");team.settName("张淏");int insert = teamDao.insert(team);System.out.println(insert);}@Testpublic void test03(){ApplicationContext ac = new ClassPathXmlApplicationContext("spring.xml");TeamDao teamDao = (TeamDao) ac.getBean("teamDao");Team team = new Team();team.settId(5);team.setLocation("郑州3");team.settName("AI3");int update = teamDao.update(team);System.out.println(update);}@Testpublic void test04(){ApplicationContext ac = new ClassPathXmlApplicationContext("spring.xml");TeamDao teamDao = (TeamDao) ac.getBean("teamDao");int update = teamDao.del(5);System.out.println(update);}@Testpublic void test05(){ApplicationContext ac = new ClassPathXmlApplicationContext("spring.xml");TeamDao teamDao = (TeamDao) ac.getBean("teamDao");Team team = teamDao.getTeamById(2);System.out.println(team);}@Testpublic void test06(){ApplicationContext ac = new ClassPathXmlApplicationContext("spring.xml");TeamDao teamDao = (TeamDao) ac.getBean("teamDao");List<Team> list = teamDao.getTeamAll();for(Team team : list) {System.out.println(team);}System.out.println(list);}@Testpublic void test07(){ApplicationContext ac = new ClassPathXmlApplicationContext("spring.xml");TeamDao teamDao = (TeamDao) ac.getBean("teamDao");int a = teamDao.getCount();System.out.println(a);}@Testpublic void test08(){ApplicationContext ac = new ClassPathXmlApplicationContext("spring.xml");TeamDao teamDao = (TeamDao) ac.getBean("teamDao");Map<String, Object> many = teamDao.getMany();for(String a : many.keySet()) {System.out.println(a + "=" + many.get(a));}}
}