Java implements Dbhelper to support big data addition deletion and modification
- 2020-05-05 11:11:01
- OfStack
When doing the project, the technology selection is very important, in the bottom of the method directly affects our big data access, and the speed of change, there are a lot of good in Java ORM framework, such as: JPA, Hibernate, etc., as we say, framework has the advantage of the framework, of course, there are some place to improve, this time, you need to our demand for different business, different views, encapsulation, afresh to the underlying architecture to support large data deletion.
Code:
import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.jsp.jstl.sql.*;
/**
* DbHelper
* @author qmx
*
*/
public class Dbhelper {
private String sql; // To the incoming sql statements
public void setSql(String sql) {
this.sql = sql;
}
private List sqlValues; //sql Statement parameters
public void setSqlValues(List sqlValues) {
this.sqlValues = sqlValues;
}
private List<List> sqlValue; //sql Statement parameters
public void setSqlValue(List<List> sqlValues) {
this.sqlValue = sqlValues;
}
private Connection con; // Connection object
public void setCon(Connection con) {
this.con = con;
}
public Dbhelper(){
this.con=getConnection(); // to Connection Is the initial value of the object
}
/**
* Getting a database connection
* @return
*/
private Connection getConnection(){
String driver_class=null;
String driver_url=null;
String database_user=null;
String database_password=null;
try {
InputStream fis=this.getClass().getResourceAsStream("/db.properties"); // Load the database configuration file into memory
Properties p=new Properties();
p.load(fis);
driver_class=p.getProperty("driver_class"); // Gets the database configuration file
driver_url=p.getProperty("driver_url");
database_user=p.getProperty("database_user");
database_password=p.getProperty("database_password");
Class.forName(driver_class);
con=DriverManager.getConnection(driver_url,database_user,database_password);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
/**
* Close the database
* @param con
* @param pst
* @param rst
*/
private void closeAll(Connection con,PreparedStatement pst,ResultSet rst){
if(rst!=null){
try {
rst.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(pst!=null){
try {
pst.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(con!=null){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* Close the database
* @param con
* @param pst
* @param rst
*/
private void closeAll(Connection con,Statement pst,ResultSet rst){
if(rst!=null){
try {
rst.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(pst!=null){
try {
pst.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(con!=null){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* To find the
* @param sql
* @param sqlValues
* @return
*/
public Result executeQuery(){
Result result=null;
ResultSet rst=null;
PreparedStatement pst=null;
try {
pst=con.prepareStatement(sql);
if(sqlValues!=null&&sqlValues.size()>0){ // when sql When a placeholder exists in a statement
setSqlValues(pst,sqlValues);
}
rst=pst.executeQuery();
result=ResultSupport.toResult(rst); // Be sure to complete the transformation before shutting down the database
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
this.closeAll(con, pst, rst);
}
return result;
}
/**
* Increases the deletion
* @return
*/
public int executeUpdate(){
int result=-1;
PreparedStatement pst=null;
try {
pst=con.prepareStatement(sql);
if(sqlValues!=null&&sqlValues.size()>0){ // when sql When a placeholder exists in a statement
setSqlValues(pst,sqlValues);
}
result=pst.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
this.closeAll(con, pst, null);
}
return result;
}
/**
* use PreparedStatement Add batch method
* @return
*/
public int[] executeUpdateMore(){
int[] result=null;
try{
PreparedStatement prest =con.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
for(List sqlValueString : sqlValue){
for(int i=0;i<sqlValueString.size();i++){
try {
prest.setObject(i+1,sqlValueString.get(i));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
prest.addBatch();
}
prest.executeBatch();
/* con.commit();*/
this.closeAll(con, prest, null);
} catch (SQLException ex){
Logger.getLogger(Dbhelper.class.getName()).log(Level.SEVERE, null,ex);
}
return result;
}
/**
* use PreparedStatement Add batch method ,strvalue:
* "INSERT INTOadlogs(ip,website,yyyymmdd,hour,object_id) VALUES('192.168.1.3','localhost','20081009',8,'23123')"
* @return
* @throws SQLException
*/
public int[] executeUpdateMoreNotAuto() throws SQLException{
int[] result =null;
con.setAutoCommit(false);
Statement stmt =con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
String[] SqlString= null;
for(String strvalue : SqlString){
stmt.execute(strvalue);
}
con.commit();
return result;
}
/**
* use PreparedStatement Add batch method ,strvalue:
* "INSERT INTOadlogs(ip,website,yyyymmdd,hour,object_id) VALUES('192.168.1.3','localhost','20081009',8,'23123')"
* @return
* @throws SQLException
*/
public int[] executeMoreNotAuto() throws SQLException{
// Save the current auto-commit mode
Boolean booleanautoCommit=false;
String[] SqlString= null;
int[] result= null;
try
{
booleanautoCommit=con.getAutoCommit();
// Turn off auto-submit
con.setAutoCommit(false);
Statement stmt =con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
// use Statement Collecting multiple pieces at the same time sql statements
/*stmt.addBatch(insert_sql1);
stmt.addBatch(insert_sql2);
stmt.addBatch(update_sql3);*/
for(String strvalue : SqlString){
stmt.addBatch(strvalue);
}
// Submit all of them at the same time sql statements
stmt.executeBatch();
// Commit changes
con.commit();
con.setAutoCommit(booleanautoCommit);
this.closeAll(con, stmt, null);
}
catch(Exception e)
{
e.printStackTrace();
con.rollback(); // set setAutoCommit(false) Not in the catch In the Connection the rollBack Operation, the table of the operation will be locked, resulting in a database deadlock
}
return result;
}
/**
* to sql Placeholder assignment in a statement
* @param pst
* @param sqlValues
*/
private void setSqlValues(PreparedStatement pst,List sqlValues){
for(int i=0;i<sqlValues.size();i++){
try {
pst.setObject(i+1,sqlValues.get(i));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
Our database access information is written in db.properties:
driver_class=com.mysql.jdbc.Driver
driver_url=jdbc:mysql://192.168.22.246:3306/importexceltest
database_user=basic
database_password=basic
Test:
import java.util.*;
public class ImportExcelTest {
public static void main(String[] args){
/*Dbhelper db = new Dbhelper();
String sql = "insert into tb_coursetype(id,courseTypeName) values('2012003','qmx3')";
db.setSql(sql);
db.executeUpdate();*/
/*Dbhelper db1 = new Dbhelper();
String sql1 = "insert into tb_coursetype(id,courseTypeName) values(?,?)";
List sqlValues = new ArrayList();
sqlValues.add("2012004");
sqlValues.add("qmx4");
db1.setSqlValues(sqlValues);
db1.setSql(sql1);
db1.executeUpdate();*/
Dbhelper db = new Dbhelper();
String sql = "insert into tb_coursetype(id,courseTypeName) values(?,?)";
List<List> sqlValues = new ArrayList();
List sqlValueString =new ArrayList();
sqlValueString.add("2012010");
sqlValueString.add("qmx10");
sqlValues.add(sqlValueString);
List sqlValueString1 =new ArrayList();
sqlValueString1.add("2012011");
sqlValueString1.add("qmx11");
sqlValues.add(sqlValueString1);
List sqlValueString2 =new ArrayList();
sqlValueString2.add("2012012");
sqlValueString2.add("qmx12");
sqlValues.add(sqlValueString2);
List sqlValueString3 =new ArrayList();
sqlValueString3.add("2012013");
sqlValueString3.add("qmx13");
sqlValues.add(sqlValueString3);
db.setSqlValue(sqlValues);
db.setSql(sql);
db.executeUpdateMore();
}
}