Java 使用 JDBC 连接数据库的代码整合[MySql、SqlServer、Oracle]-[经过设计模式改造]（2020年寒假小目标01）

博客期：121

星期三

　　今天对过去整个大二和大三用到的数据库的方法进行汇总，可以有效的使用、套用，每一个部分都有《软件设计模式》知识，上述代码满足了开闭原则，如果要使用此代码，只需添加数据类继承已经写好的类就可以了，无需修改。　　

　　开发项目中，项目整体结构：

　    【小编网不好，图传不上去，到时候，补上】

　　类图：

　　【小编网不好，图传不上去，到时候，补上】

　　源码：

　　　　com.dblink.basic.sqlkind 包：

 1 package com.dblink.basic.sqlKind;
 2 
 3 /*标准信息连接类*/
 4 public abstract class Sql {
 5     //---[属性成员]
 6     /*端口*/
 7     protected String port = "";
 8     /*端口*/
 9     protected String database = "";
10     /*主机地址*/
11     protected String host = "";
12     //---[方法成员]
13     /*获取连接 URL*/
14     public abstract String buildURL();
15     /*获得相应数据库给定的驱动*/
16     public abstract String getDriver();
17     /*get、set方法*/
18     public String getPort() {
19         return port;
20     }
21     public void setPort(String port) {
22         this.port = port;
23     }
24     public String getDatabase() {
25         return database;
26     }
27     public void setDatabase(String database) {
28         this.database = database;
29     }
30     /*构造方法*/
31     public Sql(String database,String port) {
32         this.port = port;
33         this.database = database;
34         this.host = "localhost";
35     }
36     public Sql(String database,String port,String host) {
37         this.port = port;
38         this.database = database;
39         this.host = host;
40     }
41 }

 1 package com.dblink.basic.sqlKind;
 2 
 3 public class MySql extends Sql{
 4     //---[方法成员]
 5     /*构造方法*/
 6     public MySql(String database) {
 7         super(database, "3306");
 8     }
 9     public MySql(String database, String port) {
10         super(database, port);
11     }
12     public MySql(String database, String port,boolean is) {
13         super(database, port);
14     }
15     public MySql(String database, boolean is,String host) {
16         super(database, "3306", host);
17     }
18     public MySql(String database, String port, String host) {
19         super(database, port, host);
20     }
21     /*获取连接 URL*/
22     @Override
23     public String buildURL() {
24         return "jdbc:mysql://"+host+":"+port+"/"+database+"?useSSL=false&serverTimezone=UTC";
25     }
26     /*获得相应数据库给定的驱动*/
27     @Override
28     public String getDriver() {
29         return "com.mysql.jdbc.Driver";
30     }
31 }

 1 package com.dblink.basic.sqlKind;
 2 
 3 public class MySql_s extends MySql{
 4     //---[方法成员]
 5     /*构造方法*/
 6     public MySql_s(String database) {
 7         super(database);
 8     }
 9     public MySql_s(String database, String port) {
10         super(database, port);
11     }
12     public MySql_s(String database, String port, boolean is) {
13         super(database, port, is);
14     }
15     public MySql_s(String database, boolean is, String host) {
16         super(database, is, host);
17     }
18     public MySql_s(String database, String port, String host) {
19         super(database, port, host);
20     }
21     /*获得相应数据库给定的驱动*/
22     @Override
23     public String getDriver() {
24         return "com.mysql.cj.jdbc.Driver";
25     }
26 }

 1 package com.dblink.basic.sqlKind;
 2 
 3 public class SqlServer extends Sql{
 4     //---[方法成员]
 5     /*构造方法*/
 6     public SqlServer(String database) {
 7         super(database, "1433");
 8     }
 9     public SqlServer(String database, String port) {
10         super(database, port);
11     }
12     public SqlServer(String database, String port,boolean is) {
13         super(database, port);
14     }
15     public SqlServer(String database, boolean is,String host) {
16         super(database, "1433", host);
17     }
18     public SqlServer(String database, String port, String host) {
19         super(database, port, host);
20     }
21     /*获取连接 URL*/
22     @Override
23     public String buildURL() {
24         return "jdbc:sqlserver://"+host+":"+port+";DatabaseName="+database+";";
25     }
26     /*获得相应数据库给定的驱动*/
27     @Override
28     public String getDriver() {
29         return "com.microsoft.sqlserver.jdbc.SQLServerDriver";
30     }
31 }

 1 package com.dblink.basic.sqlKind;
 2 
 3 public class WindowsSqlServer extends SqlServer {
 4     //---[方法成员]
 5     /*构造方法*/
 6     public WindowsSqlServer(String database) {
 7         super(database);
 8     }
 9     public WindowsSqlServer(String database, String port) {
10         super(database, port);
11     }
12     public WindowsSqlServer(String database, String port, boolean is) {
13         super(database, port, is);
14     }
15     public WindowsSqlServer(String database, boolean is, String host) {
16         super(database, is, host);
17     }
18     public WindowsSqlServer(String database, String port, String host) {
19         super(database, port, host);
20     }
21     /*获取连接 URL*/
22     @Override
23     public String buildURL() {
24         return "jdbc:sqlserver://"+host+":"+port+";integratedSecurity=true;DatabaseName="+database+";";
25     }
26 }

 1 package com.dblink.basic.sqlKind;
 2 
 3 public class SqlServer extends Sql{
 4     //---[方法成员]
 5     /*构造方法*/
 6     public SqlServer(String database) {
 7         super(database, "1433");
 8     }
 9     public SqlServer(String database, String port) {
10         super(database, port);
11     }
12     public SqlServer(String database, String port,boolean is) {
13         super(database, port);
14     }
15     public SqlServer(String database, boolean is,String host) {
16         super(database, "1433", host);
17     }
18     public SqlServer(String database, String port, String host) {
19         super(database, port, host);
20     }
21     /*获取连接 URL*/
22     @Override
23     public String buildURL() {
24         return "jdbc:sqlserver://"+host+":"+port+";DatabaseName="+database+";";
25     }
26     /*获得相应数据库给定的驱动*/
27     @Override
28     public String getDriver() {
29         return "com.microsoft.sqlserver.jdbc.SQLServerDriver";
30     }
31 }

　　　　com.dblink.basic.utils 包：

1 package com.dblink.basic.utils;
2 
3 import java.sql.Connection;
4 
5 public interface DBUtils {
6     public Connection getConnection();
7 }

 1 package com.dblink.basic.utils;
 2 
 3 //登录成员类
 4 public class UserInfo {
 5     //---[属性成员]
 6     private String username;
 7     private String password;
 8     //---[方法成员]
 9     /*set、get方法*/
10     //set
11     public void setUsername(String username) {
12         this.username = username;
13     }
14     public void setPassword(String password) {
15         this.password = password;
16     }
17     //get
18     public String getUsername() {
19         return username;
20     }
21     public String getPassword() {
22         return password;
23     }
24     /*构造方法*/
25     public UserInfo(String username,String password){
26         this.username = username;
27         this.password = password;
28     }
29     /*检验*/
30     @Override
31     public String toString(){
32         return username+","+password;
33     }
34 }

　　　　com.dblink.basic 包：

 1 package com.dblink.basic;
 2 
 3 import com.dblink.basic.sqlKind.Sql;
 4 import com.dblink.basic.utils.UserInfo;
 5 
 6 import java.sql.Connection;
 7 import java.sql.DriverManager;
 8 import java.sql.SQLException;
 9 
10 public class SqlUtils {
11     //---[属性成员]
12     private Sql sqlInfo;
13     private UserInfo userInfo;
14     //---[方法成员]
15     private Connection getConnection() throws ClassNotFoundException, SQLException {
16         Class.forName(sqlInfo.getDriver());
17         Connection conn = null;
18         if(userInfo==null)
19             conn = DriverManager.getConnection(sqlInfo.buildURL());
20         else
21             conn = DriverManager.getConnection(sqlInfo.buildURL(),userInfo.getUsername(),userInfo.getPassword());
22 
23         return conn;
24     }
25     public SqlConnection getSQLConnection() throws SQLException, ClassNotFoundException {
26         return new SqlConnection(getConnection());
27     }
28     /*构造方法*/
29     public SqlUtils(Sql sqlInfo,UserInfo userInfo){
30         this.sqlInfo = sqlInfo;
31         this.userInfo = userInfo;
32     }
33 }

 1 package com.dblink.basic;
 2 
 3 import java.sql.Connection;
 4 import java.sql.ResultSet;
 5 import java.sql.SQLException;
 6 import java.sql.Statement;
 7 
 8 public class SqlConnection {
 9     //---[基础数据]
10     public Connection conn;
11     public Statement stmt;
12     public ResultSet rs;
13     //---[方法区]
14     //构造方法
15     public SqlConnection() {
16         this.conn = null;
17         this.stmt = null;
18         this.rs = null;
19     }
20     public SqlConnection(Connection conn) {
21         this.conn = conn;
22         this.stmt = null;
23         this.rs = null;
24     }
25     public void close() {
26         try {
27             if(this.rs!=null) {
28                 this.rs.close();
29             }
30             if(this.stmt!=null)
31                 this.stmt.close();
32             if(this.conn!=null)
33                 this.conn.close();
34         } catch (SQLException e) {
35             e.printStackTrace();
36         }
37     }
38 }

　　　　com.dblink.bean 包：

 1 package com.dblink.bean;
 2 
 3 import org.json.JSONArray;
 4 
 5 import java.util.ArrayList;
 6 import java.util.Collection;
 7 
 8 public class BeanGroup extends ArrayList <JavaBean> {
 9     //---[方法区]
10     //构造方法
11     public BeanGroup(int initialCapacity) {
12         super(initialCapacity);
13     }
14     public BeanGroup() {
15         super();
16     }
17     public BeanGroup(Collection<JavaBean> c) {
18         super(c);
19     }
20     //转化方法
21     public String toString(){
22         return super.toString();
23     }
24     public JSONArray toJSONArray(){
25         JSONArray jsonArray = new JSONArray();
26         int leng = super.size();
27         for(int i=0;i<leng;++i){
28             JavaBean jb = super.get(i);
29             jsonArray.put(jb.toJSONArray());
30         }
31         return jsonArray;
32     }
33 }

 1 package com.dblink.bean;
 2 
 3 import org.json.JSONArray;
 4 import org.json.JSONObject;
 5 
 6 import java.util.ArrayList;
 7 import java.util.Collection;
 8 
 9 public class JavaBean extends ArrayList<Object> {
10     //---[方法区]
11     //构造方法
12     public JavaBean() {
13         super();
14     }
15     public JavaBean(Collection<Object> c) {
16         super(c);
17     }
18     public JavaBean(int initialCapacity) {
19         super(initialCapacity);
20     }
21     //转化方法
22     public String toString(){
23         return super.toString();
24     }
25     public JSONArray toJSONArray(){
26         JSONArray jsonArray = new JSONArray();
27         int leng = super.size();
28         for(int i=0;i<leng;++i){
29             Object q = super.get(i);
30             jsonArray.put(q);
31         }
32         return jsonArray;
33     }
34 }

 1 package com.dblink.bean;
 2 
 3 import org.json.JSONArray;
 4 import org.json.JSONObject;
 5 
 6 public class ReadableTable {
 7     //---[属性成员]
 8     //列名称集合
 9     public JavaBean cloName;
10     //表格详细信息
11     public BeanGroup beans;
12     //---[方法成员]
13     /*构造方法*/
14     public ReadableTable(){
15         this.cloName = null;
16         this.beans = null;
17     }
18     public ReadableTable(JavaBean cloName,BeanGroup beans){
19         this.cloName = cloName;
20         this.beans = beans;
21     }
22     /*格式转化*/
23     public JSONObject toJSONObject(){
24         JSONObject jsonObject = new JSONObject();
25         jsonObject.put("Length",this.beans.size());
26         jsonObject.put("Column",this.cloName.size());
27         jsonObject.put("cloName",this.cloName.toJSONArray());
28         jsonObject.put("beans",this.beans.toJSONArray());
29         return jsonObject;
30     }
31     public JSONArray toJSONArray(){
32         JSONArray jsonArray = new JSONArray();
33         JSONObject jsonObject = new JSONObject();
34         int leng = this.beans.size();
35         int cloNum = this.cloName.size();
36         jsonObject.put("Length",leng);
37         jsonObject.put("Column",cloNum);
38         jsonObject.put("ColNames",this.cloName.toJSONArray());
39         jsonArray.put(jsonObject);
40         for (int i=0;i<leng;++i)
41         {
42             JSONObject jso = new JSONObject();
43             JavaBean jb = this.beans.get(i);
44             for(int j=0;j<cloNum;++j)
45             {
46                 Object obj = jb.get(j);
47                 String name = this.cloName.get(j).toString();
48                 jso.put(name,obj);
49             }
50             jsonArray.put(jso);
51         }
52         return jsonArray;
53     }
54 }

　　　　com.dblink.sql 包：

 1 package com.dblink.sql;
 2 
 3 import com.dblink.basic.SqlConnection;
 4 import com.dblink.basic.SqlUtils;
 5 import com.dblink.bean.BeanGroup;
 6 import com.dblink.bean.JavaBean;
 7 import com.dblink.bean.ReadableTable;
 8 
 9 import java.sql.PreparedStatement;
10 import java.sql.ResultSetMetaData;
11 import java.sql.SQLException;
12 
13 /*
                        

                            

                                
< 上一篇
05: MySQL高级查询
                            
                            

                                 
下一篇 >
MySQL必知存储引擎