MySQL数据库和JDBC编程

来源：整理自李刚《疯狂Java讲义》。

1. 环境配置

2. 数据库连接

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" , "kl123456");
        // 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));
            }
        }){}

    }
}

3. DDL 建表

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
{
    // 加载驱动
    Class.forName(driver);
    try(
    // 获取数据库连接
    Connection conn = DriverManager.getConnection(url , user , pass);
    // 使用Connection来创建一个Statment对象
    Statement stmt = conn.createStatement())
    {
        // 执行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("-----建表成功-----");
}

public int insertData(String sql)throws Exception
{
    // 加载驱动
    Class.forName(driver);
    try(
    // 获取数据库连接
    Connection conn = DriverManager.getConnection(url
    , user , pass);
    // 使用Connection来创建一个Statment对象
    Statement stmt = conn.createStatement())
    {
        // 执行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 + "条记录受影响--");
}

5.执行SQL语句 （DDL、DML）

public void executeSql(String sql)throws Exception
{
    // 加载驱动
    Class.forName(driver);
    try(
    // 获取数据库连接
    Connection conn = DriverManager.getConnection(url
    , user , pass);
    // 使用Connection来创建一个Statement对象
    Statement stmt = conn.createStatement())
    {
        // 执行SQL,返回boolean值表示是否包含ResultSet
        boolean hasResultSet = stmt.execute(sql);
        // 如果执行后有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");
}

6. 预编译SQL

public void insertUseStatement()throws Exception
{
    long start = System.currentTimeMillis();
    try(
    // 获取数据库连接
    Connection conn = DriverManager.getConnection(url, user , pass);
    // 使用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(
    // 获取数据库连接
       Connection conn = DriverManager.getConnection(url, user , pass);
    // 使用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();
}

7.防止SQL注入攻击

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

8. 调用存储过程

public void callProcedure()throws Exception
{
    // 加载驱动
    Class.forName(driver);
    try(
    // 获取数据库连接
    Connection conn = DriverManager.getConnection(url, user , pass);
    // 使用Connection来创建一个CallableStatment对象
    CallableStatement cstmt = conn.prepareCall("{call add_pro(?,?,?)}"))
    {
        cstmt.setInt(1, 4);
        cstmt.setInt(2, 5);
        // 注册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();
}

public void query(String sql)throws Exception
{
    // 加载驱动
    Class.forName(driver);
    try(
    // 获取数据库连接
    Connection conn = DriverManager.getConnection(url , user , pass);
    // 使用Connection来创建一个PreparedStatement对象
    // 传入控制结果集可滚动，可更新的参数。
    PreparedStatement pstmt = conn.prepareStatement(sql
    , ResultSet.TYPE_SCROLL_INSENSITIVE
    , ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = pstmt.executeQuery())
    {
        rs.last();
        int rowCount = rs.getRow();
        for (int i = rowCount; i > 0 ; 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");
}

10.处理Blob类型数据

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 imageModel= new DefaultListModel<>();
    private JList 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");
    Class.forName(driver);
    // 获取数据库连接
    conn = DriverManager.getConnection(url , user , pass);
    // 创建执行插入的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();
}
}

11.分析结果集

// 根据用户输入的SQL执行查询
try(
    ResultSet rs = stmt.executeQuery(sqlField.getText()))
{
    // 取出ResultSet的MetaData

    ResultSetMetaData rsmd = rs.getMetaData();
    Vector<String> columnNames =  new Vector<>();
    Vector<Vector<String>> data = new Vector<>();
    // 把ResultSet的所有列名添加到Vector里
    for (int i = 0 ; i < rsmd.getColumnCount(); i++ )
    {
        columnNames.add(rsmd.getColumnName(i + 1));
    }
    // 把ResultSet的所有记录添加到Vector里
    while (rs.next())
    {
        Vector<String> v = new Vector<>();
        for (int i = 0 ; i < rsmd.getColumnCount(); i++ )
        {
            v.add(rs.getString(i + 1));
        }
        data.add(v);
    }
    // 创建新的JTable
    JTable table = new JTable(data , columnNames);
    scrollPane = new JScrollPane(table);
    // 添加新的Table
    jf.add(scrollPane);
    // 更新主窗口
    jf.validate();
}
catch (Exception e)
{
    e.printStackTrace();
}