需求:别的项目, 代码扣过来了, 数据库也拿过来了, 但是数据库全是空表, 一共700 张表,需求是给表添加假数据,让它能运行起来。
一下是代码实现:
1.数据库连接:
public staticConnection getConnection(){
Connection conn= null;try{
Class.forName("com.mysql.jdbc.Driver");
String url= "jdbc:mysql://192.168.0.12:3306/dmp_report_testdb?";
String user= "root";
String pass= "ycmedia_2015";
conn=DriverManager.getConnection(url,user,pass);
}catch(ClassNotFoundException e) {
e.printStackTrace();
}catch(SQLException e) {
e.printStackTrace();
}returnconn;
}
2.获取所有的表名:
public static Set getTableName() throwsSQLException{
Connection conn=getConnection();
DatabaseMetaData data=conn.getMetaData();
ResultSet colRet= data.getColumns(null,"%", "%","%");
Set set= new TreeSet();while(colRet.next()) {
String typeName=colRet.getString("TABLE_NAME");
set.add(typeName);
}returnset;
}
3.获取表的数据类型,mysql有几十种,常用大概十几种:
* @return
* @throwsSQLException*/
public static Set getType() throwsSQLException{
Connection conn=getConnection();
DatabaseMetaData data=conn.getMetaData();
ResultSet colRet= data.getColumns(null,"%", "%","%");
Set set= new TreeSet();while(colRet.next()) {
String typeName=colRet.getString("TYPE_NAME");
set.add(typeName);
}returnset;
}
4 分类:
/*** BIGINT
BIGINT UNSIGNED(没有符号) 1-10
INT
INT UNSIGNED
SMALLINT
SMALLINT UNSIGNED
TINYINT
TINYINT UNSIGNED
BIT 10
DATE 最近一周
DATETIME
TIMESTAMP (最近一周)
DECIMAL 11.11
DOUBLE 11.11
LONGTEXT
MEDIUMTEXT
TEXT
VARCHAR
5 字符串类型处理
//随机字符串
public static String getRandomString(intlength){
String str="abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
Random random=newRandom();
StringBuffer sb=newStringBuffer();for(int i=0;i
sb.append(str.charAt(number));
}returnsb.toString();
}
6.时间类型处理
/*** 获取最近一个月随机一天
*@return
*/
public staticString getMoth(Integer type){
Random r= newRandom();
SimpleDateFormat sdf= null;if(type==1){
sdf=new SimpleDateFormat("yyyy-MM-dd");
}else{
sdf=new SimpleDateFormat("yyyy-MM-dd 00:00:00");
}
Calendar c=Calendar.getInstance();
c.add(Calendar.DATE,- r.nextInt(30));
Date monday=c.getTime();returnsdf.format(monday);
}
7.获取sql
/*** 获取sql
*@paramlist
*@paramtableName
*@return
*/
public static String getSql(Listlist,String tableName){
StringBuffer sb= newStringBuffer();
Random random= newRandom();
sb.append("insert into "+tableName +" values (");for (int i = 0; i < list.size(); i++) {if(list.get(i).getColumnType().contains("INT")){
sb.append(random.nextInt(10)+", ");
}else if(list.get(i).getColumnType().contains("CHAR")||list.get(i).getColumnType().contains("TEXT"))
{
sb.append("'"+getRandomString(5)+"', ");
}else if(list.get(i).getColumnType().equals("BIT"))
{
sb.append("10, ");
}else if(list.get(i).getColumnType().equals("DOUBLE")||list.get(i).getColumnType().equals("DECIMAL"))
{
sb.append("11.11, ");
}else if(list.get(i).getColumnType().equals("DATETIME")||list.get(i).getColumnType().equals("TIMESTAMP"))
{
sb.append("'"+getMoth(2)+"', ");
}else if(list.get(i).getColumnType().equals("DATE")){
sb.append("'"+getMoth(1)+"', ");
}
}return sb.toString().substring(0, sb.toString().length()-2)+")";
}
8 .最后一步,主分支
public static void main(String[] args) throwsException{
Connection conn=getConnection();
Set set =getTableName();
List list =new ArrayList();for(String str :set) {
list=getColumnList(str);for (int i = 0; i < 20; i++) {
String sql=getSql(list,str);
System.err.println(sql);try{
conn.prepareStatement(sql).execute();
}catch(Exception e) {
System.err.println("遇到异常");continue;
}
}
list=new ArrayList();
}
}
=====================================================分割线=====================================================================
当然这种场景很少见吧, 大部分项目都不超过100 张表, 几十张最多了,实际开发中,而且这里的数据都是比较乱 的, 因为很多表都有关联, 只是大致插表, 当跑不通的部分就可以人为 修改那块了,比自己慢慢插数据好点