Java游戏服务器开发之十二--数据库连接整合mybatis

阳光穿透心脏的1/2处 2022-05-21 04:14 200阅读 0赞

应该比较让人关心的还有一个就是数据存储的问题了。  
  下面将会介绍连接数据库的相关内容  
  使用的是mysql数据库，框架中将使用mybatis  
  这里介绍时假设各位都使用或者了解过mysql。

1.在pom中添加相关包

2.创建数据库和表

3.创建映射实体类entity与数据库操作对象Dao  
  4.创建映射文件mapper.xml  
  5.在spring中配置mybatis  
 6.调用Dao进行测试

1.在pom中添加相关包

<dependency>
          <groupId>org.mybatis</groupId>
          <artifactId>mybatis</artifactId>
          <version>3.3.0</version>
        </dependency>
        <dependency>
          <groupId>org.mybatis</groupId>
          <artifactId>mybatis-spring</artifactId>
          <version>1.2.3</version>
        </dependency>
        <dependency>
          <groupId>org.mybatis.caches</groupId>
          <artifactId>mybatis-ehcache</artifactId>
          <version>1.0.3</version>
        </dependency>
        <dependency>
          <groupId>org.hibernate.javax.persistence</groupId>
          <artifactId>hibernate-jpa-2.1-api</artifactId>
          <version>1.0.0.Final</version>
        </dependency>
        
        <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <version>5.1.38</version>
        </dependency>
        
        <dependency>
          <groupId>com.alibaba</groupId>
          <artifactId>druid</artifactId>
          <version>1.0.16</version>
        </dependency>

2.创建数据库和表  
创建jg\_server，选择utf8的编码  
创建user表，里面就是个字段，id/name/account/password  
插入一条测试数据

具体的sql语句如下

/*
    SQLyog 企业版 - MySQL GUI v8.14 
    MySQL - 5.6.26-log : Database - jg_server
    *********************************************************************
    */
    
    
    /*!40101 SET NAMES utf8 */;
    
    /*!40101 SET SQL_MODE=''*/;
    
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    CREATE DATABASE /*!32312 IF NOT EXISTS*/`jg_server` /*!40100 DEFAULT CHARACTER SET utf8 */;
    
    USE `jg_server`;
    
    /*Table structure for table `user` */
    
    DROP TABLE IF EXISTS `user`;
    
    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(128) DEFAULT '-',
      `account` varchar(32) DEFAULT '-',
      `password` varchar(32) DEFAULT '-',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 CHECKSUM=1 ROW_FORMAT=DYNAMIC;
    
    /*Data for the table `user` */
    
    insert  into `user`(`id`,`name`,`account`,`password`) values (1,'2','3','4');
    
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

3.创建映射实体类entity与数据库操作对象Dao  
与user表对应，类名使用User，别名也使用这个

UserDao里面就写一个方法，获取全部的表数据

User

/*
     * Copyright (C), 2015-2018
     * FileName: User
     * Author:   zhao
     * Date:     2018/6/28 14:31
     * Description: 用户表
     * History:
     * <author>          <time>          <version>          <desc>
     * 作者姓名           修改时间           版本号              描述
     */
    package com.lizhaoblog.server.biz.entity;
    
    import org.apache.ibatis.type.Alias;
    
    import javax.persistence.Column;
    import javax.persistence.Id;
    import javax.persistence.Table;
    
    /**
     * 〈一句话功能简述〉<br>
     * 〈用户表对应的实体类〉
     *
     * @author zhao
     * @date 2018/6/28 14:31
     * @since 1.0.0
     */
    @Table(name = "user")
    @Alias("User")
    public class User {
      @Id
      @Column(name = "id")
      private Integer id;
      @Column(name = "name")
      private String name;
      @Column(name = "account")
      private String account;
      @Column(name = "password")
      private String password;
    
      public Integer getId() {
        return id;
      }
    
      public void setId(Integer id) {
        this.id = id;
      }
    
      public String getName() {
        return name;
      }
    
      public void setName(String name) {
        this.name = name;
      }
    
      public String getAccount() {
        return account;
      }
    
      public void setAccount(String account) {
        this.account = account;
      }
    
      public String getPassword() {
        return password;
      }
    
      public void setPassword(String password) {
        this.password = password;
      }
    }

UserDao

/*
     * Copyright (C), 2015-2018
     * FileName: UserDao
     * Author:   zhao
     * Date:     2018/6/28 14:37
     * Description: User表数据处理
     * History:
     * <author>          <time>          <version>          <desc>
     * 作者姓名           修改时间           版本号              描述
     */
    package com.lizhaoblog.server.biz.dao.mysql;
    
    import com.lizhaoblog.server.biz.entity.User;
    
    import org.apache.ibatis.annotations.CacheNamespaceRef;
    import org.apache.ibatis.annotations.Options;
    import org.apache.ibatis.annotations.ResultMap;
    import org.apache.ibatis.annotations.Select;
    
    import java.util.List;
    
    /**
     * 〈一句话功能简述〉<br>
     * 〈User表数据处理 〉
     *
     * @author zhao
     * @date 2018/6/28 14:37
     * @since 1.0.0
     */
    @CacheNamespaceRef(UserDao.class)
    public interface UserDao {
      @Select("SELECT * FROM user")
      @ResultMap("resultMap")
      @Options(useCache = false)
      List<User> listUser();
    }

5.创建映射文件mapper.xml  
我们直接使用 类名.xml的方式，然后写一个resultMap  
resources--mybatis--jpa中添加User.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="com.lizhaoblog.server.biz.dao.mysql.UserDao">
      <cache type="org.mybatis.caches.ehcache.LoggingEhcache"/>
      <resultMap type="com.lizhaoblog.server.biz.entity.User" id="resultMap">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="account" column="account"/>
        <result property="password" column="password"/>
      </resultMap>
    </mapper>

4.spring中配置mybatis  
在resources--mybatis中添加一个mybatis-config.xml  
数据库连接池的配置，在resources--properties添加db-config-dev.properties

在ApplicationContext.xml的同级目录添加一个ApplicationContext-mysql.xml

修改ApplicationContext.xml

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD SQL Map Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
      <settings>
        
        <setting name="defaultExecutorType" value="REUSE"/>
      </settings>
    </configuration>

db-config-dev.properties

mysql.url:jdbc:mysql://localhost:3306/jg_server?useUnicode=true&characterEncoding=UTF-8
    mysql.driverClassName:com.mysql.jdbc.Driver
    #mysql.username:root
    #mysql.password:admin
    mysql.username:adminjg
    mysql.password:admin666
    
    
    filters:stat
    
    maxActive:20
    initialSize:1
    maxWait:60000
    minIdle:10
    maxIdle:15
    
    timeBetweenEvictionRunsMillis:60000
    minEvictableIdleTimeMillis:300000
    
    validationQuery:SELECT 'x'
    testWhileIdle:true
    testOnBorrow:false
    testOnReturn:false
    
    maxOpenPreparedStatements:20
    removeAbandoned:true
    removeAbandonedTimeout:1800
    logAbandoned:true

ApplicationContext-mysql.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"
           xsi:schemaLocation="http://www.springframework.org/schema/beans
    						http://www.springframework.org/schema/beans/spring-beans-4.2.xsd">
    
        <bean name="mysqlTransactionManager"
              class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <property name="dataSource" ref="mysqlDs"></property>
            <qualifier value="mysql"/>
        </bean>
    
        
        <bean id="mysqlDs" class="com.alibaba.druid.pool.DruidDataSource"
              destroy-method="close">
            
            <property name="url" value="${mysql.url}"/>
            <property name="username" value="${mysql.username}"/>
            <property name="password" value="${mysql.password}"/>
            <property name="driverClassName" value="${mysql.driverClassName}"/>
            <property name="filters" value="${filters}"/>
            
            <property name="maxActive" value="${maxActive}"/>
            
            <property name="initialSize" value="${initialSize}"/>
            
            <property name="maxWait" value="${maxWait}"/>
            
            <property name="minIdle" value="${minIdle}"/>
            
            <property name="timeBetweenEvictionRunsMillis" value="${timeBetweenEvictionRunsMillis}"/>
            
            <property name="minEvictableIdleTimeMillis" value="${minEvictableIdleTimeMillis}"/>
            <property name="validationQuery" value="${validationQuery}"/>
            <property name="testWhileIdle" value="${testWhileIdle}"/>
            <property name="testOnBorrow" value="${testOnBorrow}"/>
            <property name="testOnReturn" value="${testOnReturn}"/>
            <property name="maxOpenPreparedStatements" value="${maxOpenPreparedStatements}"/>
            
            <property name="removeAbandoned" value="${removeAbandoned}"/>
            
            <property name="removeAbandonedTimeout" value="${removeAbandonedTimeout}"/>
            
            <property name="logAbandoned" value="${logAbandoned}"/>
        </bean>
    
        
        <bean id="mysqlSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
            <property name="dataSource" ref="mysqlDs"/>
            <property name="configLocation" value="classpath:mybatis/mybatis-config.xml"></property>
            <property name="typeAliasesPackage" value="com.lizhaoblog"></property>
            
            
            <property name="mapperLocations" value="classpath:mybatis/*/*.xml"></property>
        </bean>
    
        <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
            <property name="basePackage" value="com.lizhaoblog.server.biz.dao.mysql"></property>
            <property name="sqlSessionFactoryBeanName" value="mysqlSqlSessionFactory"></property>
        </bean>
    
    </beans>

修改ApplicationContext.xml

<import resource="ApplicationContext-mysql.xml"/>
      
      <bean id="cfgProps" class="org.springframework.beans.factory.config.PropertiesFactoryBean">
        <property name="locations">
          <list>
            <value>classpath:properties/server-config-dev.properties</value>
            <value>classpath:properties/db-config-dev.properties</value>
          </list>
        </property>
      </bean>

6.调用Dao进行测试  
在handler里面注入UserDao，然后调用list方法  
@Autowired  
private UserDao userDao;

List<User> myAll = userDao.listUser();

上面的代码在码云上 https://gitee.com/lizhaoandroid/JgServer  
可以加qq群一起探讨Java游戏服务器开发的相关知识 676231564