mybatis 基础01
hibernate 比较不好的一点 就是全表映射，
比如不好定制 sql, 把表中的所有字段都 select 出来，增加网络开销 和 序列化的开销，增加了 CPU 的操作
使用 hibernate 不需要 写 sql,屏蔽了 sql, 不利于 调优
项目日志导入：
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.enjoylearing.mybatis</groupId> <artifactId>mybatis-quick-start</artifactId> <version>0.0.1-SNAPSHOT</version> <dependencies> <!-- 单元测试相关依赖 --> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-test</artifactId> <version>4.3.2.RELEASE</version> <scope>test</scope> </dependency> <!-- 日志相关依赖 --> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> <version>1.7.10</version> </dependency> <dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-classic</artifactId> <version>1.1.2</version> </dependency> <dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-core</artifactId> <version>1.1.2</version> </dependency> <!-- mysql驱动 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.18</version> </dependency> <!-- mybatis相关依赖 --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.1</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <version>2.1</version> <configuration> <source>1.7</source> <target>1.7</target> </configuration> </plugin> <plugin> <groupId>org.mybatis.generator</groupId> <artifactId>mybatis-generator-maven-plugin</artifactId> <version>1.3.2</version> <configuration> <verbose>true</verbose> <overwrite>true</overwrite> </configuration> </plugin> </plugins> </build> </project>
package com.enjoylearning.mybatis; //STEP 1. 导入sql相关的包 import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import org.junit.Test; import com.enjoylearning.mybatis.entity.TUser; public class JdbcDemo { static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true"; // Database credentials static final String USER = "root"; static final String PASS = "422525"; @Test public void QueryStatementDemo() { Connection conn = null; Statement stmt = null; List<TUser> users = new ArrayList<>(); try { // STEP 2: 注册mysql的驱动 Class.forName("com.mysql.jdbc.Driver"); // STEP 3: 获得一个连接 System.out.println("Connecting to database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); // STEP 4: 创建一个查询 System.out.println("Creating statement..."); stmt = conn.createStatement(); String userName = "lison"; String sql="SELECT * FROM t_user where user_name='"+userName+"'"; ResultSet rs = stmt.executeQuery(sql); System.out.println(stmt.toString()); // STEP 5: 从resultSet中获取数据并转化成bean while (rs.next()) { System.out.println("------------------------------"); // Retrieve by column name TUser user = new TUser(); // user.setId(rs.getInt("id")); // user.setUserName(rs.getString("user_name")); user.setRealName(rs.getString("real_name")); user.setSex(rs.getByte("sex")); user.setMobile(rs.getString("mobile")); user.setEmail(rs.getString("email")); user.setNote(rs.getString("note")); System.out.println(user.toString()); users.add(user); } // STEP 6: 关闭连接 rs.close(); stmt.close(); conn.close(); } catch (SQLException se) { // Handle errors for JDBC se.printStackTrace(); } catch (Exception e) { // Handle errors for Class.forName e.printStackTrace(); } finally { // finally block used to close resources try { if (stmt != null) stmt.close(); } catch (SQLException se2) { }// nothing we can do try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } } System.out.println("-------------------------"); System.out.println("there are "+users.size()+" users in the list!"); } @Test public void QueryPreparedStatementDemo() { Connection conn = null; PreparedStatement stmt = null; List<TUser> users = new ArrayList<>(); try { // STEP 2: 注册mysql的驱动 Class.forName("com.mysql.jdbc.Driver"); // STEP 3: 获得一个连接 System.out.println("Connecting to database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); // STEP 4: 创建一个查询 System.out.println("Creating statement..."); String sql; sql = "SELECT * FROM t_user where user_name= ? "; stmt = conn.prepareStatement(sql); stmt.setString(1, "lison"); System.out.println(stmt.toString());//打印sql ResultSet rs = stmt.executeQuery(); // STEP 5: 从resultSet中获取数据并转化成bean while (rs.next()) { System.out.println("------------------------------"); // Retrieve by column name TUser user = new TUser(); // user.setId(rs.getInt("id")); // user.setUserName(rs.getString("user_name")); user.setRealName(rs.getString("real_name")); user.setSex(rs.getByte("sex")); user.setMobile(rs.getString("mobile")); user.setEmail(rs.getString("email")); user.setNote(rs.getString("note")); System.out.println(user.toString()); users.add(user); } // STEP 6: 关闭连接 rs.close(); stmt.close(); conn.close(); } catch (SQLException se) { // Handle errors for JDBC se.printStackTrace(); } catch (Exception e) { // Handle errors for Class.forName e.printStackTrace(); } finally { // finally block used to close resources try { if (stmt != null) stmt.close(); } catch (SQLException se2) { }// nothing we can do try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } } System.out.println("-------------------------"); System.out.println("there are "+users.size()+" users in the list!"); } @Test public void updateDemo(){ Connection conn = null; PreparedStatement stmt = null; try { // STEP 2: 注册mysql的驱动 Class.forName("com.mysql.jdbc.Driver"); // STEP 3: 获得一个连接 System.out.println("Connecting to database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); // STEP 4: 启动手动提交 conn.setAutoCommit(false); // STEP 5: 创建一个更新 System.out.println("Creating statement..."); String sql = "update t_user set mobile= ? where user_name= ? "; stmt = conn.prepareStatement(sql); stmt.setString(1, "186995587411"); stmt.setString(2, "lison"); System.out.println(stmt.toString());//打印sql int ret = stmt.executeUpdate(); System.out.println("此次修改影响数据库的行数为："+ret); // STEP 6: 手动提交数据 conn.commit(); // STEP 7: 关闭连接 stmt.close(); conn.close(); } catch (SQLException se) { // Handle errors for JDBC try { conn.rollback(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } se.printStackTrace(); } catch (Exception e) { try { conn.rollback(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } e.printStackTrace(); } finally { // finally block used to close resources try { if (stmt != null) stmt.close(); } catch (SQLException se2) { }// nothing we can do try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } } } }
使用 maven 快速应用：
package com.enjoylearning.mybatis; import java.io.IOException; import java.io.InputStream; 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.Before; import org.junit.Test; import com.enjoylearning.mybatis.entity.TUser; import com.enjoylearning.mybatis.mapper.TUserMapper; public class MybatisQuickStart { private SqlSessionFactory sqlSessionFactory; @Before public void init() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); // 1.读取mybatis配置文件创SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); inputStream.close(); } @Test // 快速入门 public void quickStart() throws IOException { // 2.获取sqlSession SqlSession sqlSession = sqlSessionFactory.openSession(); // 3.获取对应mapper TUserMapper mapper = sqlSession.getMapper(TUserMapper.class); // 4.执行查询语句并返回结果 TUser user = mapper.selectByPrimaryKey(1); System.out.println(user.toString()); } }
mybatis 的配置 ： mybatis-config.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" /> <settings> <setting name="mapUnderscoreToCamelCase" value="true" /> </settings> <!--配置environment环境 --> <environments default="development"> <!-- 环境配置1，每个SqlSessionFactory对应一个环境 --> <environment id="development"> <transactionManager type="JDBC" /> <dataSource type="UNPOOLED"> <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> <!-- 映射文件，mapper的配置文件 --> <!-- <mappers> 直接映射到相应的mapper文件 <mapper resource="sqlmapper/TUserMapper.xml" /> </mappers> --> <mappers> <mapper class="com.enjoylearning.mybatis.mapper.TUserMapper" /> </mappers> </configuration>
mybatis 常用属性总结：
配置 typeAliases 别名， mapper.xml 就不需要写全路径了
typeHandler 自定义类型转换：
mybatis 有一个注册中心的概念， 可以将 java的 类型 映射成 数据库的类型
可以点进去 看一个 BigDecimalTypeHandler
/** * Copyright 2009-2015 the original author or authors. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package org.apache.ibatis.type; import java.math.BigDecimal; import java.sql.CallableStatement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** * @author Clinton Begin */ public class BigDecimalTypeHandler extends BaseTypeHandler<BigDecimal> { @Override public void setNonNullParameter(PreparedStatement ps, int i, BigDecimal parameter, JdbcType jdbcType) throws SQLException { ps.setBigDecimal(i, parameter); } @Override public BigDecimal getNullableResult(ResultSet rs, String columnName) throws SQLException { return rs.getBigDecimal(columnName); } @Override public BigDecimal getNullableResult(ResultSet rs, int columnIndex) throws SQLException { return rs.getBigDecimal(columnIndex); } @Override public BigDecimal getNullableResult(CallableStatement cs, int columnIndex) throws SQLException { return cs.getBigDecimal(columnIndex); } }
我们可以自定义 一个 TypeHandler , 继承 mybatis 实现的 接口，就可以自动进行类型转化了。
