蓝蓝站点

Java读书笔记

13.5.2 处理Blob类型数据

发表于 2019-07-05 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.5 管理结果集 Waline：本文字数： 7.8k 阅读时长 ≈ 7 分钟

13.5.2 处理Blob类型数据

Blob(Binary Long Object)是二进制长对象的意思,Blob列通常用于存储大文件,典型的Blob内容是一张图片或一个声音文件,由于它们的特殊性,必须使用特殊的方式来存储。使用Blob列可以把图片、声音等文件的二进制数据保存在数据库里,并可以从数据库里恢复指定文件。
如果需要将图片插入数据库,显然不能直接通过普通的SQL语句来完成,因为有一个关键的问题: Blob常量无法表示。所以将Blob数据插入数据库需要使用PreparedStatement,该对象有一个方法:setBinaryStream(int parameterIndex, InputStream x),该方法可以为指定参数传入二进制输入流,从而可以实现将Blob数据保存到数据库的功能。
当需要从ResultSet里取出Blob数据时,可以调用ResultSet的getBlob( int columnIndex)方法,该方法将返回一个Blob对象,Blob对象提供了getBinaryStream()方法来获取该Blob数据的输入流,也可以使用Blb对象提供的getBytes()方法直接取出该Blob对象封装的二进制数据。
为了把图片放入数据库,本程序先使用如下SQL语句来建立一个数据表。

create table img_table
(
    img_id int auto_increment primary key,
    img_name varchar(255),
    img_data mediumblob
);

上面SQL语句中的img_data列使用mediumblob类型,而是blob类型。因为MySQL数据库里的blob类型最多只能存储64KB内容,这可能不够满足实际用途。所以使用mediumblob类型,该类型的数据列可以存储16MB内容.
下面程序可以实现图片”上传”——实际上就是将图片保存到数据库,并在右边的列表框中显示图片的名字,
当用户双击列表框中的图片名时,左边窗口将显示该图片—实质就是根据选中的ID从数据库里查找图片,并将其显示出来。

import java.sql.*;
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.util.Properties;
import java.util.ArrayList;
import java.io.*;
import javax.swing.filechooser.FileFilter;

public class BlobTest
{
    JFrame jf = new JFrame("图片管理程序");
    private static Connection conn;
    private static PreparedStatement insert;
    private static PreparedStatement query;
    private static PreparedStatement queryAll;
    // 定义一个DefaultListModel对象
    private DefaultListModel<ImageHolder> imageModel = new DefaultListModel<>();
    private JList<ImageHolder> imageList = new JList<>(imageModel);
    private JTextField filePath = new JTextField(26);
    private JButton browserBn = new JButton("...");
    private JButton uploadBn = new JButton("上传");
    private JLabel imageLabel = new JLabel();
    // 以当前路径创建文件选择器
    JFileChooser chooser = new JFileChooser(".");
    // 创建文件过滤器
    ExtensionFileFilter filter = new ExtensionFileFilter();
    static
    {
        try
        {
            Properties props = new Properties();
            props.load(new FileInputStream("mysql.ini"));
            String driver = props.getProperty("driver");
            String url = props.getProperty("url");
            String user = props.getProperty("user");
            String pass = props.getProperty("pass");
            // 1.加载数据库驱动
            Class.forName(driver);
            // 2.获取数据库连接
            conn = DriverManager.getConnection(url, user, pass);
            // 3.创建执行插入的PreparedStatement对象，
            // 该对象执行插入后可以返回自动生成的主键
            insert = conn.prepareStatement(
                    "insert into img_table" + " values(null,?,?)",
                    Statement.RETURN_GENERATED_KEYS);
            // 创建两个PreparedStatement对象，用于查询指定图片，查询所有图片
            query = conn.prepareStatement(
                    "select img_data from img_table" + " where img_id=?");
            queryAll = conn.prepareStatement(
                    "select img_id, " + " img_name from img_table");
        } catch (Exception e)
        {
            e.printStackTrace();
        }
    }
    public void init() throws SQLException
    {
        // -------初始化文件选择器--------
        filter.addExtension("jpg");
        filter.addExtension("jpeg");
        filter.addExtension("gif");
        filter.addExtension("png");
        filter.setDescription("图片文件(*.jpg,*.jpeg,*.gif,*.png)");
        chooser.addChoosableFileFilter(filter);
        // 禁止“文件类型”下拉列表中显示“所有文件”选项。
        chooser.setAcceptAllFileFilterUsed(false);
        // ---------初始化程序界面---------
        fillListModel();
        filePath.setEditable(false);
        // 只能单选
        imageList.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
        JPanel jp = new JPanel();
        jp.add(filePath);
        jp.add(browserBn);
        browserBn.addActionListener(event ->
        {
            // 显示文件对话框
            int result = chooser.showDialog(jf, "浏览图片文件上传");
            // 如果用户选择了APPROVE（赞同）按钮，即打开，保存等效按钮
            if (result == JFileChooser.APPROVE_OPTION)
            {
                filePath.setText(chooser.getSelectedFile().getPath());
            }
        });
        jp.add(uploadBn);
        uploadBn.addActionListener(avt ->
        {
            // 如果上传文件的文本框有内容
            if (filePath.getText().trim().length() > 0)
            {
                // 将指定文件保存到数据库
                upload(filePath.getText());
                // 清空文本框内容
                filePath.setText("");
            }
        });
        JPanel left = new JPanel();
        left.setLayout(new BorderLayout());
        left.add(new JScrollPane(imageLabel), BorderLayout.CENTER);
        left.add(jp, BorderLayout.SOUTH);
        jf.add(left);
        imageList.setFixedCellWidth(160);
        jf.add(new JScrollPane(imageList), BorderLayout.EAST);
        imageList.addMouseListener(new MouseAdapter()
        {
            public void mouseClicked(MouseEvent e)
            {
                // 如果鼠标双击
                if (e.getClickCount() >= 2)
                {
                    // 取出选中的List项
                    ImageHolder cur = (ImageHolder) imageList
                            .getSelectedValue();
                    try
                    {
                        // 显示选中项对应的Image
                        showImage(cur.getId());
                    } catch (SQLException sqle)
                    {
                        sqle.printStackTrace();
                    }
                }
            }
        });
        jf.setSize(620, 400);
        jf.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        jf.setVisible(true);
    }
    // ----------查找img_table填充ListModel----------
    public void fillListModel() throws SQLException
    {

        try (
                // 执行查询
                ResultSet rs = queryAll.executeQuery())
        {
            // 先清除所有元素
            imageModel.clear();
            // 把查询的全部记录添加到ListModel中
            while (rs.next())
            {
                imageModel.addElement(
                        new ImageHolder(rs.getInt(1), rs.getString(2)));
            }
        }
    }
    // ---------将指定图片放入数据库---------
    public void upload(String fileName)
    {
        // 截取文件名
        String imageName = fileName.substring(fileName.lastIndexOf('\\') + 1,
                fileName.lastIndexOf('.'));
        File f = new File(fileName);
        try (InputStream is = new FileInputStream(f))
        {
            // 设置图片名参数
            insert.setString(1, imageName);
            // 设置二进制流参数
            insert.setBinaryStream(2, is, (int) f.length());
            int affect = insert.executeUpdate();
            if (affect == 1)
            {
                // 重新更新ListModel，将会让JList显示最新的图片列表
                fillListModel();
            }
        } catch (Exception e)
        {
            e.printStackTrace();
        }
    }
    // ---------根据图片ID来显示图片----------
    public void showImage(int id) throws SQLException
    {
        // 设置参数
        query.setInt(1, id);
        try (
                // 执行查询
                ResultSet rs = query.executeQuery())
        {
            if (rs.next())
            {
                // 取出Blob列
                Blob imgBlob = rs.getBlob(1);
                // 取出Blob列里的数据
                ImageIcon icon = new ImageIcon(
                        imgBlob.getBytes(1L, (int) imgBlob.length()));
                imageLabel.setIcon(icon);
            }
        }
    }
    public static void main(String[] args) throws SQLException
    {
        new BlobTest().init();
    }
}
// 创建FileFilter的子类，用以实现文件过滤功能
class ExtensionFileFilter extends FileFilter
{
    private String description = "";
    private ArrayList<String> extensions = new ArrayList<>();
    // 自定义方法，用于添加文件扩展名
    public void addExtension(String extension)
    {
        if (!extension.startsWith("."))
        {
            extension = "." + extension;
            extensions.add(extension.toLowerCase());
        }
    }
    // 用于设置该文件过滤器的描述文本
    public void setDescription(String aDescription)
    {
        description = aDescription;
    }
    // 继承FileFilter类必须实现的抽象方法，返回该文件过滤器的描述文本
    public String getDescription()
    {
        return description;
    }
    // 继承FileFilter类必须实现的抽象方法，判断该文件过滤器是否接受该文件
    public boolean accept(File f)
    {
        // 如果该文件是路径，接受该文件
        if (f.isDirectory())
            return true;
        // 将文件名转为小写（全部转为小写后比较，用于忽略文件名大小写）
        String name = f.getName().toLowerCase();
        // 遍历所有可接受的扩展名，如果扩展名相同，该文件就可接受。
        for (String extension : extensions)
        {
            if (name.endsWith(extension))
            {
                return true;
            }
        }
        return false;
    }
}
// 创建一个ImageHolder类，用于封装图片名、图片ID
class ImageHolder
{
    // 封装图片的ID
    private int id;
    // 封装图片的图片名字
    private String name;
    public ImageHolder()
    {
    }
    public ImageHolder(int id, String name)
    {
        this.id = id;
        this.name = name;
    }
    // id的setter和getter方法
    public void setId(int id)
    {
        this.id = id;
    }
    public int getId()
    {
        return this.id;
    }
    // name的setter和getter方法
    public void setName(String name)
    {
        this.name = name;
    }
    public String getName()
    {
        return this.name;
    }
    // 重写toString方法，返回图片名
    public String toString()
    {
        return name;
    }
}

下面是一个简单的查询执行器,当用户在文本框内输入合法的查询语句并执行成功后,下面的表格将会显示查询结果。

13.5 管理结果集 13.5.1 可滚动,可更新的结果集

发表于 2019-07-04 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.5 管理结果集 Waline：本文字数： 3.4k 阅读时长 ≈ 3 分钟

13.5 管理结果集

JDBC使用ResultSet来封装执行查询得到的查询结果,然后通过移动ResultSet的记录指针来取出结果集的内容。除此之外,JDBC还允许通过ResultSet来更新记录,并提供了ResultSetMetaData来获得ResultSet对象的相关信息。

13.5.1 可滚动,可更新的结果集

前面提到, ResultSet定位记录指针的方法有absolute()、 previous()等方法,但前面程序自始至终都只用了next()方法来移动记录指针,实际上也可以使用absolute()、 previous()、 last()等方法来移动记录指针。

可以使用absolute()、 previous()、 afterLast()等方法自由移动记录指针的ResultSet()被称为可滚动的结果集。

Java 5以后 ResultSet 默认可滚动

在JDK1.4以前,默认打开的ResultSet是不可滚动的,必须在创建Statement或PreparedStatement时传入额外的参数。从Java 5.0以后,默认打开的ResultSet就是可滚动的,无须传入额外的参数.

以默认方式打开的ResultSet是不可更新的,如果希望创建可更新的ResultSet,则必须在创建Statement或PreparedStatement时传入额外的参数。

Connection在创建Statement或PreparedStatement时还可额外传入如下两个参数。

resultSetType:控制ResultSet的类型,该参数可以取如下三个值。
- ResultSet.TYPE_FOWARD_ONLY:该常量控制记录指针只能向前移动。这是JDK 1.4以前的默认值。
- ResultSet.TYPE_SCROLL_INSENSITIVE:该常量控制记录指针可以自由移动(可滚动结果集),但底层数据的改变不会影响ResultSet的内容。
- ResultSet.TYPE_SCROLL_SENSITIVE:该常量控制记录指针可以自由移动(可滚动结果集),而且底层数据的改变会影响ResultSet的内容。
resultSetConcurrency:控制ResultSet的并发类型,该参数可以接收如下两个值。
- ResultSet.CONCUR_READ_ONLY:该常量指示ResultSet是只读的并发模式(默认)。
- ResultSet.CONCUR_UPDATABLE:该常量指示ResultSet是可更新的并发模式。

注意:TYPE_SCROLL_INSENSITIVE、 TYPE_SCROLL_SENSITIVE两个常量的作用需要底层数据库驱动的支持,对于有些数据库驱动来说,这两个常量并没有太大的区别。

下面代码通过这两个参数创建了一个PreparedStatement对象,由该对象生成的ResultSet对象将是可滚动、可更新的结果集。

需要指出的是,可更新的结果集还需要满足如下两个条件。

所有数据都应该来自一个表。
选出的数据集必须包含主键列。

通过该PreparedStatement创建的ResultSet就是可滚动、可更新的,程序可调用ResultSet的updateXxx(int columnIndex, Xxx value)方法来修改记录指针所指向的行的特定列的值,最后调用ResultSet的updateRow()方法来提交修改。
Java 8为ResultSet添加了:

updateObject(String columnLabel,Object x, SQLType targetSqlType)和
updateObject(int columnIndex, Object x, SQLType targetsqlType)

这两个默认方法,这两个方法可以直接用Object来修改记录指针所指记录、特定列的值,其中SQLType用于指定该数据列的类型.但目前最新的MySQL驱动暂不支持该方法。
下面程序示范了这种创建可滚动、可更新的结果集的方法。

import java.util.*;
import java.io.*;
import java.sql.*;

public class ResultSetTest
{
    private String driver;
    private String url;
    private String user;
    private String pass;
    public void initParam(String paramFile) throws Exception
    {
        // 使用Properties类来加载属性文件
        Properties props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pass = props.getProperty("pass");
    }
    public void query(String sql) throws Exception
    {
        // 1.加载驱动
        Class.forName(driver);
        try (
                // 2.获取数据库连接
                Connection conn = DriverManager.getConnection(url, user, pass);
                // 3.创建Statement对象
                // 使用Connection来创建一个PreparedStatement对象
                // 传入参数使得结果集可滚动,可更新.
                PreparedStatement pstmt = conn.prepareStatement(sql,
                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_UPDATABLE);
                // 4.使用Statement对象操作数据库
                ResultSet rs = pstmt.executeQuery())
        {
            // 移动记录指针到最后一行
            rs.last();
            // 获取行号
            int rowCount = rs.getRow();
            for (int i = rowCount; i > 0; i--)
            {
                // 移动行记录指针到第i行
                rs.absolute(i);
                // 从结果集中取出数据列
                System.out.println(rs.getString(1) + "\t" + rs.getString(2)
                        + "\t" + rs.getString(3));
                // 修改记录指针所有记录、第2列的值
                rs.updateString(2, "学生名" + i);
                // 提交修改
                rs.updateRow();
            }
        }
    }
    public static void main(String[] args) throws Exception
    {
        ResultSetTest rt = new ResultSetTest();
        rt.initParam("mysql.ini");
        rt.query("select * from student_table");
    }
}

运行上面程序,将会看到student_table表中的记录被倒过来输出了,因为是从最大记录行开始输出的。而且当程序运行结束后, student_table表中所有记录的student_name列的值都被修改了。
运行效果如下:

7       赵六    3
6       null    2
5       _王五   2
4       王五    2
3       李四    1
2       张三    1
1       张三    1

注意:
如果要创建可更新的结果集,则使用查询语句查询的数据通常只能来自于一个数据表,而且查询结果集中的数据列必须包含主键列,否则将会引起更新失败

13.4.4 使用CallableStatement调用存储过程

发表于 2019-07-04 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.4 执行SQL语句的方式 Waline：本文字数： 2.3k 阅读时长 ≈ 2 分钟

13.4.4 使用CallableStatement调用存储过程

下面的SQL语句可以在MySQL数据库中创建一个简单的存储过程。

delimiter $$
create procedure add_pro(a int,b int,out sum int)
begin
set sum = a +b;
end;
$$

上面的SQL语句将MySQL的语句结束符改为双美元符($$),这样就可以在创建存储过程中使用分号作为分隔符( MySQL默认使用分号作为语句结束符)。上面程序创建了名为add_pro的存储过程,该存储过程包含三个参数:a、b是传入参数,而sum使用out修饰,是传出参数。
调用存储过程使用CallableStatement,可以通过Connection的prepareCall()方法来创建CallableStatement对象,创建该对象时需要传入调用存储过程的SQL语句。调用存储过程的SQL语句总是这种格式:{call 过程名 (?,?,?,)},其中的问号作为存储过程参数的占位符。例如,如下代码就创建了调用上面存储过程的CallableStatement对象。
cstmt=conn.prepareCall("{call add_pro(?,?,?)");
存储过程的参数既有传入参数,也有传出参数。所谓传入参数就是Java程序必须为这些参数传入值,可以通过CallableStatement的setXxx()方法为传入参数设置值;
所谓传出参数就是Java程序可以通过该参数获取存储过程里的值, CallableStatement需要调用registerOutParameter()方法来注册该参数。如下代码所示:
cstmt.registerOutParameter(3, Types.INTEGER);
经过上面步骤之后,就可以调用CallableStatement的execute()方法来执行存储过程了,执行结束后通过CallableStatement对象的getXxx(int index)方法来获取指定传出参数的值。下面程序示范了如何来调用该存储过程。

import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.util.*;
import java.io.*;
import java.sql.*;

public class CallableStatementTest
{
    private String driver;
    private String url;
    private String user;
    private String pass;
    public void initParam(String paramFile) throws Exception
    {
        // 使用Properties类来加载属性文件
        Properties props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pass = props.getProperty("pass");
    }
    public void callProcedure() throws Exception
    {
        // 1.加载驱动
        Class.forName(driver);
        try (
                // 2.获取数据库连接
                Connection conn = DriverManager.getConnection(url, user, pass);
                // 3.使用Connection来创建一个CallableStatment对象
                CallableStatement cstmt = conn
                        .prepareCall("{call add_pro(?,?,?)}"))
        {
            cstmt.setInt(1, 4);
            cstmt.setInt(2, 5);
            // 4.注册CallableStatement的第三个参数是int类型
            cstmt.registerOutParameter(3, Types.INTEGER);
            // 执行存储过程
            cstmt.execute();
            // 获取，并输出存储过程传出参数的值。
            System.out.println("执行结果是: " + cstmt.getInt(3));
        }
    }
    public static void main(String[] args) throws Exception
    {
        CallableStatementTest ct = new CallableStatementTest();
        ct.initParam("mysql.ini");
        ct.callProcedure();
    }
}

运行上面程序将会看到这个简单存储过程的执行结果,传入参数分别是4、5,执行加法后传出总和9.

13.4.3 使用 PreparedStatement执行SQL语句

发表于 2019-07-04 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.4 执行SQL语句的方式 Waline：本文字数： 7.1k 阅读时长 ≈ 6 分钟

13.4.3 使用 PreparedStatement执行SQL语句

如果经常需要反复执行一条结构相似的SQL语句,例如如下两条SQL语句:

1 2	insert into student_table values(null, '张三', 1); insert into student_table values(null, '李四', 2);

对于这两条SQL语句而言,它们的结构基本相似,只是执行插入时插入的值不同而已。对于这种情况,可以使用带占位符(?)参数的SQL语句来代替它:
insert into student_table values(null,?,?);
但Statement执行SQL语句时不允许使用问号占位符参数,而且这个问号占位符参数必须获得值后以执行。为了满足这种功能,JDBC提供了PreparedStatement接口,它是Statement接口的子接口,它可以预编译SQL语句,预编译后的SQL语句被存储在PreparedStatement对象中,然后可以使用该对象多次高效地执行该语句。简而言之,使用PreparedStatement比使用Statement的效率要高。
创建PreparedStatement对象使用Connection的prepareStatement()方法,该方法需要传入一个SQL字符串,该SQL字符串可以包含占位符参数。如下代码所示:

1	pstmt=conn.prepareStatement("insert into student_table values (null,?,1)");

PreparedStatement也提供了execute()、executeUpdate()、executeQuery()三个方法来执行SQL语句,不过这三个方法无须传入参数,因为PreparedStatement已存储了预编译的SQL语句。
使用PreparedStatement预编译SQL语句时,该SQL语句可以带占位符参数,因此在执行SQL语句之前必须为这些参数传入参数值, Preparedstatement提供了以下两种方式来传入参数值:

如果程序知道预编译SQL语句中各参数的类型(如参数类型为Xxx),则使用setXxx(int index, Xxx value)方法来传入参数值。
如果程序不清楚预编译SQL语句中各参数的类型,则可以使用setObject()方法来传入参数,由PreparedStatement来负责类型转换。

下面程序示范了使用Statement和PreparedStatement分别插入100条记录的对比。使用Statement需要传入100条SQL语句,但使用PreparedStatement则只需要传入1条预编译的SQL语句,然后100次为该PreparedStatement的参数设值即可.

import java.util.*;
import java.io.*;
import java.sql.*;

public class PreparedStatementTest
{
    private String driver;
    private String url;
    private String user;
    private String pass;
    public void initParam(String paramFile) throws Exception
    {
        // 使用Properties类来加载属性文件
        Properties props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pass = props.getProperty("pass");
        // 1.加载驱动
        Class.forName(driver);
    }
    public void insertUseStatement() throws Exception
    {
        long start = System.currentTimeMillis();
        try (
                // 2.获取数据库连接
                Connection conn = DriverManager.getConnection(url, user, pass);
                // 3.使用Connection来创建一个Statment对象
                Statement stmt = conn.createStatement())
        {
            // 需要使用100条SQL语句来插入100条记录
            for (int i = 0; i < 100; i++)
            {
                stmt.executeUpdate("insert into student_table values("
                        + " null ,'姓名" + i + "' , 1)");
            }
            System.out.println(
                    "使用Statement费时:" + (System.currentTimeMillis() - start));
        }
    }
    public void insertUsePrepare() throws Exception
    {
        long start = System.currentTimeMillis();
        try (
                // 2.获取数据库连接
                Connection conn = DriverManager.getConnection(url, user, pass);
                // 3.使用Connection来创建一个PreparedStatement对象
                PreparedStatement pstmt = conn.prepareStatement(
                        "insert into student_table values(null,?,1)"))

        {
            // 100次为PreparedStatement的参数设值，就可以插入100条记录
            for (int i = 0; i < 100; i++)
            {
                pstmt.setString(1, "姓名" + i);
                pstmt.executeUpdate();
            }
            System.out.println("使用PreparedStatement费时:"
                    + (System.currentTimeMillis() - start));
        }
    }
    public static void main(String[] args) throws Exception
    {
        PreparedStatementTest pt = new PreparedStatementTest();
        pt.initParam("mysql.ini");
        pt.insertUseStatement();
        pt.insertUsePrepare();
    }
}

多次运行上面程序,可以发现使用PreparedStatement插入100条记录所用的时间比使用Statement插入100条记录所用的时间少,这表明**PreparedStatement的执行效率比Statement的执行效率高**。
除此之外,使用PreparedStatement还有一个优势——当SQL语句中要使用参数时,无须”拼接SQL字符串。而使用Statement则要”拼接”SQL字符串,这是相当容易出现错误的,尤其是当SQL语句中有多个字符串参数时。使用PreparedStatement则只需要使用问号占位符来代替这些参数即可,降低了编程复杂度。
使用PreparedStatement还有一个很好的作用—用于防止SQL注入(SQL注入是一个较常见的Cracker入侵方式,它利用SQL语句的漏洞来入侵)。
下面以一个简单的登录窗口为例来介绍这种SQL注入的结果。下面登录窗口包含两个文本框,个用于输入用户名,一个用于输入密码,系统根据用户输入与jdbc_test表里的记录进行匹配,如果找到相应记录则提示登录成功.

package jdbc.test;

import java.awt.BorderLayout;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
import javax.swing.JTextField;

public class LoginFrame
{
    private final String PROP_FILE = "mysql.ini";
    private String driver;
    // url是数据库的服务地址
    private String url;
    private String user;
    private String pass;
    // 登录界面的GUI组件
    private JFrame jf = new JFrame("登录");
    private JTextField userField = new JTextField(20);
    private JTextField passField = new JTextField(20);
    private JButton loginButton = new JButton("登录");
    public void init() throws Exception
    {
        Properties connProp = new Properties();
        connProp.load(new FileInputStream(PROP_FILE));
        driver = connProp.getProperty("driver");
        url = connProp.getProperty("url");
        user = connProp.getProperty("user");
        pass = connProp.getProperty("pass");
        // 加载驱动
        Class.forName(driver);
        // 为登录按钮添加事件监听器
        loginButton.addActionListener(e ->
        {
            // 登录成功则显示“登录成功”
            if (validate(userField.getText(), passField.getText()))
            {
                JOptionPane.showMessageDialog(jf, "登录成功");
            }
            // 否则显示“登录失败”
            else
            {
                JOptionPane.showMessageDialog(jf, "登录失败");
            }
        });
        jf.add(userField, BorderLayout.NORTH);
        jf.add(passField);
        jf.add(loginButton, BorderLayout.SOUTH);
        jf.pack();
        jf.setVisible(true);
    }
    private boolean validate(String userName, String userPass)
    {
        // 执行查询的SQL语句
        String sql = "select * from jdbc_test " + "where jdbc_name='" + userName
                + "' and jdbc_desc='" + userPass + "'";
        System.out.println(sql);
        try (Connection conn = DriverManager.getConnection(url, user, pass);
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(sql))
        {
            // 如果查询的ResultSet里有超过一条的记录，则登录成功
            if (rs.next())
            {
                return true;
            }
        } catch (Exception e)
        {
            e.printStackTrace();
        }
        return false;
    }

    // private boolean validate(String userName, String userPass)
    // {
    // try (Connection conn = DriverManager.getConnection(url, user, pass);
    // PreparedStatement pstmt = conn.prepareStatement(
    // "select * from jdbc_test where jdbc_name=? and jdbc_desc=?"))
    // {
    // pstmt.setString(1, userName);
    // pstmt.setString(2, userPass);
    // try (ResultSet rs = pstmt.executeQuery())
    // {
    // // 如果查询的ResultSet里有超过一条的记录，则登录成功
    // if (rs.next())
    // {
    // return true;
    // }
    // }
    // } catch (Exception e)
    // {
    // e.printStackTrace();
    // }
    // return false;
    // }

    public static void main(String[] args) throws Exception
    {
        new LoginFrame().init();
    }
}

运行上面程序,如果用户正常输入其用户名、密码当然没有问题,输入正确可以正常登录,输入错误将提示输入失败。但如果这个用户是一个Cracker,他可以在文本框中输入' or true or ',这个输入明显是不正确的,但当单击”登录”按钮后也会显示”登录成功”对话框。可以在程序运行的后台看到如下SOL语句:

1	select * from jdbc_test where jdbc_name='' or true or '' and jdbc_desc=''

看到这条SQL语句,读者应该不难明白为什么这样输入也可以显示”正常登录”对话框了,因为Cracker直接输入了true,而SQL把这个true当成了直接量。
如果把上面的validate()方法注释掉,然后取消另一个validate()方法的注释,换成使用PreparedStatement来执行验证.
这样,即使用户输入了:' or true or ',系统一样会显示”登录失败”对话框.
总体来看,使用PreparedStatement比使用Statement多了如下三个好处:

PreparedStatement预编译SQL语句,性能更好。
PreparedStatement无须”拼接”SQL语句,编程更简单。
PreparedStatement可以防止SQL注入,安全性更好。

基于以上三点,通常推荐避免使用Statement来执行SQL语句,改为使用PreparedStatement执行SQL语句.

注意

使用PreparedStatement执行带占位符参数的SQL语句时,SQL语句中的占位符参数只能代替普通值,不要使用占位符参数代替表名、列名等数据库对象,更不要用占位符参数来代替SQL语句中的Insert、 select等关键字。

13.4.2 使用execute方法执行SQL语句

发表于 2019-07-04 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.4 执行SQL语句的方式 Waline：本文字数： 2.8k 阅读时长 ≈ 3 分钟

13.4.2 使用execute方法执行SQL语句

Statement的execute()方法几乎可以执行任何SQL语句,但它执行SQL语句时比较麻烦,通常没有必要使用execute()方法来执行SQL语句,使用executeQuery()或executeUpdate()方法更简单。但如果不清楚SQL语句的类型,则只能使用execute方法来执行该SQL语句了。
使用execute()方法执行SQL语句的返回值是boolean值,它表明执行该SQL语句是否返回了ResultSet对象。那么如何来获取执行SQL语句后得到的ResultSet对象呢? Statement提供了如下两个方法来获取执行结果。

方法	描述
`getResultSet()`	获取该`Statement`执行查询语句所返回的`ResultSet`对象。
`getUpdateCount()`	获取该`Statement`执行`DML`语句所影响的记录行数

下面程序示范了使用Statement的execute()方法来执行任意的SQL语句,执行不同的SQL语句时产生不同的输出。

import java.util.*;
import java.io.*;
import java.sql.*;

public class ExecuteSQL
{
    private String driver;
    private String url;
    private String user;
    private String pass;
    public void initParam(String paramFile) throws Exception
    {
        // 使用Properties类来加载属性文件
        Properties props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pass = props.getProperty("pass");
    }
    public void executeSql(String sql) throws Exception
    {
        // 1.加载驱动
        Class.forName(driver);
        try (
                // 2.获取数据库连接
                Connection conn = DriverManager.getConnection(url, user, pass);
                // 3.使用Connection来创建一个Statement对象
                Statement stmt = conn.createStatement())
        {
            // 4.执行SQL,返回boolean值表示是否包含ResultSet
            boolean hasResultSet = stmt.execute(sql);
            // 5.如果执行后有ResultSet结果集
            if (hasResultSet)
            {
                try (
                        // 获取结果集
                        ResultSet rs = stmt.getResultSet())
                {
                    // ResultSetMetaData是用于分析结果集的元数据接口
                    ResultSetMetaData rsmd = rs.getMetaData();
                    // 获取列数
                    int columnCount = rsmd.getColumnCount();
                    // 迭代输出ResultSet对象
                    while (rs.next())
                    {
                        // 依次输出每列的值
                        for (int i = 0; i < columnCount; i++)
                        {
                            System.out.print(rs.getString(i + 1) + "\t");
                        }
                        System.out.print("\n");
                    }
                }
            } else
            {
                System.out
                        .println("该SQL语句影响的记录有" + stmt.getUpdateCount() + "条");
            }
        }
    }
    public static void main(String[] args) throws Exception
    {
        ExecuteSQL es = new ExecuteSQL();
        es.initParam("mysql.ini");
        System.out.println("------执行删除表的DDL语句-----");
        es.executeSql("drop table if exists my_test");
        System.out.println("------执行建表的DDL语句-----");
        es.executeSql("create table my_test"
                + "(test_id int auto_increment primary key, "
                + "test_name varchar(255))");
        System.out.println("------执行插入数据的DML语句-----");
        es.executeSql("insert into my_test(test_name) "
                + "select student_name from student_table");
        System.out.println("------执行查询数据的查询语句-----");
        es.executeSql("select * from my_test");
    }
}

运行上面程序,会看到使用Statement的不同方法执行不同SQL语句的效果。执行DDL语句显示受影响的记录条数为0;执行DML语句显示插入、修改或删除的记录条数;执行查询语句则可以输出查询结果。
提示
上面程序获得SQL执行结果时没有根据各列的数据类型调用相应的getXxx()方法而是直接使用getString()方法来取得值,这是可以的。 ResultSet的getString()方法几乎可以获取除Blob之外的任意类型列的值,因为所有的数据类型都可以自动转换成字符串类型。

13.4 执行SQL语句的方式

发表于 2019-07-04 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.4 执行SQL语句的方式 Waline：本文字数： 3.6k 阅读时长 ≈ 3 分钟

13.4 执行SQL语句的方式

前面介绍了JDBC执行查询等示例程序,实际上,JDBC不仅可以执行查询,也可以执行DDLML等SQL语句,从而允许通过JDBC最大限度地控制数据库。

13.4.1 使用executeUpdate方法执行DDL和DML语句

Statement提供了三个方法来执行SQL语句,前面已经介绍了使用executeQuery来执行查询语句,下面将介绍使用executeLargeUpdate()或executeUpdate()来执行DDL和DML语句。使用Statement执行DDL和DML语句的步骤与执行普通查询语句的步骤基本相似,区别在于:

执行了DDL语句后返回值为0,
执行了DML语句后返回值为受影响的记录条数。

数据库模式定义语言DDL(Data Definition Language),主要由create(创建)、alter(修改)、drop(删除)和truncate(删除)四个关键字完成。
数据操纵语言(Data Manipulation Language, DML),由insert(插入),update(更新),delete(删除)
对于一个表:

当不再需要该表时, 用drop;
当你仍要保留该表,但要删除所有记录时,用truncate;
当你要删除部分记录时,用delete.

executeUpdate()方法执行DDL语句

下面程序示范了使用executeUpdate()方法创建数据表,如果MySQL驱动支持,也可以使用executeLargeUpdate()方法。

下面的实例中没有直接把数据库连接信息写在程序里,而是使用一个mysql.ini文件(就是一个properties文件)来保存数据库连接信息,mysql.ini文件中的那内容如下:

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/select_test
user=root
pass=root

当需要把应用程序从开发环境移植到生产环境时,无须修改源代码,只需要修改mysql.ini配置文件即可.

import java.util.*;
import java.io.*;
import java.sql.*;

public class ExecuteDDL
{
    private String driver;
    private String url;
    private String user;
    private String pass;
    public void initParam(String paramFile) throws Exception
    {
        // 使用Properties类来加载属性文件
        Properties props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pass = props.getProperty("pass");
    }
    public void createTable(String sql) throws Exception
    {
        // 1.加载驱动
        Class.forName(driver);
        try (
                // 2.获取数据库连接
                Connection conn = DriverManager.getConnection(url, user, pass);
                // 3.使用Connection来创建一个Statment对象
                Statement stmt = conn.createStatement())
        {
            // 4.执行DDL,创建数据表
            stmt.executeUpdate(sql);
        }
    }
    public static void main(String[] args) throws Exception
    {
        ExecuteDDL ed = new ExecuteDDL();
        ed.initParam("mysql.ini");
        ed.createTable("create table jdbc_test "
                + "( jdbc_id int auto_increment primary key, "
                + "jdbc_name varchar(255), " + "jdbc_desc text);");
        System.out.println("-----建表成功-----");
    }
}

运行上面程序,执行成功后会看到select_test数据库中添加了一个jdbc_test数据表,这表明JDBC执行DDL语句成功。

executeUpdate()方法执行DML语句

使用executeUpdate()执行DML语句与执行DDL语句基本相似,区别是executeUpdate()执行DDL语句后返回0,而执行DML语句后返回受影响的记录条数。
下面程序将会执行一条insert语句,这条insert语句会向刚刚建立的jdbc_test数据表中插入几条记录。因为使用了带子查询的insert语句,所以可以一次插入多条语句.

import java.util.*;
import java.io.*;
import java.sql.*;

public class ExecuteDML
{
    private String driver;
    private String url;
    private String user;
    private String pass;

    public void initParam(String paramFile) throws Exception
    {
        // 使用Properties类来加载属性文件
        Properties props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pass = props.getProperty("pass");
    }
    public int insertData(String sql) throws Exception
    {
        // 1.加载驱动
        Class.forName(driver);
        try (
                // 2.获取数据库连接
                Connection conn = DriverManager.getConnection(url, user, pass);
                // 3.使用Connection来创建一个Statment对象
                Statement stmt = conn.createStatement())
        {
            // 4.执行DML,返回受影响的记录条数
            return stmt.executeUpdate(sql);
        }
    }
    public static void main(String[] args) throws Exception
    {
        ExecuteDML ed = new ExecuteDML();
        ed.initParam("mysql.ini");
        int result = ed.insertData("insert into jdbc_test(jdbc_name,jdbc_desc)"
                + "select s.student_name , t.teacher_name "
                + "from student_table s , teacher_table t "
                + "where s.java_teacher = t.teacher_id;");
        System.out.println("--系统中共有" + result + "条记录受影响--");
    }
}

运行上面程序,执行成功将会看到jdbc_test数据表中多了几条记录,而且在程序控制台会看到输出有几条记录受影响的信息。

13.3.2 JDBC编程步骤

发表于 2019-07-04 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.3 JDBC的典型用法 Waline：本文字数： 3.3k 阅读时长 ≈ 3 分钟

13.3.2 JDBC编程步骤

大致了解了JDBC API的相关接口和类之后,下面就可以进行JDBC编程了,JDBC编程大致按如下步骤进行。

1.加载数据库驱动

通常使用Class类的forName()静态方法来加载驱动。例如如下代码:
Class forName(driverClass)
上面代码中的driverClass就是数据库驱动类所对应的字符串。例如,加载MySQL的驱动采用如下代码:
Class.forName("com.mysql.jdbc.Driver");
而加载Oracle的驱动则采用如下代码:
Class.forName("oracle.jdbc.driver.OracleDriver");
从上面代码中可以看出,加载驱动时并不是真正使用数据库的驱动类,只是使用数据库驱动类名的字符串而已。

2.通过DriverManager获取数据库连接

DriverManager提供了如下方法:
DriverManager getConnection(String url, String user, String password);
当使用DriverManager获取数据库连接时,通常需要传入三个参数:数据库URL、登录数据库的用户名和密码。这三个参数中用户名和密码通常由DBA(数据库管理员)分配,而且该用户还应该具有相应的权限,才可以执行相应的SQL语句
数据库URL通常遵循如下写法
jdbc:subprotocol:other stuff
上面URL写法中的jdbc是固定的,而subprotocol指定连接到特定数据库的驱动,而后面的other和stuff是不固定的—也没有较强的规律,不同数据库的URL写法可能存在较大差异例如, MySQL数据库的URL写法如下:
jdbc:mysql://hostName:port/DataBaseName
Oracle数据库的URL写法如下:
jdbc:oracle:thin:@hostName:port:databaseName

3.通过Connection对象创建Statement对象

Connection创建Statement的方法有如下三个。

方法	描述
`createStatement()`	创建基本的`Statement`对象。
`prepareStatement(String sql)`	根据传入的`SQL`语句创建预编译的`Statement`对象。
`prepareCall(String sql)`	根据传入的`SQL`语句创建`Callablestatement`对象。

4.使用Statement执行SQL语句

所有的Statement都有如下三个方法来执行SQL语句。

方法	描述
`execute()`	可以执行任何`SQL`语句,但比较麻烦。
`executeUpdate()`	主要用于执行`DML`和`DDL`语句。执行`DML`语句返回受`SQL`语句影响的行数,执行`DDL`语句返回0
`executeQuery()`	只能执行查询语句,执行后返回代表查询结果的`ResultSet`对象。

5.操作结果集

如果执行的SQL语句是查询语句,则执行结果将返回一个ResultSet对象,该对象里保存了SQL语句查询的结果。程序可以通过操作该ResultSet对象来取出查询结果。 ResultSet对象主要提供了如下两类方法。

移动记录指针的方法:next()、previous()、first()、last()、beforeFirst()、afterLast()、absolute()
获取记录指针指向的行中特定列的值:getXxx()方法。该方法既可使用列索引作为参数,也可使用列名作为参数。
- 使用列索引作为参数性能更好,
- 使用列名作为参数可读性更好。

ResultSet实质是一个査询结果集,在逻辑结构上非常类似于一个表。图13.17显示了ResultSet的逻辑结构,以及操作ResultSet结果集并获取值的方法示意图

6.回收数据库资源

包括关闭ResultSet、 Statement和Connection等资源。

实例

下面程序简单示范了JDBC编程,并通过ResultSet获得结果集的过程。

import java.sql.*;
public class ConnMySql
{
    public static void main(String[] args) throws Exception
    {
        // 1.加载驱动，使用反射的知识，现在记住这么写。
        Class.forName("com.mysql.jdbc.Driver");
        try (
                // 2.使用DriverManager获取数据库连接,
                // 其中返回的Connection就代表了Java程序和数据库的连接
                // 不同数据库的URL写法需要查驱动文档知道，用户名、密码由DBA分配
                Connection conn = DriverManager.getConnection(
                        "jdbc:mysql://127.0.0.1:3306/select_test", "root",
                        "root");
                // 3.使用Connection来创建一个Statment对象
                Statement stmt = conn.createStatement();
                // 4.执行SQL语句
                /*
                 * Statement有三种执行sql语句的方法： 1 execute 可执行任何SQL语句。- 返回一个boolean值，
                 * 如果执行后第一个结果是ResultSet，则返回true，否则返回false 2 executeQuery
                 * 执行Select语句 － 返回查询到的结果集 3 executeUpdate 用于执行DML语句。－ 返回一个整数，
                 * 代表被SQL语句影响的记录条数
                 */
                ResultSet rs = stmt.executeQuery("select s.* , teacher_name"
                        + " from student_table s , teacher_table t"
                        + " where t.teacher_id = s.java_teacher");
            )
        {
            // ResultSet有系列的getXxx(列索引 | 列名)，用于获取记录指针
            // 指向行、特定列的值，不断地使用next()将记录指针下移一行，
            // 如果移动之后记录指针依然指向有效行，则next()方法返回true。
            while (rs.next())
            {
                System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t"
                        + rs.getString(3) + "\t" + rs.getString(4));
            }
        }
    }
}

上面程序严格按JDBC访问数据库的步骤执行了一条多表连接査询语句,这条连接查询语句就是前面介绍SQL 92连接时所讲的连接查询语句。
运行上面程序,会看到如下所示:

1       张三    1       Yeeku
2       张三    1       Yeeku
3       李四    1       Yeeku
4       王五    2       Leegang
5       _王五   2       Leegang
6       null    2       Leegang
7       赵六    3       Martine

与前面介绍的步骤略有区别的是,本程序采用了自动关闭资源的try语句来关闭各种数据库资源,Java 7改写了Connection、Statement、 ResultSet等接口,它们都继承了AutoCloseable接口,因此它们都可以由try语句来关闭。

小结

JDBC编程大致按如下步骤进行

1.加载数据库驱动
2.通过DriverManager获取数据库连接
3.通过Connection对象创建Statement对象
4.使用Statement执行SQL语句
5.操作结果集
6.回收数据库资源

13.3 JDBC的典型用法 13.3.1 JDBC4.2常用接口和类简介

发表于 2019-07-03 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.3 JDBC的典型用法 Waline：本文字数： 3.7k 阅读时长 ≈ 3 分钟

13.3 JDBC的典型用法

13.3.1 JDBC4.2常用接口和类简介

Java 8支持JDBC4.2标准,JDBC4.2在原有JDBC标准上增加了一些新特性。下面介绍这些JDBC API时会提到Java 8新增的功能.

DriverManager

DriverManager:用于管理JDBC驱动的服务类。程序中使用该类的主要功能是获取Connection对象,该类包含如下方法。

public static synchronized Connection getConnection(String url, String user, String pass) throws SQLException:该方法可以获得url对应数据库的连接。

Connection

Connection:代表数据库连接对象,每个Connection代表一个物理连接会话。要想访问数据库必须先获得数据库连接。该接口的常用方法如下。

1.获取Statement对象的方法

方法	描述
`Statement createStatement() throws SQLExcetpion`	该方法返回一个`Statement`对象。
`PreparedStatement prepareStatement(String sql) throws SQLExcetpion`	该方法返回预编译的`Statement`对象,即将`SQL`语句提交到数据库进行预编译。
`CallableStatement prepareCall(String sql) throws SQLExcetpion`	该方法返回`CallableStatement`对象,该对象用于调用存储过程。

上面三个方法都返回用于执行SQL语句的Statement对象, PreparedStatement、 CallableStatement是Statement的子类,只有获得了Statement之后才可执行SQL语句。

除此之外, Connection还有如下几个用于控制事务的方法。

2.控制事务的方法

方法	描述
`Savepoint setSavepoint()`	创建一个保存点。
`Savepoint setSavepoint(String name)`	以指定名字来创建一个保存点
`void setTransactionIsolation(int level)`	设置事务的隔离级别。
`void rollback()`	回滚事务。
`void rollback(Savepoint savepoint)`	将事务回滚到指定的保存点。
`void setAutoCommit(boolean autoCommit)`	关闭自动提交,打开事务。
`void commit()`	提交事务。

3.Java 7中Connection新增方法

Java 7为Connection新增了setSchema(String schema)、 getSchema()两个方法,这两个方法用于控制该Connection访问的数据库Schema
Java 7还为Connection新增了setNetworkTimeou(Executor executor,int milliseconds)、 getNetworkTimeout()两个方法来控制数据库连接的超时行为。

Statement

Statement:用于执行SQL语句的工具接口。该对象既可用于执行DDL、DCL语句,也可用于执行DML语句,还可用于执行SQL查询。当执行SQL査询时,返回查询到的结果集。它的常用方法如下。

方法	描述
`ResultSet executeQuery(String sql) throws SQLException`	该方法用于执行查询语句,并返回查询结果对应的 `ResultSet`对象。该方法只能用于执行查询语句.
`int executeUpdate(String sql) ) throws SQLExcetion`	该方法用于执行`DML`语句,并返回受影响的行数;该方法也可用于执行`DDL`语句,执行`DDL`语句将返回0。
`boolean execute(String sql) ) throws SQLException`	该方法可执行任何`SQL`语句。如果执行后第一个结果为 `ResultSet`对象,则返回`true`;如果执行后第一个结果为受影响的行数或没有任何结果,则返回 `false`

Java 7新增方法

Java 7为Statement新增了closeOnCompletion()方法,如果Statement执行了该方法,则当所有依赖于该Statement的ResultSet关闭时,该Statement会自动关闭。
Java7还为Statement提供了一个isCloseOnCompletion()方法,该方法用于判断该Statement是否打开了” closeOn Completion“功能。

Java 8新增方法

Java 8为Statement新增了多个重载的executeLargeUpdate()方法,这些方法相当于增强版的executeUpdate()方法,返回值类型为long——也就是说,当DML语句影响的记录条数超过Integer.MAX_VALUE时,就应该使用executeLargeUpdate()方法.

PreparedStatement

PreparedStatement:预编译的Statement对象。 PreparedStatement是Statement的子接口,它允许数据库预编译SQL语句(这些SQL语句通常带有参数),以后每次只改变SQL命令的参数,避免数据库每次都需要编译SQL语句,因此性能更好。相对于Statement而言,使用PreparedStatement执行SQL语句时,无须再传入SQL语句,只要为预编译的SQL语句传入参数值即可。所以它比Statement多了如下方法:

void setXxx(int parameterIndex,Xxx value):该方法根据传入参数值的类型不同,需要使用不同的方法。传入的值根据索引传给SQL语句中指定位置的参数。

注意
PreparedStatement同样有executeUpdate()、 executeQuery()和execute()三个方法,只是这三个方法无须接收SQL字符串,因为PreparedStatement对象已经预编译了SQL命令,只要为这些命令传入参数即可。

Java 8还为PreparedStatement增加了不带参数的executeLargeUpdate()方法—执行DML语句影响的记录条数可能超过Integer.MAX_VALUE时,就应该使用executeLargeUpdate()方法。

ResultSet

ResultSet:结果集对象。该对象包含访问查询结果的方法, ResultSet可以通过列索引或列名获得列数据。它包含了如下常用方法来移动记录指针。

方法	描述
`void close()`	释放`ResultSet`对象。
`boolean absolute(int row)`	将结果集的记录指针移动到第`row`行,如果`row`是负数,则移动到倒数第`row`行。如果移动后的记录指针指向一条有效记录,则该方法返回`true`
`void beforeFirst()`	将`ResultSet`的记录指针定位到首行之前,这是`ResultSet`结果集记录指针的初始状态(记录指针的起始位置位于第一行之前)
`boolean previous()`	将`ResultSet`的记录指针定位到上一行。如果移动后的记录指针指向一条有效记录,则该方法返回`true`
`boolean next()`	将`ResultSet`的记录指针定位到下一行,如果移动后的记录指针指向一条有效记录,则该方法返回`true`
`boolean last()`	将`ResultSet`的记录指针定位到最后一行,如果移动后的记录指针指向一条有效记录,则该方法返回`true`
`void afterLast()`	将`ResultSet`的记录指针定位到最后一行之后

支持的Java版本

在JDK1.4以前,采用默认方法创建的Statement所查询得到的ResultSet不支持absolute()、 previous()等移动记录指针的方法,它只支持next()这个移动记录指针的方法,即ResultSet的记录指针只能向下移动,而且每次只能移动一格。
从Java5.0以后就避免了这个问题,程序采用默认方法创建的Statement所查询得到的ResultSet也支持absolute、previous等方法

当把记录指针移动到指定行之后, ResultSet可通过getXxx(int columnIndex)或getXx(Stringcolumn Label)方法来获取当前行、指定列的值,前者根据列索引获取值,后者根据列名获取值。

Java 7新增了<T> T getObject(int columnIndex, Class<T> type)和
<T> T getObject(String columnLabel,Class<T>type)两个泛型方法,它们可以获取任意类型的值

13.2.14 集合运算

发表于 2019-07-02 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.2 SQL语法 Waline：本文字数： 2.8k 阅读时长 ≈ 3 分钟

13.2.14 集合运算

select语句查询的结果是一个包含多条数据的结果集,类似于数学里的集合,可以进行交( Intersect)、并( union)和差( minus)运算, select查询得到的结果集也可能需要进行这三种运算.
为了对两个结果集进行集合运算,这两个结果集必须满足如下条件。

两个结果集所包含的数据列的数量必须相等。
两个结果集所包含的数据列的数据类型也必须一一对应。

1. union运算

unIon运算的语法格式如下:
select语句 union select语句
下面的SQL语句查询出所有教师的信息和主键小于4的学生信息。

1
2
3

select * from teacher_table
union
select student_id,student_name from student_table;

运行效果:

mysql> select * from teacher_table
union
select student_id,student_name from student_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
|          1 | 张三         |
|          2 | 张三         |
|          3 | 李四         |
|          4 | 王五         |
|          5 | _王五        |
|          6 | NULL         |
|          7 | 赵六         |
+------------+--------------+
10 rows in set

2. minus运算

minus运算的语法格式如下:
select语句 minus select语句
上面的语法格式十分简单,不过很遗憾,MySQL并不支持使用minus运算符,因此只能借助于子查询来实现上面的minus运算。
假如想从所有学生记录中”减去”与老师记录的ID相同、姓名相同的记录,则可进行如下的minus运算:

1
2
3

select student_id,student_name from student_table
minus
select teacher_id,teacher_name from teacher_table;

不过, MySQL并不支持这种运算。但可以通过如下子查询来实现上面运算

select student_id,student_name from student_table
where (student_id,student_name)
not in
(
    select teacher_id,teacher_name from teacher_table
);

运行效果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set

mysql> select student_id,student_name from student_table
where (student_id,student_name)
not in
(
    select teacher_id,teacher_name from teacher_table
);
+------------+--------------+
| student_id | student_name |
+------------+--------------+
|          1 | 张三         |
|          2 | 张三         |
|          3 | 李四         |
|          4 | 王五         |
|          5 | _王五        |
|          6 | NULL         |
|          7 | 赵六         |
+------------+--------------+
7 rows in set

mysql>

3. intersect运算

intersect运算的语法格式如下:
select语句 intersect select语句
上面的语法格式十分简单,不过很遗憾, MySQL并不支持使用intersect运算符,因此只能借助于多表连接查询来实现上面的intersect运算。
假如想找出学生记录中与老师记录中的ID相同、姓名相同的记录,则可进行如下的intersect运算:

1
2
3

select student_id,student_name from student_table
intersect
select teacher_id,teacher_name from teacher_table;

不过, MySQL并不支持这种运算。但可以通过如下多表连接查询来实现上面运算。

1
2
3

select student_id,student_name from student_table
join teacher_table
on (student_id=teacher_id and student_name=teacher_name);

需要指出的是,如果进行intersect运算的两个select子句中都包括了where条件,那么将intersect运算改写成多表连接査询后还需要将两个where条件进行and运算。假如有如下Intersect运算的SQL语句:

1
2
3

select student_id,student_name from student_table where student_id<4
intersect
select teacher_id,teacher_name from teacher_table where teacher_name like '李%';

上面语句改写如下:

select student_id,student_name from student_table
join
teacher_table
on(student_id=teacher_id and student_name=teacher_name)
where student_id<4 and teacher_name like '李%';

13.2.13 子查询

发表于 2019-07-02 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.2 SQL语法 Waline：本文字数： 6.6k 阅读时长 ≈ 6 分钟

13.2.13 子查询

下面是本文用到的测试数据:

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50508
Source Host           : localhost:3306
Source Database       : select_test

Target Server Type    : MYSQL
Target Server Version : 50508
File Encoding         : 65001

Date: 2019-07-02 22:42:04
*/

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `emp_table`;
CREATE TABLE `emp_table` (
  `emp_id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_name` varchar(255) DEFAULT NULL,
  `manager_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`emp_id`),
  KEY `manager_id` (`manager_id`),
  CONSTRAINT `emp_table_ibfk_1` FOREIGN KEY (`manager_id`) REFERENCES `emp_table` (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `emp_table` VALUES ('1', '唐僧', null);
INSERT INTO `emp_table` VALUES ('2', '孙悟空', '1');
INSERT INTO `emp_table` VALUES ('3', '猪八戒', '1');
INSERT INTO `emp_table` VALUES ('4', '沙僧', '1');

DROP TABLE IF EXISTS `student_table`;
CREATE TABLE `student_table` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(255) DEFAULT NULL,
  `java_teacher` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`),
  KEY `java_teacher` (`java_teacher`),
  CONSTRAINT `student_table_ibfk_1` FOREIGN KEY (`java_teacher`) REFERENCES `teacher_table` (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `student_table` VALUES ('1', '张三', '1');
INSERT INTO `student_table` VALUES ('2', '张三', '1');
INSERT INTO `student_table` VALUES ('3', '李四', '1');
INSERT INTO `student_table` VALUES ('4', '王五', '2');
INSERT INTO `student_table` VALUES ('5', '_王五', '2');
INSERT INTO `student_table` VALUES ('6', null, '2');
INSERT INTO `student_table` VALUES ('7', '赵六', '3');

DROP TABLE IF EXISTS `teacher_table`;
CREATE TABLE `teacher_table` (
  `teacher_id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `teacher_table` VALUES ('1', 'Yeeku');
INSERT INTO `teacher_table` VALUES ('2', 'Leegang');
INSERT INTO `teacher_table` VALUES ('3', 'Martine');

子查询就是指在査询语句中嵌套另一个査询,子査询可以支持多层嵌套。对于一个普通的查询语句而言,子查询可以出现在两个位置:

出现在from语句后,此时子查询的结果可以看成一个数据表,这种用法也被称为行内视图,因为该子查询的实质就是一个临时视图。
出现在where条件后作为过滤条件的值。
使用子查询时要注意如下几点。
子查询要用括号括起来。
把子查询当成数据表时(出现在from之后),可以为该子查询起别名,尤其是作为前缀来限定数据列时,必须给子查询起别名。

对于把子查询当做数据表来用,只是把之前的表名变成子查询(也可为子查询起别名),其他部分与普通查询没有任何区别。下面的SQL语句示范了把子查询当成数据表的用法。

1
2
3

select * 
from (select * from student_table) t
where t.java_teacher>1;

运行效果:

mysql> select * 
from (select * from student_table) t
where t.java_teacher>1;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
4 rows in set

把子查询当成数据表的用法更准确地说是当成视图,可以把上面的SQL语句理解成在执行查询时创建了一个临时视图,该视图名为t,所以这种临时创建的视图也被称为行内视图。
还有一种情形:把子查询当成where条件中的值,如果子查询返回单行、单列值,则被当成一个标量值使用,也就可以使用单行记录比较运算符。例如如下SQL语句:

select * from student_table
where java_teacher > 
(
    select teacher_id from teacher_table
    where teacher_name = 'Yeeku'
);

运行结果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set

mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set

mysql> select * from student_table
where java_teacher > 
(
    select teacher_id from teacher_table
    where teacher_name = 'Yeeku'
);
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
4 rows in set

mysql>

上面查询语句中的子査询将返回一个单行、单列的值(该值就是1),如果把上面查询语句的括号部分换成1,那么这条语句就再简单不过了,相同功能的查询结果如下所示:

mysql> select * from student_table
where java_teacher > 1;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
4 rows in set

mysql>

放在Where子句的子查询会把单行,单列的返回值当成标量处理.

如果子査询返回多个值,则需要使用in、any和all等关键字,in可以单独使用,与前面介绍比较运算符时所讲的in完全一样,此时可以把子查询返回的多个值当成一个值列表。如下SQL语句所示:

select *
from student_table
where student_id in
(
    select teacher_id from teacher_table
);

运行效果:

mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select *
from student_table
where student_id in
(
    select teacher_id from teacher_table
);
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
+------------+--------------+--------------+
3 rows in set
mysql>

上面查询语句中的子查询将返回多个值,这多个值将被当成一个值列表,只要student_id与该值列表中的任意一个值相等,就可以选出这条记录。
any和all可以与>、≥、<、<、<>、=等运算符结合使用,与any结合使用分别表示大于、大于等于、小于、小于等于、不等于、等于其中任意一个值;
与叫all结合使用分别表示大于、大于等于、小于、小于等于、不等于、等于全部值。从上面介绍中可以看出,=any的作用与in的作用相同。如下SQL语句使用=any来代替上面的in:

select * from student_table
where student_id = any
(
    select teacher_id from teacher_table
);

运行结果:

mysql> select * from student_table
where student_id = any
(
    select teacher_id from teacher_table
);
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
+------------+--------------+--------------+
3 rows in set

<ANY只要小于值列表中的最大值即可,
>ANY只要大于值列表中的最小值即可。
<all要求小于值列表中的最小值,
>all要求大于值列表中的最大值
下面的SQL语句选出student_table表中student_id大于teacher_table表中所有teacher_id的记录。
1
2
3
4
5
6
select *
from student_table
where student_id >
all (
select teacher_id from teacher_table
);
还有一种子查询可以返回多行、多列,此时where子句中应该有对应的数据列,并使用圆括号将多个数据列组合起来。如下SQL语句所示:
1
2
3
select * from student_table
where (student_id,student_name) = any
(select teacher_id,teacher_name from teacher_table);