Java sql column names

JDBC ResultSet get columns with table alias

But this backfires on me and I get nothing. I read the API, but they don’t really talk about this case. Is such a feature vendor dependent?

8 Answers 8

JDBC will simply name the columns by what is specified in the query — it doesn’t know about table names etc.

Option 1: Name the columns differently in the query, ie

SELECT a.columnName as columnNameA, b.columnName as columnNameB, . from table1 a, table2 b where (WHATEVER) 

then in your java code refer to the column aliases:

resultSet.getString("columnNameA"); resultSet.getString("columnNameB"); 

Option 2: Refer to the column position in your call to the JDBC API:

resultSet.getString(1); resultSet.getString(2); 

Note that the JDBC API uses one-based indexes — ie they count from 1 (not from 0 like java indexes), so use 1 for the first column, 2 for the second column, etc

I would recommend option 1, because it’s safer to refer to named columns: Someone may change the order of the columns in the query and it would silently break your code (you would be accessing the wrong column but would not know), but if they change the columns names, you’ll at least get a «no such column» exception at runtime.

ResultSetMetadata.getColumnLabel() is what you need

(edit) sample example, as stated by bharal in comment

SELECT * from table1 a, table2 b where (WHATEVER) ResultSetMetaData rsmd = rset.getMetaData(); rsmd.getColumnLabel(1); 

With SQLite JDBC driver this gives me only column name, e.g. username , instead of full a.username . Is there a way to get a , table alias?

SELECT A.ID 'A_ID', B.ID 'B_ID' FROM TABLE1 AS A, TABLE2 AS B. 

And specify all the columns you are retrieving (is a good practice).

If you are using MySQL just add

&useOldAliasMetadataBehavior=true 

Afterwards you can use this little Helper:

import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.HashMap; import java.util.Map; public class ResultSetHelper < private final MapcolumnMap; public ResultSetHelper(ResultSet rs) throws SQLException < this.columnMap = new HashMap<>(); ResultSetMetaData md = rs.getMetaData(); int columnCount = md.getColumnCount(); for (int index = 1; index String tableAlias = md.getTableName(index); if (tableAlias != null && !tableAlias.trim().isEmpty()) < columnMap.put(tableAlias + "." + columnName, index); >> > public Integer getColumnIndex(String columnName) < return columnMap.get(columnName); >public Integer getColumnIndex(String tableAlias, String columnName) < return columnMap.get(tableAlias + "." + columnName); >> 

Ok, it seems there’s no method like resultSet.getString(«a.columnName»); and you have to alias your columns at sql level, but inasmuch as there’s a getTableName(iCol) method I hope guys at java.sql.ResultSet add such a feature.

You can use alias on SQL level. Then you retrieve data by indexes. (But this approach will make maintenance a real nightmare)

SELECT a.column, b.column FROM table1 a, table2 b String value = rs.getString(1); 

One idea I had is to use the getTableName(iCol) to grab the table names for the duplicately-named columns, then wrap a hash of your own keys (with the table name prefix) that would point you to the correct column index, and reference your column-values that way. This would require an initial loop through the meta data at the beginning to set up. The only issue I see with this is that you are aliasing the table names as well. I’ve yet to find a way of getting those table name aliases from jdbc without managing it yourself when you build the sql statement. This solution would depend on what the syntactical pay-off would be to you.

@Bohemian — you can use the ResultSetMetaData::getTableName(int) function to get the tablename. I’m not sure if the result depends on the jdbc implementation, though. docs.oracle.com/javase/7/docs/api/java/sql/…

SELECT a.*, b.* from table1 a, table2 b where (WHATEVER) 

than you can read from resulset by table alias

resultSet.getString("a.columnName"); resultSet.getString("b.columnName"); 

Источник

How to get all the column names from a ResultSet using JDBC

You can get the name of a particular column using the getColumnName() method of the ResultSetMetadata interface.

This method accepts an integer value representing the index of a column and returns a String value representing the name of the specified column.

Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below −

CREATE TABLE MyPlayers( ID INT, First_Name VARCHAR(255), Last_Name VARCHAR(255), Date_Of_Birth date, Place_Of_Birth VARCHAR(255), Country VARCHAR(255), PRIMARY KEY (ID) );

Now, we will insert 7 records in MyPlayers table using INSERT statements −

insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India'); insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica'); insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka'); insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India'); insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India'); insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India'); insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

Following JDBC program establishes connection with the database, retrieves the contents of the table named MyPlayers in to a ResultSet object, gets the column names of the table using the getColumnName() method and displays them.

Example

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class RS_AllColumnNames < public static void main(String args[]) throws Exception < //Registering the Driver DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //Getting the connection String mysqlUrl = "jdbc:mysql://localhost/mydatabase"; Connection con = DriverManager.getConnection(mysqlUrl, "root", "password"); System.out.println("Connection established. "); //Creating a Statement object Statement stmt = con.createStatement(); //Retrieving the data ResultSet rs = stmt.executeQuery("select * from MyPlayers"); //Retrieving the ResultSetMetadata object ResultSetMetaData rsMetaData = rs.getMetaData(); System.out.println("List of column names in the current table: "); //Retrieving the list of column names int count = rsMetaData.getColumnCount(); for(int i = 1; i> >

Output

Connection established. List of column names in the current table: ID First_Name Last_Name Date_Of_Birth Place_Of_Birth Country

Источник

Getting column names from query

consider a query like select a.apple,b.mango,c.orange from A a,B b,C c where . (Some conditions) here i need to fetch only the column names based on the query. apple mango orange. we have a query builder where end user creates/generates any type of query using that, my duty is to select only the column names from the query as above for my further operations. How can i achieve this, through java code or query? my db is sql server 2005.

5 Answers 5

to get the ResultSetMetaData java interface

PreparedStatement ps=con.prepareStatement("select * from your_table_name"); ResultSet rs=ps.executeQuery(); ResultSetMetaData rsmd=rs.getMetaData(); System.out.println("Total columns: "+rsmd.getColumnCount()); System.out.println("Column Name of 1st column: "+rsmd.getColumnName(1)); con.close(); 

In the case you need only to display the columns name that you already know you can simply put them(column names) directly into the SELECT list :

SELECT apple AS apple ,mango AS mango,orange AS orange 

otherwise you can query the information schema service table of SQL Server :

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=OBJECT_NAME(OBJECT_ID('a')) OR TABLE_NAME=OBJECT_NAME(OBJECT_ID('b')) OR TABLE_NAME=OBJECT_NAME(OBJECT_ID('c')) 

With Java and from the original query you can read the column names using ResultSetMetaData object :

ResultSetMetaData rsmd = rs.getMetaData(); String apple = rsmd.getColumnName(1); //Column apple String mango = rsmd.getColumnName(2); 

Источник

Getting Column Names from a database table in Java

Here we are providing you an example with code that retrieves all columns name in a specific database table. Sometimes, you need to know the number of columns and the names of the columns of the table, so you can retrieve it with the help of this example.

Getting Column Names from a database table in Java

Getting Column Names from a database table in Java

Here we are providing you an example with code that retrieves all columns name in a specific database table. Sometimes, you need to know the number of columns and the names of the columns of the table, so you can retrieve it with the help of this example. See detail information given below:

Description of program:

Create a class ColumnName. The name of the class should be such that the other person can easily understand what this program is going to do. Strictly follow the naming conventions of java. Now declare a static method inside which creates the connection with MySQL database through the JDBC driver. After establishing the connection then you get all columns name and number of columns of specified table with the help of some APIs and methods.

ResultSetMetaData:
This is an interface of java.sql package that can be used for getting information about types and properties of columns in a ResultSet object.

getColumnCount():
Above method retrieves number of columns (integer types data) in the ResultSet object.

getColumnName(int column):
This method returns columns name (string type data) from ResultSetMetaData object and takes integer type value.

Here is the code of program:

import java.sql.*; public class ColumnName < public static void main ( String [] args ) < System.out.println ( «Getting Column Names Example!» ) ; Connection con = null ; String url = «jdbc:mysql://localhost:3306/» ; String db = «jdbctutorial» ; String driver = «com.mysql.jdbc.Driver» ; String user = «root» ; String pass = «root» ; try < Class.forName ( driver ) ; con = DriverManager.getConnection ( url+db, user, pass ) ; try < Statement st = con.createStatement () ; ResultSet rs = st.executeQuery ( «SELECT * FROM employee6» ) ; ResultSetMetaData md = rs.getMetaData () ; int col = md.getColumnCount () ; System.out.println ( «Number of Column : » + col ) ; System.out.println ( «Columns Name: » ) ; for ( int i = 1 ; i String col_name = md.getColumnName ( i ) ; System.out.println ( col_name ) ; > > catch ( SQLException s ) < System.out.println ( «SQL statement is not executed!» ) ; > > catch ( Exception e ) < e.printStackTrace () ; > > >

Tutorials

1. Mapping MySQL Data Types in Java
2. Connecting to a MySQL Database in Java
3. Creating a Database in MySQL
4. Creating a Database Table
5. Creating a MySQL Database Table to store Java Types
6. Description of Database Table
7. Deleting a Table from Database
8. Retrieving Tables from a Database
9. Inserting values in MySQL database table
10. Retrieving All Rows from a Database Table
11. Adding a New Column Name in Database Table
12. Change Column Name of a Table
13. Make Unique Column in Database Table
14. Remove Unique Column in Database Table
15. Arrange a Column of Database Table
16. Arrange a Column of Database Table
17. Sum of Column in a Database Table
18. Deleting All Rows from a Database Table
19. Delete a Specific Row from a Database Table
20. Delete a Column from a Database Table
21. Join tables in the specific database
22. Join tables with the NATURAL LEFT JOIN operation
23. Join tables with the NATURAL RIGHT JOIN operation
24. Cross Join Tables in a Specific Database
25. Prepared Statement Set Object
26. Statement Batch Update
27. Prepared Statement With Batch Update
28. Select Records Using Prepared Statement
29. Update Records using Prepared Statement
30. Inserting Records using the Prepared Statement
31. Count Records using the Prepared Statement
32. Deleting Records using the Prepared Statement
33. Using the Prepared Statement Twice
34. Set Data Types by using Prepared Statement
35. Set byte, short and long data types by using the Prepared Statement
36. Prepared Statement Set Big Decimal
37. Set Date by using the Prepared Statement
38. Set Time by using the Prepared Statement
39. Set Timestamp by using the Prepared Statement
40. Copy Table in a MySQL Database

Источник