Spring学习笔记:jdbcTemplate和数据源配置
一、使用Spring框架jdbcTemplate实现数据库的增删改查
1.数据库
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/* SQLyog Ultimate v8.32 MySQL - 5.7.19-log : Database - infosm ********************************************************************* */ /*!40101 SET NAMES utf8 */; /*!40101 SET SQL_MODE=''*/; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; CREATE DATABASE /*!32312 IF NOT EXISTS*/`infosm` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `infosm`; /*Table structure for table `infosm_news` */ DROP TABLE IF EXISTS `infosm_news`; CREATE TABLE `infosm_news` ( `newsid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '咨询ID', `newstitle` varchar(50) DEFAULT NULL COMMENT '咨询标题', `newscontent` varchar(1000) DEFAULT NULL COMMENT '资讯内容', `clickcount` bigint(20) DEFAULT NULL COMMENT '点击数', PRIMARY KEY (`newsid`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='新闻资讯表'; /*Data for the table `infosm_news` */ insert into `infosm_news`(`newsid`,`newstitle`,`newscontent`,`clickcount`) values (1,'大家一起来学JAVA吧','JAVA是不可或缺的程序语言',9),(2,'好好学习','天天向上',3),(3,'我最喜欢的歌曲','黯然销魂',2),(4,'哈哈哈','色调风格的',0),(5,'谁狗带','电饭锅',NULL); /*Table structure for table `infosm_talk` */ DROP TABLE IF EXISTS `infosm_talk`; CREATE TABLE `infosm_talk` ( `tid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '评论编号', `content` varchar(200) DEFAULT NULL COMMENT '评论内容', `talktime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '评论时间', `nid` bigint(20) DEFAULT NULL COMMENT '咨询ID', PRIMARY KEY (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8 COMMENT='评论表'; /*Data for the table `infosm_talk` */ insert into `infosm_talk`(`tid`,`content`,`talktime`,`nid`) values (1,'很不错,我想学','2017-09-26 12:54:15',1),(2,'大家一起来学吧','2017-09-26 12:54:37',1),(3,'今天考试','2017-09-26 16:15:12',2),(4,'天气很不错哦','2017-09-26 16:15:36',1),(5,'哈哈哈','2017-09-26 17:01:24',2),(6,'国庆节放假啦','2017-09-29 12:08:22',3),(7,'中秋节放假啦','2017-09-29 12:38:58',3),(8,'中秋节放假啦','2017-09-29 13:56:21',3),(9,'中秋节放假啦','2017-09-29 13:56:22',3),(10,'中秋节放假啦','2017-09-29 13:56:23',3),(11,'中秋节放假啦','2017-09-29 13:56:23',3),(12,'中秋节放假啦','2017-09-29 13:56:23',3),(13,'中秋节放假啦','2017-09-29 13:56:23',3),(14,'中秋节放假啦','2017-09-29 13:56:23',3),(15,'中秋节放假啦','2017-09-29 13:56:24',3),(16,'中秋节放假啦','2017-09-29 13:56:39',1),(17,'中秋节放假啦','2017-09-29 13:56:40',1),(18,'中秋节放假啦','2017-09-29 13:56:40',1),(19,'中秋节放假啦','2017-09-29 13:56:40',1),(20,'中秋节放假啦','2017-09-29 13:56:40',1),(21,'中秋节放假啦','2017-09-29 13:56:40',1),(22,'中秋节放假啦','2017-09-29 13:56:40',1),(23,'中秋节放假啦','2017-09-29 13:56:41',1),(24,'中秋节放假啦','2017-09-29 13:56:41',1),(25,'中秋节放假啦','2017-09-29 13:56:41',1),(26,'中秋节放假啦','2017-09-29 13:56:50',5),(27,'中秋节放假啦','2017-09-29 13:56:50',5),(28,'中秋节放假啦','2017-09-29 13:56:51',5),(29,'中秋节放假啦','2017-09-29 13:56:51',5),(30,'中秋节放假啦','2017-09-29 13:56:51',5),(31,'中秋节放假啦','2017-09-29 13:56:57',4),(32,'中秋节放假啦','2017-09-29 13:56:57',4),(33,'中秋节放假啦','2017-09-29 13:56:57',4),(34,'中秋节放假啦','2017-09-29 13:56:57',4); /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
2.使用maven导入相关Jar包
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
<dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.3</version> <scope>test</scope> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>4.2.0.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>4.2.0.RELEASE</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.43</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.0.18</version> </dependency> <!-- https://mvnrepository.com/artifact/c3p0/c3p0 --> <dependency> <groupId>c3p0</groupId> <artifactId>c3p0</artifactId> <version>0.9.1.2</version> </dependency> <!-- https://mvnrepository.com/artifact/commons-dbcp/commons-dbcp --> <dependency> <groupId>commons-dbcp</groupId> <artifactId>commons-dbcp</artifactId> <version>1.4</version> </dependency> </dependencies>
3.项目框架结构
4.接口和类的代码
bean:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public class News { private Integer newsid; private String newstitle; private String newscontent; private Integer clickcount; public Integer getNewsid() { return newsid; } public void setNewsid(Integer newsid) { this.newsid = newsid; } public String getNewstitle() { return newstitle; } public void setNewstitle(String newstitle) { this.newstitle = newstitle; } public String getNewscontent() { return newscontent; } public void setNewscontent(String newscontent) { this.newscontent = newscontent; } public Integer getClickcount() { return clickcount; } public void setClickcount(Integer clickcount) { this.clickcount = clickcount; } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import java.util.Date; public class Talk { private Integer tid; private String content; private Date talktime; private Integer nid; public Integer getTid() { return tid; } public void setTid(Integer tid) { this.tid = tid; } public String getContent() { return content; } public void setContent(String content) { this.content = content; } public Date getTalktime() { return talktime; } public void setTalktime(Date talktime) { this.talktime = talktime; } public Integer getNid() { return nid; } public void setNid(Integer nid) { this.nid = nid; } }
dao:
import java.util.List; public interface INewsDao { public List<News> findAll(); }
import cn.infosm.bean.News; import cn.infosm.dao.INewsDao; 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; public class NewsDaoImpl extends JdbcDaoSupport implements INewsDao { @Override public List<News> findAll() { String sql = "SELECT * FROM infosm_news"; List<News> list = getJdbcTemplate().query(sql, new RowMapper<News>() { @Override public News mapRow(ResultSet rs, int rowNum) throws SQLException { News news = new News(); news.setNewsid(rs.getInt("newsid")); news.setNewstitle(rs.getString("newstitle")); news.setNewscontent(rs.getString("newscontent")); news.setClickcount(rs.getInt("clickcount")); return news; } }); return list; } }
service:
import java.util.List; public interface INewsService { public List<News> findAll(); }
import java.util.List; public class NewsServiceImpl implements INewsService { private INewsDao dao; public INewsDao getDao() { return dao; } public void setDao(INewsDao dao) { this.dao = dao; } @Override public List<News> findAll() { return dao.findAll(); } }
applicationContext.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" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd"> <!--通过bean元素生命需要Spring创建的实例。该实例的类型通过class属性指定, 并通过id属性为该实例制定一个名称,以便于访问--> <!--DataSource jdbc--> <bean id="dateSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql:///infosm"/> <property name="username" value="root"/> <property name="password" value="tengyu"/> </bean> <!--jdbcTemplate--> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dateSource"/> </bean> <!--newsDao--> <bean id="newsDao" class="cn.infosm.dao.impl.NewsDaoImpl"> <property name="jdbcTemplate" ref="jdbcTemplate"/> </bean> <!--newsService--> <bean id="newsService" class="cn.infosm.service.impl.NewsServiceImpl"> <property name="dao" ref="newsDao"/> </bean> <!--talkDao--> <bean id="talkDao" class="cn.infosm.dao.impl.TalkDaoImpl"> <property name="jdbcTemplate" ref="jdbcTemplate"/> </bean> <!--talkService--> <bean id="talkService" class="cn.infosm.service.impl.TalkServiceImpl"> <property name="dao" ref="talkDao"/> </bean> </beans>
测试类:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import java.util.List; public class NewsMapperTest { @Test public void findAllNews(){ ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml"); INewsService newsService = (INewsService) context.getBean("newsService"); List<News> list = newsService.findAll(); for (News news :list) { System.out.println(news.getNewstitle()); } } @Test public void findAllTalks(){ ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml"); ITalkService talkService = (ITalkService) context.getBean("talkService"); List<Talk> list = talkService.findAll(); for (Talk talk :list) { System.out.println(talk.getTid()+talk.getContent()+talk.getTalktime()); } } }
二、修改数据源,多种数据源配置方法(applicationContext.xml中替换jdbc数据源)
1、dbcp数据源
<!--DataSource dbcp--> <!--<bean id="dateSource" class="org.apache.commons.dbcp.BasicDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql:///infosm"/> <property name="username" value="root"/> <property name="password" value="tengyu"/> </bean>-->
2、c3p0数据源
<!--DataSource c3p0--> <!--<bean id="dateSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="driverClass" value="com.mysql.jdbc.Driver"/> <property name="jdbcUrl" value="jdbc:mysql:///infosm"/> <property name="user" value="root"/> <property name="password" value="tengyu"/> </bean>-->
3、druid数据源(alibaba)
<!--DataSource druid--> <!--<bean id="dateSource" class="com.alibaba.druid.pool.DruidDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql:///infosm"/> <property name="username" value="root"/> <property name="password" value="tengyu"/> </bean>-->
三、修改数据源时需要引入相应的架包(注意)
maven引入依赖