为了优化JDBC操作数据库,通过Mybatis对数据库进行操作,主要实现Mybati核心配置文件以及编写sql映射文件,解决硬编码问题和sql语句后期维护问题,主要步骤如下:
1.环境准备
Mysql中创建名为db1的数据库,在数据库db1中创建user表;
create database db1;
use db1;
drop table if exists user;
create table user(
id int primary key auto_increment,
username varchar(20),
password varchar(20)
);
INSERT INTO user VALUES(1,'张三','111');
INSERT INTO user VALUES(2,'李四','222');
INSERT INTO user VALUES(3,'王五','333');
2.创建Javaweb项目模块,导入需要的依赖坐标
2.1创建项目
2.2在pom.xml文件中导入需要的依赖,junit、mysql、mybatis、logback-classic
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<!--MYBATIS依赖-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.5</version>
</dependency>
<!-- mysql驱动依赖连接数据库-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.25</version>
</dependency>
<!--编写日志依赖,logback还有一个配置文件需要放在resources下-->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.2.3</version>
</dependency>
3.编写Mybatis核心配置文件
在resources下创建mybatis-config.xml核心配置文件,入门配置方法官网链接:
https://mybatis.org/mybatis-3/zh/index.html
3.1Mybatis核心设置,配置数据库信息,源代码如下:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<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}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="org/mybatis/example/BlogMapper.xml"/>
</mappers>
</configuration>
例中代码:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<!--数据库连接信息-->
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:// 192.111.1.111:3306/mybatis?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="1111"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--加载sql映射文件-->
<mapper resource="UserMapper.xml"/>
</mappers>
</configuration>
需要修改的红框部分:注意IP地址更换,sql语句映射文件路径等第4步完成后在修改;
4.编写sql映射文件
sql映射方法在mybatis官网下均有说明,源码如下:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.mybatis.example.BlogMapper">
<select id="selectBlog" resultType="Blog">
select * from Blog where id = #{id}
</select>
</mapper>
在resources下创建Usermapper.xml sql映射文件,namespace为名称空间,id为sql方法id,resultType为sql语句返回结果的类型,在5.1中定义一个User类,通过名称空间.方法id进行sql语句调用,查找user表,实现对数据库的操作。
本例中的代码如下:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="demo1">
<select id="selectALL" resultType="com.FZU.pojo.User">
select * from user
</select>
</mapper>
5.编码
1)在pojo包中定义User类,实现Getter()、Setter()和toString()方法
package com.FZU.pojo;
public class User {
private int id;
private String userName;
private String passWord;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
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;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", userName='" + userName + '\'' +
", passWord='" + passWord + '\'' +
'}';
}
}
2) 加载核心配置文件,获取sqlSessionFactory对象
3) 获取sqlSession对象
//2.获取sqlsession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
4) 执行sql语句,返回值User类
//3.执行sql语句
List<Object> objects = sqlSession.selectList("demo1.selectALL");
红框部分即调用Usermapper.xml文件中 空间名.id名
5)对返回值进行显示
//4.对返回值进行操作,显示
System.out.println(objects);
6) 释放资源
//5.释放资源
sqlSession.close();
运行结果如下:
配置文件完整代码如下:
package com.FZU;
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 java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class mybatisDemo {
public static void main(String[] args) throws IOException {
//1.加载mybatis核心配置文件,获取SqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2.获取sqlsession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//3.执行sql语句
List<Object> objects = sqlSession.selectList("demo1.selectALL");
//4.对返回值进行操作,显示
System.out.println(objects);
//5.释放资源
sqlSession.close();
}
}
注意点:
1.修改ip地址及数据库
2.程序中路径问题
需要源码加百度云: