java sql数据库操作类_Java 学习 - 写了个简单的数据库操作类

老师要求将数据库操作封装成一个类

简单写了一个，感觉用处不大，单纯为了考试

package gui.zyl;

import java.sql.*;

import java.util.ArrayList;

import java.util.Vector;

import java.awt.*;

import javax.swing.*;

import javax.swing.table.DefaultTableModel;

import javax.swing.table.TableModel;

public class DButil {

private boolean ConnectStatus = false;

private Connection conn = null;

public DButil() {}

public boolean getConnectStatus() {

return ConnectStatus;

}

public void connectDB(String dbName,String user,String password) {

/*

* Connect to database

* Sample Parameters: "myshop","root",""

*/

try {

if(conn != null && !conn.isClosed()) {

conn.close();

}

conn = DriverManager.getConnection("jdbc:mysql://localhost/"+dbName+"?serverTimezone=GMT%2B8&characterEncoding=utf8",user, password);

ConnectStatus = true;

} catch (SQLException e) {

JOptionPane.showConfirmDialog(null,"数据库连接建立失败,程序即将关闭","系统消息",JOptionPane.CLOSED_OPTION);

e.printStackTrace();

System.exit(0);

}

}

public void disconnectDB() {

/*

* Disconnect database

*/

try {

if(ConnectStatus == false) {

JOptionPane.showConfirmDialog(null,"数据库连接尚未建立","系统消息",JOptionPane.CLOSED_OPTION);

}else {

ConnectStatus = false;

conn.close();

}

} catch (SQLException e) {

JOptionPane.showConfirmDialog(null,"数据库连接关闭失败","系统消息",JOptionPane.CLOSED_OPTION);

e.printStackTrace();

}

}

public ResultSet queryAll(String tableName) throws SQLException{

/*

* query all the rows in table 'tableName'

* if query succeed,return a ResultSet

* return null otherwise

*/

if(ConnectStatus == false) {

JOptionPane.showConfirmDialog(null,"数据库连接尚未建立","系统消息",JOptionPane.CLOSED_OPTION);

return null;

}else {

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("select * from " + tableName);

return rs;

}

}

public ResultSet queryAll(String tableName,String booleanExpression) throws SQLException {

/*

* query all the rows in 'tableName' that make 'booleanExpression' true

* if query succeed,return a ResultSet

* return null otherwise

*

* Sample Parameters: "items","price > 10" / "items","id like '%001%'"

*/

if(ConnectStatus == false) {

JOptionPane.showConfirmDialog(null,"数据库连接尚未建立","系统消息",JOptionPane.CLOSED_OPTION);

return null;

}else {

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("select * from " + tableName + " where " + booleanExpression);

return rs;

}

}

public ResultSet queryByCol(String tableName,String colNames) throws SQLException {

/*

* query selected columns in 'tableName'

* if query succeed,return a ResultSet

* return null otherwise

*

* Sample Parameters:"items","id" / "items","id,name,price"

*/

if(ConnectStatus == false) {

JOptionPane.showConfirmDialog(null,"数据库连接尚未建立","系统消息",JOptionPane.CLOSED_OPTION);

return null;

}else {

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("select " + colNames + " from " + tableName);

return rs;

}

}

public ResultSet queryByCol(String tableName,String colNames,String booleanExpression) throws SQLException {

/*

* query selected columns in 'tableName' that make 'booleanExpression' true

* if query succeed,return a ResultSet

* return null otherwise

*

* Sample Parameters:"items","id" / "items","id,name,price"

*/

if(ConnectStatus == false) {

JOptionPane.showConfirmDialog(null,"数据库连接尚未建立","系统消息",JOptionPane.CLOSED_OPTION);

return null;

}else {

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("select " + colNames + " from " + tableName + " where " + booleanExpression);

return rs;

}

}

public int insertRow(String tableName,String ... args) throws SQLException {

/*

* insert a row into 'tableName'

* if insert succeed,return 1;if insert failed,return 0

* return -1 otherwise(for example,sql synatx error)

*

* Sample Parameters:"items","'001'" / "items","'001'","'car'","12.8"

*

*/

if(ConnectStatus == false) {

JOptionPane.showConfirmDialog(null,"数据库连接尚未建立","系统消息",JOptionPane.CLOSED_OPTION);

return -1;

}else {

int first = 1;

String para = "";

for(String arg : args) {

if(first == 1) first = 0; else para += ",";

para += arg;

}

Statement stmt = conn.createStatement();

int affected = stmt.executeUpdate("insert into " + tableName + " values(" + para + ")");

return affected;

}

}

public int setRow(String tableName,String updateInfo) throws SQLException {

/*

* update all rows in 'tableName'

* if update succeed,return 1;if update failed,return 0

* return -1 otherwise(for example,sql synatx error)

*

* Sample Parameters:"items","set id = '1'" / "items","set id='1',price=10"

*

*/

if(ConnectStatus == false) {

JOptionPane.showConfirmDialog(null,"数据库连接尚未建立","系统消息",JOptionPane.CLOSED_OPTION);

return -1;

}else {

Statement stmt = conn.createStatement();

int affected = stmt.executeUpdate("update " + tableName +" set " + updateInfo);

return affected;

}

}

public int setRow(String tableName,String updateInfo,String booleanExpression) throws SQLException {

/*

* update all rows in 'tableName'

* if update succeed,return 1;if update failed,return 0

* return -1 otherwise(for example,sql synatx error)

*

* Sample Parameters:"items","set id = '1'","price < 10"/ "items","set id='1',price=10","price = 100"

*

*/

if(ConnectStatus == false) {

JOptionPane.showConfirmDialog(null,"数据库连接尚未建立","系统消息",JOptionPane.CLOSED_OPTION);

return -1;

}else {

Statement stmt = conn.createStatement();

int affected = stmt.executeUpdate("update " + tableName +" set " + updateInfo + " where " + booleanExpression);

return affected;

}

}

public TableModel createTableModel(ResultSet rs,ArrayList colNames) {

/*

* write data to a tablemodel

* usage:

* DButil dbu = new DButil();

* dbu.connectDB(...);

* ResultSet rs = dbu.queryAll(...);

* TabelModel tb = rs.createTableModel(rs,colNames);

*/

try {

Vector colparas = new Vector();

for(String s:colNames) {

colparas.add(s);

}

DefaultTableModel df = new DefaultTableModel(colparas,0);

while(rs.next()) {

String[] line = {

rs.getString(1),

rs.getString(2),

String.valueOf(rs.getInt(3)),

String.valueOf(rs.getInt(4)),

String.valueOf(rs.getInt(5))

};

df.addRow(line);

}

return df;

} catch (SQLException e) {

JOptionPane.showConfirmDialog(null,"发生意外错误","系统消息",JOptionPane.CLOSED_OPTION);

e.printStackTrace();

return null;

}

}

}