删除程序之一，用于删除id=1的一条记录，在删除后停住断点，另一个删除程序会进行不下去。
package com.hy.multidelete;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.log4j.Logger;
public class Deleter {
private static Logger log = Logger.getLogger(Deleter.class);
public void doDelete() {
Connection conn = null;
Statement stmt = null;
try{
Class.forName(DBParam.Driver).newInstance();
conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd);
conn.setAutoCommit(false);
stmt = conn.createStatement();
String sql="delete from TestTB17 where id=1";
int deleted=stmt.executeUpdate(sql);
// 在此处停住断点，另一个删除程序CleanExpiredMocker会执行不下去
log.info("Deleter deleted "+deleted+" records.");
// 直到接下来回滚或提交CleanExpiredMocker才可以执行
conn.rollback();
log.info("Rollbacked.");
} catch (Exception e) {
System.out.print(e.getMessage());
} finally {
try {
stmt.close();
conn.close();
} catch (SQLException e) {
System.out.print("Can't close stmt/conn because of " + e.getMessage());
}
}
}
public static void main(String[] args) {
Deleter d=new Deleter();
d.doDelete();
}
}
另一删除程序，用于删除整表全部记录记录，此删除与上一程序删除id=1的操作存在交集，因此如果deleter先执行，要等deleter提交或回滚后，此程序才能执行下去：
package com.hy.multidelete;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.log4j.Logger;
public class CleanExpiredMocker {
private static Logger log = Logger.getLogger(CleanExpiredMocker.class);
public void doClean() {
Connection conn = null;
Statement stmt = null;
try{
Class.forName(DBParam.Driver).newInstance();
conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd);
stmt = conn.createStatement();
String sql="delete from TestTB17 where 1=1";
int deleted=stmt.executeUpdate(sql);
log.info("CleanExpiredMocker deleted "+deleted+" records.");;
} catch (Exception e) {
System.out.print(e.getMessage());
} finally {
try {
stmt.close();
conn.close();
} catch (SQLException e) {
System.out.print("Can't close stmt/conn because of " + e.getMessage());
}
}
}
public static void main(String[] args) {
CleanExpiredMocker c=new CleanExpiredMocker();
c.doClean();
}
}
Select ...for update 也有同样阻断其它session(操作记录与select...for update语句的结果集存在交集)的效果:
package tablelock;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.log4j.Logger;
public class Selecter {
private static Logger log = Logger.getLogger(Selecter.class);
public void doDelete() {
Connection conn = null;
Statement stmt = null;
try{
Class.forName(DBParam.Driver).newInstance();
conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd);
conn.setAutoCommit(false);
stmt = conn.createStatement();
String sql="select * from TestTB17 for update";
stmt.executeUpdate(sql);
log.info("Will block other session before commit/rollback.");
conn.rollback();
log.info("Rollbacked.");
} catch (Exception e) {
System.out.print(e.getMessage());
} finally {
try {
stmt.close();
conn.close();
} catch (SQLException e) {
System.out.print("Can't close stmt/conn because of " + e.getMessage());
}
}
}
public static void main(String[] args) {
Selecter d=new Selecter();
d.doDelete();
}
}
往下是一些数据准备工作，可以一笑而过。
建表程序：
package com.hy.multidelete;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.log4j.Logger;
/**
* 此类用于管理表的创建和销毁
* @author 逆火
*
* 2019年11月16日 下午5:40:22
*/
public class TableHandler {
private static Logger log = Logger.getLogger(TableHandler.class);
private String[] tablenames= { "TestTB17",
};
/**
* Get the create table ddl of a table
* @param table
* @return
*/
private String getCreateTbSql(String table) {
StringBuilder sb=new StringBuilder();
sb.append("CREATE TABLE "+table);
sb.append("(");
sb.append("\"ID\" NUMBER(8,0) not null primary key,");
sb.append("\"NAME\" NVARCHAR2(60) not null,");
sb.append("\"AGE\" NUMBER(3,0) DEFAULT 0 not null ,");
sb.append("\"CREATEDTIME\" TIMESTAMP (6) not null");
sb.append(")");
return sb.toString();
}
/**
* Judge if a table is exist
* @param table
* @param stmt
* @return
* @throws SQLException
*/
private boolean isTableExist(String table,Statement stmt) throws SQLException {
String sql="SELECT COUNT (*) as cnt FROM ALL_TABLES WHERE table_name = UPPER('"+table+"')";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
int count = rs.getInt("cnt");
return count==1;
}
return false;
}
/**
* Crate tables
*/
public void createTables() {
Connection conn = null;
Statement stmt = null;
try{
Class.forName(DBParam.Driver).newInstance();
conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd);
stmt = conn.createStatement();
int i=0;
for(String table:tablenames) {
i++;
String sql=getCreateTbSql(table);
stmt.executeUpdate(sql);
if(isTableExist(table,stmt)) {
log.info("#"+i+" "+table+" created.");
}
}
} catch (Exception e) {
System.out.print(e.getMessage());
} finally {
try {
stmt.close();
conn.close();
} catch (SQLException e) {
System.out.print("Can't close stmt/conn because of " + e.getMessage());
}
}
}
/**
* Remove all the tables
*/
public void dropTables() {
Connection conn = null;
Statement stmt = null;
try{
Class.forName(DBParam.Driver).newInstance();
conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd);
stmt = conn.createStatement();
int i=0;
for(String table:tablenames) {
i++;
String sql="drop table "+table;
stmt.executeUpdate(sql);
if(isTableExist(table,stmt)==false) {
log.info("#"+i+" "+table+" dropped.");
}
}
} catch (Exception e) {
System.out.print(e.getMessage());
} finally {
try {
stmt.close();
conn.close();
} catch (SQLException e) {
System.out.print("Can't close stmt/conn because of " + e.getMessage());
}
}
}
/**
* Kick start
* @param args
*/
public static void main(String[] args) {
TableHandler th=new TableHandler();
th.createTables();
}
}
给表插入五百记录的程序：
package com.hy.multidelete;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import org.apache.log4j.Logger;
import com.hy.DBParam;
class TbCnt{
String tb;// tablename
int cnt;// count should be inserted
public TbCnt(String tb,int cnt) {
this.tb=tb;
this.cnt=cnt;
}
}
class Field{
String name; // 列名
int size; // 列容量
String type; // 列数据类型
boolean allowNull;// 列是否允許为空
}
/**
* 输入表名和需要插入的记录数，自动将所需记录插入表，不需要指定主键和必填字段
* @author 逆火
*
* 2019年11月23日 下午2:19:02
*/
public class TableAutoInserter {
private static Logger log = Logger.getLogger(TableAutoInserter.class);
private final int BatchSize=250;// 一次性插入记录数
private List<TbCnt> tbCntList;// 容纳表名和插入数量的链表
/**
* 构造函数
*/
public TableAutoInserter() {
tbCntList=new ArrayList<TbCnt>();
}
/**
* 加入一张要插入的表
* @param tc
* @return
*/
public TableAutoInserter add(TbCnt tc) {
tbCntList.add(tc);
return this;
}
/**
* 给已有的所有表插值
*/
public void insertAllTables() {
Connection conn = null;
Statement stmt = null;
try{
Class.forName(DBParam.Driver).newInstance();
conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd);
stmt = conn.createStatement();
log.info("Begin to access "+DBParam.DbUrl+" as "+DBParam.User+"...");
int index=0;
for(TbCnt tc:tbCntList) {
String table=tc.tb;
int count=tc.cnt;
long startTime = System.currentTimeMillis();
truncateTable(table,conn,stmt);
List<TypeField> fieldList=fetchTableFields(conn,stmt,table,false);
insertDataToTable(index,table,count,fieldList,conn,stmt);
if(isAllInserted(count,table,stmt)) {
long endTime = System.currentTimeMillis();
log.info("#"+index+" "+count+" records were inserted to table:'" + table + "' used " + sec2DHMS(startTime,endTime) );
}
index++;
}
} catch (Exception e) {
System.out.print(e.getMessage());
e.printStackTrace();
} finally {
try {
stmt.close();
conn.close();
} catch (SQLException e) {
log.error("Can't close stmt/conn because of " + e.getMessage());
}
}
}
/**
* 从表中取字段信息
* @param conn
* @param stmt
* @param table
* @param isFetchall:此参数为真时取所有字段，为假时取非空字段
* @return
* @throws Exception
*/
private List<TypeField> fetchTableFields(Connection conn,Statement stmt,String table,boolean isFetchall) throws SQLException{
List<TypeField> fields=new ArrayList<TypeField>();
String sql = "select * from "+table+"";
ResultSet rs = stmt.executeQuery(sql);
ResultSetMetaData rsMetadata = rs.getMetaData();
int count = rsMetadata.getColumnCount();
for (int i=0; i<count; i++) {
int index=i+1;
String columnName = rsMetadata.getColumnLabel(index);// 列名
String columnType = rsMetadata.getColumnTypeName(index);// 列数据类型
boolean allowNull=(rsMetadata.isNullable(index)!=0);// 列是否允許为空
//int size =rsMetadata.getColumnDisplaySize(index);// 列容量 此值在数据超过列容量限制时能用到
TypeField tf=new TypeField();
tf.field=columnName;
tf.type=getShortType(columnName,columnType);
if(isFetchall==true) {
fields.add(tf);
}else {
if(allowNull==false) {
fields.add(tf);
}
}
}
rs.close();
return fields;
}
/**
* 得到列类型的短名称，此短名称在getInsertSql函数中会用到
* @param columnType
* @return
* @throws SQLException
*/
private String getShortType(String field,String columnType) throws SQLException{
if(field.equalsIgnoreCase("id")) {
return "PK";
}
if(columnType.equalsIgnoreCase("timestamp")) {
return "DT";
}else if(columnType.equalsIgnoreCase("number")) {
return "NB";
}else if(columnType.equalsIgnoreCase("nvarchar2")) {
return "CH";
}else {
throw new SQLException("Unknown columnType:'"+columnType+"'.");
}
}
/**
* delete all data in a table quickly
* @param tableName
* @param conn
* @param stmt
* @throws SQLException
*/
private void truncateTable(String tableName,Connection conn,Statement stmt) throws SQLException{
String sql="truncate table "+tableName;
stmt.execute(sql);
log.info("truncated table:"+tableName);
}
/**
* Insert date to a table
* @param tableIndex
* @param tableName
* @param count
* @param innerArr
* @param conn
* @param stmt
* @throws SQLException
*/
private void insertDataToTable(int tableIndex,String tableName,int count,List<TypeField> typefields,Connection conn,Statement stmt) throws SQLException{
List<String> fields=new ArrayList<String>();
List<String> values=new ArrayList<String>();
int index=0;
for(TypeField tf:typefields) {
fields.add(tf.field);
values.add("''{"+index+"}''");
index++;
}
int interval=2*365*24*60*60/count;// 涓ゅ勾鐨勭鏁伴櫎浠ユ�讳釜鏁板嵆涓洪棿闅�
index=0;
int times=count/BatchSize;
for(int i=0;i<times;i++) {
StringBuilder sb=new StringBuilder();
sb.append("INSERT ALL ");
for(int j=0;j<BatchSize;j++) {
index=i*BatchSize+j;
sb.append(getInsertSql(tableName,typefields,index,interval));
}
sb.append(" select * from dual");
String sql = sb.toString();
long startTime = System.currentTimeMillis();
stmt.executeUpdate(sql);
long endTime = System.currentTimeMillis();
log.info("#"+tableIndex+"-"+i+" "+BatchSize+" records inserted to '"+tableName+"' used " + sec2DHMS(startTime,endTime));
}
}
/**
* get insert sql
* @param tableName
* @param typefields
* @param index
* @return
*/
private String getInsertSql(String tableName,List<TypeField> typefields,int index,int interval) {
String currTime=getDatetimeBefore(index,interval);
StringBuilder sb=new StringBuilder();
sb.append(" INTO "+tableName+"(");
List<String> fields=new ArrayList<String>();
for(TypeField tf:typefields) {
fields.add(tf.field);
}
sb.append(String.join(",",fields));
sb.append(") values(");
List<String> values=new ArrayList<String>();
for(TypeField tf:typefields) {
if(tf.type.equals("PK")) {
values.add("'"+String.valueOf(index)+"'");
}else if(tf.type.equals("CH")) {
values.add("'0'");
}else if(tf.type.equals("NB")) {
values.add("'0'");
}else if(tf.type.equals("DT")) {
values.add("to_date('"+currTime+"','yyyy-MM-dd HH24:mi:ss')");
}
}
sb.append(String.join(",",values));
sb.append(")");
String insertSql=sb.toString();
return insertSql;
}
/**
* get datetime n seconds before
* @param n
* @param interval
* @return
*/
private static String getDatetimeBefore(int n,int interval) {
try {
Calendar now = Calendar.getInstance();
now.add(Calendar.SECOND,-n*interval);//鏃ユ湡鍑忓幓n*10绉�
Date newDate=now.getTime();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String retval = sdf.format(newDate);
return retval;
}
catch(Exception ex) {
ex.printStackTrace();
return null;
}
}
/**
* judge if all records are inserted
* @param count
* @param table
* @param stmt
* @return
* @throws SQLException
*/
private boolean isAllInserted(int count,String table,Statement stmt) throws SQLException {
String sql="SELECT COUNT (*) as cnt FROM "+table;
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
int cnt = rs.getInt("cnt");
return cnt==count;
}
return false;
}
/**
* change seconds to DayHourMinuteSecond format
* @param stratMs
* @param endMs
* @return
*/
private static String sec2DHMS(long stratMs,long endMs) {
String retval = null;
long secondCount=(endMs-stratMs)/1000;
long days = secondCount / (60 * 60 * 24);
long hours = (secondCount % (60 * 60 * 24)) / (60 * 60);
long minutes = (secondCount % (60 * 60)) / 60;
long seconds = secondCount % 60;
if (days > 0) {
retval = days + "d" + hours + "h" + minutes + "m" + seconds + "s";
} else if (hours > 0) {
retval = hours + "h" + minutes + "m" + seconds + "s";
} else if (minutes > 0) {
retval = minutes + "m" + seconds + "s";
} else {
retval = seconds + "s";
}
return retval;
}
protected static final class TypeField{
String type;
String field;
}
public static void main(String[] args) {
TableAutoInserter ti=new TableAutoInserter();
String[] tablenames= { "TestTB17",};
for(String table:tablenames) {
ti.add(new TbCnt(table,500));
}
ti.insertAllTables();
}
}
Oracle数据库的连接参数：
package com.hy.multidelete;
/**
* 数据库连接参数
* @author 逆火
*
* 2019年11月16日 上午8:09:24
*/
public final class DBParam {
public final static String Driver = "oracle.jdbc.driver.OracleDriver";
public final static String DbUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
public final static String User = "ufo";
public final static String Pswd = "1234";
}
--END-- 2019年11月28日20:53:39
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
