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

public class DatabaseMetaDataTest
{
    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 info() throws Exception
    {
        // 加载驱动
        Class.forName(driver);
        try(
            // 获取数据库连接
            Connection conn = DriverManager.getConnection(url
                , user , pass))
        {
            // 获取的DatabaseMetaData对象
            DatabaseMetaData dbmd = conn.getMetaData();
            // 获取MySQL支持的所有表类型
            ResultSet rs = dbmd.getTableTypes();
            System.out.println("--MySQL支持的表类型信息--");
            printResultSet(rs);
            // 获取当前数据库的全部数据表
            rs = dbmd.getTables(null,null, "%" , new String[]{"TABLE"});
            System.out.println("--当前数据库里的数据表信息--");
            printResultSet(rs);
            // 获取student_table表的主键
            rs = dbmd.getPrimaryKeys(null , null, "student_table");
            System.out.println("--student_table表的主键信息--");
            printResultSet(rs);
            // 获取当前数据库的全部存储过程
            rs = dbmd.getProcedures(null , null, "%");
            System.out.println("--当前数据库里的存储过程信息--");
            printResultSet(rs);
            // 获取teacher_table表和student_table之间的外键约束
            rs = dbmd.getCrossReference(null,null, "teacher_table"
                , null, null, "student_table");
            System.out.println("--teacher_table表和student_table之间"
                + "的外键约束--");
            printResultSet(rs);
            // 获取student_table表的全部数据列
            rs = dbmd.getColumns(null, null, "student_table", "%");
            System.out.println("--student_table表的全部数据列--");
            printResultSet(rs);
        }
    }
    public void printResultSet(ResultSet rs)throws SQLException
    {
        ResultSetMetaData rsmd = rs.getMetaData();
        // 打印ResultSet的所有列标题
        for (int i = 0 ; i < rsmd.getColumnCount() ; i++ )
        {
            System.out.print(rsmd.getColumnName(i + 1) + "\t");
        }
        System.out.print("\n");
        // 打印ResultSet里的全部数据
        while (rs.next())
        {
            for (int i = 0; i < rsmd.getColumnCount() ; i++ )
            {
                System.out.print(rs.getString(i + 1) + "\t");
            }
            System.out.print("\n");
        }
        rs.close();
    }
    public static void main(String[] args)
        throws Exception
    {
        DatabaseMetaDataTest dt = new DatabaseMetaDataTest();
        dt.initParam("mysql.ini");
        dt.info();
    }
}

Statement stmt=conn.createStatement();
//使用Statement同时收集多条SQL语句
stmt.addBatch(sql1);
stmt.addBatch(sql2);
stmt. addBatch(sql3);
...
//同时执行所有的SQL语句
stmt.executeLargeBatch();

//1.保存当前的自动的提交模式
boolean autoCommit= conn.getAutoCommit();
//2.关闭自动提交
conn.setAutoCommit(false);
//3.使用 Statement同时收集多条SQL语句
stmt.addBatch(sql1);
stmt.addBatch(sql2);
stmt.addBatch(sql3);
...
//4.同时提交所有的SQL语句
stmt.executeLargeBatch();
//5.提交修改
conn.commit();
//6.恢复原有的自动提交模式
conn.setAutoCommit(autoCommit);

statement.executeUpdate(...);
statement.executeUpdate(...);
statement.executeUpdate(...);

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

public class TransactionTest
{
    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 insertInTransaction(String[] sqls) throws Exception
    {
        // 1.加载驱动
        Class.forName(driver);
        try (Connection conn = DriverManager.getConnection(url, user, pass))
        {
            // 关闭自动提交，开启事务
            conn.setAutoCommit(false);
            try (
                    // 使用Connection来创建一个Statment对象
                    Statement stmt = conn.createStatement())
            {
                // 循环多次执行SQL语句
                for (String sql : sqls)
                {
                    stmt.executeUpdate(sql);
                }
            }
            // 提交事务
            conn.commit();
        }
    }
    public static void main(String[] args) throws Exception
    {
        TransactionTest tt = new TransactionTest();
        tt.initParam("mysql.ini");
        String[] sqls = new String[]
        {"insert into student_table values(null , 'aaa' ,1)",
                "insert into student_table values(null , 'bbb' ,1)",
                "insert into student_table values(null , 'ccc' ,1)",
                // 下面这条SQL语句将会违反外键约束，
                // 因为teacher_table中没有ID为5的记录。
                "insert into student_table values(null , 'ccc' ,5)" // ①
        };
        tt.insertInTransaction(sqls);
    }
}

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

public class CachedRowSetPage
{
    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 CachedRowSet query(String sql, int pageSize, int page)
            throws Exception
    {
        // 1.加载驱动
        Class.forName(driver);
        try (
                // 2.获取数据库连接
                Connection conn = DriverManager.getConnection(url, user, pass);
                // 3.创建Statement对象
                Statement stmt = conn.createStatement();
                // 4.使用Statement对象执行SQL语句
                ResultSet rs = stmt.executeQuery(sql))
        {
            // 使用RowSetProvider创建RowSetFactory
            RowSetFactory factory = RowSetProvider.newFactory();
            // 创建默认的CachedRowSet实例
            CachedRowSet cachedRs = factory.createCachedRowSet();
            // 设置每页显示pageSize条记录
            cachedRs.setPageSize(pageSize);
            // 使用ResultSet装填RowSet，设置从第几条记录开始
            cachedRs.populate(rs, (page - 1) * pageSize + 1);
            return cachedRs;
        }
    }
    public static void main(String[] args) throws Exception
    {
        CachedRowSetPage cp = new CachedRowSetPage();
        cp.initParam("mysql.ini");
        CachedRowSet rs = cp.query("select * from student_table", 3, 2); // ①
        // 向后滚动结果集
        while (rs.next())
        {
            System.out.println(rs.getString(1) + "\t" + rs.getString(2) + "\t"
                    + rs.getString(3));
        }
    }
}

// 设置每页显示pageSize条记录
cachedRs.setPageSize(pageSize);
// 使用ResultSet装填RowSet，设置从第几条记录开始
cachedRs.populate(rs, (page - 1) * pageSize + 1);

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

public class CachedRowSetTest
{
    private static String driver;
    private static String url;
    private static String user;
    private static 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 CachedRowSet query(String sql) throws Exception
    {
        // 1.加载驱动
        Class.forName(driver);
        // 2.获取数据库连接
        Connection conn = DriverManager.getConnection(url, user, pass);
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        // 3.使用RowSetProvider创建RowSetFactory
        RowSetFactory factory = RowSetProvider.newFactory();
        // 4.创建默认的CachedRowSet实例
        CachedRowSet cachedRs = factory.createCachedRowSet();
        // 5.使用ResultSet装填RowSet
        cachedRs.populate(rs); // ①
        // 关闭资源
        rs.close();
        stmt.close();
        conn.close();
        return cachedRs;
    }
    public static void main(String[] args) throws Exception
    {
        CachedRowSetTest ct = new CachedRowSetTest();
        ct.initParam("mysql.ini");
        CachedRowSet rs = ct.query("select * from student_table");
        rs.afterLast();
        // 向前滚动结果集
        while (rs.previous())
        {
            System.out.println(rs.getString(1) + "\t" + rs.getString(2) + "\t"
                    + rs.getString(3));
            if (rs.getInt("student_id") == 3)
            {
                // 修改指定记录行
                rs.updateString("student_name", "孙悟空");
                rs.updateRow();
            }
        }
        // 重新获取数据库连接
        Connection conn = DriverManager.getConnection(url, user, pass);
        conn.setAutoCommit(false);
        // 把对RowSet所做的修改同步到底层数据库
        rs.acceptChanges(conn);
    }
}

import java.util.*;
import java.io.*;
import java.sql.*;
import javax.sql.rowset.*;
import com.sun.rowset.*;

public class JdbcRowSetTest
{
    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 update(String sql) throws Exception
    {
        // .加载驱动
        Class.forName(driver);
        try (
                // 2.获取数据库连接
                Connection conn = DriverManager.getConnection(url, user, pass);
                // 3.创建JdbcRowSetImpl对象
                JdbcRowSet jdbcRs = new JdbcRowSetImpl(conn)) // ①
        {
            // 设置SQL查询语句
            jdbcRs.setCommand(sql);
            // 执行查询
            jdbcRs.execute();
            // 记录指针移动到最后一行
            jdbcRs.afterLast();
            // 向前滚动结果集
            while (jdbcRs.previous())
            {
                System.out.println(jdbcRs.getString(1) + "\t"
                        + jdbcRs.getString(2) + "\t" + jdbcRs.getString(3));
                if (jdbcRs.getInt("student_id") == 3)
                {
                    // 修改指定记录行
                    jdbcRs.updateString("student_name", "孙悟空");
                    // 更新该行记录
                    jdbcRs.updateRow();
                }
            }
        }
    }
    public static void main(String[] args) throws Exception
    {
        JdbcRowSetTest jt = new JdbcRowSetTest();
        jt.initParam("mysql.ini");
        jt.update("select * from student_table");
    }
}

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

public class RowSetFactoryTest
{
    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 update(String sql) throws Exception
    {
        // 1.加载驱动
        Class.forName(driver);
        // 2.使用RowSetProvider创建RowSetFactory
        RowSetFactory factory = RowSetProvider.newFactory();
        try (
                // 3.使用RowSetFactory创建默认的JdbcRowSet实例
                JdbcRowSet jdbcRowSet = factory.createJdbcRowSet())
        {
            // 4.设置必要的连接信息
            jdbcRowSet.setUrl(url);
            jdbcRowSet.setUsername(user);
            jdbcRowSet.setPassword(pass);
            // 设置SQL查询语句
            jdbcRowSet.setCommand(sql);
            // 执行查询
            jdbcRowSet.execute();
            jdbcRowSet.afterLast();
            // 向前滚动结果集
            while (jdbcRowSet.previous())
            {
                System.out.println(jdbcRowSet.getString(1) + "\t"
                        + jdbcRowSet.getString(2) + "\t" + jdbcRowSet.getString(3));
                if (jdbcRowSet.getInt("student_id") == 3)
                {
                    // 修改指定记录行
                    jdbcRowSet.updateString("student_name", "孙悟空");
                    jdbcRowSet.updateRow();
                }
            }
        }
    }
    public static void main(String[] args) throws Exception
    {
        RowSetFactoryTest jt = new RowSetFactoryTest();
        jt.initParam("mysql.ini");
        jt.update("select * from student_table");
    }
}

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

public class QueryExecutor
{
    JFrame jf = new JFrame("查询执行器");
    private JScrollPane scrollPane;
    private JButton execBn = new JButton("查询");
    // 用于输入查询语句的文本框
    private JTextField sqlField = new JTextField(45);
    private static Connection conn;
    private static Statement stmt;
    // 采用静态初始化块来初始化Connection、Statement对象
    static
    {
        try
        {
            Properties props = new Properties();
            props.load(new FileInputStream("mysql.ini"));
            String drivers = props.getProperty("driver");
            String url = props.getProperty("url");
            String username = props.getProperty("user");
            String password = props.getProperty("pass");
            // 1。加载数据库驱动
            Class.forName(drivers);
            // 2.取得数据库连接
            conn = DriverManager.getConnection(url, username, password);
            // 3.通过连接创建Statement对象
            stmt = conn.createStatement();
        } catch (Exception e)
        {
            e.printStackTrace();
        }
    }
    // --------初始化界面的方法---------
    public void init()
    {
        JPanel top = new JPanel();
        top.add(new JLabel("输入查询语句："));
        top.add(sqlField);
        top.add(execBn);
        // 为执行按钮、单行文本框添加事件监听器
        execBn.addActionListener(new ExceListener());
        sqlField.addActionListener(new ExceListener());
        jf.add(top, BorderLayout.NORTH);
        jf.setSize(680, 480);
        jf.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        jf.setVisible(true);
    }
    // 定义监听器
    class ExceListener implements ActionListener
    {
        public void actionPerformed(ActionEvent evt)
        {
            // 删除原来的JTable(JTable使用scrollPane来包装)
            if (scrollPane != null)
            {
                jf.remove(scrollPane);
            }
            try (
                    // 根据用户输入的SQL执行查询
                    ResultSet rs = stmt.executeQuery(sqlField.getText()))
            {
                // 取出ResultSet的MetaData
                ResultSetMetaData resultSetMetaData = rs.getMetaData();
                Vector<String> columnNames = new Vector<>();
                Vector<Vector<String>> data = new Vector<>();
                // 把ResultSet的所有列名添加到Vector里
                for (int i = 0; i < resultSetMetaData.getColumnCount(); i++)
                {
                    columnNames.add(resultSetMetaData.getColumnName(i + 1));
                }
                // 把ResultSet的所有记录添加到Vector里
                while (rs.next())
                {
                    Vector<String> v = new Vector<>();
                    for (int i = 0, length = resultSetMetaData
                            .getColumnCount(); i < length; 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();
            }
        }
    }
    public static void main(String[] args)
    {
        new QueryExecutor().init();
    }
}