之前使用SpringMVC框架做了一个项目,后来项目中需要将数据持久化,就选择了使用mybits 3.4.1+mysql 5.7这样的架构,mybits比较灵活,入门比较简单,虽然写的SQL语句比较多,但做一些小项目还是可以的,本人使用的是maven配置mybits。
先上传一张项目文件结构
第一步,在原有的项目中添加mybits和mysql驱动的依赖。
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.39</version>
</dependency>
添加好后可以选中项目右键maven->upadte project
第二步,在src/main/resources文件夹下创建数据库配置文件jdbc.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/speech
username=root
password=yue960713
#\u5B9A\u4E49\u521D\u59CB\u8FDE\u63A5\u6570
initialSize=0
#\u5B9A\u4E49\u6700\u5927\u8FDE\u63A5\u6570
maxActive=20
#\u5B9A\u4E49\u6700\u5927\u7A7A\u95F2
maxIdle=20
#\u5B9A\u4E49\u6700\u5C0F\u7A7A\u95F2
minIdle=1
#\u5B9A\u4E49\u6700\u957F\u7B49\u5F85\u65F6\u95F4
maxWait=60000
第三步,在src/main/resources文件夹下创建mybits配置文件mybits-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<!-- 这是根标签 -->
<configuration>
<!-- 元素允许在主配置文件之外提供一个properties格式对应文件,从而使得主配置文件更加通用。这样对部署非常有用 -->
<!-- <properties resource="mysql.properties" /> -->
<!-- 设置别名,一定要放在properties下面 -->
<properties resource="jdbc.properties"></properties>
<typeAliases>
<typeAlias alias="User" type="com.yueyibo.entity.User" />
</typeAliases>
<!-- 配置数据源相关的信息 -->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
<!--<property name="driver" value="${driver}" />
<property name="url" value="${url}" />
<property name="username" value="${username}" />
<property name="password" value="${password}" /> -->
</dataSource>
</environment>
</environments>
<!-- 列出映射文件 -->
<mappers>
<mapper resource="com/yueyibo/entity/User.xml" />
</mappers>
</configuration>
第四步,创建映射实体User.java
package com.yueyibo.entity;
public class User {
private int id;
private String account;
private String username;
private String password;
private String token;
private String appkey;
private String appsecret;
private String balance;
public User(){
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getAccount() {
return account;
}
public void setAccount(String account) {
this.account = account;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getToken() {
return token;
}
public void setToken(String token) {
this.token = token;
}
public String getAppkey() {
return appkey;
}
public void setAppkey(String appkey) {
this.appkey = appkey;
}
public String getAppsecret() {
return appsecret;
}
public void setAppsecret(String appsecret) {
this.appsecret = appsecret;
}
public String getBalance() {
return balance;
}
public void setBalance(String balance) {
this.balance = balance;
}
}
第五步,创建实体类对应的映射文件User.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.yueyibo.entity.UserMapper">
<!-- 查找 -->
<select id="findUser" parameterType="com.yueyibo.entity.LoginEntity" resultType="User">
select * from User where account =#{account} and password=#{password}
</select>
<select id="findUserByUsername" parameterType="java.lang.String" resultType="User">
select * from User where username =#{username}
</select>
<!-- 插入 -->
<!-- useGeneratedKeys设置为"true"表明要MyBatis获取由数据库自动生成的主键;keyProperty="id"指定把获取到的主键值注入到Employeer的id属性 -->
<insert id="addUser" parameterType="User"
useGeneratedKeys="true" keyProperty="id">
insert into User(id,account,username,password,token,appkey,appsecret,balance)
values(#{id},#{account},#{username},#{password},#{token},#{appkey},#{appsecret},#{balance})
</insert>
<!-- 删除 -->
<delete id="deleteUserById" parameterType="int">
delete from User where id = #{id}
</delete>
<!-- 修改 -->
<update id="updateUserById" parameterType="User">
update User set account = #{account},username= #{username},password = #{password}
,token=#{token},appkey=#{appkey},appsecret=#{appsecret},balance=#{balance} where id = #{id}
</update>
</mapper>
第六步,创建Dao类UserDao.java
package com.yueyibo.Dao;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.yueyibo.entity.LoginEntity;
import com.yueyibo.entity.User;
public class UserDao {
private static SqlSessionFactory sqlSessionFactory;
private static Reader reader;
static {
try {
reader = Resources.getResourceAsReader("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 查找
*/
public static User findUserByLoginEntity(LoginEntity loginEntity) {
SqlSession session = null;
try {
session = sqlSessionFactory.openSession();
User user = (User) session.selectOne(
"com.yueyibo.entity.UserMapper.findUser", loginEntity);
if (user == null) {
System.out.println("null");
return null;
}
else {
System.out.println(user.getAccount()+user.getPassword());
return user;
}
} finally {
session.close();
}
}
/**
* 增加
*/
public static boolean addUser(User user){
SqlSession session = null;
try {
session = sqlSessionFactory.openSession();
//返回值是记录条数
int resultCount = session.insert("com.yueyibo.entity.UserMapper.addUser", user );
System.out.printf("当前插入的employeer_id :%d 当前插入数据库中条数:%d " , user.getId() ,resultCount); //获取插入对象的id
System.out.println("");
session.commit() ;
if(resultCount>=1){
return true;
}
else{
return false;
}
} finally {
session.close();
}
}
/**
* 删除
*
*/
public static void deleteUesrById(int id){
SqlSession session = null;
try {
session = sqlSessionFactory.openSession();
//返回值是记录条数
int resultCount=session.delete("com.yueyibo.entity.UserMappper.deleteUserById",id);
System.out.println("当前删除数据库中条数: "+resultCount); //获取插入对象的id
session.commit() ;
} finally {
session.close();
}
}
/**
* 更改
*/
public static void updateUser(User user){
SqlSession session = null;
try {
session = sqlSessionFactory.openSession();
session.update("com.yueyibo.entity.UserMapper.updateUser",user);
session.commit() ;
} finally {
session.close();
}
}
public static User findUserByUsername(String username) {
SqlSession session = null;
try {
session = sqlSessionFactory.openSession();
User user = (User) session.selectOne(
"com.yueyibo.entity.UserMapper.findUserByUsername", username);
if (user == null) {
System.out.println("null");
return null;
}
else {
System.out.println(user.getAccount()+user.getPassword());
return user;
}
} finally {
session.close();
}
}
}
以上就配置好了mybits,接下来写一个jsp页面验证一下,事先我已经在数据库中插入了一条记录用作验证
login.jsp代码如下登录成功则显示用户名和权限否则显示登陆失败
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<form action="checkLogin" method="post">
账号:<input type="text" name="account"/>
密码:<input type="password" name="password"/>
<input type="submit" value="提交"/>
</form>
<% String LoginStatus=(String)session.getAttribute("LoginStatus");
if("1".equals(LoginStatus)){
String username=(String)session.getAttribute("username");
String token=(String)session.getAttribute("token");
out.print("<h1>"+username+"</h1>");
out.print("<h1>"+token+"</h1>");
}
else if("0".equals(LoginStatus)){
out.print("<h1>登陆失败<h1>");
}
%>
</body>
</html>
controller代码如下
@Controller
public class AudioController {
@RequestMapping(value = "/login")
public String login(){
return "login";
}
//登录
@RequestMapping(value = "/checkLogin", method=RequestMethod.POST)
public String checkLogin(@RequestParam(value="account") String account,
@RequestParam(value="password") String password,
HttpServletRequest req,HttpServletResponse res){
HttpSession session=req.getSession();
LoginEntity loginEntity=new LoginEntity(account,password);
User user=UserDao.findUserByLoginEntity(loginEntity);
if(user==null){
session.setAttribute("LoginStatus","0");
}
else{
session.setAttribute("LoginStatus","1");
session.setAttribute("username", user.getUsername());
session.setAttribute("token", user.getToken());
}
return "forward:login";
}
}
运行项目,在浏览器输入如下url将会出现以下画面
测试一下错误的登录账号
正确输入登录账号后