Mybatsi连接数据库

mybatis的使用

①添加MyBatis的maven包

②创建user数据表

③编写User实体类

④编写核心文件MyBatisConfig.xml

⑤编写映射文件UserMapper.xml 和 UserMapper接口

⑥编写测试类

1.导入依赖

    <!--    数据库-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.37</version>
    </dependency>

    <!-- MyBatis -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.5.3</version>
    </dependency>
    
    <!--    日志依赖-->
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
    </dependency>

2.数据库建表

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

3.实体类

public class User {
    private int id;
    private String username;
    private String password;
    private String phone;
    private int age;
    set get 方法
}

4.编写mybatis的配置文件

# 数据库信息 db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/xinzhi666?useUnicode=true&characterEncoding=utf-8
username=root
password=root

#mybaits-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!--MyBatis的DTD约束-->
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<!--configuration 核心根标签-->
<configuration>

    <!--引入数据库连接的配置文件-->
    <properties resource="db.properties"/>

    <!--配置LOG4J-->
    <settings>
        <setting name="logImpl" value="log4j"/>
    </settings>

    <!--起别名-->
    <typeAliases>
        <typeAlias type="com.xinzhi.model.User" alias="user"/>
        <!--<package name="com.xinzhi.model"/>-->
    </typeAliases>

    <!--environments配置数据库环境，环境可以有多个。default属性指定使用的是哪个-->
    <environments default="mysql">
        <!--environment配置数据库环境  id属性唯一标识-->
        <environment id="mysql">
            <!-- transactionManager事务管理。  type属性，采用JDBC默认的事务-->
            <transactionManager type="JDBC"></transactionManager>
            <!-- dataSource数据源信息   type属性 连接池-->
            <dataSource type="POOLED">
                <!-- property获取数据库连接的配置信息 -->
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${username}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>

    <!-- mappers引入映射配置文件 -->
    <mappers>
        <!-- mapper 引入指定的映射配置文件   resource属性指定映射配置文件的名称 -->
        <mapper resource="UserMapper.xml"/>
    </mappers>
</configuration>


# 日志文件  log4j.properties
# Global logging configuration
# ERROR WARN INFO DEBUG
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

5.接口和映射文件

public interface UserMapper {
    List<User> selectAll();
    User selectByid(int id);
}

//映射文件  
<?xml version="1.0" encoding="UTF-8" ?>
<!--MyBatis的DTD约束-->
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--
    mapper：核心根标签
    namespace属性：名称空间
-->
<mapper namespace="com.xinzhi.dao.UserMapper">
    <!--
        select:查询功能的标签
        id属性:唯一标识
        resultType属性:指定结果映射对象类型
        parameterType属性:指定参数映射对象类型
    -->
    <select id="selectAll" resultType="user">
        SELECT * FROM user;
    </select>
    <select id="selectByid" resultType="com.xinzhi.model.User" parameterType="int">
        select * from user where id=#{id}
    </select>
</mapper>

6.测试类

package com.xinzhi;


import com.xinzhi.dao.UserMapper;
import com.xinzhi.model.User;
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 java.io.IOException;
import java.io.InputStream;
import java.util.List;

/**
 * Hello world!
 *
 */
public class App {
    public static void main( String[] args )throws IOException {
        //加载核心配置文件
        InputStream is = Resources.getResourceAsStream("mybaits-config.xml");
        //获得sqlSession工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        //获取sqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = mapper.selectAll();
        System.out.println(users);
        User user = mapper.selectByid(5);
        System.out.println(user);
    }
}

mybatis和spring整合

1开发步骤

• 1 导入maven依赖

• 2 创建账户的实体类

• 3 创建mapper接口和对应的xml文件

• 4 Service接口类和实现类

• 5 数据库的配置文件

• 6 spring配置文件

• 7 mybatis的配置文件

• 8 测试功能

1.导入依赖

<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.xinzhi</groupId>
  <artifactId>MyBaitsSpringOne</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>MyBaitsSpringOne</name>
  <url>http://maven.apache.org</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-beans</artifactId>
      <version>5.0.7.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-core</artifactId>
      <version>5.0.7.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>5.0.7.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-expression</artifactId>
      <version>5.0.7.RELEASE</version>
    </dependency>
    <!-- 基于AspectJ的aop依赖 -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-aspects</artifactId>
      <version>5.0.7.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>aopalliance</groupId>
      <artifactId>aopalliance</artifactId>
      <version>1.0</version>
    </dependency>
    <!-- spring 事务管理和JDBC依赖包 -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>5.0.7.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.0.7.RELEASE</version>
    </dependency>
    <!-- spring 单元测试组件包 -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-test</artifactId>
      <version>5.0.7.RELEASE</version>
    </dependency>
    <!-- 单元测试Junit -->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
    </dependency>
    <!-- mysql数据库驱动包 -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.31</version>
    </dependency>
    <!-- dbcp连接池的依赖包 -->
    <dependency>
      <groupId>commons-dbcp</groupId>
      <artifactId>commons-dbcp</artifactId>
      <version>1.4</version>
    </dependency>
    <!-- mybatis依赖 -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.5</version>
    </dependency>
    <!-- mybatis和spring的整合依赖 -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
      <version>1.3.1</version>
    </dependency>

    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.24</version>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-surefire-plugin</artifactId>
        <version>2.5</version>
        <configuration>
          <skipTests>true</skipTests>
        </configuration>
      </plugin>
    </plugins>
  </build>
</project>

2.创建实体类

package com.xinzhi.model;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.math.BigDecimal;
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Account {
    private int id;

    private String name;

    private BigDecimal money;
}

3.创建mapper接口和对应的文件

package com.xinzhi.dao;

import org.apache.ibatis.annotations.Param;

import java.math.BigDecimal;

public interface AccountMapper {
    BigDecimal queryMoneyByName(String name);
    int update(@Param("name")String name, @Param("money")BigDecimal money);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!--MyBatis的DTD约束-->
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--
    mapper：核心根标签
    namespace属性：名称空间
-->
<mapper namespace="com.xinzhi.dao.AccountMapper">
    <!-- 查询 -->
    <select id="queryMoneyByName" resultType="java.math.BigDecimal">
        SELECT money from account where name = #{name}
    </select>

    <!-- 修改 -->
    <update id="update">
        UPDATE account SET money = #{money} WHERE name = #{name}
    </update>

</mapper>

4 Service接口类和实现类

package com.xinzhi.service;

import java.math.BigDecimal;

public interface IAccountService {
    /*
     * 转账功能
     * @param from 主动转账
     * @param to 收款的人
     * @param money 金额
     * */
    void transfer(String from, String to, BigDecimal money);
}

package com.xinzhi.service.Impl;

import com.xinzhi.dao.AccountMapper;
import com.xinzhi.service.IAccountService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
@Service
@Transactional
public class AccountServiceImpl implements IAccountService {
    @Autowired
    private AccountMapper accountMapper;
    @Override
    public void transfer(String from, String to, BigDecimal money) {
        BigDecimal b1 = accountMapper.queryMoneyByName(from);
        BigDecimal b2 = accountMapper.queryMoneyByName(to);
        if (b1.compareTo(money) == -1){
            System.out.println("账户余额不足，不能转账");
            return;
        }
        BigDecimal subtract = b1.subtract(money);
        BigDecimal add = b2.add(money);
        accountMapper.update(from,subtract);
        accountMapper.update(to,add);
        System.out.println("转账成功");
    }
}

5.配置数据库

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8
jdbc.username=root
jdbc.password=root

6.写spring文件

文件名:applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
    http://www.springframework.org/schema/tx
    http://www.springframework.org/schema/tx/spring-tx-4.3.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context-4.3.xsd
    http://www.springframework.org/schema/aop
    http://www.springframework.org/schema/aop/spring-aop-4.3.xsd">

    <!--引入外部的配置文件-->
    <context:property-placeholder location="classpath:db.properties"/>
    <!--	配置数据源-->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
    </bean>
    <!--	配置事务管理器-->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
    </bean>

    <!--开启事务注解-->
    <tx:annotation-driven transaction-manager="transactionManager" />

    <!--	xml方式配置事务，一般使用上面注解的方式，因为方便和简单，其实事务也是对业务层逻辑的增强-->
    <!--	<tx:advice transaction-manager="transactionManager" id="txAdvice" >-->
    <!--		<tx:attributes>-->
    <!--			<tx:method name="*" isolation="DEFAULT" propagation="REQUIRED" read-only="false"/>-->
    <!--		</tx:attributes>-->
    <!--	</tx:advice>-->
    <!--	-->
    <!--	<aop:config>-->
    <!--		<aop:pointcut id="myPointCut" expression="execution(* com.soft.impl.*.*(..))"/>-->
    <!--		<aop:advisor advice-ref="txAdvice" pointcut-ref="myPointCut"/>-->
    <!--	</aop:config>-->


    <!--	配置sqlSessionFactory，主要是指定数据源，配置文件的位置-->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="configLocation" value="classpath:mybatis-config.xml"/>
        <property name="mapperLocations" value="classpath:mapper/*.xml"/>

    </bean>

    <!--	扫描mapper文件-->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.xinzhi.dao"/>
    </bean>


    <!--	组件扫描，扫描指定包下的注解-->
    <context:component-scan base-package="com.xinzhi"/>

</beans>

7.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>
    <!--    开启二级缓存，默认是关闭的-->
    <settings>
        <setting name = "cacheEnabled" value = "true" />
    </settings>

    <!--    别名-->
    <typeAliases>
        <package name="com.xinzhi.model" />
    </typeAliases>

    <!--    map.xml文件的位置指定，因为sqlsessionfactory中已经指定过了，所以可以不配置-->
    <mappers>
        <!--        <mapper resource="mapper/AccountMapper.xml"/>-->
    </mappers>

</configuration>

8.测试

package com.xinzhi;

import com.xinzhi.service.IAccountService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.math.BigDecimal;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:applicationContext.xml"})
public class Text01 {
    @Autowired
    private IAccountService accountService;
    @Test
    public void text01(){
        accountService.transfer("张思雨","李四",new BigDecimal(2000));
    }
}