java连接mysql底层封装

最新推荐文章于 2022-11-07 16:36:56 发布

weixin_34408717

最新推荐文章于 2022-11-07 16:36:56 发布

阅读量243

点赞数

文章标签： java 数据库

原文链接：https://my.oschina.net/garyun/blog/602814

版权

为什么80%的码农都做不了架构师？>>>

 1 package com.dao.db;
 2 
 3 import java.sql.Connection;
 4 import java.sql.SQLException;
 5 
 6 /**
 7  * 数据库连接层MYSQL
 8  * @author Administrator
 9  *
10  */
11 public class DBConnection {
12     
13     
14     /**
15      * 连接数据库
16      * @return
17      */
18     public static Connection getDBConnection()
19     {
20         // 1. 注册驱动
21         try {
22             Class.forName("com.mysql.jdbc.Driver");
23         } catch (ClassNotFoundException e) {
24             // TODO Auto-generated catch block
25             e.printStackTrace();
26         }
27         // 获取数据库的连接
28         try {
29             Connection conn  = java.sql.DriverManager.getConnection("jdbc:mysql://localhost/mysql?useUnicode=true&characterEncoding=utf-8", "root", "root");
30             return conn;
31         } catch (SQLException e1) {
32             e1.printStackTrace();
33         }
34         return null;
35     }
36     
37 }

连接数据库

  1 package com.dao.db;
  2 
  3 import java.sql.Connection;
  4 import java.sql.PreparedStatement;
  5 import java.sql.ResultSet;
  6 import java.sql.ResultSetMetaData;
  7 import java.sql.SQLException;
  8 import java.sql.Types;
  9 import java.util.ArrayList;
 10 import java.util.HashMap;
 11 
 12 /**
 13  * MYSQL数据库底层封装
 14  * @author Administrator
 15  *
 16  */
 17 public class DBManager {
 18     
 19     private PreparedStatement pstmt;
 20     private Connection conn;
 21     private ResultSet rs;
 22     
 23 
 24     /**
 25      * 打开数据库
 26      */
 27     public DBManager() {
 28         conn = DBConnection.getDBConnection();
 29     }
 30     
 31     /**
 32      * 执行修改添加操作
 33      * @param coulmn
 34      * @param type
 35      * @param sql
 36      * @return
 37      * @throws SQLException
 38      */
 39     public  boolean updateOrAdd(String[] coulmn, int[] type, String sql) throws SQLException
 40     {
 41         if(!setPstmtParam(coulmn, type, sql))
 42             return false;
 43         boolean flag = pstmt.executeUpdate()>0?true:false;
 44         closeDB();
 45         return flag;
 46     }
 47     /**
 48      * 获取查询结果集
 49      * @param coulmn
 50      * @param type
 51      * @param sql
 52      * @throws SQLException
 53      */
 54     public DataTable getResultData(String[] coulmn, int[] type, String sql) throws SQLException
 55     {
 56         DataTable dt = new DataTable();
 57         
 58         ArrayList<HashMap<String, String>>list = new ArrayList<HashMap<String, String>>();
 59         
 60         if(!setPstmtParam(coulmn, type, sql))
 61             return null;
 62         rs = pstmt.executeQuery();
 63         ResultSetMetaData rsmd = rs.getMetaData();//取数据库的列名 
 64         int numberOfColumns = rsmd.getColumnCount();
 65         while(rs.next())
 66         {
 67             HashMap<String, String> rsTree = new HashMap<String, String>(); 
 68             for(int r=1;r<numberOfColumns+1;r++)
 69              {
 70                rsTree.put(rsmd.getColumnName(r),rs.getObject(r).toString());
 71              }
 72             list.add(rsTree);
 73         }
 74         closeDB();
 75         dt.setDataTable(list);
 76         return dt;
 77     }
 78     
 79     /**
 80      * 参数设置
 81      * @param coulmn
 82      * @param type
 83      * @throws SQLException 
 84      * @throws NumberFormatException 
 85      */
 86     private boolean setPstmtParam(String[] coulmn, int[] type, String sql) throws NumberFormatException, SQLException
 87     {
 88         if(sql== null) return false;
 89         pstmt = conn.prepareStatement(sql);
 90         if(coulmn != null && type != null && coulmn.length !=0 && type.length !=0   )
 91         {        
 92             for (int i = 0; i<type.length; i++) {
 93                 switch (type[i]) {
 94                 case Types.INTEGER:
 95                     pstmt.setInt(i+1, Integer.parseInt(coulmn[i]));
 96                     break;
 97                 case Types.BOOLEAN:
 98                     pstmt.setBoolean(i+1, Boolean.parseBoolean(coulmn[i]));
 99                     break;
100                 case Types.CHAR:
101                     pstmt.setString(i+1, coulmn[i]);
102                     break;
103                 case Types.DOUBLE:
104                     pstmt.setDouble(i+1, Double.parseDouble(coulmn[i]));
105                     break;
106                 case Types.FLOAT:
107                     pstmt.setFloat(i+1, Float.parseFloat(coulmn[i]));
108                     break;
109                 default:
110                     break;
111                 }
112             }
113         }
114         return true;
115     }
116     
117     /**
118      * 关闭数据库
119      * @throws SQLException
120      */
121     private void closeDB() throws SQLException
122     {
123         if(rs != null)
124         {
125             rs.close();
126         }
127         if(pstmt != null)
128         {
129             pstmt.close();
130         }
131         if(conn != null)
132         {
133             conn.close();
134         }
135         
136     }
137 }

数据层封装

 1 package com.dao.db;
 2 
 3 import java.util.ArrayList;
 4 import java.util.HashMap;
 5 import java.util.Iterator;
 6 import java.util.Map;
 7 import java.util.Set;
 8 
 9 /**
10  * 数据集封装
11  * @author Administrator
12  *
13  */
14 public class DataTable {
15     
16     public String[] column;//列字段
17     public String[][] row; //行值
18     public int rowCount = 0;//行数
19     public int colCoun = 0;//列数
20     
21     
22     public DataTable() {
23         super();
24     }
25     
26     public DataTable(String[] column, String[][] row, int rowCount, int colCoun) {
27         super();
28         this.column = column;
29         this.row = row;
30         this.rowCount = rowCount;
31         this.colCoun = colCoun;
32     }
33 
34 
35     public void setDataTable(ArrayList<HashMap<String, String>> list) {
36         rowCount = list.size();
37         colCoun = list.get(0).size();
38         column = new String[colCoun];
39         row = new String[rowCount][colCoun];
40         for (int i = 0; i < rowCount; i++) {
41             Set<Map.Entry<String, String>> set = list.get(i).entrySet();
42             int j = 0;
43             for (Iterator<Map.Entry<String, String>> it = set.iterator(); it
44                     .hasNext();) {
45                 Map.Entry<String, String> entry = (Map.Entry<String, String>) it
46                         .next();
47                 row[i][j] = entry.getValue();
48                 if (i == rowCount - 1) {
49                     column[j] = entry.getKey();
50                 }
51                 j++;
52             }
53         }
54     }
55 
56     public String[] getColumn() {
57         return column;
58     }
59 
60     public void setColumn(String[] column) {
61         this.column = column;
62     }
63 
64     public String[][] getRow() {
65         return row;
66     }
67 
68     public void setRow(String[][] row) {
69         this.row = row;
70     }
71 
72     public int getRowCount() {
73         return rowCount;
74     }
75 
76     public void setRowCount(int rowCount) {
77         this.rowCount = rowCount;
78     }
79 
80     public int getColCoun() {
81         return colCoun;
82     }
83 
84     public void setColCoun(int colCoun) {
85         this.colCoun = colCoun;
86     }
87     
88     
89 
90 }

数据集封装

 1 package com.bussiness.test;
 2 
 3 import java.sql.SQLException;
 4 import java.sql.Types;
 5 
 6 import com.dao.db.DBManager;
 7 import com.dao.db.DataTable;
 8 
 9 public class TestBusIness{
10     
11     static String searchSql = "select * from score";
12     static String insertSql = "insert into score(name, age, score)values(?,?,?)";
13     static String deleteSql = "delete from score where id = ?";
14     static String updateSql = "update score set name = ? where id = ?";
15     
16     public static void main(String[] args) {
17         intsertData();
18         searchData();
19     }
20     
21     private static void intsertData()
22     {    
23         DBManager dm = new DBManager();
24         String[] coulmn = new String[]{"wyf2",  "23", "89.5"};
25         int[] type = new int[]{Types.CHAR, Types.INTEGER, Types.DOUBLE};
26         
27         try {
28             boolean flag = dm.updateOrAdd(coulmn, type, insertSql);
29             if(flag)
30                 System.out.println("插入成功");
31         } catch (SQLException e) {
32             e.printStackTrace();
33         }
34     }
35     private static void searchData()
36     {    
37         DBManager dm = new DBManager();
38         String[] coulmn = null;
39         int[] type = null;
40         
41         try {
42             DataTable dt = dm.getResultData(coulmn, type, searchSql);
43             if(dt != null && dt.getRowCount()> 0){            
44                 for(int i = 0; i<dt.getRowCount(); i++)
45                 {
46                     for(int j = 0; j<dt.getColCoun(); j++)
47                     System.out.printf(dt.getRow()[i][j]+"\t");
48                     System.out.println();
49                 }
50             }
51             else
52                 System.out.println("查询失败");
53         } catch (SQLException e) {
54             e.printStackTrace();
55         }
56     }
57 }

测试Demo