Sql parsing in java

java-How to parse sql columns with JDBC or jSqlParser ?

This post would demo how to parse sql column names with JDBC or jSqlParser.

2. Environments

3. The code

3.1 The database table environment

Assume that we have a table named tbl_city, which has two column names like this:

CREATE TABLE tbl_city ( id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT, name VARCHAR(255) );

3.2 Parse column names with JDBC

The most easiest way is to execute the sql and get the columns from the ResultSetMetaData.

public static void main(String[] args)  System.out.println("Mysql JDBC Parse Column Names Testing ~"); try  Class.forName("com.mysql.jdbc.Driver"); > catch (ClassNotFoundException e)  System.err.println("Unable to find the mysql JDBC Driver!"); e.printStackTrace(); return; > // default database: mysql // JDK 1.7+, auto close connection with try-with-resources, you do not need to use try-finally to close the resource. try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456"))  //would close the Connection automatically String sql = "select * from tbl_city"; // the sql to parse java.sql.Statement statement = connection.prepareStatement(sql); try (ResultSet rs = statement.executeQuery(sql)) //would close the ResultSet automatically ResultSetMetaData rsMeta = rs.getMetaData(); int columnCount = rsMeta.getColumnCount(); for (int i = 1; i  columnCount; i++)  String columnName = rsMeta.getColumnName(i); System.out.format("%s\n", columnName); > > > catch (SQLException e)  System.err.println("Something went wrong!"); e.printStackTrace(); return; > >

Execute the code and we get this result:

3.2 Parse with jSqlParser library

But what if we don’t want to execute the sql , or we can’t execute the sql, how to parse sql column names without execute it? You can try the jSqlParser library.

3.2.1 Add the dependency

  com.github.jsqlparser jsqlparser 1.1 

3.2.2 The Utility Method

public static ListString> getColumns(String sql) throws JSQLParserException  CCJSqlParserManager parserRealSql = new CCJSqlParserManager(); Statement stmt = parserRealSql.parse(new StringReader(sql)); // create a jSqlParser Statement from the sql ListString> list=new ArrayListString>(); // contains the columns result if (stmt instanceof Select)  // only parse select sql Select selectStatement = (Select) stmt; //convert to Select Statement PlainSelect ps = (PlainSelect)selectStatement.getSelectBody(); ListSelectItem> selectitems = ps.getSelectItems(); selectitems.stream().forEach(selectItem -> list.add(selectItem.toString()));//add the selected items to result > return list; >

3.2.3 Now we test it

 public static void main(String[] args) throws JSQLParserException  String sql = "select a,b from tbl_city"; getColumns(sql).stream().forEach(c-> System.out.println(c)); // get a b System.out.println("----"); String sql2 = "select a from (select b from table1 left join table2 on table2.c=table1.d)"; // get a getColumns(sql2).stream().forEach(c-> System.out.println(c)); System.out.println("----"); String sql3 = "select * from tbl_city"; getColumns(sql).stream().forEach(c-> System.out.println(c)); // get * >

As you can see, the jSqlParser can parse the sql columns without executing it, but if you do not specify the column names in your sql, for example, select * from test, it can not get the right column names. So ,use it with caution.

4. Conclusion

You can parse the sql columns with JDBC, if you choose this way, you must execute the sql on the database, then you would get the real column names. If you can’t or don’t want to execute the sql ,you can try the jSqlParser library. But use it with caution.

Источник

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

JSqlParser parses an SQL statement and translate it into a hierarchy of Java classes. The generated hierarchy can be navigated using the Visitor Pattern

License

Apache-2.0, LGPL-2.1 licenses found

Licenses found

JSQLParser/JSqlParser

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

Please visit the WebSite. JSqlParser is a RDBMS agnostic SQL statement parser. It translates SQL statements into a traversable hierarchy of Java classes (see Samples):

SELECT 1 FROM dual WHERE a = b

SQL Text └─Statements: statement.select.PlainSelect ├─selectItems: statement.select.SelectItem │ └─LongValue: 1 ├─Table: dual └─where: expression.operators.relational.EqualsTo ├─Column: a └─Column: b 

String sqlStr = "select 1 from dual where a=b"; PlainSelect select = (PlainSelect) CCJSqlParserUtil.parse(sqlStr); SelectItem selectItem = select.getSelectItems().get(0); Assertions.assertEquals( new LongValue(1) , selectItem.getExpression()); Table table = (Table) select.getFromItem(); Assertions.assertEquals("dual", table.getName()); EqualsTo equalsTo = (EqualsTo) select.getWhere(); Column a = (Column) equalsTo.getLeftExpression(); Column b = (Column) equalsTo.getRightExpression(); Assertions.assertEquals("a", a.getColumnName()); Assertions.assertEquals("b", b.getColumnName()); >

JSqlParser aims to support the SQL standard as well as all major RDBMS. Any missing syntax or features can be added on demand.

RDBMS	Statements
Oracle MS SQL Server and Sybase PostgreSQL MySQL and MariaDB DB2 H2 and HSQLDB and Derby SQLite	SELECT INSERT , UPDATE , UPSERT , MERGE DELETE , TRUNCATE TABLE CREATE . , ALTER . , DROP . WITH .

JSqlParser can also be used to create SQL Statements from Java Code with a fluent API (see Samples).

Alternatives to JSqlParser?

General SQL Parser looks pretty good, with extended SQL syntax (like PL/SQL and T-SQL) and java + .NET APIs. The tool is commercial (license available online), with a free download option.

Источник

Parsing SQL CREATE query using JSQLParser

There are multiple use-cases for parsing a Create Statement. Suppose if you want to validate the table schema between the source and target system and make sure you are using the same table and column names, you can very well parse the Create statements of both the systems and generate a report or send an alert email with the observations. For Parsing SQLs in java using the JSQLParser library, we can use the below maven dependency in the project.

JSQLParser maven dependency

You need to create a new maven project and add the following dependency in your pom.xml file to access this parsing library

 com.github.jsqlparser jsqlparser 1.4 

The following JSQLParser example program will help you to understand how we can parse the CREATE SQL statement.

Parsing SQL Create query in Java

import net.sf.jsqlparser.JSQLParserException; import net.sf.jsqlparser.expression.Expression; import net.sf.jsqlparser.expression.operators.conditional.AndExpression; import net.sf.jsqlparser.parser.CCJSqlParserUtil; import net.sf.jsqlparser.schema.Column; import net.sf.jsqlparser.schema.Table; import net.sf.jsqlparser.statement.Statement; import net.sf.jsqlparser.statement.create.table.ColDataType; import net.sf.jsqlparser.statement.create.table.ColumnDefinition; import net.sf.jsqlparser.statement.create.table.CreateTable; import net.sf.jsqlparser.statement.insert.Insert; import net.sf.jsqlparser.statement.select.*; import net.sf.jsqlparser.util.SelectUtils; import java.util.ArrayList; import java.util.List; public class CreateSqlParser < public static void main(String[] args) < System.out.println("Program to parse CREATE sql statement"); System.out.println("-------------------------------------"); String createSQL = "CREATE Table User.TableName (" + "id INTEGER NOT NULL," + "name varchar(100) NOT NULL," + "CRE_TS TIMESTAMP(0) NOT NULL," + "UPD_TS TIMESTAMP(0) NOT NULL" + ")" ; try < Statement createTable = CCJSqlParserUtil.parse(createSQL); System.out.println("Table Name from query: " + ((CreateTable) createTable).getTable().getName()); System.out.println("Database Name from query: " + ((CreateTable) createTable).getTable().getSchemaName()); String sqlTableName = ((CreateTable) createTable).getTable().getName(); System.out.println("Columns in the given insert query"); System.out.println("---------------------------------"); for(ColumnDefinition col: ((CreateTable) createTable).getColumnDefinitions()) < System.out.println(col.getColumnName() + " - " + col.getColDataType().toString()); >//Modifying the tablename and DB ((CreateTable) createTable).getTable().setName("NewTable"); ((CreateTable) createTable).getTable().setSchemaName("NewDatabase"); //Adding a new column to the create statement ColumnDefinition newCol = new ColumnDefinition(); newCol.setColumnName("processDate"); ColDataType colDataType = new ColDataType(); colDataType.setDataType("DATE"); newCol.setColDataType(colDataType); List colSpecStrings = new ArrayList<>(); colSpecStrings.add("FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE '1970-01-01'"); newCol.setColumnSpecStrings(colSpecStrings); ((CreateTable) createTable).getColumnDefinitions().add(newCol); String newSql = createTable.toString(); System.out.println("\nNew Create statement:"); System.out.println("-----------------------"); System.out.println(newSql); System.out.println("New Columns"); System.out.println("-----------"); for(ColumnDefinition col: ((CreateTable) createTable).getColumnDefinitions()) < System.out.println(col.getColumnName() + " - " + col.getColDataType().toString()); >> catch (JSQLParserException e) < e.printStackTrace(); >> >

Expected output

The output of the above JSQLParser example program is given below. You can see that we are able to extract the database name, table name, list of columns and the data types of each of the columns as well.

Program to parse CREATE sql statement ------------------------------------- Table Name from query: TableName Database Name from query: User Columns in the given insert query --------------------------------- id - INTEGER name - varchar (100) CRE_TS - TIMESTAMP (0) UPD_TS - TIMESTAMP (0) New Create statement: ----------------------- CREATE TABLE NewDatabase.NewTable (id INTEGER NOT NULL, name varchar (100) NOT NULL, CRE_TS TIMESTAMP (0) NOT NULL, UPD_TS TIMESTAMP (0) NOT NULL, processDate DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE '1970-01-01') New Columns ----------- id - INTEGER name - varchar (100) CRE_TS - TIMESTAMP (0) UPD_TS - TIMESTAMP (0) processDate - DATE

Источник