Probably the most powerful jdbc DBManager in history, jdbcdbmanager

Probably the most powerful jdbc DBManager in history, jdbcdbmanager

Package com. lb. util; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. resultSetMetaData; import java. SQL. SQLException; import java. SQL. statement; import java. util. hashMap; import java. util. using list; import java. util. list; import java. util. map; public class DBManagerV1 {private String userName; private String password; pr Ivate String url; private String driver; public DBManagerV1 (String userName, String password, String url, String driver) {this. userName = userName; this. password = password; this. url = url; this. driver = driver;} public Connection getConnection () {Connection conn = null; try {Class. forName (driver); conn = DriverManager. getConnection (url, userName, password);} catch (ClassNotFoundException e) {E. printStackTrace ();} catch (SQLException e) {e. printStackTrace () ;}return conn;} public void close (ResultSet rs, Statement statement, Connection conn) {if (rs! = Null) {try {rs. close () ;}catch (SQLException e) {e. printStackTrace () ;}} if (statement! = Null) {try {statement. close () ;}catch (SQLException e) {e. printStackTrace () ;}} if (conn! = Null) {try {conn. close ();} catch (SQLException e) {e. printStackTrace () ;}} public void close (ResultSet rs, PreparedStatement ps, Connection conn) {if (rs! = Null) {try {rs. close () ;}catch (SQLException e) {e. printStackTrace () ;}} if (ps! = Null) {try {ps. close () ;}catch (SQLException e) {e. printStackTrace () ;}} if (conn! = Null) {try {conn. close ();} catch (SQLException e) {e. printStackTrace () ;}}/ *** execute an SQL statement (no pre-compilation is used and there is a risk of SQL injection) * @ param SQL */public void execute (String SQL) {Connection conn = null; Statement statement = null; conn = getConnection (); try {statement = conn. createStatement (); statement.exe cute (SQL);} catch (SQLException e) {System. out. println (SQL); System. out. println ("error"); e. printSta CkTrace (); if (conn! = Null) {// transaction rollback try {conn. rollback ();} catch (SQLException e1) {e1.printStackTrace () ;}} finally {close (null, statement, conn );}} /*** execute multiple sqls in batches and roll back all failures (no pre-compilation is used, which is risky to SQL injection) */public void executesBantch (List <String> sqls) {Connection conn = null; Statement statement = null; conn = getConnection (); // disable automatic submission try {statement = conn. createStatement (); conn. setAutoCommit (false); (String SQL: sqls) {statement. addBatch (SQL);} statement.exe cuteBatch (); // submit the transaction conn. commit (); conn. setAutoCommit (true);} catch (SQLException e) {for (String SQL: sqls) {System. out. println (SQL);} System. out. println ("error"); e. printStackTrace (); if (conn! = Null) {// transaction rollback try {conn. rollback ();} catch (SQLException e1) {e1.printStackTrace () ;}} finally {close (null, statement, conn );}} /*** @ param querySql query statement * @ return Map <String, Object> </br> ** key: column name </br> * value: column value **/public List <Map <String, Object> query (String querySql) {Connection conn = null; PreparedStatement ps = null; conn = getConnection (); try {ps = conn. prepareSta Tement (querySql); ResultSet rs = ps.exe cuteQuery (); List <Map <String, Object> objMapList = new lateral List <> (); while (rs. next () {Map <String, Object> objMap = new HashMap <String, Object> (); ResultSetMetaData rsm = rs. getMetaData (); // get the column set int colCount = rsm. getColumnCount (); // obtain the number of columns for (int I = 0; I <colCount; I ++) {String colName = rsm. getColumnName (I + 1); Object colValue = rs. getObject (1 ); ObjMap. put (colName, colValue);} objMapList. add (objMap);} return objMapList;} catch (SQLException e) {e. printStackTrace ();} return null;}/*** pre-compiled batch execution (recommended) ** @ param paramsList parameter List * @ param sqlTemplate SQL template * @ throws RuntimeException */public void bantchExecute (List <Object []> paramsList, String sqlTemplate) throws RuntimeException {Connection conn = null; PreparedStatement ps = nul L; try {conn = getConnection (); conn. setAutoCommit (false); ps = conn. prepareStatement (sqlTemplate); for (Object [] params: paramsList) {for (int I = 0; I <params. length; I ++) {ps. setObject (I + 1, params [I]);} ps. addBatch ();} ps.exe cuteBatch (); // submit the transaction conn. commit ();} catch (SQLException e) {e. printStackTrace (); if (conn! = Null) {// transaction rollback try {conn. rollback (); throw new RuntimeException ();} catch (SQLException e1) {e1.printStackTrace () ;}} finally {close (null, ps, conn );}} /*** recommended, secure and Efficient * @ param sqlMaps </br> * List <Object []> paramList * String sqlTemplate * @ throws RuntimeException */public void bantchExecute (LinkedHashMap <List <Object []>, string> sqlMaps) throws RuntimeException {Connection conn = nul L; PreparedStatement ps = null; try {conn = getConnection (); for (Entry <List <Object []>, String> sqlMap: sqlMaps. entrySet () {conn. setAutoCommit (false); List <Object []> paramList = sqlMap. getKey (); String sqlTemplate = sqlMap. getValue (); ps = conn. prepareStatement (sqlTemplate); for (Object [] params: paramList) {for (int I = 0; I <params. length; I ++) {ps. setObject (I + 1, params [I]);} ps. addBa Tch () ;}ps.exe cuteBatch (); conn. commit () ;}} catch (SQLException e) {e. printStackTrace (); if (conn! = Null) {// transaction rollback try {conn. rollback (); throw new RuntimeException ();} catch (SQLException e1) {e1.printStackTrace () ;}} finally {close (null, ps, conn );}}}

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.