package com.pritom.kumar;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
/**
* Created by pritom on 18/10/2016.
*/
public class StoredProcedureUtils {
static Connection connection = null;
public static void main(String[] args) throws Exception {
alterTableSchema("some_db_name");
}
static void alterTableSchema(String databaseName) throws Exception {
/* Query for retrieve table names from database */
String query = "SELECT table_name FROM information_schema.tables " +
"WHERE table_schema='" + databaseName + "' ORDER BY table_name;";
/* Create statement */
Statement statement = getConnection().createStatement();
/* Retrieving all table names */
ResultSet resultSet = statement.executeQuery(query);
List<String> tableList = new ArrayList<String>();
while (resultSet.next()) {
tableList.add(resultSet.getString(1));
}
/* Set database not to check foreign key constraints */
statement.execute("SET FOREIGN_KEY_CHECKS=0;");
/* Alter table structure */
for (Integer index = 0; index < tableList.size(); index++) {
String tableName = tableList.get(index);
try {
statement.execute("ALTER TABLE " + databaseName + "." + tableName + " MODIFY id BIGINT(20) AUTO_INCREMENT;");
pl(formatAsLength("Done Alter Table=" + tableName, 70, "") + (index + 1) + " Of " + tableList.size());
}
catch (Exception ex) {
pl(formatAsLength("Error Alter Table=" + tableName, 70, "") + "Error=" + ex.getMessage());
}
}
/* Set database mode to check foreign key constraints again */
statement.execute("SET FOREIGN_KEY_CHECKS=1;");
/* Close statement */
statement.close();
}
static Connection getConnection() throws Exception {
if (connection != null) {
return connection;
}
connection = DriverManager.getConnection("jdbc:mysql://localhost/some_db_name?user=root&password=");
return connection;
}
static void pl(Object o) {
System.out.println("" + o);
}
static String formatAsLength(String number, Integer minLength, String replaceEmptyWith) {
number = String.format("%-" + minLength + "s", number);
return replaceEmptyWith.length() > 0 ? number.replace(" ", replaceEmptyWith) : number;
}
}
Monday, November 7, 2016
Alter all table of a mysql database using java
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment