标签 mysql5.7 下的文章

2023-05-02

mysql5.7 sql语句实现父子查询 mysql5.7 sql语句实现父子查询1.数据表创建SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS `goods_metas`; CREATE TABLE `goods_metas` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号', `name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '名', `type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '类型', `count` int(11) NULL DEFAULT 0 COMMENT '统计', `parent` int(11) NULL DEFAULT 0 COMMENT '父编号（只有class需要）', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `name`(`name`, `type`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; INSERT INTO `goods_metas` VALUES (1, '美甲', 'class', 0, 0); INSERT INTO `goods_metas` VALUES (2, '长甲', 'class', 0, 1); INSERT INTO `goods_metas` VALUES (3, '短甲', 'class', 0, 1); INSERT INTO `goods_metas` VALUES (4, '可爱', 'tag', 0, 0); INSERT INTO `goods_metas` VALUES (5, '简约', 'tag', 0, 0); INSERT INTO `goods_metas` VALUES (6, '测试', 'class', 0, 2); SET FOREIGN_KEY_CHECKS = 1;2.根据父id查所有子节点将{id}修改为查询的父id不包含父节点自身select * from goods_metas where id in (SELECT id FROM (SELECT * FROM goods_metas where type="class") goods_metas, (SELECT @pid := '{id}') pd WHERE FIND_IN_SET(parent, @pid) > 0 AND @pid := concat(@pid, ',', id));id=0查询结果+----+----------+-------+-------+--------+ | id | name | type | count | parent | +----+----------+-------+-------+--------+ | 2 | 长甲 | class | 0 | 1 | | 3 | 短甲 | class | 0 | 1 | | 6 | 测试 | class | 0 | 2 | +----+----------+-------+-------+--------+

2023年05月02日
196 阅读
0 评论
0 点赞

2023-03-12

Java MyBatis实体类属性名与数据库字段表名称不一致解决办法 MyBatis实体类属性名与数据库字段表名称不一致解决办法1. 查询字段时使用别名对不一样的列名起别名，让别名和实体类的属性名一样<mapper namespace="org.example.mapper.UserMapper"> <select id="selectAll" resultType="user"> select 数据库表字段名 as 实体类属性名 from user; </select> </mapper>但是每次查询都需要重新定义一次别名使用sql片段的方式解决<mapper namespace="org.example.mapper.UserMapper"> <sql id="dataName"> 数据库表字段名 as 实体类属性名 </sql> <select id="selectAll" resultType="user"> select <include refid="dataName" /> from user; </select> </mapper>但是此方法缺点不灵活2.使用resultMap映射别名<mapper namespace="org.example.mapper.UserMapper"> <resultMap id="dataName" type="user"> <id column="id" property="id"></id> <result column="数据库表字段名" property="实体类属性名"></result> </resultMap> <select id="selectAll" resultMap="dataName"> select * from user; </select> </mapper>id作为主键映射，result作为一般字段映射

2023年03月12日
182 阅读
0 评论
0 点赞

2023-03-12

Java MyBatis代理开发 MyBatis代理开发0. 环境准备User.javapackage org.example; public class User { private Integer id; private String username; private String password; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getAll(){ return "id=" + id + " username=" + username + " password=" + password; } } 1. 定义与SQL映射文件同名的Mapper接口，并且将Mapper接口和SQL映射文件放置在同一目录下创建UserMapper.java空接口创建UserMapper.xml文件(IDEA中resources创建目录使用/代替.)2. 设置SQL映射文件的namespace属性为Mapper接口全限定名UserMapper.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.example.mapper.UserMapper"> <select id="selectAll" resultType="org.example.User"> select * from user; </select> </mapper>3. 在Mapper接口中定义方法，方法名就是SQL映射文件中sql语句的id，并保持参数类型和返回值类型一致UserMapper.javapackage org.example.mapper; import org.example.User; import java.util.List; public interface UserMapper { List<User> selectAll(); }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> <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://localhost:3306/java?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true"/> <property name="username" value="root"/> <property name="password" value="root"/> </dataSource> </environment> </environments> <mappers> <mapper resource="org/example/mapper/UserMapper.xml"/> </mappers> </configuration>4.编码：通过 SqlSession的 getMapper方法获取 Mapper接口的代理对象调用对应方法完成sql的执行主函数调用 public static void main(String[] args) throws IOException { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); SqlSession sqlSession = sqlSessionFactory.openSession(); // 获取UserMapper代理对象 UserMapper userMapper = sqlSession.getMapper(UserMapper.class); List<User> users = userMapper.selectAll(); System.out.println(users.get(0).getAll()); sqlSession.close(); }执行结果id=1 username=admin password=1ed1252369b609c48a93ea80b57f52ed细节：如果Mapper接口名称和SQL映射文件名称相同，并在同一目录下，则可以使用包扫描的方式简化SQL映射文件的加载mybatis-config.xml <mappers>    <package name="org.example.mapper"/> </mappers>

2023年03月12日
110 阅读
0 评论
0 点赞

2023-03-10

Java MyBatis快速入门 MyBatis 快速入门1.数据库准备1.1 数据库信息地址端口用户名密码localhost3306rootroot1.2 创建java数据库create database java;1.3 创建user数据表并且添加数据SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for user -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', `username` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名', `password` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `user_username_unique`(`username`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Records of user -- ---------------------------- INSERT INTO `user` VALUES (1, 'admin', '1ed1252369b609c48a93ea80b57f52ed'); SET FOREIGN_KEY_CHECKS = 1; 2.Java Maven项目2.1 配置pom.xml <dependencies> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.6</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.28</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.13.2</version> <scope>test</scope> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> <version>1.7.20</version> </dependency> <dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-classic</artifactId> <version>1.2.9</version> </dependency> <dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-core</artifactId> <version>1.2.9</version> </dependency> </dependencies>2.2 在resources添加logback.xml文件（日志：可选）<?xml version="1.0" encoding="UTF-8"?>  <configuration scan="false" scanPeriod="60 seconds" debug="false">  <property name="LOG_HOME" value="${catalina.base}/logs/" />  <property name="appName" value="u-plan"/>  <appender name="stdout" class="ch.qos.logback.core.ConsoleAppender"> <Encoding>UTF-8</Encoding>  <layout class="ch.qos.logback.classic.PatternLayout"> <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n</pattern> </layout> </appender>  <appender name="appLogAppender" class="ch.qos.logback.core.rolling.RollingFileAppender"> <Encoding>UTF-8</Encoding>  <file>${LOG_HOME}/${appName}.log</file>  <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">  <fileNamePattern>${LOG_HOME}/${appName}-%d{yyyy-MM-dd}-%i.log </fileNamePattern>  <MaxHistory>30</MaxHistory>  <timeBasedFileNamingAndTriggeringPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP"> <maxFileSize>512MB</maxFileSize> </timeBasedFileNamingAndTriggeringPolicy> </rollingPolicy>  <layout class="ch.qos.logback.classic.PatternLayout"> <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [ %thread ] - [ %-5level ] [%logger{50} : %line ] - %msg%n</pattern> </layout> </appender>   <logger name="org.hibernate" level="error" />  <logger name="org.springframework" level="error" additivity="false"/> <logger name="com.fairyland" level="info" additivity="true"> <appender-ref ref="appLogAppender" /> </logger>  <root level="info"> <appender-ref ref="stdout" /> <appender-ref ref="appLogAppender" /> </root> </configuration> 2.3 在resources下创建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> <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://localhost:3306/java?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true"/> <property name="username" value="root"/> <property name="password" value="root"/> </dataSource> </environment> </environments> <mappers> <mapper resource="UserMapper.xml"/> </mappers> </configuration>2.4 创建数据表User实体类package org.example; public class User { private Integer id; private String username; private String password; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getAll(){ return "id=" + id + " username=" + username + " password=" + password; } }2.5在resources下创建MyBatis的Sql映射文件UserMapper.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="user"> <select id="selectAll" resultType="org.example.User"> select * from user; </select> </mapper>3. 运行效果主函数执行public class Main { public static void main(String[] args) throws IOException { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); SqlSession sqlSession = sqlSessionFactory.openSession(); List<User> objects = sqlSession.selectList("user.selectAll"); System.out.println(objects.get(0).getAll()); } }执行结果id=1 username=admin password=1ed1252369b609c48a93ea80b57f52ed

2023年03月10日
170 阅读
0 评论
0 点赞

2022-03-24

2022年03月24日
172 阅读
0 评论
0 点赞