任务1到任务4都有答案，整个实验难点在于任务5，所以这篇随笔只分享任务5的解题思路
任务5 模仿下面截图设计并实现向表TEMPL插入行的操作，GUI界面要求实现单行插入、多行插入、通过子查询插入的功能。
1.GUI界面
编写GUI界面过程中脑袋要有程序的框架，每个人的GUI风格不同，框架也不同，我的框架如下
class Table implements ActionListener
{
//申明图形界面组件
private JButton btn1;
private JButton btn2;
......
//初始化表格
public Table()
{
//制作图形界面
}
//实现每个btn的功能
public btn1Fun()
{
//JDBC语句
}
public btn2Fun()
{
//JDBC语句
}
......
//监听器处理函数
public void actionPerformed(ActionEvent e)
{
if(e.getSource()==this.btn1)
{
this.btn1Fun();
}
if(e.getSource()==this.btn2)
{
this.btn2Fun();
}
......
}
}
public class Test5
{
public static void main(String args[])
{
new Table() ;
}
}
2.单行插入
图形界面可以用多行标签+文本框的形式，如下图
也可以用一行表格的形式，这个可以参考多行插入
sql语句为不带参数标记的insert，可以用statement对象执行语句
3.多行插入
图形界面用多行表格的形式，如下图
sql语句为带参数标记的insert，用preparedStament对象执行语句，
因为是插入多行，可以用for循环，即从表格中提取一行，然后执行一条sql语句
也可以用批处理pstmt.addBatch()，最后一起执行
4.子查询插入
子查询查询的是哪个表？插入的又是哪个表？
查询的当然是employee表
因为只是查询并不修改，而同一个表中主键empno是不能相同的，所以不可能把查询出来的结果插回原来的表中
所以只能插入一个新表，那什么表和employee表有相同结构呢？想起老师让我们创建一个employee的副本templ的作用了么？就是用在这里的
图形界面如下
sql语句是带参数标记的insert，用preparedStament对象执行语句
这是整个程序
import javax.swing.*;
import javax.swing.table.*;
import java.awt.*;
import java.awt.event.*;
import java.util.*;
import java.sql.*;
class Table implements ActionListener
{
private JFrame frame = new JFrame("打开表——TEMPL") ;
private JTabbedPane tab = new JTabbedPane(JTabbedPane.TOP);//设置标签在顶部显示
private String[] titles = {"EMPNO", "FIRSTNME","MIDINIT","LASTNAME","WORKDEPT","PHONENO",
"HIREDATE","JOB","EDLEVEL","SEX","BIRTHDATE","SALARY","BONUS","COMM"} ;
private Object [][] employeeInfo = {}; // 定义数据
private JTable table = null;//显示数据的表格
private JTable tableInsert = null;//用于插入多行数据
private DefaultTableModel tableModel = null; //TableModel
private DefaultTableModel tableModelInser = null; //TableModel
private TableColumn column;
/*插入一行的的输入框*/
private JTextField textFile1 = new JTextField(20);
private JTextField textFile2 = new JTextField(20);
private JTextField textFile3 = new JTextField(20);
private JTextField textFile4 = new JTextField(20);
private JTextField textFile5 = new JTextField(20);
private JTextField textFile6 = new JTextField(20);
private JTextField textFile7 = new JTextField(20);
private JTextField textFile8 = new JTextField(20);
private JTextField textFile9 = new JTextField(20);
private JTextField textFile10 = new JTextField(20);
private JTextField textFile11 = new JTextField(20);
private JTextField textFile12 = new JTextField(20);
private JTextField textFile13 = new JTextField(20);
private JTextField textFile14 = new JTextField(20);
//进行更新的输入框
private JTextField updateTextFile1 = new JTextField(20);
private JTextField updateTextFile2 = new JTextField(20);
private JTextField updateTextFile3 = new JTextField(20);
private JTextField updateTextFile4 = new JTextField(20);
/*插入一行的标签*/
private JLabel label1 = new JLabel("EMPNO");
private JLabel label2 = new JLabel("FIRSTNME");
private JLabel label3 = new JLabel("MIDINIT");
private JLabel label4 = new JLabel("LASTNAME");
private JLabel label5 = new JLabel("WORKDEPT");
private JLabel label6 = new JLabel("PHONENO");
private JLabel label7 = new JLabel("HIREDATE");
private JLabel label8 = new JLabel("JOB");
private JLabel label9 = new JLabel("EDLEVEL");
private JLabel label10 = new JLabel("SEX");
private JLabel label11 = new JLabel("BIRTHDATE");
private JLabel label12 = new JLabel("SALARY");
private JLabel label13 = new JLabel("BONUS");
private JLabel label14 = new JLabel("COMM");
//进行更新的标签
private JLabel updateLabel1 = new JLabel("你想查询的列");
private JLabel updateLabel2 = new JLabel("你想查询的值");
private JButton refreshBtn = new JButton("刷新表格") ;; //刷新表格按钮
private JButton insertRowBtn = new JButton("插入一行") ;
private JButton addRowBtn = new JButton("增加行") ;
private JButton removeRowBtn = new JButton("删除行") ;
private JButton insertMulRowBtn = new JButton("插入多行") ;
private JButton updateBtn = new JButton("更新") ;
//初始化表格
public Table()
{
this.tableModel = new DefaultTableModel(this.employeeInfo,this.titles) ;//显示数据的表格
this.table = new JTable(this.tableModel) ;
JScrollPane scr1 = new JScrollPane(this.table,ScrollPaneConstants.VERTICAL_SCROLLBAR_ALWAYS,ScrollPaneConstants.HORIZONTAL_SCROLLBAR_ALWAYS) ;
this.tableModelInser = new DefaultTableModel(this.employeeInfo,this.titles) ;//显示数据的表格
this.tableInsert = new JTable(this.tableModelInser) ;
JScrollPane scr1Insert = new JScrollPane(this.tableInsert,ScrollPaneConstants.VERTICAL_SCROLLBAR_ALWAYS,ScrollPaneConstants.HORIZONTAL_SCROLLBAR_ALWAYS) ;
JPanel pan1 = new JPanel() ;
JPanel pan2 = new JPanel() ;
JPanel pan3 = new JPanel() ;
JPanel pan4 = new JPanel() ;
pan1.add(scr1) ;
pan1.add(refreshBtn) ;
pan2.add(label1) ;
pan2.add(textFile1) ;
pan2.add(label2) ;
pan2.add(textFile2) ;
pan2.add(label3) ;
pan2.add(textFile3) ;
pan2.add(label4) ;
pan2.add(textFile4) ;
pan2.add(label5) ;
pan2.add(textFile5) ;
pan2.add(label6) ;
pan2.add(textFile6) ;
pan2.add(label7) ;
pan2.add(textFile7) ;
pan2.add(label8) ;
pan2.add(textFile8) ;
pan2.add(label9) ;
pan2.add(textFile9) ;
pan2.add(label10) ;
pan2.add(textFile10) ;
pan2.add(label11) ;
pan2.add(textFile11) ;
pan2.add(label12) ;
pan2.add(textFile12) ;
pan2.add(label13) ;
pan2.add(textFile13) ;
pan2.add(label14) ;
pan2.add(textFile14) ;
pan2.add(insertRowBtn) ;
pan3.add(scr1Insert);
pan3.add(addRowBtn);
pan3.add(removeRowBtn);
pan4.add(updateLabel1);
pan4.add(updateTextFile1);
pan4.add(updateLabel2);
pan4.add(updateTextFile2);
pan4.add(updateBtn);
tab.addTab("数据库",pan1) ;
tab.addTab("插入一行",pan2) ;
tab.addTab("插入多行",pan3) ;
tab.addTab("子查询插入",pan4) ;
frame.add(tab) ;
frame.setSize(1000,600) ;// 根据组件自动调整大小
frame.setLocation(300,200) ;
frame.setVisible(true) ;
frame.addWindowListener(new WindowAdapter()
{
public void windowClosing(WindowEvent e)
{
System.exit(1) ;
}
}) ;
this.refreshBtn.addActionListener(this) ;
this.insertRowBtn.addActionListener(this) ;
this.addRowBtn.addActionListener(this) ;
this.removeRowBtn.addActionListener(this) ;
this.insertMulRowBtn.addActionListener(this) ;
this.updateBtn.addActionListener(this) ;
}
/*显示表格的函数*/
public void refreshFun()
{
while (tableModel.getRowCount()>0)
{
tableModel.removeRow(0);
}
try
{
Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver");
//注册
Connection conn = DriverManager.getConnection("JDBC:DB2:SAMPLE","db2admin","db2admin");
String sql = "SELECT EMPNO, FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO,HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,COMM FROM TEMPL" ;
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery(sql);
while (rs.next())
{
Object obj1 = rs.getString(1);
Object obj2 = rs.getString(2);
Object obj3 = rs.getString(3);
Object obj4 = rs.getString(4);
Object obj5 = rs.getString(5);
Object obj6 = rs.getString(6);
Object obj7 = rs.getString(7);
Object obj8 = rs.getString(8);
Object obj9 = rs.getString(9);
Object obj10 = rs.getString(10);
Object obj11 = rs.getString(11);
Object obj12 = rs.getString(12);
Object obj13 = rs.getString(13);
Object obj14 = rs.getString(14);
Object obj[] = {obj1,obj2,obj3,obj4,obj5,obj6,obj7,obj8,obj9,obj10,obj11,obj12,obj13,obj14};
this.tableModel.addRow(obj);
}
rs.close();
conn.close();
}
catch (SQLException e)
{
System.out.println("SQLState:" + e.getSQLState());
System.out.println("Message:" + e.getMessage());
System.out.println("Vendor:" + e.getErrorCode());
}
catch (ClassNotFoundException e)
{
System.out.println(e);
}
}
//向数据库插入一行数据
public void insertRowFun()
{
try
{
Object obj1 = textFile1.getText();
Object obj2 = textFile2.getText();
Object obj3 = textFile3.getText();
Object obj4 = textFile4.getText();
Object obj5 = textFile5.getText();
Object obj6 = textFile6.getText();
Object obj7 = textFile7.getText();
Object obj8 = textFile8.getText();
Object obj9 = textFile9.getText();
Object obj10 = textFile10.getText();
Object obj11 = textFile11.getText();
Object obj12 = textFile12.getText();
Object obj13 = textFile13.getText();
Object obj14 = textFile14.getText();
Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver");
//注册
Connection conn = DriverManager.getConnection("JDBC:DB2:SAMPLE","db2admin","db2admin");
String sql = "INSERT INTO TEMPL(EMPNO, FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO,HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,COMM)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setObject(1,obj1);
pstmt.setObject(2,obj2);
pstmt.setObject(3,obj3);
pstmt.setObject(4,obj4);
pstmt.setObject(5,obj5);
pstmt.setObject(6,obj6);
pstmt.setObject(7,obj7);
pstmt.setObject(8,obj8);
pstmt.setObject(9,obj9);
pstmt.setObject(10,obj10);
pstmt.setObject(11,obj11);
pstmt.setObject(12,obj12);
pstmt.setObject(13,obj13);
pstmt.setObject(14,obj14);
pstmt.executeUpdate();
conn.close();
}
catch (SQLException e)
{
System.out.println("SQLState:" + e.getSQLState());
System.out.println("Message:" + e.getMessage());
System.out.println("Vendor:" + e.getErrorCode());
}
catch (ClassNotFoundException e)
{
System.out.println(e);
}
catch(NumberFormatException e)
{
System.out.println(e);
}
}
//向数据库插入多行数据
public void insertMulRowFun()
{
try
{
Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver");
//注册
Connection conn = DriverManager.getConnection("JDBC:DB2:SAMPLE","db2admin","db2admin");
String sql = "INSERT INTO TEMPL(EMPNO, FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO,HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,COMM)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
for(int i = 0; i < tableModelInser.getRowCount(); i++)
{
Object obj1 = tableModelInser.getValueAt(i,0);
Object obj2 = tableModelInser.getValueAt(i,1);
Object obj3 = tableModelInser.getValueAt(i,2);
Object obj4 = tableModelInser.getValueAt(i,3);
Object obj5 = tableModelInser.getValueAt(i,4);
Object obj6 = tableModelInser.getValueAt(i,5);
Object obj7 = tableModelInser.getValueAt(i,6);
Object obj8 = tableModelInser.getValueAt(i,7);
Object obj9 = tableModelInser.getValueAt(i,8);
Object obj10 = tableModelInser.getValueAt(i,9);
Object obj11 = tableModelInser.getValueAt(i,10);
Object obj12 = tableModelInser.getValueAt(i,11);
Object obj13 = tableModelInser.getValueAt(i,12);
Object obj14 = tableModelInser.getValueAt(i,13);
pstmt.setObject(1,obj1);
pstmt.setObject(2,obj2);
pstmt.setObject(3,obj3);
pstmt.setObject(4,obj4);
pstmt.setObject(5,obj5);
pstmt.setObject(6,obj6);
pstmt.setObject(7,obj7);
pstmt.setObject(8,obj8);
pstmt.setObject(9,obj9);
pstmt.setObject(10,obj10);
pstmt.setObject(11,obj11);
pstmt.setObject(12,obj12);
pstmt.setObject(13,obj13);
pstmt.setObject(14,obj14);
pstmt.executeUpdate();
}
conn.close();
}
catch (SQLException e)
{
System.out.println("SQLState:" + e.getSQLState());
System.out.println("Message:" + e.getMessage());
System.out.println("Vendor:" + e.getErrorCode());
}
catch (ClassNotFoundException e)
{
System.out.println(e);
}
catch(NumberFormatException e)
{
System.out.println(e);
}
}
//子查询插入
public void updateFun()
{
try
{
Object obj1 = updateTextFile1.getText();
Object obj2 = updateTextFile2.getText();
Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver");
//注册
Connection conn = DriverManager.getConnection("JDBC:DB2:SAMPLE","db2admin","db2admin");
String sql = "INSERT INTO EMPLOYEE SELECT * FROM TEMPL WHERE "+obj1+"='"+obj2+"'";
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql);
conn.close();
}
catch (SQLException e)
{
System.out.println("SQLState:" + e.getSQLState());
System.out.println("Message:" + e.getMessage());
System.out.println("Vendor:" + e.getErrorCode());
}
catch (ClassNotFoundException e)
{
System.out.println(e);
}
catch(NumberFormatException e)
{
System.out.println(e);
}
}
//监听器处理函数
public void actionPerformed(ActionEvent e)
{
if(e.getSource()==this.refreshBtn)
{
this.refreshFun();
}
if(e.getSource()==this.insertRowBtn)
{
this.insertRowFun();
}
if(e.getSource()==this.addRowBtn)
{
this.tableModelInser.addRow(new Object[] {});
}
if(e.getSource()==this.removeRowBtn)
{
int rowCount = this.tableModelInser.getRowCount() - 1 ;
if(rowCount>=0)
{ // 判断是否还有行可以删除
this.tableModelInser.removeRow(rowCount) ;
this.tableModelInser.setRowCount(rowCount) ; // 设置新的行数
}
}
if(e.getSource()==this.insertMulRowBtn)
{
this.insertMulRowFun();
}
if(e.getSource()==this.updateBtn)
{
this.updateFun();
}
}
}
public class Test5
{
public static void main(String args[])
{
new Table() ;
}
}
这里是课程的源码
链接：https://pan.baidu.com/s/1JvnTFWfmht149x56qyXqMA
提取码：1234