JDBC的PreparedStatement和不使用Spring的Mybatis使用示例

JDBC的PreparedStatement的使用示例

• Mybatis内部也是基于JDBC的相关API来执行SQL操作的，如PreparedStatment，Statement等，以下为原生的JDBC的PreparedStatement的使用示例：

  public void selectBlog(){
      //数据库连接
      Connection connection = null;
      //预编译语句PreparedStatement，编译后，之后的调用直接执行，提高数据库性能
      PreparedStatement preparedStatement = null;
      ResultSet resultSet = null;
  
      try {
          //加载数据库驱动
          Class.forName("com.mysql.jdbc.Driver");
          //通过驱动管理类获取数据库连接
          connection =  DriverManager.getConnection("jdbc:mysql://localhost:3306/blogs", "root", "");
          //定义sql语句 ?表示占位符
          String sql = "select * from Blog where id =  ?";
          //获取预处理statement
          preparedStatement = connection.prepareStatement(sql);
          //设置参数，第一个参数为sql语句中参数的序号（从1开始），第二个参数为设置的参数值
          preparedStatement.setInt(1, 123);
          //向数据库发出sql执行查询，查询出结果集
          resultSet =  preparedStatement.executeQuery();
          
          // 第二次直接执行
          preparedStatement.setInt(1, 124);
          resultSet =  preparedStatement.executeQuery();
          //遍历查询结果集
          while(resultSet.next()){
              System.out.println(resultSet.getString("id")+"  "+resultSet.getString("name"));
          }
          
      } catch (Exception e) {
          e.printStackTrace();
      }finally{
          //释放资源
          if(resultSet!=null){
              try {
                  resultSet.close();
              } catch (SQLException e) {
                  // TODO Auto-generated catch block
                  e.printStackTrace();
              }
          }
          if(preparedStatement!=null){
              try {
                  preparedStatement.close();
              } catch (SQLException e) {
                  // TODO Auto-generated catch block
                  e.printStackTrace();
              }
          }
          if(connection!=null){
              try {
                  connection.close();
              } catch (SQLException e) {
                  // TODO Auto-generated catch block
                  e.printStackTrace();
              }
          }
      }
  }
  

不使用Spring的Mybatis的使用示例

• 在应用项目中，我们一般基于Mybatis提供的mybatis-spring包，结合Spring来使用mybatis，但是mybatis也可以不基于Spring来直接使用，以下不使用Spring来使用mybatis的相关使用示例：

基于 XML 中构建 SqlSessionFactory

• BlogMapper.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 namespace="org.mybatis.example.BlogMapper">
    <select id="selectBlog" resultType="Blog">
      select * from Blog where id = #{id}
    </select>
  </mapper>
  

• BlogMapper接口如下：

  package org.mybatis.example;
  public interface BlogMapper {
    Blog selectBlog(int id);
  }
  

• mybatisConfig.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>
    <environments default="development">
      <environment id="development">
        <transactionManager type="JDBC"/>
        <dataSource type="POOLED">
          <property name="driver" value="${driver}"/>
          <property name="url" value="${url}"/>
          <property name="username" value="${username}"/>
          <property name="password" value="${password}"/>
        </dataSource>
      </environment>
    </environments>
    <mappers>
      <mapper resource="org/mybatis/example/BlogMapper.xml"/>
    </mappers>
  </configuration>
  

• 不使用Spring的应用代码配置：

  String resource = "org/mybatis/example/mybatis-config.xml";
  InputStream inputStream = Resources.getResourceAsStream(resource);
  SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
  
  SqlSession session = sqlSessionFactory.openSession();
  try {
    BlogMapper mapper = session.getMapper(BlogMapper.class);
    Blog blog = mapper.selectBlog(101);
  } finally {
    session.close();
  }
  

不使用 XML 构建 SqlSessionFactory

• BlogMapper接口如下：

  package org.mybatis.example;
  public interface BlogMapper {
    @Select("SELECT * FROM blog WHERE id = #{id}")
    Blog selectBlog(int id);
  }
  

• 不使用Spring的应用代码配置：

  DataSource dataSource = BlogDataSourceFactory.getBlogDataSource();
  TransactionFactory transactionFactory = new JdbcTransactionFactory();
  Environment environment = new Environment("development", transactionFactory, dataSource);
  
  Configuration configuration = new Configuration(environment);
  configuration.addMapper(BlogMapper.class);
  SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);
  
  SqlSession session = sqlSessionFactory.openSession();
  
  try {
    BlogMapper mapper = session.getMapper(BlogMapper.class);
    Blog blog = mapper.selectBlog(101);
  } finally {
    session.close();
  }