java连接mysql底层封装
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 }
