系列博文：
JavaWeb_(Mybatis框架)JDBC操作数据库和Mybatis框架操作数据库区别_一 传送门
JavaWeb_(Mybatis框架)使用Mybatis对表进行增、删、改、查操作_二 传送门
JavaWeb_(Mybatis框架)Mapper动态代理开发_三 传送门
JavaWeb_(Mybatis框架)主配置文件介绍_四 传送门
JavaWeb_(Mybatis框架)输入和输出参数_五 传送门
JavaWeb_(Mybatis框架)关联查询_六传送门 传送门
JavaWeb_(Mybatis框架)动态sql_七传送门 传送门
准备数据库
/*
SQLyog Professional v12.08 (64 bit)
MySQL - 5.5.49 : Database - ssm_mybatis
*********************************************************************
*/ /*!40101 SET NAMES utf8 */; /*!40101 SET SQL_MODE=''*/; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`ssm_mybatis` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `ssm_mybatis`; /*Table structure for table `country` */ DROP TABLE IF EXISTS `country`; CREATE TABLE `country` (
`c_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '国家id',
`c_countryname` varchar(128) NOT NULL COMMENT '国家名称',
`c_capital` varchar(128) DEFAULT NULL COMMENT '国家首都名称',
PRIMARY KEY (`c_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /*Data for the table `country` */ insert into `country`(`c_id`,`c_countryname`,`c_capital`) values (1,'中国','北京'),(3,'美国','华盛顿'),(4,'英国','伦敦'),(5,'日本','东京'); /*Table structure for table `user` */ DROP TABLE IF EXISTS `user`; CREATE TABLE `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',
PRIMARY KEY (`u_id`),
KEY `FK_user_cid` (`u_cid`),
CONSTRAINT `FK_user_cid` FOREIGN KEY (`u_cid`) REFERENCES `country` (`c_Id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8; /*Data for the table `user` */ insert into `user`(`u_id`,`u_username`,`u_password`,`u_sex`,`u_createTime`,`u_cid`) values (1,'老王','','',NULL,1),(2,'jack','','',NULL,3),(3,'alice','ali111','',NULL,3),(4,'王司机','','',NULL,1),(5,'anna','ali111','',NULL,4),(6,'李师傅','','',NULL,1),(7,'漩涡鸣人','','',NULL,5),(8,'娜美','','',NULL,5),(9,'王五','abc','',NULL,1),(10,'老赵',NULL,NULL,NULL,NULL),(11,'老孙',NULL,NULL,NULL,NULL),(12,'98k',NULL,NULL,NULL,NULL); /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
ssm_mybatis.sql
JDBC操作数据库
1.链接对象
2.查询对象
3.结果集对象
4.加载数据库驱动
5.获取链接
6.获取statement
7.查询 输出结果
8.关闭资源链接
package com.Gary.test; import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException; public class JdbcTest { public static void main(String[] args) { //1.链接对象
Connection con = null; //2.查询对象
PreparedStatement ps = null; //3.结果集对象
ResultSet rs = null; try {
//4.加载数据库驱动
Class.forName("com.mysql.jdbc.Driver"); //5.获取链接
con =DriverManager.getConnection("jdbc:mysql://localhost:3306/ssm_mybatis","root","123456"); String sql = "SELECT * FROM user WHERE u_sex = ?"; //6.获取statement
ps = con.prepareStatement(sql);
ps.setString(1, "0"); //7.查询 输出结果
rs = ps.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 {
//8.关闭资源链接
if(rs != null) {
try {
rs.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
if(ps !=null) {
try {
ps.close();
} catch (SQLException e) { e.printStackTrace();
}
}
if(con != null) {
try {
con.close();
} catch (SQLException e) { e.printStackTrace();
}
}
} } }
JdbcTest.java
使用JDBC操作数据库不足：频繁开启数据库链接，关闭数据库链接，消耗大量资源
1、可以通过主配置文件配置连接池解决频繁创建、释放数据库连接造成的性能影响；
2、动态SQL解决JDBC中硬编码问题：
a)Where条件改变；
b)占位符位置变化；
3、可通过包装类方便的获取数据库查询结果集对象；
4、使Dao层业务逻辑和数据库访问分离更易维护和测试。
Mybatis操作数据库优势
Mybatis架构图
Mybatis框架的搭建
package com.Gary;
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 Integer 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 + "]";
}
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 Integer getU_cid() {
return u_cid;
}
public void setU_cid(Integer u_cid) {
this.u_cid = u_cid;
}
}
User.java
<?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">
<!-- 使用JDBC的事务 -->
<transactionManager type="JDBC"/>
<!-- 使用连接池链接数据库 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/ssm_mybatis"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments> <mappers>
<mapper resource="mapper/UserMapper.xml"/>
</mappers>
</configuration>
sqlMapConfig.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> </mapper>
UserMapper.xml