使用mybatis连接mysql数据库的一个完整实例

项目完整结构:

pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
<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/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>

<groupId>com.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.16</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.2</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
</project>

Student类:

package com.jnshu.student;

public class Student {
private int id;
private String name;
private String major;
private long admission_time;
private String graduate_school;
private long online_id;
private String diary_link;
private String wish;
private String guide;
private long create_time;
private long update_time;

public Student() {
}

public Student(String name, String major, long admission_time, String graduate_school, long online_id, String diary_link, String wish, String guide,long create_time,long update_time) {
this.name = name;
this.major = major;
this.admission_time = admission_time;
this.graduate_school = graduate_school;
this.online_id = online_id;
this.diary_link = diary_link;
this.wish = wish;
this.guide = guide;
this.create_time = create_time;
this.update_time=update_time;
}

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 getMajor() {
return major;
}

public void setMajor(String major) {
this.major = major;
}

public long getAdmission_time() {
return admission_time;
}

public void setAdmission_time(long admission_time) {
this.admission_time = admission_time;
}

public String getGraduate_school() {
return graduate_school;
}

public void setGraduate_school(String graduate_school) {
this.graduate_school = graduate_school;
}

public long getOnline_id() {
return online_id;
}

public void setOnline_id(int online_id) {
this.online_id = online_id;
}

public String getDiary_link() {
return diary_link;
}

public void setDiary_link(String diary_link) {
this.diary_link = diary_link;
}

public String getWish() {
return wish;
}

public void setWish(String wish) {
this.wish = wish;
}

public String getGuide() {
return guide;
}

public void setGuide(String guide) {
this.guide = guide;
}

public long getCreate_time() {
return create_time;
}

public void setCreate_time(long create_time) {
this.create_time = create_time;
}

public long getUpdate_time() {
return update_time;
}

public void setUpdate_time(long update_time) {
this.update_time = update_time;
}

@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", major='" + major + '\'' +
", admission_time='" + admission_time + '\'' +
", graduate_school='" + graduate_school + '\'' +
", online_id=" + online_id +
", diary_link='" + diary_link + '\'' +
", wish='" + wish + '\'' +
", guide='" + guide + '\'' +
", create_time='" + create_time + '\'' +
", update_time='" + update_time + '\'' +
'}';
}
}

StudentMapper:

package com.jnshu.studentMapper;

import com.jnshu.student.Student;

public interface StudentMapper {
public Student selectUserByID(int id);
public Student selectUsersByName(String name);
public Student selectUsersById(int id);
public int addUser(Student student);
public int updateUser(Student student);
public int deleteUser(int id);
}

StudentMapper.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.jnshu.studentMapper.StudentMapper">

<select id="selectUserByID" parameterType="int" resultType="Student">
select * from student where id = #{id}
</select>

<!-- 定义的resultMap，可以解决类的属性名和数据库列名不一致的问题-->
<!-- <resultMap type="Student" id="userResultMap">
<id property="id" column="user_id" />
<result property="userName" column="user_userName" />
<result property="userAge" column="user_userAge" />
<result property="userAddress" column="user_userAddress" />
</resultMap> -->

<!-- 返回list的select语句，注意 resultMap的值是指向前面定义好的 -->
<!-- <select id="selectUsersByName" parameterType="string" resultMap="userResultMap">
select * from student where student.userName = #{userName}
</select> -->

<select id="selectUsersByName" parameterType="string" resultType="Student">
select * from student where name = #{name}
</select>
<select id="selectUsersById" parameterType="int" resultType="Student">
select * from student where id = #{id}
</select>

<!--useGeneratedKeys设置为"true"表明要MyBatis获取由数据库自动生成的主键；keyProperty="id"指定把获取到的主键值注入到User的id属性-->
<insert id="addUser" parameterType="Student"
useGeneratedKeys="true" keyProperty="id">
insert into student (name,major,admission_time,graduate_school,
online_id,diary_link,wish,guide,create_time,update_time)
values(#{name},#{major},#{admission_time},
#{graduate_school},#{online_id},#{diary_link},#{wish},
#{guide},#{create_time},#{update_time})
</insert>
<update id="updateUser" parameterType="Student" >
update student set wish=#{wish} where name=#{name}
</update>
<delete id="deleteUser" parameterType="int">
delete from student where id=#{id}
</delete>
</mapper>

Configuration.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>
<!-- mybatis别名定义 -->
<typeAliases>
<typeAlias alias="Student" type="com.jnshu.student.Student"/>
</typeAliases>
<!--配置数据源-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/wang" />
<property name="username" value="root"/>
<property name="password" value="123456wq"/>
</dataSource>
</environment>
</environments>

<!-- mybatis的mapper文件，每个xml配置文件对应一个接口 -->
<mappers>
<mapper resource="StudentMapper.xml"/>
</mappers>
</configuration>

log4j.properties:

log4j.rootLogger=INFO,CONSOLE,file
log4j.logger.cn.smbms.dao=debug
log4j.logger.com.ibatis=debug
log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=debug
log4j.logger.com.ibatis.common.jdbc.ScriptRunner=debug
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=debug
log4j.logger.java.sql.Connection=debug
log4j.logger.java.sql.Statement=debug
log4j.logger.java.sql.PreparedStatement=debug
log4j.logger.java.sql.ResultSet=debug
log4j.logger.org.tuckey.web.filters.urlrewrite.UrlRewriteFilter=debug
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.Threshold=info
log4j.appender.CONSOLE.Target=System.out
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern= [%p] %d %c - %m%n
log4j.appender.file=org.apache.log4j.DailyRollingFileAppender
log4j.appender.file.DatePattern=yyyy-MM-dd
log4j.appender.file.File=log.log
log4j.appender.file.Append=true
log4j.appender.file.Threshold=error
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{yyyy-M-d HH:mm:ss}%x[%5p](%F:%L) %m%n
log4j.logger.com.opensymphony.xwork2=error

测试类TestMybatis:

import com.jnshu.student.Student;
import com.jnshu.studentMapper.StudentMapper;
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 org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.junit.Test;
import java.io.Reader;

public class TestMybatis {
private static SqlSessionFactory sqlSessionFactory;
private static Reader reader;
private static final Logger log= LogManager.getLogger(TestMybatis.class);
static {
try {
reader = Resources.getResourceAsReader("Configuration.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (Exception e) {
e.printStackTrace();
}
}
public static SqlSessionFactory getSession() {
return sqlSessionFactory;
}

@Test
//增加一个用户返回id
public void addUser() {
Student student = new Student();
student.setWish("place");
student.setName("test_add");
student.setOnline_id(304);
SqlSession session = sqlSessionFactory.openSession();
try {
StudentMapper userOperation = session
.getMapper(StudentMapper.class);
userOperation.addUser(student);
session.commit();
log.info("新增用户ID：" + student.getId());
} finally {
session.close();
}
}
@Test
//更改立愿
public void updateUser() {
SqlSession session = sqlSessionFactory.openSession();
try {
StudentMapper userOperation = session
.getMapper(StudentMapper.class);
Student student = userOperation.selectUserByID(54);
if (student != null) {
student.setWish("写代码");
int result = userOperation.updateUser(student);
session.commit();
boolean flag = result>0?true:false;
log.info(flag);
}
else {
log.info("false");
}
} finally {
session.close();
}
}
@Test
//根据id查询
public void getUserByID() {
SqlSession session = sqlSessionFactory.openSession();
try {
StudentMapper userOperation = session.getMapper(StudentMapper.class);
//session.getMapper()的作用.已知Mapper为映射器.
Student student = userOperation.selectUserByID(54);
if (student != null) {
log.info(student.toString());
}
} finally {
session.close();
}
}
@Test
//根据姓名查询
public void getUserByName(){
SqlSession session = sqlSessionFactory.openSession();
try {
StudentMapper userOperation = session.getMapper(StudentMapper.class);
//session.getMapper()的作用.已知Mapper为映射器.
Student student = userOperation.selectUsersByName("test_add");
if (student != null) {
log.info(student.toString());
}
} finally {
session.close();
}
}
@Test
//根据id删除数据
public void deleteUser() {
SqlSession session = sqlSessionFactory.openSession();
try {
StudentMapper userOperation = session
.getMapper(StudentMapper.class);
int result = userOperation.deleteUser(54);
session.commit();
boolean flag = result>0?true:false;
log.info(flag);
} finally {
session.close();
}
}
}