java连接mysql底层封装详解

本文实例为大家分享了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();
 }
}
}

来源：https://www.cnblogs.com/FCWORLD/p/3401622.html