Java initialization database read SQL configuration file build table

Create a Init.sql file in the SQL folder under Src/main/resources, and put the build table statement:

CREATE TABLE {DB}. ' Agent_status ' (
' Device_num ' varchar (+) not NULL,
' Start_time ' timestamp not NULL the DEFAULT current_timestamp on UPDATE current_timestamp,
' STATUS ' int (one) is not NULL,
' DURATION ' int (one) DEFAULT NULL,
' End_time ' timestamp null DEFAULT NULL,
' Call_type ' int (one) DEFAULT NULL,
' session_id ' varchar (255) DEFAULT NULL,
' SOURCE ' varchar (255) DEFAULT NULL,
' custom_num ' varchar DEFAULT NULL,
' Media_type ' int (one) DEFAULT NULL
) Engine=innodb DEFAULT Charset=utf8 checksum=1 delay_key_write=1 row_format=dynamic;

CREATE TABLE {DB}. ' Associate_data ' (
' SESSIONID ' varchar (255) is not NULL,
' Device_num ' varchar (255) DEFAULT NULL,
' Agent_name ' varchar (255) DEFAULT NULL,
' skill_group_id ' int (one) DEFAULT NULL,
' Skill_group_name ' varchar (255) DEFAULT NULL,
' Time ' timestamp not NULL for DEFAULT current_timestamp on UPDATE current_timestamp,
' SOURCE ' varchar (255) DEFAULT NULL,
' Call_type ' int (one) DEFAULT NULL,
' agent_id ' varchar DEFAULT NULL,
PRIMARY KEY (' SESSIONID ')
) Engine=innodb DEFAULT Charset=utf8 checksum=1 delay_key_write=1 row_format=dynamic;

Read the. SQL Tool class Reportformsqlutil, with the following code:

Package com.channelsoft.col.colweb.util;

Import Java.io.InputStream; Import java.util.ArrayList; Import java.util.List;

Import Org.slf4j.Logger; Import Org.slf4j.LoggerFactory;

public class Reportformsqlutil {

private static Logger log = Loggerfactory.getlogger (Configutil.class);

public static list<string> Loadsql () throws Exception {

InputStream Sqlfilein = null; list<string> sqllist = new arraylist<string> ();

try {

Sqlfilein = ReportFormSqlUtil.class.getResourceAsStream ("/sql/reportform.sql"); Configuration file path

StringBuffer sqlsb = new StringBuffer ();

byte[] buff = new byte[1024];

int byteread = 0;

while ((Byteread = Sqlfilein.read (buff))! =-1) {

Sqlsb.append (New String (buff, 0, Byteread, "Utf-8"));

}

Sqlfilein.close ();

Windows line break is \ r \ n, Linux under

string[] Sqlarr = sqlsb.tostring (). Split ("(; \\s*\\r\\n) | (; \ \s*\\n) ");

for (int i = 0; i < sqlarr.length; i++) {

String sql = Sqlarr[i].replaceall ("--. *", ""). Trim ();

if (!sql.equals ("")) {sqllist.add (SQL); }

}

return sqllist;

} catch (Exception ex) {

Log.error ("Read report SQL build table file [Reportform.sql] failed.", ex);

throw new Exception (Ex.getmessage ());

}

}

}

Initialize Database Build table:

Set up a reporting database for users
list<string> listsql = Reportformsqlutil.loadsql ();//read. SQL Build Table statement
String crsql = "CREATE database Col_user_" + user.getid () + "DEFAULT CHARACTER SET UTF8";//Build Library statement
try {
Build Library
Jdbctemplate.update (Crsql);
for (int i = 0;i < Listsql.size (); i++) {
Build a Table statement
String SQLL = Listsql.get (i). Replacefirst ("\\{db\\}", "Col_user_" +user.getid ());
System.out.println (SQLL);
Jdbctemplate.update (SQLL);
}
} catch (Exception e) {
Logger.error ("Initialize User Report database error", E);
Throw e;
}

Java initialization database read SQL configuration file build table