一、Java使用 Mybatis 操作 Mysql 查询的完整过程和程序代码示例
MyBatis 与原来使用JDBC操作 MYSQL的方式对比起来有不少优点,在灵活性方面可以定义 SQL 查询语句、参数映射、结果映射等以及其他高级功能如缓存、拦截器等,使得 MyBatis 可以适应各种复杂的业务需求;在程序简化方面,使用 MyBatis可以将 SQL 语句直接嵌入到 XML 配置文件中,从而将数据库操作和 Java 代码分离简化开发和维护。还有其采用的一级缓存和二级缓存从而提升的性能优化等。
使用Mybatis操作Mysql需要进行以下几步操作,首先我们在IDEA中创建了一个新项目。
1. 导入必要的jar包支持
我这里有包括mybatis,spring-webmvc,junit,lombok,mysql-connector-java。必须要的包就是mybatis和mysql-connector-java。此外,此处需要注意一个静态资源过滤配置问题。不然运行时Mybatis 会报错:Could not find resource xxxMapper.xml。因为不配置资源过滤的话,这些内容就不会被导出至target目录中,从而程序运行时找不到对应文件报错资源导出失败。位置在 pom.xml文件中,加入以下代码在 [<build>中</build>] 如下:
<?xml version="1.0" encoding="UTF-8"?>
<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.kermit</groupId>
<artifactId>mybatis</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>21</maven.compiler.source>
<maven.compiler.target>21</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-webmvc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>6.1.12</version>
</dependency>
<!-- https://mvnrepository.com/artifact/junit/junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.34</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
</dependencies>
<build>
<!--maven静态资源过滤-->
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
</build>
</project>
2. 编辑 Mybatis 配置文件:
编辑 Mybatis 配置文件 mybatis-config.xml 放在resources目录下。需要注意。原来的JAVA mybatis的配置文件中使用的是 com.mysql.jdbc.Driver 报警告了提示其已过期, This is deprecated. 使用新包 com.mysql.cj.jdbc.Driver'。详细报错如下:
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException:
其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="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/fangha?serverTimezone=UTC&useSSL=true&useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/kermit/mapper/IndustryMapper.xml"/>
</mappers>
</configuration>
除了上面的注意事项外,还有一个MYSQL时区配置,不然执行Mysql连接时提示,详细报错如下: 看提示是在连接数据库时没有指定时区。
Error querying database. Cause: java.sql.SQLException: The server time zone value '�й���ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specifc time zone value if you want to utilize time zone support.
The server time zone value '�й���ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver。
解决的方法就是把连接的语句中增加 serverTimezone=UTC 即可。
<property name="url" value="jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC&useSSL=true&useUnicode=true&characterEncoding=UTF-8"/>
3. 创建基础的 pojo 类。
package com.kermit.pojo;
import lombok.Data;
@Data
public class Industry {
public int id;
public String name;
public String jname;
public String desp;
}
文件名: Industry.java ,注:POJO中的类属性定义均与 MYSQL数据库中的表字段内容相结合起来。比如我这里的数据库表截图如下:
4. 创建对应的 Mapper 接口和 mybatis XML文件
创建对应 Mapper 接口代码如下: IndustryMapper.java
package com.kermit.mapper;
import com.kermit.pojo.Industry;
import java.util.List;
public interface IndustryMapper {
List<Industry> getIndustryList();
}
mybatis XML文件内容如下: IndustryMapper.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.kermit.mapper.IndustryMapper">
<select id="getIndustryList" resultType="com.kermit.pojo.Industry">
select * from wx_industry
</select>
</mapper>
5. 最后进行程序测试。
import com.kermit.mapper.IndustryMapper;
import com.kermit.pojo.Industry;
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.awt.geom.Area;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class TestMybatis {
private static SqlSessionFactory sqlSessionFactory;
public static void main(String[] args) throws IOException {
try {
//使用mybatis:先取得SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
SqlSession session = sqlSessionFactory.openSession();
IndustryMapper industryMapper = session.getMapper(IndustryMapper.class);
List<Industry> industrylist = industryMapper.getIndustryList();
for (Industry industry : industrylist) {
System.out.println(industry);
}
}
}
最后,上方的程序执行后即可成功将数据库中的表内容全部取出,测试程序中先使用 SqlSessionFactoryBuilder 工厂建造类创建工厂类 sqlSessionFactory,然后再使用sqlSessionFactory创建数据库连接类 SqlSession。可以提炼为一个单独的工具实现,在其它的文章中有相关的记录,这里只为实现结果就写在了测试程序中。
二、线上的报错 CommunicationsException..milliseconds ago 问题处理
记一个线上的报错 com.mysql.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was xxx milliseconds ago 问题处理。之前开发的线上的JAVA项目在进行刷新的时候,发现第一次刷新时会报错com.mysql.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was xxx milliseconds ago。详细报错如下:
### Cause: com.mysql.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was 104,365,096 milliseconds ago. The last packet sent successfully to the server was 104,365,097 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem. ; The last packet successfully received from the server was 104,365,096 milliseconds ago. The last packet sent successfully to the server was 104,365,097 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.; nested exception is com.mysql.cj.jdbc.exceptions.CommunicationsException: #acquireRetryAttempts设置成几,后面就会重复报多少次。 The last packet successfully received from the server was 104,365,096 milliseconds ago...
这里使用的是C3P0连接池,报错:The last packet successfully received from the server 会重复,且其重复次数即是C3P0中的#acquireRetryAttempts配置,原因是MySQL服务器的默认的wait_timeout是28800秒即8小时,一个连接的空闲时间超过8小时,MySQL将自动断开连接,而连接池并不知道,会一直认为该连接还是有效的(未校验有效性),当应用申请使用该连接时,就会导致上面的报错。
解决方法:其实报错中已经详细提示了解决方法如下:
1. You should consider either expiring and/or testing connection validity before use in your application,
配置如c3p0在应用使用前测试连接。如下:
#添加estConnection配置
<property name="maxIdleTime" value="900" />
<property name="testConnectionOnCheckin" value="true" />
<property name="idleConnectionTestPeriod" value="60" />
2. increasing the server configured values for client timeouts,
配置数据库服务端的timeout参数
3. or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
在url=jdbc:mysql://连接属性上增加autoReconnect=true选项实现自动连接。