一、MyMyBatis工程搭建
新建Maven项目:mybatis-demo
准备数据源
1 # 删除mybatis_demo数据库 2 drop database if exists mybatis_demo; 3 4 # 创建mybatis_demo数据库 5 create database mybatis_demo; 6 7 # 使用mybatis_demo数据库 8 use mybatis_demo; 9 10 # 创建account表 11 create table user ( 12 id int auto_increment primary key, 13 username varchar(20), 14 age int, 15 score int 16 ); 17 18 # 新增数据 19 insert into user (id, username, age, score) values 20 (1,'peter', 18, 100), (2,'pedro', 24, 200), 21 (3,'jerry', 28, 500), (4,'mike', 12, 300), 22 (5,'tom', 27, 1000);
连接数据库
1 <dependencies> 2 <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> 3 <dependency> 4 <groupId>mysql</groupId> 5 <artifactId>mysql-connector-java</artifactId> 6 <version>8.0.23</version> 7 </dependency> 8 </dependencies>
代码结构图:
代码编写:
JDBCDemo.java
1 import java.sql.Connection; 2 import java.sql.DriverManager; 3 import java.sql.PreparedStatement; 4 import java.sql.ResultSet; 5 6 public class JDBCDemo { 7 public static void main(String[] args) throws Exception { 8 //1.注册驱动 9 Class.forName("com.mysql.cj.jdbc.Driver"); 10 //2.获取连接 11 Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis_demo", "root", "root"); 12 //3.获取Statement对象 13 PreparedStatement preparedStatement = connection.prepareStatement("select * from user WHERE id = ?"); 14 preparedStatement.setInt(1, 1); 15 //4.执行SQL语句返回结果集 16 ResultSet resultSet = preparedStatement.executeQuery(); 17 //5.遍历结果集 18 while (resultSet.next()) { 19 System.out.println("username: " + resultSet.getString("username")); 20 System.out.println("age: " + resultSet.getString("age")); 21 } 22 //6.释放资源 23 resultSet.close(); 24 preparedStatement.close(); 25 connection.close(); 26 } 27 }
执行结果:
引入MyBatis依赖
1 <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis --> 2 <dependency> 3 <groupId>org.mybatis</groupId> 4 <artifactId>mybatis</artifactId> 5 <version>3.5.6</version> 6 </dependency> 7 <!-- https://mvnrepository.com/artifact/ch.qos.logback/logback-classic --> 8 <dependency> 9 <groupId>ch.qos.logback</groupId> 10 <artifactId>logback-classic</artifactId> 11 <version>1.3.0-alpha5</version> 12 <scope>test</scope> 13 </dependency>
删除JDBC连接:JDBCDemo.java
新建文件:StartNoXml.java
1 import org.apache.ibatis.datasource.pooled.PooledDataSource; 2 import org.apache.ibatis.mapping.Environment; 3 import org.apache.ibatis.session.Configuration; 4 import org.apache.ibatis.session.SqlSession; 5 import org.apache.ibatis.session.SqlSessionFactory; 6 import org.apache.ibatis.session.SqlSessionFactoryBuilder; 7 import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory; 8 9 import java.sql.PreparedStatement; 10 import java.sql.ResultSet; 11 import java.sql.SQLException; 12 13 @SuppressWarnings({"SqlResolve", "SqlNoDataSourceInspection", "Duplicates"}) 14 public class StartNoXml { 15 public static void main(String[] args) throws SQLException { 16 // 准备jdbc事务类 17 JdbcTransactionFactory jdbcTransactionFactory = new JdbcTransactionFactory(); 18 // 配置数据源 19 PooledDataSource dataSource = new PooledDataSource( 20 "com.mysql.cj.jdbc.Driver", 21 "jdbc:mysql://localhost:3306/mybatis_demo?useSSL=false", 22 "root", 23 "root"); 24 // 配置环境,向环境中指定环境id、事务和数据源 25 Environment environment = new Environment.Builder("development") 26 .transactionFactory(jdbcTransactionFactory) 27 .dataSource(dataSource).build(); 28 // 新建 MyBatis 配置类 29 Configuration configuration = new Configuration(environment); 30 // 得到 SqlSessionFactory 核心类 31 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration); 32 // 开始一个 sql 会话 33 SqlSession session = sqlSessionFactory.openSession(); 34 // 得到 sql 连接并运行 sql 语句 35 PreparedStatement preStatement = session 36 .getConnection() 37 .prepareStatement("SELECT * FROM user WHERE id = ?"); 38 preStatement.setInt(1, 1); 39 ResultSet result = preStatement.executeQuery(); 40 // 验证结果 41 while (result.next()) { 42 System.out.println("username: " + result.getString("username")); 43 System.out.println("age: " + result.getString("age")); 44 } 45 // 关闭会话 46 session.close(); 47 } 48 }
执行结果:
配置文件:mybatis-config.xml
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> 3 <configuration> 4 <!-- 环境变量 --> 5 <environments default="development"> 6 <environment id="development"> 7 <!-- 事务管理器 --> 8 <transactionManager type="JDBC"/> 9 <!-- 数据源 --> 10 <dataSource type="POOLED"> 11 <property name="driver" value="com.mysql.cj.jdbc.Driver"/> 12 <property name="url" value="jdbc:mysql://localhost:3306/mybatis_demo?useSSL=false"/> 13 <property name="username" value="root"/> 14 <property name="password" value="123456"/> 15 </dataSource> 16 </environment> 17 </environments> 18 <!-- mapper配置 --> 19 <mappers> 20 <mapper class="mapper.UserMapper"/> 21 </mappers> 22 </configuration>
修改StartNoXml.java类名:StartWithXml.java
1 import org.apache.ibatis.datasource.pooled.PooledDataSource; 2 import org.apache.ibatis.io.Resources; 3 import org.apache.ibatis.mapping.Environment; 4 import org.apache.ibatis.session.Configuration; 5 import org.apache.ibatis.session.SqlSession; 6 import org.apache.ibatis.session.SqlSessionFactory; 7 import org.apache.ibatis.session.SqlSessionFactoryBuilder; 8 import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory; 9 10 import java.io.IOException; 11 import java.io.InputStream; 12 import java.sql.PreparedStatement; 13 import java.sql.ResultSet; 14 import java.sql.SQLException; 15 16 @SuppressWarnings({"SqlResolve", "SqlNoDataSourceInspection", "Duplicates"}) 17 public class StartWithXml { 18 public static void main(String[] args) throws SQLException, IOException { 19 // 读取配置文件 20 InputStream configuration = Resources.getResourceAsStream("mybatis-config.xml"); 21 // 得到 SqlSessionFactory 核心类 22 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration); 23 // 开始一个 sql 会话 24 SqlSession session = sqlSessionFactory.openSession(); 25 // 得到 sql 连接并运行 sql 语句 26 PreparedStatement preStatement = session 27 .getConnection() 28 .prepareStatement("SELECT * FROM user WHERE id = ?"); 29 preStatement.setInt(1, 1); 30 ResultSet result = preStatement.executeQuery(); 31 // 验证结果 32 while (result.next()) { 33 System.out.println("username: " + result.getString("username")); 34 System.out.println("age: " + result.getString("age")); 35 } 36 // 关闭会话 37 session.close(); 38 } 39 }
执行结果:
二、MyBatis实现Mapper配置查询
注解方式使用Mapper
代码编写:
在mybatis-config.xml配置文件中加入mapper配置
1 <!-- mapper配置 --> 2 <mappers> 3 <mapper class="mapper.UserMapper"/> 4 </mappers>
新建mapper包,新建一个UserMapper.java类
UserMapper.java
1 package mapper; 2 3 import entity.User; 4 import org.apache.ibatis.annotations.Select; 5 6 public interface UserMapper { 7 /** 8 * 通过用户id查询用户名称 9 * 10 * @param id 用户id 11 * @return 用户名称 12 */ 13 //通过#{**}的方式来查询 14 @Select("SELECT username FROM user WHERE id = #{id}") 15 String selectUsernameById(Integer id); 16 /** 17 * 通过用户id查询用户年龄 18 * 19 * @param id 用户id 20 * @return 用户年龄 21 */ 22 Integer selectUserAgeById(Integer id); 23 24 /** 25 * 通过用户id查询用户信息 26 * 27 * @param id 28 * @return 29 */ 30 User selectUserById(Integer id); 31 32 }
UserTest.java
1 import entity.User; 2 import mapper.UserMapper; 3 import org.apache.ibatis.io.Resources; 4 import org.apache.ibatis.session.SqlSession; 5 import org.apache.ibatis.session.SqlSessionFactory; 6 import org.apache.ibatis.session.SqlSessionFactoryBuilder; 7 8 import java.io.IOException; 9 import java.io.InputStream; 10 import java.sql.SQLException; 11 12 @SuppressWarnings({"SqlResolve", "SqlNoDataSourceInspection", "Duplicates"}) 13 public class UserTest { 14 public static void main(String[] args) throws IOException, SQLException { 15 // 读取配置文件 16 InputStream configuration = Resources.getResourceAsStream("mybatis-config.xml"); 17 // 得到 SqlSessionFactory 核心类 18 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration); 19 // 开始一个 sql 会话 20 SqlSession session = sqlSessionFactory.openSession(); 21 // 得到 mapper 22 UserMapper mapper = session.getMapper(UserMapper.class); 23 // 调用注解的SQL 24 String username = mapper.selectUsernameById(1); 25 System.out.println("username: " + username); 26 // 调用XML的SQL 27 Integer age = mapper.selectUserAgeById(1); 28 System.out.println("age: " + age); 29 30 // 调用通过用户id查询用户信息的方法 31 User user = mapper.selectUserById(1); 32 System.out.println(user); 33 // 关闭会话 34 session.close(); 35 } 36 }
执行结果: