When working on projects, technical selection is very important. The underlying method directly affects our speed of access and modification of big data. There are many excellent ORM frameworks in Java, such as JPA, Hibernate, etc. As we said, frameworks have the benefits of frameworks, and of course there are some areas that can be improved. At this time, we need to re-encapsulate the underlying architecture for different business needs and different visits to support big data addition, deletion and modification.
Code:
import java.io.*; import java.sql.*; 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; //The sql statement to be passed public void setSql(String sql) { this.sql = sql; } private List sqlValues; //System parameters of sql statement public void setSqlValues(List sqlValues) { this.sqlValues = sqlValues; } private List<List> sqlValue; //System parameters of sql statement 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(); //Assign initial value to the Connection object} /** * Get 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"); //Get 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(); } } if(con!=null){ try { con.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(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(); } } } /** * Find * @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 a placeholder exists in a sql statement, setSqlValues(pst,sqlValues); } rst=pst.executeQuery(); result=ResultSupport.toResult(rst); // Be sure to complete the conversion before closing the database} catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ this.closeAll(con, pst, rst); } return result; } /** * Add, delete, modify, * @return */ public int executeUpdate(){ int result=-1; PreparedStatement pst=null; try { pst=con.prepareStatement(sql); if(sqlValues!=null&&sqlValues.size()>0){ // When a placeholder exists in a sql 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; } /** * Method using PreparedStatement to add batches* @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 to 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 to 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 automatic submission mode Boolean booleanautoCommit=false; String[] SqlString= null; int[] result= null; try { booleanautoCommit=con.getAutoCommit(); //Close automatic submission con.setAutoCommit(false); Statement stmt =con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); //Use Statement to collect multiple sql statements at the same time/*stmt.addBatch(insert_sql1); stmt.addBatch(insert_sql2); stmt.addBatch(update_sql3); */ for(String strvalue: SqlString){ stmt.addBatch(strvalue); } //Submit all sql statements at the same time stmt.executeBatch(); //Submit modification con.commit(); con.setAutoCommit(booleanautoCommit); this.closeAll(con, stmt, null); } catch(Exception e) { e.printStackTrace(); con.rollback(); //Set setAutoCommit(false) does not perform the rollBack operation of Connection in the catch, and the table of the operation will be locked, causing the database to be deadlocked} return result; } /** * Assign a value to the placeholder in the sql 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 write information to access the database 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(); } }