java连接mysql底层封装详解

时间:2021-11-24 18:38:25

本文实例为大家分享了java连接mysql底层封装代码,供大家参考,具体内容如下

连接数据库

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

数据层封装

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

数据集封装

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

测试Demo

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

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持服务器之家。

原文链接:https://www.cnblogs.com/FCWORLD/p/3401622.html