Mybatis和Jdbc操作数据库的比较
-
简介:
是一款优秀的持久层框架,它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。
-
使用JDBC操作数据库(以mysql数据库为例)
-
流程
-
导入MySQL数据库的驱动jar包,并添加到环境中
-
注册驱动
//注册驱动 Class.forName("com.mysql.jdbc.Driver");
-
获取数据库连接对象
//获取连接对象 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ssm_mybatis?serverTimezone=UTC", "root", "root");
使用DriverManager类的静态方法getConnection()获取数据库连接对象,需要三个参数:
- 数据库表的url
- 数据库连接用户名
- 数据库连接密码
-
获取PrepareStatement对象(执行sql语句的对象)
//获取PrepareStatement对象 String sql = "select * from user where u_sex = ?"; //预设被执行的sql PreparedStatement pStatement = conn.prepareStatement(sql); pStatement.setString(1, "1"); //设置占位符的数据,参数1为第几个占位符,参数2为占位符的数据
使用数据库连接对象的prepareStatement()方法获得PreparedStatement对象,需要一个sql语句作为参数
-
获得结果集
//获取结果集 ResultSet rs = pStatement.executeQuery();
PreparedStatement对象调用executeQuery()方法进行DQL语句的执行,当然还有DDL,DML的执行方法
-
操作结果集
//遍历结果集 while(rs.next()) { System.out.println(rs.getString("u_id") + " " + rs.getString("u_username") + " " + rs.getString("u_sex")); }
结果集对象调用next()方法可以判断是否还有结果没被处理。结果集的getXxx()方法可以获取每一个结果中的数据,Xxx代表数据的类型,参数为数据库表中列名
-
关闭结果集对象
if(rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } }
-
关闭PrepareStatement对象
if(pStatement != null) { try { pStatement.close(); } catch (SQLException e) { e.printStackTrace(); } }
-
关闭数据库连接对象
if(conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } }
-
-
实例:查询数据库中user表的某些数据,并显示部分内容
-
建立与表对应的Java模型类
User.java
/* * 用户类 */ public class User { private Integer u_id; private String u_username; private String u_password; private String u_sex; private String u_createTime; private String u_cid; public User() { super(); } public User(Integer u_id, String u_username, String u_password, String u_sex, String u_createTime, String u_cid) { super(); this.u_id = u_id; this.u_username = u_username; this.u_password = u_password; this.u_sex = u_sex; this.u_createTime = u_createTime; this.u_cid = u_cid; } public Integer getU_id() { return u_id; } public void setU_id(Integer u_id) { this.u_id = u_id; } public String getU_username() { return u_username; } public void setU_username(String u_username) { this.u_username = u_username; } public String getU_password() { return u_password; } public void setU_password(String u_password) { this.u_password = u_password; } public String getU_sex() { return u_sex; } public void setU_sex(String u_sex) { this.u_sex = u_sex; } public String getU_createTime() { return u_createTime; } public void setU_createTime(String u_createTime) { this.u_createTime = u_createTime; } public String getU_cid() { return u_cid; } public void setU_cid(String u_cid) { this.u_cid = u_cid; } @Override public String toString() { return "User [u_id=" + u_id + ", u_username=" + u_username + ", u_password=" + u_password + ", u_sex=" + u_sex + ", u_createTime=" + u_createTime + ", u_cid=" + u_cid + "]"; } }
-
使用jdbc进行查询
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import org.junit.Test; public class JdbcTest { @Test public void test1() { Connection conn = null; PreparedStatement pStatement = null; ResultSet rs = null; try { //注册驱动 Class.forName("com.mysql.jdbc.Driver"); //获取连接对象 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ssm_mybatis?serverTimezone=UTC", "root", "root"); //获取statement对象 String sql = "select * from user where u_sex = ?"; //预设被执行的sql pStatement = conn.prepareStatement(sql); pStatement.setString(1, "1"); //设置占位符的数据,参数1为第几个占位符,参数2为占位符的数据 //获取结果集 rs = pStatement.executeQuery(); //操作结果集 while(rs.next()) { System.out.println(rs.getString("u_id") + " " + rs.getString("u_username") + " " + rs.getString("u_sex")); } } catch (Exception e) { e.printStackTrace(); } finally { //释放结果集资源 if(rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } //释放statement对象 if(pStatement != null) { try { pStatement.close(); } catch (SQLException e) { e.printStackTrace(); } } //释放数据库连接对象 if(conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }
-
-
不足
-
需要对资源对象进行管理
-
sql语句被硬编码在Java类中,不方便修改
-
需要对结果集进行再封装处理
-
-
- 使用Mybatis操作数据库
-
流程
-
创建并配置配置文件SqlMapConfig.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">
-
配置数据库源
<environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <!-- 数据库连接池,由mybaits管理 --> <dataSource type="POOLED"> <!-- 配置数据源的信息 --> <property name="driver" value="com.mysql.jdbc.Driver"/> <!-- 驱动类 --> <property name="url" value="jdbc:mysql://localhost:3306/ssm_mybatis?serverTimezone=UTC"/> <!-- 数据库源 --> <property name="username" value="root"/> <!-- 登录数据库的用户 --> <property name="password" value="root"/> <!-- 登录密码 --> </dataSource> </environment> </environments>
-
配置sql映射文件
<!-- 配置具体的sql映射文件 --> <mappers> <mapper resource="mapper/user_mapper.xml"/> </mappers>
-
简洁的、完整的配置文件
<?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> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <!-- 数据库连接池,由mybaits管理 --> <dataSource type="POOLED"> <!-- 配置数据源的信息 --> <property name="driver" value="com.mysql.jdbc.Driver"/> <!-- 驱动类 --> <property name="url" value="jdbc:mysql://localhost:3306/ssm_mybatis?serverTimezone=UTC"/> <!-- 数据库源 --> <property name="username" value="root"/> <!-- 登录数据库的用户 --> <property name="password" value="root"/> <!-- 登录密码 --> </dataSource> </environment> </environments> <!-- 配置具体的sql映射文件 --> <mappers> <mapper resource="mapper/user_mapper.xml"/> </mappers> </configuration>
-
-
创建sql映射文件
-
配置文件头
<?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">
-
配置sql语句的映射
<!-- namespace为命名空间,为了区别其他mapper --> <mapper namespace="userMapper"> <!-- id表示该标签的唯一标志 parameterType为参数类型,与sql语句的占位符对应的数据类型对应,用包装类型 resultType为返回值类型,使用自定义的类要用包全名,mybatis会自动将数据封装进类对象 --> <select id="selectUserById" parameterType="Integer" resultType="cn.zero.model.User"> select * from user where u_id = #{id} </select> <insert id="insert" parameterType="cn.zero.model.User"> insert into user value(#{u_id}, #{u_username}, #{u_password}, #{u_sex}, #{u_createTime}, #{u_cid}); </insert> <update id="update" parameterType="cn.zero.model.User"> update user set u_password = #{u_password}, u_cid = #{u_cid} where u_id = #{u_id} </update> <delete id="delete" parameterType="Integer"> delete from user where u_id = #{id} </delete> </mapper>
-
-
读取配置文件 SqlMapConfig.xml
//读取配置文件 String resource = "SqlMapConfig.xml"; //配置文件路径 InputStream inputStream = null; try { inputStream = Resources.getResourceAsStream(resource); } catch (IOException e) { e.printStackTrace(); }
使用mybatis提供的工具类Resources的静态方法getResourceAsStream()将配置文件放入输入流中
-
创建 SqlSessionFactoryBuilder 对象
//获得SqlSessionFactoryBuilder对象 SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
-
获取 SqlSessionFactory 对象
//获得SqlSessionFactory对象 SqlSessionFactory ssf = ssfb.build(inputStream);
使用 SqlSessionFactoryBuilder 对象的build()方法获取对象
-
获取 SqlSession 对象
//获得Sqlsession对象 SqlSession session = ssf.openSession();
使用 SqlSessionFactory 对象的openSession()方法获取对象
-
使用 SqlSession 对象执行sql映射文件中sql语句
//执行对应的sql语句 User u = session.selectOne("userMapper.selectUserById", 1);
第一个参数为sql映射文件中的namespace和id构成,可以定位到每一个sql语句
第二个参数为sql语句所需的参数
-
处理获得数据(一般只在查询时处理数据)
//显示执行结果 System.out.println(u);
-
提交事务(查询无需提交事务,其他操作需要)
//提交事务 session.commit();
-
-
实例:对数据库用户表(user)的简单CRUD
-
SqlMapConfig.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> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <!-- 数据库连接池,由mybaits管理 --> <dataSource type="POOLED"> <!-- 配置数据源的信息 --> <property name="driver" value="com.mysql.jdbc.Driver"/> <!-- 驱动类 --> <property name="url" value="jdbc:mysql://localhost:3306/ssm_mybatis?serverTimezone=UTC"/> <!-- 数据库源 --> <property name="username" value="root"/> <!-- 登录数据库的用户 --> <property name="password" value="root"/> <!-- 登录密码 --> </dataSource> </environment> </environments> <!-- 配置具体的sql映射文件 --> <mappers> <mapper resource="mapper/user_mapper.xml"/> </mappers> </configuration>
-
user_mapper.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"> <!-- namespace为命名空间,为了区别其他mapper --> <mapper namespace="userMapper"> <!-- id表示该标签的唯一标志 parameterType为参数类型,与sql语句的占位符对应的数据类型对应,用包装类型 resultType为返回值类型,使用自定义的类要用包全名,mybatis会自动将数据封装进类对象 --> <select id="selectUserById" parameterType="Integer" resultType="cn.zero.model.User"> select * from user where u_id = #{id} </select> <insert id="insert" parameterType="cn.zero.model.User"> insert into user value(#{u_id}, #{u_username}, #{u_password}, #{u_sex}, #{u_createTime}, #{u_cid}); </insert> <update id="update" parameterType="cn.zero.model.User"> update user set u_password = #{u_password}, u_cid = #{u_cid} where u_id = #{u_id} </update> <delete id="delete" parameterType="Integer"> delete from user where u_id = #{id} </delete> </mapper>
-
对应的Java模型类
package cn.zero.model; import java.util.Date; /* * 用户类 */ public class User { private Integer u_id; private String u_username; private String u_password; private String u_sex; private Date u_createTime; private String u_cid; public User() { super(); } public User(Integer u_id, String u_username, String u_password, String u_sex, Date u_createTime, String u_cid) { super(); this.u_id = u_id; this.u_username = u_username; this.u_password = u_password; this.u_sex = u_sex; this.u_createTime = u_createTime; this.u_cid = u_cid; } public Integer getU_id() { return u_id; } public void setU_id(Integer u_id) { this.u_id = u_id; } public String getU_username() { return u_username; } public void setU_username(String u_username) { this.u_username = u_username; } public String getU_password() { return u_password; } public void setU_password(String u_password) { this.u_password = u_password; } public String getU_sex() { return u_sex; } public void setU_sex(String u_sex) { this.u_sex = u_sex; } public Date getU_createTime() { return u_createTime; } public void setU_createTime(Date u_createTime) { this.u_createTime = u_createTime; } public String getU_cid() { return u_cid; } public void setU_cid(String u_cid) { this.u_cid = u_cid; } @Override public String toString() { return "User [u_id=" + u_id + ", u_username=" + u_username + ", u_password=" + u_password + ", u_sex=" + u_sex + ", u_createTime=" + u_createTime + ", u_cid=" + u_cid + "]"; } }
-
对应sql语句的执行测试
package cn.zero.test; import java.io.IOException; import java.io.InputStream; import java.util.Date; 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.junit.Test; import cn.zero.model.User; public class MybatisTest { @Test //执行根据id查询数据 public void selectById() { //读取配置文件 String resource = "SqlMapConfig.xml"; //配置文件路径 InputStream inputStream = null; try { inputStream = Resources.getResourceAsStream(resource); } catch (IOException e) { e.printStackTrace(); } //获得SqlSessionFactoryBuilder对象 SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder(); //获得SqlSessionFactory对象 SqlSessionFactory ssf = ssfb.build(inputStream); //获得Sqlsession对象 SqlSession session = ssf.openSession(); //执行对应的sql语句 User u = session.selectOne("userMapper.selectUserById", 1); //显示执行结果 System.out.println(u); } @Test //执行添加用户 public void insert() { String resource = "SqlMapConfig.xml"; InputStream in = null; //读取配置文件 try { in = Resources.getResourceAsStream(resource ); } catch (IOException e) { e.printStackTrace(); } //创建SqlSessionFactoryBuilder对象 SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder(); //使用SqlSessionFactoryBuilder对象获取SqlSessionFactory对象 SqlSessionFactory ssf = ssfb.build(in); //使用SqlSessionFactory对象获取SqlSession对象 SqlSession session = ssf.openSession(); //使用SqlSession对象执行对应sql语句 User user = new User(); user.setU_username("haha"); user.setU_password("1234"); user.setU_sex("1"); user.setU_createTime(new Date()); user.setU_cid("1"); session.insert("userMapper.insert", user); //提交事务 session.commit(); } @Test //执行更新用户 public void update() { String resource = "SqlMapConfig.xml"; InputStream in = null; //读取配置文件 try { in = Resources.getResourceAsStream(resource); } catch (IOException e) { e.printStackTrace(); } //获取SqlSessionFactoryBuilder对象 SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder(); //获取SqlSessionFactory对象 SqlSessionFactory ssf = ssfb.build(in); //获取SqlSession对象 SqlSession session = ssf.openSession(); //使用SqlSession对象执行对应sql语句 User user = new User(); user.setU_id(13); user.setU_password("1234567"); user.setU_cid("5"); session.update("userMapper.update", user); //提交事务 session.commit(); } @Test //测试删除 public void delete() { //读取配置文件 String resource = "SqlMapConfig.xml"; InputStream in = null; try { in = Resources.getResourceAsStream(resource); } catch (IOException e) { e.printStackTrace(); } //获取SqlSessionFactoryBuilder对象 SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder(); //获取SqlSessionFactory对象 SqlSessionFactory ssf = ssfb.build(in); //获取SqlSession对象 SqlSession session = ssf.openSession(); //执行sql语句 session.delete("userMapper.delete", 13); //提交事务 session.commit(); } }
-
-
特点
-
将sql语句和java代码分离,只需传相应的参数即可
-
结合了jdbc和数据库连接池的使用
-
省去了jdbc中对资源的释放管理
-
对查询的结果会进行封装到需要的java类对象中
-
-