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

Source: Internet
Author: User
Tags stringbuffer

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.