Jdbc:
当java需要访问数据库数据交互时,一共需要几步进行连接。
1.在Pom文件引入Mysql驱动依赖
2.Class.forName注册驱动(在新版Mybatis中在getConnection中已经进行注册驱动,不用单独注册了)
3.获取一个Connection链接
4.创建一个Statement对象
5.execute()方法执行sql,execute方法返回一个ResultSet结果集
6.通过ResultSet获取数据,给POJO的属性赋值
7.最后关闭数据库相关资源,包括Result,Statement,Connection.关闭顺序与开启顺序相反
jdbc代码:
public class JdbcTest {
@Test
public void testJdbc() throws IOException {
Connection conn = null;
Statement stmt = null;
Blog blog = new Blog();
try {
// 注册 JDBC 驱动
// Class.forName("com.mysql.jdbc.Driver");
// 打开连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/gp-mybatis", "root", "123456");
// 执行查询
stmt = conn.createStatement();
String sql = "SELECT bid, name, author_id FROM blog where bid = 1";
ResultSet rs = stmt.executeQuery(sql);
// 获取结果集
while (rs.next()) {
Integer bid = rs.getInt("bid");
String name = rs.getString("name");
Integer authorId = rs.getInt("author_id");
blog.setAuthorId(authorId);
blog.setBid(bid);
blog.setName(name);
}
System.out.println(blog);
rs.close();
stmt.close();
conn.close();
} catch (SQLException se) {
se.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (stmt != null) stmt.close();
} catch (SQLException se2) {
}
try {
if (conn != null) conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
}
/**
* 原生JDBC的批量操作方式 ps.addBatch()
* @throws IOException
*/
@Test
public void testJdbcBatch() throws IOException {
Connection conn = null;
PreparedStatement ps = null;
try {
Long start = System.currentTimeMillis();
// 打开连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true", "root", "123456");
ps = conn.prepareStatement(
"INSERT into blog values (?, ?, ?)");
for (int i = 1000; i < 101000; i++) {
Blog blog = new Blog();
ps.setInt(1, i);
ps.setString(2, String.valueOf(i)+"");
ps.setInt(3, 1001);
ps.addBatch();
}
ps.executeBatch();
// conn.commit();
ps.close();
conn.close();
Long end = System.currentTimeMillis();
System.out.println("cost:"+(end -start ) +"ms");
} catch (SQLException se) {
se.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (ps != null) ps.close();
} catch (SQLException se2) {
}
try {
if (conn != null) conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
}
}
orm框架:
因为最早时候使用的全自动的orm框架,使用早年的hibernate,会将所有表和类进行完全映射,包括底层也有一些优化(一级缓存,二级缓存)等,但因为在业务比较复杂的时候无法进行对sql优化,后出现了mybatis框架,最早时候叫ibatis,从2010年后更名为Mybatis。Mybatis作者也加入了英雄联盟的团队。
Mybatis配置文件:
<?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"></properties>
<settings>
<!-- 打印查询语句 -->
<setting name="logImpl" value="STDOUT_LOGGING" />
<!-- 控制全局缓存(二级缓存),默认 true-->
<setting name="cacheEnabled" value="true"/>
<!-- 延迟加载的全局开关。当开启时,所有关联对象都会延迟加载。默认 false -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 当开启时,任何方法的调用都会加载该对象的所有属性。默认 false,可通过select标签的 fetchType来覆盖-->
<setting name="aggressiveLazyLoading" value="true"/>
<!-- Mybatis 创建具有延迟加载能力的对象所用到的代理工具,默认JAVASSIST -->
<!--<setting name="proxyFactory" value="CGLIB" />-->
<!-- STATEMENT级别的缓存,使一级缓存,只针对当前执行的这一statement有效 -->
<!--
<setting name="localCacheScope" value="STATEMENT"/>
-->
<setting name="localCacheScope" value="SESSION"/>
</settings>
<typeAliases>
<typeAlias alias="blog" type="com.gupaoedu.domain.Blog" />
</typeAliases>
<!-- <typeHandlers>
<typeHandler handler="com.gupaoedu.type.MyTypeHandler"></typeHandler>
</typeHandlers>-->
<!-- 对象工厂 -->
<!-- <objectFactory type="com.gupaoedu.objectfactory.GPObjectFactory">
<property name="gupao" value="666"/>
</objectFactory>-->
<!-- <plugins>
<plugin interceptor="com.gupaoedu.interceptor.SQLInterceptor">
<property name="gupao" value="betterme" />
</plugin>
<plugin interceptor="com.gupaoedu.interceptor.MyPageInterceptor">
</plugin>
</plugins>-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/><!-- 单独使用时配置成MANAGED没有事务 -->
<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="BlogMapper.xml"/>
<mapper resource="BlogMapperExt.xml"/>
</mappers>
</configuration>
Mybatis代码:
public class MyBatisTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void prepare() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
/**
* 使用 MyBatis API方式
* @throws IOException
*/
@Test
public void testStatement() throws IOException {
SqlSession session = sqlSessionFactory.openSession();
try {
Blog blog = (Blog) session.selectOne("com.gupaoedu.mapper.BlogMapper.selectBlogById", 1);
System.out.println(blog);
} finally {
session.close();
}
}
/**
* 通过 SqlSession.getMapper(XXXMapper.class) 接口方式
* @throws IOException
*/
@Test
public void testSelect() throws IOException {
SqlSession session = sqlSessionFactory.openSession(); // ExecutorType.BATCH
try {
BlogMapper mapper = session.getMapper(BlogMapper.class);
Blog blog = mapper.selectBlogById(1);
System.out.println(blog);
} finally {
session.close();
}
}
@Test
public void testSelectList() throws IOException {
SqlSession session = sqlSessionFactory.openSession(); // ExecutorType.BATCH
try {
BlogMapper mapper = session.getMapper(BlogMapper.class);
Blog blog = new Blog();
blog.setName("改行");
List<Blog> list1 = new ArrayList<Blog>();
List<Blog> list2 = new ArrayList<Blog>();
list1 = mapper.selectBlogListIf(blog);
list2 = mapper.selectBlogListChoose(blog);
} finally {
session.close();
}
}
/**
* 动态SQL批量插入
* @throws IOException
*/
@Test
public void testInsert() throws IOException {
SqlSession session = sqlSessionFactory.openSession();
try {
BlogMapper mapper = session.getMapper(BlogMapper.class);
Blog blog = new Blog();
blog.setBid(1688);
blog.setName("测试插入");
blog.setAuthorId(1111);
System.out.println(mapper.insertBlog(blog));
session.commit();
} finally {
session.close();
}
}
/**
* 动态SQL批量删除
* @throws IOException
*/
@Test
public void testDelete() throws IOException {
SqlSession session = sqlSessionFactory.openSession();
try {
BlogMapper mapper = session.getMapper(BlogMapper.class);
List<Blog> list = new ArrayList<Blog>();
Blog blog1 = new Blog();
blog1.setBid(666);
list.add(blog1);
Blog blog2 = new Blog();
blog2.setBid(777);
list.add(blog2);
mapper.deleteByList(list);
} finally {
session.close();
}
}
/**
* 单条更新
* @throws IOException
*/
@Test
public void testUpdate() throws IOException {
SqlSession session = sqlSessionFactory.openSession();
try {
BlogMapper mapper = session.getMapper(BlogMapper.class);
Blog newBlog = new Blog();
newBlog.setBid(333);
newBlog.setName("修改以后的名字");
mapper.updateByPrimaryKey(newBlog);
session.commit();
} finally {
session.close();
}
}
/**
* 动态SQL批量更新
* @throws IOException
*/
@Test
public void testUpdateBlogList() throws IOException {
SqlSession session = sqlSessionFactory.openSession();
try {
BlogMapper mapper = session.getMapper(BlogMapper.class);
List<Blog> list = new ArrayList<Blog>();
Blog blog1 = new Blog();
blog1.setBid(666);
blog1.setName("newName666");
blog1.setAuthorId(666666);
list.add(blog1);
Blog blog2 = new Blog();
blog2.setBid(777);
blog2.setName("newName777");
blog2.setAuthorId(777777);
list.add(blog2);
mapper.updateBlogList(list);
session.commit();
} finally {
session.close();
}
}
/**
* # 和 $ 的区别
* @throws IOException
*/
@Test
public void testSelectByBean() throws IOException {
SqlSession session = sqlSessionFactory.openSession();
try {
BlogMapper mapper = session.getMapper(BlogMapper.class);
Blog queryBean = new Blog();
queryBean.setName("MySQL从入门到改行");
List<Blog> blog = mapper.selectBlogByBean(queryBean);
System.out.println("查询结果:"+blog);
} finally {
session.close();
}
}
/**
* 逻辑分页
* @throws IOException
*/
@Test
public void testSelectByRowBounds() throws IOException {
SqlSession session = sqlSessionFactory.openSession();
try {
BlogMapper mapper = session.getMapper(BlogMapper.class);
int start = 0; // offset
int pageSize = 5; // limit
RowBounds rb = new RowBounds(start, pageSize);
List<Blog> list = mapper.selectBlogList(rb); // 使用逻辑分页
for(Blog b :list){
System.out.println(b);
}
} finally {
session.close();
}
}
/**
* Mapper.xml的继承性
* @throws IOException
*/
@Test
public void testMapperExt() throws IOException {
SqlSession session = sqlSessionFactory.openSession();
try {
BlogMapperExt mapper = session.getMapper(BlogMapperExt.class);
Blog blog = mapper.selectBlogByName("MySQL从入门到改行");
System.out.println(blog);
// 继承了父Mapper的方法
Blog blog1 = mapper.selectBlogById(1);
System.out.println(blog1);
} finally {
session.close();
}
}
/**
* 一对一,一篇文章对应一个作者
* 嵌套结果,不存在N+1问题
*/
@Test
public void testSelectBlogWithAuthorResult() throws IOException {
SqlSession session = sqlSessionFactory.openSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
BlogAndAuthor blog = mapper.selectBlogWithAuthorResult(1);
System.out.println("-----------:"+blog);
}
/**
* 一对一,一篇文章对应一个作者
* 嵌套查询,会有N+1的问题
*/
@Test
public void testSelectBlogWithAuthorQuery() throws IOException {
SqlSession session = sqlSessionFactory.openSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
BlogAndAuthor blog = mapper.selectBlogWithAuthorQuery(1);
System.out.println("-----------:"+blog.getClass());
// 如果开启了延迟加载(lazyLoadingEnabled=true),会在使用的时候才发出SQL
// equals,clone,hashCode,toString也会触发延迟加载
System.out.println("-----------调用toString方法:"+blog);
System.out.println("-----------getAuthor:"+blog.getAuthor().toString());
// 如果 aggressiveLazyLoading = true ,也会触发加载,否则不会
//System.out.println("-----------getName:"+blog.getName());
}
/**
* 一对多关联查询:一篇文章对应多条评论
* @throws IOException
*/
@Test
public void testSelectBlogWithComment() throws IOException {
SqlSession session = sqlSessionFactory.openSession();
try {
BlogMapper mapper = session.getMapper(BlogMapper.class);
BlogAndComment blog = mapper.selectBlogWithCommentById(1);
System.out.println(blog);
} finally {
session.close();
}
}
/**
* 多对多关联查询:作者的文章的评论
* @throws IOException
*/
@Test
public void testSelectAuthorWithBlog() throws IOException {
SqlSession session = sqlSessionFactory.openSession();
try {
BlogMapper mapper = session.getMapper(BlogMapper.class);
List<AuthorAndBlog> authors = mapper.selectAuthorWithBlog();
for (AuthorAndBlog author : authors){
System.out.println(author);
}
} finally {
session.close();
}
}
}
配置文件:
分为全局配置文件和mapper映射器。
全局配置:xml等基础配置文件,从头到尾都要用的
mapper映射器:针对增删改查的操作。