java连接mysql底层封装详解

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>list = new ArrayList>();

if(!setPstmtParam(coulmn, type, sql))

return null;

rs = pstmt.executeQuery();

ResultSetMetaData rsmd = rs.getMetaData();//取数据库的列名

int numberOfColumns = rsmd.getColumnCount();

while(rs.next())

{

HashMap rsTree = new HashMap();

for(int r=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

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

gAsQio {

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> 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> set = list.get(i).entrySet();

int j = 0;

for (Iterator> it = set.iterator(); it

.hasNext();) {

Map.Entry entry = (Map.Entry) 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, searchSgAsQioql);

if(dt != null && dt.getRowCount()> 0){

for(int i = 0; i

{

for(int j = 0; j

System.out.printf(dt.getRow()[i][j]+"\t");

System.out.println();

}

}

else

System.out.println("查询失败");

} catch (SQLException e) {

e.printStackTrace();

}

}

}

{

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

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

gAsQio {

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> 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> set = list.get(i).entrySet();

int j = 0;

for (Iterator> it = set.iterator(); it

.hasNext();) {

Map.Entry entry = (Map.Entry) 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, searchSgAsQioql);

if(dt != null && dt.getRowCount()> 0){

for(int i = 0; i

{

for(int j = 0; j

System.out.printf(dt.getRow()[i][j]+"\t");

System.out.println();

}

}

else

System.out.println("查询失败");

} catch (SQLException e) {

e.printStackTrace();

}

}

}

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

gAsQio {

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> 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> set = list.get(i).entrySet();

int j = 0;

for (Iterator> it = set.iterator(); it

.hasNext();) {

Map.Entry entry = (Map.Entry) 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, searchSgAsQioql);

if(dt != null && dt.getRowCount()> 0){

for(int i = 0; i

{

for(int j = 0; j

System.out.printf(dt.getRow()[i][j]+"\t");

System.out.println();

}

}

else

System.out.println("查询失败");

} catch (SQLException e) {

e.printStackTrace();

}

}

}

{

for(int j = 0; j

System.out.printf(dt.getRow()[i][j]+"\t");

System.out.println();

}

}

else

System.out.println("查询失败");

} catch (SQLException e) {

e.printStackTrace();

}

}

}

System.out.printf(dt.getRow()[i][j]+"\t");

System.out.println();

}

}

else

System.out.println("查询失败");

} catch (SQLException e) {

e.printStackTrace();

}

}

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。