JDBC 使用方法 >> 1. 静态SQL; 2. 动态SQL; 3. 批处理; 4. 事务.

JDBCTest.java

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

public class TestJDBC
{
    // 数据库URL
    private String url = "jdbc:mysql://localhost:3306/test?"
            + "user=root&password=root&useUnicode=true&characterEncoding=gb2312";

    // 数据库用户
    private String user = "root";

    // 数据库秘密
    private String password = "root";

    // 加载JDBC驱动程序
    private void loadDriver()
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
        }
        catch (ClassNotFoundException e)
        {
            System.out.println("找不到驱动程序类，加载驱动失败！");
            e.printStackTrace();
        }
    }

    // 创建数据库连接
    private Connection getConnection()
    {
        Connection conn = null;
        try
        {
            conn = DriverManager.getConnection(url, user, password);
        }
        catch (SQLException e)
        {
            System.out.println("数据库连接失败！");
            e.printStackTrace();
        }
        return conn;
    }

    // 执行静态SQL语句
    private void testStatement() throws SQLException
    {
        Connection conn = getConnection();
        Statement statement = conn.createStatement();

        ResultSet resultSet = statement.executeQuery("select * from Person");

        String name;
        int age;
        while (resultSet.next())
        {
            name = resultSet.getString(1);
            age = resultSet.getInt(2);
            System.out.println("name: " + name + ",\t age: " + age);
        }

        statement.close();
        conn.close();
    }

    // 执行动态SQL语句
    private void testPreparedStatement() throws SQLException
    {
        Connection conn = getConnection();
        PreparedStatement statement = conn
                .prepareStatement("insert into Person (username, age) values (?, ?)");
        statement.setString(1, "XiaoMa");
        statement.setInt(2, 30);
        int result = statement.executeUpdate();
        System.out.println("Affect " + result + " lines.");

        statement.close();
        conn.close();
    }

    // 批处理
    private void testBatch() throws SQLException
    {
        Connection conn = getConnection();
        PreparedStatement statement = conn
                .prepareStatement("insert into Person (username, age) values (?, ?)");

        Person[] persons = new Person[]
        { new Person("Tom", 10), new Person("John", 20), new Person("Amy", 25) };

        for (int i = 0; i < persons.length; i++)
        {
            statement.setString(1, persons[i].getName());
            statement.setInt(2, persons[i].getAge());
            statement.addBatch();
        }

        statement.executeBatch();

        statement.close();
        conn.close();
    }

    // 事务
    private void testTransaction()
    {
        Connection conn = getConnection();
        Statement statement = null;

        // 保存点
        Savepoint sp = null;

        // 设置禁止自动提交
        try
        {
            conn.setAutoCommit(false);

            statement = conn.createStatement();

            statement
                    .executeUpdate("insert into Person (username, age)values('tmp', 20)");

            sp = conn.setSavepoint();

            statement
                    .executeUpdate("update Person set age=100 where username='tmp'");
            statement
                    .executeUpdate("insert into Person (username, age)values('tmp', 120)");

            // 提交事务
            conn.commit();
        }
        catch (SQLException e)
        {
            try
            {
                // 回滚
                conn.rollback(sp);
                conn.commit();
            }
            catch (SQLException e1)
            {
                e.printStackTrace();
            }

            e.printStackTrace();
        }
        finally
        {
            closeResource(conn, statement);
        }
    }

    // 关闭资源
    private void closeResource(Connection conn, Statement statement)
    {
        try
        {
            if (null != statement)
            {
                statement.close();
            }
            conn.close();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws SQLException
    {
        TestJDBC test = new TestJDBC();
        test.loadDriver();
        test.testStatement();
        test.testPreparedStatement();
        test.testBatch();
        test.testTransaction();
    }
}

Person.java

class Person
{
    private String name;
    private int age;

    public Person(String name, int age)
    {
        this.name = name;
        this.age = age;
    }

    public String getName()
    {
        return name;
    }

    public void setName(String name)
    {
        this.name = name;
    }

    public int getAge()
    {
        return age;
    }

    public void setAge(int age)
    {
        this.age = age;
    }
}