ebean icon indicating copy to clipboard operation
ebean copied to clipboard

if mysql table field has desc, CQueryBuilder return bad sql

Open zh-or opened this issue 1 year ago • 0 comments

Expected behavior

build sql is

select t0.id, t0.content, t0.create_time, t0.desc from link_test t0 order by t0.id 

Actual behavior

build sql is

select t0.id, t0.content, t0.create_time, t0.desc from link_test t0 order by t0.id t0.desc

Steps to reproduce

mysql table sql

CREATE TABLE `link_test` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`content` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
	`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`desc` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
	PRIMARY KEY (`id`) USING BTREE
)
COMMENT='test table'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=7
;

pom.xml


    <dependencies>
        <dependency>
            <groupId>io.ebean</groupId>
            <artifactId>ebean-mysql</artifactId>
            <version>14.1.0</version>
        </dependency>

        <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
        </dependency>
    </dependencies>



    <build>


        <plugins>

            <plugin> <!-- perform ebean enhancement -->
                <groupId>io.ebean</groupId>
                <artifactId>ebean-maven-plugin</artifactId>
                <!--<version>14.1.0</version>-->
                <version>14.1.0</version>
                <extensions>true</extensions>
            </plugin>

            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.10.1</version>
                <configuration>
                    <annotationProcessorPaths>
                        <path> <!-- generate ebean query beans -->
                            <groupId>io.ebean</groupId>
                            <artifactId>querybean-generator</artifactId>
                            <version>14.1.0</version>
                        </path>
                    </annotationProcessorPaths>
                </configuration>
            </plugin>


        </plugins>
    </build>

bean java code

@Entity
public class LinkTest {
    @Id
    public int id;
    public String content;
    public Date createTime;
    public String desc;
    ...getter ....setter
}

test code

   public static void main(String[] args) {
        DataSourceConfig dataSourceConfig = new DataSourceConfig();
        dataSourceConfig.setUrl("jdbc:mysql://xxxxx");
        dataSourceConfig.setUsername("xxx");
        dataSourceConfig.setPassword("xxxxx");
        dataSourceConfig.setMaxConnections(100);
        dataSourceConfig.setMinConnections(10);

        DatabaseConfig config = new DatabaseConfig();
        config.setDataSourceConfig(dataSourceConfig);

        Database db = DatabaseFactory.create(config);

        List<LinkTest> list = db.find(LinkTest.class).where().orderBy().desc("id").findList();

        System.out.println("list size:" + list.size());
    }

Runtime screenshot image

Exception in thread "main" jakarta.persistence.PersistenceException: Query threw SQLException: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 't0.desc' at line 1 Bind values:[] Query was:select t0.id, t0.content, t0.create_time, t0.desc from link_test t0 order by t0.id t0.desc
	at io.ebean.config.dbplatform.SqlCodeTranslator.translate(SqlCodeTranslator.java:85)
	at io.ebean.config.dbplatform.DatabasePlatform.translate(DatabasePlatform.java:212)
	at io.ebeaninternal.server.query.CQueryEngine.translate(CQueryEngine.java:139)
	at io.ebeaninternal.server.query.DefaultOrmQueryEngine.translate(DefaultOrmQueryEngine.java:37)
	at io.ebeaninternal.server.core.OrmQueryRequest.translate(OrmQueryRequest.java:58)
	at io.ebeaninternal.server.query.CQuery.createPersistenceException(CQuery.java:647)
	at io.ebeaninternal.server.query.CQueryEngine.findMany(CQueryEngine.java:364)
	at io.ebeaninternal.server.query.DefaultOrmQueryEngine.findMany(DefaultOrmQueryEngine.java:126)
	at io.ebeaninternal.server.core.OrmQueryRequest.findList(OrmQueryRequest.java:403)
	at io.ebeaninternal.server.core.DefaultServer.findList(DefaultServer.java:1430)
	at io.ebeaninternal.server.core.DefaultServer.findList(DefaultServer.java:1409)
	at io.ebeaninternal.server.querydefn.DefaultOrmQuery.findList(DefaultOrmQuery.java:1533)
	at test.EbeanTest.main(EbeanTest.java:26)
Caused by: 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 't0.desc' at line 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:972)
	at io.ebean.datasource.pool.ExtendedPreparedStatement.executeQuery(ExtendedPreparedStatement.java:113)
	at io.ebeaninternal.server.query.CQuery.prepareResultSet(CQuery.java:341)
	at io.ebeaninternal.server.query.CQuery.prepareBindExecuteQueryWithOption(CQuery.java:298)
	at io.ebeaninternal.server.query.CQuery.prepareBindExecuteQuery(CQuery.java:294)
	at io.ebeaninternal.server.query.CQueryEngine.findMany(CQueryEngine.java:343)
	... 6 more
CLOSING busy connection: name[db18] startTime[1719802980613] busySeconds[0] stackTrace[] stmt[null]
Disconnected from the target VM, address: '127.0.0.1:25555', transport: 'socket'

Process finished with exit code 1

zh-or avatar Jul 01 '24 03:07 zh-or