Detailed explanation of java connection mysql bottom layer package

This article example shares the java connection mysql bottom layer encapsulation code for your reference, the specific content is as follows

Connect to a database

package com.dao.db;

import java.sql.Connection;
import java.sql.SQLException;

/**
 *  Database connection layer MYSQL
 * @author Administrator
 *
 */
public class DBConnection {
 
 
 /**
  *  Connect to a database 
  * @return
  */
 public static Connection getDBConnection()
 {
  // 1.  Register driver 
  try {
   Class.forName("com.mysql.jdbc.Driver");
  } catch (ClassNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  //  Get the connection to the database 
  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;
 }
 
}

Data layer encapsulation

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 Database underlying encapsulation 
 * @author Administrator
 *
 */
public class DBManager {
 
 private PreparedStatement pstmt;
 private Connection conn;
 private ResultSet rs;
 

 /**
  *  Open the database 
  */
 public DBManager() {
  conn = DBConnection.getDBConnection();
 }
 
 /**
  *  Perform a Modify Add operation 
  * @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;
 }
 /**
  *  Get the query result set 
  * @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();// Take the column name of the database  
  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;
 }
 
 /**
  *  Parameter setting 
  * @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;
 }
 
 /**
  *  Close the database 
  * @throws SQLException
  */
 private void closeDB() throws SQLException
 {
  if(rs != null)
  {
   rs.close();
  }
  if(pstmt != null)
  {
   pstmt.close();
  }
  if(conn != null)
  {
   conn.close();
  }
  
 }
}

Data set encapsulation

package com.dao.db;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

/**
 *  Data set encapsulation 
 * @author Administrator
 *
 */
public class DataTable {
 
 public String[] column;// Column field 
 public String[][] row; // Row value 
 public int rowCount = 0;// Number of rows 
 public int colCoun = 0;// Number of columns 
 
 
 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;
 }
 
 

}

Test 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(" Successful insertion ");
  } 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(" Query failed ");
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }
}