Java DBCP Connection pool, large data processing loop multiple table operation insert case

Base Connection Pool class:

Package Com.yl.sys.dao;

Import Java.io.InputStream;
Import java.sql.Connection;
Import java.sql.SQLException;
Import java.util.Properties;
Import Java.util.Vector;

public class Connectionpooltool {
Private vector<connection> pool;
Private String URL;
Private String user;
private String password;

/**
* The size of the connection pool, which is the number of database connections in the connection pool.
*/
private int poolsize = 1;

private static Connectionpooltool instance = NULL;

/**
* Private construction method, prohibit external creation of objects of this class, want to get the object of this class, through the <code>getIstance</code> method. The list mode in design mode is used.
*/
Private Connectionpooltool () {
Init ();
}

/**
* Connection Pool initialization method, read the contents of the properties file to establish the initial connection in the connection pool
* @date 10:40:20
* @author Duchaowei
* @descripte
*/
private void init () {
Pool = new vector<connection> (poolsize);
Readconfig ();
Addconnection ();
}

/**
* Back to connect to the connection pool
* @date 10:40:29
* @author Duchaowei
* @descripte
* @param Conn
*/
Public synchronized void release (Connection conn) {
POOL.ADD (conn);

}

/**
* Close all database connections in the connection pool
*/
Public synchronized void Closepool () {
for (int i = 0; i < pool.size (); i++) {
try {
((Connection) pool.get (i)). Close ();
} catch (SQLException e) {
E.printstacktrace ();
}
Pool.remove (i);
}
}

/**
* Returns an object from the current connection pool
*/
public static Connectionpooltool getinstance () {
if (instance = = null) {
Instance = new Connectionpooltool ();
}
return instance;
}

/**
* Returns a database connection in the connection pool
*/
Public synchronized Connection getconnection () {
if (pool.size () > 0) {
Connection conn = pool.get (0);
Pool.remove (conn);
Return conn;
} else {
return null;
}
}

/**
* Create a database connection for the initial settings in the connection pool
* @date 9:10:14
* @author Duchaowei
* @descripte
*/
private void Addconnection () {
Connection conn = null;
for (int i = 0; i < poolsize; i++) {

try {
Class.forName ("Org.postgresql.Driver");
conn = java.sql.DriverManager.getConnection (URL, user, password);
POOL.ADD (conn);

} catch (ClassNotFoundException e) {
E.printstacktrace ();
} catch (SQLException e) {
E.printstacktrace ();
}

}
}

/**
* Read setting properties file for connection pool
* @date 9:10:02
* @author Duchaowei
* @descripte
*/
private void Readconfig () {
Properties prop = new properties ();
InputStream in = LocalPostgisDAO.class.getResourceAsStream ("localpost.properties");
try {
Prop.load (in);
url = prop.getproperty ("url");
user = Prop.getproperty ("user");
Password = prop.getproperty ("password");
} catch (Exception e) {
E.printstacktrace ();
}
}
}

Call class

Package com.yl.zhi.stand.root;

Import Java.io.BufferedReader;
Import Java.io.File;
Import Java.io.FileInputStream;
Import java.io.IOException;
Import Java.io.InputStreamReader;
Import java.sql.Connection;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.sql.Statement;
Import java.util.ArrayList;
Import Java.util.HashMap;
Import java.util.List;
Import Java.util.Map;

Import Org.json.JSONArray;
Import Org.json.JSONObject;

Import Com.alibaba.fastjson.JSON;
Import Com.mongodb.BasicDBObject;
Import com.mongodb.DBCollection;
Import Com.mongodb.DBCursor;
Import Com.mongodb.DBObject;
Import Com.yl.sys.dao.ConnectionPoolTool;
Import Com.yl.sys.dao.LocalMongoDAO;
Import Com.yl.zhi.stand.utils.CompressUtils;

/**
* Create a citation relationship class
*
* @date 3:25:21
* @author Duchaowei
* @descripte
*/
public class Createquoterelation {

Document parsing configuration file
Private static final String name = "Createquoterelation_config.js";
Query data Volume
private static final int searchsize = 20000;
To store all citations of hashcode,
Private Map<integer, integer> quoterepeatmap = new hashmap<> ();
Number of statistical error citations
private int errorcount = 0;
Store error citations
Private list<string> errorlist = new arraylist<> ();
Statistics cannot be inserted into document data
private int errorwx = 0;
Document file ID not inserted
Private list<string> errorwxlist = new arraylist<> ();

private Connection con = null;

public void Start () {
Connection MONGO
Localmongodao.initmongodb ();

try {
Execute ();
} catch (Exception e) {
E.printstacktrace ();
}
}

/**
* Perform input
*
* @date 3:35:35
* @author Duchaowei
* @descripte
* @throws Exception
*/
public void execute () throws Exception {
Reading configuration Files
Jsonarray main = new Jsonarray (Getjsonconfig ());
for (int i = 0; i < main.length (); i++) {
Reader (Main.getjsonobject (i));
}
}

private void Reader (Jsonobject object) throws Exception {
MONGO source data table name
String mongotable = object.getstring ("mongotable");
Storing the main table
String maintable = object.getstring ("maintable");
Relational tables
String relationtable = object.getstring ("relationtable");
Get the total MongoDB source data
int totalcount = Getmongodbcount (mongotable);
int Searchcount = (totalcount + searchSize-1)/searchsize;

Initializing the database connection pool
Connectionpooltool pool = connectionpooltool.getinstance ();
for (int i = 0; i < Searchcount; i++) {
int skipnum = searchsize * i;
list<map<string, object>> list = Getmongodata (mongotable, skipnum);
SYSTEM.OUT.PRINTLN ("Processing 20,000 articles start time:" + system.currenttimemillis ());

Open transaction
For (map<string, object> map:list) {
Long start = System.currenttimemillis ();
con = pool.getconnection ();
Con.setautocommit (FALSE);
try {
Insert Main Table (literature table), return as data ID
Integer Mainid = Insertmain (map, maintable);
Work with citations, return IDs of citations
list<integer> quotelist = dealquote (Map, object);

if (quotelist! = null && quotelist.size () > 0) {
Insert Relationship Table
Insertrelation (Mainid, Quotelist, relationtable);
}
Submit
Con.commit ();
} catch (Exception e) {
E.printstacktrace ();
errorwx++;
Errorwxlist.add (Map.get ("_id"). toString ());
Con.rollback ();
Con.setautocommit (TRUE);
}finally{
Release link
Pool.release (con);
}
}
SYSTEM.OUT.PRINTLN ("Processing 20,000 articles End time:" + system.currenttimemillis ());
}
Close Connection Pool
Pool.closepool ();
System.out.println ("The number of documents not inserted is:" + errorwx);
System.out.println ("not inserted in the bibliography is named:" + json.tojsonstring (errorwxlist));
SYSTEM.OUT.PRINTLN ("Error format Citation quantity:" + errorcount);
}

/**
* Get citations for this document
*
* @date 9:19:41
* @author Duchaowei
* @descripte
* @param map
* @return
* @throws SQLException
*/
Private list<integer> Dealquote (map<string, object> Map, Jsonobject Object) throws SQLException {
Returns all citations IDs
list<integer> list = new arraylist<> ();
MONGO source data table name
String mongotable = object.getstring ("mongotable");
Table of citations
String quotetable = object.getstring ("quotetable");
Get Data
byte[] str = compressutils.ungzip ((byte[)) map.get ("Data");
String obj = new string (str);
Jsonobject data = new Jsonobject (obj);
if (data! = NULL) {
Get URLs to differentiate old and new data
String webSite = data.getstring ("Detail_uri");
Document type
String type = Map.get ("Source_type"). ToString ();
Citation
String reference = data.getstring ("reference");
string[] Quotes = getsplitquote (reference);
Store all the processed citations
list<map<string, object>> quotelist = new arraylist<> ();
for (String quote:quotes) {
if (quote! = NULL &&! "". Equals (quote)) {
int quotehashcode = Quote.hashcode ();
if (Quoterepeatmap.containskey (Quotehashcode)) {
Citation ID
Integer Quoteid = Quoterepeatmap.get (Quotehashcode);
List.add (Quoteid);
} else {
map<string, object> quotemap = null;
Quote = Quote.replaceall ("'", "" ");
if (Website.equals ("")) {
Old data processing citations
Quotemap = oldformatquote (quote, mongotable, type);
} else {
Working with citations
Quotemap = newformatquote (quote, mongotable);
}
if (quotemap! = null) {
Quotelist.add (QUOTEMAP);
}
}
}
}
Insert citation to postgress return insert Object
List= insertquote (quotelist, quotetable);
}
return list;
}

/**
* Split citation
*
* @date 3:19:21
* @author Duchaowei
* @descripte
* @param str
* @return
*/
Private string[] Getsplitquote (String str) {
if (str! = null) {
String reg = "[\\[]{1}[0-9][\\]]{1}";
Return Str.split (REG);
}
return null;
}

/**
* Insert Relationship Table
*
* @date 2:46:38
* @author Duchaowei
* @descripte
* @param list
* @param relationtable
* @throws SQLException
*/
private void Insertrelation (Integer mainid, list<integer> quotelist, String relationtable) throws SQLException {
StringBuffer sql = new StringBuffer ();
Sql.append ("INSERT into" + relationtable);
Sql.append ("(wx_id,yw_id)");
Sql.append ("values");
StringBuffer valuesql = new StringBuffer ();
for (Integer quoteid:quotelist) {
Valuesql.append ("(" + Mainid + "," + Quoteid + "),");
}
String valuestr = valuesql.tostring ();
Valuestr = valuestr.substring (0, Valuestr.lastindexof (",")) + ";";
Sql.append (VALUESTR);
PreparedStatement PS1 = con.preparestatement (sql.tostring ());
Ps1.executeupdate ();
}

/**
* Insert citation to Table of citations
*
* @date 11:03:07
* @author Duchaowei
* @descripte
* @param map
* @return
* @throws SQLException
*/
Private list<integer> Insertquote (list<map<string, object>> List, String quotetable)
Throws SQLException {
list<integer> resultlist = new arraylist<> ();
if (list = null && list.size () > 0) {
StringBuffer sb = new StringBuffer ();
Sb.append ("INSERT into" + quotetable);
Sb.append ("(Mongo_id,title,type,author,company,dates,flag)");
Sb.append ("values");
StringBuffer valuesb = new StringBuffer ();
For (map<string, object> map:list) {
Valuesb.append ("(" + map.get ("mongo_id") + ",");
Valuesb.append ("'" + map.get ("title"). ToString () + "',");
Valuesb.append ("'" + map.get ("type"). ToString () + "',");
Valuesb.append ("'" + Map.get ("author"). ToString () + "',");
Valuesb.append ("'" + Map.get ("Company"). ToString () + "',");
Valuesb.append ("'" + map.get ("dates"). ToString () + "',");
Valuesb.append ("0),");
}
String valuestr = valuesb.tostring ();
Sb.append (valuestr.substring (0, Valuestr.lastindexof (",")));
PreparedStatement PS1 = con.preparestatement (sb.tostring (), Statement.return_generated_keys);
Ps1.executeupdate ();
ResultSet rs = Ps1.getgeneratedkeys ();
while (Rs.next ()) {
Resultlist.add (Rs.getint (1));
}
Rs.close ();
}
return resultlist;
}

/**
* Insert Main Table
*
* @date 9:07:26
* @author Duchaowei
* @descripte
* @param map
* @param tableName
* @return
* @throws SQLException
*/
Private Integer Insertmain (map<string, object> Map, String tableName) throws SQLException {
Integer result = null;
Data
byte[] str = compressutils.ungzip ((byte[)) map.get ("Data");
String obj = new string (str);
Jsonobject data = new Jsonobject (obj);
StringBuffer sb = new StringBuffer ();
Sb.append ("INSERT into" + tableName);
Sb.append ("(Mongo_id,title,type,author,company,dates,flag)");
Sb.append ("values");
Sb.append ("'" + map.get ("_id"). ToString () + "',");
Sb.append ("'" + data.getstring ("title"). ReplaceAll ("'", "'" ") +" ', ");
Sb.append ("'" + data.getstring ("type"). ReplaceAll ("'", "'" ") +" ', ");
Sb.append ("'" + data.getstring ("author"). ReplaceAll ("'", "" ") +" ', ");
Sb.append ("'" + data.getstring ("Company"). ReplaceAll ("'", "'" ") +" ', ");
Sb.append ("'" + data.getstring ("dates"). ReplaceAll ("'", "" ") +" ', ");
Sb.append ("0)");

PreparedStatement PS1 = con.preparestatement (sb.tostring (), Statement.return_generated_keys);
Ps1.executeupdate ();
ResultSet rs = Ps1.getgeneratedkeys ();
while (Rs.next ()) {
result = Rs.getint (1);
}
Rs.close ();
Queryrunner runner = new Queryrunner (DataSource);
result = Runner.insert (Sb.tostring (), New scalarhandler<integer> ("id"));
Arrayhandler: Converts the first row of data in the result set into an array of objects.
Arraylisthandler: Converts each row of data in the result set into an array of objects, which is then stored in the list.
Beanhandler: Encapsulates the first row of data in the result set into a corresponding JavaBean instance.
Beanlisthandler: Each row of data in the result set is encapsulated in a corresponding JavaBean instance and stored in the list.
Maphandler: Encapsulates the first row of data in the result set into a map, where key is the column name, and value is the corresponding value.
Maplisthandler: Each row of data in the result set is encapsulated in a map and then stored in the list.
Columnlisthandler: The data from a column in the result set is stored in the list.
Keyedhandler (name): Encapsulates each row of data in the result set into a map (List) and then saves the map to a map with the specified column key.
Scalarhandler: Gets the value of the first row of data specified column in the result set, commonly used for single-value queries
result =
Localpostgisdao.getinstance (). Getquery (). Insert (Sb.tostring (),
New Resultsethandler<integer> () {
@Override
Public Integer handle (ResultSet arg0) throws SQLException {
Return Arg0.getint (0);
// }});
return result;
}

/**
* Old data processing citations
*
* @date 4:37:24
* @author Duchaowei
* @descripte
* @param quote
* @param mongotable
* @return
*/
Private map<string, object> oldformatquote (string quote, String mongotable, String type) {

map<string, object> rsmap = new hashmap<> ();
string[] Mdarray = Quote.split (",");
try {
Mongoid
Rsmap.put ("mongo_id", 0);
Article name
Rsmap.put ("title", Mdarray[1].trim ());
Type
Rsmap.put ("type", type);
Author
Rsmap.put ("Author", Mdarray[0].trim ());
Institutions
Rsmap.put ("Company", Mdarray[2].trim (). Replace ("'", "" "));
Time
Rsmap.put ("Dates", Mdarray[3].trim ());
} catch (Exception e) {
errorcount++;
Errorlist.add (quote);
return null;
}
Get mongoid of citations
map<string, object> map = Getmongodata (mongotable, mdarray[1]);
if (map! = null) {
Rsmap.put ("mongo_id", Map.get ("_id"));
} else {
Rsmap.put ("mongo_id", 0);
// }
return rsmap;

}

/**
* Handling Single citations
*
* @date 10:24:37
* @author Duchaowei
* @descripte
* @param quote
* @return
*/
Private map<string, object> newformatquote (string quote, String mongotable) {
map<string, object> rsmap = new hashmap<> ();
string[] Mdarray = Quote.replaceall (";", ""). Split ("\ \");
try {
Mongoid
Rsmap.put ("mongo_id", 0);
Article name
Rsmap.put ("title", Mdarray[0].trim (). substring (0, Mdarray[0].indexof ("[")));
Type
Rsmap.put ("type", Mdarray[0].trim (). substring (Mdarray[0].indexof ("["), Mdarray[0].length ()));
Author
Rsmap.put ("Author", Mdarray[1].trim ());
Institutions
Rsmap.put ("Company", Mdarray[2].trim ());
Time
Rsmap.put ("Dates", Mdarray[3].trim ());
} catch (Exception e) {
errorcount++;
Errorlist.add (quote);
return null;
}
Get mongoid of citations
map<string, object> map = Getmongodata (mongotable, mdarray[0].substring (0, Mdarray[0].indexof ("[")));
if (map! = null) {
Rsmap.put ("mongo_id", Map.get ("_id"));
} else {
Rsmap.put ("mongo_id", 0);
//}
Rsmap.put ("mongo_id", 0);
return rsmap;
}

/**
* Get the total number of MONGO documents
*
* @date pm 4:01:33
* @author Duchaowei
* @descripte
* @param collectionname
* @return
*/
Private int getmongodbcount (String collectionname) {
Basicdbobject sort = new Basicdbobject () ;
Sort.put ("_id", 1);
Dbcollection collection = Localmongodao.getcollection (CollectionName);
Dbcursor cursor = Collection.find (). sort (sort);
return Cursor.count ();
}

/**
* Paging Query
*
* @date pm 5:44:57
* @author Duchaowei
* @descripte
* @param collectionname
* @param skipnum
* @param totalcount
* @return
*/
@SuppressWarnings ("unchecked")
Private List<map <string, object>> getmongodata (String collectionname, int skipnum) {
List<map<string, Object> > list = new arraylist<> ();
Basicdbobject sort = new Basicdbobject ();
Sort.put ("_id", 1);//1 identification ordered
Dbcollection collection = Localmongodao.getcollection (CollectionName);
dbcursor cursor = Collection.find (). Sort (sort). Skip (Skipnum). Limit (searchsize);
while (Cursor.hasnext ()) {
DBObject obj = Cursor.next ();
if (obj! = null) {
List.add (Obj.tomap ());
}
}
return list;
}

/**
* Get MONGO Data
*
* @date morning 11:08:51
* @author Duchaowei
* @descripte
* @param collectionname< br> * @param tital
* @return
*/
@SuppressWarnings ("unchecked")
Private map<string, object> Getmongodata (String CollectionName, string title) {
Basicdbobject queryobj = new Basicdbobject ();
Basicdbobject queryobj_1 = new Basicdbobject ();
Queryobj.put ("Data", queryobj_1);
Queryobj_1.put ("title", title);
Dbcollection collection = Localmongodao.getcollection (CollectionName);
DBObject obj = Collection.findone (queryobj);
if (obj! = null) {
return obj.tomap ();
}
return null;
}

/**
* Get configuration file
*
* @date 3:37:57
* @author Duchaowei
* @descripte
* @return
* @throws IOException
*/
Private String Getjsonconfig () throws IOException {
StringBuffer buffer = new StringBuffer ();
String Realpath = Rootstart.getrootpath ();
String subpath = "/web-inf/res/config/" + name;
String FilePath = Realpath + subpath;
SYSTEM.OUT.PRINTLN ("Standardization of scientific Documents:" + FilePath);
String FilePath = "d:/ylkfsoft/workspace/indexweb/zhishi_config/" + name;
File File = new file (FilePath);
BufferedReader BufferedReader = new BufferedReader (new InputStreamReader (new FileInputStream (file), "Utf-8"));
String line = null;
while (line = Bufferedreader.readline ()) = null) {
line = Line.trim ();
Buffer.append (line);
}
Bufferedreader.close ();

Return Removenotes (Buffer.tostring ());
}

/**
* Remove Comments
*
* @date 3:38:10
* @author Duchaowei
* @descripte
* @param str
* @return
*/
private string Removenotes (String str) {
int start = Str.indexof ("//#");
int end = Str.indexof ("#//");
if (Start >-1 && End >-1) {
String oldstr = str.substring (start, end + 3);
String removestr = str.replace (Oldstr, "");
Return Removenotes (REMOVESTR);
} else {
return str;
}
}

public static void Main (string[] args) {
Createquoterelation CQR = new Createquoterelation ();
Cqr. Start ();
}
}

Java DBCP Connection pool, large data processing loop multiple table operation insert case