java--第12章数据库编程

Bertha 。 2023-01-04 10:30 108阅读 0赞

# **实验目的：** #

![f03cff38b5ad7766814c0998bc7c8f3c.png][]

1.掌握JDBC编程。

2.熟悉批处理和事务处理。

# **实验内容：** #

1.在Java程序中创建SQL Server数据库和表，并使用批处理和事务处理。

2.设计一个程序，实现对表books的数据处理。

3.运用AWT和Swing开发技术，以图形界面方式操作数据。

**实验步骤：**

1.在Java程序中创建SQL Server数据库bookstore和表books，在表中插入数据并在控制台显示插入的数据。

1）表books的结构如下：

![2021010916234261.png][]

2）在表books中插入以下数据：

1501, 'Java实用教程',43.00

1502, 'JSP网站编程',49.00

1503, 'Struts 2核心编程',58.00

1504, 'Hibernate 必备宝典',89.00

1505, 'C程序设计',35.00

提示：

1）导入要用到的包中的类接口：

import java.sql.Connection;
    
    import java.sql.DriverManager;
    
    import java.sql.ResultSet;
    
    import java.sql.SQLException;
    
    import java.sql.Statement;

2）右键src,在出现的快捷菜单中点击build path，再点击Configure Build Path,添加扩展类sqljdbc4.jar，再点击Apply and Close。

![20210109162342303.png][]

源代码：

package homework.实验12_数据库编程;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    public class sy12_1 {
        public static void main(String[] args) {
            ResultSet rs = null;
            Statement stmt = null;
            Connection conn = null;
            try{
                Class.forName("com.mysql.cj.jdbc.Driver");
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/?characterEncoding=UTF-8","用户名","密码");
                String url = "jdbc:mysql://localhost:3306/check_stand?" +
                        "useUnicode=true&characterEncoding=utf-8&useSSL=false";
    
                conn.setAutoCommit(false);
                //sql语句
                stmt = conn.createStatement();
                stmt.executeUpdate("Create database bookstore");
                stmt.executeUpdate("use bookstore");
                //创建表结构
                stmt.executeUpdate("create table books(id int primary key,title nchar(25),price float)");
                //执行sql语句
                stmt.executeUpdate("insert into books values(1501,'java使用教程',43.00)");
                stmt.executeUpdate("insert into books values(1502,'JSP网络编程',49.008)");
                stmt.executeUpdate("insert into books values(1503,'Struts2核心编程',58.00)");
                stmt.executeUpdate("insert into books values(1504,'Hibernate必备宝典',89.00)");
                stmt.executeUpdate("insert into books values(1505,'C程序设计',35.00)");
                conn.commit();
                rs = stmt.executeQuery("select * from books");
                while(rs.next()){
                    System.out.print(rs.getInt("id"));
                    System.out.print('\t'+rs.getString("title"));
                    System.out.print('\t'+rs.getString("price"));
                    System.out.println();
                }
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
            catch (SQLException e){
                e.printStackTrace();
                try{
                    if(conn != null){
                        conn.rollback();
                        conn.setAutoCommit(true);
                    }
                }
                catch (SQLException e1){
                    e1.printStackTrace();
                }finally{
                    try{
                        if(rs != null){
                            rs.close();
                            rs = null;
                        }if(stmt != null){
                            stmt.close();
                            stmt = null;
                        }if(conn != null){
                            conn.close();
                            conn = null;
                        }
                    } catch (SQLException e2) {
                        e2.printStackTrace();
                    }
                }
            }
        }
    }

运行结果截图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTc2NTQ4_size_16_color_FFFFFF_t_70][]

2. 设计一个程序，实现对表books的数据处理：

1）插入两条数据：

1506,'C\#程序设计',45.00

1507,'ASP.NET程序设计',48.00

2）修改一条数据：

将id为1507的title修改为：ASP.NET案例教程。

3）删除一条数据：

删除价格超过80元的图书。

4）查询表中所有记录显示到控制台。

源代码：

package homework.实验12_数据库编程;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    public class sy12_2 {
        public static void main(String[] args) {
            ResultSet rs = null;
            Statement stmt = null;
            Connection conn = null;
            try{
                Class.forName("com.mysql.cj.jdbc.Driver");
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/?characterEncoding=UTF-8","账户","密码");
                String url = "jdbc:mysql://localhost:3306/check_stand?" +
                        "useUnicode=true&characterEncoding=utf-8&useSSL=false";
    
                conn.setAutoCommit(false);
                //sql语句
                stmt = conn.createStatement();
                stmt.executeUpdate("use bookstore");
                /*
                执行sql语句
                 */
                //插入数据
                stmt.executeUpdate("insert into books values(1506,'C#程序设计',45.00)");
                stmt.executeUpdate("insert into books values(1507,'ASP.NET程序设计',48.00)");
                //修改数据
                stmt.executeUpdate("update books set title='ASP.NET案例分析' where id=1507");
                //删除数据
                stmt.executeUpdate("delete from books where price > 80");
                //conn.commit();
                //查询数据
                rs = stmt.executeQuery("select * from books");
                while(rs.next()){
                    System.out.print(rs.getInt("id"));
                    System.out.print('\t'+rs.getString("title"));
                    System.out.print('\t'+rs.getString("price"));
                    System.out.println();
                }
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
            catch (SQLException e){
                e.printStackTrace();
                try{
                    if(conn != null){
                        conn.rollback();
                        conn.setAutoCommit(true);
                    }
                }
                catch (SQLException e1){
                    e1.printStackTrace();
                }finally{
                    try{
                        if(rs != null){
                            rs.close();
                            rs = null;
                        }if(stmt != null){
                            stmt.close();
                            stmt = null;
                        }if(conn != null){
                            conn.close();
                            conn = null;
                        }
                    } catch (SQLException e2) {
                        e2.printStackTrace();
                    }
                }
            }
        }
    
    }

运行结果截图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTc2NTQ4_size_16_color_FFFFFF_t_70 1][]

3.运用AWT和Swing开发技术，做出一个具有GUI界面的程序，以图形界面方式操作bookstore数据库books表中的图书信息。界面可参考下图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTc2NTQ4_size_16_color_FFFFFF_t_70 2][]

源代码：

package homework.实验12_数据库编程;
    import java.awt.*;
    import java.util.*;
    import java.awt.event.*;
    import javax.swing.*;
    import javax.swing.table.*;
    import java.sql.*;
    public class sy12_3 {
        //主窗口
        JFrame f = new JFrame("图书馆信息操作");
        //书号所在的子面板
        JLabel lId = new JLabel("书号:",JLabel.LEFT);
        JTextField tfId = new JTextField(18);
        //书名所在的子面板
        JPanel jp1 = new JPanel();
        JLabel lTitle = new JLabel("书名:",JLabel.LEFT);
        JTextField tfTitle = new JTextField(18);
        //定价所在的子面板
        JPanel jp2 = new JPanel();
        JLabel lPri = new JLabel("定价:",JLabel.LEFT);
        JTextField tfPri = new JTextField(18);
        //网格布局
        JPanel jp3 = new JPanel();
        JPanel jpl = new JPanel(new GridLayout(3,1,0,2));
    
        /*
        设计表格用于显示的数据
         */
        static String[] field =  {"书号","书名","定价"};
        static Object[][] data;
        static DefaultTableModel mod = new DefaultTableModel(data,field);  //用mod模型来创建表格
        JTable tab = new JTable(mod);  //创建一个滚动容器
        JScrollPane jsp = new JScrollPane();
        /*创建界面上的按钮操作*/
        JButton bIns = new JButton("插入");
        JButton bUpd = new JButton("更新");
        JButton bDel = new JButton("删除");
        JButton bQue = new JButton("查询");
        JPanel jpb = new JPanel(new GridLayout(4,1,0,22));
    
        static ResultSet rs = null;
        static Statement stmt = null;
        static Connection conn = null;
        //初始化中创建数据库连接
        static{
            try{
                Class.forName("com.mysql.cj.jdbc.Driver");
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/?characterEncoding=UTF-8","用户名","密码");
                conn.setAutoCommit(false);
            } catch (ClassNotFoundException | SQLException e) {
                e.printStackTrace();
            }
        }
        /*完成布局 */
        void go(){/**在此方法内实现对整个界面的布局*/
            /*总体布局*/
            f.add("North", jpl);
            f.add("Center", jsp);
            f.add("East", jpb);
            /*图书信息表单栏的布局*/
            jp1.add(lId);
            jp1.add(tfId);
            jp2.add(lTitle);
            jp2.add(tfTitle);
            jp3.add( lPri);
            jp3.add(tfPri);
            jpl.add(jp1);
            jpl.add(jp2);
            jpl.add(jp3);
            /*放置数据表格组件*/
            jsp.getViewport().add(tab);
            /*按钮布局*/
            jpb.add(bIns);
            jpb.add(bUpd);
            jpb.add(bDel);
            jpb.add(bQue);
            /*注册按钮事件监听器*/
            bIns.addActionListener(new ButtonH(1));
            bUpd .addActionListener(new ButtonH(2));
            bDel.addActionListener(new ButtonH(3));
            bQue.addActionListener(new ButtonH(4));
            /*注册表格事件监听器*/
            tab.addMouseListener(new TableH());
            f.setSize(500,280);
            f.setVisible(true);
            f.setDefaultCloseOperation( JFrame.EXIT_ON_CLOSE);
        }
        static void preview(){
            try{
                mod.setRowCount(0);
                rs = stmt.executeQuery("SELECT * FROM books");
                while(rs.next()){
                    Object[] data = {rs.getString("id"),rs.getString("title"),rs.getFloat("price")};
                    mod.addRow(data);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }finally{
                try{
                    if(rs != null){
                        rs.close();
                        rs = null;
                    }
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
        }
    
        public static void main(String[] args) {
            sy12_3 d = new sy12_3();
            d.go();
            preview();
        }
        class ButtonH implements ActionListener {
            int sel;
            ButtonH(int select){
                sel = select;
            }
            @Override
            public void actionPerformed(ActionEvent e) {
                if(sel ==1){
                    try{
                        String sql = "insert into books values('"+tfId.getText()+"','"+tfTitle.getText()+"','"+tfPri.getText()+")";
                        stmt.executeUpdate(sql);
                        preview();
                    }catch(Exception e1){
                        e1.printStackTrace();
                    }
                }
                if(sel ==2){
                    try{
                        String sql = "UPDATE books SET price = "+tfPri.getText()+",title ='"+tfTitle.getText()+"'Where id = '"+tfId.getText()+"'";
                        stmt.executeUpdate(sql);
                        preview();
                    }
                    catch(Exception e1){
                        e1.printStackTrace();
                    }
                }
                if(sel == 3){
                    try{
                        String cid = (String)tab.getValueAt(tab.getSelectedRow(),0);
                        String sql = "delete from books where id = '"+cid+"'";
                        stmt.executeUpdate(sql);
                        preview();
                    }
                    catch(Exception e1){
                        e1.printStackTrace();
                    }
                }
                if(sel == 4){
                    try{
                        if(tfId.getText() == ""){
                            preview();
                        }else{
                            String sql = "select * from books where id like '%"+tfId.getText()+"%'";
                            mod.setRowCount(0);
                            rs = stmt.executeQuery(sql);
                            while(rs.next()){
                                Object[] data = {rs.getString("id"),rs.getString("title"),rs.getFloat("price")};
                                mod.addRow(data);
                            }
                        }
                    }
                    catch(Exception e1){
                        e1.printStackTrace();
                    }finally{
                        try{
                            if(rs != null);{
                                rs.close();
                                rs = null;
                            }
                        } catch (SQLException e1) {
                            e1.printStackTrace();
                        }
                    }
                }
    
            }
        }
        class TableH extends MouseAdapter {
            //表格事件监听器TableH继承MouseAdapter，能对鼠标操作表格的动作作出响应*/
            public void mouseClicked(MouseEvent e){
                /*将鼠标单击选中行的图书信息显示到表单栏中*/
                int row;
                String cid,ctitle;
                float cpri;
                row = tab.getSelectedRow();
                cid = (String)tab.getValueAt(row,0);
                ctitle = (String)tab.getValueAt(row,1);
                cpri = (Float)tab.getValueAt(row,2);
                tfId.setText(cid);
                tfTitle.setText(ctitle);
                tfPri.setText(Float.toString(cpri));
            }
        }
    }

# **实验小结** #

1.  ## 中文显示乱码问题 ##

![20210109162342486.png][]

打印结果出现中文乱码，经过一番百度，原来是数据库也要指定编码格式

![20210109162342485.png][]

1.  Java对mysql数据库进行连接、查询和修改

1.  调用Class.forName()方法加载驱动程序。
2.  调用DriverManager对象的getConnection()方法，获得一个Connection对象。
3.  创建一个Statement对象，准备一个SQL语句，这个SQL语句可以是Statement对象（立即执行的的语句）、PreparedStatement语句（预编译的语句）或CallableStatement对象（存储过程调用的语句）。
4.  调用excuteQuery()等方法执行SQL语句，并将结果保存在ResultSet对象；或者调用executeUpdate()等方法执行SQL语句，不返回ResultSet对象的结果。
5.  对返回的ResultSet对象进行显示等相当的处理。
6.  释放资源。

## 2.java中mysql加载驱动方式如下： ##

Class.forName("com.mysql.cj.jdbc.Driver");
    
    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test_demo?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC","root","password");

[f03cff38b5ad7766814c0998bc7c8f3c.png]: /images/20221119/c7843e8015864dc7a7b601b0096a608b.png
[2021010916234261.png]: /images/20221119/26b1ba45fcae41e4822e118f2c9e0587.png
[20210109162342303.png]: https://img-blog.csdnimg.cn/20210109162342303.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTc2NTQ4_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210109162342490.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTc2NTQ4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTc2NTQ4_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210109162342379.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTc2NTQ4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTc2NTQ4_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20210109162342479.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTc2NTQ4,size_16,color_FFFFFF,t_70
[20210109162342486.png]: https://img-blog.csdnimg.cn/20210109162342486.png
[20210109162342485.png]: https://img-blog.csdnimg.cn/20210109162342485.png