hooyantsing's Blog

第50次课程

字数统计: 2.7k阅读时长: 16 min
2019/07/30

源辰74班

第50次课程

07.30.2019

内容

MIS系统一[廖彦]

f4093bbf54ac48d905ec063fd3d4a979.png

Main Buju (包含布局练习)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
package am.buju;

import org.eclipse.swt.widgets.Display;
import org.eclipse.swt.widgets.Shell;
import org.eclipse.swt.layout.GridLayout;
import org.eclipse.swt.widgets.Table;

import java.sql.Date;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.eclipse.swt.SWT;
import org.eclipse.swt.widgets.Label;
import org.eclipse.swt.widgets.MessageBox;
import org.eclipse.swt.layout.GridData;
import org.eclipse.swt.widgets.Text;
import org.eclipse.swt.widgets.Composite;
import org.eclipse.swt.widgets.Button;
import org.eclipse.swt.events.SelectionAdapter;
import org.eclipse.swt.events.SelectionEvent;
import org.eclipse.swt.layout.FillLayout;
import org.eclipse.swt.widgets.Combo;
import org.eclipse.swt.widgets.TableColumn;
import org.eclipse.swt.widgets.TableItem;
import org.eclipse.swt.widgets.Spinner;
import org.eclipse.swt.widgets.DateTime;

public class Buju {

    protected Shell shell;
    private Label lblNewLabe;
    private Label lblNewLabel_1;
    private Label lblNewLabel_2;
    private Label lblNewLabel_3;
    private Text text;
    private Text text_1;
    private Text text_2;
    private Table table;
    private Composite composite;
    private Button btnNewButton;
    private Button btnNewButton_1;
    private Button btnNewButton_2;
    private Button btnNewButton_3;
    private Button btnNewButton_4;
    private Combo combo;
    private TableColumn tblclmnNewColumn;
    private TableColumn tblclmnNewColumn_1;
    private TableItem tableItem;
    private Label label;
    private Button btnCheckButton;
    private TableColumn tblclmnNewColumn_2;
    private Label label_1;
    private Button button;
    private Label lblNewLabel_4;
    private DateTime dateTime;
    private DateTime dateTime_1;
    /**
     * Launch the application.
     * @param args
     */
    public static void main(String[] args) {
        try {
            Buju window = new Buju();
            window.open();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * Open the window.
     */
    public void open() {
        Display display = Display.getDefault();
        createContents();
        shell.open();
        shell.layout();
        while (!shell.isDisposed()) {
            if (!display.readAndDispatch()) {
                display.sleep();
            }
        }
    }

    /**
     * Create contents of the window.
     */
    protected void createContents() {
        shell = new Shell();
        shell.setSize(780, 630);
        shell.setLayout(new GridLayout(8, false));
        
        lblNewLabe = new Label(shell, SWT.NONE);
        lblNewLabe.setLayoutData(new GridData(SWT.RIGHT, SWT.CENTER, false, false, 1, 1));
        lblNewLabe.setText("\u59D3\u540D");
        
        text = new Text(shell, SWT.BORDER);
        text.setLayoutData(new GridData(SWT.FILL, SWT.CENTER, true, false, 6, 1));
        
        composite = new Composite(shell, SWT.NONE);
        composite.setLayout(new GridLayout(2, false));
        GridData gd_composite = new GridData(SWT.CENTER, SWT.CENTER, false, false, 1, 4);
        gd_composite.widthHint = 174;
        composite.setLayoutData(gd_composite);
        
        btnNewButton = new Button(composite, SWT.NONE);
        
        GridData gd_btnNewButton = new GridData(SWT.LEFT, SWT.CENTER, false, false, 2, 1);
        gd_btnNewButton.widthHint = 165;
        btnNewButton.setLayoutData(gd_btnNewButton);
        btnNewButton.setBounds(0, 0, 80, 27);
        btnNewButton.setText("\u67E5\u8BE2");
        
        btnNewButton_1 = new Button(composite, SWT.NONE);
        btnNewButton_1.setText("New Button");
        
        btnNewButton_2 = new Button(composite, SWT.NONE);
        btnNewButton_2.addSelectionListener(new SelectionAdapter() {
            @SuppressWarnings("unchecked")
            @Override
            public void widgetSelected(SelectionEvent e) {
                if(table.getSelectionCount()>0) {
                    //确认用户有选中表格的某一行,从tableitem中获取打他
                    TableItem[] items = table.getSelection();
                    TableItem item = items[0];
                    Map<String,Object> row = (Map<String, Object>) item.getData();
                    //打开编辑窗口,将data传入窗口
                    new EmpWin(shell,SWT.NONE,row).open();
                    
                    //未完待续...
                    
                    //窗口中完成编辑,并保存到数据库,退出之后
                    //如果保存成功,则刷新表格,执行查询方法
                    //如果取消保存,则什么都不做
                } else {
                    MessageBox mb = new MessageBox(shell);
                    mb.setText("系统提示");
                    mb.setMessage("请选择要修改的记录");
                    mb.open();
                }
            }
        });
        btnNewButton_2.setLayoutData(new GridData(SWT.FILL, SWT.CENTER, false, false, 1, 1));
        btnNewButton_2.setText("\u4FEE\u6539");
        
        btnNewButton_3 = new Button(composite, SWT.NONE);
        btnNewButton_3.setText("New Button");
        
        btnNewButton_4 = new Button(composite, SWT.NONE);
        btnNewButton_4.setText("New Button");
        
        lblNewLabel_1 = new Label(shell, SWT.NONE);
        lblNewLabel_1.setLayoutData(new GridData(SWT.RIGHT, SWT.CENTER, false, false, 1, 1));
        lblNewLabel_1.setText("New Label");
        
        text_1 = new Text(shell, SWT.BORDER);
        text_1.setLayoutData(new GridData(SWT.FILL, SWT.CENTER, true, false, 6, 1));
        
        lblNewLabel_2 = new Label(shell, SWT.NONE);
        lblNewLabel_2.setLayoutData(new GridData(SWT.RIGHT, SWT.CENTER, false, false, 1, 1));
        lblNewLabel_2.setText("New Label");
        
        text_2 = new Text(shell, SWT.BORDER);
        text_2.setLayoutData(new GridData(SWT.FILL, SWT.CENTER, true, false, 6, 1));
        
        lblNewLabel_3 = new Label(shell, SWT.NONE);
        lblNewLabel_3.setLayoutData(new GridData(SWT.RIGHT, SWT.CENTER, false, false, 1, 1));
        lblNewLabel_3.setText("\u90E8\u95E8");
        
        String sql = "select * from dept";
        List<Map<String,Object>> ret = DBhelper.query(sql);
        String[] items = new String[ret.size()];
        for(int i=0;i<ret.size();i++) {
            Map<String,Object> row = ret.get(i);
            items[i] = row.get("DNAME")+"";
        }
        combo = new Combo(shell, SWT.NONE);
        combo.setItems(items);
        combo.setLayoutData(new GridData(SWT.FILL, SWT.CENTER, true, false, 6, 1));
        //将数据库的查询结果保存到combo中
        combo.setData(ret);;
        
        label = new Label(shell, SWT.NONE);
        label.setText("\u5F00\u59CB\u65E5\u671F\uFF1A");
        
        dateTime = new DateTime(shell, SWT.BORDER);
        
        btnCheckButton = new Button(shell, SWT.CHECK);
        btnCheckButton.addSelectionListener(new SelectionAdapter() {
            @Override
            public void widgetSelected(SelectionEvent e) {
            }
        });
        
        lblNewLabel_4 = new Label(shell, SWT.NONE);
        
        label_1 = new Label(shell, SWT.NONE);
        label_1.setText("                  \u622A\u6B62\u65E5\u671F\uFF1A");
        
        dateTime_1 = new DateTime(shell, SWT.BORDER);
        
        button = new Button(shell, SWT.CHECK);
        new Label(shell, SWT.NONE);
        
        table = new Table(shell, SWT.BORDER | SWT.FULL_SELECTION);
        table.setLayoutData(new GridData(SWT.FILL, SWT.FILL, true, true, 8, 2));
        table.setHeaderVisible(true);
        table.setLinesVisible(true);
        
        tblclmnNewColumn = new TableColumn(table, SWT.NONE);
        tblclmnNewColumn.setWidth(100);
        tblclmnNewColumn.setText("\u7F16\u53F7");
        
        tblclmnNewColumn_1 = new TableColumn(table, SWT.NONE);
        tblclmnNewColumn_1.setWidth(100);
        tblclmnNewColumn_1.setText("\u59D3\u540D");
        
        tblclmnNewColumn_2 = new TableColumn(table, SWT.NONE);
        tblclmnNewColumn_2.setWidth(100);
        tblclmnNewColumn_2.setText("New Column");
        
        btnNewButton.addSelectionListener(new SelectionAdapter() {
            @Override
            public void widgetSelected(SelectionEvent e) {
                String sql = "select * from emp where 1=1";
                List<Object> paramList = new ArrayList<>();
                //排除空
                if(text.getText().trim().length()>0) {
                    sql += " and ename like ?";
                    paramList.add("%"+text.getText()+"%");
                }
                if(combo.getText().trim().length()>0){
                    sql += " and deptno = ?";
                    @SuppressWarnings("unchecked")
                    List<Map<String,Object>> list = (List<Map<String, Object>>) combo.getData();
                    //获取下拉列表的选择中项索引
                    int index = combo.getSelectionIndex();
                    paramList.add(list.get(index).get("DEPTNO"));
                }
                /**
                 * 加入日期
                 */
                if(btnCheckButton.getSelection()) {
                    int y = dateTime.getYear();
                    int m = dateTime.getMonth() + 1;
                    int d = dateTime.getDay();
                    sql += " and hiredate >= ?";
                    //构建用于数据库的Date对象    java.sql.Date
                    Date date = Date.valueOf(y+"-"+m+"-"+d);
                    paramList.add(date);
                }
                if(button.getSelection()) {
                    int y = dateTime_1.getYear();
                    int m = dateTime_1.getMonth() + 1;
                    int d = dateTime_1.getDay();
                    sql += " and hiredate <= ?";
                    //构建用于数据库的Date对象    java.sql.Date
                    Date date = Date.valueOf(y+"-"+m+"-"+d);
                    paramList.add(date);
                }
                
                
                
                
                List<Map<String,Object>> ret = DBhelper.query(sql, paramList.toArray());
                table.removeAll();
                for(Map<String,Object> row:ret) {
                    tableItem = new TableItem(table,SWT.NONE);
                    tableItem.setText(new String[] {
                            ""+row.get("EMPNO"),
                            ""+row.get("ENAME"),
                            (""+row.get("HIREDATE")).substring(0, 10)
                    });
                    //将获取的数据放入tableItem中
                    tableItem.setData(row);
                }
            }
        });
    }
}

Class EmpWin

Dialog

35050e99ac7af97e0c44bc310f1239c9.png

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
package am.buju;

import org.eclipse.swt.widgets.Dialog;
import org.eclipse.swt.widgets.Display;
import org.eclipse.swt.widgets.Shell;
import org.eclipse.swt.widgets.Label;

import java.util.Map;

import org.eclipse.swt.SWT;
import org.eclipse.swt.widgets.Text;
import org.eclipse.swt.widgets.Combo;
import org.eclipse.swt.widgets.Spinner;
import org.eclipse.swt.widgets.Button;

public class EmpWin extends Dialog {

    protected Object result;
    protected Shell shell;
    private Text text;
    private Map<String,Object> row;

    /**
     * Create the dialog.
     * @param parent
     * @param style
     */
    public EmpWin(Shell parent, int style,Map<String,Object> row) {
        super(parent, style);
        setText("SWT Dialog");
        this.row = row;
    }

    /**
     * Open the dialog.
     * @return the result
     */
    public Object open() {
        createContents();
        shell.open();
        shell.layout();
        Display display = getParent().getDisplay();
        while (!shell.isDisposed()) {
            if (!display.readAndDispatch()) {
                display.sleep();
            }
        }
        return result;
    }

    /**
     * Create contents of the dialog.
     */
    private void createContents() {
        shell = new Shell(getParent(), getStyle());
        shell.setSize(334, 458);
        shell.setText(getText());
        
        Label lblNewLabel = new Label(shell, SWT.NONE);
        lblNewLabel.setBounds(55, 55, 61, 17);
        lblNewLabel.setText("\u59D3\u540D");
        
        text = new Text(shell, SWT.BORDER);
        text.setBounds(122, 52, 152, 23);
        
        Label label = new Label(shell, SWT.NONE);
        label.setBounds(55, 107, 61, 17);
        label.setText("\u804C\u4F4D");
        
        Combo combo = new Combo(shell, SWT.NONE);
        combo.setBounds(122, 104, 152, 25);
        
        Label label_1 = new Label(shell, SWT.NONE);
        label_1.setBounds(55, 156, 61, 17);
        label_1.setText("\u9886\u5BFC");
        
        Combo combo_1 = new Combo(shell, SWT.NONE);
        combo_1.setBounds(122, 153, 152, 25);
        
        Label label_2 = new Label(shell, SWT.NONE);
        label_2.setBounds(55, 202, 61, 17);
        label_2.setText("\u90E8\u95E8");
        
        Combo combo_2 = new Combo(shell, SWT.NONE);
        combo_2.setBounds(122, 199, 152, 25);
        
        Label label_3 = new Label(shell, SWT.NONE);
        label_3.setBounds(55, 251, 61, 17);
        label_3.setText("\u85AA\u8D44");
        
        Spinner spinner = new Spinner(shell, SWT.BORDER);
        spinner.setBounds(122, 248, 76, 23);
        
        Label label_4 = new Label(shell, SWT.NONE);
        label_4.setBounds(55, 296, 61, 17);
        label_4.setText("\u5956\u91D1");
        
        Spinner spinner_1 = new Spinner(shell, SWT.BORDER);
        spinner_1.setBounds(122, 293, 76, 23);
        
        Button button = new Button(shell, SWT.NONE);
        button.setBounds(66, 355, 80, 27);
        button.setText("\u4FDD\u5B58");
        
        Button button_1 = new Button(shell, SWT.NONE);
        button_1.setBounds(176, 355, 80, 27);
        button_1.setText("\u53D6\u6D88");
        
        //给下拉列表添加选项
        SwtHelper.addComboItem(combo, "JOB","select distinct JOB from emp");
        SwtHelper.addComboItem(combo_1, "ENAME","select * from emp");
        SwtHelper.addComboItem(combo_2, "DNAME","select * from dept");
        
        text.setText(""+row.get("ENAME"));
        
        SwtHelper.selectComboItem(combo, "JOB", row.get("JOB"));
        SwtHelper.selectComboItem(combo_1, "EMPNO", row.get("MGR"));
        SwtHelper.selectComboItem(combo_2, "DEPTNO", row.get("DEPTNO"));
    }
}

以下工具类

Class DBhelper

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
package am.buju;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

public class DBhelper {
    
    private static String url;
    private static String username;
    private static String password;
    
    //驱动加载
    static {
        try {
            Properties p = new Properties();
            //读取配置文件,转成输入流
            InputStream in = DBhelper.class.getClassLoader().getResourceAsStream("db.properties");
            p.load(in);
            String driver = p.getProperty("driver");
            url = p.getProperty("url");
            username = p.getProperty("username");
            password = p.getProperty("password");
            Class.forName(driver);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    

    public static void main(String[] args) {
        //String sql = "insert into dept values(?,?,?)";
        //update(sql,11,"财务","衡阳");
    }
    
    /**
     * ...可变参数数组
     */
    //增删改
    public static int update(String sql,Object...paramArray) {
        Connection conn = null;
        try {
            conn = openConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            for(int i=0;i<paramArray.length;i++) {
                ps.setObject(i+1, paramArray[i]);
            }
            return ps.executeUpdate();
        } catch(Exception e){
            //异常转型
            throw new RuntimeException(e);
        } finally {
            try {
                if(conn!=null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    
    //连接
    public static Connection openConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }
    //查找
    public static List<Map<String,Object>> query(String sql,Object...paramArray) {
        Connection conn = null;
        try {
            conn = openConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            for(int i=0;i<paramArray.length;i++) {
                ps.setObject(i+1, paramArray[i]);
            }
            ResultSet rs = ps.executeQuery();
            //获取元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            List<Map<String,Object>> ret = new ArrayList<>();
            while(rs.next()) {
                Map<String,Object> row = new LinkedHashMap<>();
                for(int i=1;i<=rsmd.getColumnCount();i++) {
                    String columnName = rsmd.getColumnName(i);
                    Object columnValue = rs.getObject(i);
                    row.put(columnName, columnValue);
                }
                ret.add(row);
            }
            return ret;
        } catch(Exception e){
            //异常转型
            throw new RuntimeException(e);
        } finally {
            try {
                if(conn!=null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    
    /**
     * 查询一条记录(例如根据主键查询记录)
     */
    public static Map<String,Object> queryOne (String sql,Object...paramArray){
        Connection conn = null;
        try {
            conn = openConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            for(int i=0;i<paramArray.length;i++) {
                ps.setObject(i+1, paramArray[i]);
            }
            ResultSet rs = ps.executeQuery();
            //获取元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            if(rs.next()) {
                Map<String,Object> row = new LinkedHashMap<>();
                for(int i=1;i<rsmd.getColumnCount();i++) {
                    //获取列名
                    String columnName = rsmd.getColumnName(i);
                    Object columnValue = rs.getObject(i);
                    row.put(columnName, columnValue);
                }
                return row;
            }
            return null;
        } catch(Exception e){
            //异常转型
            throw new RuntimeException(e);
        } finally {
            try {
                if(conn!=null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    
    //分页查询
    public static List<Map<String,Object>> queryPage (
            String sql,int page,int rows,Object...paramArray){
        int startrow = (page - 1) * rows;
        int endrow = page * rows;
        sql = "select t.*\n" +
              " from ( select t.*,rownum rn from ( "+sql+") t where rownum <= ?) t\n" +
              " where t.rn > ?";    
        //生成新的查询数组
        Object[] newParamArray = new Object[paramArray.length + 2];
        System.arraycopy(paramArray, 0, newParamArray, 0, paramArray.length);
        newParamArray[newParamArray.length - 2] = endrow;
        newParamArray[newParamArray.length - 1] = startrow;

        return query(sql,newParamArray);
    }
    
    /**
     * 从查询结果中获取一个值,返回第一个行第一列的值
     */
    public static Object getValue(String sql,Object...paramArray) {
        Map<String,Object> row = queryOne(sql,paramArray);
        if(row==null) {
            return null;
        }else {
            for(Map.Entry<String, Object> e:row.entrySet()) {
                return e.getValue();
            }
        }
        return null;
    }
}

Class SwtHelper

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
package am.buju;
import java.util.List;
import java.util.Map;
import org.eclipse.swt.widgets.Combo;
public class SwtHelper {
    public static void addComboItem(Combo  combo,String column,String  sql,Object...params) {
       List<Map<String,Object>> ret =  DBhelper.query(sql, params);
       String[] items = new  String[ret.size()];
       for(int i=0;i<ret.size();i++) {
           items[i] =  ""+ret.get(i).get(column);
       }
       combo.setItems(items);
       combo.setData(ret);
    }
    
    public static void selectComboItem(Combo  combo,String column,Object value) {
       @SuppressWarnings("unchecked")
       List<Map<String,Object>> ret =  (List<Map<String, Object>>) combo.getData();
       for(int  index=0;index<ret.size();index++) {
           if(ret.get(index).get(column).equals(value))  {
               combo.select(index);
               break;
           }
       }
    }
}
CATALOG