上代码:效果就是:testDb数据库里面多有表(如果有guid字段)的guid字段都变为varchar(500)。若有需要,可以继续修改封装。
import java.sql.*;
public class TestMysqlJDBC {
public static void main(String[] args) {
//修改 数据库dbName里面 所有 字段fieldNameToUpdate 的长度为500
String dbName = "testDb";//要操作的数据库名
String fieldNameToUpdate = "guid";//要修改的字段名
String url = "jdbc:mysql://192.168.1.64:3306/testDb";
String userName = "user1";
String password = "1234";
Connection con = null;
try {
// 加载驱动器类
Class.forName("com.mysql.jdbc.Driver");
} catch (Exception e) {
e.printStackTrace();
}
try {
con = DriverManager.getConnection(url, userName, password);
} catch (Exception e) {
e.printStackTrace();
}
// 接着就可以操作MySql数据库了
try {
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("show tables");
//返回查询的结果
while(rs.next())
{
String tableName = rs.getString(1);
String sql_tabColumns = "SHOW COLUMNS FROM "+tableName+" FROM "+dbName;
PreparedStatement preStatement = con.prepareStatement(sql_tabColumns);
ResultSet rs2 = preStatement.executeQuery();
while(rs2.next())
{
String fieldName = rs2.getString(1);
if(fieldNameToUpdate.equals(fieldName)){
//相应的表有 对应的字段 则修改
updateTableField(con, tableName, fieldName);
System.out.println("修改表"+tableName+"字段"+fieldName);
}
}
preStatement.close();
System.out.println(tableName);
}
rs.close();
stmt.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private static void updateTableField(Connection con, String tableName,
String fieldName) throws SQLException {
String sql = "alter table "+tableName+" modify column "+fieldName+" varchar(500)";
PreparedStatement preStatement = con.prepareStatement(sql);
preStatement.executeUpdate();
preStatement.close();
}
}