java连接mysql底层封装详解

Java  /  管理员 发布于 8年前   295

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

连接数据库

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; } }

数据层封装

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();  }   }}

数据集封装

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

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();  } }}

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