spring-JDBC配置，使用，一些报错，mybatis原理，优化
一. 配置spring的jdbc的pom.xml遇到报错 missing artifactXXXXX。
修改dependency的版本如下
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>3.1.1.RELEASE</version>
</dependency>
二.代码的execute执行方法报错，增加dependency如下：
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>4.3.20.RELEASE</version>
</dependency>
三.代码执行报错 Caused by: org.xml.sax.SAXParseException;
原因是设置的数据库的密码太复杂了，可能有一些标识符识别的问题，修改了一下数据库的密码。
<!-- 连接数据库的用户名 --> <property name="username" value="root" /> <property name="password" value="12345678"/>
四.xml中的mysql的driver的jar包没有导入，导致不能识别<property name="driverClassName" value="com.mysql.jdbc.Driver" />
增加dependency
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.15</version>
</dependency>
其中还有一个问题，新增的这个connector的包版本比较新，报了一个错误：Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'.需要把xml中的value更新一下。
五.xml中配置的mysql的库是spring，数据库中并没有创建，出现报错：Could not get JDBC Connection; nested exception is java.sql.SQLSyntaxErrorException: Unknown database 'spring'
在mysql中创建一个spring的数据库就可以了
六.报错Could not get JDBC Connection; nested exception is 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.
原因是mysql的时区设置有问题，需要在mysql中修改。
打开mysql的命令行，执行语句：
mysql> show variables like '%time_zone%'; mysql> set global time_zone='+8:00'
七 示例代码
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:context="http://www.springframework.org/schema/context" xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:aop="http://www.springframework.org/schema/aop" xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.1.1.xsd http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-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:component-scan base-package="com.itheima" /> <aop:aspectj-autoproxy /> <!-- 配置数据源 --> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <!-- 数据库驱动 --> <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" /> <!-- 连接数据库的url --> <property name="url" value="jdbc:mysql://localhost:3306/spring" /> <!-- 连接数据库的用户名 --> <property name="username" value="root" /> <property name="password" value="12345678"/> </bean> <!-- 将datasource注入到jdbcTemplate中 --> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource" /> </bean> <!-- 将template注入到dao中 <bean id="xxx" class="Xxx"> <property name="jdbcTemplate" ref="jdbcTemplate" /> </bean>--> </beans>
JdbcTemplateTest.java
package com.itheima.jdbcTemplate;
import org.apache.catalina.core.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
public class JdbcTemplateTest {
public static void main(String[] args) {
ClassPathXmlApplicationContext applicationContext =
new ClassPathXmlApplicationContext("com/itheima/jdbcTemplate/applicationContext.xml");
JdbcTemplate jdTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
jdTemplate.execute("create table account ( id int primary key auto_increment, username varchar(50), balance double)");
System.out.println("账户表account创建成功");
}
}
pom.xml
<?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.example</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<!-- <packaging>war</packaging> -->
<name>demo</name>
<description>Demo project for Spring Boot</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.0.RELEASE</version>
<relativePath /> <!-- lookup parent from repository -->
</parent>
<properties>
<docker.image.prefix>spring-boot-yiibai</docker.image.prefix>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
<!-- 表明启动类 -->
<start-class>com.example.demo.DemoApplication</start-class>
</properties>
<dependencies>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-aop -->
<!-- https://mvnrepository.com/artifact/junit/junit -->
<!-- https://mvnrepository.com/artifact/org.aspectj/aspectjrt -->
<!-- https://mvnrepository.com/artifact/org.springframework/org.springframework.jdbc -->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjrt</artifactId>
<version>1.9.3</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.aspectj/aspectjweaver -->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.3</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<!-- rest相关 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>4.3.6.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>3.1.1.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java
提供mysql的driver-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.15</version>
</dependency>
<!-- 防止jdbcTemplate的execute方法调用报错 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>4.3.20.RELEASE</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>com.spotify</groupId>
<artifactId>docker-maven-plugin</artifactId>
<version>1.0.0</version>
<configuration>
<imageName>${docker.image.prefix}/${project.artifactId}</imageName>
<dockerDirectory>src/main/docker</dockerDirectory>
<resources>
<resource>
<directory>${project.build.directory}</directory>
<include>${project.build.finalName}.jar</include>
</resource>
</resources>
</configuration>
</plugin>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
其他. xml的配置太复杂了，数据库配置应该剥离出来的，后面估计要改。
一个正常的创建类--类对应数据库的表--操作表的接口--实现表的接口--测试代码，这个是数据库操作和代码触发都写在实现接口中，以后mybatis会分离数据库操作和实现操作。
mybatis的执行顺序和原理
mybatis示例代码
其中包括：dbProperties提取；mybatis注解
step1-1:
读取mybatis_config.xml配置，包括数据库配置，和ORM（对象-数据库关系映射）
<?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>
<properties resource="db.properties"/>
<environments default="mysql">
<environment id = "mysql">
<transactionManager type="JDBC"/>
<dataSource type = "POOLED">
<property name = "driver" value = "${jdbc.driver}"/>
<property name = "url" value = "${jdbc.url}"/>
<property name = "username" value = "${jdbc.username}"/>
<property name = "password" value = "${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource = "com/itheima/mapper/CustomerMapper.xml"/>
</mappers>
</configuration>
step1-2
db.properties 配置的数据库信息
jdbc.driver=com.mysql.cj.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/mybatis jdbc.username=root jdbc.password=12345678
step1-3
CustomerMapper.xml 配置的ORM信息，包括一些操作数据库的语句。
<?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.itheima.mapper.CustomerMapper">
<select id = "findCustomerById" parameterType="Integer"
resultType="com.itheima.po.Customer">
select * from t_customer where id = #{id}
</select>
<select id = "findCustomerByName" parameterType="String"
resultType="com.itheima.po.Customer">
select * from t_customer where username like concat('%',#{value},'%')
</select>
<insert id ="addCustomer" parameterType="com.itheima.po.Customer">
insert into t_customer (username,jobs,phone)
values(#{username},#{jobs},#{phone})
</insert>
<update id = "updateCustomer" parameterType="com.itheima.po.Customer">
update t_customer set
username = #{username},jobs=#{jobs},phone=#{phone}
where id=#{id}
</update>
</mapper>
step2-1
创建会话工厂，与数据库建立连接（使用单例模式，仅创建一个sqlSessionFactory去连接数据库。如果创建多个，每次要加载全部的配置，耗时长，且容易消耗光数据库的资源（mysql.ini的max_connections参数））
参考： https://blog.csdn.net/xiaolizi22233/article/details/54172958
public class MybatisTest {
private SqlSessionFactory sqlSessionFactory=null;
String resource= "mybatis-config.xml";
public SqlSessionFactory getSqlSessionFactory() throws IOException{
if(sqlSessionFactory==null){
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
}
return sqlSessionFactory;
}
}
step2-2
创建sqlSession对象，该对象包含执行sql的所有方法（增删改查）
step3-1
mybatis底层定义了一个Executor接口操作数据库，根据sqlSession传递的参数，动态生成要执行的sql语句
step3-2
Executor接口的执行方法中，有一个MappedStatement类型的参数，该参数封装了sql语句的id，参数等（对应CustomerMapper.xml）
step4
输入参数映射，MappedStatement对象对输入参数进行定义，Executor通过MappedStatement在执行sql前，将输入的java对象映射到sql中
step5
输出参数映射，数据库执行完sql后，mappedStatement对输出结果定义（pojo，list，map,基本类型等），Executor将mappedStatement结果映射到java对象中
@Test
public void findCustomerByIdTest() throws IOException{
SqlSession sqlSession = getSqlSessionFactory().openSession();
// sqlSession.selectOne("com.itheima.mapper.CustomerMapper.findCustomerById",1);
Customer customer = sqlSession.selectOne("com.itheima.mapper.CustomerMapper.findCustomerById",1);
System.out.println(customer.toString());
sqlSession.close();
}
/**
* 模糊查询
* @throws IOException
*/
@Test
public void findCustomerByNameTest() throws IOException{
SqlSession sqlSession = getSqlSessionFactory().openSession();
// sqlSession.selectOne("com.itheima.mapper.CustomerMapper.findCustomerById",1);
List<Customer> customers = sqlSession.selectList("com.itheima.mapper.CustomerMapper.findCustomerByName","j");
for(Customer customer:customers){
System.out.println(customer);
}
sqlSession.close();
}
/**
* 插入数据
* @throws IOException
*/
@Test
public void addCustomerTest() throws IOException{
SqlSession sqlSession = getSqlSessionFactory().openSession();
// sqlSession.selectOne("com.itheima.mapper.CustomerMapper.findCustomerById",1);
Customer customer =new Customer();
customer.setJobs("student");
customer.setId(4);
customer.setPhone("13242424028");
customer.setUsername("jones");
int rows=sqlSession.insert("com.itheima.mapper"
+".CustomerMapper.addCustomer",customer);
if(rows>0){
System.out.println("您成功插入了"+rows+"条数据");
}else{
System.out.println("插入操作失败");
}
// System.out.println(customer.toString());
sqlSession.commit();
sqlSession.close();
}
/**
* 修改数据
*/
@Test
public void updateCustomerTest()throws Exception{
SqlSession sqlSession = getSqlSessionFactory().openSession();
// sqlSession.selectOne("com.itheima.mapper.CustomerMapper.findCustomerById",1);
Customer customer =new Customer();
customer.setJobs("student");
customer.setId(4);
customer.setPhone("13000011122");
customer.setUsername("jones");
int rows=sqlSession.update("com.itheima.mapper"
+".CustomerMapper.updateCustomer",customer);
if(rows>0){
System.out.println("您成功修改了"+rows+"条数据");
}else{
System.out.println("插入操作失败");
}
// System.out.println(customer.toString());
sqlSession.commit();
sqlSession.close();
}
优化：
1. 配置mybatis插件，包括自动根据数据库表结构生成pojo类，DAO，xml；实现分页；追踪dao 接口和mapper文件里xml做一些基础变更
https://blog.csdn.net/Winstin1995/article/details/79679887
还有一些其他插件，可以自行安装。
2.mapper.xml有很多标签，其中有<sql>可以用来定义一些常用字段
<sql id="customerColumns">id, username, jobs, phone</sql>
<select id="findCustomerById" parameterType="Integer"
resultType="com.itheima.po.Customer">
select <include refid="customerColumns"/> from t_customer
where id = #{id}
</select>
