Spring学习笔记：jdbcTemplate和数据源配置
一、使用Spring框架jdbcTemplate实现数据库的增删改查
1.数据库
/* 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包
<dependencies> <dependency> <groupId>junitgroupId> <artifactId>junitartifactId> <version>4.3version> <scope>testscope> dependency> <dependency> <groupId>org.springframeworkgroupId> <artifactId>spring-webmvcartifactId> <version>4.2.0.RELEASEversion> dependency> <dependency> <groupId>org.springframeworkgroupId> <artifactId>spring-jdbcartifactId> <version>4.2.0.RELEASEversion> dependency> <dependency> <groupId>mysqlgroupId> <artifactId>mysql-connector-javaartifactId> <version>5.1.43version> dependency> <dependency> <groupId>com.alibabagroupId> <artifactId>druidartifactId> <version>1.0.18version> dependency> <dependency> <groupId>c3p0groupId> <artifactId>c3p0artifactId> <version>0.9.1.2version> dependency> <dependency> <groupId>commons-dbcpgroupId> <artifactId>commons-dbcpartifactId> <version>1.4version> dependency> dependencies>
3.项目框架结构
4.接口和类的代码
bean：
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; } }
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 ListfindAll(); }
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 ListfindAll() { String sql = "SELECT * FROM infosm_news"; List list = getJdbcTemplate().query(sql, new RowMapper () { @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 ListfindAll(); }
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 ListfindAll() { 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 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> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dateSource"/> bean> <bean id="newsDao" class="cn.infosm.dao.impl.NewsDaoImpl"> <property name="jdbcTemplate" ref="jdbcTemplate"/> bean> <bean id="newsService" class="cn.infosm.service.impl.NewsServiceImpl"> <property name="dao" ref="newsDao"/> bean> <bean id="talkDao" class="cn.infosm.dao.impl.TalkDaoImpl"> <property name="jdbcTemplate" ref="jdbcTemplate"/> bean> <bean id="talkService" class="cn.infosm.service.impl.TalkServiceImpl"> <property name="dao" ref="talkDao"/> bean> beans>
测试类：
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数据源
2、c3p0数据源
3、druid数据源（alibaba）
三、修改数据源时需要引入相应的架包（注意）
maven引入依赖