Maven + Spring MVC+Mybatis + MySQL +AngularJS + Bootstrap 实现简单微博应用(二)访问数据库

半年过去了,为了对得起看过(一)的三百多位朋友和我自己,还是得要把这系列写完。AnyWay,迟做总比不做好。

(上一节:Maven + Spring MVC+Mybatis + MySQL +AngularJS + Bootstrap 实现前后台交互页面(一)环境搭建

这一节我们将整合spring、mybatis成功连接数据库并可以进行表的增删改查。

1. 准备所需要的pom依赖

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.sommer.sample</groupId>
	<artifactId>webproject</artifactId>
	<packaging>war</packaging>
	<version>0.0.1-SNAPSHOT</version>
	<name>webproject Maven Webapp</name>
	<url>http://maven.apache.org</url>
	<properties>
		<!-- spring版本号 -->
		<spring.version>4.2.4.RELEASE</spring.version>
		<!-- mybatis版本号 -->
		<mybatis.version>3.4.1</mybatis.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.12</version>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.4.1</version>
		</dependency>
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis-spring</artifactId>
			<version>1.3.0</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-beans</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<!-- Spring 在 3.2.13版本后,要单独引用 -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-oxm</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-tx</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-web</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>4.2.4.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context-support</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-test</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<!-- java ee (注解什么的都要用到它) -->
		<dependency>
			<groupId>javax</groupId>
			<artifactId>javaee-api</artifactId>
			<version>7.0</version>
		</dependency>
		<!-- dbcp用来在applicationContext.xml中配置数据库 -->
		<dependency>
			<groupId>commons-dbcp</groupId>
			<artifactId>commons-dbcp</artifactId>
			<version>1.4</version>
		</dependency>
		<!-- Mysql数据库链接 -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>6.0.2</version>
		</dependency>
		<!-- Spring 4.x 依赖的相关 json jar -->
		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-databind</artifactId>
			<version>2.5.4</version>
		</dependency>
		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-core</artifactId>
			<version>2.5.4</version>
		</dependency>
		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-annotations</artifactId>
			<version>2.5.4</version>
		</dependency>
	</dependencies>
	<build>
		<finalName>webproject</finalName>
	</build>
</project>

2. 配置文件

spring-mvc是web层的配置,我们放到下一节。


2.1 数据库连接配置


为了方便管理和修改,我们将这些信息放在jdbc.properties里面

#database

driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/weibo?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false
jdbc.username=root
jdbc.password=123456
jdbc.maxWait=5000

com.mysql.cj.jdbc.Driver是mysql-connector-java 6.2的新驱动名称,老的是com.mysql.jdbc.Driver。

useUnicode、characterEncoding 指定字符的编码、解码格式; serverTimezone为了让server(tomcat)和mysql的时区同步,不加的话会可能会报下面的错,这是mysql-connector-java 从5.1.33版本后TimeUtil类存在bug。mysql新的安全性设置要求SSL连接,所以加上参数userSSL=false。

Cannot create PoolableConnectionFactory
 (The server time zone value '?й???????' is unrecognized or represents more than one time zone. 
You must configure either the server or JDBC driver
 (via the serverTimezone configuration property) 
to use a more specifc time zone value if you want to utilize time zone support.)


2.2 spring-mybatis整合

<span style="font-size:14px;"><beans xmlns="http://www.springframework.org/schema/beans"
		xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
		xmlns:aop="http://www.springframework.org/schema/aop"
		xmlns:tx="http://www.springframework.org/schema/tx"
		xmlns:context="http://www.springframework.org/schema/context"
		xsi:schemaLocation="
			http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd
			http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd
			http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.2.xsd
			http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd">

    <!-- 自动扫描的包 ,将带有注解的类 纳入spring容器管理 -->
    <context:component-scan base-package="com.sommer.sample"></context:component-scan>

    <!-- 引入jdbc配置文件 -->
    <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="locations">
            <list>
                <value>classpath*:conf/jdbc.properties</value>
            </list>
        </property>
    </bean>

    <!-- dataSource 配置 -->
    <bean id="sampleDataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <!-- 基本属性 url、user、password -->
        <property name="driverClassName" value="${driver}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
 		<property name="maxWait" value="${jdbc.maxWait}" />

    </bean>

    <!-- mybatis文件配置,扫描所有mapper文件 -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    	<property name="dataSource" ref="sampleDataSource"/>
    	<property name="mapperLocations" value="classpath:mappers/*.xml"/>
    </bean>

    <!-- spring与mybatis整合配置,扫描所有dao -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    	<property name="basePackage" value="com.sommer.sample.dashboard.dao"/>
    	<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
    </bean>

</beans>
</span>

3. Java 代码

Java代码分成实体层entity、数据访问层dao、业务服务层service(又分为接口和实现)和控制层controller。控制层是与前端交互打交道的,可以先不管。



package com.weibo.dashboard.entity;

public class User {
	private int id;
	private String name;
	private String password;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
}

package com.weibo.dashboard.dao;

import org.apache.ibatis.annotations.Param;

import com.weibo.dashboard.entity.User;

public interface UserMapper {
	User select(@Param("name")String name);
	int userNameExits(@Param("name")String name);
	int accountValid(User user);
	int insert(User user);
}
package com.weibo.dashboard.service;

import com.weibo.dashboard.entity.User;

public interface UserService {
	User select(String name);
	int userNameExits(String name);
	boolean accountValid(User user);
	int insert(User user);
}

package com.weibo.dashboard.service.impl;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.weibo.dashboard.dao.UserMapper;
import com.weibo.dashboard.entity.User;
import com.weibo.dashboard.service.UserService;

@Service
public class UserServiceImpl implements UserService{

	@Resource
	private UserMapper userMapper;
	
	public User select(String name) {
		return userMapper.select(name);
	}

	public int userNameExits(String name) {
		return userMapper.userNameExits(name);
	}

	public boolean accountValid(User user) {
		return userMapper.accountValid(user)>0;
	}

	public int insert(User user) {
		return userMapper.insert(user);
	}

}

4. SQL映射的xml文件



<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper
	namespace="com.weibo.dashboard.dao.UserMapper">
	<resultMap id="userMap" type="com.weibo.dashboard.entity.User">
		<id property="id" column="id" javaType="int"/>
		<result property="name" column="name" javaType="String"/>
		<result property="password" column="password" javaType="String"/>
	</resultMap>
	<select id="select" resultMap="userMap">
		select * from users
		<where>
			<if test="name!=null">
				and name=#{name}
			</if>
		</where>
	</select>
	<select id="userNameExits" resultType="int">
		select count(0) from users where name=#{name}
	</select>
	<select id="accountValid" parameterType="com.weibo.dashboard.entity.User" resultType="int">
		select count(0) from users where name=#{name} and password=#{password}
	</select>
	<insert id="insert" parameterType="com.weibo.dashboard.entity.User" useGeneratedKeys="true" keyProperty="id">
		insert into users (name, password) values (#{name},#{password})
	</insert>
</mapper>



这里要注意的是映射文件的头部,mybatis的配置文件是:

<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD  Config 3.0//EN"               
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
...
</configuration>

mapper.xml文件的头部是

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper>
...
</mapper>

 
doctype定义写错的话eclipse一般会提示的。 

5. 单元测试:

package webproject;

import javax.annotation.Resource;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.weibo.dashboard.entity.User;
import com.weibo.dashboard.service.UserService;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath:conf/spring-mybatis.xml"})
public class UserTest {
	@Resource
	UserService us;
	
	@Test
	public void testAdd(){
		User user = new User();
		user.setName("test");
		user.setPassword("testpd");
		int res= us.insert(user);
		System.out.println(res);
	}
	
	@Test
	public void testAccountValid(){
		User user = new User();
		user.setName("test");
		user.setPassword("testpd");
		boolean res = us.accountValid(user);
		System.out.println(res);
	}
	
}



执行单元测试,打印出

testAdd方法打印出1,表示插入成功;testAccountValid方法打印出true,表示用户账号有效。


同理我们加上posts,comments的插入、删除等功能:


Post.java

package com.weibo.dashboard.entity;

import java.util.Date;
import java.util.List;

public class Post {
	private int id;
	private Date date;
	private String authorName;
	private String content;
	private int likes;
	private List<Comment> commentList;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public Date getDate() {
		return date;
	}
	public void setDate(Date date) {
		this.date = date;
	}
	
	public String getAuthorName() {
		return authorName;
	}
	public void setAuthorName(String authorName) {
		this.authorName = authorName;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public int getLikes() {
		return likes;
	}
	public void setLikes(int likes) {
		this.likes = likes;
	}
	public List<Comment> getCommentList() {
		return commentList;
	}
	public void setCommentList(List<Comment> commentList) {
		this.commentList = commentList;
	}
	
}<span style="color:#006600;"><strong><span style="font-size: 18px;">
</span></strong></span>
PostMapper.java

package com.weibo.dashboard.dao;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.weibo.dashboard.entity.Post;

public interface PostMapper {
	List<Post> findList();
	List<Post> postByUser(@Param("userName") String userName);
	int insert(Post post);
	int delete(@Param("id") int id);
	int like(@Param("id") int id);
	int dislike(@Param("id") int id);
	
}


PostService.java

package com.weibo.dashboard.service;

import java.util.List;

import com.weibo.dashboard.entity.Post;

public interface PostService {
	List<Post> findList();

	List<Post> postByUser(String userName);

	int insert(Post post);

	int delete(int id);

	int like(int id);

	int dislike(int id);
}<span style="color:#006600;">
</span>

PostServiceImpl.java

package com.weibo.dashboard.service.impl;

import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.weibo.dashboard.dao.PostMapper;
import com.weibo.dashboard.entity.Post;
import com.weibo.dashboard.service.PostService;
@Service
public class PostServiceImpl implements PostService{

	@Resource
	PostMapper postMapper;
	
	public List<Post> findList() {
		return postMapper.findList();
	}

	public List<Post> postByUser(String userName) {
		return postMapper.postByUser(userName);
	}

	public int insert(Post post) {
		return postMapper.insert(post);
	}

	public int delete(int id) {
		return postMapper.delete(id);
	}

	public int like(int id) {
		return postMapper.like(id);
	}

	public int dislike(int id) {
		return postMapper.dislike(id);
	}

}

postMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.weibo.dashboard.dao.PostMapper">
	<resultMap id="postMap" type="com.weibo.dashboard.entity.Post">
		<id property="id" column="id" javaType="int"/>
		<result property="date" column="date" javaType="Date"/>
		<result property="authorName" column="author_name" javaType="String"/>
		<result property="content" column="content" javaType="String"/>
		<result property="likes" column="likes" javaType="int"/>
		<collection property="commentList" column="commentList" ofType="com.weibo.dashboard.entity.Comment">
			<result property="cDate" column="c_date" javaType="Date"/>
			<result property="cContent" column="c_content" javaType="String"/>
			<result property="cAuthorName" column="c_author_name" javaType="String"/>
		</collection>
	</resultMap>
	<select id="findList" resultMap="postMap">
		select * from posts left outer join comments on post_id = posts.id order by posts.date desc
	</select>
	<select id="postByUser" resultMap="postMap">
		select * from posts,comments where post_id = posts.id and posts.author_name = #{userName} order by posts.date desc
	</select>
	<insert id="insert" parameterType="com.weibo.dashboard.entity.Post" useGeneratedKeys="true" keyProperty="id">
		insert into posts
		(date,author_name,content)values
		(now(),#{authorName},#{content})
	</insert>
	<delete id="delete">
		delete from posts where id=#{id}
	</delete>
	<update id="like">
		update posts set likes=IF(likes IS NULL, 1,likes+1) where id=#{id}
	</update>
	<update id="dislike">
		update posts set likes=IF(likes >0, likes-1,0) where id=#{id}
	</update>
</mapper>


Comment.java

package com.weibo.dashboard.entity;

import java.util.Date;

public class Comment {
	private int id;
	private Date cDate;
	private String cContent;
	private String cAuthorName;
	private int postId;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public Date getcDate() {
		return cDate;
	}
	public void setcDate(Date cDate) {
		this.cDate = cDate;
	}
	public String getcContent() {
		return cContent;
	}
	public void setcContent(String cContent) {
		this.cContent = cContent;
	}
	public String getcAuthorName() {
		return cAuthorName;
	}
	public void setcAuthorName(String cAuthorName) {
		this.cAuthorName = cAuthorName;
	}
	public int getPostId() {
		return postId;
	}
	public void setPostId(int postId) {
		this.postId = postId;
	}

}<span style="color:#009900;"><span style="font-size: 18px;">
</span></span>


CommentMapper.java


public interface CommentMapper {
<span style="white-space:pre">	</span>
<span style="white-space:pre">	</span>int insert(Comment comment);
<span style="white-space:pre">	</span>int delete(@Param("id") int id);
<span style="white-space:pre">	</span>
}

CommentService.java

package com.weibo.dashboard.service;

import com.weibo.dashboard.entity.Comment;

public interface CommentService {
	
	int insert(Comment comment);
	int delete(int id);
}

CommentServiceImpl.java

package com.weibo.dashboard.service.impl;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.weibo.dashboard.dao.CommentMapper;
import com.weibo.dashboard.entity.Comment;
import com.weibo.dashboard.service.CommentService;
@Service
public class CommentServiceImpl implements CommentService{

	@Resource
	CommentMapper commentMapper;
	public int insert(Comment comment) {
		return commentMapper.insert(comment);
	}

	public int delete(int id) {
		return commentMapper.delete(id);
	}

}


commentMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.weibo.dashboard.dao.CommentMapper">
	<resultMap id="commentMap" type="com.weibo.dashboard.entity.Comment">
		<id property="id" column="id" javaType="int" />
		<result property="cDate" column="c_date" javaType="Date" />
		<result property="cContent" column="c_content" javaType="String" />
		<result property="cAuthorName" column="c_author_name" javaType="String" />
		<result property="postId" column="post_id" javaType="String" />
	</resultMap>
	
	<insert id="insert" parameterType="com.weibo.dashboard.entity.Comment" useGeneratedKeys="true" keyProperty="id">
		insert into comments
		(c_date,c_author_name,post_id,c_content)values
		(now(),#{cAuthorName},#{postId},#{cContent})
	</insert>
	<delete id="delete">
		delete from comments where id=#{id}
	</delete>
	
</mapper>


下一节将使用SpringMVC实现前后台交互。

http://blog.csdn.net/shymi1991/article/details/52563870


  • 0
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值