Java CCJSqlParserManager类代码示例

import net.sf.jsqlparser.parser.CCJSqlParserManager; //导入依赖的package包/类
public void testParse() throws Exception {
	CCJSqlParserManager parserManager = new CCJSqlParserManager();
	BufferedReader in = new BufferedReader(new FileReader("testfiles" + File.separator + "simple_parsing.txt"));
	String statement = "";
	while (true) {
		try {
			statement = CCJSqlParserManagerTest.getStatement(in);
			if (statement == null)
				break;
				
			Statement parsedStm = parserManager.parse(new StringReader(statement));
			//System.out.println(statement);
		} catch (JSQLParserException e) {
			throw new TestException("impossible to parse statement: " + statement, e);
		}
	}
}
 

import net.sf.jsqlparser.parser.CCJSqlParserManager; //导入依赖的package包/类
@Test
public void testParse() throws Exception {
    CCJSqlParserManager parserManager = new CCJSqlParserManager();
    URL simpleParsing = Thread.currentThread().getContextClassLoader().getResource("simple_parsing.txt");
    try (BufferedReader in = new BufferedReader(new InputStreamReader(simpleParsing.openStream()))) {
        String statement = "";
        while (true) {
            try {
                statement = CCJSqlParserManagerTest.getStatement(in);
                if (statement == null) {
                    break;
                }

                Statement parsedStm = parserManager.parse(new StringReader(statement));
                //System.out.println(statement);
            } catch (JSQLParserException e) {
                throw new TestException("impossible to parse statement: " + statement, e);
            }
        }
    }
}
 

import net.sf.jsqlparser.parser.CCJSqlParserManager; //导入依赖的package包/类
@BeforeClass
public static void startServer() throws Exception {
    Thread.sleep(1000);
    // LOG = LoggerFactory.getLogger(BaseTest.class);
    // manager = DDFManager.get("spark");
    /*
    manager = DDFManager.get("jdbc", new JDBCDataSourceDescriptor(new
            DataSourceURI("jdbc:mysql://localhost/testdb"), new
            JDBCDataSourceDescriptor.JDBCDataSourceCredentials("pauser",
            "papwd"), null));
    DataSourceDescriptor ds = manager.getDataSourceDescriptor();
    if (ds instanceof
            JDBCDataSourceDescriptor) {
        System.out.println("hello");
    }
    DDF ret = manager.sql2ddf("select * from testtable", "jdbc");*/
    // Add 2 test ddfs.
    manager = DDFManager.get(DDFManager.EngineType.SPARK);

    parser = new CCJSqlParserManager();
}
 

import net.sf.jsqlparser.parser.CCJSqlParserManager; //导入依赖的package包/类
@Inject
public QueryId(@Assisted String sql, CCJSqlParserManager pm) throws QueryException {
    try {
        Statement stmt = pm.parse(new StringReader(sql));
        if (stmt instanceof Select) {
            this.select = (Select) stmt;
            this.sql = SQL_FORMATTER.format(this.select.toString());
            this.md5 = getMD5(this.sql);
            this.md5Raw = getMD5(sql);
            this.id = QueryFactory.nextUID();
        } else {
            throw new QueryException("Only SELECT statements allowed!");
        }
    } catch (JSQLParserException ex) {
        throw new QueryException(ex.getCause().getMessage());
    }
}
 

import net.sf.jsqlparser.parser.CCJSqlParserManager; //导入依赖的package包/类
public AbstractParser() throws SchedulerException {
    
    final SchedulerFactory schedulerFactory = new StdSchedulerFactory();
    final Scheduler scheduler = schedulerFactory.getScheduler();
    
    this.injector = Guice.createInjector(new AbstractModule() {
        
        @Override
        protected void configure() {
            install(new FactoryModuleBuilder().build(ResourceFactory.class));
            bind(MetadataFactoryIf.class).to(MetadataFactoryTest.class);
            bind(CCJSqlParserManager.class).toInstance(new CCJSqlParserManager());
            bind(ConnectionFactory.class).toInstance(new ConnectionFactoryTest());
            bind(Scheduler.class).toInstance(scheduler);
        }
    });
    this.rf = injector.getInstance(ResourceFactory.class);
    this.mf = injector.getInstance(MetadataFactoryIf.class);
}
 

import net.sf.jsqlparser.parser.CCJSqlParserManager; //导入依赖的package包/类
/**
 * "INSERT OR REPLACE"语法会报错，SqlParser不支持
 *
 * @throws JSQLParserException
 */
@Test(expected = Exception.class)
public void testInsertOrReplace() throws JSQLParserException {
    String sql = "INSERT OR REPLACE INTO \"USER\" (\"_id\",\"UID\",\"NAME\") VALUES (?,?,?)";

    CCJSqlParserManager pm = new CCJSqlParserManager();

    Statement statement = pm.parse(new StringReader(sql));
}
 

import net.sf.jsqlparser.parser.CCJSqlParserManager; //导入依赖的package包/类
/**
 * To make ddal-jsqlparser work well, JSqlParser should include the feature of 'support getting jdbc parameter index'.
 * And this feature is provided on the version of {@link <a href="https://github.com/JSQLParser/JSqlParser/releases/tag/jsqlparser-0.9.7">0.9.7</a>}.
 * This method is designed to check the necessary feature.
 */
private static void checkJSqlParserFeature() throws JSQLParserException {
    CCJSqlParserManager parserManager = new CCJSqlParserManager();
    String sql = "SELECT * FROM tab_1 WHERE tab_1.col_1 = ? AND col_2 IN (SELECT DISTINCT col_2 FROM tab_2 WHERE col_3 LIKE ? AND col_4 > ?) LIMIT ?, ?";
    Select select = (Select) parserManager.parse(new StringReader(sql));
    PlainSelect selectBody = (PlainSelect) select.getSelectBody();
    //
    AndExpression andExpression = (AndExpression) selectBody.getWhere();
    EqualsTo equalsTo = (EqualsTo) andExpression.getLeftExpression();
    JdbcParameter jdbcParameter = (JdbcParameter) equalsTo.getRightExpression();
    Integer index1 = jdbcParameter.getIndex();
    if (index1 != 1) {
        throw new IllegalStateException("Current version of JSQLParser doesn't support the feature of 'support "
                                        + "get jdbc parameter index'");
    }
    //
    InExpression inExpression = (InExpression) andExpression.getRightExpression();
    SubSelect subSelect = (SubSelect) inExpression.getRightItemsList();
    PlainSelect subSelectBody = (PlainSelect) subSelect.getSelectBody();
    AndExpression subAndExpression = (AndExpression) subSelectBody.getWhere();
    LikeExpression likeExpression = (LikeExpression) subAndExpression.getLeftExpression();
    if (((JdbcParameter) likeExpression.getRightExpression()).getIndex() != 2) {
        throw new IllegalStateException(
                                        "Current version of JSQLParser doesn't support the feature of 'support get jdbc parameter index'");
    }
    //
    GreaterThan greaterThan = (GreaterThan) subAndExpression.getRightExpression();
    if (((JdbcParameter) greaterThan.getRightExpression()).getIndex() != 3) {
        throw new IllegalStateException(
                                        "Current version of JSQLParser doesn't support the feature of 'support get jdbc parameter index'");
    }
    //
    Expression offset = selectBody.getLimit().getOffset();
    Expression rowCount = selectBody.getLimit().getRowCount();
    if (((JdbcParameter) offset).getIndex() != 4 || ((JdbcParameter) rowCount).getIndex() != 5) {
        throw new IllegalStateException(
                                        "Current version of JSQLParser doesn't support the feature of 'support get jdbc parameter index'");
    }
}
 

import net.sf.jsqlparser.parser.CCJSqlParserManager; //导入依赖的package包/类
private Statement getStatement() {
   if (statement == null) {
      try {
         statement = new CCJSqlParserManager().parse(new StringReader(sql));
      } catch (JSQLParserException e) {
         log.error(e.getMessage(), e);
         throw new CibetJdbcException(e.getMessage(), e);
      }
   }
   return statement;
}
 

import net.sf.jsqlparser.parser.CCJSqlParserManager; //导入依赖的package包/类
public static PlainSelect parseQuery(CCJSqlParserManager parserManager, String query) throws JSQLParserException {
    PlainSelect parsedQueryString;
    if(!query.toLowerCase().trim().startsWith("select")) {
        query = FAKE_SELECT_PREFIX + query;
    }
    parsedQueryString =
            (PlainSelect) ((Select) parserManager.parse(new StringReader(query)))
                    .getSelectBody();
    return parsedQueryString;
}
 

import net.sf.jsqlparser.parser.CCJSqlParserManager; //导入依赖的package包/类
/**
	 * @param args
	 * @throws JSQLParserException
	 */
	public static void main(String[] args) throws JSQLParserException {
		CCJSqlParserManager pm = new CCJSqlParserManager();
//		String sql = "update XXX set x=10 where x in( SELECT * FROM MY_TABLE1, MY_TABLE2, (SELECT * FROM MY_TABLE3) LEFT OUTER JOIN MY_TABLE4 "
//		String sql = "update XXX set x=10 where x in( SELECT * FROM MY_TABLE1, MY_TABLE2, (SELECT * FROM MY_TABLE3) LEFT OUTER JOIN MY_TABLE4 "
//		String sql = "update XXX set x=10 where x in( SELECT * FROM MY_TABLE1, MY_TABLE2, (SELECT * FROM MY_TABLE3) LEFT OUTER JOIN MY_TABLE4 "
//		String sql = "update XXX set x=10 where x in( SELECT * FROM MY_TABLE1, MY_TABLE2, (SELECT * FROM MY_TABLE3) LEFT OUTER JOIN MY_TABLE4 "
//				+ " WHERE ID = (SELECT MAX(ID) FROM MY_TABLE5) AND ID2 IN (SELECT * FROM MY_TABLE6) and x= ? )";
//		String sql2 = "update XXX set x =10 where aa like '%55' ";
//		String sql3 = "select now ";
//		String sql4 = "delete from xxx ";
//		String sql5 = "insert into xxx values (10,20)";

//		String file ="d:/tmp/sample-query2.out";
//		String sql=new String(FileUtil.readAll(new File(file)));

		//String sql="select * from sss where is1='a' ";
		//String sql="call USER() ";
		String sql = "SELECT /* line.b612.domain.sticker.StickerCategoryStickerRepository.findStickerIdByCategoryIdOrderByOrderz */\n" +
				"                        stickerId\n" +
				"                FROM\n" +
				"                        sticker_category_sticker\n" +
				"                WHERE\n" +
				"                        categoryId = @{1}\n" +
				"                ORDER BY\n" +
				"                        orderz DESC";
		System.out.println(sql);
//		EscapeLiteralSQL esql =new EscapeLiteralSQL(sql);
//		esql.process();
//		System.out.println(esql.getParsedSql());
//
		System.out.println(SqlTables.parseTable(sql));
//		System.out.println(SqlTables.getInstance().doAction(sql2));
//		System.out.println(SqlTables.getInstance().doAction(sql3));
//		System.out.println(SqlTables.getInstance().doAction(sql4));
//		System.out.println(SqlTables.getInstance().doAction(sql5));
		}
 

import net.sf.jsqlparser.parser.CCJSqlParserManager; //导入依赖的package包/类
public ThirdEyeQueryParser(String sql) throws Exception {
  this.sql = sql;

  CCJSqlParserManager parserManager = new CCJSqlParserManager();
  Statement statement = parserManager.parse(new StringReader(sql));
  ((Select) statement).getSelectBody().accept(this);

  if (query.getStart() == null || query.getEnd() == null) {
    throw new Exception("Must provide BETWEEN clause");
  }
}
 

import net.sf.jsqlparser.parser.CCJSqlParserManager; //导入依赖的package包/类
/**
* 
* @param sql
* @return
* @throws JSQLParserException
*/
  public static StatementFinder getStatementFinder(String sql) {

      CCJSqlParserManager pm = new CCJSqlParserManager();
      net.sf.jsqlparser.statement.Statement statement = null;
      StatementFinder statementFinder = null;
      try {
          statement = pm.parse(new StringReader(sql));
          if (statement instanceof Select) {
              Select selectStatement = (Select) statement;
              statementFinder = new SelectItemFinder(selectStatement);
          } else if (statement instanceof Insert) {
              Insert insertStatement = (Insert) statement;
              statementFinder = new InsertItemFinder(insertStatement);
          }
          else if (statement instanceof Delete) {
              Delete deleteStatement = (Delete) statement;
              statementFinder = new DeleteItemFinder(deleteStatement);
          } else if (statement instanceof Update) {
              Update updateStatement = (Update) statement;
              statementFinder = new UpdateItemFinder(updateStatement);
          }
      } catch (JSQLParserException e) {
          e.printStackTrace();
          throw new RuntimeException(e);
      }
      return statementFinder;
  }
 

import net.sf.jsqlparser.parser.CCJSqlParserManager; //导入依赖的package包/类
@Test
public void intercept() throws Exception {
    String sql = "INSERT INTO person (id, name) VALUES (1, 'KK')";

    CCJSqlParserManager pm     = new CCJSqlParserManager();
    Insert              insert = (Insert) pm.parse(new StringReader(sql));

    Insert insertProxy = new InsertOrReplaceProxy().getInstance(Insert.class, insert);

    Table table = insertProxy.getTable();

    Assert.assertEquals("person", table.getName());

    String string = insertProxy.toString();

    Assert.assertEquals("INSERT OR REPLACE INTO person (id, name) VALUES (1, 'KK')", string);
}
 

import net.sf.jsqlparser.parser.CCJSqlParserManager; //导入依赖的package包/类
public static void main(String[] args) throws Exception {
		System.gc();
		System.out.println(Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory());
		CCJSqlParserManager parserManager = new CCJSqlParserManager();

/*		String longQuery = new String(
			"select  *  from  (  SELECT  intermediate.id  as  id  ,  intermediate.date  as          "
				+ "date  FROM  (  SELECT  DISTINCT   (  id  )   FROM  (  SELECT                           "
				+ "wct_workflows.workflow_id  as  id  ,  wct_transaction.date  as  date  FROM             "
				+ "wct_audit_entry  ,  wct_transaction  ,  wct_workflows  WHERE                           "
				+ "(  wct_audit_entry.privilege  =  'W'  or  wct_audit_entry.privilege  =                 "
				+ "'C'  )  and  wct_audit_entry.outcome  =  't'  and                                      "
				+ "wct_audit_entry.transaction_id  =  wct_transaction.transaction_id  and                 "
				+ "wct_transaction.user_id  = 164 and  wct_audit_entry.object_id  =                       "
				+ "wct_workflows.active_version_id ))) UNION  SELECT  wct_workflows.workflow_id  as       "
				+ "id  ,  wct_transaction.date  as  date  FROM  wct_audit_entry  ,                        "
				+ "wct_transaction  ,  wct_workflows  WHERE  (  wct_audit_entry.privilege  =              "
				+ "'W'  or  wct_audit_entry.privilege  =  'C'  )  and  wct_audit_entry.outcome            "
				+ "=  't'  and  wct_audit_entry.transaction_id  =                                         "
				+ "wct_transaction.transaction_id  and  wct_transaction.user_id  = 164 and                "
				+ "afdf=  (  select  wct_audit_entry.object_id  from  wct_audit_entry  ,                  "
				+ "wct_workflow_archive  where  wct_audit_entry.object_id  =                              "
				+ "wct_workflow_archive.archive_id  and  wct_workflows.workflow_id  =                     "
				+ "wct_workflow_archive.workflow_id  )                                                    "
				+ "UNION  SELECT  wct_workflows.workflow_id                                               "
				+ "as  id  ,  wct_transaction.date  as  date  FROM  wct_audit_entry  ,                    "
				+ "wct_transaction  ,  wct_workflows  WHERE  (  wct_audit_entry.privilege  =              "
				+ "'W'  OR  wct_audit_entry.privilege  =  'E'  OR  wct_audit_entry.privilege  =           "
				+ "'A'  )  and  wct_audit_entry.outcome  =  't'  and                                      "
				+ "wct_audit_entry.transaction_id  =  wct_transaction.transaction_id  and                 "
				+ "wct_transaction.user_id  = 164 and  wct_audit_entry.object_id  =                       "
				+ "wct_workflows.workflow_id    UNION SELECT * FROM interm2  ,  wct_workflow_docs  WHERE  "
				+ "interm2.id  =  wct_workflow_docs.document_id  ORDER BY  id  ,  date  DESC              ");
*/
		String longQuery = new String("select * from k where ID > 4");
		
		
		/*		String longQuery = "select  *  from  (  SELECT  intermediate.id  as  id  ,  intermediate.date  as          "
						+ "date  FROM  (  SELECT  DISTINCT   (  id  )   FROM  (  SELECT                           "
						+ "wct_workflows.workflow_id  as  id  ,  wct_transaction.date  as  date  FROM             "
						+ "wct_audit_entry  ,  wct_transaction  ,  wct_workflows  WHERE                           "
						+ "(  wct_audit_entry.privilege  =  'W'  or  wct_audit_entry.privilege  =                 "
						+ "'C'  ))))";
*/
/*		String longQuery = "select  *  from  d WHERE                           "
				+ "(  wct_audit_entry.privilege  =  'W'  or  wct_audit_entry.privilege  =                 "
				+ "'C'  )  and  wct_audit_entry.outcome  =  't'  and                                      "
				+ "wct_audit_entry.transaction_id  =  wct_transaction.transaction_id  and                 "
				+ "wct_transaction.user_id  = 164 and  wct_audit_entry.object_id  =                       "
				+ "wct_workflows.active_version_id ";
*/
		StringReader stringReader = new StringReader(longQuery);
	    Statement statement = parserManager.parse(stringReader);
//		stringReader = new StringReader(longQuery);
//		Statement statement2 = parserManager.parse(stringReader);
//		stringReader = null;
		//statement2 = null;
		statement = null;
		parserManager = null;
		longQuery = null;
		System.gc();
		System.out.println(Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory());


	}
 

import net.sf.jsqlparser.parser.CCJSqlParserManager; //导入依赖的package包/类
/**
 * Loads an object by primary key. It also verifies that the object falls within the results of a given query.
 * @param persistence the persistence object
 * @param database the database (connection provider)
 * @param entityName the name of the entity to load
 * @param pk the primary key object
 * @param hqlQueryString the query (where condition) that the object must fulfill
 * @param rootObject the OGNL root object against which to evaluate the query string.
 * @return the loaded object, or null if an object with that key does not exist or falls outside the query.
 */
public static Object getObjectByPk(
        Persistence persistence, String database, String entityName,
        Serializable pk, String hqlQueryString, Object rootObject) {
    TableAccessor table = persistence.getTableAccessor(database, entityName);
    List<Object> result;
    PropertyAccessor[] keyProperties = table.getKeyProperties();
    OgnlHqlFormat hqlFormat = OgnlHqlFormat.create(hqlQueryString);
    String formatString = hqlFormat.getFormatString();
    Object[] ognlParameters = hqlFormat.evaluateOgnlExpressions(rootObject);
    int p = ognlParameters.length;
    Object[] parameters = new Object[p + keyProperties.length];
    System.arraycopy(ognlParameters, 0, parameters, 0, p);
    try {
        PlainSelect parsedQuery = parseQuery(new CCJSqlParserManager(), formatString);
        if(parsedQuery.getWhere() == null) {
            return getObjectByPk(persistence, database, entityName, pk);
        }

        Alias mainEntityAlias = getEntityAlias(entityName, parsedQuery);
        net.sf.jsqlparser.schema.Table mainEntityTable;
        if(mainEntityAlias != null) {
            mainEntityTable = new net.sf.jsqlparser.schema.Table(null, mainEntityAlias.getName());
        } else {
            mainEntityTable = new net.sf.jsqlparser.schema.Table();
        }


        for(int i = 0; i < keyProperties.length; i++) {
            PropertyAccessor propertyAccessor = keyProperties[i];
            EqualsTo condition = new EqualsTo();
            parsedQuery.setWhere(
                    new AndExpression(condition, new Parenthesis(parsedQuery.getWhere())));
            net.sf.jsqlparser.schema.Column column =
                    new net.sf.jsqlparser.schema.Column(mainEntityTable, propertyAccessor.getName());
            condition.setLeftExpression(column);
            JdbcParameter jdbcParameter = new JdbcParameter();
            jdbcParameter.setIndex(p + i + 1);
            condition.setRightExpression(jdbcParameter);
            parameters[p + i] = propertyAccessor.get(pk);
        }

        String fullQueryString = parsedQuery.toString();
        if(fullQueryString.toLowerCase().startsWith(FAKE_SELECT_PREFIX)) {
            fullQueryString = fullQueryString.substring(FAKE_SELECT_PREFIX.length());
        }
        Session session = persistence.getSession(database);
        result = runHqlQuery(session, fullQueryString, parameters);
        if(result != null && !result.isEmpty()) {
            return result.get(0);
        } else {
            return null;
        }
    } catch (JSQLParserException e) {
        throw new Error(e);
    }
}