老师要求将数据库操作封装成一个类
简单写了一个，感觉用处不大，单纯为了考试
考完试后更：这个我记得有点BUG
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<String> 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<String> colparas = new Vector<String>();
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;
}
}
}