首先实现一个DBAccount类，完成数据库连接细节，这里使用的是mysql，你可以自行设计一个输入界面来获取用户输入的数据库连接参数：
class DBAccount{
//数据库路径
String theDBName;
//用户名
String theUserID;
//帐户
String thePasswd;
DBAccount(){
theDBName="jdbc:mysql://localhost/dbname";
theUserID="root";
thePasswd="";
}
/**设置数据库路径*/
public void setDBName(String dbname){
theDBName=dbname;
return;
}
/**设置数据库用户名*/
public void setUserID (String id){
theUserID=id;
return;
}
/**设置数据库用户密码*/
public void setPasswd(String pwd){
thePasswd=pwd;
return;
}
}
然后设计一个面板来显示相应的组件，包括一个下拉列表用于选择数据表，一个文本输入框用于输入待执行的SQL语句，一个Table用于显示查询、选择、管理后的数据集，几个管理数据库的按钮。
/**数据对象基类，负责各数据表的操作抽象*/
class DB extends JPanel implements ActionListener{
//数据库帐户
DBAccount theDBAccount;
//数据库操作类
DBServer theDBServer;
//当前打开数据库的表信息
Vector vectorOfDBTable;
//显示当前数据集数据的表
JTable tableOfDBTable;
QueryTableModel dbTableModel;
JTextArea textareaOfSQL;
JButton buttonOfQueryOk;
JButton buttonOfSetBlank;
JComboBox comboboxOfDBTable;
JButton buttonOfDBTableDataAdd;
JButton buttonOfDBTableDataModify;
JButton buttonOfDBTableDataDelete;
JButton buttonOfDBTableDataRefresh;
//保存当前数据表的列名称
Vector vectorOfTableColumnName=new Vector(0);
//保存当前数据表的数据
Vector vectorOfTableData=new Vector(0);
//DialogOfDataModify dlgOfDataModify;
//放置数据记录添加相关的控件
RecordAddPanel paneOfRecordAdd;
/**构造函数*/
DB(){
theDBAccount=new DBAccount();
//JOptionPane.showMessageDialog(null, UIManager.getLookAndFeel(),"提示",JOptionPane.PLAIN_MESSAGE);
getDBConnect();
getTableData();
setRecordAddPanelContent();
}
/**事件处理*/
public void actionPerformed(ActionEvent e){
//tableOfDBTable.setColumnSelectionAllowed(true);
Object eventSource= e.getSource();
//选择数据表
if(eventSource==comboboxOfDBTable){
getTableData();
setRecordAddPanelContent();
//theDBServer.dbModel.fireTableChanged(null);
//tableOfDBTable.setVisible(false);
//init(vectorOfDBTable);
}
//撤销查询语句
else if(eventSource==buttonOfSetBlank){
textareaOfSQL.setText("");
getTableData();
}
//输入查询语句
else if(eventSource==buttonOfQueryOk){
String QuerySentence=textareaOfSQL.getText();
if (QuerySentence.equals("")){
JOptionPane.showMessageDialog(null,"您没有输入任何查询语句，请检查您的输入！",
"错误",JOptionPane.ERROR_MESSAGE);
return;
}
if(!theDBServer.getQueryData(QuerySentence)){
JOptionPane.showMessageDialog(null,"数据库操作出现错误，请检查您的输入！",
"错误",JOptionPane.ERROR_MESSAGE);
}
//getTableData();
setRecordAddPanelContent();
}
//修改数据表记录
else if(eventSource==buttonOfDBTableDataModify){
dbTableDataModify();
getTableData();
setRecordAddPanelContent();
}
//添加记录
else if(eventSource==buttonOfDBTableDataAdd){
//recordDataAdd(comboboxOfDBTable.getSelectedItem().toString(), );
dbTableDataAdd();
getTableData();
}
//删除记录
else if(eventSource==buttonOfDBTableDataDelete){
dbTableRowDelete();
getTableData();
}
}
/**设置数据字段添加面板的内容*/
public void setRecordAddPanelContent(){
int columnNum=tableOfDBTable.getModel().getColumnCount();
String[] columnName=new String[columnNum];
for (int i=0;i<columnNum;i++){
columnName[i]=tableOfDBTable.getModel().getColumnName(i);
}
paneOfRecordAdd.setVisible(false);
paneOfRecordAdd.setContent(columnName);
}
/**删除数据表记录*/
public void dbTableRowDelete(){
int xOfSelectedData=tableOfDBTable.getSelectedRow();
if (xOfSelectedData>=0){
String tableName=comboboxOfDBTable.getSelectedItem().toString();
String keyWord=tableOfDBTable.getModel().getValueAt(xOfSelectedData,0).toString();
String columnName=tableOfDBTable.getModel().getColumnName(0);
int sure=JOptionPane.showConfirmDialog(null,"您确认删除该记录吗？","确认删除",JOptionPane.YES_NO_CANCEL_OPTION);
System.out.println(sure);
if (sure==0){
theDBServer.tableRowDelete(tableName,columnName,keyWord);
}
}else{
JOptionPane.showMessageDialog(null,
"您没有选中待删除的记录！","错误",JOptionPane.ERROR_MESSAGE);
}
}
/**添加数据库记录*/
public void dbTableDataAdd(){
int length=paneOfRecordAdd.columnName.length;
String tableName=comboboxOfDBTable.getSelectedItem().toString();
String[] recordColumnName=new String[length];
recordColumnName=paneOfRecordAdd.columnName;
String[] recordContent=new String[length];
for (int i=0;i<length;i++){
recordContent[i]=paneOfRecordAdd.columnTextField[i].getText();
}
theDBServer.recordDataAdd(tableName,recordColumnName,recordContent);
}
/**修改数据表记录*/
public void dbTableDataModify(){
//theDBServer.dbModel.setValueAt(1,1,"aa");
int xOfSelectedData=tableOfDBTable.getSelectedRow();
int yOfSelectedData=tableOfDBTable.getSelectedColumn();
System.out.println(xOfSelectedData+"\n");
System.out.println(yOfSelectedData+"\n");
if (xOfSelectedData>=0||yOfSelectedData>=0){
String waitInput=(String)tableOfDBTable.getValueAt(
xOfSelectedData,yOfSelectedData);
System.out.println(waitInput);
String input=(String)JOptionPane.showInputDialog(this,"请键入新的记录字段值：",
"修改",JOptionPane.QUESTION_MESSAGE,null,null,waitInput);
String dbChooserName=comboboxOfDBTable.getSelectedItem().toString();
String coulmnName=theDBServer.dbModel.getColumnName(yOfSelectedData);
String keyCoulmnName=theDBServer.dbModel.getColumnName(0);
String keyWord=tableOfDBTable.getValueAt(xOfSelectedData,0).toString();
System.out.println("列名:"+coulmnName);
System.out.println("主键:"+keyWord);
theDBServer.getDBUpdate(dbChooserName,coulmnName,keyCoulmnName,keyWord,input);
//theDBServer.dbModel.setValueAt(input,xOfSelectedData,yOfSelectedData);//
//.setValueAt(xOfSelectedData,yOfSelectedData,input);
}else{
JOptionPane.showMessageDialog(null,
"您没有选中待修改的字段！","错误",JOptionPane.ERROR_MESSAGE);
}
}
/**获取数据库连接*/
public void getDBConnect(){
theDBServer=new DBServer(theDBAccount);
boolean successed=theDBServer.getDBConnection();
if (!successed){
JOptionPane.showMessageDialog(null,"未能正确连接到数据库！",
"错误",JOptionPane.ERROR_MESSAGE);
//System.exit(0);
}else{
vectorOfDBTable=theDBServer.getTableName();
if(vectorOfDBTable==null){
JOptionPane.showMessageDialog(null,"未能正确连接到数据库！",
"错误",JOptionPane.ERROR_MESSAGE);
//System.exit(0);
}else{
init(vectorOfDBTable);
addComponentListener();
tableOfDBTable.setModel(theDBServer.dbModel);
tableOfDBTable.setCellSelectionEnabled(true);
}
}
}
/**界面初始化*/
public void init(Vector vectorOfDBTable){
setLayout(new BorderLayout());
//各静态控件
JPanel leftPanel;
JPanel rightPanel;
JPanel bottomPanel;
JPanel buttonPanel;
JPanel topPanel;
JPanel topLeftPanel;
JPanel topLeftTopPanel;
JPanel topRightPanel;
JLabel labelOfDBTable;
//左边
leftPanel=new JPanel(new BorderLayout());
JPanel northLeftPanel=new JPanel(new FlowLayout(FlowLayout.LEFT));
JPanel southLeftPanel=new JPanel(new FlowLayout(FlowLayout.CENTER));
JPanel centerLeftPanel=new JPanel(new BorderLayout());
leftPanel.setPreferredSize(new Dimension(250,600));
leftPanel.setBorder(new TitledBorder(new TitledBorder(new EtchedBorder(EtchedBorder.RAISED,
Color.white, new Color(165, 163, 151)), "数据查询")));
centerLeftPanel.setBorder(new LineBorder(new Color(165, 163, 151)));
labelOfDBTable=new JLabel("数据表: ");
comboboxOfDBTable=new JComboBox(vectorOfDBTable);
comboboxOfDBTable.setPreferredSize(new Dimension(170,20));
textareaOfSQL=new JTextArea(1,50);
textareaOfSQL.setLineWrap(true);
textareaOfSQL.setWrapStyleWord(true);
JScrollPane textSQL=new JScrollPane(textareaOfSQL);
textSQL.setHorizontalScrollBar(null);
buttonOfQueryOk=new JButton("提交");
buttonOfSetBlank=new JButton("撤销");
northLeftPanel.add(labelOfDBTable);
northLeftPanel.add(comboboxOfDBTable);
southLeftPanel.add(buttonOfQueryOk);
southLeftPanel.add(buttonOfSetBlank);
centerLeftPanel.add(textSQL);
leftPanel.add(northLeftPanel,"North");
leftPanel.add(centerLeftPanel,"Center");
leftPanel.add(southLeftPanel,"South");
add(leftPanel,"West");
//右边
rightPanel=new JPanel(new BorderLayout());
JPanel centerRightPanel=new JPanel(new BorderLayout());
JPanel southRightPanel=new JPanel(new FlowLayout(FlowLayout.CENTER));
rightPanel.setPreferredSize(new Dimension(550,600));
rightPanel.setBorder(new TitledBorder(new TitledBorder(new EtchedBorder(EtchedBorder.RAISED,
Color.white, new Color(165, 163, 151)), "数据表")));
centerRightPanel.setBorder(new LineBorder(new Color(165, 163, 151)));
//tableOfDBTable=new JTable();
tableOfDBTable=new JTable(theDBServer.dbModel);
JScrollPane scrollpane = new JScrollPane(tableOfDBTable);
paneOfRecordAdd=new RecordAddPanel();
buttonOfDBTableDataAdd=new JButton("添加");
buttonOfDBTableDataModify=new JButton("修改");
buttonOfDBTableDataDelete=new JButton("删除");
//buttonOfDBTableDataRefresh=new JButton("刷新");
centerRightPanel.add(scrollpane,"Center");
centerRightPanel.add(paneOfRecordAdd,"South");
southRightPanel.add(buttonOfDBTableDataAdd);
southRightPanel.add(buttonOfDBTableDataModify);
southRightPanel.add(buttonOfDBTableDataDelete);
//southRightPanel.add(buttonOfDBTableDataRefresh);
rightPanel.add(centerRightPanel,"Center");
rightPanel.add(southRightPanel,"South");
add(rightPanel,"Center");
}
/**添加控件事件监听*/
public void addComponentListener() {
buttonOfQueryOk.addActionListener(this);
buttonOfSetBlank.addActionListener(this);
comboboxOfDBTable.addActionListener(this);
buttonOfDBTableDataAdd.addActionListener(this);
buttonOfDBTableDataModify.addActionListener(this);
buttonOfDBTableDataDelete.addActionListener(this);
//buttonOfDBTableDataRefresh.addActionListener(this);
}
/**获取用户选定数据表的数据*/
public void getTableData(){
String dbChooserName=comboboxOfDBTable.getSelectedItem().toString();
if(!theDBServer.getTableData(dbChooserName)){
JOptionPane.showMessageDialog(null,"数据库操作出现错误，请检查您的输入！",
"错误",JOptionPane.ERROR_MESSAGE);
}
theDBServer.getTableData(dbChooserName);
tableOfDBTable.setModel(theDBServer.dbModel);
}
public void sysExit(){
System.exit(0);
}
}
其中，增加数据表记录的部分单独实现了一个JPanel类来实现，可以动态适应当前数据表记录的列数。
class RecordAddPanel extends JPanel{
String[] columnName;
JLabel[] columnNameLabel;
JTextField[] columnTextField;
JPanel[] layPanel;
RecordAddPanel(){
//setLayout(new FlowLayout(FlowLayout.LEFT));
//setPreferredSize(new Dimension(550,60));
setBorder(new LineBorder(new Color(165, 163, 151)));
//setVisible(true);
}
public void setContent(String[] data){
removeAll();
int layNum;
int length=data.length;
columnName=new String[length];
columnNameLabel=new JLabel[length];
columnTextField=new JTextField[length];
int i;
if (length%3!=0){
layNum=length/3+1;
}else{
layNum=length/3;
}
layPanel=new JPanel[layNum];
setVisible(false);
setLayout(new GridLayout(layNum,1));
setPreferredSize(new Dimension(550,30*layNum));
System.out.println("this:"+layNum);
for (int j=0;j<layNum;j++){
layPanel[j]=new JPanel(new FlowLayout(FlowLayout.LEFT));
for (i=3*j;i<3*(j+1);i++){
if(i<length){
columnName[i]=data[i];
columnNameLabel[i]=new JLabel(columnName[i]);
columnTextField[i]=new JTextField(10);
//System.out.println(columnName[i]);
layPanel[j].add(columnNameLabel[i]);
layPanel[j].add(columnTextField[i]);
}
}
add(layPanel[j]);
}
setVisible(true);
}
public void clearContent(){
removeAll();
setVisible(true);
}
}
最后是和数据库打交道的类了，负责将DB面板上的用户行为转换为实际的数据库操作，并负责刷新用户面板上的数据表源；
class DBServer extends JPanel{
DBAccount theDBAccount;
QueryTableModel dbModel;
String DBPath;
String DBUser;
String UserPasswd;
Statement theStatement;
String ul;
Connection theConnection;
public DBServer(DBAccount dbAccount){
theDBAccount=dbAccount;
}
private void displaySQLErrors(SQLException e) {
JOptionPane.showMessageDialog(this,
"SQLException: " + e.getMessage()+"\n"+
"SQLState: " + e.getSQLState()+"\n"+
"VendorError: " + e.getErrorCode(),
"错误提示",
JOptionPane.WARNING_MESSAGE);
}
public boolean getDBConnection(){
try{
if (theConnection==null||theConnection.isClosed()){
DBPath=theDBAccount.theDBName;
DBUser=theDBAccount.theUserID;
UserPasswd=theDBAccount.thePasswd;
//加载驱动
Class.forName("com.mysql.jdbc.Driver").newInstance();
theConnection=DriverManager.getConnection(DBPath,DBUser,UserPasswd);
theStatement=theConnection.createStatement();
dbModel=new QueryTableModel(this);
return true;
}
return false;
}catch(SQLException e){
JOptionPane.showMessageDialog(this,"连接数据库出错！",
"错误提示",JOptionPane.WARNING_MESSAGE);
return false;
}catch(Exception e){
JOptionPane.showMessageDialog(this,"数据库驱动出错！",
"错误提示",JOptionPane.WARNING_MESSAGE);
return false;
}
}
/**获取数据表信息*/
public Vector getTableName(){
try{
DatabaseMetaData dbmd;
String namestr,typestr,cstr,strn,indexstr,prostr;
Vector vectorOfTableName=new Vector(0);
short data;
int index,pron;
String[] type={"table"};
getDBConnection();
synchronized (theConnection){
dbmd=theConnection.getMetaData();}
// 建立一个结果集
ResultSet rs=dbmd.getTables(null,null,null,type);
// 输出信息
while(rs.next()){
String tempStr=rs.getString("TABLE_NAME");
vectorOfTableName.addElement(tempStr);
//typestr=rs.getString("TABLE_TYPE");
System.out.println(tempStr);
}
theConnection.close();
return vectorOfTableName;
}catch(SQLException e){
displaySQLErrors(e);
return null;
}
}
/**获取表的各列名称
* @param String tableName:选定的表名称
* */
public Vector getTableColumnName(String tableName){
try{
String strOfTableName,typestr,cstr,strn,indexstr,prostr;
short data;
getDBConnection();
DatabaseMetaData dbmd=theConnection.getMetaData();
Vector vectorOfTableColumnName=new Vector(0);
String[] type={"table"};
// 建立一个结果集
ResultSet rs=dbmd.getColumns(null,null,tableName,"%");
// 产生一个对表列的结果集
while(rs.next()){
String temp=rs.getString("COLUMN_NAME");
vectorOfTableColumnName.addElement(rs.getString("COLUMN_NAME"));
}
theConnection.close();
return vectorOfTableColumnName;
}
catch(SQLException e){
displaySQLErrors(e);
return null;
}
}
/**获取选定表的数据
* @param String tableName:选定的表名称
* */
public boolean getTableData(String tableName){
return dbModel.setQuery("select * from "+ tableName);
}
/**获取根据主键值得到的单行数据记录*/
public String[] getSingleRowData(String tablename,String columnname, String keyword){
String[] record;
try{
getDBConnection();
String querySentence="select * from "+tablename+" where "+columnname+" ="+"'"+keyword+"'";
//String querySentence="select * from users";
System.out.println(querySentence);
ResultSet rs=theStatement.executeQuery(querySentence);
ResultSetMetaData rm=rs.getMetaData();
int columnNum=rm.getColumnCount();
record = new String[columnNum];
while(rs.next()){
for (int i=0; i <columnNum; i++) {
//System.out.println(rs.getString(i));
record[i] = rs.getString(i + 1);
System.out.println(record[i]);
}
}
theConnection.close();
}
catch(SQLException e){
displaySQLErrors(e);
return null;
}
return record;
}
/**添加数据表字段记录*/
public void recordDataAdd(String tableName, String[] columnName, String[] data){
getDBConnection();
String updataSentence="INSERT INTO "+tableName + " (";
int length=columnName.length;
for (int i=0;i<length-1;i++){
updataSentence=updataSentence+columnName[i]+", ";
}
updataSentence=updataSentence+columnName[length-1]+") VALUES (";
for (int i=0;i<length-1;i++){
updataSentence=updataSentence+"'"+data[i]+"', ";
}
updataSentence=updataSentence+"'"+data[length-1]+"')";
System.out.println(updataSentence);
try{
theStatement.executeUpdate(updataSentence);
}
catch (SQLException e){
displaySQLErrors(e);
}
}
/**执行查询语句*/
public boolean getQueryData(String QuerySentence){
return dbModel.setQuery(QuerySentence);
}
/**修改字段记录项*/
public void getDBUpdate(String dbChooserName, String coulmnName,
String keyCoulmnName, String keyWord, String input){
getDBConnection();
try{
String a="UPDATE "+dbChooserName+" SET "+coulmnName+" ='"
+input+ "' WHERE "+keyCoulmnName+" ='"+keyWord+"'";
System.out.println(a);
theStatement.executeUpdate(a);
}
catch (SQLException e){
displaySQLErrors(e);
}
}
public void tableRowDelete(String tableName, String columnName, String data){
getDBConnection();
try{
String a="DELETE FROM "+tableName+" WHERE "+columnName+" ='"
+data+ "'";
System.out.println(a);
theStatement.executeUpdate(a);
}
catch (SQLException e){
displaySQLErrors(e);
}
}
public static void main (String args[]){
//DBManager theDBManager=new DBManager();
//theDBManager.conn("jdbc:mysql://localhost/wpss","root","'xh327413'");
}
}
/**查询结果对象的数据表模型对象*/
class QueryTableModel extends AbstractTableModel {
DBServer theDBServer;
//数据缓存
Vector cache;
//列数
int colCount;
//表头
String[] headers;
//声明对象
Statement theStatement;
/**构造函数*/
public QueryTableModel(DBServer dbserver) {
cache = new Vector();
theDBServer=dbserver;
}
public String getColumnName(int i){
return headers[i];
}
public int getColumnCount(){
return colCount;
}
public int getRowCount() {
return cache.size();
}
public Object getValueAt(int row, int col) {
return ((String[])cache.elementAt(row))[col];
}
public void setValueAt(int row, int col, String input) {
((String[])cache.elementAt(row))[col]=input;
}
public boolean setQuery(String query) {
cache = new Vector();
try {
// Execute the query and store the result set and its metadata
theDBServer.getDBConnection();
theStatement=theDBServer.theStatement;
ResultSet rs = theStatement.executeQuery(query);
ResultSetMetaData meta = rs.getMetaData();
colCount = meta.getColumnCount();
// Now we must rebuild the headers array with the new column names
headers = new String[colCount];
for (int h=1; h <= colCount; h++) {
headers[h-1] = meta.getColumnName(h);
}
// and file the cache with the records from our query. This would not be
// practical if we were expecting a few million records in response to our
// query, but we aren't, so we can do this.
while (rs.next()) {
String[] record = new String[colCount];
for (int i=0; i < colCount; i++) {
record[i] = rs.getString(i + 1);
}
cache.addElement(record);
}
fireTableChanged(null);
// notify everyone that we have a new table.
return true;
}
catch(Exception e) {
cache = new Vector(0); // blank it out and keep going.
//e.printStackTrace();
return false;
}
}
}
