系列博文：
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,'老王','123','1',NULL,1),(2,'jack','123321','1',NULL,3),(3,'alice','ali111','0',NULL,3),(4,'王司机','666','1',NULL,1),(5,'anna','ali111','0',NULL,4),(6,'李师傅','222','1',NULL,1),(7,'漩涡鸣人','233','1',NULL,5),(8,'娜美','233','0',NULL,5),(9,'王五','abc','1',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 */;
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();
}
}
}
}
}
使用JDBC操作数据库不足：频繁开启数据库链接，关闭数据库链接，消耗大量资源
1、可以通过主配置文件配置连接池解决频繁创建、释放数据库连接造成的性能影响；
2、动态SQL解决JDBC中硬编码问题：
a)Where条件改变；
b)占位符位置变化；
3、可通过包装类方便的获取数据库查询结果集对象；
4、使Dao层业务逻辑和数据库访问分离更易维护和测试。
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; } }
<?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>
<?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>