Tag Archives: SQL

[Solved] Mybatis add dependencies Error: org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration.

Mybatis generates an error after adding a dependency: org.springframework.core.nestedioexception: failed to parse config resource: class path resource [mybatis/mybatis config. XML]; Needed exception is org.apache.ibatis.builder.builderexception: error parsing SQL mapper configuration. Cause: org.apache.ibatis.logging.logexception: error setting log implementation. Cause: java.lang.reflect.invocationtargetexceptionlog4j

 

Attachment: springboot running error:

Problem recurrence

After adding logs for mybatis configuration

<settings>
    <setting name="logImpl" value="LOG4J" />
</settings>

Dependencies in Maven will have red wavy lines: log4j:log4j:unknown

Check the dependency of log4j in pom.xml

<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <scope>compile</scope>
</dependency>

Causes and Solutions

The version number is not written. After consulting the data, it is found that:

Because:
log4j has changed the jar package since version 1.2.17.
for example, the MVN dependency of versions 1.2.17 and earlier is written as follows:

<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>

However, the MVN dependency of versions after 1.2.17 is written as follows:

<!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-core -->
<dependency>
    <groupId>org.apache.logging.log4j</groupId>
    <artifactId>log4j-core</artifactId>
    <version>2.13.1</version>
</dependency>

Therefore, the declaration displayed according to the first method uses version 1.2.17, and the problem is solved.

Later, I want to modify the dependency settings in POM to reproduce the problem. I guess the reason is that Maven has handled the relevant dependencies and needs Maven clean operation

Investigate the reasons:

We know that the dependency in springboot does not need to write the version number because of its automatic version arbitration mechanism. We click the parent project of the project

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.5.4</version>
    <relativePath/> <!-- lookup parent from repository -->
</parent>

CTRL + left click spring boot starter parent to find that the project also has a parent project spring boot dependencies

<parent>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-dependencies</artifactId>
  <version>2.5.4</version>
</parent>

After entering spring boot dependencies, you can see that it declares the version number and dependencies of jar packages commonly used in the development process

We searched log4j and found that

<log4j2.version>2.14.1</log4j2.version>

It looks like the same reason as we found before.
P.S. SpringBoot runs reporting errors:
org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'bookController': Unsatisfied dependency expressed through field 'bookService'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'bookService': Unsatisfied dependency expressed through field 'bookMapper'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'bookMapper' defined in file [D:\tsgl\target\classes\com\by\tsgl\mapper\BookMapper.class]: Unsatisfied dependency expressed through bean property 'sqlSessionFactory'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sqlSessionFactory' defined in class path resource [org/mybatis/spring/boot/autoconfigure/MybatisAutoConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.apache.ibatis.session.SqlSessionFactory]: Factory method 'sqlSessionFactory' threw exception; nested exception is org.springframework.core.NestedIOException: Failed to parse config resource: class path resource [mybatis/mybatis-config.xml]; nested exception is org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: org.apache.ibatis.logging.LogException: Error setting Log implementation. Cause: java.lang.reflect.InvocationTargetException

How to Solve SQL comments error in the mybatis query

Query condition modification, comment out the old sql, resulting in an error
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property=’command.bldgLevel’, mode=IN, javaType=class java.lang.Object, jdbcType=VARCHAR, numericScale=null, resultMapId=’null’, jdbcTypeName=’null’, expression=’null’}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #2 with JdbcType VARCHAR . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #2 with JdbcType VARCHAR . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1).

 

Solution:
Remove useless comments

How to Solve MySQL version 5.7+ Group by group error

MySQL-this is incompatible with sql_mode=only_full_group_By error resolution

1. Principle level
this error occurs in MySQL version 5.7 and above:

   The default sql configuration for mysql 5.7 is: sql_mode="ONLY_FULL_GROUP_BY", which strictly enforces the "SQL92 standard".

   When upgrading from 5.6 to 5.7, most of them choose to adjust sql_mode to make it consistent with 5.6 in order to be as compatible as possible with the program.

2. SQL level

    In sql execution, the cause appears.

    Simply put: the output is called target list, which is the field followed by select, and a place group by column, which is

    group by followed by the field. Because the ONLY_FULL_GROUP_BY setting is turned on, so if a field is not in the target list 

    and group by fields, or the value of the aggregation function, then this sql query is considered illegal by mysql and will report an error.

Translated with www.DeepL.com/Translator (free version)

1. View the statement of SQL_mode is as follows

select @@GLOBAL.sql_mode;

Second, the solution – (recommended solution two)
① solution one: sql statement temporarily modify sql_mode
set @@GLOBAL.sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Question.

          After restarting the mysql database service, ONLY_FULL_GROUP_BY will still appear.

② Solution 2: perfect solution.

To modify the MySQL configuration file, add SQL manually_ Mode is mandatory. Only is not required_ FULL_ GROUP_ By attribute,
VI/etc/my. CNF
Add or modify configuration file:
sql_mode =‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISIN_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’

Restart the MySQL service and solve it successfully.

	service mysqld restart

Clickhouse error: XXXX.XXXX_local20211009 (8fdb18e9-bb4c-42d8-8fdb-18e9bb4c02d8): auto…

Code: 49, e.displayText() = DB::Exception: Part 20211009_67706_67706_0 is covered by 20211009_67118_67714_12 but should be merged into 20211009_67706_67715_1. This shouldn’t happen often., Stack trace (when copying this message, always include the lines below):
Error Messages:
XXXX.XXXX_local20211009 (8fdb18e9-bb4c-42d8-8fdb-18e9bb4c02d8): auto DB::StorageReplicatedMergeTree::processQueueEntry(ReplicatedMergeTreeQueue::SelectedEntryPtr)::(anonymous class)::operator()(DB::StorageReplicatedMergeTree::LogEntryPtr &) const: Code: 49, e.displayText() = DB::Exception: Part 20211009_67706_67706_0 is covered by 20211009_67118_67714_12 but should be merged into 20211009_67706_67715_1. This shouldn’t happen often., Stack trace (when copying this message, always include the lines below):
Solution 1.
1. Try to delete the local table and the distributed table XXXX.XXXX_local20211009

Mapper.xml Error: Error setting non null for parameter #3 with JdbcType null.

Record the problems encountered in learning SSM framework 3
Exception Reporting
Message Request processing failed; nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property=‘id’, mode=IN, javaType=class java.lang.Integer, jdbcType=null, numericScale=null, resultMapId=‘null’, jdbcTypeName=‘null’, expression=‘null’}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #3 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).
Description The server encountered an unexpected condition that prevented it from completing the request.
The problem occurs mainly in the mapper.xml file and can be due to several reasons.
1. two #’s are written together and will be identified incorrectly (my error)

<update id="updateById" parameterType="main.java.com.dy.domain.News">
        update news
        <set>
            <include refid="News_update"/>
        </set>
        where id=##{id}
    </update>

2. JavaEE comments appear inside/**/

<update id="updateById" parameterType="main.java.com.dy.domain.News">
        update news
        <set> /*  */
            <include refid="News_update"/>
        </set>
        where id=#{id}
    </update>

3. #{id} covered with quotation marks

<update id="updateById" parameterType="main.java.com.dy.domain.News">
        update news
        <set>
            <include refid="News_update"/>
        </set>
        where id='#{id}'
    </update>

All the above detailed errors may lead to errors in the execution of SQL statements, so we should be as careful as possible in the future development process.

Error creating bean with name ‘dataSourceScriptDatabaseInitializer‘ defined in class path resource [

Error creating bean with name ‘dataSourceScriptDatabaseInitializer’ defined in class path resource [org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]: Unsatisfied dependency expressed through method ‘dataSourceScriptDatabaseInitializer’ parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘dataSource’ defined in class path resource

Error creating bean with name 'dataSourceScriptDatabaseInitializer' defined in class path resource [org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]: Unsatisfied dependency expressed through method 'dataSourceScriptDatabaseInitializer' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource

The reason for my error is that I made an error when using @springboottest automatic injection when I first learned spring data JPA

@SpringBootTest

//springbootTest The server is not turned on by default,

public class MainTest {
    @Autowired
UserRepository userRepository;

@Test
    void t1(){
    System.out.println(userRepository);
}
}

I searched the Internet for several hours and debugged it. I found that the datasource was always empty, because it was tested and run in the test method at that time, and the errors given were always the same as above. However, I clearly configured the database in application.yml, and later found it during overall debugging, Failed to load driver class com.mysql.cj.jdbc.driver in either of hikariconfig class loader or thread context classloader

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.25</version>
        </dependency>

Of course, it may also be due to other errors. If you also encounter this error, you can see whether you have added MySQL dependencies to your dependencies

SQL editor and reconnect [an exception when MySQL (workbench) updates data] [error code: 1175]

Abnormal conditions:

  Error Code: 1175.
You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.To disable safe mode, toggle the option in Preferences -> SQL Editor -> Query Editor and reconnect.

When using the secure update mode, the update statement must use a keyword column attribute to limit the scope of the update. If you do not use keyword columns to limit the scope or update all records, you cannot use safe update mode.

The solution is to execute the following statement to set it to non secure update mode.

#Non-secure mode
SET SQL_SAFE_UPDATES=0;
#secure mode
SET SQL_SAFE_UPDATES=1;

Bulk Update Error: #Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax； check the m

Error:
mybatis plus foreach batch insert is OK. Change it to update and keep reporting errors. It is OK to copy the SQL and execute it separately.
reason:
the insert statement supports batch and can be written in one statement:

INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...), (value1,value2,value3,...), (value1,value2,value3,...), (value1,value2,value3,...), (value1,value2,value3,...),(value1,value2,value3,...)；

Update does not support batch. There are multiple statements corresponding to cyclic batch

update table set c1=v1;
update table set c1=v2;
update table set c1=v3;
.
.
.
update table set c1=v...;

Mybatis does not support executing multiple statements (multiple semicolons) by default
solution:
add the allowmultiqueries = true parameter to the database connection

Allowmultiqueries = true function:
1. You can carry semicolons after SQL statements to realize multi statement execution
2. You can execute batch processing and issue multiple SQL statements at the same time

[Solved] Pyodbc.ProgrammingError: No results. Previous SQL was not a query.

Call the stored procedure on the remote sqlserver server with Python. Code fragment:


    conn = pyodbc.connect(SERVER=host, UID=user, PWD=password, DATABASE=dbname,
                            DRIVER=driver)
    cur = conn.cursor()
    if not cur:
        raise (NameError, 'Database connection error)
    else:
        cur.execute("EXEC GetLastData")
        resList = list()
        resList = cur.fetchall()

Execution error:

pyodbc.ProgrammingError: No results.  Previous SQL was not a query.

After checking, the stored procedure can be executed normally in the sqlserver environment. It seems that there is a problem when calling pyodbc. A similar problem is found on stackoverflow. The answer is as follows:
the problem was solved by adding set NOCOUNT on; to the beginning of the anonymous code block. That statement suppresses the record count values generated by DML statements like UPDATE … and allows the result set to be retrieved directly.

The problem is solved by adding it to the beginning of the anonymous code block. This statement suppresses the record count value generated by the DML statement and allows the set result to be retrieved directly. SET NOCOUNT ON; UPDATE …

So add a sentence set NOCOUNT on to the stored procedure

CREATE proc [dbo].[GetLastData]
AS
BEGIN

SET NOCOUNT ON

declare @begindate datetime,@enddate datetime
select @begindate=CONVERT(varchar(7),GETDATE(),120)+'-01'
select @enddate=DATEADD(MONTH,1,@begindate)

nested exception is org.apache.ibatis.builder.BuilderException: Error evaluating expression [Solved]

This exception is usually a problem with dynamic SQL. Find the corresponding SQL and check the dynamic SQL syntax according to the following prompt information.

Problem description

Exception information:
needed exception is org.apache.ibatis.builder.builderexception: error evaluating expression 'ides'. Return value (806) was not Iterable.

According to the exception prompt information, find the dynamic SQL statement where ides is located.

<foreach  collection="ides"  index="index" item="ides" open="(" separator="," close=")">
     #{ides}
</foreach>
...
<foreach  collection="ides"  index="index" item="ides" open="(" separator="," close=")">
     #{ides}
</foreach>

Finally, it is found that
two <foreach></ foreach> Statement operates on the same item variable, resulting in the failure of dynamic SQL splicing of the latter.

Solution:

Change the item property in any statement to a different value.

<foreach  collection="ides"  index="index" item="idess" open="(" separator="," close=")">
     #{idess}
</foreach>
...
<foreach  collection="ides"  index="index" item="ides" open="(" separator="," close=")">
     #{ides}
</foreach>

### The error may involve defaultParameterMap ### The error occurred while setting parameters

I encountered an error today. Running rankdao. Selectbyid (ID) directly reported an error to me. I didn’t understand it

Error message:

2021-09-22 10:40:58.824 ERROR 8364 --- [nio-8888-exec-4] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank WHERE id=1' at line 1
### The error may exist in com/brilliantZC/music/dao/MusicRankDao.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT id,song_list_id,consumer_id,score FROM rank WHERE id=?
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank WHERE id=1' at line 1
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank WHERE id=1' at line 1] with root cause

At the beginning, Baidu has various methods, some say

    an error is reported when the value of the column in the created table is less than the actual value. The @ resource or @ Autowired annotation is forgotten on the service interface introduced by the controller layer. The method called by the controller layer is private, resulting in distribution failure. Check whether the mapper interface code conforms to the specification (automatically ignored by mybatis plus)

    I tried again, but I still couldn’t, so I took the wrong SQL statement and executed it

    SELECT id,song_list_id,consumer_id,score FROM rank WHERE id=1

    Still report an error

    music> SELECT id,song_list_id,consumer_id,score FROM rank WHERE id=1
[2021-09-22 10:55:58] [42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank WHERE id=1' at line 1

    Then I was confused. Then I removed the query statement and still reported an error

    SELECT id,song_list_id,consumer_id,score FROM rank

    Then Baidu saw that because rank is a keyword, it can not be directly used as a non keyword. It needs to be used with `. It can be executed after replacement

    SELECT id,song_list_id,consumer_id,score FROM `rank`

    Then I modified the table name and solved the error!!!