java--第12章数据库编程

北山啦

发布于 2022-11-27 11:42:09

7890

发布于 2022-11-27 11:42:09

文章被收录于专栏：北山啦的博客

实验目的：

1.掌握JDBC编程。

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

实验内容：

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

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

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

实验步骤：

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

1）表books的结构如下：

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。

源代码：

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();
                }
            }
        }
    }
}

运行结果截图：

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();
                }
            }
        }
    }

}

运行结果截图：

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

源代码：

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));
        }
    }
}

实验小结

中文显示乱码问题

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

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

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");

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2021-01-09，如有侵权请联系 cloudcommunity@tencent.com 删除

sql server