一、使用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*/;
View Code
2.使用maven导入相关Jar包
junit
junit
4.3
test
org.springframework
spring-webmvc
4.2.0.RELEASE
org.springframework
spring-jdbc
4.2.0.RELEASE
mysql
mysql-connector-java
5.1.43
com.alibaba
druid
1.0.18
c3p0
c3p0
0.9.1.2
commons-dbcp
commons-dbcp
1.4
View Code
3.项目框架结构
4.接口和类的代码
bean:
public classNews {privateInteger newsid;privateString newstitle;privateString newscontent;privateInteger clickcount;publicInteger getNewsid() {returnnewsid;
}public voidsetNewsid(Integer newsid) {this.newsid =newsid;
}publicString getNewstitle() {returnnewstitle;
}public voidsetNewstitle(String newstitle) {this.newstitle =newstitle;
}publicString getNewscontent() {returnnewscontent;
}public voidsetNewscontent(String newscontent) {this.newscontent =newscontent;
}publicInteger getClickcount() {returnclickcount;
}public voidsetClickcount(Integer clickcount) {this.clickcount =clickcount;
}
}
View Code
importjava.util.Date;public classTalk {privateInteger tid;privateString content;privateDate talktime;privateInteger nid;publicInteger getTid() {returntid;
}public voidsetTid(Integer tid) {this.tid =tid;
}publicString getContent() {returncontent;
}public voidsetContent(String content) {this.content =content;
}publicDate getTalktime() {returntalktime;
}public voidsetTalktime(Date talktime) {this.talktime =talktime;
}publicInteger getNid() {returnnid;
}public voidsetNid(Integer nid) {this.nid =nid;
}
}
View Code
dao:
importjava.util.List;public interfaceINewsDao {public ListfindAll();
}
importcn.infosm.bean.News;importcn.infosm.dao.INewsDao;importorg.springframework.jdbc.core.RowMapper;importorg.springframework.jdbc.core.support.JdbcDaoSupport;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.util.List;public class NewsDaoImpl extends JdbcDaoSupport implementsINewsDao {
@Overridepublic ListfindAll() {
String sql= "SELECT * FROM infosm_news";
List list = getJdbcTemplate().query(sql, new RowMapper() {
@Overridepublic News mapRow(ResultSet rs, int rowNum) throwsSQLException {
News news= newNews();
news.setNewsid(rs.getInt("newsid"));
news.setNewstitle(rs.getString("newstitle"));
news.setNewscontent(rs.getString("newscontent"));
news.setClickcount(rs.getInt("clickcount"));returnnews;
}
});returnlist;
}
}
service:
importjava.util.List;public interfaceINewsService {public ListfindAll();
}
importjava.util.List;public class NewsServiceImpl implementsINewsService {privateINewsDao dao;publicINewsDao getDao() {returndao;
}public voidsetDao(INewsDao dao) {this.dao =dao;
}
@Overridepublic ListfindAll() {returndao.findAll();
}
}
applicationContext.xml:
http://www.springframework.org/schema/beans/spring-beans.xsd">
测试类:
import java.util.List;
public class NewsMapperTest {
@Test
public void findAllNews(){
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
INewsService newsService = (INewsService) context.getBean("newsService");
Listlist = 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");
Listlist = talkService.findAll();
for (Talk talk :list) {
System.out.println(talk.getTid()+talk.getContent()+talk.getTalktime());
}
}
}
View Code
二、修改数据源,多种数据源配置方法(applicationContext.xml中替换jdbc数据源)
1、dbcp数据源
2、c3p0数据源
3、druid数据源(alibaba)
三、修改数据源时需要引入相应的架包(注意)
maven引入依赖