Java 访问数据库


Java通过JDBC API访问数据库。


JDBC主要做3件事:

  • 连接数据库。
  • 发送查询或更新SQL语句到数据库。
  • 接收数据库返回的查询或更新结果,并处理返回结果。

如果要访问Mysql数据库,需要安装Mysql数据库的jdbc驱动程序。下载Mysql数据库的JDBC驱动程序(jar文件)。更改CLASSPATH环境变量以包含JDBC驱动程序。其它数据库(Oracle或SQL Server)类似处理。

查询数据库

查询数据库有下面几步:

  • 和数据库建立连接。
  • 创建一个Statement。
  • 执行SQL查询。
  • 处理结果集ResultSet。
  • 关闭资源,包含ResultSet、Statement和数据库连接。

下面是创建表的SQL:

create table user(
    id bigint primary key AUTO_INCREMENT,
    name varchar(50),
    password varchar(100),
    status int,
    descr varchar(200)
);
alter table user AUTO_INCREMENT=1;

下面是往表中增加数据的SQL:

insert into user(name, password, status, descr) values('tom', '123', 1, 'like game');
insert into user(name, password, status, descr) values('james', 'abc', 1, 'love music');

下面是查询数据库的例子:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Query {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/tutor";
            String user = "tutor";
            String password = "tutor";
            conn = DriverManager.getConnection(url, user, password);

            stmt = conn.createStatement();
            stmt.executeQuery("select * from user");
            rs = stmt.getResultSet();
            while (rs.next()) {
                System.out.printf("id :%d, name : %s %n", rs.getInt("id"), rs.getString("name"));
            }

        } catch (Exception e) {
            e.printStackTrace(System.out);
        } finally {
            if (rs != null)
                try {
                    rs.close();
                } catch (Exception e) {
                }
            if (stmt != null)
                try {
                    stmt.close();
                } catch (Exception e) {
                }
            if (conn != null)
                try {
                    conn.close();
                } catch (Exception e) {
                }
        }
    }
}

上面例子中Class.forName("com.mysql.jdbc.Driver");语句用来加载Mysql数据库的驱动程序。String url = "jdbc:mysql://localhost:3306/tutor";中的tutor是数据库名。


更改数据库

下面是更改数据库的例子:

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

public class Update {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/tutor";
            String user = "tutor";
            String password = "tutor";
            conn = DriverManager.getConnection(url, user, password);
            
            // 更改
            pstmt = conn.prepareStatement("update user set descr = ? where name = ?");
            pstmt.setString(1, "爱好音乐");
            pstmt.setString(2, "tom");
            pstmt.executeUpdate();

            // 查询
            stmt = conn.createStatement();
            stmt.executeQuery("select * from user");
            rs = stmt.getResultSet();
            while (rs.next()) {
                System.out.printf("name :%s, descr : %s %n", rs.getString("name"), rs.getString("descr"));
            }

        } catch (Exception e) {
            e.printStackTrace(System.out);
        } finally {
            if (pstmt != null)
                try {
                    pstmt.close();
                } catch (Exception e) {
                }
            if (rs != null)
                try {
                    rs.close();
                } catch (Exception e) {
                }
            if (stmt != null)
                try {
                    stmt.close();
                } catch (Exception e) {
                }
            if (conn != null)
                try {
                    conn.close();
                } catch (Exception e) {
                }
        }
    }
}

上面例子中用到了Prepared Statement,Prepared Statement的SQL第一次执行时被编译,之后再执行该SQL时不会再被编译。而Statement的SQL每次执行时都会被编译。Prepared Statement的性能较Statement好。

数据库事务

数据库事务是为了保证数据的一致性。数据库事务将一或多个对数据库的操作作为一个整体,全部操作都执行或全部操作都不执行。

执行数据库事务有下面几步: 禁用数据库的自动提交模式。 执行一或多个数据库操作。 提交事务。 如果有一个数据库操作失败,回滚所有数据库操作。 * 关闭资源,包含ResultSet、Statement(或Prepared Statement)和数据库连接。

下面是数据库事务和例子:

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

public class Transaction {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/tutor";
            String user = "tutor";
            String password = "tutor";
            conn = DriverManager.getConnection(url, user, password);
            
            conn.setAutoCommit(false);
            
            // 更改
            pstmt = conn.prepareStatement("update user set descr = ? where name = ?");
            pstmt.setString(1, "爱好读书");
            pstmt.setString(2, "tom");
            pstmt.executeUpdate();
            
            pstmt.setString(1, "爱好游戏");
            pstmt.setString(2, "james");
            pstmt.executeUpdate();
            
            // 查询
            stmt = conn.createStatement();
            stmt.executeQuery("select * from user");
            rs = stmt.getResultSet();
            while (rs.next()) {
                System.out.printf("name :%s, descr : %s %n", rs.getString("name"), rs.getString("descr"));
            }

            conn.commit();
        } catch (Exception e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch(Exception e) {
                }
            }
            e.printStackTrace(System.out);
        } finally {
            if (pstmt != null)
                try {
                    pstmt.close();
                } catch (Exception e) {
                }
            if (rs != null)
                try {
                    rs.close();
                } catch (Exception e) {
                }
            if (stmt != null)
                try {
                    stmt.close();
                } catch (Exception e) {
                }
            if (conn != null)
                try {
                    conn.close();
                } catch (Exception e) {
                }
        }
    }
}

存储过程

存储过程是逻辑上相关的一组数据库操作。

执行下面的SQL创建Mysql存储过程:

drop procedure if exists getUserDescr;
DELIMITER //
create procedure getUserDescr(IN userName varchar(50), OUT userDescr varchar(200))
  begin
    select descr into userDescr
      from user
      where name = userName;
  end //
DELIMITER ;

getUserDescr存储过程有两个参数,输入参数userName和输出参数userDescr

下面是存储过程的例子:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Types;

public class StoredProc {
    public static void main(String[] args) {
        Connection conn = null;
        CallableStatement cs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/tutor";
            String user = "tutor";
            String password = "tutor";
            conn = DriverManager.getConnection(url, user, password);

            cs = conn.prepareCall("{call getUserDescr(?, ?)}");
            cs.setString(1, "james");
            cs.registerOutParameter(2, Types.VARCHAR);
            cs.executeQuery();

            String descr = cs.getString(2);
            System.out.println("descr : " + descr);
        } catch (Exception e) {
            e.printStackTrace(System.out);
        } finally {
            if (cs != null)
                try {
                    cs.close();
                } catch (Exception e) {
                }
            if (conn != null)
                try {
                    conn.close();
                } catch (Exception e) {
                }
        }
    }
}