CODEWITHSUNDEEP
javamysqlprimary-key
Jojodmo
Jojodmo

Reputation: 23596

Get all primary keys in a MySQL table using Java

How could I get the names of all primary keys in a MySQL table using Java? Let's say I have a table:

Name │ Phone Number │ verified
─────┼──────────────┼─────────
John │ 871-123-1842 │    0
Mark │ 912-917-8172 │    1
Matt │ 182-134-9917 │    1
Jake │ 971-991-8264 │    1

I would like to get the values John, Mark, Matt, and Jake from the table.

I know there could be an alternative of just having a list of all of all of the primary keys, and adding to it whenever a new one is added, just I'm looking for something simpler, that will take up less space. I'm looking for something like:

PreparedStatement query = mySqlConnection.prepareStatement("SELECT * FROM `table`");
ResultSet results = query.executeQuery();
List<Object> results = results.getPrimaryKeyValues(); //I don't know what to put here

So, how could I achieve getting an Array, or something that I can turn into an Array, of all of the primary keys inside of a MySQL table using Java?

Upvotes: 0

Views: 2809

Answers (3)

user6044627
user6044627

Reputation: 100

 DatabaseMetaData databaseMetaData = conn.getMetaData();  

 ResultSet resultSet = databaseMetaData.getPrimaryKeys(null, null ,  tablename);          

while (resultSet.next()) {
                String primarykeys= resultSet.getString(4);
                System.out.println(primarykeys);
            }

Upvotes: 0

rion18
rion18

Reputation: 1221

First off, I suppose you don't know what columns are Primary Keys on your table. The first step would be to get these column names, and then get the values.

To get the column names:

public ArrayList<String> findPrimaryKeyColumnNames(Connection connection, 
                 String catalog, String schema, String table){ 
    /* open the connection*/
    ...

    ArrayList<String> columns = new ArrayList<String>();
    DatabaseMetaData dbData = connection.getMetaData();
    Resultset result = dbData.getPrimaryKeys(catalog, schema, table);
    while (result.next())
        columns.add(result.getString("COLUMN_NAME"));

    ... 
    /* you should... PROBABLY... close your connection ;) */

    return columns;
}

What this method does is it returns an ArrayList containing the names of the columns that are Foreign Keys from the table you put in your parameter.

With this new info, you can easily make a dynamic query that goes like:

String query = "SELECT ";
for(String column : columns) //columns contains the info from the first method
    query += column + ", ";
query = query.substring(0, query.length - 2); //you want to get rid of that extra comma
query += "FROM " + tableName;

You can then recover the data from this query and it will only contain your primary keys for a given table.

Upvotes: 1

camickr
camickr

Reputation: 324108

There is no method like getPrimaryKeyValues().

You need to iterate through the ResultSet to create your own list of values. Something like:

String sql = "Select Name from yourTableNameHere";

Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery( sql );
List<String> names = new ArrayList<String>();

while (rs.next())
{
    names.add( rs.getString(1) );
}

rs.close();
stmt.close();

System.out.println( names );

Upvotes: 0

Related Questions