JDBC & Mybatis操作数据库的区别

JDBC操作数据库（以mysql为例）

• 基本流程
  1. 加载数据库驱动
  2. 获取连接
  3. 获取查询对象statement
  4. 查询并输出结果
• 代码示例

				//加载数据库驱动
				Class.forName("com.mysql.jdbc.Driver");
				//获取连接
				con = DriverManager.getConnection("jdbc:mysql://localhost:3306/ssm_mybatis?useSSL=false&serverTimezone=UTC", "root", "***");
				String sql = "select * from user where u_sex = ?";
				
				//获取statement
				 ps = con.prepareStatement(sql);
				 ps.setString(1, "0");
				//查询 输出结果
				 rs = ps.executeQuery();
				while(rs.next()) {
					System.out.println(rs.getString("u_id")+"	"+rs.getString("u_username")+"   "+rs.getString("u_sex"));
				}

• 存在问题
  1. 需要频繁地创建数据库连接以及释放，造成了数据库性能的浪费。可使用连接池解决
  2. sql语句是硬编码形式，不好维护。将sql语句编码到xml文件中
  3. 结果集将表字段进行硬编码。可将结果集映射为Java对象

使用Mybatis 操作数据库

• Mybatis 架构图
  

• 基本步骤

• 运行环境准备

  1. 创建sqlMapConfig.xml主配置文件；
  • sqlMapConfig文件头

	<?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">

2. 创建Mapper.xml映射文件；Mapper文件头

	<?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">

3. 导入约束包，创建对应的bean对象

• 查询步骤
  1. 读取配置文件；
  2. 创建ssqlSessionFactoryBulider；
  3. 创建sqlSessionfactory；
  4. 生产一个sqlSession；
  5. 使用session操作数据库；
• 代码示例

String resource = "sqlMapConfig.xml";
		//读取配置文件
		InputStream in = Resources.getResourceAsStream(resource);
		
		//需要ssqlSessionFactoryBulider
		SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
		
		
		//创建sqlSessionfactory
		SqlSessionFactory ssf = ssfb.build(in);
		//生产一个sqlSession
		SqlSession session = ssf.openSession();
		//操作数据库
		//要操作的sql语句，sql的参数
		User user = session.selectOne("UserMapper.selectUserById", 1);
		System.out.println(user);

• User对象

package com.my.bean;

import java.util.Date;

public class User {
	/*
	 *   `u_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `u_username` VARCHAR(64) NOT NULL COMMENT '用户名',
  `u_password` VARCHAR(64) DEFAULT NULL COMMENT '用户密码',
  `u_sex` VARCHAR(16) DEFAULT NULL COMMENT '用户性别',
  `u_createTime` DATETIME DEFAULT NULL COMMENT '用户创建时间',
  `u_cid` INT(11) DEFAULT NULL COMMENT '用户国家id',
	 */
	private Integer u_id;
	private String u_username;
	private String u_password;
	private String u_sex;
	private Date u_createTime;
	private Integer u_cid;
	public Integer getU_id() {
		return u_id;
	}
	public User() {
		super();
	}
	public User(Integer u_id, String u_username, String u_password, String u_sex, Date u_createTime, Integer 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 void setU_id(Integer u_id) {
		this.u_id = u_id;
	}
	public String getU_username() {
		return u_username;
	}
	@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 + "]";
	}
	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 Integer getU_cid() {
		return u_cid;
	}
	public void setU_cid(Integer u_cid) {
		this.u_cid = u_cid;
	}
}

• 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>
	<!-- 读取配置文件 -->
	<properties resource="db.properties"/>
	
	<typeAliases>
<!-- 		<typeAlias type="com.my.bean.User" alias="user"/> -->
		<package name="com.my.bean"/>
	</typeAliases>
	
  <!-- 集成spring后不使用 -->
  <environments default="development">
    <environment id="development">
      <!-- 使用jdbc的事务 -->
      <transactionManager type="JDBC"/>
      <!-- 连接数据库 -->
      <dataSource type="POOLED">
        <property name="driver" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
<!--     <mapper resource="mapper/UserMapper.xml"/> -->
<!-- 	<mapper url="file:\\\D:\eclipse_workplace\sts4_workplace\ssm_mybatis\src\mapper\UserMapper.xml"/> -->
<!-- 	<mapper class="com.my.mapper.UserMapper"/> -->
<!-- 推荐使用 -->
	<package name="com.my.mapper"/>
  </mappers>
</configuration>

• Mapper.xml中配置sql语句

<sql id="myselect">
	  select * from user
</sql>