JDBC

一、JDBC简介

　　1.JDBC

　　JDBC是用于在Java语言编程中与数据库连接的API，参见(https://www.yiibai.com/jdbc)。

　　通常，JDBC体系结构由两层组成：

• JDBC API：提供应用程序到JDBC管理器连接；JDBC API使用驱动程序管理器并指定数据库的驱动程序来提供与异构数据库的透明连接。

• JDBC驱动程序API：支持JDBC管理器到驱动程序连接。JDBC驱动程序管理器确保使用正确的驱动程序来访问每个数据源。 驱动程序管理器能够支持连接到多个异构数据库的多个并发驱动程序。

　　2.JDBC API

　　JDBC API提供以下接口和类 -

• DriverManager：此类管理数据库驱动程序列表。 使用通信子协议将来自java应用程序的连接请求与适当的数据库驱动程序进行匹配。在JDBC下识别某个子协议的第一个驱动程序将用于建立数据库连接。

• Driver：此接口处理与数据库服务器的通信。我们很少会直接与Driver对象进行交互。 但会使用DriverManager对象来管理这种类型的对象。 它还提取与使用Driver对象相关的信息。

• Connection：此接口具有用于联系数据库的所有方法。 连接(Connection)对象表示通信上下文，即，与数据库的所有通信仅通过连接对象。

• Statement：使用从此接口创建的对象将SQL语句提交到数据库。 除了执行存储过程之外，一些派生接口还接受参数。

• ResultSet：在使用Statement对象执行SQL查询后，这些对象保存从数据库检索的数据。 它作为一个迭代器并可移动ResultSet对象查询的数据。

• SQLException：此类处理数据库应用程序中发生的任何错误。

　　3.JDBC流程

• 连接到数据库

• 创建SQL或MySQL语句

• 在数据库中执行SQL或MySQL查询

• 查看和修改结果记录

 二、JDBC 驱动导入(macOS)版本及使用示例

　　1.下载并导入驱动

　　参考官方下载安装教程(https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-installing.html)。

　　1.假设已经安装了eclipse(Java EE版本)、Java1.8.0_171、msyql5.7。并给java配置了环境变量。

　　2.下载mysql-connector-java-8.0.2，网址：https://dev.mysql.com/downloads/connector/j/，在"Select Operating System"选项中选择"Platform Independent"，如下两个版本都可以下载：

　

　　3.在eclipse中点击项目文件夹，右键并点击"properties"，左侧选择"Java Build Path"，右侧选择"Libraries"，点击"Add External JARs..."，将下载的mysql-connector-java-8.0.12.jar添加进来。项目文件下就会出现"Referenced Libraries"类库。

　　4.备注，JDBC连接oracle同样需要下载(ojdbc6.jar，对应oracle_database_11g，java1.8）。导入和连接的方式相同。

　　2.Example示例

　　参考官方使用教程(https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-examples.html)。

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

public class TestJDBC {
    public static void main(String[] args) {
        Connection conn = null; // 
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 1.提供驱动包路径
            Class.forName("com.mysql.cj.jdbc.Driver").newInstance(); // 驱动名称
            // 2.创建连接，和sqlAlchemy一样
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "1234"); // 路径，用户名，密码;test为测试数据库
            // 3.创建游标
            stmt = conn.createStatement();
            // 4.执行查询
            rs = stmt.executeQuery("SELECT * FROM `example` limit 10"); // t_source是一张表,executeQuery通常用于查询
            rs.next();// 选择一条数据，rs是个迭代器
            System.out.println(String.format("index: %s, anch: %s", rs.getString("index"), rs.getString("anch")));            
            /**
            while(rs.next()) {
                System.out.println(String.format("index: %s, anch: %s", rs.getString("index"), rs.getString("anch")));            
            }*/
            
            // 5.执行插入
            String insert_sql = String.format("INSERT INTO %s VALUES('%s', '%s', %d, %d)", "example", "z", "890", 4363, 2916);
            System.out.println(insert_sql);
            int out = stmt.executeUpdate(insert_sql);  // 返回被影响的记录数
            if(out > 0) {
                System.out.println("插入记录成功！");
            }
            // 6.执行修改
            String update_sql = "UPDATE example SET power=4300 WHERE `index`='d'";
            if(stmt.executeUpdate(update_sql) > 0) { // stmt.executeUpdate结果返回整数
                System.out.println("修改记录成功！");
            }
            
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {

            if(rs != null) {
                try {
                    rs.close();
                }catch(SQLException sqlEx){ }//ignore
            }
            rs = null;
            if(stmt != null) {
                try{
                    stmt.close();
                }catch(SQLException sqlEx) {} // ignore
            }
            stmt = null;
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

三、DAO

　　同Django里的QuerySet类似，DAO是一种对象化数据的设计思想，作用是将数据库数据的有关操作对象化。它将数据库的数据转化为java的对象并返回(读数据)，将java的对象转换为数据库表的一条记录(写数据)。

　　DAO对数据进行三层分离：1.定义一个model类，它将数据库一条记录转化为一个实例对象；2.定义一个dao类，用于操作数据库，并根据模型类实例化一条记录；3.定义一个service，用来对业务提供服务。

　　1.DAO分层设计示例

// 实例对象对应example表中的一条记录，字段和成员变量一一对应
public class Example {
    private String index;
    private String anch;
    private int power;
    private int time;
    
    public String getIndex() {
        return index;
    }
    public void setIndex(String index) {
        this.index = index;
    }
    public String getAnch() {
        return anch;
    }
    public void setAnch(String anch) {
        this.anch = anch;
    }
    public int getPower() {
        return power;
    }
    public void setPower(int power) {
        this.power = power;
    }
    public int getTime() {
        return time;
    }
    public void setTime(int time) {
        this.time = time;
    }
    @Override
    public String toString() {
        return "Example [" + index + ", " + anch + ", " + power + ", " + time + "]";
    }
    
}

// dao类，负责与数据库的链接和操作
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class ExampleDAO {
    private static final String CLASS_NAME = "com.mysql.cj.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost:3306/test";
    private static final String USER = "root";
    private static final String PASSWORD = "1234";
    
    public Example findRecordByIndex(String index) {
        Connection conn = null;
        Statement state = null;
        ResultSet result = null;
        try {
            Class.forName(CLASS_NAME);
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            state = conn.createStatement();
            String sql = String.format("SELECT * FROM example WHERE `index`='%s'", index);
            
            result = state.executeQuery(sql);
            if(result.next()) { // 假设index是唯一主键，即只有一条记录
                Example example = new Example();
                example.setIndex(result.getString("index"));
                example.setAnch(result.getString("anch"));
                example.setPower(result.getInt("power"));
                example.setTime(result.getInt("time"));
                System.out.println(example.toString());
                return example;
            }
            
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
            if(conn != null) {
                try {
                    conn.close();
                }catch(Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return null; // 没找到就返回null
    }
}

// service类，面向业务
public class ExampleService {
    private ExampleDAO exdao = new ExampleDAO();
    public void findRecordByIndex(String index) {
        if(index!=null && index!="") {
            Example record = exdao.findRecordByIndex(index);
            if(record != null) {
                System.out.println(record.toString());
            }else {
                System.out.println("查无此人");
            }
        }
    }
    public static void main(String[] args) {
        ExampleService service = new ExampleService();
        service.findRecordByIndex("f");
    }
}

　　2.将链接和断开抽象出来

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class BaseDAO {
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost:3306/test";
    private static final String USER = "root";
    private static final String PASSWORD = "1234";
    
    static {
        try {
            Class.forName(DRIVER);
            // Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        }catch(Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e); // 抛出异常
        }
    }
    
    protected Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
    protected static void closeConnection(Connection conn) {
        if(conn != null) {
            try {
                conn.close();
            }catch(SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

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

// 继承自BaseDAO
public class ExampleDAO  extends BaseDAO {
    
    public Example findRecordByIndex(String index) {
        Connection conn = null;
        Statement state = null;
        ResultSet result = null;
        try {
            conn = getConnection(); // 调用父类链接方法
            state = conn.createStatement();
            String sql = String.format("SELECT * FROM example WHERE `index`='%s'", index);
            
            result = state.executeQuery(sql);
            if(result.next()) { // 假设index是唯一主键，即只有一条记录
                Example example = new Example();
                example.setIndex(result.getString("index"));
                example.setAnch(result.getString("anch"));
                example.setPower(result.getInt("power"));
                example.setTime(result.getInt("time"));
                System.out.println(example.toString());
                return example;
            }
            
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
            closeConnection(conn); // 调用父类断开方法
        }
        return null; // 没找到就返回null
    }
}

　　3.将配置信息写到一个文件里

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class BaseDAO {
    /** java.util.Properties用于读取.properties文件，以key=value按每行读取 */
    private static Properties properties = new Properties();
    private static String DRIVER;
    private static String URL;
    private static String USER;
    private static String PASSWORD;
    
    static {
        try {
            properties.load(BaseDAO.class.getClassLoader().getResourceAsStream(""+"jdbc2/db.properties"));
            DRIVER = properties.getProperty("jdbc.driver");
            URL = properties.getProperty("jdbc.url");
            USER = properties.getProperty("jdbc.user");
            PASSWORD = properties.getProperty("jdbc.password");
            Class.forName(DRIVER);
        }catch(Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e); // 抛出异常
        }
    }
    
    protected Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
    protected static void closeConnection(Connection conn) {
        if(conn != null) {
            try {
                conn.close();
            }catch(SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/test
jdbc.user=root
jdbc.password=1234

 三、JDBC事务操作

　　基于两个需求：一、在执行多条插入sql时将其作为一个事务整体，防止个别sql出现错误造成无法追查核回滚。二、批处理sql时，缓存和一次性提交，降低数据传输次数，提高网络通信效率。

jdbc.driver=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@10.211.55.15:1521:orcl
jdbc.user=scott
jdbc.password=tiger

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class BaseDAO {
    /** java.util.Properties用于读取.properties文件，以key=value按每行读取 */
    private static Properties properties = new Properties();
    private static String DRIVER;
    private static String URL;
    private static String USER;
    private static String PASSWORD;
    
    static {
        try {
            properties.load(BaseDAO.class.getClassLoader().getResourceAsStream(""+"oracle/entities/db.properties"));
            DRIVER = properties.getProperty("jdbc.driver");
            URL = properties.getProperty("jdbc.url");
            USER = properties.getProperty("jdbc.user");
            PASSWORD = properties.getProperty("jdbc.password");
            Class.forName(DRIVER);
        }catch(Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e); // 抛出异常
        }
    }
    
    protected static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
    protected static void closeConnection(Connection conn) {
        if(conn != null) {
            try {
                conn.close();
            }catch(SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

import java.io.Serializable;

public class UserInfo implements Serializable{
    
    private static final long serialVersionUID = 1L;
    private String id;
    private String name;
    private String password;
    private int age;
    private String sex;
    private String email;
    
    UserInfo(String name, String password, int age, String sex, String email){
        this.name = name;
        this.age = age;
        this.password = password;
        this.sex = sex;
        this.email = email;
    }    
    
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    
}

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import oracle.dao.BaseDAO;

public class UserInfoDAO extends BaseDAO {
    private static final String INSERT = "INSERT INTO userinfo(id, name, password, age, sex, email)VALUES("+
                                         "sys_guid(), ?, ?, ?, ?, ?" +")";
    /** 创建userinfo表 */
    public void createTable(){ 
        Connection conn = null;
        Statement state = null;
        try {
            conn = BaseDAO.getConnection();
            state = conn.createStatement();
            // 执行逻辑块
            String sql = "create table userinfo(id varchar2(36) primary key, name varchar2(30), password varchar2(50)," + 
                                                 "age number(4), sex varchar2(2), email varchar2(50))";
            if(!state.execute(sql)) { // 创建/删除表用execute，返回false表示操作成功
                System.out.println("创建成功！");
            }
        }catch(Exception e) {e.printStackTrace();}
        finally {BaseDAO.closeConnection(conn);}
    }
    /** 删除userinfo表 */
    public void deleteTable() {
        Connection conn = null;
        Statement state = null;
        try {
            conn = BaseDAO.getConnection();
            state = conn.createStatement();
            String sql = "drop table userinfo";
            if(!state.execute(sql)) { // 创建/删除表用execute，返回false表示操作成功
                System.out.println("删除成功！");
            }
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
            BaseDAO.closeConnection(conn);
        }
    }

    /** 插入一条记录 */
    public boolean save(UserInfo userinfo) {
        return false;
    }
    /** 插入多条记录 -- 单条插入测试 */
    private static boolean save_test1() {
        // 通过conn.setAutoCommit(false) + conn.commit()保证事务操作的一致性
        Connection conn = null;
        try {
            conn = getConnection(); // 连接数据库
            PreparedStatement state = conn.prepareStatement(INSERT); // 创建游标
            conn.setAutoCommit(false); // 禁止自动提交，可以保证只要一条sql语句执行错误，事务(所有sql)提交失败，保证一致性
            // 遍历数组，执行sql
            for(int i=0; i<1000; i++) {
                 state.setString(1, "name-" + i);
                 state.setString(2, "password-" + i);
                 state.setInt(3, i);
                 state.setString(4, "1");
                 state.setString(5, "email-" + i);
                 state.executeUpdate(); // 这条插入语句需要执行for循环次，并且是立刻执行
                 System.out.println("插入第" + i+1 + "条记录，总共1000条记录.");
            }
            conn.commit();
            System.out.println("插入记录完成!");
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            if(conn != null) {
                try {
                    conn.rollback(); // 捕获异常后事务回滚
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        }finally {
            try {conn.close();} catch (SQLException e) {e.printStackTrace();}
        }
        return false;
    }
    
    private static boolean save_test2() {
        // 批处理版
        Connection conn = null;
        PreparedStatement state = null;
        try {
            conn = getConnection(); 
            state = conn.prepareStatement(INSERT); 
            conn.setAutoCommit(false);
            for(int i=0; i<1000; i++) {
                 state.setString(1, "name-" + i);
                 state.setString(2, "password-" + i);
                 state.setInt(3, i);
                 state.setString(4, "1");
                 state.setString(5, "email-" + i);
                 state.addBatch(); // 添加到本地batch
                 System.out.println("插入第" + i+1 + "条记录，总共1000条记录.");
            }
            state.executeBatch(); // 缓存所有的sql
            conn.commit();
            System.out.println("插入记录完成!");
            return true;
            
        } catch (SQLException e) {
            e.printStackTrace();
            if(conn != null) {
                try {
                    conn.rollback(); // 捕获异常后事务回滚
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        }finally {
            try {conn.close();} catch (SQLException e) {e.printStackTrace();}
        }
        return false;
    }
    
    public void save(String test, int num) {
        if(test == "test") {
            if(num == 0) {
                UserInfoDAO.save_test1();
            }else if(num == 1) {
                UserInfoDAO.save_test2();
            }
        }
    }
    
    private boolean save(List<UserInfo> userinfos) {
            // 批处理版
            Connection conn = null;
            PreparedStatement state = null;
            try {
                conn = getConnection(); 
                state = conn.prepareStatement(INSERT); 
                conn.setAutoCommit(false);
                for(UserInfo userinfo: userinfos) {
                     state.setString(1, userinfo.getName());
                     state.setString(2, userinfo.getPassword());
                     state.setInt(3, userinfo.getAge());
                     state.setString(4, userinfo.getSex());
                     state.setString(5, userinfo.getEmail());
                     state.addBatch(); // 添加到本地batch
                     System.out.println("成功插入一条记录，总共1000条记录.");
                }
                state.executeBatch(); // 缓存所有的sql
                conn.commit();
                System.out.println("插入记录完成!");
                return true;
            } catch (SQLException e) {
                e.printStackTrace();
                if(conn != null) {
                    try {
                        conn.rollback(); // 捕获异常后事务回滚
                    } catch (SQLException e1) {
                        e1.printStackTrace();
                    }
                }
            }finally {
                try {conn.close();} catch (SQLException e) {e.printStackTrace();}
            }
            return false;
        }
    
    public static void main(String[] args) {
        UserInfoDAO userInfoDAO = new UserInfoDAO();
        userInfoDAO.createTable();
        // userInfoDAO.save("test", 0);
        // userInfoDAO.save("test", 1);
        
        List<UserInfo> userinfos = new ArrayList<UserInfo>();
        userinfos.add(new UserInfo("Jan", "1234", 18, "1", "Jan@126.com"));
        userinfos.add(new UserInfo("An", "1234", 24, "1", "An@126.com"));
        userinfos.add(new UserInfo("Bob", "1234", 26, "1", "Bob@126.com"));
        userInfoDAO.save(userinfos);
        // userInfoDAO.deleteTable();

    }
}

四、JDBC的线程处理

　　为了模块中共享变量以及模块间解耦，将BaseDAO和UserInfoDAO重写。将Connection作为共享变量放到线程中，并根据线程中的Connection重写连接、提交、回滚、关闭等操作。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class BaseDAO {
    /** java.util.Properties用于读取.properties文件，以key=value按每行读取 */
    private static Properties properties = new Properties();
    private static String DRIVER;
    private static String URL;
    private static String USER;
    private static String PASSWORD;
    
    // 线程内共享Connection变量
    private static ThreadLocal<Connection> localConn = new ThreadLocal<Connection>();
    
    static {
        try {
            properties.load(BaseDAO.class.getClassLoader().getResourceAsStream(""+"oracle/thread/db.properties"));
            DRIVER = properties.getProperty("jdbc.driver");
            URL = properties.getProperty("jdbc.url");
            USER = properties.getProperty("jdbc.user");
            PASSWORD = properties.getProperty("jdbc.password");
            Class.forName(DRIVER);
        }catch(Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e); // 抛出异常
        }
    }
    
    protected Connection getConnection(){
        // 查看线程localConn中是否存了conn这个变量，没有就创建，有就直接用
        Connection conn = localConn.get();
        // 如果是空的，说明是第一次连接，创建conn，放到线程中
        if(conn == null) {
            try {
                conn = DriverManager.getConnection(URL, USER, PASSWORD);
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            localConn.set(conn);
        }
        return conn;
        
    }
    protected void closeConnection() {
        Connection conn = localConn.get();
        if(conn != null) {
            try {
                conn.close();// 将线程中的那个connection关闭
                localConn.remove();// 删除connection占用
                
            }catch(SQLException e) {
                e.printStackTrace();
            }
        }
    }
    protected void commit() {
        //
        Connection conn = localConn.get();
        if(conn != null) {
            try {
                conn.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    protected void rollback() {
        //
        Connection conn = localConn.get();
        if(conn != null) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class UserInfoDAO extends BaseDAO {
    private static final String INSERT = "INSERT INTO userinfo(id, name, password, age, sex, email)VALUES("+
                                         "sys_guid(), ?, ?, ?, ?, ?" +")";
    /** 创建userinfo表 */
    public void createTable(){ 
        Connection conn = null;
        Statement state = null;
        try {
            conn = getConnection();
            state = conn.createStatement();
            // 执行逻辑块
            String sql = "create table userinfo(id varchar2(36) primary key, name varchar2(30), password varchar2(50)," + 
                                                 "age number(4), sex varchar2(2), email varchar2(50))";
            if(!state.execute(sql)) { // 创建/删除表用execute，返回false表示操作成功
                System.out.println("创建成功！");
            }
        }catch(Exception e) {e.printStackTrace();}
        finally {
            closeConnection();
        }
    }
    /** 删除userinfo表 */
    public void deleteTable() {
        Connection conn = null;
        Statement state = null;
        try {
            conn = getConnection();
            state = conn.createStatement();
            String sql = "drop table userinfo";
            if(!state.execute(sql)) { // 创建/删除表用execute，返回false表示操作成功
                System.out.println("删除成功！");
            }
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
            closeConnection();
        }
    }
    private boolean save(List<UserInfo> userinfos) {
        Connection conn = null;
        PreparedStatement state = null;
            try {
                conn = getConnection();
                state = conn.prepareStatement(INSERT); 
                conn.setAutoCommit(false);
                for(UserInfo user: userinfos) {
                     state.setString(1, user.getName());
                     state.setString(2, user.getPassword());
                     state.setInt(3, user.getAge());
                     state.setString(4, user.getSex());
                     state.setString(5, user.getEmail());
                     state.addBatch(); // 添加到本地batch
                     System.out.println("成功插入一条记录，总共1000条记录.");
                }
                state.executeBatch(); // 缓存所有的sql
                commit();
                System.out.println("插入记录完成!");
                return true;
            } catch (SQLException e) {
                e.printStackTrace();
                rollback();
            }finally {
                closeConnection();
            }
            return false;
        }
    public static void main(String[] args) {
        UserInfoDAO userInfoDAO = new UserInfoDAO();
        userInfoDAO.createTable();
        List<UserInfo> userinfos = new ArrayList<UserInfo>();
        userinfos.add(new UserInfo("Jan", "1234", 18, "1", "Jan@126.com"));
        userinfos.add(new UserInfo("An", "1234", 24, "1", "An@126.com"));
        userinfos.add(new UserInfo("Bob", "1234", 26, "1", "Bob@126.com"));
        userInfoDAO.save(userinfos);
        userInfoDAO.deleteTable();

    }
}

五、JDBC的XML配置

　　前端修炼之路里提到过，HTML是一种超文本标记语言，它的基础是XML。XML同样用标签化语义来保存节点信息。

　　1.创建JDBC配置信息的模型类。

public class DBInfo {
    private String url;
    private String driver;
    private String username;
    private String password;
    private String dbName;

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getDriver() {
        return driver;
    }

    public void setDriver(String driver) {
        this.driver = driver;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getDbName() {
        return dbName;
    }

    public void setDbName(String dbName) {
        this.dbName = dbName;
    }

    public String getAttUser() {
        return attUser;
    }

    public void setAttUser(String attUser) {
        this.attUser = attUser;
    }

    private String attUser;// 数据库标签的属性user

    @Override
    public String toString() {
        return '
' + "  { " + url + ", " +
                driver + ", " +
                username + ", " +
                password + ", " +
                dbName + ", " +
                attUser  + "} " + '
';
    }
}

　　2.XML保存JDBC配置信息。

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE jdbc [
        <!ELEMENT jdbc (oracle*,  mysql*)>
        <!ELEMENT oracle (url,driver,username,password)>
        <!ELEMENT mysql (url,driver,username,password)>
        <!ELEMENT url (#PCDATA)>
        <!ELEMENT driver (#PCDATA)>
        <!ELEMENT username (#PCDATA)>
        <!ELEMENT password (#PCDATA)>
        <!ATTLIST oracle user CDATA #REQUIRED>
        <!ATTLIST mysql user CDATA #REQUIRED>
]>

<jdbc>
    <oracle user="oracle">
        <url>jdbc:oracle:thin:@10.211.55.15:1521:orcl</url>
        <driver>oracle.jdbc.driver.OracleDriver></driver>
        <username>scott</username>
        <password>tiger</password>
    </oracle>

    <mysql user="mysql">
        <url>jdbc:mysql://localhost:3306/test</url>
        <driver>com.mysql.cj.jdbc.Driver</driver>
        <username>root</username>
        <password>1234</password>
    </mysql>
</jdbc>

　　3.XML的读写操作。

import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.dom4j.io.XMLWriter;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

public class XMLUtils {
    public static final String JDBC = "jdbc";
    public static final String ORACLE = "oracle";
    public static final String MYSQL = "mysql";
    public static final String URL = "url";
    public static final String DRIVER = "driver";
    public static final String USER_NAME = "username";
    public static final String PASSWORD = "password";
    public static final String USER = "user";


    /**
     * 解析XML文件，返回配置参数
     * @param stream
     * @return
     */
    public static List<DBInfo> xmlToDBInfo(InputStream stream){
        /*
        使用DOM解析XML文件，DOM4J和JDOM
        解析步骤：
            1.创建XML解析器
            2.读取XML文件，返回DOM对象，参见python BeautifulSoup模块
            3.根据包提供的方法获取指定节点(文本)
         */
        SAXReader reader = new SAXReader();//
        reader.setValidation(true); // 检查xml的合法性，验证DTD<xml .... />，非必要

        Document dom;
        try{
            dom = reader.read(stream); // 读取并解析文件
        }catch (Exception e){
            e.printStackTrace();
            throw new RuntimeException("数据读取错误！", e);
        }

        Element root = dom.getRootElement();// 获取文件

        if(!JDBC.equals(root.getName())){
            throw new RuntimeException("数据格式错误！根应该是jdbc");
        }
        /*
        root.elements();// 获取所有元素以集合的形式返回
        root.element(String name);// 获取指定子元素
        root.elements(String name);// 获取指定名字的所有子元素
         */

        List<Element> childList = root.elements();

        List<DBInfo> infos = new ArrayList<DBInfo>();
        for (Element child: childList){
            // 将标签数据转化为DBInfo对象并存入集合infos
            DBInfo info = toDBInfo(child);
            infos.add(info);
        }

        return infos;
    }

    /**
     * 将标签内容转化为DBInfo对象
     * @param element
     * @return
     */
    private static DBInfo toDBInfo(Element element){
        DBInfo info = new DBInfo();
        String url = element.elementText(URL);
        String driver = element.elementText(DRIVER);
        String username = element.elementText(USER_NAME);
        String password = element.elementText(PASSWORD);

        // 获取标签的属性
        String attUser = element.attributeValue(USER);
        // 获取标签的名字
        String dbName = element.getName();
        info.setAttUser(attUser);
        info.setDbName(dbName);
        info.setDriver(driver);
        info.setPassword(password);
        info.setUrl(url);
        info.setUsername(username);
        return info;
    }

    /**
     * 将一组数据写到XML文件里，跟js添加节点元素和子元素类似
     * @param infos
     * @param path
     */
    public static void writeDBInfosToXml(List<DBInfo> infos, OutputStream out) throws IOException {

        /*
        1.创建一个文档对象document
        2.向文档对象中添加根标记
        3.向根标记中添加元素内容
        4.将根标记写出
         */
        Document doc = DocumentHelper.createDocument();

        Element root = doc.addElement(JDBC);// 创建根节点

        for(DBInfo info: infos){
            Element element = toDBInfoElement(info);
            root.add(element);
        }

        XMLWriter writer = new XMLWriter(out);
        writer.write(root);//将这个根节点写除去
        writer.close();
        System.out.println("写出完毕!");
        return;
    }
    private static Element toDBInfoElement(DBInfo info){

        Element element = DocumentHelper.createElement(info.getDbName());// 创建一个


        /*
        Element url = DocumentHelper.createElement(URL);
        url.setText(info.getUrl());

        Element driver = DocumentHelper.createElement(DRIVER);
        driver.setText(info.getDriver());

        Element username = DocumentHelper.createElement(USER_NAME);
        username.setText(info.getUsername());

        Element password = DocumentHelper.createElement(PASSWORD);
        password.setText(info.getPassword());

        element.add(url)
        element.add(driver);
        element.add(username);
        element.add(password);
        */

        // 简写
        element.addElement(URL).setText(info.getUrl());// addElement返回这个元素
        element.addElement(DRIVER).setText(info.getDriver());
        element.addElement(USER_NAME).setText(info.getUsername());
        element.addElement(PASSWORD).setText(info.getPassword());

        element.addAttribute(USER, info.getAttUser());
        return element;

    }
}

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.List;

public class TestXMLUtils {
    public static void main(String[] args) throws Exception{


        FileInputStream file = new FileInputStream("src/jdbc/xml/jdbc.xml");
        List<DBInfo> infos = XMLUtils.xmlToDBInfo(file);
        System.out.println(infos);


        File path = new File("out.xml");
        FileOutputStream outFile = new FileOutputStream(path);
        XMLUtils.writeDBInfosToXml(infos, outFile);
    }
}

　　当然JDBC的配置信息也可以通过Xpath来查询。留以后述。