Tool for connecting to the database in the project

Different databases are sometimes used in projects. The project writes a tool class that connects to different databases (including mysql, SQLserver, oracle, and access): importjava. io. file; importjava. SQL. connection; importjava. SQL. driverManager; importjava. SQL. resultSet; importjava. SQL. resultSetMet

Different databases are sometimes used in projects. The project writes a tool class for connecting to different databases (including mysql, SQL server, oracle, and access): import java. io. file; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. resultSet; import java. SQL. resultSetMet

Different databases are sometimes used in projects. The project writes a tool class for connecting to different databases (including mysql, SQL server, oracle, and access:

Import java. io. file; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. resultSet; import java. SQL. resultSetMetaData; import java. SQL. SQLException; import java. SQL. statement; import java. util. arrayList; import java. util. hashMap; import java. util. list; import java. util. map; public class ConnectionDbUtils {/*** obtain the database connection object (SQL server) ** @ param server * @ param database * database name * @ param user_id * User name * @ param password * @ return Connection * @ throws ClassNotFoundException * @ throws SQLException */ public static Connection getSqlServerConnection (String server, string database, String user_id, String password) throws ClassNotFoundException, SQLException {Class. forName ("com. microsoft. sqlserver. jdbc. SQLServerDriver "); Connection con = DriverManager. getConnection ("jdbc: sqlserver: //" + server + ": 1433; DatabaseName =" + database, user_id, password); return con ;} /*** obtain the database connection object (MySql) ** @ param server * @ param database * database name * @ param user_id * User name * @ param password * @ return Connection * @ throws ClassNotFoundException * @ throws SQLException */ public static Connection getMySqlConnection (String server, string database, String user_id, String password) throws ClassNotFoundException, SQLException {Class. forName ("com. mysql. jdbc. driver "); return DriverManager. getConnection ("jdbc: mysql: //" + server + ": 3306/" + database, user_id, password );} /*** connect to oracle through JDBC * @ param server IP * @ param database * @ param user_id username * @ param password * @ param SQL * @ return * @ throws ClassNotFoundException *@ throws SQLException */public static Connection getOracleConnection (String server, string database, String user_id, String password) throws ClassNotFoundException, SQLException {Class. forName ("oracle. jdbc. driver. oracleDriver "); Connection conn = DriverManager. getConnection ("jdbc: oracle: thin: @" + server + ": 1521:" + database, user_id, password); return conn ;} /*** JDBC connection Access * @ param database path * @ param user_id username * @ param password * @ param SQL * @ return * @ throws ClassNotFoundException * @ throws SQLException *@ throws IllegalAccessException * @ throws InstantiationException */public static Connection getAccessConnection (String database, string user_id, String password) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {System. out. println ("==================" + database); Class. forName ("com. hxtt. SQL. access. accessDriver "). newInstance (); Connection conn = DriverManager. getConnection ("jdbc: Access: //" + database, user_id, password); System. out. println ("connection successful"); return conn ;} /*** Access query data ** @ param database * @ param user_id * @ param password * @ param strSql * @ return * @ throws Exception */public static List
 
  
> QueryAccessData (String database, String user_id, String password, String strSql) throws Exception {File file = new File (database); if (file. exists () {if (file. canWrite () {System. out. println ("not read-only");} else {System. out. println ("read-only"); file. setWritable (true) ;}} else {System. out. println ("does not exist");} Connection con = ConnectionDbUtils. getAccessConnection (database, user_id, password); Statement stmt = con. createStatement (); ResultSet rs = stmt.exe cuteQuery (strSql); List
  
   
> ListMap = new ArrayList
   
    
> (); While (rs. next () {Map
    
     
Map = new HashMap
     
      
(); ResultSetMetaData rsmd = rs. getMetaData (); for (int I = 1; I <= rsmd. getColumnCount (); I ++) {String columnName = rsmd. getColumnName (I); Object objValue = rs. getObject (columnName); map. put (columnName, objValue);} listMap. add (map) ;}return listMap ;} /*** add, delete, and modify Access ** @ param database * @ param user_id * @ param password * @ param SQL */public static void createAccessSQLExecute (String database, S Tring user_id, String password, String SQL) {Statement stmt = null; try {Connection con = ConnectionDbUtils. getAccessConnection (database, user_id, password); stmt = con. createStatement (); int I = stmt.exe cuteUpdate (SQL); System. out. println ("executing SQL statement:" + SQL); System. out. println ("processing successful! The number of processed items is "+ I);} catch (Exception e) {e. printStackTrace (); System. out. println ("execution failed, please check whether the remote database has enabled the service");} finally {try {if (null! = Stmt) {stmt. close () ;}} catch (SQLException e) {e. printStackTrace ();}}} /*** oracle query data * @ param server * @ param database * @ param user_id * @ param password * @ param strSql * @ return * @ throws Exception */public static List
      
        > QueryOracleData (String server, String database, String user_id, String password, String strSql) throws Exception {Connection con = ConnectionDbUtils. getOracleConnection (server, database, user_id, password); Statement stmt = con. createStatement (); ResultSet rs = stmt.exe cuteQuery (strSql); List
       
         > ListMap = new ArrayList
        
          > (); While (rs. next () {Map
         
           Map = new HashMap
          
            (); ResultSetMetaData rsmd = rs. getMetaData (); for (int I = 1; I <= rsmd. getColumnCount (); I ++) {String columnName = rsmd. getColumnName (I); Object objValue = rs. getObject (columnName); map. put (columnName, objValue);} listMap. add (map) ;}return listMap ;} /*** add, delete, and modify oracle ** @ param server * @ param database * @ param user_id * @ param password * @ param SQL */public static void createOracleSQLExecute (St Ring server, String database, String user_id, String password, String SQL) {Statement stmt = null; try {Connection con = ConnectionDbUtils. getOracleConnection (server, database, user_id, password); stmt = con. createStatement (); System. out. println ("run SQL statement:" + SQL); int I = stmt.exe cuteUpdate (SQL); System. out. println ("processing successful! The number of processed items is "+ I);} catch (Exception e) {e. printStackTrace (); System. out. println ("execution failed, please check whether the remote database has enabled the service");} finally {try {if (null! = Stmt) {stmt. close () ;}} catch (SQLException e) {e. printStackTrace () ;}}/ *** query data (SQL server) ** @ param server * @ param database name * @ param user_id username * @ param password * @ param strSql SQL statement * @ return List
           
             > * @ Throws Exception */public static List
            
              > QuerySqlServerData (String server, String database, String user_id, String password, String strSql) throws Exception {Connection con = ConnectionDbUtils. getSqlServerConnection (server, database, user_id, password); Statement stmt = con. createStatement (); System. out. println ("querySqlServerData SQL statement ==========" + strSql); ResultSet rs = stmt.exe cuteQuery (strSql); List
             
               > ListMap = new ArrayList
              
                > (); While (rs. next () {Map
               
                 Map = new HashMap
                
                  (); ResultSetMetaData rsmd = rs. getMetaData (); for (int I = 1; I <= rsmd. getColumnCount (); I ++) {String columnName = rsmd. getColumnName (I); Object objValue = rs. getObject (columnName); map. put (columnName, objValue);} listMap. add (map) ;}return listMap ;} /*** Execute SQL addition, modification, and deletion operations ** @ param conn * @ param SQL */public static void createSQLExecute (String server, String database, String user_id, string password, List
                 
                   SQL) {Statement stmt = null; try {Connection con = ConnectionDbUtils. getSqlServerConnection (server, database, user_id, password); stmt = con. createStatement (); for (String s: SQL) {System. out. println ("run SQL statement:" + SQL); int I = stmt.exe cuteUpdate (s); System. out. println ("processing successful! The number of processed items is "+ I);} catch (Exception e) {e. printStackTrace (); System. out. println ("execution failed, please check whether the remote database has enabled the service");} finally {try {if (null! = Stmt) {stmt. close () ;}} catch (SQLException e) {e. printStackTrace () ;}}/ *** add, modify, and delete SQL statements ** @ param conn * @ param SQL */public static void createSQLExecute (String server, string database, String user_id, String password, String SQL) {Statement stmt = null; try {System. out. println ("run SQL statement:" + SQL); Connection con = ConnectionDbUtils. getSqlServerConnection (server, database, user_id, Password); stmt = con. createStatement (); int I = stmt.exe cuteUpdate (SQL); System. out. println ("processed successfully! The number of processed items is "+ I);} catch (Exception e) {e. printStackTrace (); System. out. println ("execution failed, please check whether the remote database has enabled the service");} finally {try {if (null! = Stmt) {stmt. close () ;}} catch (SQLException e) {e. printStackTrace ();}}} /*** MySql query data * @ param server * @ param database * @ param user_id * @ param password * @ param strSql * @ return * @ throws Exception */public static List
                  
                    > QueryMySqlData (String server, String database, String user_id, String password, String SQL) throws Exception {Connection con = ConnectionDbUtils. getMySqlConnection (server, database, user_id, password); Statement stmt = con. createStatement (); ResultSet rs = stmt.exe cuteQuery (SQL); List
                   
                     > ListMap = new ArrayList
                    
                      > (); While (rs. next () {Map
                     
                       Map = new HashMap
                      
                        (); ResultSetMetaData rsmd = rs. getMetaData (); for (int I = 1; I <= rsmd. getColumnCount (); I ++) {String columnName = rsmd. getColumnName (I); Object objValue = rs. getObject (columnName); map. put (columnName, objValue);} listMap. add (map) ;}return listMap ;} /*** execute add, delete, and modify MySql * @ param server * @ param database * @ param user_id * @ param password * @ param SQL */public static void createMySqlExecute (Stri Ng server, String database, String user_id, String password, String SQL) {Connection con = null; Statement stmt = null; try {System. out. println ("run SQL statement:" + SQL); con = ConnectionDbUtils. getMySqlConnection (server, database, user_id, password); stmt = con. createStatement (); int successCount = stmt.exe cuteUpdate (SQL); System. out. println ("processing successful! The number of processed items is "+ successCount);} catch (Exception e) {e. printStackTrace (); System. out. println ("execution failed, please check whether the remote database has enabled the service");} finally {try {if (null! = Stmt) {stmt. close () ;}} catch (SQLException e) {e. printStackTrace ();}}}}